Thread: convert legacy self-managed primary keys to postgresql's identity orserial pk's?
convert legacy self-managed primary keys to postgresql's identity orserial pk's?
From
john snow
Date:
we have a legacy xbase application / database where many of our "tables" (dbf files actually) have self-managed counters or "primary keys" (values in the sequence are managed by our own application code).
say the last value for such a "primary key" was 100 and the column name for the primary key is job_id. we have a counters table where we can find the row for job_id, then its last value.
when we need the next job_id value , we lock either just the job_id row or the entire counters table, depending on what the xbase product allows us to do (we have two different xbase products).
my question is: are there gotchas we need to be aware of if we try to self-manage such counters or sequences in postgresql? or should we just take advantage of postgresql-managed identity / serial id columns?
even if you're recommending the latter, i would still appreciate knowing the potential gotchas or perhaps proper way(s) of self-managing sequence values.
i am not the original developer of the xbase apps, so i am just doing some due diligence.
thanks so much for any help or guidance
Re: convert legacy self-managed primary keys to postgresql's identity or serial pk's?
From
Tom Lane
Date:
john snow <ofbizfanster@gmail.com> writes:
> we have a legacy xbase application / database where many of our "tables"
> (dbf files actually) have self-managed counters or "primary keys" (values
> in the sequence are managed by our own application code).
> say the last value for such a "primary key" was 100 and the column name for
> the primary key is job_id. we have a counters table where we can find the
> row for job_id, then its last value.
> when we need the next job_id value , we lock either just the job_id row or
> the entire counters table, depending on what the xbase product allows us to
> do (we have two different xbase products).
> my question is: are there gotchas we need to be aware of if we try to
> self-manage such counters or sequences in postgresql? or should we just
> take advantage of postgresql-managed identity / serial id columns?
One thing to watch out for is that sequence nextval() is not
transactional. In the scheme you've got, you do not (I imagine) get any
unused "holes" in the series of job_id values: if a transaction fails
between incrementing the counters row and committing, the increment rolls
back along with the insertion of the new job row, and the next transaction
will assign that same id. With sequences, the same situation would result
in that id value going permanently unused, because nextval() doesn't roll
back. Some people feel that that's a showstopper, eg because they have
audit checks that every id appears once.
Even discounting the possibility of rollback, it'd be possible in the
sequences world for the transaction inserting job_id N+1 to commit and
become visible to the rest of the app before the transaction inserting
job_id N does. That could also look like an inconsistency, depending on
how much your app is assuming about id values.
I also wonder if there are cases where locking the whole counters table
is actually semantically important to you, rather than an unfortunate
side-effect. It's hard to see why that might be, but I'm still
caffeine-deprived this morning.
Sequences would almost certainly be speedier and allow for more
concurrency than what you're doing. You just have to look at how much
you're assuming about the behavior of the id values.
regards, tom lane