Message-ID: <4201CB28.8050206@commandprompt.com>
Date: Wed, 02 Feb 2005 22:56:40 -0800
From: "Joshua D. Drake" <jd@commandprompt.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>,
	pgsql-hackers@postgresql.org
Subject: Re: pg_dump bug in 7.3.9 with sequences
References: <42013187.7040806@commandprompt.com>
	<6686.1107379308@sss.pgh.pa.us>
	<42014C28.1070300@commandprompt.com>
	<20050202220404.GC1967@dcc.uchile.cl>
	<42016727.3020107@commandprompt.com>
	<11680.1107406182@sss.pgh.pa.us>
In-Reply-To: <11680.1107406182@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="------------050701070706010004080002"

This is a multi-part message in MIME format.
--------------050701070706010004080002
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>>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).

Sincerely,

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"
>CREATE TABLE
>dtest=# alter sequence t1_f1_seq cycle;
>ALTER SEQUENCE
>
>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@postgresql.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@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


--------------050701070706010004080002
Content-Type: text/x-vcard; charset=utf-8;
 name="jd.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="jd.vcf"

begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:jd@commandprompt.com
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


--------------050701070706010004080002--