Thread: 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 >