Обсуждение: Migration problem - serial fields

Greetings,

I've only been working with PG for a few days, so bear with me--hopefully
this isn't too much of a newbie dumb question!  If this would be better
posted in the novice group, please let me know. As a bit of background,
while I have a good bit of experience working with different dbs, I have
little formal training and it's possible that I do not do things in the
"right way".  A side-effect of doing small web/db projects.

Onward...I've got an existing db in SQL7 that I need to migrate over to PG.
Following advice from an older usenet post, I was able to establish an odbc
connection between the two servers using Enterprise Manager.  First I had
run scripts to create the tables on PG vs. allowing EM to do it (it did not
do a very good job!)  I would like to use the datasource-to-datasource
transfer if possible since it means I wouldn't have to export to text files
and import into PG.

The snag I'm encountering is with Identity fields in SQL7.  I discovered the
equivalent in PG -- sequences/serial field.  I would like to use serial if
possible, but here's the problem:  getting the existing records over while
*keeping the existing values* for the Identity/sequence ID fields.  They
actually transfer over fine, but the next insert into the  PG table
generates a duplicate ID error.  It seems obvious that my transfer did not
update the sequence used by the serial field.  However, I'm just not sure of
the best course of action.

I have the PG Developer's Handbook, but the only example that applies
assumes that I would rewrite the serial fields (ie, let them auto-generate
by not giving values during the import/transfer).  This will not work due to
the fact that those IDs are used as foreign keys in other tables.


I've thought about the following as solutions:

1.  Do the transfer, put values into the serial field, then find the highest
value and manually set the sequence somehow to start from highest+1 (seed
value).  I don't know if this means moving away from serial field to a
"nextval of sequence" approach.  I understand there is a problem with orphan
sequences with serial fields if you drop tables, so maybe I shouldn't use
serials anyway.

2.  Stop using autonumber-type fields!  I have used random-character unique
IDs (varchar) in the past; if they are a more solid solution I will use that
approach.

3.  Let the code increment the ID (ie, find highest and increment), and make
the ID an int field -- not the best solution due to possibility of
simultaneous inserts, plus now more logic moves into the application.

4.  ...your suggestions...

Thanks for any help you can provide.

Rick

On Mon, 4 Mar 2002, Rick Anderson wrote:

> The snag I'm encountering is with Identity fields in SQL7.  I discovered the
> equivalent in PG -- sequences/serial field.  I would like to use serial if
> possible, but here's the problem:  getting the existing records over while
> *keeping the existing values* for the Identity/sequence ID fields.  They
> actually transfer over fine, but the next insert into the  PG table
> generates a duplicate ID error.  It seems obvious that my transfer did not
> update the sequence used by the serial field.  However, I'm just not sure of
> the best course of action.

Yes, the sequence is only updated if a value is not given (it's
effectively just a default so nextval doesn't get called otherwise)

> I've thought about the following as solutions:
>
> 1.  Do the transfer, put values into the serial field, then find the highest
> value and manually set the sequence somehow to start from highest+1 (seed
> value).  I don't know if this means moving away from serial field to a

This is probably the easiest.  setval('<seq name>', highest value) should
be okay.  IIRC this is what pg_dump does.

> "nextval of sequence" approach.  I understand there is a problem with orphan
> sequences with serial fields if you drop tables, so maybe I shouldn't use
> serials anyway.

It's just a thing to remember when you drop the table.  You run into
similar problems with standalone sequences anyway.

[2002-03-04 14:07] Rick Anderson said:
| The snag I'm encountering is with Identity fields in SQL7.  I discovered the
| equivalent in PG -- sequences/serial field.  I would like to use serial if
| possible, but here's the problem:  getting the existing records over while
| *keeping the existing values* for the Identity/sequence ID fields.  They
| actually transfer over fine, but the next insert into the  PG table
| generates a duplicate ID error.  It seems obvious that my transfer did not
| update the sequence used by the serial field.  However, I'm just not sure of
| the best course of action.

You need to use something like

  db=> select setval('$A',(select max($B) from $C)+1);

where
  $A = serial-sequence name
  $B = serial column name
  $C = table name

hth.
  brent

--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman