Обсуждение: Unexpected Backend PID reported by Notification

Hi. I have a unit test using a single connection, that simulates a
client interacting with a server via a PostgreSQL "queue", i.e. a
non-writable table with SECURITY DEFINER procedures to mediate writes
to that table, with those PROC-initiated updates triggering
pg_notify() messages (via an UPDATE trigger).

The test is passing, I get all the side-effects and notifications I
expect. BUT...
For some reason, the backend_pid reported on the notification object
itself (i.e. PGnotify::be_pid),
is different from the one reported for the (sole) connection the unit
test is using (PQbackendPID()).

How can that be?
Are Stored PROCs running in a different backend?
Are Triggers running in a different backend?

Any doc pointers to explain this behavior?

Thanks. --DD

PS: v14 server on RedHat; v16 libpq on Windows
PPS: Below's a snippet of my test code, which shows actual PID values:

    auto perreq_notif = c.notification();
    BOOST_REQUIRE(perreq_notif);
    BOOST_CHECK_EQUAL(perreq_notif.channel(), req.channel());
    /*
    ** In fact I get perreq_notif.backend_pid() == N + c.backend_pid() !!!
    ** Is the fact the pg_notify() is done from a trigger the reason???
    ** e.g. [4053957 != 4053955]
    BOOST_CHECK_EQUAL(perreq_notif.backend_pid(), c.backend_pid());
    */
    BOOST_CHECK_EQUAL(perreq_notif.payload(), "...");

On 6/11/24 08:05, Dominique Devienne wrote:
> Hi. I have a unit test using a single connection, that simulates a
> client interacting with a server via a PostgreSQL "queue", i.e. a
> non-writable table with SECURITY DEFINER procedures to mediate writes
> to that table, with those PROC-initiated updates triggering
> pg_notify() messages (via an UPDATE trigger).
> 
> The test is passing, I get all the side-effects and notifications I
> expect. BUT...
> For some reason, the backend_pid reported on the notification object
> itself (i.e. PGnotify::be_pid),
> is different from the one reported for the (sole) connection the unit
> test is using (PQbackendPID()).
> 
> How can that be?
> Are Stored PROCs running in a different backend?
> Are Triggers running in a different backend?
> 
> Any doc pointers to explain this behavior?

https://www.postgresql.org/docs/current/sql-notify.html

"It is common for a client that executes NOTIFY to be listening on the 
same notification channel itself. In that case it will get back a 
notification event, just like all the other listening sessions. 
Depending on the application logic, this could result in useless work, 
for example, reading a database table to find the same updates that that 
session just wrote out. It is possible to avoid such extra work by 
noticing whether the notifying session's server process PID (supplied in 
the notification event message) is the same as one's own session's PID 
(available from libpq). When they are the same, the notification event 
is one's own work bouncing back, and can be ignored."

Looks to me like are seeing the correct thing, a client session that is 
different from the server process.

> 
> Thanks. --DD
> 
> PS: v14 server on RedHat; v16 libpq on Windows
> PPS: Below's a snippet of my test code, which shows actual PID values:
> 
>      auto perreq_notif = c.notification();
>      BOOST_REQUIRE(perreq_notif);
>      BOOST_CHECK_EQUAL(perreq_notif.channel(), req.channel());
>      /*
>      ** In fact I get perreq_notif.backend_pid() == N + c.backend_pid() !!!
>      ** Is the fact the pg_notify() is done from a trigger the reason???
>      ** e.g. [4053957 != 4053955]
>      BOOST_CHECK_EQUAL(perreq_notif.backend_pid(), c.backend_pid());
>      */
>      BOOST_CHECK_EQUAL(perreq_notif.payload(), "...");
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Are Stored PROCs running in a different backend?
Are Triggers running in a different backend?

On Tue, Jun 11, 2024 at 5:29 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tuesday, June 11, 2024, Dominique Devienne <ddevienne@gmail.com> wrote:
>> Are Stored PROCs running in a different backend?
>> Are Triggers running in a different backend?
> No to both.  Whatever backend the SQL to invoke those was sent on is the backend that executes them.

Thanks for confirming David (and Adrian). I indeed had two connections
after all. User error. Sorry for the noise. --DD