Subject: Syntax on BEFORE Trigger - Cascade?

From: David Barbour <[email-masked]>
Date: Thu, 13 Jun 2024 09:11:01 -0500

Good Morning,

We have a table - I'll call it *import_job* (which is the actual name) -
that lists jobs to be executed.  Each job has one or more child components
listed in another table called *import_file*.

The child table has a foreign key column called *import_job_oid*
referencing the primary key in *import_file*.

When a record in *import_job* is deleted, the child records (file records)
in *import_file* need to be deleted first.

The constraint in both Oracle and Postgres is similar (Postgres version):
*ALTER TABLE IF EXISTS idev.import_file*

*    ADD CONSTRAINT fk1_import_file FOREIGN KEY (import_job_oid)
REFERENCES idev.import_job (oid) MATCH SIMPLE    ON UPDATE NO ACTION    ON
DELETE CASCADE;*

The files are appropriately deleted in Oracle, but Postgres is returning
the following:
*ERROR: Attempt to suppress referential action with before trigger.
CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1
OPERATOR(pg_catalog.=) "import_job_oid"" *

There aren't any delete triggers for either table. Any idea why this isn't
working? Does cascade function differently in Postgres? Read the docs,
Googled the heck out of this and played all sorts of games with the tables.
I've also tried creating a before trigger on import_job, but can't seem to
get the right syntax for taking the oid from the psql delete picked up by
the trigger.

Here is one of my (many) attempts (have tried describing, setting, using
new.oid, old.oid, a bunch of stuff) and can't get this right either:

*CREATE OR REPLACE FUNCTION idev."td_import_job$import_job"() ** RETURNS
trigger ** LANGUAGE 'plpgsql' ** VOLATILE NOT LEAKPROOF **AS $BODY$ * *BEGIN
** RAISE NOTICE 'Value %', new.oid ** DELETE FROM idev.import_file ** WHERE
import_job_oid = new.oid; **RETURN OLD; **END; **$BODY$; * *delete from
idev.import_job where oid = 44949; * *NOTICE: Value <NULL> * *ERROR:
Attempt to suppress referential action with before trigger. *
*CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1
OPERATOR(pg_catalog.=) "import_job_oid"  *

-- 

*David A. Barbour*

[email-masked] <[email-masked]>*

*(214) 292-4096*

Istation

8150 North Central Expressway, Suite 2000

Dallas, TX 75206

www.Istation.com <http://www.istation.com/>

CONFIDENTIALITY / PROPRIETARY NOTICE:

The information contained in this e-mail, including any attachment(s), is
confidential information that may be privileged and exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient, or if you received this message in error, then any direct or
indirect disclosure, distribution or copying of this message is strictly
prohibited. If you have received this message in error, please notify
Istation by calling 866-883-7323 immediately and by sending a return
e-mail; delete this message; and destroy all copies, including attachments.

Thank you.

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 13 Jun 2024 07:25:25 -0700

On Thursday, June 13, 2024, David Barbour <[email-masked]> wrote:

>
> When a record in *import_job* is deleted, the child records (file
> records) in *import_file* need to be deleted first.
>

This, by itself, should work.  If it isn’t, please provide a self-contained
test case demonstrating that fact so it can be investigated/explained.

What version are you running?

> working? Does cascade function differently in Postgres?
>
>
Nope (to both)

>
> I've also tried creating a before trigger on import_job,
>

Why?

>
> but can't seem to get the right syntax for taking the oid from the psql
> delete picked up by the trigger.
>

Your broken attempt to do this is likely what is causing the error.

> new.oid, old.oid, a bunch of stuff) and can't get this right either:

Delete only populates OLD.

David J.