public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Dominique Devienne <[email protected]>
To: [email protected]
Subject: Re: Unexpected Backend PID reported by Notification
Date: Tue, 11 Jun 2024 08:21:07 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-8cwTFBeTS2_7n97e5C42i-pTVw5o+vyqnwxesQFLbp_A@mail.gmail.com>
References: <CAFCRh-8cwTFBeTS2_7n97e5C42i-pTVw5o+vyqnwxesQFLbp_A@mail.gmail.com>

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
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Unexpected Backend PID reported by Notification
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox