Parallel index creation & pg_stat_activity

[Phil Florent]

Hi,
I created an index on a 11devel base while sampling pg_stat_activity with a 
little tool. Tool catches a line if state = active. Collected rows are 
aggregated and sorted by activity percentage.

Test environment :

select version();
  version

PostgreSQL 11devel (Debian 11~~devel~20180227.2330-1~420.git51057fe.pgdg+1) on 
x86_64-pc-linux-gnu, compiled by gcc (Debian 7.3.0-5) 7.3.0, 64-bit
(1 ligne)

Temps : 0,762 ms

create table t1(c1 bigint, c2 double precision, c3 text);
CREATE TABLE

insert into t1 select generate_series(1,1,1), random(), 
md5(random()::text) ;
INSERT 0 1

With a select (select max(c1) from t1 group by c2;) I have this kind of output :

./t -d 20 -o "pid, backend_type, query, wait_event_type, wait_event"
traqueur 2.04.00 - performance tool for PostgreSQL 9.3 => 11
INFORMATION, no connection parameters provided, connecting to traqueur database 
...
INFORMATION, connected to traqueur database
INFORMATION, PostgreSQL version : 11
INFORMATION, sql preparation ...
INFORMATION, sql execution ...
busy_pc | distinct_exe |  pid  |  backend_type   |query 
   | wait_event_type |  wait_event
-+--+---+-+-+-+--
  76 | 1 / 151  | 10065 | parallel worker | select max(c1) from t1 
group by c2; | IO  | DataFileRead
  73 | 1 / 146  |  8262 | client backend  | select max(c1) from t1 
group by c2; | IO  | DataFileRead
  72 | 1 / 144  | 10066 | parallel worker | select max(c1) from t1 
group by c2; | IO  | DataFileRead
  26 | 1 / 53   | 10066 | parallel worker | select max(c1) from t1 
group by c2; | |
  26 | 1 / 51   |  8262 | client backend  | select max(c1) from t1 
group by c2; | |
  24 | 1 / 47   | 10065 | parallel worker | select max(c1) from t1 
group by c2; | |
   2 | 1 / 3| 10066 | parallel worker | select max(c1) from t1 
group by c2; | IO  | BufFileWrite
   2 | 1 / 3|  8262 | client backend  | select max(c1) from t1 
group by c2; | IO  | BufFileWrite
   1 | 1 / 2| 10065 | parallel worker | select max(c1) from t1 
group by c2; | IO  | BufFileWrite



With an index creation (create index t1_i1 on t1(c1, c2);) I have this kind of 
output :

./t -d 20 -o "pid, backend_type, query, wait_event_type, wait_event"
traqueur 2.04.00 - performance tool for PostgreSQL 9.3 => 11
INFORMATION, no connection parameters provided, connecting to traqueur database 
...
INFORMATION, connected to traqueur database
INFORMATION, PostgreSQL version : 11
INFORMATION, sql preparation ...
INFORMATION, sql execution ...
busy_pc | distinct_exe | pid  |  backend_type  |   query
   | wait_event_type |  wait_event
-+--+--++---+-+--
  68 | 1 / 136  | 8262 | client backend | create index t1_i1 on t1(c1, 
c2); | IO  | DataFileRead
  26 | 1 / 53   | 8262 | client backend | create index t1_i1 on t1(c1, 
c2); | |
   6 | 1 / 11   | 8262 | client backend | create index t1_i1 on t1(c1, 
c2); | IO  | BufFileWrite
(3 rows)


No parallel worker. At least one parallel worker was active though, I could see 
its work with a direct query on pg_stat_activity or a ps -ef :

...
postgres  8262  8230  7 08:54 ?00:22:46 postgres: 11/main: postgres 
postgres [local] CREATE INDEX
...
postgres  9833  8230 23 14:17 ?00:00:33 postgres: 11/main: parallel 
worker for PID 8262
...

Tool only catches activity of the client backend cause column state of 
pg_stat_activity is null for the parallel workers in this case. I added an 
option to do a  "(state = 'active' or wait_event_is not null)"  It's not 100% 
accurate though : I miss the activity of the parallel workers which is not 
waiting and it’s more difficult to know who helps whom since query is also null.
I can imagine various workarounds but 11 is in devel and maybe columns active & 
query of pg_stat_activity will be filled for the parallel workers even for an 
index creation ?

Best regards
Phil

No parallel plan on an union all subquery

[Phil Florent]

Hi,

I have a question about parallel plans. Here is my test case :

select version();
 version
--
 PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled 
by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

create unlogged table drop_me as select generate_series(1,7e7) n1;
SELECT 7000

explain
select count(*)
from (select
 n1
  from drop_me
) s;

  QUERY PLAN
--
 Finalize Aggregate  (cost=675319.13..675319.14 rows=1 width=8)
   ->  Gather  (cost=675318.92..675319.13 rows=2 width=8)
 Workers Planned: 2
 ->  Partial Aggregate  (cost=674318.92..674318.93 rows=1 width=8)
   ->  Parallel Seq Scan on drop_me  (cost=0.00..601402.13 
rows=29166713 width=0)
 JIT:
   Functions: 4
   Options: Inlining true, Optimization true, Expressions true, Deforming true

Parallel plan, OK, 1s

explain
select count(*)
from (select
 n1
  from drop_me
  union all
  select
 n1
  from drop_me) ua;

  QUERY PLAN
--
 Finalize Aggregate  (cost=1640315.00..1640315.01 rows=1 width=8)
   ->  Gather  (cost=1640314.96..1640314.99 rows=2 width=8)
 Workers Planned: 2
 ->  Partial Aggregate  (cost=1640304.96..1640304.97 rows=1 width=8)
   ->  Parallel Append  (cost=0.00..1494471.40 rows=58333426 
width=0)
 ->  Parallel Seq Scan on drop_me  (cost=0.00..601402.13 
rows=29166713 width=0)
 ->  Parallel Seq Scan on drop_me drop_me_1  
(cost=0.00..601402.13 rows=29166713 width=0)
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true


Parallel plan, 2s2

explain
select count(*)
from (select
 n1
  from drop_me
  union all
  values(1)) ua;

   QUERY PLAN

 Aggregate  (cost=2934739.24..2934739.25 rows=1 width=8)
   ->  Append  (cost=0.00..2059737.83 rows=7113 width=32)
 ->  Seq Scan on drop_me  (cost=0.00..1009736.12 rows=7112 width=6)
 ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=32)
   ->  Result  (cost=0.00..0.01 rows=1 width=4)
 JIT:
   Functions: 4
   Options: Inlining true, Optimization true, Expressions true, Deforming true

No parallel plan, 2s6

I read the documentation but I don't get the reason of the "noparallel" seq 
scan of drop_me in the last case ?

Best regards,

Phil

Strange (and good) side effect of partitioning ?

[Phil Florent]

Hi,

I read that on Jonathan Lewis' blog :

(I believe that there may be some RDBMS which will treat (e.g.) “X between 20 
and 10” as being identical to “X between 10 and 20” )

I am puzzled. PostgreSQL seems NOT to treat X between 20 and 10” as being 
identical to “X between 10 and 20" but it's complicated.

Here is my test case:

select version();
 version
--
 PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled 
by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

create table t1 (rn integer , object_name text) partition by range(rn);

create table t1a partition of t1 for values from (1) to (50001);


\d+ t1
 Table partitionnée « public.t1 »
   Colonne   |  Type   | Collationnement | NULL-able | Par défaut | Stockage | 
Cible de statistiques | Description
-+-+-+---++--+---+-
 rn  | integer | |   || plain|  
 |
 object_name | text| |   || extended |  
 |
Clé de partition : RANGE (rn)
Partitions: t1a FOR VALUES FROM (1) TO (50001)



