Feedback-ID: i76984098:Fastmail
Message-ID: <55bb1667-4972-4704-9548-45afeab45aa5@aklaver.com>
Date: Tue, 11 Jun 2024 08:21:07 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Unexpected Backend PID reported by Notification
To: Dominique Devienne <ddevienne@gmail.com>,
 pgsql-general@lists.postgresql.org
References: <CAFCRh-8cwTFBeTS2_7n97e5C42i-pTVw5o+vyqnwxesQFLbp_A@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAFCRh-8cwTFBeTS2_7n97e5C42i-pTVw5o+vyqnwxesQFLbp_A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/55bb1667-4972-4704-9548-45afeab45aa5%40aklaver.com>
Precedence: bulk

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