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
