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