insert into t1 select
rownum  rn,
upper(md5(random()::text)) object_name
from
(select generate_series(1,5) rownum) serie
;

explain analyze select  object_name
fromt1
where
rn between 20 and 10
;
 QUERY PLAN

 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.002 rows=0 
loops=1)
   One-Time Filter: false
 Planning Time: 0.116 ms
 Execution Time: 0.020 ms


It's OK but:

explain analyze select  object_name
fromt1a
where
rn between 20 and 10
;
   QUERY PLAN
-
 Seq Scan on t1a  (cost=0.00..1167.00 rows=1 width=33) (actual 
time=6.553..6.553 rows=0 loops=1)
   Filter: ((rn >= 20) AND (rn <= 10))
   Rows Removed by Filter: 5
 Planning Time: 0.092 ms
 Execution Time: 6.573 ms

At first I thought it was related to partition pruning but:

set enable_partition_pruning = false;

explain analyze select  object_name
fromt1
where
rn between 20 and 10
;

 QUERY PLAN

 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003 rows=0 
loops=1)
   One-Time Filter: false
 Planning Time: 0.104 ms
 Execution Time: 0.021 ms


Confirmation since I still obtain "One-Time Filter: false" if I don't filter on 
the partition key:

create table t2 (rn integer , rn2 integer, object_name text) partition by 
range(rn);

create table t2a partition of t2 for values from (1) to (50001);

d+ t2
 Table partitionnée « public.t2 »
   Colonne   |  Type   | Collationnement | NULL-able | Par défaut | Stockage | 
Cible de statistiques | Description
-+-+-+---++--+---+-
 rn  | integer | |   || plain|  
 |
 rn2 | integer | |   || plain|  
 |
 object_name | text| |   || extended |  
 |
Clé de partition : RANGE (rn)
Partitions: t2a FOR VALUES FROM (1) TO (50001)

insert into t2 select
rownum  rn, rownum rn2,
upper(md5(random()::text)) object_name
from
(select generate_series(1,5) rownum) serie
;

explain analyze select  object_name
fromt2
where
rn2 between 20 and 10
;


 QUERY PLAN

 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003 rows=0 
loops=1)
   One-Time Filter: false
 Planning Time: 0.185 ms
 Execution Time: 0.019 ms

I don't understand why I don't obtain " One-Time Filter: false" with a classic 
table or a partition ?

Best regards,

Phil

RE: Strange (and good) side effect of partitioning ?

[Phil Florent]

Hi Tom, Hi Rob

Thanks for this clear and complete explanation. My question was unclear since I 
didn't even consider the results could be identical and it was about the plans. 
I had misunderstood what J.Lewis had written since he probably meant some RDBMS 
always do a BETWEEN SYMETRIC. Our application currently has double 
compatibility with Oracle and PostgreSQL, PostgreSQL only after 2021, and I 
hope we won't be compatible with a RDBMS that would not respect SQL standard on 
this aspect.

>But if you have a workload where it's really worth spending
>planner cycles looking for self-contradictory queries, you can
>turn it on.

It was theoretical but it is a DSS tool and some queries can be dynamically 
built by the end users. Perhaps it really happens on some cases since I don't 
know if we always check criterias not to obtain self-contradictory queries. 
Since it's not OLTP our execution times are always much more important than our 
planning times anyway.

There are other places it could be more interesting to spend time for better 
performance. Debian 10/PostgreSQL 11 is our initial PostgreSQL platform. It's 
very efficient but I have to prepare Debian 11/PostgresQL 13. My first goal was 
to avoid performance regressions but I now want to always fully use planning 
capabilites of PostgreSQL. We currently have to completely deactive merge joins 
for some workload, nested loops for some other workload. It's OK but it's not 
optimal.
My current goal is to always activate (almost) everything with Debian 
11/PostgreSQL 13 and everything with Debian 12/PostgreSQL 14+.
I will try to increase default_statistics_target it could be worth the price. I 
will also try to activate enable_partitionwise_aggregate and 
enable_partitionwise_join since we use partitioning by list of hospitals and 
subpartitioning by range of times. Replacing our slow Oracle "union all" views 
by PostgreSQL partitioned tables to deal with group of hospitals has still to 
be completed.

Best regards,

Phil



De : Tom Lane 
Envoyé : vendredi 15 janvier 2021 03:12
À : Phil Florent 
Cc : pgsql-gene...@postgresql.org 
Objet : Re: Strange (and good) side effect of partitioning ?

I wrote:
> There's no specific mechanism in Postgres that would cause "X between 20
> and 10" to be reduced to constant-false

Wait, I take that back.  There is a mechanism that can conclude that
"X >= 20" and "X <= 10" are contradictory, but it's not applied by
default.  Observe:

regression=# set constraint_exclusion = default;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
 QUERY PLAN
-
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: ((unique1 >= 20) AND (unique1 <= 10))
(2 rows)

regression=# set constraint_exclusion = on;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
QUERY PLAN
--
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

The default value of constraint_exclusion is "partition", which means
(you guessed it) that it's applied only to potential partitioning
constraints.  This is a heuristic based on the typical payoff of
excluding whole partitions versus skipping an empty index scan.
But if you have a workload where it's really worth spending
planner cycles looking for self-contradictory queries, you can
turn it on.

regards, tom lane

RE: Strange (and good) side effect of partitioning ?

[Phil Florent]

Hi Tom,

>The default value of constraint_exclusion is "partition", which means
>(you guessed it) that it's applied only to potential partitioning
>constraints.  This is a heuristic based on the typical payoff of
>excluding whole partitions versus skipping an empty index scan.
>But if you have a workload where it's really worth spending
>planner cycles looking for self-contradictory queries, you can
>turn it on.

Interesting. Test case was not real but planning times have to be considered 
from a more general point of view. They are not a problem with our DSS app but 
we will also migrate our OLTP applications.
Partitioning is something new for me since we currently don't use it for our 
OLTP apps. It was not a technical choice, partitioning is not included in 
standard license of our current RDBMS. I will globally check the gain/loss with 
real workloads anyway.

Best regards,

Phil




De : Tom Lane 
Envoyé : vendredi 15 janvier 2021 03:12
À : Phil Florent 
Cc : pgsql-gene...@postgresql.org 
Objet : Re: Strange (and good) side effect of partitioning ?

I wrote:
> There's no specific mechanism in Postgres that would cause "X between 20
> and 10" to be reduced to constant-false

Wait, I take that back.  There is a mechanism that can conclude that
"X >= 20" and "X <= 10" are contradictory, but it's not applied by
default.  Observe:

regression=# set constraint_exclusion = default;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
 QUERY PLAN
-
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: ((unique1 >= 20) AND (unique1 <= 10))
(2 rows)

regression=# set constraint_exclusion = on;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
QUERY PLAN
--
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

The default value of constraint_exclusion is "partition", which means
(you guessed it) that it's applied only to potential partitioning
constraints.  This is a heuristic based on the typical payoff of
excluding whole partitions versus skipping an empty index scan.
But if you have a workload where it's really worth spending
planner cycles looking for self-contradictory queries, you can
turn it on.

regards, tom lane

Support logical replication of DDLs

[Phil Florent]

Hi,

As an end-user, I am highly interested in the patch 
https://commitfest.postgresql.org/42/3595/ but I don't fully get its main goal 
in its first version.
It's "for all tables"  that will be implemented ?
If one needs a complete replication of a cluster, a hot standby will always be 
more efficient than a publication right ? I use both for different needs in 
public hospitals I work for (hot standby for disaster recovery & logical 
replication for dss)
The main interest of a publication is to be able to filter things on the 
publisher and to add stuff on the replicated cluster.
If you compare PostgreSQL with less avanced RDBMS that don't really implement 
schemas (typically Oracle), the huge advantage of Postgre is that many things 
(e.g security) can be dynamically implemented via schemas.
Developers just have put a table in the "good" schema and that's all. Logical 
DML replication now fully implements this logic since PostgreSQL 15. Only 
remaining problem is that a "for tables in schema" publication has to be owned 
by a superuser (because a normal user can have tables that don't belong to him 
in a schema it owns ?) If DDL replication only works with "FOR ALL TABLES " and 
not "FOR TABLES IN SCHEMA" it reduces its interest anyway.
The main problem with DML replication as of PostgreSQL 15 is that some DDL can 
interrupt the logical replication (new tables, new columns etc.).
If DDL replication was able to avoid dynamically those incidents, it would be 
great. If many features are missing it's normal. But if the first version was 
able be to deal with the majority of DDL commands that can cause incidents with 
the current DML implementation (a new table can break logical DML replication 
but a new index cannot etc.) and to implement the very same logic that is used 
with DML replication in terms of granularity it would be a huge plus.
Thoughts ?

Best regards,
Phil

Unlogged partitionned tables and hot standbys

[Phil Florent]

Hi,



Today I corrected a bug on my perf tool, I had forgotten to set the unlogged 
attribute on the table partitions if end-user wanted to miminize the overhead 
of data collection and purges. Then I played a bit with partitioning, unlogged 
attribute and hot standby just for fun and I am a bit puzzled.



Here is a very simple case :



traqueur=# select version();

  version

---

PostgreSQL 15.5 (Debian 15.5-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc 
(Debian 12.2.0-14) 12.2.0, 64-bit

(1 ligne)



Suppose I created my partionned table with "unlogged" attribute even if it does 
not mean much (perhaps command should ignore/refuse my unlogged attribute here 
but it is accepted). The partition was also created with the "unlogged" 
attribute.



  Schéma  |  Nom  |Type 
   | Propriétaire |  Persistence   | Méthode d'accès |   Taille   | 
Description

--+---++--++-++-

traqueur | traqueur_bloqueurs_process| table 
partitionnée | postgres | non journalisé | | 0 bytes|

traqueur | traqueur_bloqueurs_process_2023_12_21_2023_12_22  | table
  | postgres | non journalisé | heap| 16 kB  |



On the hot standby I cannot query the partitionned table nor its partition, 
which is quite logical :



traqueur=*# table traqueur_bloqueurs_process;

ERREUR:  ne peut pas accéder à des tables temporaires et non tracées lors de la 
restauration



traqueur=*# table traqueur_bloqueurs_process_2023_12_21_2023_12_22;

ERREUR:  ne peut pas accéder à des tables temporaires et non tracées lors de la 
restauration



(seems error message is not 100% consistent (in French only ?) with psql 
information "non tracées" vs "non journalisé" but OK)



Now I want to turn the logging mode of those tables to "permanent" to query the 
perf information from the standby. I do that on the primary cluster :



traqueur=# alter table traqueur_bloqueurs_process set logged;

ALTER TABLE

traqueur=# \d+

Liste 
des relations

  Schéma  |  Nom  |Type 
   | Propriétaire |  Persistence   | Méthode d'accès |   Taille   | 
Description

--+---++--++-++-

traqueur | traqueur_bloqueurs_process| table 
partitionnée | postgres | non journalisé | | 0 bytes|





My command silently did nothing on the partitioned table and it seems it is not 
really an issue, only partitions do have data after all.



traqueur=# alter table traqueur_bloqueurs_process_2023_12_21_2023_12_22 set 
logged;

ALTER TABLE

traqueur=# \d+

Liste 
des relations

  Schéma  |  Nom  |Type 
   | Propriétaire |  Persistence   | Méthode d'accès |   Taille   | 
Description

--+---++--++-++-

traqueur | traqueur_bloqueurs_process| table 
partitionnée | postgres | non journalisé | | 0 bytes|

traqueur | traqueur_bloqueurs_process_2023_12_21_2023_12_22  | table
  | postgres | permanent  | heap| 8192 bytes |

...



Situation is 100% OK on the primary cluster but I still cannot query my 
partitioned table on the hot standby database even if I can now query its 
partition:



traqueur=*# table traqueur_bloqueurs_process;

ERREUR:  ne peut pas accéder à des tables temporaires et non tracées lors de la 
restauration



traqueur=*# table traqueur_bloqueurs_process_2023_12_21_2023_12_22;

...

(0 ligne)



It could be convenient that the "alter table  set logged;" changes the mode 
even on partitioned tables or that the unlogged attribute could be 
ignored/refused at the creation of the partitioned table seems if it has no 
sense ? Seems a bit strange to apply it with « create table » and to ignore it 
with « alter table », it has consequences in the case I described . I have no 
way to properly correct my initial mistake that was silently applied.

The idea is not to change anything on the underlying partitions, command would 
still do "nothing" about logging but it would allow the partitioned table to be 
queried on the standby after the change. I gues

pg 13 fatal error message mentionning "standby_mode"

[Phil Florent]

Hi,

I had forgotten to include a restore_command and my standby database did not 
start (expected behavior) but the error message puzzled me :


select version();

  version

---

PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 
10.2.1-6) 10.2.1 20210110, 64-bit

(1 ligne)



juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]: 2021-06-08 
11:50:26.666 CEST [2927] FATAL:  doit spécifier une restore_command quand 
standby_mode n'est pas activé

It's in French but it means I have to specify restore_command if standby_mode 
is not activated.

It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ? 
Perhaps the French translation is wrong ?


Best regards,

Phil

RE: pg 13 fatal error message mentionning "standby_mode"

[Phil Florent]

Thanks Guillaume. After this change the error message will even be clearer in 
French than in the original version in fact.
Regards,
Phil


De : Guillaume Lelarge 
Envoyé : mardi 8 juin 2021 13:21
À : Phil Florent 
Cc : pgsql-gene...@postgresql.org 
Objet : Re: pg 13 fatal error message mentionning "standby_mode"

Le mar. 8 juin 2021 à 13:15, Guillaume Lelarge 
mailto:guilla...@lelarge.info>> a écrit :
Hi,

Le mar. 8 juin 2021 à 12:11, Phil Florent 
mailto:philflor...@hotmail.com>> a écrit :
Hi,

I had forgotten to include a restore_command and my standby database did not 
start (expected behavior) but the error message puzzled me :


select version();

  version

---

PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 
10.2.1-6) 10.2.1 20210110, 64-bit

(1 ligne)



juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]: 2021-06-08 
11:50:26.666 CEST [2927] FATAL:  doit spécifier une restore_command quand 
standby_mode n'est pas activé

It's in French but it means I have to specify restore_command if standby_mode 
is not activated.

It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ? 
Perhaps the French translation is wrong ?


The translation is wrong. The original message is "must specify restore_command 
when standby mode is not enabled". There's no underscore between standby and 
mode, so it isn't the parameter. I must have missed that change during the 
translation. I'll fix it right away.


Done.


--
Guillaume.

RE: Unlogged partitionned tables and hot standbys

[Phil Florent]

Hi,
Old mail but I see the issue is corrected in PostgreSQL 18 devel, many thanks.
Best regards,
Phil

De : Phil Florent 
Envoyé : jeudi 21 décembre 2023 17:00
À : pgsql-gene...@postgresql.org 
Objet : Unlogged partitionned tables and hot standbys


Hi,



Today I corrected a bug on my perf tool, I had forgotten to set the unlogged 
attribute on the table partitions if end-user wanted to miminize the overhead 
of data collection and purges. Then I played a bit with partitioning, unlogged 
attribute and hot standby just for fun and I am a bit puzzled.



