pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
possible bug with sequences
2+ messages / 2 participants
[nested] [flat]

* possible bug with sequences
@ 2004-03-29 05:07  Victor Sudakov <vas@mpeks.no-spam-here.tomsk.su>
  0 siblings, 1 reply; 2+ messages in thread

From: Victor Sudakov @ 2004-03-29 05:07 UTC (permalink / raw)
  To: pgsql-admin

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

-- 
Victor Sudakov,  VAS4-RIPE, VAS47-RIPN
2:5005/149@fidonet http://vas.tomsk.ru/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: possible bug with sequences
@ 2004-04-06 10:03  Daniel Rubio <drubior@tinet.org>
  parent: Victor Sudakov <vas@mpeks.no-spam-here.tomsk.su>
  0 siblings, 0 replies; 2+ messages in thread

From: Daniel Rubio @ 2004-04-06 10:03 UTC (permalink / raw)
  To: 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
********************************************************



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-04-06 10:03 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-29 05:07 possible bug with sequences Victor Sudakov <vas@mpeks.no-spam-here.tomsk.su>
2004-04-06 10:03 ` Daniel Rubio <drubior@tinet.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox