Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uAq9R-002ot5-JT for pgsql-general@arkaria.postgresql.org; Fri, 02 May 2025 13:10:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uAq9P-00938U-Ju for pgsql-general@arkaria.postgresql.org; Fri, 02 May 2025 13:10:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uAq9O-00938K-RL for pgsql-general@lists.postgresql.org; Fri, 02 May 2025 13:10:00 +0000 Received: from mail-vi1eur03olkn2102.outbound.protection.outlook.com ([40.92.57.102] helo=EUR03-VI1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uAq9N-000gtR-1I for pgsql-general@postgresql.org; Fri, 02 May 2025 13:09:58 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=gnxnZjLolcR40D3q0XMfWF/3i8UiT6zMepiXn5iP6oP9PYE6bXOwVyT5tK3IQ0/wlJBJCXEOpFQx+Qd4x+WR10xezTqWtQofOXgC9hiuZzmN8XQnhi5R+wf/OUpwVYtpW73+yhvdwZtwQ8cRlUpY41MEanA80dM7mDK+oxU2CF6KSovddSL30lc55e3KtFcPAnGix5mDqAE7ycVaWxPPf1wV+tZkCbGNwQ7iRrn2bh/Z5MYXIVEGtR1ofSU4pfOnohiUy1IcFW5jFuAF/FGDV9ArLViBKvYUSAv1JoEYplDZsKNbb0h3srCltpOt8ebte20WXqCldxqzOKZuZCpPRA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=0U9Pmhjs2iuIyCg941Xzvn7Vi7evY1/4L51uDCEEVaI=; b=IPOgmhjOp8dUGHCfm2uKgfsKnqtDzkvVWqJQWKljJZnwopgru1WRQD4EKomeQIK7vMfahlu/yMDFFHJvRF5uZdcF/kOtVOWN0VPEQFdC165nRpdlsPeigXR7x3m2MaPRM3FdSq7rgtL6bissE8sjwyQ+19jMu0+9SRApe0UY6WVieo38fY/Ct8BS60A+2WytL4grjgokubbUgZbIhNCcGkI90rWmviFs5c6ANlxFezIZz4dBxTMbJjQgTDPzF8u8MjZ6NruFikyHzwxDsSxE9d8uUjEBVoCfhi2DInRZlmYcS7LgfUvD1EEb+QOaweEhr8AN00NGak2QVrqmdzQr/Q== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=0U9Pmhjs2iuIyCg941Xzvn7Vi7evY1/4L51uDCEEVaI=; b=UcTS7zCgTtBlcMfde65gKuYSUf5Ya2HbOxDlxA/7pjzQ+6H/TSs1W8AMLJeJPv1Su9ce6YNfznHqfcFSkvE4+/Sw5sNg/Sb90Gi40cFYfXkuGFY6S0Sdgp9lkkAsQNDp2pWx0gTxW62zsBlieMOwrH4YMQE+rqba4SeXRaChJvWTVFMbyxLDS3xZk4pTsYS19wOnyLyy9XgFAQA0d7OHTh0R2UmxiYKl4yBvth/2BJ8K0CtgxyeSnf0Zd9pE8mH4EUaT9kF2zT3sJMOMOy9Fkxa9ScyXRpgBtmkwOJIx4seSA11Cxx0wyEwDzFXqPHqvB7P9jIbzEwotuljl1SdUmg== Received: from DU2PR10MB7766.EURPRD10.PROD.OUTLOOK.COM (2603:10a6:10:49c::5) by VI1PR10MB3518.EURPRD10.PROD.OUTLOOK.COM (2603:10a6:800:140::13) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8699.24; Fri, 2 May 2025 13:09:53 +0000 Received: from DU2PR10MB7766.EURPRD10.PROD.OUTLOOK.COM ([fe80::f079:7324:1abc:c1c7]) by DU2PR10MB7766.EURPRD10.PROD.OUTLOOK.COM ([fe80::f079:7324:1abc:c1c7%5]) with mapi id 15.20.8678.028; Fri, 2 May 2025 13:09:53 +0000 From: Phil Florent To: "pgsql-general@postgresql.org" Subject: RE: Unlogged partitionned tables and hot standbys Thread-Topic: Unlogged partitionned tables and hot standbys Thread-Index: AQHaNCKzSxYbN+YHdUyRtTPyYjLsX7PCXmKc Date: Fri, 2 May 2025 13:09:52 +0000 Message-ID: References: In-Reply-To: Accept-Language: fr-FR, en-US Content-Language: fr-FR X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DU2PR10MB7766:EE_|VI1PR10MB3518:EE_ x-ms-office365-filtering-correlation-id: acf84087-22cc-4c16-19b4-08dd897aa08b x-ms-exchange-slblob-mailprops: J5N6liNEYVTIhkbwDb3YRPN8U/UymBK7bmYCtvw9FSIbFOuAPt2DrlAoqU9RTTt7wmE/4GiyE7RypGi0sOCkrRHMtaRaKt/HRKt1SDQ/x6XlrQwsLr74IOgQeAR20RDjeakPh43LAK9N9SafPe28MsJ6nET6KkrzDYPolvMDK2Z1t5ZF6Zn2OG0Zf5CYFIn64/hc9TgwWeaRqV3V6zNTk3vrZ8idVPa/w2WLI6fnlertLuL28D00W5gqPDcPPa3rfZBjMJ9JbS5AVkkUWIoNyBXkzYi1FmVfSjw0OhREzEzK8IPlyJfV3yOvBdZ27r1fQ4zRK8ak3pDrDTkJNHpTmAbivchlJAqjCf/8SJdRXKykC9kNK4XFUkEJbfFtMsTEDDX15CjG19uB6TGEL9RSbiBNm1v2X0uGXK0vZ9AdE55q5cJvzY6tWlth2uZpu72LYr5t0fegGIOr3wuyCFMcAkn8PhMx9N4VwIT+1QrmmNVRwhm4A93AUPFLxRI4LAurqOMaC0aRHtTKiQcWN92yZ9i1eyrALYxtIQBMeqV+3nI0zp+XQyGWo/3WXTl43MyAul1zoUwKL5qdRMxTU5Z9DM1geC7KsFAyVwiwdMZGP5I1knhUKPoTxdJ2YmzB0Wwi5/2e5JUgeM7DOxgJRP6u4UeK8CdCU77xvA7YwJIlFFPyelupT0IFwHe8H2Z8d8ka1t8BKGsb0MgSGfibfX1zAZa4xmZ4Q+ju x-microsoft-antispam: BCL:0;ARA:14566002|19110799003|8060799006|461199028|15030799003|8062599003|15080799006|7092599003|3412199025|19061999003|440099028|41001999003|18061999006|102099032; x-microsoft-antispam-message-info: =?iso-8859-1?Q?jmtXmJ57uNHB2fvXsL/uG5f0LA9Kc37ptEae3p7fTFY+vCDHBSPlQNJS89?= =?iso-8859-1?Q?TOHrx2gJ1S4I+bnv+/uC32KjmsB8wlDx5STcYwWe1oib5H/t405Bjdw9YE?= =?iso-8859-1?Q?SZMbM8AIuupNaYoZYg4tfrhWpU9ttjjns2af8t3ZAZRRElQYCTn9r7ZvE+?= =?iso-8859-1?Q?M8PoLcQRfm21F/SM7aN2heDDqQ173dTvacc9ZpKIn8P3+pxmqf2tILQCvk?= =?iso-8859-1?Q?g15KpLNlcHsk4jAV8nvnXkK6F5gQL4n0YMOypoR/PXYqk88eORZxIho96s?= =?iso-8859-1?Q?F0f0X9TK+X2Eoq0hNFeKvu+7aZkRCaY1j7484m//m55HWTbszBQNpBl4h7?= =?iso-8859-1?Q?ZJTetPyHa7FthhezqOqiQi6CmIb1eOcBNsym8otYZmZIcr3ChZpnAMc7XV?= =?iso-8859-1?Q?T6vrY/9jR9nDJDrA/hfWkPStsEROr3zwBYXokXmpgxgOhLAK/hRFn9IBXJ?= =?iso-8859-1?Q?UPo/II+KiETx1DSMzWoHC5gv2biEXb/QeTUcIVGMZYOz7uz+b/4PxvJL6V?= =?iso-8859-1?Q?WJzIWRh0Ki7FdEGc9sE/lPKexm78XK97XfU2dAE5hylLHm7J8UuSkJ4x77?= =?iso-8859-1?Q?NbGdrWXHKQ7TYccgJDw4DxeRZVs8Z5ttTirnRq5KTZRorwetVbYhusLn6J?= =?iso-8859-1?Q?vJXcbXWY6ixcqrH8t2RO03Ql2NTxfcQwe8BrLGHKUi4e9Mm8hxJ7T4Z+sV?= =?iso-8859-1?Q?n1M/8uL3J5suyU+rNCS04UL4VCqmT5R9/3RB9bUWTWUiQS+XOJ1OAxupTc?= =?iso-8859-1?Q?dpUdoT4CMv9qMf3af8u7OR8ZG3G6iyh43xo2l6b3ri5swhoerXK+bQ3odb?= =?iso-8859-1?Q?ja1RI/lg2cqNQGEhKD23lnyXdcPOFubGpqvDAlAYZ04IG0FlKp+SjCdqhA?= =?iso-8859-1?Q?/JK+wfpVkVxAjz4CT9M+NpnDCBzOwsC4lCPb2dPaY4/Z/YhZvKbi7ypBnw?= =?iso-8859-1?Q?nv30cNSozcwYhqgb1iRHWkD0TUi5jNN6kIHJ4/VnrlNPHzmMVnyp0VLqh6?= =?iso-8859-1?Q?KRiMndjSBR1EXMKGGikRGAw8uvdL50+FSYF1EzdSb/8yeMNAaYrZ9l2y2G?= =?iso-8859-1?Q?saLW5MvurSHOSAZgnGyJc1az07NPN2nuWAJVS1Gb5wUAWVr9F36VwWM/PB?= =?iso-8859-1?Q?/Tmg98cb5fy+abDAcYjQvSLizDi4Q2HseJLjbT17x1F2EBG71BU0vlsjBp?= =?iso-8859-1?Q?e/3d4FvQMVxlVm22DnmI1c11dSNZL2g6aqWN59C525CfVL0/JZfrkokLg5?= =?iso-8859-1?Q?2deOBSPwLXP+19SsDDGR16XZ68hUWZiH9RYxyqAhQ=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?+CiDFWER1wOcSrJeDzMjfGIzjIZSRLfHP281l2khJ7tkxAa1cjoJ92hYK5?= =?iso-8859-1?Q?u5LMRoI3yFNiBplKhbNpRHRhednOxP7ozG8Y3zOfMhgc+IiYUpkJCCQ03a?= =?iso-8859-1?Q?6FVk+pa8PfWtSsR6B9TtcpQmw7rLr+yzA9FY0ay6ufzXr1wXVNhCBINa0O?= =?iso-8859-1?Q?22gY4yj1lchQ99SJhKwoK0OSBvOP6m7n/zwmuVxE4riLz4yD+1isEogRri?= =?iso-8859-1?Q?MKbDkdB7ohw3M7Q5SRqFcjTFxzWYNxPzfa8XNk1x6Qi7/RXL5q5TwWlUY6?= =?iso-8859-1?Q?+/6MJNlwHTkP5eOZseQg2bUi1LxpdCGt8yDaPbhMNacibCjGzSAuKkwgwd?= =?iso-8859-1?Q?Jni/1Mw5jcMa1GmLZyUoxpNCPHLWMYnCwOdml9zxk/iFWnaF/8HlBOu+UF?= =?iso-8859-1?Q?oSmytIsSplBD//7L6khJvrYbvYuEVcxrpM9CJWFzfU3JzsLpjEgr54gXb4?= =?iso-8859-1?Q?vUuVR5OOVg2gnHZXmdfg2/3pj+3x8Rd7KHaS3eOZ6hUnOokDkz4OVyOKOy?= =?iso-8859-1?Q?66sdQuAF8DgSrZ9fRXlJ4aAuW5crOvUEc+FTC/XJVzWZejWzI7HZBMCna9?= =?iso-8859-1?Q?9v5izjtuRSuEcbYnMRU46tc5rCRKZbVJG3Sly18h/Wq6x58+EgxGjpNhHp?= =?iso-8859-1?Q?/ToJJDzKInn0k6z8lB2LyjGCJgBvmR9Eaectcots8WSaQgj3GblS3RMNMQ?= =?iso-8859-1?Q?Y6Ay1z1+o1cw0ESA6GnNEibhPGqUXWX1+5h2LVmcWH/092iRWYZjCv47rj?= =?iso-8859-1?Q?R9lxssceB/goy1ySHLT/Q36SKgmRKqy7QfdWo8Dm1jaeGnVZDhZkR1zUbI?= =?iso-8859-1?Q?oxZA611StRyPo7HL7vm/HAuScKsHpq7r02WHhwWzAroeTdV9ncaQUvQ/Iz?= =?iso-8859-1?Q?ofwzklI1ynX3dKNNPALvMHCmAZcCFxDxaBCsW27koUSpOjlh6FtubactM4?= =?iso-8859-1?Q?d+EzKmSsbDHjNxtH42yPl1f9VLCjSaoKCKh1GwMv4PwHZFsVxiVXCtFJ1h?= =?iso-8859-1?Q?JEOLK+nSzODfkuFUdt6LjV0NqChjb52JYL97RHIBBFcTNo+IaocFucHf3Q?= =?iso-8859-1?Q?PEsJjpOCe2xwqx+WfCmBwSoRt5H9wxuUuFYbQEjclaie3OSgs0RYk55wzP?= =?iso-8859-1?Q?5kNINbHkAjTGshGIE+DPiHCLibBVm7hQlmhVv0PXUTHdKqOfoan/V5Kqso?= =?iso-8859-1?Q?GhqDdau/nfG+jY/JzmeBVEzkT6rYYTfGDoKTs0GAE0zT1VK0hZUVwEEuaG?= =?iso-8859-1?Q?fS16//J3fC8Ri1L3qQnCP206IZHOvOXcjdHTMgD50=3D?= Content-Type: multipart/alternative; boundary="_000_DU2PR10MB77665E2EF27001AEA9C7ACEEBA8D2DU2PR10MB7766EURP_" MIME-Version: 1.0 X-OriginatorOrg: sct-15-20-7719-20-msonline-outlook-4359a.templateTenant X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DU2PR10MB7766.EURPRD10.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: acf84087-22cc-4c16-19b4-08dd897aa08b X-MS-Exchange-CrossTenant-originalarrivaltime: 02 May 2025 13:09:52.8626 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: VI1PR10MB3518 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DU2PR10MB77665E2EF27001AEA9C7ACEEBA8D2DU2PR10MB7766EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, Old mail but I see the issue is corrected in PostgreSQL 18 devel, many than= ks. Best regards, Phil ________________________________ De : Phil Florent Envoy=E9 : jeudi 21 d=E9cembre 2023 17:00 =C0 : pgsql-general@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 unlogge= d attribute on the table partitions if end-user wanted to miminize the over= head 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=3D# 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 attrib= ute here but it is accepted). The partition was also created with the "unlo= gged" attribute. Sch=E9ma | Nom | = Type | Propri=E9taire | Persistence | M=E9thode d'acc=E8s | T= aille | Description ----------+-------------------------------------------------------+--------= ------------+--------------+----------------+-----------------+------------= +------------- traqueur | traqueur_bloqueurs_process | table pa= rtitionn=E9e | postgres | non journalis=E9 | | 0 bytes = | traqueur | traqueur_bloqueurs_process_2023_12_21_2023_12_22 | table = | postgres | non journalis=E9 | heap | 16 kB = | On the hot standby I cannot query the partitionned table nor its partition,= which is quite logical : traqueur=3D*# table traqueur_bloqueurs_process; ERREUR: ne peut pas acc=E9der =E0 des tables temporaires et non trac=E9es = lors de la restauration traqueur=3D*# table traqueur_bloqueurs_process_2023_12_21_2023_12_22; ERREUR: ne peut pas acc=E9der =E0 des tables temporaires et non trac=E9es = lors de la restauration (seems error message is not 100% consistent (in French only ?) with psql in= formation "non trac=E9es" vs "non journalis=E9" 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=3D# alter table traqueur_bloqueurs_process set logged; ALTER TABLE traqueur=3D# \d+ Lis= te des relations Sch=E9ma | Nom | = Type | Propri=E9taire | Persistence | M=E9thode d'acc=E8s | T= aille | Description ----------+-------------------------------------------------------+--------= ------------+--------------+----------------+-----------------+------------= +------------- traqueur | traqueur_bloqueurs_process | table pa= rtitionn=E9e | postgres | non journalis=E9 | | 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=3D# alter table traqueur_bloqueurs_process_2023_12_21_2023_12_22 s= et logged; ALTER TABLE traqueur=3D# \d+ Lis= te des relations Sch=E9ma | Nom | = Type | Propri=E9taire | Persistence | M=E9thode d'acc=E8s | T= aille | Description ----------+-------------------------------------------------------+--------= ------------+--------------+----------------+-----------------+------------= +------------- traqueur | traqueur_bloqueurs_process | table pa= rtitionn=E9e | postgres | non journalis=E9 | | 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 par= titioned table on the hot standby database even if I can now query its part= ition: traqueur=3D*# table traqueur_bloqueurs_process; ERREUR: ne peut pas acc=E9der =E0 des tables temporaires et non trac=E9es = lors de la restauration traqueur=3D*# 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 ign= ored/refused at the creation of the partitioned table seems if it has no se= nse ? Seems a bit strange to apply it with =AB create table =BB and to igno= re it with =AB alter table =BB, it has consequences in the case I described= . I have no way to properly correct my initial mistake that was silently a= pplied. The idea is not to change anything on the underlying partitions, command wo= uld still do "nothing" about logging but it would allow the partitioned tab= le to be queried on the standby after the change. I guess it could be possi= ble 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 id= ea. Best regards, Phil --_000_DU2PR10MB77665E2EF27001AEA9C7ACEEBA8D2DU2PR10MB7766EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
Old mail but I see the issue is corrected in PostgreSQL 18 devel, many than= ks.
Best regards,
Phil
De : Phil Florent <philf= lorent@hotmail.com>
Envoy=E9 : jeudi 21 d=E9cembre 2023 17:00
=C0 : pgsql-general@postgresql.org <pgsql-general@postgresql.org&= gt;
Objet : Unlogged partitionned tables and hot standbys
 

<= span style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)">Hi,

 

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

 

Here is a very simple case :

 

traqueur=3D# select vers= ion();

        &nb= sp;            =             &nb= sp;            =         version

---------------------------------------------------= ----------------------------------------------------------------

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

(1 ligne)

 

Suppose I created my partionn= ed 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 th= e "unlogged" attribute.

 

  Sch=E9ma  |    &nbs= p;            &= nbsp;        Nom    =             &nb= sp;         |   &nbs= p;    Type        | Propr= i=E9taire |  Persistence   | M=E9thode d'acc=E8s | &nbs= p; Taille   | Description

----------+-------------= ------------------------------------------+--------------------+-----------= ---+----------------+-----------------+------------+-------------

traqueur | traqueur_bloq= ueurs_process          &n= bsp;            = ;     | table partitionn=E9e | postgres  &nbs= p;  | non journalis=E9 |       &nbs= p;         | 0 bytes  &nb= sp; |

traqueur | traqueur_bloqueurs_process_2023_12_21_20= 23_12_22      | table     = ;         | postgres  &nb= sp;  | non journalis=E9 | heap      &nbs= p;     | 16 kB      |

 

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

 

traqueur=3D*# table traq= ueur_bloqueurs_process;

ERREUR:  ne peut pa= s acc=E9der =E0 des tables temporaires et non trac=E9es lors de la restaura= tion

 

