Обсуждение: Delete cascade and trigger permissions?

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
notthe addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or
copyingof this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error. 

On Thu, Jul 06, 2006 at 02:06:46PM -0400, Brennan, Sean (IMS) wrote:
> 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!

I ran a test based on this description without any trouble.  Could
you post a minimal but complete test case that demonstrates the
problem?  That is, all SQL statements that somebody could execute
in an empty database to see the same behavior you're seeing.  Also,
what version of PostgreSQL are you running?

--
Michael Fuhr