Re: Indexes that would span partitions.
> How many partitions do you expect to have? Why are you partitioning on that > column? Do you have need to drop old data all at the same time? How many rows > in each partition or in total do you expect to have? In my case there is a hierarchy so the partitions are based on certain parts of the hierarchy. For example. There is an item 1.2.3.4.5.6.7.8 The partition is based on 1.2 (a function based hash scheme). I could fine tune the number of partitions simply by writing my function. Each item has certain attributes that I want to index. For example all items have an expiry_date. I will be archiving the items when they expire but of course all those items will not be in the same partition. In fact it's highly probable all partitions will have to be visited in order to accomplish this task. Unfortunately I will not be able to drop an entire partition and I can't partition by expiry date because access is most often by the item number. Also of course the expiry date can change over the lifetime of the item.
Re: Restoring using PG_DUMP in PG12 results in
Thanks Adrian, Can we say that, "Despite an informational Error, entire data got imported with sanity in the original case"? Also, can we say that either of the approaches mentioned i.e. Approach 1 & 2 are equally good to do migration from PG 10 to PG 12? Thanks & Regards Pranjal Shukla On 3/14/22, 8:25 PM, "Adrian Klaver" wrote: On 3/14/22 06:39, Shukla, Pranjal wrote: > Hello, > > We tried importing into an empty database in PG 12 from the dump that > was created in PG 10. Import was successful but we got an message that > an error was ignored. We agin imported with -e option and the following > message was printed: > > pg_restore: while PROCESSING TOC: > > pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres > > pg_restore: error: could not execute query: ERROR:schema "public" > already exists Basically an informational error that tells you that the restore was trying to create an object that already exists, in this case the pubic schema. Not really an issue. > > Command was: CREATE SCHEMA public; > > There are some blogs that ar pointing to that fact the error is because > of different versions of PG Admin Utility namely different versions of > pg_dump() and pg_resotre() command in source and destination versions. > We use the following command for pg_dump and pg_restore. > > 1. Have you faced this scenario? > 2. Off the approaches mentioned above, which one do you think we should > opt for while doing migration from PG 10 to 12 (in different machines)? Honestly I could not follow what you did. Best practice when moving from older version to newer version is to use the newer version's pg_dump against the older version and then use the newer versions pg_restore or psql(for text dumps) to restore. > > PS: Below is the command we use to create database: > > /CREATE DATABASE mydb WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' > TEMPLATE template0;/ > > *Thanks & Regards* > > *Pranjal Shukla* > -- Adrian Klaver adrian.kla...@aklaver.com
Re[2]: LISTEN/NOTIFY ultra slow speed
i looked at the file "\src\backend\commands\async.c"
there is a check for duplicate messages
/ PG 10
if (AsyncExistsPendingNotify(channel, payload))
return;
***/
/ PG 13
if (AsyncExistsPendingNotify(n))
{
…...
}
***/
Is this slowdown related to this check?
Is it possible to skip the check if I'm absolutely sure the message is unique?
--
Arsen Arutyunyan
>Вторник, 15 марта 2022, 10:34 +03:00 от Арсен Арутюнян :
>
>
>1)
>select version();
> version
>
> PostgreSQL 10.6, compiled by Visual C++ build 1800, 64-bit
>
>2) $PGDATA/pg_notify/
>Sometimes this folder is empty, sometimes it contains the file I have
>attached. The file is always less than 200kb
>
>
>--
>Arsen Arutyunyan
>
>
>>Понедельник, 14 марта 2022, 15:53 +03:00 от Арсен Арутюнян < aru...@bk.ru >:
>>
>> Hello! I'm having trouble with LISTEN/NOTIFY speed. Now I have about
>>100-200 (few?) clients who are actively messaging. But recently I started to
>>notice a drop in speed. Changing the log_min_duration_statement parameter did
>>not show slow queries in the log.
>> As it turned out, the pg_notify(text ,text) function
>> or the
>> NOTIFY text, 'text' queries takes an extremely long time
>>(0.7-1.5sec),
>> but the select pg_notification_queue_usage() query always returns 0.
>>Please tell me what's the matter, where did I make a mistake?
>>What needs to be done to increase the speed of adding to the message queue?
>>
>>--
>>Arsen Arutyunyan
>|What PG version is this exactly? What is in $PGDATA/pg_notify/ ?
>| regards, tom lane
>
Re: Postgres Crash Issue
On Tue, Mar 15, 2022 at 07:20:57AM +, Sankar, Uma (Uma) wrote: > Hi All, > > We have a database crash issue last Friday and it's a patroni HA-based > Postgres database running, we have checked the pg logs and it shows > error as shared memory corruptions. Can someone please check the > attached logs and share your suggestions and feedback on the crash > issues. Thanks in advance. It wasn't crash. Logs show: #v+ 2022-03-11 08:58:42.956 UTC [27799] LOG: received immediate shutdown request 2022-03-11 08:58:42.956 UTC [17115] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.956 UTC [17115] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [14618] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [14618] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [14618] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [16396] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [16396] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [16396] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [12873] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [12873] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [12873] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.958 UTC [16564] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.958 UTC [16564] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.958 UTC [16564] HINT: In a moment you should be able to reconnect to the database and repeat your command. #v- and #v+ 2022-03-11 08:58:45.969 UTC [17354] LOG: consistent recovery state reached at A/E1836620 2022-03-11 08:58:45.969 UTC [17347] LOG: database system is ready to accept read only connections 2022-03-11 08:59:01.234 UTC [17347] LOG: received fast shutdown request 2022-03-11 08:59:01.234 UTC [17347] LOG: aborting any active transactions 2022-03-11 08:59:01.234 UTC [17412] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.234 UTC [17415] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.234 UTC [17393] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.235 UTC [17399] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.235 UTC [17408] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.236 UTC [17394] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.236 UTC [17389] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.237 UTC [17400] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.237 UTC [17378] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.238 UTC [17362] LOG: shutting down 2022-03-11 08:59:01.254 UTC [17347] LOG: database system is shut down 2022-03-11 08:59:01.419 UTC [17444] LOG: database system was shut down in recovery at 2022-03-11 08:59:01 UTC 2022-03-11 08:59:01.419 UTC [17444] LOG: entering standby mode 2022-03-11 08:59:01.420 UTC [17444] LOG: redo starts at A/E17CB690 #v- Something/someone stopped the pg, twice, ~ 20 seconds apart. What/how/why - can't tell, but "received ... shutdown request" is not crash. It is someone/something doing "pg_ctl stop" depesz
Re: [External]Re: Postgres Crash Issue
On Tue, Mar 15, 2022 at 01:30:25PM +, Menon, Deepak (Deepak) wrote: > Hi Depesz, > > Then what does this message mean ? > > 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded > this server process to roll back the current transaction and exit, because > another server process exited abnormally and possibly corrupted shared > memory. When someone issues pg_ctl -m immediate stop (which is the immediate shutdown). pg basically shutd backends in their head. This, in turn, means that all other have to exit to avoid shared memory corruption. Sometimes such exit happens before pg_ctl will kill next backend, sometimes not. So you sometimes can get such messages. depesz
Re: [External]Re: Postgres Crash Issue
On Tue, Mar 15, 2022 at 01:38:04PM +, Menon, Deepak (Deepak) wrote: > Thanks Depesz. Is there anyway to check the source of the command as this HA > setup is managed by using Patroni >From pg perspective it got immediate stop request. There is no way to tell why/how. depesz
Re: [External]Re: Postgres Crash Issue
hubert depesz lubaczewski writes: > On Tue, Mar 15, 2022 at 01:30:25PM +, Menon, Deepak (Deepak) wrote: >> Then what does this message mean ? >> >> 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded >> this server process to roll back the current transaction and exit, because >> another server process exited abnormally and possibly corrupted shared >> memory. > When someone issues pg_ctl -m immediate stop (which is the immediate > shutdown). pg basically shutd backends in their head. This, in turn, > means that all other have to exit to avoid shared memory corruption. FWIW, v14 and later produce more on-point error reporting in this case. The code path used to be the same as for a backend crash, but now clients see something like WARNING: terminating connection due to immediate shutdown command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and the server log isn't spammed with copies of that, it just looks like 2022-03-15 10:07:48.241 EDT [315456] LOG: received immediate shutdown request 2022-03-15 10:07:48.244 EDT [315456] LOG: database system is shut down None of this gets us any closer to understanding where the signal came from, unfortunately. regards, tom lane
Re: Re[2]: LISTEN/NOTIFY ultra slow speed
=?UTF-8?B?0JDRgNGB0LXQvSDQkNGA0YPRgtGO0L3Rj9C9?= writes: > i looked at the file "\src\backend\commands\async.c" > there is a check for duplicate messages Yup. > Is this slowdown related to this check? [ shrug... ] You've provided no evidence either for or against that possibility. It seems a little unlikely that you could have a lot of pending messages without also seeing data accumulating in pg_notify/, but on the other hand it's also hard to think of other places that time could be getting spent if the queue is always empty. Can you use perf(1) or some such tool to try to identify where the time is being spent? There's some advice about that here: https://wiki.postgresql.org/wiki/Profiling_with_perf > Is it possible to skip the check if I'm absolutely sure the message is unique? Not a good idea. You could upgrade to PG 13 or later, where that code has been improved. regards, tom lane
Re: Restoring using PG_DUMP in PG12 results in
On 3/14/22 23:42, Shukla, Pranjal wrote: Thanks Adrian, Can we say that, "Despite an informational Error, entire data got imported with sanity in the original case"? Yes. To verify see that the public schema is there and has tables and other objects in it. Also, can we say that either of the approaches mentioned i.e. Approach 1 & 2 are equally good to do migration from PG 10 to PG 12? As I said before I could not understand what you did. The best approach in your case is to: 1) Use the Postgres 12 version of pg_dump to dump the Postgres 10 server. 2) Use the Postgres 12 version of pg_restore to restore it to the Postgres 12 server. Thanks & Regards Pranjal Shukla On 3/14/22, 8:25 PM, "Adrian Klaver" wrote: On 3/14/22 06:39, Shukla, Pranjal wrote: > Hello, > > We tried importing into an empty database in PG 12 from the dump that > was created in PG 10. Import was successful but we got an message that > an error was ignored. We agin imported with -e option and the following > message was printed: > > pg_restore: while PROCESSING TOC: > > pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres > > pg_restore: error: could not execute query: ERROR:schema "public" > already exists Basically an informational error that tells you that the restore was trying to create an object that already exists, in this case the pubic schema. Not really an issue. > > Command was: CREATE SCHEMA public; > > There are some blogs that ar pointing to that fact the error is because > of different versions of PG Admin Utility namely different versions of > pg_dump() and pg_resotre() command in source and destination versions. > We use the following command for pg_dump and pg_restore. > > 1. Have you faced this scenario? > 2. Off the approaches mentioned above, which one do you think we should > opt for while doing migration from PG 10 to 12 (in different machines)? Honestly I could not follow what you did. Best practice when moving from older version to newer version is to use the newer version's pg_dump against the older version and then use the newer versions pg_restore or psql(for text dumps) to restore. > > PS: Below is the command we use to create database: > > /CREATE DATABASE mydb WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' > TEMPLATE template0;/ > > *Thanks & Regards* > > *Pranjal Shukla* > -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Corrupt Index
Hi All, has anyone experienced corrupted unique indexes? During a Patroni leader switchover, we had a situation where a unique index stopped "working", the index did not have records that were in fact in the table and the table had duplicate records which should have been prevented by said unique index. Fixing the duplicate records and reindexing seems to have fixed the problems, but I would like to be aware of it next time. Is there a way to test for index corruption? Anyone else had this issue before? Thanks -Jorel
Re: Corrupt Index
Jorel Casal writes: > During a Patroni leader switchover, we had a situation where a unique index > stopped "working", the index did not have records > that were in fact in the table and the table had duplicate records which > should have been prevented by said unique index. If the index was on a string type (text/varchar), the most likely explanation is a switch to a different OS version with inconsistent collation rules. See https://wiki.postgresql.org/wiki/Locale_data_changes regards, tom lane
RE: [External]Re: Postgres Crash Issue
Hi Depesz, Then what does this message mean ? 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. Deepak Menon| Avaya Managed Services-Delivery|+91 9899012875| men...@avaya.com Leave Alert : -Original Message- From: dep...@depesz.com Sent: Tuesday, March 15, 2022 6:58 PM To: Sankar, Uma (Uma) **CTR** Cc: pgsql-gene...@postgresql.org; Menon, Deepak (Deepak) Subject: [External]Re: Postgres Crash Issue [External Sender] On Tue, Mar 15, 2022 at 07:20:57AM +, Sankar, Uma (Uma) wrote: > Hi All, > > We have a database crash issue last Friday and it's a patroni HA-based > Postgres database running, we have checked the pg logs and it shows > error as shared memory corruptions. Can someone please check the > attached logs and share your suggestions and feedback on the crash > issues. Thanks in advance. It wasn't crash. Logs show: #v+ 2022-03-11 08:58:42.956 UTC [27799] LOG: received immediate shutdown request 2022-03-11 08:58:42.956 UTC [17115] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.956 UTC [17115] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [14618] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [14618] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [14618] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [16396] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [16396] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [16396] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.957 UTC [12873] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.957 UTC [12873] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.957 UTC [12873] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2022-03-11 08:58:42.958 UTC [16564] WARNING: terminating connection because of crash of another server process 2022-03-11 08:58:42.958 UTC [16564] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2022-03-11 08:58:42.958 UTC [16564] HINT: In a moment you should be able to reconnect to the database and repeat your command. #v- and #v+ 2022-03-11 08:58:45.969 UTC [17354] LOG: consistent recovery state reached at A/E1836620 2022-03-11 08:58:45.969 UTC [17347] LOG: database system is ready to accept read only connections 2022-03-11 08:59:01.234 UTC [17347] LOG: received fast shutdown request 2022-03-11 08:59:01.234 UTC [17347] LOG: aborting any active transactions 2022-03-11 08:59:01.234 UTC [17412] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.234 UTC [17415] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.234 UTC [17393] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.235 UTC [17399] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.235 UTC [17408] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.236 UTC [17394] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.236 UTC [17389] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.237 UTC [17400] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.237 UTC [17378] FATAL: terminating connection due to administrator command 2022-03-11 08:59:01.238 UTC [17362] LOG: shutting down 2022-03-11 08:59:01.254 UTC [17347] LOG: database system is shut down 2022-03-11 08:59:01.419 UTC [17444] LOG: database system was shut down in recovery at 2022-03-11 08:59:01 UTC 2022-03-11 08:59:01.419 UTC [17444] LOG: enterin
RE: [External]Re: Postgres Crash Issue
Thanks Depesz. Is there anyway to check the source of the command as this HA setup is managed by using Patroni Deepak Menon| Avaya Managed Services-Delivery|+91 9899012875| men...@avaya.com Leave Alert : -Original Message- From: dep...@depesz.com Sent: Tuesday, March 15, 2022 7:02 PM To: Menon, Deepak (Deepak) Cc: Sankar, Uma (Uma) **CTR** ; pgsql-gene...@postgresql.org Subject: Re: [External]Re: Postgres Crash Issue On Tue, Mar 15, 2022 at 01:30:25PM +, Menon, Deepak (Deepak) wrote: > Hi Depesz, > > Then what does this message mean ? > > 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded > this server process to roll back the current transaction and exit, because > another server process exited abnormally and possibly corrupted shared > memory. When someone issues pg_ctl -m immediate stop (which is the immediate shutdown). pg basically shutd backends in their head. This, in turn, means that all other have to exit to avoid shared memory corruption. Sometimes such exit happens before pg_ctl will kill next backend, sometimes not. So you sometimes can get such messages. depesz
Re: [External]Re: Postgres Crash Issue
On 3/15/22 6:30 AM, Menon, Deepak (Deepak) wrote: Hi Depesz, Then what does this message mean ? From here, assuming someone/thing did pg_ctl -m immediate stop: https://www.postgresql.org/docs/current/app-pg-ctl.html "“Immediate” mode will abort all server processes immediately, without a clean shutdown. This choice will lead to a crash-recovery cycle during the next server start." 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. Deepak Menon| Avaya Managed Services-Delivery|+91 9899012875| men...@avaya.com Leave Alert : -- Adrian Klaver adrian.kla...@aklaver.com
Re[4]: LISTEN/NOTIFY ultra slow speed
I decided to rebuild pg 10 first.
I commented out the lines of code with duplicate checking
(/src/backend/commands/async.c)
// if (AsyncExistsPendingNotify(channel, payload))
// return;
and launched this version of pg into production.
This was the only way I could figure out if there was a significant difference
in speed. And the results turned out to be very interesting, the query speed
increased by about two times. But unfortunately it's still far from the
desired level
before the code changes
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 974,119 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 859,855 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 695,741 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 77,001 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 475,590 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 973,449 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 1039,825 мс (00:01,040)
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 1233,576 мс (00:01,234)
after the code changes
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 431,337 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 460,794 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 498,042 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 604,931 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 438,763 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 587,827 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 621,079 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 549,264 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 447,218 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 526,980 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48119697"}');
Время: 417,556 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 185,374 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 554,529 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 654,769 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 577,187 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 246,831 мс
rmdatabase=# select
pg_notify('rmdb_client_message','{"Command":239,"MsgId":"48117898"}');
Время: 648,865 мс
tomorrow I will deal with the perf
--
Arsen Arutyunyan
>Вторник, 15 марта 2022, 17:32 +03:00 от Tom Lane :
>
>=?UTF-8?B?0JDRgNGB0LXQvSDQkNGA0YPRgtGO0L3Rj9C9?= < aru...@bk.ru > writes:
>> i looked at the file "\src\backend\commands\async.c"
>> there is a check for duplicate messages
>
>Yup.
>
>> Is this slowdown related to this check?
>
>[ shrug... ] You've provided no evidence either for or against that
>possibility.
>
>It seems a little unlikely that you could have a lot of pending messages
>without also seeing data accumulating in pg_notify/, but on the other hand
>it's also hard to think of other places that time could be getting spent
>if the queue is always empty. Can you use perf(1) or some such tool to
>try to identify where the time is being spent? There's some advice
>about that here:
>
>https://wiki.postgresql.org/wiki/Profiling_with_perf
>
>> Is it possible to skip the check if I'm absolutely sure the message is
>> unique?
>
>Not a good idea. You could upgrade to PG 13 or later, where that
>code has been improved.
>
>regards, tom lane
