Re: possible bug with sequences - Mailing list pgsql-admin

Hi!

I've had the same problem while restoring one postgres database with
changed table names during the migrations from 7.3.2 to 7.4.1.

I sent the message to the list on 03/15/2004, but it seems it hasn't
arrived.I hope it can help, Here is:

-----------------------------------------------------------------------
Hi all.

Yesterday I planned the migration from a couple of databases from a
7.3.2 postgres box to a 7.4.1.

I did the pg_dump with the -C command, and substituted on the dump 2
aparitions of $libdir for the path of my libraries ( on the previous
tests, postgres didn't found this variable value, despite i had the
variable set: dynamic_library_path = '/apps/pg/lib', where pg is a
dinamic link)
¿Some idea on how to solve this?

Ok, now I'm on the new host, ready to import my database, via psql -d
template1 -f filename, it works fine, excepting from these errors:

psql:file1:879: ERROR:  relation "usuari_codi_seq" does not exist
psql:file1:887: ERROR:  relation "idioma_codi_seq" does not exist
psql:file1:895: ERROR:  relation "portada_codi_seq" does not exist
psql:file1:903: ERROR:  relation "nivell1_codi_seq" does not exist

Looking at the dump, for these lines, I see that postgres is trying to
set the values from these sequences, e.g.

-- Name: nivell1_codi_seq; Type: SEQUENCE SET; Schema: public; Owner:
riudoms
SELECT pg_catalog.setval ('nivell1_codi_seq', 14, true);

The problem resides in that the database user updated the table names
(but it seems that when it occurs, postgres doesn't change the sequence
names associated to them), and when importing, postgres automatically
created the sequences with the new names prefix, and then failed the
value setting

Fortunatelly the database had a few tables and sequences, and I could
solve this via PGAdmin, but I think it could be a disaster dumping for
example all databases on the system (we make hosting, and every user can
change the name of a table when he wants ...) or big-structured databases.

Someone has some idea of how this issue could be solved? Is a known
"bug" (If can be considered a bug ...)?

Until one more "collateral damage", I updated the sequences value to the
number the setval was telling, e.g ( 14 in SELECT pg_catalog.setval
('nivell1_codi_seq', 14, true);), but when the used tried to make a
insert it failed because the database told it was duplicated key values
(the second insert worked fine, because the sequence had incremented
this value), why this error happened? I would have to put the value from
setval+1 ? why?

Thanks in advance
----------------------------------------------------------------------
Victor Sudakov wrote:

> Colleagues,
>
> What is the internal difference between an implicit sequence (created
> automatically by the "serial" data type) and an explicit sequence
> (created manually)?
>
> I think I have hit something that can qualify as a bug. How to
> reproduce:
>
> ============== cut here =============================
>
> reports=# CREATE TABLE sometable (id serial, name text);
> NOTICE:  CREATE TABLE will create implicit sequence "sometable_id_seq" for "serial" column "sometable.id"
> CREATE TABLE
> reports=# ALTER TABLE sometable RENAME TO othername;
> ALTER TABLE
> reports=# \d othername
>                            Table "admin.othername"
>  Column |  Type   |                        Modifiers
> --------+---------+----------------------------------------------------------
>  id     | integer | not null default nextval('admin.sometable_id_seq'::text)
>  name   | text    |
>
> ============== cut here =============================
>
> Voila! pg_restore will now return an error on trying to execute
> SELECT pg_catalog.setval('sometable_id_seq', 4, true);
> because this sequence has not been created either explicitly or implicitly!
>
> It is 100% reproducible on postgresql-7.4.1
>


--
********************************************************
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio a oasi.org
********************************************************