Thread: Referencing to system catalog problem

Referencing to system catalog problem

From
"Davor J."
Date:
I am logged in as superuser. I am trying to create something similar to
this:


Code:
CREATE TABLE tbl_unit_convfunctions(
unit_from integer REFERENCES tbl_units (unit_id),
unit_to integer REFERENCES tbl_units (unit_id),
proc_id oid REFERENCES pg_proc (oid)
)but no matter what I refer to from pg_proc, i get the error message:

ERROR: permission denied: "pg_proc" is a system catalog
SQL state: 42501

Has anyone any suggestions how to do something similar, or even better: how
to solve this error. I couldn't find any useful information on the net about
this issue.

Thanks,
Davor

Original post:
http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog-problem-670063.html

Note: OID's are unique in that table, and should be referable, and I
explicitely granted the REFERENCE priviledge to the superuser.




Re: Referencing to system catalog problem

From
Adrian Klaver
Date:
On Saturday 23 January 2010 6:15:36 am Davor J. wrote:
> I am logged in as superuser. I am trying to create something similar to
> this:
>
>
> Code:
> CREATE TABLE tbl_unit_convfunctions(
> unit_from integer REFERENCES tbl_units (unit_id),
> unit_to integer REFERENCES tbl_units (unit_id),
> proc_id oid REFERENCES pg_proc (oid)
> )but no matter what I refer to from pg_proc, i get the error message:
>
> ERROR: permission denied: "pg_proc" is a system catalog
> SQL state: 42501
>
> Has anyone any suggestions how to do something similar, or even better: how
> to solve this error. I couldn't find any useful information on the net
> about this issue.
>
> Thanks,
> Davor
>
> Original post:
> http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog-
>problem-670063.html
>
> Note: OID's are unique in that table, and should be referable, and I
> explicitely granted the REFERENCE priviledge to the superuser.


You can't have FKs to system tables. See this post for explanation:
http://archives.postgresql.org/pgsql-general/2004-12/msg00840.php

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Referencing to system catalog problem

From
"Davor J."
Date:
Thank you Adrian. I apparently missed that post. Guess I will have to come
up with an different approach.

"Adrian Klaver" <adrian.klaver@gmail.com> wrote in message
news:201001231002.15874.adrian.klaver@gmail.com...
> On Saturday 23 January 2010 6:15:36 am Davor J. wrote:
>> I am logged in as superuser. I am trying to create something similar to
>> this:
>>
>>
>> Code:
>> CREATE TABLE tbl_unit_convfunctions(
>> unit_from integer REFERENCES tbl_units (unit_id),
>> unit_to integer REFERENCES tbl_units (unit_id),
>> proc_id oid REFERENCES pg_proc (oid)
>> )but no matter what I refer to from pg_proc, i get the error message:
>>
>> ERROR: permission denied: "pg_proc" is a system catalog
>> SQL state: 42501
>>
>> Has anyone any suggestions how to do something similar, or even better:
>> how
>> to solve this error. I couldn't find any useful information on the net
>> about this issue.
>>
>> Thanks,
>> Davor
>>
>> Original post:
>> http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog-
>>problem-670063.html
>>
>> Note: OID's are unique in that table, and should be referable, and I
>> explicitely granted the REFERENCE priviledge to the superuser.
>
>
> You can't have FKs to system tables. See this post for explanation:
> http://archives.postgresql.org/pgsql-general/2004-12/msg00840.php
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>