pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedTransaction support in 6.5.3/JDBC
13+ messages / 3 participants
[nested] [flat]
* Transaction support in 6.5.3/JDBC
@ 1999-12-08 01:07 Assaf Arkin <arkin@exoffice.com>
0 siblings, 0 replies; 13+ messages in thread
From: Assaf Arkin @ 1999-12-08 01:07 UTC (permalink / raw)
To: pgsql-interfaces@hub.org
For the past few weeks we've been developing an open source Java
transaction monitor. Our RDBMS of choice is PostgreSQL and we're using
Peter's JDBC driver, which works for us.
In order to use JDBC inside a transaction server it must support an
interface beyond the basic commit/rollback for dealing with distributed,
shared and suspended transactions. We're using the XAResource interface
which implements X/Open XA compatible distributed transaction
capabilities.
While implementing X/Open XA support on top of the JDBC driver, I ran
into a few issues with the back end. I'm using PostgreSQL 6.5.3 on
RedHat 6.0, Java 1.2 and hacked the JDBC driver to show FE-BE
communication.
My questions so far are:
1. When a dead-lock occurs trying to update the same row from multiple
threads, there does not seem to be any timeout and both connections hang
forever. Is there any dead-lock detection in 6.5 and will there be on in
6.6 or 7.0?
2. In the event of such a dead-lock, the XA layer will attempt to
terminate one of the connections. Right now the only recourse is to
shutdown the connection forcefully, hoping that the transaction is
rolledback and all locks are released. Am I safe to assume that?
3. I would rather cancel the pending update/insert, and according to the
interface specs there is a way to recieve a pid/key on startup and use
it to cancel an operation in progress. However, at the end of the
authentication process that happens at startup, no pid/key are send to
the FE, nor does the BE acknowledge that a query can be made.
4. The XA layer is responsible for beginning and commiting the
transactions directly and does so by sending the proper
begin/commit/rollback commands to the BE. It is possible that anywhere
between a begin and commit/rollback the transaction could be commited or
rolledback and another transaction started in its place not through a
commit/rollback SQL command?
5. Is it possible to determine the state of the transation prior to
issuing a commit operation and determine whether the transaction will
commit or rollback once a commit is issued?
arkin
--
____________________________________________________________
Assaf Arkin arkin@exoffice.com
CTO http://www.exoffice.com
Exoffice, The ExoLab Company tel: (650) 259-9796
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: [INTERFACES] Transaction support in 6.5.3/JDBC
@ 1999-12-08 07:26 Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-08 09:07 ` Re: [INTERFACES] Transaction support in 6.5.3/JDBC Tom Lane <tgl@sss.pgh.pa.us>
1999-12-08 19:41 ` Re: [INTERFACES] Transaction support in 6.5.3/JDBC Assaf Arkin <arkin@exoffice.com>
1999-12-09 18:14 ` Re: [INTERFACES] Transaction support in 6.5.3/JDBC Assaf Arkin <arkin@exoffice.com>
0 siblings, 3 replies; 13+ messages in thread
From: Peter Mount @ 1999-12-08 07:26 UTC (permalink / raw)
To: 'Assaf Arkin' <arkin@exoffice.com>; pgsql-interfaces@hub.org
Due to having to suffer MS Outlook here at work, my comments are
prefixed with PM :-)
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Assaf Arkin [mailto:arkin@exoffice.com]
Sent: Wednesday, December 08, 1999 1:08 AM
To: pgsql-interfaces@hub.org
Subject: [INTERFACES] Transaction support in 6.5.3/JDBC
For the past few weeks we've been developing an open source Java
transaction monitor. Our RDBMS of choice is PostgreSQL and we're using
Peter's JDBC driver, which works for us.
In order to use JDBC inside a transaction server it must support an
interface beyond the basic commit/rollback for dealing with distributed,
shared and suspended transactions. We're using the XAResource interface
which implements X/Open XA compatible distributed transaction
capabilities.
While implementing X/Open XA support on top of the JDBC driver, I ran
into a few issues with the back end. I'm using PostgreSQL 6.5.3 on
RedHat 6.0, Java 1.2 and hacked the JDBC driver to show FE-BE
communication.
My questions so far are:
1. When a dead-lock occurs trying to update the same row from multiple
threads, there does not seem to be any timeout and both connections hang
forever. Is there any dead-lock detection in 6.5 and will there be on in
6.6 or 7.0?
PM: I'm not sure if there is/will be in the backend. I'm planning on
making the OOB abort stuff working for 7.0, but I'm not certain on
dead-lock's.
2. In the event of such a dead-lock, the XA layer will attempt to
terminate one of the connections. Right now the only recourse is to
shutdown the connection forcefully, hoping that the transaction is
rolledback and all locks are released. Am I safe to assume that?
PM: Someone correct me if I'm wrong, but the transaction should roll
back if the connection is closed whilst it's open, regardless of the
interface.
3. I would rather cancel the pending update/insert, and according to the
interface specs there is a way to recieve a pid/key on startup and use
it to cancel an operation in progress. However, at the end of the
authentication process that happens at startup, no pid/key are send to
the FE, nor does the BE acknowledge that a query can be made.
PM: Do you mean the pid of the backend running a particular connection?
If so, I can see some security headaches if one connection can cancel an
operation running on another. As for the BE not acknowledging that a
query can be made, thats just how the current protocol works.
4. The XA layer is responsible for beginning and commiting the
transactions directly and does so by sending the proper
begin/commit/rollback commands to the BE. It is possible that anywhere
between a begin and commit/rollback the transaction could be commited or
rolledback and another transaction started in its place not through a
commit/rollback SQL command?
PM: JDBC based code should never issue begin/commit/rollback commands,
and should use the similarly named methods in Connection. This is
because a JDBC driver could be issuing these statements internally, and
it would be confused. With our driver, you could currently get away with
it, but it's not guaranteed to stay that way.
5. Is it possible to determine the state of the transation prior to
issuing a commit operation and determine whether the transaction will
commit or rollback once a commit is issued?
PM: Hmmm, in theory if a transaction is in a dead state (ie: an SQL
statement failed, so anything else is ignored until the rollback), there
should be a message in the notify queue. Our JDBC driver keeps these in
the warnings queue, so you could read them prior to calling commit()
yourself.
arkin
--
____________________________________________________________
Assaf Arkin arkin@exoffice.com
CTO http://www.exoffice.com
Exoffice, The ExoLab Company tel: (650) 259-9796
************
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [INTERFACES] Transaction support in 6.5.3/JDBC
1999-12-08 07:26 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-12-08 09:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 1999-12-08 09:07 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: 'Assaf Arkin' <arkin@exoffice.com>; pgsql-interfaces@hub.org
Peter Mount <petermount@it.maidstone.gov.uk> writes:
> 1. When a dead-lock occurs trying to update the same row from multiple
> threads, there does not seem to be any timeout and both connections hang
> forever. Is there any dead-lock detection in 6.5 and will there be on in
> 6.6 or 7.0?
> PM: I'm not sure if there is/will be in the backend. I'm planning on
> making the OOB abort stuff working for 7.0, but I'm not certain on
> dead-lock's.
This doesn't seem right to me --- the backend should detect deadlocks
(not right away, but within a few seconds or a minute at most). If
it doesn't, that's not JDBC's fault. More details please?
> 2. In the event of such a dead-lock, the XA layer will attempt to
> terminate one of the connections. Right now the only recourse is to
> shutdown the connection forcefully, hoping that the transaction is
> rolledback and all locks are released. Am I safe to assume that?
> PM: Someone correct me if I'm wrong, but the transaction should roll
> back if the connection is closed whilst it's open, regardless of the
> interface.
Right. Again, this'd be a backend bug if it didn't happen. The backend
isn't supposed to rely on correct frontend behavior to ensure database
integrity.
> 3. I would rather cancel the pending update/insert, and according to the
> interface specs there is a way to recieve a pid/key on startup and use
> it to cancel an operation in progress. However, at the end of the
> authentication process that happens at startup, no pid/key are send to
> the FE, nor does the BE acknowledge that a query can be made.
> PM: Do you mean the pid of the backend running a particular connection?
> If so, I can see some security headaches if one connection can cancel an
> operation running on another. As for the BE not acknowledging that a
> query can be made, thats just how the current protocol works.
This one maybe can be blamed on JDBC. In the 2.0 protocol the BE
will send a cancel security key --- but I seem to recall that the
JDBC client still requests protocol 1.0?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [INTERFACES] Transaction support in 6.5.3/JDBC
1999-12-08 07:26 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-12-08 19:41 ` Assaf Arkin <arkin@exoffice.com>
2 siblings, 0 replies; 13+ messages in thread
From: Assaf Arkin @ 1999-12-08 19:41 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces@hub.org
> PM: JDBC based code should never issue begin/commit/rollback commands,
> and should use the similarly named methods in Connection. This is
> because a JDBC driver could be issuing these statements internally, and
> it would be confused. With our driver, you could currently get away with
> it, but it's not guaranteed to stay that way.
Inside a transaction, the application should not even use
commit/rollback on the JDBC connection, only through the transaction
monitor API. This is easy to solve, I simply return a ClientConnection
wrapper that prevents that. But someone can still send a commit/rollback
statement directly through the JDBC driver.
What I'm more afraid of is some operation that will cause a
commit/rollback to occur, e.g. a failed update, a trigger or stored
procedure.
> PM: Hmmm, in theory if a transaction is in a dead state (ie: an SQL
> statement failed, so anything else is ignored until the rollback), there
> should be a message in the notify queue. Our JDBC driver keeps these in
> the warnings queue, so you could read them prior to calling commit()
> yourself.
Thanks I'll try to look that out.
I've minimized all the special requirements I need from the driver to
three methods calls:
1. enbleSQLTransactions -- prevents a commit/rollback from being
executed directly in SQL; you can never be too careful ;-)
2. prepare -- should return false if the transaction is read-only, true
if it will commit, throw an exception if it will rollback
3. isCriticalError -- should tell me if a critical error occured in the
connection and the connection is no longer useable
How do I detect no. 3? Is there are certain range of error codes, should
I just look at certain PSQLExceptions as being critial (e.g. all I/O
related errors)?
arkin
>
> arkin
>
> --
> ____________________________________________________________
> Assaf Arkin arkin@exoffice.com
> CTO http://www.exoffice.com
> Exoffice, The ExoLab Company tel: (650) 259-9796
>
> ************
>
> ************
--
____________________________________________________________
Assaf Arkin arkin@exoffice.com
CTO http://www.exoffice.com
Exoffice, The ExoLab Company tel: (650) 259-9796
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [INTERFACES] Transaction support in 6.5.3/JDBC
1999-12-08 07:26 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-12-09 18:14 ` Assaf Arkin <arkin@exoffice.com>
2 siblings, 0 replies; 13+ messages in thread
From: Assaf Arkin @ 1999-12-09 18:14 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces@hub.org
> PM: Hmmm, in theory if a transaction is in a dead state (ie: an SQL
> statement failed, so anything else is ignored until the rollback), there
> should be a message in the notify queue. Our JDBC driver keeps these in
> the warnings queue, so you could read them prior to calling commit()
> yourself.
From what I gather if an SQL statement failed, the CompletedResponse
will show *ABORT STATUS*. That's an easy modification. I'm not sure
about a warning being sent, I need to figure how to test this one.
The BE also returns the name of the cursor. I assume this one should be
returned from ResultSet.getCursorName()?
arkin
>
> arkin
>
> --
> ____________________________________________________________
> Assaf Arkin arkin@exoffice.com
> CTO http://www.exoffice.com
> Exoffice, The ExoLab Company tel: (650) 259-9796
>
> ************
>
> ************
--
____________________________________________________________
Assaf Arkin arkin@exoffice.com
CTO http://www.exoffice.com
Exoffice, The ExoLab Company tel: (650) 259-9796
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: [INTERFACES] Transaction support in 6.5.3/JDBC
@ 1999-12-08 10:08 Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-08 19:43 ` Re: [INTERFACES] Transaction support in 6.5.3/JDBC Assaf Arkin <arkin@exoffice.com>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Mount @ 1999-12-08 10:08 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: 'Assaf Arkin' <arkin@exoffice.com>; pgsql-interfaces@hub.org
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, December 08, 1999 9:07 AM
To: Peter Mount
Cc: 'Assaf Arkin'; pgsql-interfaces@hub.org
Subject: Re: [INTERFACES] Transaction support in 6.5.3/JDBC
Peter Mount <petermount@it.maidstone.gov.uk> writes:
> 1. When a dead-lock occurs trying to update the same row from multiple
> threads, there does not seem to be any timeout and both connections
hang
> forever. Is there any dead-lock detection in 6.5 and will there be on
in
> 6.6 or 7.0?
> PM: I'm not sure if there is/will be in the backend. I'm planning on
> making the OOB abort stuff working for 7.0, but I'm not certain on
> dead-lock's.
This doesn't seem right to me --- the backend should detect deadlocks
(not right away, but within a few seconds or a minute at most). If
it doesn't, that's not JDBC's fault. More details please?
PM: I didn't think it was.
> 2. In the event of such a dead-lock, the XA layer will attempt to
> terminate one of the connections. Right now the only recourse is to
> shutdown the connection forcefully, hoping that the transaction is
> rolledback and all locks are released. Am I safe to assume that?
> PM: Someone correct me if I'm wrong, but the transaction should roll
> back if the connection is closed whilst it's open, regardless of the
> interface.
Right. Again, this'd be a backend bug if it didn't happen. The backend
isn't supposed to rely on correct frontend behavior to ensure database
integrity.
PM: That's what I thought was the case.
> 3. I would rather cancel the pending update/insert, and according to
the
> interface specs there is a way to recieve a pid/key on startup and use
> it to cancel an operation in progress. However, at the end of the
> authentication process that happens at startup, no pid/key are send to
> the FE, nor does the BE acknowledge that a query can be made.
> PM: Do you mean the pid of the backend running a particular
connection?
> If so, I can see some security headaches if one connection can cancel
an
> operation running on another. As for the BE not acknowledging that a
> query can be made, thats just how the current protocol works.
This one maybe can be blamed on JDBC. In the 2.0 protocol the BE
will send a cancel security key --- but I seem to recall that the
JDBC client still requests protocol 1.0?
PM: In theory 6.5.3 should be requesting the current protocol (I
remember the patch being submitted to me as part of another fix).
However, I haven't (yet) had chance to look at it yet - hence not
knowing about the security key. The bit I want to add is for JDBC2,
ResultSet would by default use a cursor, and if it's closed while a read
is in effect, it would send cancel to the backend.
Peter
regards, tom lane
************
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [INTERFACES] Transaction support in 6.5.3/JDBC
1999-12-08 10:08 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-12-08 19:43 ` Assaf Arkin <arkin@exoffice.com>
1999-12-09 01:00 ` Re: [INTERFACES] Transaction support in 6.5.3/JDBC Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Assaf Arkin @ 1999-12-08 19:43 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>; pgsql-interfaces@hub.org
> Peter Mount <petermount@it.maidstone.gov.uk> writes:
> > 1. When a dead-lock occurs trying to update the same row from multiple
> > threads, there does not seem to be any timeout and both connections
> hang
> > forever. Is there any dead-lock detection in 6.5 and will there be on
> in
> > 6.6 or 7.0?
>
> > PM: I'm not sure if there is/will be in the backend. I'm planning on
> > making the OOB abort stuff working for 7.0, but I'm not certain on
> > dead-lock's.
>
> This doesn't seem right to me --- the backend should detect deadlocks
> (not right away, but within a few seconds or a minute at most). If
> it doesn't, that's not JDBC's fault. More details please?
>
> PM: I didn't think it was.
In my opinion the back-end should detect deadlocks, or just connections
that hang too long trying to get something done, and have them properly
terminated.
I have a very simple test. I start two threads, both of them attempt to
do an update on the exact same row in the table, both connections keep
hanging.
> Right. Again, this'd be a backend bug if it didn't happen. The backend
> isn't supposed to rely on correct frontend behavior to ensure database
> integrity.
>
> PM: That's what I thought was the case.
Good.
> This one maybe can be blamed on JDBC. In the 2.0 protocol the BE
> will send a cancel security key --- but I seem to recall that the
> JDBC client still requests protocol 1.0?
>
> PM: In theory 6.5.3 should be requesting the current protocol (I
> remember the patch being submitted to me as part of another fix).
> However, I haven't (yet) had chance to look at it yet - hence not
> knowing about the security key. The bit I want to add is for JDBC2,
> ResultSet would by default use a cursor, and if it's closed while a read
> is in effect, it would send cancel to the backend.
I'll try and see if I can get change the protocol and get the pid/key.
I'm only using the pid/key internally, so I don't see the possibility of
someone else interfering. It's not exposed to the code using the JDBC
driver. While the pid is known, the key is no known so one cannot
terminate an operation without creating the connection first. It's up to
the key to be reasonably unguessable.
arkin
>
> Peter
>
> regards, tom lane
>
> ************
>
> ************
--
____________________________________________________________
Assaf Arkin arkin@exoffice.com
CTO http://www.exoffice.com
Exoffice, The ExoLab Company tel: (650) 259-9796
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [INTERFACES] Transaction support in 6.5.3/JDBC
1999-12-08 10:08 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-08 19:43 ` Re: [INTERFACES] Transaction support in 6.5.3/JDBC Assaf Arkin <arkin@exoffice.com>
@ 1999-12-09 01:00 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 1999-12-09 01:00 UTC (permalink / raw)
To: Assaf Arkin <arkin@exoffice.com>; +Cc: Peter Mount <petermount@it.maidstone.gov.uk>; pgsql-interfaces@hub.org
Assaf Arkin <arkin@exoffice.com> writes:
> I have a very simple test. I start two threads, both of them attempt to
> do an update on the exact same row in the table, both connections keep
> hanging.
Hmm. The backend doesn't have any trouble detecting this; I checked
both current sources and 6.5.3:
play=> select * from z1;
f1
--
1
2
(2 rows)
play=> begin;
BEGIN
play=> update z1 set f1 = 3 where f1 = 1;
UPDATE 1
<now in a second psql do>
play=> begin;
BEGIN
play=> update z1 set f1 = 4 where f1 = 2;
UPDATE 1
play=> update z1 set f1 = 3 where f1 = 1;
<second psql is now waiting for first one to commit or abort>
<back in first psql do>
play=> update z1 set f1 = 4 where f1 = 2;
After a second or so, one psql will say
NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause.
ERROR: WaitOnLock: error on wakeup - Aborting this transaction
and the other will say
UPDATE 1
If you're not getting this behavior, then either your test code is wrong
or there's something broken in the JDBC driver...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: [INTERFACES] Transaction support in 6.5.3/JDBC
@ 1999-12-09 07:28 Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-09 18:42 ` Re: [INTERFACES] Transaction support in 6.5.3/JDBC Assaf Arkin <arkin@exoffice.com>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Mount @ 1999-12-09 07:28 UTC (permalink / raw)
To: 'Assaf Arkin' <arkin@exoffice.com>; Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces@hub.org, "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
[Hackers: Can anyone comment on my idea on how point 1 below could be
done, or not if thats the case? Thanks, Peter]
-----Original Message-----
From: Assaf Arkin [mailto:arkin@exoffice.com]
Sent: Wednesday, December 08, 1999 7:41 PM
To: Peter Mount
Cc: pgsql-interfaces@hub.org
Subject: Re: [INTERFACES] Transaction support in 6.5.3/JDBC
> PM: JDBC based code should never issue begin/commit/rollback commands,
> and should use the similarly named methods in Connection. This is
> because a JDBC driver could be issuing these statements internally,
and
> it would be confused. With our driver, you could currently get away
with
> it, but it's not guaranteed to stay that way.
Inside a transaction, the application should not even use
commit/rollback on the JDBC connection, only through the transaction
monitor API. This is easy to solve, I simply return a ClientConnection
wrapper that prevents that. But someone can still send a commit/rollback
statement directly through the JDBC driver.
What I'm more afraid of is some operation that will cause a
commit/rollback to occur, e.g. a failed update, a trigger or stored
procedure.
PM: This is tricky. Some JDBC drivers do parse the SQL before sending to
the backend, but we dont - mainly because its faster to let the
backend's parser do the job, and also it keeps our size down. The latter
affects applet users more than anything else.
PM: I suppose we could add a check for the simplest cases, ie sql
containing just "begin" "commit" "rollback" etc, but it won't catch all
possible cases.
> PM: Hmmm, in theory if a transaction is in a dead state (ie: an SQL
> statement failed, so anything else is ignored until the rollback),
there
> should be a message in the notify queue. Our JDBC driver keeps these
in
> the warnings queue, so you could read them prior to calling commit()
> yourself.
Thanks I'll try to look that out.
I've minimized all the special requirements I need from the driver to
three methods calls:
1. enbleSQLTransactions -- prevents a commit/rollback from being
executed directly in SQL; you can never be too careful ;-)
PM: I wonder if we can get this functionality in the backend's parser -
ie, for the API interfaces, they can set a variable on startup that
disables begin, commit and rollback, then when they need to use them, it
can then either temporarily clear the variable, or use a prefix that
forces the statement to work?
PM: enableSQLTransactions can then act immediately above this
functionality.
2. prepare -- should return false if the transaction is read-only, true
if it will commit, throw an exception if it will rollback
3. isCriticalError -- should tell me if a critical error occured in the
connection and the connection is no longer useable
How do I detect no. 3? Is there are certain range of error codes, should
I just look at certain PSQLExceptions as being critial (e.g. all I/O
related errors)?
PM: Don't rely on the text returned from PSQLException to be in English.
We are pretty unique in that the driver will return an error message in
the language defined by the locale of the client (also depends on if we
have translated the errors into that language). What I could to is add a
method to PSQLException that returns the original id of the Exception,
and another to return the arguments supplied. That may make your code
more portable.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [INTERFACES] Transaction support in 6.5.3/JDBC
1999-12-09 07:28 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-12-09 18:42 ` Assaf Arkin <arkin@exoffice.com>
0 siblings, 0 replies; 13+ messages in thread
From: Assaf Arkin @ 1999-12-09 18:42 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces@hub.org, "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
> PM: I wonder if we can get this functionality in the backend's parser -
> ie, for the API interfaces, they can set a variable on startup that
> disables begin, commit and rollback, then when they need to use them, it
> can then either temporarily clear the variable, or use a prefix that
> forces the statement to work?
>
> PM: enableSQLTransactions can then act immediately above this
> functionality.
That's what I was hoping for.
> 2. prepare -- should return false if the transaction is read-only, true
> if it will commit, throw an exception if it will rollback
Works fine now that I've added a check for *ABORT STATUS*.
> 3. isCriticalError -- should tell me if a critical error occured in the
> connection and the connection is no longer useable
>
> How do I detect no. 3? Is there are certain range of error codes, should
> I just look at certain PSQLExceptions as being critial (e.g. all I/O
> related errors)?
>
> PM: Don't rely on the text returned from PSQLException to be in English.
> We are pretty unique in that the driver will return an error message in
> the language defined by the locale of the client (also depends on if we
> have translated the errors into that language). What I could to is add a
> method to PSQLException that returns the original id of the Exception,
> and another to return the arguments supplied. That may make your code
> more portable.
I'm not looking into the messages, I know their language dependent. I
even added two or three new error messages, but only in English.
I'm looking for either specific error codes, range of error codes, or
some class extending PSQLException that will just indicate that this
connection is no longer useful. For example, if an I/O error occurs,
there's no ReadyForQuery reply, there's garbled response, etc.
arkin
>
> Peter
>
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough Council.
>
> ************
--
____________________________________________________________
Assaf Arkin arkin@exoffice.com
CTO http://www.exoffice.com
Exoffice, The ExoLab Company tel: (650) 259-9796
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: [INTERFACES] Transaction support in 6.5.3/JDBC
@ 1999-12-09 07:39 Peter Mount <petermount@it.maidstone.gov.uk>
0 siblings, 0 replies; 13+ messages in thread
From: Peter Mount @ 1999-12-09 07:39 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Assaf Arkin <arkin@exoffice.com>; +Cc: Peter Mount <petermount@it.maidstone.gov.uk>; pgsql-interfaces@hub.org
JDBC should break out of the deadlock in the same way as psql. I'll run
some tests, as it might be that the protected method ExecSQL() is
waiting for a normal response, and is ignoring the notify. That's the
only place I can think it is hanging.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, December 09, 1999 1:01 AM
To: Assaf Arkin
Cc: Peter Mount; pgsql-interfaces@hub.org
Subject: Re: [INTERFACES] Transaction support in 6.5.3/JDBC
Assaf Arkin <arkin@exoffice.com> writes:
> I have a very simple test. I start two threads, both of them attempt
to
> do an update on the exact same row in the table, both connections keep
> hanging.
Hmm. The backend doesn't have any trouble detecting this; I checked
both current sources and 6.5.3:
play=> select * from z1;
f1
--
1
2
(2 rows)
play=> begin;
BEGIN
play=> update z1 set f1 = 3 where f1 = 1;
UPDATE 1
<now in a second psql do>
play=> begin;
BEGIN
play=> update z1 set f1 = 4 where f1 = 2;
UPDATE 1
play=> update z1 set f1 = 3 where f1 = 1;
<second psql is now waiting for first one to commit or abort>
<back in first psql do>
play=> update z1 set f1 = 4 where f1 = 2;
After a second or so, one psql will say
NOTICE: Deadlock detected -- See the lock(l) manual page for a possible
cause.
ERROR: WaitOnLock: error on wakeup - Aborting this transaction
and the other will say
UPDATE 1
If you're not getting this behavior, then either your test code is wrong
or there's something broken in the JDBC driver...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: [INTERFACES] Transaction support in 6.5.3/JDBC
@ 1999-12-10 07:17 Peter Mount <petermount@it.maidstone.gov.uk>
0 siblings, 0 replies; 13+ messages in thread
From: Peter Mount @ 1999-12-10 07:17 UTC (permalink / raw)
To: 'Assaf Arkin' <arkin@exoffice.com>; Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces@hub.org
-----Original Message-----
From: Assaf Arkin [mailto:arkin@exoffice.com]
Sent: Thursday, December 09, 1999 6:14 PM
To: Peter Mount
Cc: pgsql-interfaces@hub.org
Subject: Re: [INTERFACES] Transaction support in 6.5.3/JDBC
> PM: Hmmm, in theory if a transaction is in a dead state (ie: an SQL
> statement failed, so anything else is ignored until the rollback),
there
> should be a message in the notify queue. Our JDBC driver keeps these
in
> the warnings queue, so you could read them prior to calling commit()
> yourself.
From what I gather if an SQL statement failed, the CompletedResponse
will show *ABORT STATUS*. That's an easy modification. I'm not sure
about a warning being sent, I need to figure how to test this one.
The BE also returns the name of the cursor. I assume this one should be
returned from ResultSet.getCursorName()?
PM: It should be, although currently it doesn't.
arkin
>
> arkin
>
> --
> ____________________________________________________________
> Assaf Arkin arkin@exoffice.com
> CTO http://www.exoffice.com
> Exoffice, The ExoLab Company tel: (650) 259-9796
>
> ************
>
> ************
--
____________________________________________________________
Assaf Arkin arkin@exoffice.com
CTO http://www.exoffice.com
Exoffice, The ExoLab Company tel: (650) 259-9796
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: [INTERFACES] Transaction support in 6.5.3/JDBC
@ 1999-12-10 07:27 Peter Mount <petermount@it.maidstone.gov.uk>
0 siblings, 0 replies; 13+ messages in thread
From: Peter Mount @ 1999-12-10 07:27 UTC (permalink / raw)
To: 'Assaf Arkin' <arkin@exoffice.com>; Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces@hub.org, "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgreSQL.org>
-----Original Message-----
From: Assaf Arkin [mailto:arkin@exoffice.com]
Sent: Thursday, December 09, 1999 6:43 PM
To: Peter Mount
Cc: pgsql-interfaces@hub.org; 'pgsql-hackers@postgresql.org'
Subject: Re: [INTERFACES] Transaction support in 6.5.3/JDBC
[snip]
> 3. isCriticalError -- should tell me if a critical error occured in
the
> connection and the connection is no longer useable
>
> How do I detect no. 3? Is there are certain range of error codes,
should
> I just look at certain PSQLExceptions as being critial (e.g. all I/O
> related errors)?
>
> PM: Don't rely on the text returned from PSQLException to be in
English.
> We are pretty unique in that the driver will return an error message
in
> the language defined by the locale of the client (also depends on if
we
> have translated the errors into that language). What I could to is add
a
> method to PSQLException that returns the original id of the Exception,
> and another to return the arguments supplied. That may make your code
> more portable.
I'm not looking into the messages, I know their language dependent. I
even added two or three new error messages, but only in English.
I'm looking for either specific error codes, range of error codes, or
some class extending PSQLException that will just indicate that this
connection is no longer useful. For example, if an I/O error occurs,
there's no ReadyForQuery reply, there's garbled response, etc.
arkin
PM: There are not error codes available. Also, there's nothing extending
PSQLException (yet), but there's no reason not to extend it.
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~1999-12-10 07:27 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-08 01:07 Transaction support in 6.5.3/JDBC Assaf Arkin <arkin@exoffice.com>
1999-12-08 07:26 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-08 09:07 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-08 19:41 ` Assaf Arkin <arkin@exoffice.com>
1999-12-09 18:14 ` Assaf Arkin <arkin@exoffice.com>
1999-12-08 10:08 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-08 19:43 ` Assaf Arkin <arkin@exoffice.com>
1999-12-09 01:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-09 07:28 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-09 18:42 ` Assaf Arkin <arkin@exoffice.com>
1999-12-09 07:39 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-10 07:17 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
1999-12-10 07:27 RE: [INTERFACES] Transaction support in 6.5.3/JDBC Peter Mount <petermount@it.maidstone.gov.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox