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


Mon, 14 Jan 2002 22:35:29 -0800
Barry Lind <barry@xythos.com>
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

Tue, 15 Jan 2002 10:25:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problem with notify/listen 

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

Thu, 24 Jan 2002 20:52:38 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: problem with notify/listen
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:

> 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)
>>
>>
> 



Thu, 24 Jan 2002 18:38:54 -0800
Barry Lind <barry@xythos.com>
Re: problem with notify/listen