Unlogged partitionned tables and hot standbys - Mailing list pgsql-general

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 XXXX 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 guess it could be possible to update pg_class manually with 'p' instead of 'u' for this table as a very dirty workaround but updating manually the catalog is never a good idea.

Best regards,

Phil