Обсуждение: blocking referencing system catalogs in 8.4 breaks my code

In testing my software on postgresql-8.4beta[12] I got
   ERROR:  42501: permission denied: "pg_namespace" is a system catalog   LOCATION:  ATAddForeignKeyConstraint,
tablecmds.c:4599

from my code which has a column   schema_name name NOT NULL REFERENCES pg_namespace ON DELETE CASCADE

this despite PostgreSQL happily accepting:   GRANT REFERENCES ON pg_namespace TO greg;

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?

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?

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.

BTW, I'm loving most of what I see in 8.4,

_Greg

J. Greg Davidson

"J. Greg Davidson" <jgd@well.com> writes:
> from my code which has a column
>     schema_name name NOT NULL REFERENCES pg_namespace ON DELETE CASCADE

You can't do that; it never has worked and is not likely to work in the
foreseeable future, because we don't support triggers on system catalogs.
Perhaps the system failed to alert you to the fact before, but if it
does so now, that's not a "regression".
        regards, tom lane

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

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
>