Обсуждение: Make an id field max(id)+1 rather than SERIAL

I have problems after exporting then importing data into newly created
databases that the sequence and the indexed serial fields get out of
alignment.

I wonder if it isn't better to always simply insert ids using some sort
of function or other procedure to calculate on each insert a new unique
id number.

Is this feasible?

Thanks for any help.
Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

On Thu, 2002-09-19 at 15:14, Rory Campbell-Lange wrote:
> I have problems after exporting then importing data into newly created
> databases that the sequence and the indexed serial fields get out of
> alignment.
>
> I wonder if it isn't better to always simply insert ids using some sort
> of function or other procedure to calculate on each insert a new unique
> id number.

After you import data, you must also reset the sequence:

  SELECT setval('sequence_name',
        (SELECT MAX(sequence_column) FROM table));
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Bring ye all the tithes into the storehouse, that
      there may be meat in mine house, and prove me now
      herewith, saith the LORD of hosts, if I will not open
      you the windows of heaven, and pour you out a
      blessing, that there shall not be room enough to
      receive it."           Malachi 3:10

On 19/09/02, Oliver Elphick (olly@lfix.co.uk) wrote:
> On Thu, 2002-09-19 at 15:14, Rory Campbell-Lange wrote:
> > I have problems after exporting then importing data into newly created
> > databases that the sequence and the indexed serial fields get out of
> > alignment.
> >
> > I wonder if it isn't better to always simply insert ids using some sort
> > of function or other procedure to calculate on each insert a new unique
> > id number.
>
> After you import data, you must also reset the sequence:
>
>   SELECT setval('sequence_name',
>         (SELECT MAX(sequence_column) FROM table));

Excellent stuff. Thanks.
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

hi,
today i had 2 corrupted tables after nearly 2 month without any problems.
i excluded the damaged records (1 in each table) and rebuild both tables.
one of the tables is used as some kind of "first in last out" queue to store
continous data for a time slot about 1 day which is about 80.000 records, so
the content of the table changes daily.
another table queues data for 3 month which is about 2.000.000 records.
currently i do a vacuum at night to keep the database clean and accesstimes
usable. i dont have a clue of where the corruption came from.
is it mandatory for the tables integrity to run "analyze" periodically?
the sytem is  a AMD Athlon, 1 gb memory running Red Hat Linux 7.1
pg version is 7.2-1PGDG

excerpt from postgresql.conf:
shared_buffers =35000
max_fsm_relations = 100
max_fsm_pages = 2000
sort_mem = 128
vacuum_mem = 8192

clients:
php-4.1.2
perl v5.6.1 with DBI::version "1.18"

any ideas how to track down the cause for corruptions like this?
any problems known  with the pg version?

thanks thilo

"Thilo Hille" <thilo@resourcery.de> writes:
> today i had 2 corrupted tables after nearly 2 month without any problems.

Corrupted how, exactly?

> is it mandatory for the tables integrity to run "analyze" periodically?

No.

> pg version is 7.2-1PGDG

You should consider updating to 7.2.2; although none of the bug fixes in
7.2.2 look like they might be causes of table corruption.

            regards, tom lane

Hi
after inserting bulk data, Select Max of the sequence.

SELECT setval('abc_xyz_seq', max value);

should get the sequence in synchro.

Vijay

Rory Campbell-Lange wrote:
>
> I have problems after exporting then importing data into newly created
> databases that the sequence and the indexed serial fields get out of
> alignment.