Re: Bug in ALTER TABLE/SEQUENCE OWNER TO

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Bernd Helmle <mailings(at)oopsware(dot)de>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Bug in ALTER TABLE/SEQUENCE OWNER TO
Date: 2005-08-13 02:25:00
Message-ID: 200508130225.j7D2P0X29526@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


I am not sure it was reported to you but this has been corrected:

test=> CREATE TABLE test(id SERIAL NOT NULL);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
CREATE TABLE
test=> ALTER TABLE TEST OWNER TO testuser;
ALTER TABLE
test=>
test=> SELECT typname, typowner, relname, relowner from pg_type c JOIN pg_class d
test-> ON (d.reltype = c.oid) WHERE typname = 'test';
typname | typowner | relname | relowner
---------+----------+---------+----------
test | 16385 | test | 16385
(1 row)

test=>
test=> SELECT typname, typowner, relname, relowner from pg_type c JOIN pg_class d
test-> ON (d.reltype = c.oid) WHERE typname = 'test_id_seq';
typname | typowner | relname | relowner
-------------+----------+-------------+----------
test_id_seq | 16385 | test_id_seq | 16385
(1 row)
_

---------------------------------------------------------------------------

Bernd Helmle wrote:
> I discovered the following confusing issue in CVS HEAD:
>
> CREATE TABLE test(id SERIAL NOT NULL);
> ALTER TABLE TEST OWNER TO testuser;
>
> SELECT typname, typowner, relname, relowner from pg_type c JOIN pg_class d
> ON (d.reltype = c.oid) WHERE typname = 'test';
>
> typname | typowner | relname | relowner
> ---------+----------+---------+----------
> test | 10 | test | 16419
>
>
> SELECT typname, typowner, relname, relowner from pg_type c JOIN pg_class d
> ON (d.reltype = c.oid) WHERE typname = 'test_id_seq';
> typname | typowner | relname | relowner
> -------------+----------+-------------+----------
> test_id_seq | 10 | test_id_seq | 16419
> (1 row)
>
> As you can see, the owner of the sequence and table row type isn't changed
> as well. I have done a small patch that adresses this issue for discussion.
> Please note that it breaks the dependency regression test at least:
>
> ALTER TABLE deptest OWNER TO regression_user3;
> DROP USER regression_user3;
> ERROR: role "regression_user3" cannot be dropped because some objects
> depend on it
> ! DETAIL: owner of table deptest
> -- if we drop the object, we can drop the user too
> DROP TABLE deptest;
> DROP USER regression_user3;
> --- 33,40 ----
> ALTER TABLE deptest OWNER TO regression_user3;
> DROP USER regression_user3;
> ERROR: role "regression_user3" cannot be dropped because some objects
> depend on it
> ! DETAIL: owner of type deptest
> ! owner of table deptest
> -- if we drop the object, we can drop the user too
> DROP TABLE deptest;
> DROP USER regression_user3;
>
> Any opinions?
>
> --
> Bernd

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2005-08-13 02:27:16 Re: openbsd, plpython, missing threading symbols
Previous Message Bruce Momjian 2005-08-13 02:22:32 Re: [HACKERS] ECPG ignores SAVEPOINT if first statement of a transaction