Re: Clarify 'dependent objects' for DROP COLUMN

On Tue, Mar 12, 2013 at 09:51:44AM +0530, Robins wrote:
> Hi,
>
> ALTER TABLE in postgresql.org/docs/devel/ says:
>
> RESTRICT: Refuse to drop the column or constraint if there are any dependent
> objects. This is the default behavior.
>
> Could someone confirm whether 'dependent objects' also includes SEQUENCES? i.e.
> if I create a sequence OWNED BY tbl.col1 and then try to drop the column with
> RESTRICT, should it allow this DROP? Currently it does, but by reading that
> line it seemed it shouldn't.

I had to dig a little bit on this. The "dependent" object would be the
removal of the constraint depending on the sequence. Here is an
example:

test=> create table test (x serial);
CREATE TABLE
test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
x | integer | not null default nextval('test_x_seq'::regclass)

test=> \ds
List of relations
Schema | Name | Type | Owner
--------+------------+----------+----------
public | test_x_seq | sequence | postgres
(1 row)

--> test=> drop sequence test_x_seq;
ERROR: cannot drop sequence test_x_seq because other objects depend on it
DETAIL: default for table test column x depends on sequence test_x_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
--> test=> drop sequence test_x_seq cascade;
NOTICE: drop cascades to default for table test column x
DROP SEQUENCE
test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
x | integer | not null

If this does not answer your questions, please post queries showing the
problem. Thanks.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +