pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Daniel Rubio <drubior@tinet.org>
To: pgsql-admin@postgresql.org
Subject: Re: possible bug with sequences
Date: Tue, 06 Apr 2004 12:03:59 +0200
Message-ID: <4072808F.1080200@tinet.org> (raw)
In-Reply-To: <c48au4$b0a$1@mpeks.tomsk.su>
References: <c48au4$b0a$1@mpeks.tomsk.su>

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
********************************************************



view thread (2+ messages)

Message-ID: <4072808F.1080200@tinet.org>
Permalink:  ../4072808F.1080200@tinet.org/
Also on:    postgresql.org/message-id/4072808F.1080200@tinet.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: drubior@tinet.org
  Subject: Re: possible bug with sequences
  In-Reply-To: <4072808F.1080200@tinet.org>
  References: <c48au4$b0a$1@mpeks.tomsk.su> <4072808F.1080200@tinet.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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