Hi,
Old mail but I see the issue is corrected in PostgreSQL 18 devel, many thanks.
Best regards,
Phil
________________________________
De : Phil Florent <[email protected]>
Envoyé : jeudi 21 décembre 2023 17:00
À : [email protected] <[email protected]>
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 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
