public inbox for [email protected]
help / color / mirror / Atom feedClarify 'dependent objects' for DROP COLUMN
5+ messages / 3 participants
[nested] [flat]
* Clarify 'dependent objects' for DROP COLUMN
@ 2013-03-12 04:21 Robins <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Robins @ 2013-03-12 04:21 UTC (permalink / raw)
To: pgsql-docs
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.
Thanks
---
Robins Tharakan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Clarify 'dependent objects' for DROP COLUMN
@ 2013-03-12 05:42 robins <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: robins @ 2013-03-12 05:42 UTC (permalink / raw)
To: pgsql-docs
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.
Thanks
--
Robins Tharakan
p.s.: Had to re-post this. Apologies if this lands up twice in the queue.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Clarify 'dependent objects' for DROP COLUMN
@ 2013-03-13 21:19 Bruce Momjian <[email protected]>
parent: Robins <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2013-03-13 21:19 UTC (permalink / raw)
To: Robins <[email protected]>; +Cc: pgsql-docs
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 <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Clarify 'dependent objects' for DROP COLUMN
@ 2013-03-13 22:08 robins <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: robins @ 2013-03-13 22:08 UTC (permalink / raw)
To: pgsql-docs
Thanks Bruce.
I think by using the word 'constraint' I understand what the documentation
meant.
Both my queries (samples given below) arose from the fact that although
there was a 'relation', this is probably not what the documentation was
talking about.
Q1:
postgres=# CREATE TABLE serialTest3 (f1 bigint);
CREATE TABLE
postgres=# CREATE SEQUENCE seq4 OWNED BY serialTest3.f1;
CREATE SEQUENCE
postgres=# DROP SEQUENCE seq4;
DROP SEQUENCE
postgres=#
Q2:
postgres=# CREATE TABLE serialTest3 (f1 bigint);
CREATE TABLE
postgres=# CREATE SEQUENCE seq4 OWNED BY serialTest3.f1;
CREATE SEQUENCE
postgres=# ALTER TABLE serialTest3 DROP COLUMN f1 RESTRICT;
ALTER TABLE
postgres=#
I was working on some regression tests and then just wanted to be sure that
this (Q2 in particular) was perfectly legal, before adding checks for them.
Thanks again.
--
Robins
Tharakan
---------- Forwarded message ----------
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Clarify 'dependent objects' for DROP COLUMN
@ 2013-03-13 22:41 Bruce Momjian <[email protected]>
parent: robins <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2013-03-13 22:41 UTC (permalink / raw)
To: robins <[email protected]>; +Cc: pgsql-docs
On Thu, Mar 14, 2013 at 03:38:42AM +0530, robins wrote:
> Thanks Bruce.
>
> I think by using the word 'constraint' I understand what the documentation
> meant.
>
> Both my queries (samples given below) arose from the fact that although there
> was a 'relation', this is probably not what the documentation was talking
> about.
>
> Q1:
> postgres=# CREATE TABLE serialTest3 (f1 bigint);
> CREATE TABLE
> postgres=# CREATE SEQUENCE seq4 OWNED BY serialTest3.f1;
> CREATE SEQUENCE
> postgres=# DROP SEQUENCE seq4;
> DROP SEQUENCE
> postgres=#
>
>
> Q2:
> postgres=# CREATE TABLE serialTest3 (f1 bigint);
> CREATE TABLE
> postgres=# CREATE SEQUENCE seq4 OWNED BY serialTest3.f1;
> CREATE SEQUENCE
> postgres=# ALTER TABLE serialTest3 DROP COLUMN f1 RESTRICT;
> ALTER TABLE
> postgres=#
>
> I was working on some regression tests and then just wanted to be sure that
> this (Q2 in particular) was perfectly legal, before adding checks for them.
It seems like the OWNED BY only handles auto-drop, rather than sequence
removal restrictions:
The <literal>OWNED BY</literal> option causes the sequence to be
associated with a specific table column, such that if that column
(or its whole table) is dropped, the sequence will be automatically
dropped as well. The specified table must have the same owner and be in
the same schema as the sequence.
<literal>OWNED BY NONE</literal>, the default, specifies that there
is no such association.
Seems it is not the same as actually referencing the sequence in the table.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-03-13 22:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-12 04:21 Clarify 'dependent objects' for DROP COLUMN Robins <[email protected]>
2013-03-13 21:19 ` Bruce Momjian <[email protected]>
2013-03-13 22:08 ` robins <[email protected]>
2013-03-13 22:41 ` Bruce Momjian <[email protected]>
2013-03-12 05:42 Clarify 'dependent objects' for DROP COLUMN robins <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox