I've found a situation that doesn't look correct to me in 7.3.1 (and 
presumably 7.3 as well). If I alter a column so that it no longer uses a 
sequence for default values and then try to drop the aforementioned 
sequence, the dependency checking code does not allow me to drop the 
sequence on the basis that it is still deing used. As I have removed the 
default setting, however, this should not be the case, as far as I can 
tell. The following illustrates my finding.

junk=> create table testing
junk-> (testing_id serial not null primary key);
NOTICE:  CREATE TABLE will create implicit sequence 
'testing_testing_id_seq' for SERIAL column 'testing.testing_id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'testing_pkey' for table 'testing'
CREATE TABLE
junk=> \d
                   List of relations
  Schema |          Name          |   Type   | Owner
--------+------------------------+----------+--------
  public | testing                | table    | tara
  public | testing_testing_id_seq | sequence | tara
(2 rows)

junk=> \d testing
                                  Table "public.testing"
    Column   |  Type   |                            Modifiers 

------------+---------+-----------------------------------------------------------------
  testing_id | integer | not null default 
nextval('public.testing_testing_id_seq'::text)
Indexes: testing_pkey primary key btree (testing_id)

junk=> alter table testing
junk-> alter column testing_id drop default;
ALTER TABLE
junk=> \d testing
       Table "public.testing"
    Column   |  Type   | Modifiers
------------+---------+-----------
  testing_id | integer | not null
Indexes: testing_pkey primary key btree (testing_id)

junk=> drop sequence testing_testing_id_seq;
ERROR:  Cannot drop sequence testing_testing_id_seq because table 
testing column testing_id requires it
         You may drop table testing column testing_id instead

-- 
Tara Piorkowski
System Administrator, vilaj.com, LLC
<http://www.vilaj.com/>


Mon, 30 Dec 2002 11:09:55 -0500
Tara Piorkowski <tara@vilaj.com>
Bug in Dependencies Code in 7.3.x?
Tara Piorkowski <tara@vilaj.com> writes:
> junk=> create table testing
> junk-> (testing_id serial not null primary key);
> NOTICE:  CREATE TABLE will create implicit sequence 
> 'testing_testing_id_seq' for SERIAL column 'testing.testing_id'
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
> 'testing_pkey' for table 'testing'
> CREATE TABLE
> junk=> alter table testing
> junk-> alter column testing_id drop default;
> ALTER TABLE

I'd be inclined to say that the bug here is that you shouldn't be
allowed to do ALTER COLUMN DROP DEFAULT (nor SET DEFAULT for that
matter) on a SERIAL column.  The default expression is part of the
implementation of SERIAL, not an independently tweakable entity.

We could make it work sort of the way Tara is expecting if the
dependency link were to associate the sequence object with the
column's default expression, and not with the column itself ---
but if we did that, then the above DROP DEFAULT would probably make
the sequence object go away too, which is still not quite what
she's expecting.

			regards, tom lane

Mon, 30 Dec 2002 17:39:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in Dependencies Code in 7.3.x? 
Tom Lane wrote:
> Tara Piorkowski <tara@vilaj.com> writes:
>>junk=> create table testing
>>junk-> (testing_id serial not null primary key);
>>NOTICE:  CREATE TABLE will create implicit sequence 
>>'testing_testing_id_seq' for SERIAL column 'testing.testing_id'
>>NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
>>'testing_pkey' for table 'testing'
>>CREATE TABLE
>>junk=> alter table testing
>>junk-> alter column testing_id drop default;
>>ALTER TABLE
> 
> 
> I'd be inclined to say that the bug here is that you shouldn't be
> allowed to do ALTER COLUMN DROP DEFAULT (nor SET DEFAULT for that
> matter) on a SERIAL column.  The default expression is part of the
> implementation of SERIAL, not an independently tweakable entity.

Tom -

I am actually inclined to agree with you. This is not an issue I see 
coming up for us again, so making it work as you described above seems 
to me does not impact us negatively at all. This was a fluke for us as 
our application code was dependent on the manner in which the way 
pre-7.3 versions of PostgreSQL truncated sequence names. In running 
tests on the software we wrote to migrate our 7.2 databases to become 
7.3 databases we just happened to stumble across this issue by accident 
(literally).

Regardless, my thinking had been that I was looking at an INT with a 
DEFAULT set, in which case I think this would be a bonified bug, thus my 
report. However, it makes more sense to consider the column to be of 
type SERIAL, despite the labelling. Perhaps the best result would be to 
not allow the DROP DEFAULT on a SERIAL column and to label the SERIAL 
column as such (as opposed to an INT with DEFAULT).

Thanks, as always, for taking the time to consider my posting and respond.

- Tara

-- 
Tara Piorkowski
System Administrator, vilaj.com, LLC
<http://www.vilaj.com/>


Mon, 30 Dec 2002 20:08:51 -0500
Tara Piorkowski <tara@vilaj.com>
Re: Bug in Dependencies Code in 7.3.x?
Tara Piorkowski <tara@vilaj.com> writes:
> Regardless, my thinking had been that I was looking at an INT with a 
> DEFAULT set, in which case I think this would be a bonified bug, thus my 
> report.

Right --- but *if you'd declared it that way*, the system would have
reacted in the way you were expecting.  SERIAL sets up dependencies that
prevent you from dropping the sequence as a separate entity, while an
INT column with a handmade DEFAULT expression doesn't.

Ideally, a SERIAL column would completely hide the fact that it's made
from a sequence and a default expression.  We're not there yet ... but
7.3 is closer than ever before.  (It'd be interesting to look at whether
Rod Taylor's DOMAIN work could help button things up.)

			regards, tom lane

Mon, 30 Dec 2002 23:36:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in Dependencies Code in 7.3.x? 

Seems this is already a TODO:

	* Have sequence dependency track use of DEFAULT sequences, seqname.nextval

---------------------------------------------------------------------------

Tom Lane wrote:
> Tara Piorkowski <tara@vilaj.com> writes:
> > Regardless, my thinking had been that I was looking at an INT with a 
> > DEFAULT set, in which case I think this would be a bonified bug, thus my 
> > report.
> 
> Right --- but *if you'd declared it that way*, the system would have
> reacted in the way you were expecting.  SERIAL sets up dependencies that
> prevent you from dropping the sequence as a separate entity, while an
> INT column with a handmade DEFAULT expression doesn't.
> 
> Ideally, a SERIAL column would completely hide the fact that it's made
> from a sequence and a default expression.  We're not there yet ... but
> 7.3 is closer than ever before.  (It'd be interesting to look at whether
> Rod Taylor's DOMAIN work could help button things up.)
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 31 Dec 2002 11:36:37 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in Dependencies Code in 7.3.x?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Seems this is already a TODO:
> 	* Have sequence dependency track use of DEFAULT sequences, seqname.nextval

That's related but not the same issue.

			regards, tom lane

Tue, 31 Dec 2002 12:07:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in Dependencies Code in 7.3.x? 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Seems this is already a TODO:
> > 	* Have sequence dependency track use of DEFAULT sequences, seqname.nextval
> 
> That's related but not the same issue.

Related in that ALTER TABLE DROP DEFAULT _doesn't_ see a dependancy for
sequences in a DEFAULT?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 31 Dec 2002 12:32:07 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in Dependencies Code in 7.3.x?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> Seems this is already a TODO:
>>> * Have sequence dependency track use of DEFAULT sequences, seqname.nextval
>> 
>> That's related but not the same issue.

> Related in that ALTER TABLE DROP DEFAULT _doesn't_ see a dependancy for
> sequences in a DEFAULT?

Even if the dependency generator understood about nextval, it would
generate a dependency from the expression to the sequence, not vice
versa --- ie, the system would prevent you from dropping the sequence
without dropping the default expression.  It would not prevent ALTER
TABLE DROP DEFAULT, which is what's at issue here.

			regards, tom lane

Tue, 31 Dec 2002 12:41:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in Dependencies Code in 7.3.x? 

I get it, mutual dependency because they are both droppable. Added to
TODO:

	* Have DEFAULT dependency track use of sequence, for DROP DEFAULT check

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >>> Seems this is already a TODO:
> >>> * Have sequence dependency track use of DEFAULT sequences, seqname.nextval
> >> 
> >> That's related but not the same issue.
> 
> > Related in that ALTER TABLE DROP DEFAULT _doesn't_ see a dependancy for
> > sequences in a DEFAULT?
> 
> Even if the dependency generator understood about nextval, it would
> generate a dependency from the expression to the sequence, not vice
> versa --- ie, the system would prevent you from dropping the sequence
> without dropping the default expression.  It would not prevent ALTER
> TABLE DROP DEFAULT, which is what's at issue here.
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 31 Dec 2002 13:04:43 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in Dependencies Code in 7.3.x?