traqueur=3D*# table traq= ueur_bloqueurs_process_2023_12_21_2023_12_22;

ERREUR:  ne peut pa= s acc=E9der =E0 des tables temporaires et non trac=E9es lors de la restaura= tion

 

(seems error message is not 1= 00% consistent (in French only ?) with psql information "non trac=E9es= " vs "non journalis=E9" but OK)

 

Now I want to turn the loggin= g mode of those tables to "permanent" to query the perf informati= on from the standby. I do that on the primary cluster :

 

traqueur=3D# alter table= traqueur_bloqueurs_process set logged;

ALTER TABLE

traqueur=3D# \d+<= /p>

    =             &nb= sp;            =             &nb= sp;            =             &nb= sp;     Liste des relations

  Sch=E9ma  |&= nbsp;            &nb= sp;            = Nom            =             &nb= sp; |        Type    = ;    | Propri=E9taire |  Persistence   | M=E9= thode d'acc=E8s |   Taille   | Description

----------+-------------= ------------------------------------------+--------------------+-----------= ---+----------------+-----------------+------------+-------------

traqueur | traqueur_bloq= ueurs_process          &n= bsp;            = ;     | table partitionn=E9e | postgres  &nbs= p;  | non journalis=E9 |       &nbs= p;         | 0 bytes  &nb= sp; |

 

 

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=3D# alter table= traqueur_bloqueurs_process_2023_12_21_2023_12_22 set logged;

ALTER TABLE

traqueur=3D# \d+<= /p>

    =             &nb= sp;             = ;            &n= bsp;            = ;            &n= bsp;    Liste des relations

  Sch=E9ma  |&= nbsp;           &nbs= p;             = Nom            =             &nb= sp; |        Type    = ;    | Propri=E9taire |  Persistence   | M=E9= thode d'acc=E8s |   Taille   | Description

----------+-------------= ------------------------------------------+--------------------+-----------= ---+----------------+-----------------+------------+-------------

traqueur | traqueur_bloq= ueurs_process          &n= bsp;            = ;     | table partitionn=E9e | postgres  &nbs= p;  | non journalis=E9 |       &nbs= p;         | 0 bytes  &nb= sp; |

traqueur | traqueur_bloq= ueurs_process_2023_12_21_2023_12_22      | table&n= bsp;            = ; | postgres     | permanent    &nb= sp; | heap           = ; | 8192 bytes |

...

 

Situation is 100% OK on the p= rimary cluster but I still cannot query my partitioned table on the hot sta= ndby database even if I can now query its partition:

 

traqueur=3D*# table traq= ueur_bloqueurs_process;

ERREUR:  ne peut pa= s acc=E9der =E0 des tables temporaires et non trac=E9es lors de la restaura= tion

 

traqueur=3D*# table traq= ueur_bloqueurs_process_2023_12_21_2023_12_22;

...

(0 ligne)

 

It could be convenient that t= he "alter table XXXX set logged;" changes the mode even on partit= ioned tables or that the unlogged attribute could be ignored/refused at the creation of the partitioned table seems if it ha= s no sense ? Seems a bit strange to apply it with =AB create table&nbs= p;=BB and to ignore it with =AB alter table =BB, it has consequen= ces 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 any= thing on the underlying partitions, command would still do "nothing&qu= ot; about logging but it would allow the partitioned table to be queried on the standby after the change. I guess it could be p= ossible 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 goo= d idea.

 

Best regards,

Phil

--_000_DU2PR10MB77665E2EF27001AEA9C7ACEEBA8D2DU2PR10MB7766EURP_--