Date: Wed, 13 Mar 2013 17:19:41 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Robins <robins@pobox.com>
Cc: pgsql-docs@postgresql.org
Subject: Re: Clarify 'dependent objects' for DROP COLUMN
Message-ID: <20130313211941.GB22282@momjian.us>
References: 
 <CAEP4nAxFw=2pB-oJyWZkwo7hccggoDzooEBmv+umd1R-neyZXg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAEP4nAxFw=2pB-oJyWZkwo7hccggoDzooEBmv+umd1R-neyZXg@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-06-14)
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

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


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs