Greetings all,

Update: sub-classing from Class::DBI::Pg, as suggested, doesn't solve the issue - the only way that I'm going to fix this is by using before_create and after_create 'triggers'. I'll try that now.

The reason for this behaviour appears to be because Class::DBI isn't that comfortable with Views.

I'm creating a database using Class::DBI and postgres. The postgres primary keys are of postgres datatype 'SERIAL', which is a sequence. On each 'INSERT' postgres auto-increments the sequence and inserts the new number as a primary key. So far so easy.

However, Class::DBI wants to take control of this process. If a column is declared as a primary key and the sequence is declared as a sequence e.g.:

__PACKAGE__->sequence('identity_identity_id_seq');

Then Class::DBI wants to get the next val itself. This has the effect of calling postgres's nextval('identity_identity_id_seq') function. This increments the sequence returning the next value. Class::DBI stores this value (call it 'n').

Class::DBI then asks postgres to insert the row. Postgres increments the sequence again to get the new unique id. The row is inserted with primary key n+1.

Class::DBI returns what it thinks is the new primary key, n, and tries to repopulate its datastructure from the db. Of course it can't, beause there is no row with a primary key 'n'.

I need to stop this behaviour. Ideally I'd like Class::DBI not to try to increment the sequence - and then to return the new primary key id, by doing something like calling postgres's currval function and repopulating its datastructure with that value

One way is to not declare a sequence and to use Class::DBI's after_create and before_create triggers to force the behaviour I want. However, this seems messy to me.

Is there a better, easy more logical way of getting a consistent behaviour?

TIA