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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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