problem with notify/listen

Barry Lind <barry@xythos.com> writes:

If I run jdbc with setAutoCommit=true then notifications work as
expected, however if I run with setAutoCommit=false then notifications
are not being sent from the backend to the client.

You are aware that notifications are only sent at transaction commit,
right?

The only thing that is unusual about this is that the
"commit;begin;" command string is sent to the server as a single query
to be processed.

Hm. The response to this string (assuming that there's a notify
waiting) would look like

N foo (NOTIFY)
C COMMIT (commit command complete)
C BEGIN (begin command complete)
Z (ready for new query)

(that's from memory, so forgive any minor inaccuracy). I speculate that
either JDBC or your test code is dropping the NOTIFY in this situation.

regards, tom lane

I assume this item is still open. I put it in the 7.3 queue:

http://candle.pha.pa.us/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Barry Lind wrote:

I have just been looking into a bug report against the jdbc driver and I
found the following behavior that I can't explain.

If I run jdbc with setAutoCommit=true then notifications work as
expected, however if I run with setAutoCommit=false then notifications
are not being sent from the backend to the client.

With autoCommit=false the jdbc driver is sending the following SQL
commands to the server:

begin
select 1
commit;begin;
select 1
commit;begin;
select 1
...

My test code is using the select statement as a means to poll for the
notifications. The only thing that is unusual about this is that the
"commit;begin;" command string is sent to the server as a single query
to be processed.

With autoCommit=true the SQL commands are simply:

select 1
select 1
select 1
...

In this mode the notifications work correctly.

Is there something I am doing wrong, or is this expected behavior for
the server?

thanks,
--Barry

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Yes this is still an open item. I have already created a patch to jdbc
for the problem and am waiting for a 7.3 tree to apply it, as I am
uncomfortable with the changes going into 7.2 at this late date.

thanks,
--Barry

Bruce Momjian wrote: