Re: Ownership problem

On Thu, 2012-02-02 at 17:50 -0700, Scrap Happy wrote:
> Hi,
> 
> [Annoying that there aren't *easy* ways to cut and paste all of this!
> Even dialogs that *should* -- or DO! -- present the information seem
> to prevent selecting the "static" text therein for a "Copy".  So,
> blame any of my typos in transcribing this on that...  :< ]
> 
> Client: 1.14.1 (2 Dec 2011 REL-1_14_1)
>          Windows XP Pro SP3 (with updates from several months back)
> 
> Server: PostgreSQL 8.4.6 compiled from Visual C++ build 1400, 32bit
>          localhost:5432
> 
> User:   postgres (!!)
> 
> I stumbled on the following using the "point and click" interface.
> But, to clarify what I was doing, at the time, I've cut and
> pasted the contents of the "SQL" tab here, in each case (one
> "command group" issued at a time):
> 
> ---------8<----------8<-----------
>    CREATE DATABASE "Play"
>      WITH ENCODING='UTF8'
>           OWNER="SomeoneElse"
>           CONNECTION LIMIT=-1;
> .
>    CREATE SCHEMA "Example"
>           AUTHORIZATION "SomeoneElse";
> .
>    CREATE TABLE "Example"."Association"
>    (
>    )
>    WITH (
>      OIDS = FALSE
>    );
>    ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
> .
>    ALTER TABLE "Example"."Association"
>       ADD COLUMN id bigserial;
> ---------8<----------8<-----------
> 
> Yields this result from SQL "Messages" tab:
> 
> ---------8<----------8<-----------
> NOTICE:  ALTER TABLE will create implicit sequence "Association_id_seq"
>           for serial column "Association.id"
> 
> 
> ERROR:  sequence must have same owner as table it is linked to
> 
> ********** Error **********
> 
> ERROR: sequence must have same owner as table it is linked to
> SQL state: 55000
> ---------8<----------8<-----------
> 
> Repeating the same effective operations in a slightly different order:
> 
> ---------8<----------8<-----------
>    CREATE DATABASE "Play"
>      WITH ENCODING='UTF8'
>           OWNER="SomeoneElse"
>           CONNECTION LIMIT=-1;
> .
>    CREATE SCHEMA "Example"
>           AUTHORIZATION "SomeoneElse";
> .
>    CREATE TABLE "Example"."Association"
>    (
>       id bigserial
>    )
>    WITH (
>      OIDS = FALSE
>    );
>    ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
> ---------8<----------8<-----------
> 
> completes with no problems.
> 
> Of course, I can (reasonably) easily work around this. But, I
> suspect there is probably "a little something" missing, somewhere.
> 
> I didn't notice this problem with 1.12 -- but, I may have been
> doing things differently, at that time (no incentive to reinstall
> that *just* to test this)
> 
> (No doubt logging in as "SomeoneElse" would be the best "PTF".  :> )
> 

Well, I don't know what to say. This isn't an issue with pgAdmin.
PostgreSQL doesn't allow to add a column with a serial pseudo-type if
the table belongs to another user because PostgreSQL requires that the
sequence associated with the serial pseudo-type has the same owner than
the table. Not much we can do here.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org