Thread: Constraints involving a system table

Hello,

I would like to create a constraint that involves the system table.

CREATE TABLE metadata_tables
(
   schemaname text REFERENCES pg_namespace(nspname) ON DELETE CASCADE
CONSTRAINT valid_schema CHECK (schemaname != 'public' AND schemaname !=
'information_schema' AND schemaname NOT LIKE 'pg_%'),
   tablename text,
   source text
) WITH (OIDS=FALSE)
;

Basically, I only want to allow that the user can use schemanames present
in the current database as input in the column schemaname.

When I try to create this table, I get an error that it is a system table.

ERROR:  permission denied: "pg_namespace" is a system catalog

However, when looking at the system table definition, it has "GRANT SELECT
ON TABLE pg_namespace TO public;" - so everybody should be able to query a
system table.

Why is this not working? What other privileges besides "SELECT" would one
need to define a constraint?

Thanks for any hint,
Andreas

--
Andreas Neumann
Böschacherstrasse 6, CH-8624 Grüt/Gossau, Switzerland
Email: a.neumann@carto.net, Web:
* http://www.carto.net/ (Carto and SVG resources)
* http://www.carto.net/neumann/ (personal page)
* http://www.svgopen.org/ (SVG Open Conference)
* http://www.geofoto.ch/ (Georeferenced Photos of Switzerland)

ok - so the trigger is the problem. I am aware that one should not mess
around with system tables.

is there a workaround? Maybe creating a view or a function?

Thanks,
Andreas

Richard Huxton wrote:
> Andreas Neumann wrote:
>> Hello,
>>
>> I would like to create a constraint that involves the system table.
>
> I'm afraid you can't (at present, anyway)
>
>> Why is this not working? What other privileges besides "SELECT" would one
>> need to define a constraint?
>
> Well, you need to be able to add a trigger to the system table (which
> you can't do), otherwise it can't check what to do when you delete
> something from pg_namespace.
>
> I think there are two reasons for this:
> 1. Adding a badly coded trigger to a system table can make your life
> very difficult.
> 2. PG uses a few shortcuts behind the scenes when accessing system tables.
>
>

--

--
Andreas Neumann
Böschacherstrasse 6
CH-8624 Grüt (Gossau ZH)
Switzerland
Phone: ++41-44-2736668
Email: a.neumann@carto.net

Web: http://www.carto.net/neumann/
SVG Examples: http://www.carto.net/papers/svg/samples/
SVG.Open: http://www.svgopen.org/