Re: Multiple NOTIFY is ignored

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of David G. Johnston
Sent: Thursday, July 28, 2016 11:05 AM
To: Markus Kolb <markus(dot)kolb+postgres(at)tower-net(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Multiple NOTIFY is ignored

On Thu, Jul 28, 2016 at 10:29 AM, Markus Kolb <markus(dot)kolb+postgres(at)tower-net(dot)de<mailto:markus(dot)kolb+postgres(at)tower-net(dot)de>> wrote:
If I only do one
SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT dblink_disconnect('{4}');
where {3} is
NOTIFY upd_pgm, '0|UPDATE|A|'; NOTIFY upd_pgm, '0|DELETE|A|'

only the notify with UPDATE is recognized.

​"​
dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.
​"​

https://www.postgresql.org/docs/9.3/static/contrib-dblink-function.html

​As defined in the dblink docs your query isn't supported - its doesn't return rows. I don't know the interaction that makes the separate executions succeed by I'd advise simply bypassing the issue and doing:

WHERE {3} is
SELECT pg_notify('{2}','{UPDATE NOTIFY}), pg_notify('{3}','{DELETE NOTIFY}');

Not tested and I've never used NOTIFY but the select makes it return rows.

I'm not positive how execution order plays out here, you may want to try "VALUES()" instead of multiple columns.

David J.

Well,

“dblink_exec executes a command (that is, any SQL statement that doesn't return rows) in a remote database.”

Regards,
Igor