Re: blocking referencing system catalogs in 8.4 breaks my code

Hi Josh,

I think that any special "features" common to all on system tables would
go nicely near the top of 44.1 "System Catalogs Overview". That plus a
specific error message, not just a "PERMISSION DENIED", so that someone
like me can put the error message in the PostgreSQL.org search box to
find relevant documentation.

If referencing system tables was always unreliable, I'm glad that it is
now forbidden!

I've now worked around the problem by creating my own schemas table
along with functions create_schema and drop_schema which insert and
delete a referenceable row in my own table as they create and drop a
system schema.

I've been delighted to see a number of irregularities and exceptions
disappear as PostgreSQL evolves, leaving the system simpler as well as
better. Perhaps at some point someone more knowledgeable than me will
see a way to eliminate this limitation. "Information wants to be
referenceable."

Thanks for the help,

_Greg

On Fri, 2009-06-05 at 15:34 -0700, Josh Berkus wrote:
> Greg,
>
> > this despite PostgreSQL happily accepting:
> > GRANT REFERENCES ON pg_namespace TO greg;
>
> We should probably error out on this.
>
> > I found the change in tablecmds.c and a mention of it in PostgreSQL
> > Weekly News - May 17 2009, yet I don't understand why referencing
> > a system table in this manner should be a problem - is it?
>
> System tables have special properties which can make modifications to
> them behave non-transactionally. So it's never safe to have a trigger
> on a system table. I'm frankly surprised that this worked for you before.
>
> > I'm trying to have certain rows of my tables go away if certain
> > schemas are dropped. Is there a preferred way to do this?
>
> I can't off the top of my head think of a "preferred way".
>
> > If this regression is retained, it needs to be documented in the
> > reference manual AND I think that an attempt to GRANT REFERENCES
> > privileges to a system table should not be allowed.
>
> I agree that GRANT REFERENCES should error. Do you have a suggestion
> where in the manual would be a good place to warn people that FKs
> against the system tables are a bad idea?
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> www.pgexperts.com
>