Thread: Timing of notice delivery

In 9.0.3 with postgresql-9.0-801.jdbc4.jar (if version matters), I'm
wondering whether a single connection which has a listener
registered, and within a transaction generates notifications, can
commit and immediately request notifications with assurance that all
notifications generated before the commit will be delivered on that
first getNotifications() invocation after the commit.

In other words, all on one thread, all on one connection:

-- setup
Execute LISTEN on the connection.
conn.setAutoCommit(false);
-- each transaction
execute statements which fire triggers which NOTIFY.
conn.commit();
conn.getNotifications();

Can I count on all notifications triggered during a transaction
being delivered in the last step?

If not, does running one dummy query, committing, and invoking
getNotifications() again ensure that I have them all?

If not, is there any way to determine when all notifications from
that transaction have been delivered?

Remember: all on one thread, all on one connection.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> In other words, all on one thread, all on one connection:

> -- setup
> Execute LISTEN on the connection.
> conn.setAutoCommit(false);
> -- each transaction
> execute statements which fire triggers which NOTIFY.
> conn.commit();
> conn.getNotifications();

> Can I count on all notifications triggered during a transaction
> being delivered in the last step?

The backend will deliver all such notifies after returning the COMMIT's
command-complete response message, but before it delivers ReadyForQuery.
If JDBC waits for ReadyForQuery before believing the operation is
complete, you should see them reliably.

I believe that the situation for incoming notifies from other sessions
is a bit less predictable, though --- it looks like those can get sent
*after* ReadyForQuery, even if they arrived beforehand.

            regards, tom lane

On 02/02/11 12:51, Kevin Grittner wrote:

> -- setup
> Execute LISTEN on the connection.
> conn.setAutoCommit(false);
> -- each transaction
> execute statements which fire triggers which NOTIFY.
> conn.commit();
> conn.getNotifications();
>
> Can I count on all notifications triggered during a transaction
> being delivered in the last step?

This is equivalent to asking "Does the server deliver all notifications
before sending ReadyForQuery in response to Sync after the commit
command?". The last thing the driver processes from the server before
returning from commit() is the RFQ message.

Oliver

Oliver Jowett  wrote:

> This is equivalent to asking "Does the server deliver all
> notifications before sending ReadyForQuery in response to Sync
> after the commit command?". The last thing the driver processes
> from the server before returning from commit() is the RFQ message.

Tom Lane  wrote:

> The backend will deliver all such notifies after returning the
> COMMIT's command-complete response message, but before it delivers
> ReadyForQuery.  If JDBC waits for ReadyForQuery before believing
> the operation is complete, you should see them reliably.

Good news!  Many thanks to both of you!

> I believe that the situation for incoming notifies from other
> sessions is a bit less predictable, though --- it looks like those
> can get sent *after* ReadyForQuery, even if they arrived
> beforehand.

Thanks, I'll keep that in mind.  The immediate issue was regarding
self-notification; knowing we can rely on that keep things simple for
us there.  I would expect inter-session messaging to be less
deterministic.  I'm sure we can make that work as long as we don't
make the wrong assumptions.

-Kevin

Kevin Grittner wrote:

> Thanks, I'll keep that in mind.  The immediate issue was regarding
> self-notification; knowing we can rely on that keep things simple for

When you mean "self-notification" I assume you mean notifications via
transactions from your connection?
If its an identification issue, you can always work out which notifications
are "self-notifications" via the PID, which is part of the notification
message. You can easily find your connection's PID immediately after
connecting (SELECT pg_backend_pid()).

Regards
Donald Fraser

"Donald Fraser" <postgres@kiwi-fraser.net> wrote:

> If its an identification issue, you can always work out which
> notifications are "self-notifications" via the PID

No, it's strictly a timing issue.  Can the JDBC client which has
just committed a transaction count on getNotifications returning all
relevant notifications from that transaction?  The answer is yes,
which is convenient for us.  The use case is that we will have
triggers firing at the PostgreSQL level, and we don't want the
application to have to be aware of what triggers are there or to
make any assumptions about what these triggers will do; but we want
the JDBC client to be notified about what they actually *did* -- so
that GUI components reflecting the data modified by the triggers can
be updated.

Of course, ultimately we would like to get to a point where all
users displaying relevant data are updated quickly and
automatically, but there are performance considerations to that
which haven't been worked out entirely.  Right now we just want to
make sure that the user making the base change gets immediate
feedback on all side-effects.  This has already been happening
through a Java tier just above the database, but we've decided to
commit to PostgreSQL rather than maintaining database independence,
which allows us to push some of this down to the database level.
We're just trying to work out how to maintain existing functionality
during the switchover, which we've decided to do incrementally, over
the course of a few years -- although this first step of converting
the triggers and stored procedures to PostgreSQL triggers and
functions needs to be done in a single application update before we
do much else.

Ultimately, this will save us from having to maintain a lot of
home-grown infrastructure.  We currently write all our triggers and
stored procedures in an ANSI-SQL-based format, parse that and
generate Java classes to implement the triggers and stored
procedures in that intermediate level.  Even if we need to patch
PostgreSQL here and there, the work should be pretty minimal
compared to what we do now.  Fortunately , it's been pretty
reasonable to create a tree-walker for the tree emitted from our
current parser which emits PostgreSQL functions and triggers.  :-)

But I digress...  Thanks for the suggestion, anyway.

-Kevin