Обсуждение: problem with notify/listen
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
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, Pennsylvania19026
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) >> >> >