Re: Ownership problem

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.

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.

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)