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