Thread: Patch: Some more state codes

Hi all,

Here are some more state codes.

They are really independent of any other patches/applications and can
be submitted without fear of breaking anything.


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

On Wed, 7 Sep 2011, Johann 'Myrkraverk' Oskarsson wrote:

> Here are some more state codes.
>
> They are really independent of any other patches/applications and can
> be submitted without fear of breaking anything.
>

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?

Kris Jurka

Kris Jurka <books@ejurka.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/

On Wed, 7 Sep 2011, Johann 'Myrkraverk' Oskarsson wrote:

> Well, for application use imagine this scenario (from an actual
> application of mine):
>
>  try {
>      CREATE TABLE t ...
>  } catch ( SQLException e ) {
>      if ( e.getSQLState().equals( PSQLState.DUPLICATE_TABLE ) )
>          ; // do something or ignore when the table exists already
>      ...
>  }
>
>
> Now, if you don't want applications to use PSQLState and rely on the
> numerical values then there is not much need, no.
>

I guess I was not expecting users trying to write portable JDBC code to
write code that referred to PG specific classes.  Do other people do this?

> 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've got to admit that I haven't really looked into that one, but if you
are patching the driver to fix that, then there's no reason that patch
couldn't touch PSQLState as well.

Kris Jurka

Kris Jurka wrote:
>
>
> On Wed, 7 Sep 2011, Johann 'Myrkraverk' Oskarsson wrote:
>
>> Well, for application use imagine this scenario (from an actual
>> application of mine):
>>
>>   try {
>>       CREATE TABLE t ...
>>   } catch ( SQLException e ) {
>>       if ( e.getSQLState().equals( PSQLState.DUPLICATE_TABLE ) )
>>           ; // do something or ignore when the table exists already
>>       ...
>>   }
>>
>>
>> Now, if you don't want applications to use PSQLState and rely on the
>> numerical values then there is not much need, no.
>>
>
> I guess I was not expecting users trying to write portable JDBC code to
> write code that referred to PG specific classes.  Do other people do this?
Absolutely not!

>
>> 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've got to admit that I haven't really looked into that one, but if you
> are patching the driver to fix that, then there's no reason that patch
> couldn't touch PSQLState as well.
>
> Kris Jurka
>

> I guess I was not expecting users trying to write portable JDBC code to
> write code that referred to PG specific classes.  Do other people do this?

Well, not if you're striving for portable code, but for what it's
worth, there are frequently good reasons to ignore portability
completely and just use the JDBC driver as a way to access PostgreSQL
from Java. In that case, having access to as many PostgreSQL-specific
features / options / error states as possible can be very handy.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

FWIW - I'm inclined to agree with Maciek. If you are not worried about portability why not use the best available features of Postgres? From Java, the obvious way would be through JDBC.

I imagine there are a lot of people (rightly or wrongly is too philosophical) writing code to specific databases.

Bryan.