Skip site navigation (1) Skip section navigation (2)

Re: pg_dump bug in 7.3.9 with sequences

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: pg_dump bug in 7.3.9 with sequences
Date: 2005-02-03 06:56:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
>>3. When the default is changed, the dependency is updated
>>to reflect the new sequence. The old sequence is left intact
>>as an independent object.
>What exactly is the use-case of that (or any other manipulation of a
>serial column's default)?  There is no point that I can see in just
>rolling one sequence object into a serial in place of another.  Whatever
>parameter change you might need to accomplish can be done with ALTER
>SEQUENCE on the original sequence, without replacing the object per se.
>(Except for renaming it; but given the way pg_dump handles this stuff,
>you do not actually have the option to control the sequence name anyway.)
O.k. I will buy that. So I say:

#3 rev2: When the default is changed, the dependency is updated
to reflect the new sequence and the old sequence is dropped.

>I also think that altering the default expression is useless --- it's
>not a serial column anymore if you do that.  It might be worth trying to
>teach ALTER COLUMN TYPE to handle the cases of switching a serial column
>to a non-serial type or vice versa, but I don't think users should be
>allowed to reach in and mess with the default directly.
Well that would be fine if pg_dump actually handled the scenario
I presented in my previous email correctly. The problem
is you have situations where colummns became serial columns
after the fact or they are columns that were created in
a dataset before there was a serial data type (such as 7.2).


Joshua D. Drake

>In short I vote for #1.  If you want to support #2 then teach ALTER
>COLUMN TYPE to handle it.  #3 is simply pointless.
>BTW, experimenting with this reveals a different pg_dump issue, which is
>that it will not replicate a nondefault set of sequence parameters for a
>serial sequence.  For instance
>dtest=# create table t1 (f1 serial);
>NOTICE:  CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
>dtest=# alter sequence t1_f1_seq cycle;
>pg_dump will just emit "create table t1 (f1 serial)" with no hint that
>the sequence ought to be set to CYCLE mode.  I'm not sure about an
>appropriate fix offhand --- we can't very well use ALTER SEQUENCE in
>just this way in the dump, because of the risk of the sequence name
>being possibly different at reload.  (Come to think of it, we are not
>very good about propagating GRANTs on the sequence either, because of
>the same risk.)
>			regards, tom lane
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com -
PostgreSQL Replicator -- production quality replication for PostgreSQL

Attachment: jd.vcf
Description: text/x-vcard (285 bytes)

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2005-02-03 06:59:19
Subject: Re: libpq API incompatibility between 7.4 and 8.0
Previous:From: Tom LaneDate: 2005-02-03 06:38:39
Subject: Re: fatal: cache id 30 (or alike)

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group