[DOCS] Clarify 'dependent objects' for DROP COLUMN
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
[DOCS] Clarify 'dependent objects' for DROP COLUMN
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.
Re: [DOCS] Clarify 'dependent objects' for DROP COLUMN
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.
[DOCS] CREATE OPERATOR query
Hi,
While trying to create regression tests for CREATE OPERATOR, I am able to
create an operator despite not have USAGE / ALL access to the given
argument type. Shouldn't the following SQL throw an error?
BEGIN TRANSACTION;
CREATE ROLE rol_op3;
CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed');
CREATE FUNCTION fn_op3(type_op3, type_op3)
RETURNS type_op3 AS $$
SELECT NULL::type_op3;
$$ LANGUAGE sql IMMUTABLE;
REVOKE ALL ON TYPE type_op3 FROM rol_op3;
SET ROLE rol_op3;
CREATE OPERATOR #*# (
leftarg = type_op3,
rightarg = type_op3,
procedure = fn_op3
);
RESET ROLE;
ROLLBACK;
This what the doc says:
> To be able to create an operator, you must have USAGE privilege on the
argument types and the return type, as well as EXECUTE privilege on the
underlying function. If a commutator or negator operator is specified, you
must own these operators.
Any pointers would be helpful.
Thanks
--
Robins Tharakan
Re: [DOCS] CREATE OPERATOR query
Further, I seem to have a similar problem with the EXECUTE check
requirement as well.
Sample SQL given below.
BEGIN TRANSACTION;
CREATE ROLE rol_op3;
CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed');
CREATE FUNCTION fn_op3(type_op3, type_op3)
RETURNS type_op3 AS $$
SELECT NULL::type_op3;
$$ LANGUAGE sql IMMUTABLE;
REVOKE EXECUTE ON FUNCTION fn_op3(type_op3, type_op3) FROM rol_op3;
SET ROLE rol_op3;
CREATE OPERATOR #*# (
leftarg = type_op3,
rightarg = type_op3,
procedure = fn_op3
);
RESET ROLE;
ROLLBACK;
Thanks.
--
Robins Tharakan
On 22 May 2013 05:50, Robins Tharakan wrote:
> Hi,
>
> While trying to create regression tests for CREATE OPERATOR, I am able to
> create an operator despite not have USAGE / ALL access to the given
> argument type. Shouldn't the following SQL throw an error?
>
> BEGIN TRANSACTION;
> CREATE ROLE rol_op3;
> CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed');
> CREATE FUNCTION fn_op3(type_op3, type_op3)
> RETURNS type_op3 AS $$
> SELECT NULL::type_op3;
> $$ LANGUAGE sql IMMUTABLE;
> REVOKE ALL ON TYPE type_op3 FROM rol_op3;
> SET ROLE rol_op3;
> CREATE OPERATOR #*# (
>leftarg = type_op3,
>rightarg = type_op3,
>procedure = fn_op3
> );
> RESET ROLE;
> ROLLBACK;
>
> This what the doc says:
>
> > To be able to create an operator, you must have USAGE privilege on the
> argument types and the return type, as well as EXECUTE privilege on the
> underlying function. If a commutator or negator operator is specified, you
> must own these operators.
>
> Any pointers would be helpful.
>
> Thanks
> --
> Robins Tharakan
>
Re: [DOCS] CREATE OPERATOR query
Yes, but the documentation states that EXECUTE permission for the function
is required to even CREATE the operator.
This what the doc says:
> To be able to create an operator, you must have USAGE privilege on the
argument types and the return type, as well as EXECUTE privilege on the
underlying function. If a commutator or negator operator is specified, you
must own these operators.
Am I missing something obvious here?
--
Robins Tharakan
On 22 May 2013 07:12, Alvaro Herrera wrote:
> Robins Tharakan escribió:
> > Further, I seem to have a similar problem with the EXECUTE check
> > requirement as well.
> > Sample SQL given below.
> >
> > BEGIN TRANSACTION;
> > CREATE ROLE rol_op3;
> > CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed');
> > CREATE FUNCTION fn_op3(type_op3, type_op3)
> > RETURNS type_op3 AS $$
> > SELECT NULL::type_op3;
> > $$ LANGUAGE sql IMMUTABLE;
> > REVOKE EXECUTE ON FUNCTION fn_op3(type_op3, type_op3) FROM rol_op3;
>
> Doesn't PUBLIC still have EXECUTE permissions on this function?
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
Re: [DOCS] CREATE OPERATOR query
Makes perfect sense. Thanks and appreciate a prompt response. Would update the tests accordingly. -- Robins Tharakan On 22 May 2013 09:03, Alvaro Herrera wrote: > Robins Tharakan escribió: > > Yes, but the documentation states that EXECUTE permission for the > function > > is required to even CREATE the operator. > > What I mean is that your new role still has said permission through > PUBLIC (a pseudo-role which is automatically granted to all other roles > and cannot be revoked), even if you revoke it directly. > > -- > Álvaro Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
