Help with pre-loaded arbitrary key sequences

Поиск
Список
Период
Сортировка
От James B. Byrne
Тема Help with pre-loaded arbitrary key sequences
Дата
Msg-id 32935.216.185.71.22.1200069834.squirrel@webmail.harte-lyne.ca
обсуждение исходный текст
Ответы Re: Help with pre-loaded arbitrary key sequences  (Martijn van Oosterhout <kleptog@svana.org>)
Re: Help with pre-loaded arbitrary key sequences  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Список pgsql-general
I am prototyping a system migration that is to employ Ruby, Rails and
PostgreSQL.  Rails has the convention that the primary key of a row is an
arbitrary integer value assigned by the database manager through a
sequence.  As it turns out, the legacy application employs essentially the
same convention in most instances.

My question is this: Can one assign an id number to a sequenced key column
on create and override the sequencer?  If one does this then can and, if
so, how does the sequencer in Postgresql handle the eventuality of running
into a block of keys holding previously assigned numbers?

For example.  The existing client master dataset employs an eight digit
account number as primary key.  The values in use tend to cluster in
groups at each thousand increment, thus 1..375, 1001..1288, 2001..2225,
3001..3312, ..., 2001001..2001476, ..., etc.

Assuming that these existing entries were all loaded into the new table
with the values given as their primary keys and given that one could not
simply start the sequencer at a value above the highest existing value: If
one was to add a record and auto-generate a sequence number then can the
sequencer handle looping from 1 to 375 and returning 376 and then continue
until it reaches 1001, when it needs be loop again until 1288 and then
return 1289 and so forth?

During the load of the initial table data it would probably be necessary
to disable the sequencer for this column. Is this in fact the case? If so,
how is this done and how is the sequencer restored after the initial
migration of data is complete?

I presume that I can write my own sequencer function to accomplish this in
any case but I wish to know if the existing method handles this case.
Another solution is to simply decouple the existing key value from the new
and treat the exiting client number as a piece of data (with or without an
index), but that seems redundant since the new arbitrary key value might
just as well be the existing arbitrary key value.

Dispensing with the existing arbitrary number is another option but, the
fact is that present business practice is for employees to refer to their
clients and vendors by account number. The existing computer system is 25
years old but employs account numbers that predate automation. The firm is
well past the century mark and some of these numbers have been in use with
a few clients from the end of 1800's.  So, while not strictly a business
case, both the firm and some of its clients have a strong, if irrational,
attachment to preserving the existing scheme.

I regret if these questions appear naive but I am struggling with a lot of
new information on a number of fronts and as usual wish to get quick
answers to questions that may be far more involved than I realize.

Sincerely,

--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3


В списке pgsql-general по дате отправления:

Предыдущее
От: "Vyacheslav Kalinin"
Дата:
Сообщение: Prepared statement's plan
Следующее
От: "danilo.juvinao"
Дата:
Сообщение: know the schema name in a trigger