Here is a very simple case :



traqueur=# select version();

  version

---

PostgreSQL 15.5 (Debian 15.5-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc 
(Debian 12.2.0-14) 12.2.0, 64-bit

(1 ligne)



Suppose I created my partionned table with "unlogged" attribute even if it does 
not mean much (perhaps command should ignore/refuse my unlogged attribute here 
but it is accepted). The partition was also created with the "unlogged" 
attribute.



  Schéma  |  Nom  |Type 
   | Propriétaire |  Persistence   | Méthode d'accès |   Taille   | 
Description

--+---++--++-++-

traqueur | traqueur_bloqueurs_process| table 
partitionnée | postgres | non journalisé | | 0 bytes|

traqueur | traqueur_bloqueurs_process_2023_12_21_2023_12_22  | table
  | postgres | non journalisé | heap| 16 kB  |



On the hot standby I cannot query the partitionned table nor its partition, 
which is quite logical :



traqueur=*# table traqueur_bloqueurs_process;

ERREUR:  ne peut pas accéder à des tables temporaires et non tracées lors de la 
restauration



traqueur=*# table traqueur_bloqueurs_process_2023_12_21_2023_12_22;

ERREUR:  ne peut pas accéder à des tables temporaires et non tracées lors de la 
restauration



(seems error message is not 100% consistent (in French only ?) with psql 
information "non tracées" vs "non journalisé" but OK)



Now I want to turn the logging mode of those tables to "permanent" to query the 
perf information from the standby. I do that on the primary cluster :



traqueur=# alter table traqueur_bloqueurs_process set logged;

ALTER TABLE

traqueur=# \d+

Liste 
des relations

  Schéma  |  Nom  |Type 
   | Propriétaire |  Persistence   | Méthode d'accès |   Taille   | 
Description

--+---++--++-++-

traqueur | traqueur_bloqueurs_process| table 
partitionnée | postgres | non journalisé | | 0 bytes|





My command silently did nothing on the partitioned table and it seems it is not 
really an issue, only partitions do have data after all.



traqueur=# alter table traqueur_bloqueurs_process_2023_12_21_2023_12_22 set 
logged;

ALTER TABLE

traqueur=# \d+

Liste 
des relations

  Schéma  |  Nom  |Type 
   | Propriétaire |  Persistence   | Méthode d'accès |   Taille   | 
Description

--+---++--++-++-

traqueur | traqueur_bloqueurs_process| table 
partitionnée | postgres | non journalisé | | 0 bytes|

traqueur | traqueur_bloqueurs_process_2023_12_21_2023_12_22  | table
  | postgres | permanent  | heap| 8192 bytes |

...



Situation is 100% OK on the primary cluster but I still cannot query my 
partitioned table on the hot standby database even if I can now query its 
partition:



traqueur=*# table traqueur_bloqueurs_process;

ERREUR:  ne peut pas accéder à des tables temporaires et non tracées lors de la 
restauration



traqueur=*# table traqueur_bloqueurs_process_2023_12_21_2023_12_22;

...

(0 ligne)



It could be convenient that the "alter table  set logged;" changes the mode 
even on partitioned tables or that the unlogged attribute could be 
ignored/refused at the creation of the partitioned table seems if it has no 
sense ? Seems a bit strange to apply it with « create table » and to ignore it 

pg_comebinebackup

[Phil Florent]

Hi,
Not really a bug but I see in the feature matrix a small typo 
,https://www.postgresql.org/about/featurematrix/
Feature Matrix - PostgreSQL
17 16 15 14 13 12 11 10 9.6 9.5 9.4 9.3 9.2 9.1 9.0 8.4 8.3 8.2 8.1; ANY_VALUE 
aggregate: Yes: Yes: No: No: No: No: No: No: No: No: No: No: No: No: No: No: 
No: No: No ...
www.postgresql.org


pg_comebinebackup instead of pg_combinebackup

Regards,
Phil