i'm using pgadmin3 v1.2.2 on os x against a postgres 8.0.3 server to
add a bigserial column to a table. i'm getting the error:

ERROR: permission denied for relation pg_depend

i did a search on the mailing list and found some references to problems,
but none matched exactly.

the sql from the "SQL" tab of the "New Column" dialog looks like this:

CREATE SEQUENCE public.categories_idtest_seq;
ALTER TABLE categories
ADD COLUMN idtest int8;
ALTER TABLE categories
ALTER COLUMN idtest SET DEFAULT
nextval('public.categories_idtest_seq'::text);
INSERT INTO pg_depend(classid, objid, objsubid, refclassid, refobjid,
refobjsubid, deptype)
SELECT cl.oid, seq.oid, 0, cl.oid, 54592:id, attnum, 'i'
FROM pg_class cl, pg_attribute, pg_class seq
JOIN pg_namespace sn ON sn.OID=seq.relnamespace
WHERE cl.relname='pg_class'
AND seq.relname='categories_idtest_seq'
AND sn.nspname='public'
AND attrelid=54592:id AND attname='idtest';

the insert into pg_depend is an obvious candidate for the problem, since
i don't have the proper permissions to insert into the pg_depend table. (the
database was created for me by my hosting provider, and the pg_depend
table is owned by pgsql, not by my id)

now, normally i'd assume it was a permissions problem with pg_depend,
except that manually adding the table using the following command (as
executed through the pgadmin query tool) works fine:

ALTER TABLE categories ADD COLUMN idtest bigserial

the appropriate sequence is autocreated, and the appropriate entry appears
to get dumped into pg_depend with no errors. (i can delete the column and
the sequence is dropped automatically, again with no errors)

- is this a pgadmin3 bug, or purely a configuration problem?

- if it's a configuration problem, why does the query-tool version work?
shouldn't pgadmin be spitting out the shorthand version and letting
postgres do the sequence creation and pg_depend work?

thanks,

-cks


Christopher St. John
Chief Software Architect
http://www.homeconductor.com
http://artofsystems.blogspot.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Christopher St John wrote:
> i'm using pgadmin3 v1.2.2 on os x against a postgres 8.0.3 server to
> add a bigserial column to a table. i'm getting the error:
>
> ERROR: permission denied for relation pg_depend

OK, your diagnose is quite right, missing permissions; we should check
that before trying to add the dependency.
In addition, this is code from early days, apparently adding a serial is
now supported in 8.0, I missed that. Still, since pgAdmin supports
adding arbitrary sequences too, we can't drop the pg_dependency stuff
completely.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Christopher St John wrote:
> i'm using pgadmin3 v1.2.2 on os x against a postgres 8.0.3 server to
> add a bigserial column to a table. i'm getting the error:
>
> ERROR: permission denied for relation pg_depend

Ok, you're missing write privileges on pg_depend; we didn't check that.
I added code to svn to support this, thanks for reporting.

Apparently, after the initial implementation of pgAdmin's add column
code (for 7.3), adding serial and bigserial to existing tables has been
implemented; formerly, this didn't work.
Dave, I only have 8.0+ servers running, can you check when ALTER TABLE
xxx ADD COLUMN xxx serial was introduced, for 7.4 or 8.0?

In the meanwhile, dlgColumn.cpp:285 assumes pgsql>= 8.0.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq