Re: Ownership problem

On Mon, 2012-02-06 at 23:27 -0700, Scrap Happy wrote:
> On 2/4/2012 11:13 AM, Guillaume Lelarge wrote:
> > On Thu, 2012-02-02 at 17:50 -0700, Scrap Happy wrote:
>
> >> ---------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.
> >>
> >> (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.
>
> Understood. The point is, there is no way to /add/ a serial pseudo-type
> to a table owned by another user in pgadmin3.
>

Saying "in pgadmin" is not completely true. You also cannot do it in
phppgadmin, in psql... IOW, with any PostgreSQL client.

> Adding a column of type *serial won't work because the serial has
> to inherit the owner of the table to which it is being added.
>

Exactly. It has nothing to do with pgAdmin3.

> Similarly, you can /create/ the serial in pgadmin3. And, you can
> declare its owner to coincide with the owner of the table. But,
> you can't "move" it into the existing table (i.e., a drag 'n' drop
> style operation).
>
> Yet, if you add a serial column to the table WHILE YOU ARE CREATING
> THE TABLE (in pgadmin3), all will magically work as the serial gets
> created with the proper owner (as the table).
>
> The purpose of my post, here, is for future googlers that might
> find themselves in a similar situation (hence the suggested "fix",
> above)
>

OK.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com