Thread: Bug in ALTER TABLE/SEQUENCE OWNER TO

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

Bernd Helmle <mailings@oopsware.de> writes:
> I discovered the following confusing issue in CVS HEAD:
> ...
> As you can see, the owner of the sequence and table row type isn't changed 
> as well.

Hmmm ... this did not matter before shared dependencies, but it does
now ... I agree, we have to fix it.
        regards, tom lane

I am not sure it was reported to you but this has been corrected:test=> CREATE TABLE test(id SERIAL NOT NULL);NOTICE:
CREATETABLE will create implicit sequence "test_id_seq" for serial column "test.id"CREATE TABLEtest=> ALTER TABLE TEST
OWNERTO testuser;ALTER TABLEtest=>test=> SELECT typname, typowner, relname, relowner from pg_type c JOIN pg_class
dtest->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 dtest-> 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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian wrote:

>I am not sure it was reported to you but this has been corrected:
>    
>  
>
I saw Tom's fixes on -committers, thank you.