RE: BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
Just to help those who find themselves in the same situation, there is a simple
application-level workaround which consists in listening to the notifications
in the replica when they are issued by the master and vice versa.
We are programming in .NET and we use a code like this:
Code in the replica side:
var csb = new NpgsqlConnectionStringBuilder
{
Host = "master",
Database = "MasterDB",
Port = 5432,
Username = "postgres",
Password = "XXX",
};
var connection = new NpgsqlConnection(csb.ConnectionString);
connection.Open();
using (var command = new NpgsqlCommand("listen \"Table\"",
connection))
{
command.ExecuteNonQuery();
}
connection.Notification += PostgresNotification;
So you can listen from the replica every changed raised by a trigger on the
master from the replica side on the table "Table".
CREATE TRIGGER master_trigger
AFTER INSERT OR DELETE OR UPDATE
ON public."TABLE"
FOR EACH ROW
EXECUTE PROCEDURE public.master_notice();
ALTER TABLE public."Tabele"
ENABLE ALWAYS TRIGGER master_trigger;
CREATE FUNCTION public. master_notice ()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
PERFORM pg_notify('Table', Cast(NEW."ID" as text));
RETURN NEW;
END;
$BODY$;
ALTER FUNCTION public.incupdate1_notice()
OWNER TO postgres;
I hope that help someone, because the bug last from "years". I tried in version
10 11 and 12, so it is present since 2017-10-05 and I can't see any solution on
13 beta.
Best Regards.
Fabio.
-Original Message-
From: Vianello Fabio
Sent: lunedì 8 giugno 2020 11:14
To: Kyotaro Horiguchi
Cc: pgsql-b...@lists.postgresql.org; pgsql-hackers@lists.postgresql.org
Subject: RE: BUG #16481: Stored Procedure Triggered by Logical Replication is
Unable to use Notification Events
Hi Kyotaro Horiguchi, thanks for you helps.
We have a question about the bug. Why there isn't any solution in the HEAD?
This bug last since 10.4 version and I can't understand why it is not fixed in
the HEAD yet.
BR.
Fabio Vianello.
-Original Message-
From: Kyotaro Horiguchi [mailto:horikyota@gmail.com]
Sent: lunedì 8 giugno 2020 10:28
To: Vianello Fabio ;
pgsql-b...@lists.postgresql.org; pgsql-hackers@lists.postgresql.org
Subject: Re: BUG #16481: Stored Procedure Triggered by Logical Replication is
Unable to use Notification Events
Hello.
It seems to me a bug.
At Fri, 05 Jun 2020 11:05:14 +, PG Bug reporting form
wrote in
> The following bug has been logged on the website:
>
> Bug reference: 16481
> Logged by: Fabio Vianello
> Email address: fabio.viane...@salvagninigroup.com
> PostgreSQL version: 12.3
> Operating system: Windows 10
> Description:
>
> About the bug BUG #15293, on PostgreSQL version 10.4 and 11.2 as
> describe below, we found the same issue on the PostgreSQL version 12.3.
The HEAD behaves the same way.
> Is it a feature?
> Becasue in the documentation we didn't found any constraint that says
> that we can not use NOTIFY/LISTEN on logical replication tables.
>
> "When using logical replication a stored procedure executed on the
> replica is unable to use NOTIFY to send messages to other listeners.
> The stored procedure does execute as expected but the pg_notify()
> doesn't appear to have any effect. If an insert is run on the replica
> side the trigger executes the stored procedure as expected and the
> NOTIFY correctly notifies listeners.
The message is actually queued, but logical replication worker doesn't signal
that to listener backends. If any ordinary session sent a message to the same
listener after that, both messages would be shown at once.
That can be fixed by calling ProcessCompletedNotifies() in apply_handle_commit.
The function has a code to write out notifications to connected clients but it
doesn't nothing on logical replication workers.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
SALVAGNINI ITALIA S.p.A.
Via Guido Salvagnini, 51 - IT - 36040 Sarego (VI)
T. +39 0444 725111 | F. +39 0444 43 6404
Società a socio unico - Attività direz. e coord.: Salvagnini Holding S.p.A.
Clicca qui<https://www.salvagninigroup.com/company-information> per le
informazioni societarie
salvagninigroup.com<https://www.salvagninigroup.com> |
salvagnini.it<http://www.salvagnini.it>
Le informazioni trasmesse sono destinate esclusivamente alla persona o alla
società in indirizzo e sono da intendersi confidenziali e riservate. Ogni
trasmissione, inoltro, diffusione o altro uso di q
RE: BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
I think you did follow all the thread. I only ask if it is a bug or not. If it as bug and last for years I understand your point our view but I have my. If you think that signal a bug is not give a contribution I am astonished. Improve PosgreSQL is the target Best Regard. Fabio Vianello. From: David G. Johnston [mailto:david.g.johns...@gmail.com] Sent: martedì 9 giugno 2020 17:10 To: Vianello Fabio Cc: PostgreSQL Hackers ; pgsql-b...@lists.postgresql.org; Kyotaro Horiguchi ; Euler Taveira Subject: Re: BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events On Tue, Jun 9, 2020 at 12:36 AM Vianello Fabio mailto:fabio.viane...@salvagninigroup.com>> wrote: Is PostgreSQL a serious product? For me the answer is "NO". A product with a bug that last for years and the community knows. It is not serious. If you are trying to be a troll just go away, we don't need that here. If you are just venting consider that this project is no more or less likely to have oversights, incomplete documentation, or a myriad of other human induced issues than any other. Reading the linked bug report the conclusion was "won't fix - at least not right now". Sure, it probably should have been documented but wasn't. It happens. And given the lack of complaints in the intervening years the decision, to not devote volunteer resources to a marginal feature, seems like the right one. Your active recourse at this point is to either convince a volunteer hacker to take up the cause - which your attitude doesn't help - or pay someone to do it. Or figure out a personal work-around to live with the current reality. Given that there is ongoing discussion as a result of your report (i.e., the report has merit regardless of how it was reported) means you should either meaningfully contribute to the discussion or shut up until they are done - at which point you are back to making a decision. Prompting politely for attention if the thread seems to languish without a clear resolution is, IMO, acceptable. David J. SALVAGNINI ITALIA S.p.A. Via Guido Salvagnini, 51 - IT - 36040 Sarego (VI) T. +39 0444 725111 | F. +39 0444 43 6404 Società a socio unico - Attività direz. e coord.: Salvagnini Holding S.p.A. Clicca qui<https://www.salvagninigroup.com/company-information> per le informazioni societarie salvagninigroup.com<https://www.salvagninigroup.com> | salvagnini.it<http://www.salvagnini.it> Le informazioni trasmesse sono destinate esclusivamente alla persona o alla società in indirizzo e sono da intendersi confidenziali e riservate. Ogni trasmissione, inoltro, diffusione o altro uso di queste informazioni a persone o società differenti dal destinatario è proibita. Se avete ricevuto questa comunicazione per errore, per favore contattate il mittente e cancellate le informazioni da ogni computer. Questa casella di posta elettronica è riservata esclusivamente all’invio ed alla ricezione di messaggi aziendali inerenti all’attività lavorativa e non è previsto né autorizzato l’utilizzo per fini personali. Pertanto i messaggi in uscita e quelli di risposta in entrata verranno trattati quali messaggi aziendali e soggetti alla ordinaria gestione disposta con proprio disciplinare dall’azienda e, di conseguenza, eventualmente anche alla lettura da parte di persone diverse dall’intestatario della casella. Any information herein transmitted only concerns the person or the company named in the address and is deemed to be confidential. It is strictly forbidden to transmit, post, forward or otherwise use said information to anyone other than the recipient. If you have received this message by mistake, please contact the sender and delete any relevant information from your computer. This mailbox is only meant for sending and receiving messages pertaining business matters and any other use for personal purposes is forbidden and unauthorized. Therefore, any email sent and received will be handled as ordinary business messages and subject to the company's own rules, and may thus be read also by people other than the user named in the mailbox address.
RE: BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
Is PostgreSQL a serious product? For me the answer is "NO". A product with a bug that last for years and the community knows. It is not serious. BR, Fabio. From: Euler Taveira [mailto:euler.tave...@2ndquadrant.com] Sent: lunedì 8 giugno 2020 12:51 To: Kyotaro Horiguchi Cc: Vianello Fabio ; pgsql-b...@lists.postgresql.org; PostgreSQL Hackers Subject: Re: BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events On Mon, 8 Jun 2020 at 05:27, Kyotaro Horiguchi mailto:horikyota@gmail.com>> wrote: That can be fixed by calling ProcessCompletedNotifies() in apply_handle_commit. The function has a code to write out notifications to connected clients but it doesn't nothing on logical replication workers. This bug was already reported some time ago (#15293) but it slipped through the cracks. I don't think you should simply call ProcessCompletedNotifies [1]. [1] https://www.postgresql.org/message-id/13844.1532468610%40sss.pgh.pa.us -- Euler Taveira http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services SALVAGNINI ITALIA S.p.A. Via Guido Salvagnini, 51 - IT - 36040 Sarego (VI) T. +39 0444 725111 | F. +39 0444 43 6404 Società a socio unico - Attività direz. e coord.: Salvagnini Holding S.p.A. Clicca qui<https://www.salvagninigroup.com/company-information> per le informazioni societarie salvagninigroup.com<https://www.salvagninigroup.com> | salvagnini.it<http://www.salvagnini.it> Le informazioni trasmesse sono destinate esclusivamente alla persona o alla società in indirizzo e sono da intendersi confidenziali e riservate. Ogni trasmissione, inoltro, diffusione o altro uso di queste informazioni a persone o società differenti dal destinatario è proibita. Se avete ricevuto questa comunicazione per errore, per favore contattate il mittente e cancellate le informazioni da ogni computer. Questa casella di posta elettronica è riservata esclusivamente all’invio ed alla ricezione di messaggi aziendali inerenti all’attività lavorativa e non è previsto né autorizzato l’utilizzo per fini personali. Pertanto i messaggi in uscita e quelli di risposta in entrata verranno trattati quali messaggi aziendali e soggetti alla ordinaria gestione disposta con proprio disciplinare dall’azienda e, di conseguenza, eventualmente anche alla lettura da parte di persone diverse dall’intestatario della casella. Any information herein transmitted only concerns the person or the company named in the address and is deemed to be confidential. It is strictly forbidden to transmit, post, forward or otherwise use said information to anyone other than the recipient. If you have received this message by mistake, please contact the sender and delete any relevant information from your computer. This mailbox is only meant for sending and receiving messages pertaining business matters and any other use for personal purposes is forbidden and unauthorized. Therefore, any email sent and received will be handled as ordinary business messages and subject to the company's own rules, and may thus be read also by people other than the user named in the mailbox address.
RE: BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
Hi Kyotaro Horiguchi, thanks for you helps. We have a question about the bug. Why there isn't any solution in the HEAD? This bug last since 10.4 version and I can't understand why it is not fixed in the HEAD yet. BR. Fabio Vianello. -Original Message- From: Kyotaro Horiguchi [mailto:horikyota@gmail.com] Sent: lunedì 8 giugno 2020 10:28 To: Vianello Fabio ; pgsql-b...@lists.postgresql.org; pgsql-hackers@lists.postgresql.org Subject: Re: BUG #16481: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events Hello. It seems to me a bug. At Fri, 05 Jun 2020 11:05:14 +, PG Bug reporting form wrote in > The following bug has been logged on the website: > > Bug reference: 16481 > Logged by: Fabio Vianello > Email address: fabio.viane...@salvagninigroup.com > PostgreSQL version: 12.3 > Operating system: Windows 10 > Description: > > About the bug BUG #15293, on PostgreSQL version 10.4 and 11.2 as > describe below, we found the same issue on the PostgreSQL version 12.3. The HEAD behaves the same way. > Is it a feature? > Becasue in the documentation we didn't found any constraint that says > that we can not use NOTIFY/LISTEN on logical replication tables. > > "When using logical replication a stored procedure executed on the > replica is unable to use NOTIFY to send messages to other listeners. > The stored procedure does execute as expected but the pg_notify() > doesn't appear to have any effect. If an insert is run on the replica > side the trigger executes the stored procedure as expected and the > NOTIFY correctly notifies listeners. The message is actually queued, but logical replication worker doesn't signal that to listener backends. If any ordinary session sent a message to the same listener after that, both messages would be shown at once. That can be fixed by calling ProcessCompletedNotifies() in apply_handle_commit. The function has a code to write out notifications to connected clients but it doesn't nothing on logical replication workers. regards. -- Kyotaro Horiguchi NTT Open Source Software Center SALVAGNINI ITALIA S.p.A. Via Guido Salvagnini, 51 - IT - 36040 Sarego (VI) T. +39 0444 725111 | F. +39 0444 43 6404 Società a socio unico - Attività direz. e coord.: Salvagnini Holding S.p.A. Clicca qui<https://www.salvagninigroup.com/company-information> per le informazioni societarie salvagninigroup.com<https://www.salvagninigroup.com> | salvagnini.it<http://www.salvagnini.it> Le informazioni trasmesse sono destinate esclusivamente alla persona o alla società in indirizzo e sono da intendersi confidenziali e riservate. Ogni trasmissione, inoltro, diffusione o altro uso di queste informazioni a persone o società differenti dal destinatario è proibita. Se avete ricevuto questa comunicazione per errore, per favore contattate il mittente e cancellate le informazioni da ogni computer. Questa casella di posta elettronica è riservata esclusivamente all’invio ed alla ricezione di messaggi aziendali inerenti all’attività lavorativa e non è previsto né autorizzato l’utilizzo per fini personali. Pertanto i messaggi in uscita e quelli di risposta in entrata verranno trattati quali messaggi aziendali e soggetti alla ordinaria gestione disposta con proprio disciplinare dall’azienda e, di conseguenza, eventualmente anche alla lettura da parte di persone diverse dall’intestatario della casella. Any information herein transmitted only concerns the person or the company named in the address and is deemed to be confidential. It is strictly forbidden to transmit, post, forward or otherwise use said information to anyone other than the recipient. If you have received this message by mistake, please contact the sender and delete any relevant information from your computer. This mailbox is only meant for sending and receiving messages pertaining business matters and any other use for personal purposes is forbidden and unauthorized. Therefore, any email sent and received will be handled as ordinary business messages and subject to the company's own rules, and may thus be read also by people other than the user named in the mailbox address.
