Re: Patch: Some more state codes

Kris Jurka <books(at)ejurka(dot)com> writes:

> On Wed, 7 Sep 2011, Johann 'Myrkraverk' Oskarsson wrote:
>
>> Here are some more state codes.
>
> But what's the gain? This isn't supposed to be a master list of
> every possible state code (and this patch doesn't try to do that
> either). What's the point of adding some new codes that no one is
> using?

Well, for application use imagine this scenario (from an actual
application of mine):

Pseudocode:

try {
CREATE TABLE t ...
} catch ( SQLException e ) {
if ( e.getSQLState().equals( PSQLState.DUPLICATE_TABLE ) )
; // do something or ignore when the table exists already
...
}

Similarly:

try {
INSERT INTO t ...
} catch ( SQLException e ) {
if ( e.getSQLState().equals( PSQLState.UNDEFINED_TABLE ) )
; // possibly call the code above, or handle otherwise
...
}

Those are not from the actual application, just examples.

Now, if you don't want applications to use PSQLState and rely on the
numerical values then there is not much need, no.

The UNDEFINED_OBJECT constant is a little trickier. I use it to patch
the driver for the Atomikos XA testsuite. I've already submitted that
patch but do understand your reluctance to accept it since its
validity is not immediately apparent. I'll quote Guy from Atomikos:

Scenario:

1-transaction manager starts transaction

2-application does some SQL and returns ok

3-application requests commit

4-transaction manager sends prepare to Postgresql

5-postgresql server fails before receiving prepare

6-transaction manager assumes the worst and attempts/retries rollback

7-postgresql comes back up, recovers without the (unprepared)
transaction

8-postgresql receives unknown xid from the transaction manager
retrying rollback

By convention, if the DBMS does not remember a transaction it must
have been rolled back (underpinning of XA). Rollback is idempotent
so there is no reason why step 8 should fail. It can be silently
ignored by the DBMS and the transaction outcome will still be along
the expected lines (rollback = no effects on data).

This means that when the XA driver rolls back and the error code is
UNDEFINED_OBJECT the error should be ignored.

--
Johann Oskarsson http://www.2ndquadrant.com/ |[]
PostgreSQL Development, 24x7 Support, Training and Services --+--
|
Blog: http://my.opera.com/myrkraverk/blog/