Обсуждение: Ownership problem
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" CONNECTIONLIMIT=-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" CONNECTIONLIMIT=-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". :> ) Thanks! --don
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
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)
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