Re: Enhydra: DODS/PostgreSQL and the serial datatype

[Nathaniel Reed <nate@xxxxxxxxxxxxxxxxxxxxx>]

Let me see if I understand this.  Serial is a sequential key.  You mentioned 
that
the database should handle the work of maintaining the sequential (serial) 
keys.

>From your description, I infer that the database has a trigger that handles 
>the
sequences (this is just like some of our legacy tables).  The problem is that
your DO becomes inconsistent with the value on the database when you insert it
b/c the trigger fires and changes the sequence value.  So, the in-memory 
(cached)
DO contains a key of 0 even though the database row has a key of 1.  It looks 
to
the database as though you are attempting to insert a row with the same key 
(0)
as the row you just inserted.

I have also encountered this situation and received the following suggestion 
on
this mailing list which should solve your problem:  Modify the appropriate DO
template file so that the DO is read back from the database immediately after
commit().   Whenever a DO is inserted, the trigger will set the serial value, 
and
the DO will be read back into memory with the new serial key.

Nate

Nathan wrote:

> I'm writing an app that requires a database field to be incremented so
> that the record can be identified by the unique field.  The problem is
> that DODS doesn't understand what the PostgreSQL datatype 'serial' is
> and instead of leaving it alone it tries to insert a 0 into the id
> field.  The first insert works because 'id' is set to 0 but on
> subsequent inserts the id field is not incremented (left at 0) and I get
> this message:
>
>         java.sql.SQLException: Failed to insert/update DO: ERROR:
> Cannot insert a duplicate key into unique index article_id_key
>
> Has anyone else run into this problem?  How can I make the DataObject
> leave the field alone so that the DB can do its thing?  If I use
> straight JDBC it works like a charm but I really want to stick with the
> abstraction that a DataObject gives me.  It has already been a real life
> saver.
>
> I know I can handle  incrementing the field in a PO but I don't want to
> because I figure that letting the database handle the work would be more
> efficient.  If I'm wrong please let me know.
>
> TIA,
>
> nathan
>
> -----------------------------------------------------------------------------
> To unsubscribe from this mailing list, send email to majordomo@xxxxxxxxxxx
> with the text "unsubscribe enhydra" in the body of the email.
> If you have other questions regarding this mailing list, send email to
> the list admin at owner-enhydra@xxxxxxxxxxxx

-----------------------------------------------------------------------------
To unsubscribe from this mailing list, send email to majordomo@xxxxxxxxxxx
with the text "unsubscribe enhydra" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at owner-enhydra@xxxxxxxxxxxx