Hello hackers...

I've spent the last couple of evening tracing through the drop table/sequence 
code trying to figure out the best to drop the sequence when the table is 
dropped.

Here is what I am proposing to do.  I just wanted to throw out my idea and get 
some feedback since I am just beginning to understand how the backend works.

Take the following example:
CREATE TABLE foo (i SERIAL, t text);

This creates table foo, index foo_i_key, and the sequence foo_i_seq.

The sequence ocuppies three of the system tables: pg_class, pg_attribute, and 
pg_attrdef.  When the table gets dropped, the table foo and foo_i_key are 
removed.  The default portion of the sequence is also removed from the 
pg_attrdef system table, because the attrelid matches the table's oid.  

I believe this is incorrect ... I think the attrelid should match the seqences 
oid instead of the table's oid to prevent the following error:

ryan=> CREATE TABLE foo (i SERIAL, t text);
NOTICE:  CREATE TABLE will create implicit sequence foo_i_seq for SERIAL column 
foo.i
NOTICE:  CREATE TABLE/UNIQUE will create implicit index foo_i_key for table foo
CREATE

ryan=> \d

Database    = ryan
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | rbrad            | foo                              | table    |
 | rbrad            | foo_i_key                        | index    |
 | rbrad            | foo_i_seq                        | sequence |
 +------------------+----------------------------------+----------+

ryan=> \d foo;

Table    = foo
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| i                                | int4 not null default nextval('f |     4 |
| t                                | text                             |   var |
+----------------------------------+----------------------------------+-------+
Index:    foo_i_key

ryan=> drop sequence foo_i_seq;
DROP

ryan=> \d

Database    = ryan
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | rbrad            | foo                              | table    |
 | rbrad            | foo_i_key                        | index    |
 +------------------+----------------------------------+----------+
ryan=> \d foo;

Table    = foo
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| i                                | int4 not null default nextval('f |     4 |
| t                                | text                             |   var |
+----------------------------------+----------------------------------+-------+
Index:    foo_i_key

ryan=> insert into foo (t) values ('blah');
ERROR:  foo_i_seq.nextval: sequence does not exist

ryan=>

This looks pretty easy to fix.

Back to my origional point .. I think we need another system table to map the 
sequence oid to the table's oid.  I've noticed this done with the inheritance, 
indexes, etc ... but I don't see a pg_sequence table.

I would be glad to try and finish this in the next couple of evenings if this 
looks like the correct approach to the problem, otherwise could someone point me 
in the right direction :)

Thanks,
-Ryan

Mon, 15 Mar 1999 03:12:20 -0700 (MST)
Ryan Bradetich <rbrad@hpb50023.boi.hp.com>
Sequences....

Can I ask where we are with this.

> Hello hackers...
> 
> I've spent the last couple of evening tracing through the drop table/sequence 
> code trying to figure out the best to drop the sequence when the table is 
> dropped.
> 
> Here is what I am proposing to do.  I just wanted to throw out my idea and get 
> some feedback since I am just beginning to understand how the backend works.
> 
> Take the following example:
> CREATE TABLE foo (i SERIAL, t text);
> 
> This creates table foo, index foo_i_key, and the sequence foo_i_seq.
> 
> The sequence ocuppies three of the system tables: pg_class, pg_attribute, and 
> pg_attrdef.  When the table gets dropped, the table foo and foo_i_key are 
> removed.  The default portion of the sequence is also removed from the 
> pg_attrdef system table, because the attrelid matches the table's oid.  
> 
> I believe this is incorrect ... I think the attrelid should match the seqences 
> oid instead of the table's oid to prevent the following error:
> 
> ryan=> CREATE TABLE foo (i SERIAL, t text);
> NOTICE:  CREATE TABLE will create implicit sequence foo_i_seq for SERIAL column 
> foo.i
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index foo_i_key for table foo
> CREATE
> 
> ryan=> \d
> 
> Database    = ryan
>  +------------------+----------------------------------+----------+
>  |  Owner           |             Relation             |   Type   |
>  +------------------+----------------------------------+----------+
>  | rbrad            | foo                              | table    |
>  | rbrad            | foo_i_key                        | index    |
>  | rbrad            | foo_i_seq                        | sequence |
>  +------------------+----------------------------------+----------+
> 
> ryan=> \d foo;
> 
> Table    = foo
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | i                                | int4 not null default nextval('f |     4 |
> | t                                | text                             |   var |
> +----------------------------------+----------------------------------+-------+
> Index:    foo_i_key
> 
> ryan=> drop sequence foo_i_seq;
> DROP
> 
> ryan=> \d
> 
> Database    = ryan
>  +------------------+----------------------------------+----------+
>  |  Owner           |             Relation             |   Type   |
>  +------------------+----------------------------------+----------+
>  | rbrad            | foo                              | table    |
>  | rbrad            | foo_i_key                        | index    |
>  +------------------+----------------------------------+----------+
> ryan=> \d foo;
> 
> Table    = foo
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | i                                | int4 not null default nextval('f |     4 |
> | t                                | text                             |   var |
> +----------------------------------+----------------------------------+-------+
> Index:    foo_i_key
> 
> ryan=> insert into foo (t) values ('blah');
> ERROR:  foo_i_seq.nextval: sequence does not exist
> 
> ryan=>
> 
> This looks pretty easy to fix.
> 
> Back to my origional point .. I think we need another system table to map the 
> sequence oid to the table's oid.  I've noticed this done with the inheritance, 
> indexes, etc ... but I don't see a pg_sequence table.
> 
> I would be glad to try and finish this in the next couple of evenings if this 
> looks like the correct approach to the problem, otherwise could someone point me 
> in the right direction :)
> 
> Thanks,
> -Ryan
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 9 May 1999 20:42:04 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Sequences....