Обсуждение: Multiple NOTIFY is ignored

Поиск
Список
Период
Сортировка

Multiple NOTIFY is ignored

От
Markus Kolb
Дата:
Hi,

I've a question to the pgsql NOTIFY feature...

I use something like

SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT
dblink_disconnect('{4}');
where {3} is
NOTIFY upd_pgm, '0|UPDATE|A|'

SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT
dblink_disconnect('{4}');
where {3} is
NOTIFY upd_pgm, '0|DELETE|A|'

This works. Both notify are recognized.

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.

For further explanation... the working solution uses separate dblink
connections and calls for each notify.
Where as the not working tries to use only one dblink connection and
call.

As far as I can read from the documentation...
"If the same channel name is signaled multiple times from the same
transaction with identical payload strings, the database server can
decide to deliver a single notification only. On the other hand,
notifications with distinct payload strings will always be delivered as
distinct notifications."
(https://www.postgresql.org/docs/current/static/sql-notify.html)

Here the channel name is identical, but the payload differs with
UPDATE/DELETE.

So why the 2nd notify is not delivered/recognized?

Thanks
Markus

Re: Multiple NOTIFY is ignored

От
Adrian Klaver
Дата:
On 07/28/2016 07:29 AM, Markus Kolb wrote:
> Hi,
>
> I've a question to the pgsql NOTIFY feature...
>
> I use something like
>
> SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT
> dblink_disconnect('{4}');
> where {3} is
> NOTIFY upd_pgm, '0|UPDATE|A|'
>
> SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT
> dblink_disconnect('{4}');
> where {3} is
> NOTIFY upd_pgm, '0|DELETE|A|'
>
> This works. Both notify are recognized.
>
> 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.
>
> For further explanation... the working solution uses separate dblink
> connections and calls for each notify.
> Where as the not working tries to use only one dblink connection and call.
>
> As far as I can read from the documentation...
> "If the same channel name is signaled multiple times from the same
> transaction with identical payload strings, the database server can
> decide to deliver a single notification only. On the other hand,
> notifications with distinct payload strings will always be delivered as
> distinct notifications."
> (https://www.postgresql.org/docs/current/static/sql-notify.html)
>
> Here the channel name is identical, but the payload differs with
> UPDATE/DELETE.
>
> So why the 2nd notify is not delivered/recognized?

At a guess the second NOTIFY is not completed by ;

>
> Thanks
> Markus
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Multiple NOTIFY is ignored

От
Markus Kolb
Дата:
Am 28.07.2016 16:42, schrieb Adrian Klaver:
> On 07/28/2016 07:29 AM, Markus Kolb wrote:

[...]
>> So why the 2nd notify is not delivered/recognized?
>
> At a guess the second NOTIFY is not completed by ;

It is no problem of syntax.
If this would be the case I would get an error/exception.
There is no exception.

I think dblink knows how to handle the missing ; at the end.
You need to use ; only between multiple commands.

Re: Multiple NOTIFY is ignored

От
"David G. Johnston"
Дата:
On Thu, Jul 28, 2016 at 10:29 AM, Markus Kolb <markus.kolb+postgres@tower-net.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.
​"​


​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.

Re: Multiple NOTIFY is ignored

От
Igor Neyman
Дата:

 

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

 

On Thu, Jul 28, 2016 at 10:29 AM, Markus Kolb <markus.kolb+postgres@tower-net.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.

​"​

 

 

​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

Re: Multiple NOTIFY is ignored

От
Tom Lane
Дата:
Markus Kolb <markus.kolb+postgres@tower-net.de> writes:
> 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.

I can't duplicate this.

contrib_regression=# SELECT dblink_connect('myconn',connection_parameters());
 dblink_connect
----------------
 OK
(1 row)

contrib_regression=# select dblink('myconn', $$notify foo,'0|UPDATE|A|'; notify foo,'0|DELETE|A|'$$);
  dblink
----------
 (NOTIFY)
(1 row)

Over in another session that's done "LISTEN foo":

contrib_regression=# select 1;
 ?column?
----------
        1
(1 row)

Asynchronous notification "foo" with payload "0|UPDATE|A|" received from server process with PID 19442.
Asynchronous notification "foo" with payload "0|DELETE|A|" received from server process with PID 19442.

            regards, tom lane

Re: Multiple NOTIFY is ignored

От
Adrian Klaver
Дата:
On 07/28/2016 07:50 AM, Markus Kolb wrote:
> Am 28.07.2016 16:42, schrieb Adrian Klaver:
>> On 07/28/2016 07:29 AM, Markus Kolb wrote:
>
> [...]
>>> So why the 2nd notify is not delivered/recognized?
>>
>> At a guess the second NOTIFY is not completed by ;
>
> It is no problem of syntax.
> If this would be the case I would get an error/exception.
> There is no exception.
>
> I think dblink knows how to handle the missing ; at the end.
> You need to use ; only between multiple commands.

Well to me it looks like it is seeing the first NOTIFY, but not the
second. Given Tom's post, you might want to double check the commands
you are supplying to {3} are really what you think they are.

>


--
Adrian Klaver
adrian.klaver@aklaver.com