Delete cascade and trigger permissions?

From: "Brennan, Sean (IMS)" <BrennanS(at)imsweb(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Delete cascade and trigger permissions?
Date: 2006-07-06 18:06:46
Message-ID: 3B3D34578CDD4D40895983184B36E62B17D4A9@titanium.omni.imsweb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I seem to have a problem accessing a temp table from a trigger when I
use cascading deletes.

I have 2 tables: a main table and a child table. The child table has a
foreign key reference to the main table that has DELETE CASCADE on.

I have delete triggers on both tables that are run BEFORE DELETE.

All in the same db connection, I do the following:
1)create a temp table
2)delete rows from the main table
3)access the temp table in the delete trigger of the main table
4)access the temp table in the delete trigger of the child table -
FAILS!

When the trigger for the child table attempts to access the temp table,
it says "permission denied for relation temp_table".

ERROR: permission denied for relation temp_table
CONTEXT: SQL statement "SELECT field from temp_table"
PL/pgSQL function "trigger_child_delete" line 7 at select into variables
SQL statement "DELETE FROM ONLY "public"."child" WHERE "main_id" = $1"

Any thoughts? This is the only place we use cascading deletes in our
system so we haven't experienced this before. Our permissions are the
same on all triggers and all tables.

Thanks in advance.


--------------------------------------------------------

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Florian G. Pflug 2006-07-06 18:13:00 Re: Backing up the currently used wal segment
Previous Message Stephen Frost 2006-07-06 18:04:25 Re: Backing up the currently used wal segment