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

In reply to Odd Class::DBI sequence behaviour by Nomad

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.