convert legacy self-managed primary keys to postgresql's identity or serial pk's?

From: john snow <ofbizfanster(at)gmail(dot)com>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: convert legacy self-managed primary keys to postgresql's identity or serial pk's?
Date: 2018-03-15 04:49:24
Message-ID: CAE67tvXgaeiY98ixtubE+7f4EUF-sY+8x0RwHEfoVCJ3uudw=w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2018-03-15 13:55:18 Re: convert legacy self-managed primary keys to postgresql's identity or serial pk's?
Previous Message Dale Schmitz 2018-03-12 18:32:46 Re: Error handling