My thought is to do something I usually hate doing (because usually 'sequences' or 'serial fields' do the job but not in this case). Use a control table which just stores the 'next' id number to be used and insert one row with the 'first' id number.

Then when you want to insert into your table, you'll first need to lock the control table (or just the record, I'm not sure if a table lock attempt will block or immediately fail in PostgreSQL, either way you could work it out) select the 'next' id number from the control table, update the control table, unlock the control table, delete from your table, then insert into your table. I'd probably do this all in a transaction so we can rollback if any of it fails.

I'd just do the 'delete' from your table every time, because soon you'll have your thousand records and it won't be worth checking to see if you 'should' delete.

In reply to Re: Cycle through primary key with DBI by runrig
in thread Cycle through primary key with DBI by lestrrat

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.