Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHlAk-00AaUY-Du for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 14:11:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sHlAi-001wqn-0h for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 14:11:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHlAh-001whw-Ay for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 14:11:24 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sHlAe-0018Ao-Qh for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 14:11:22 +0000 Received: by mail-ot1-x329.google.com with SMTP id 46e09a7af769-6f8ef63714cso553292a34.1 for ; Thu, 13 Jun 2024 07:11:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=istation.com; s=google; t=1718287880; x=1718892680; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=bseQxwOfLOeYLT+Onf4V6wqYZZiSnpXG7pxEV0d8XuY=; b=Ea6EDwiWJRxX0Ejm/fsNdLVwd2RUwmLh/d0Wui98GWg3c7hKJJKoAv7bMaNHTFcUB4 LSnkHNrRW9fK+SZYWhFZUd9BCw35DkycLcXAgdfzfuP4LLCRIeEbgJ7s1bF5oioyK+T2 QnxPjMEu+3eyshVYGsl3k5i8DhsFNCI2Jg7cY= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718287880; x=1718892680; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=bseQxwOfLOeYLT+Onf4V6wqYZZiSnpXG7pxEV0d8XuY=; b=MhsmGcgwZVUoDFlhicNL8m+HDP5W1QutFhtTPProQ2CZ9AWsstIy8Irh8iVRj8tR19 s8bHD8TTIh9dboVu9iZnPA/ftY3wjLTaCzusLVKo0mg+tgpvAY/rH1CoOEwVpLspOsME W/pZ3hcmr957Zh0GFi/RDEQNqcRr9DyKudWp2wiAelHt9XSmv70Q0aGH+OMUzkXDFIeK 8ieZqYSd0xF5g61BjGCC/jwg+su1mAzuE4/Vk14ZMuk8fXW2DMbga6tQwJub7NYvsg5B tDgu6HJm/wr6C667S8mUsnQ1mNaI5ThXOMsfD1yQVimO8Dko128u92PF5BnOBVErD9p8 N51Q== X-Gm-Message-State: AOJu0Yz03oWUWWdUCIRT8iNsJWCZQAjJdlMc2QcDoGSdGoWNVF54zjsS wOX4mFgmcJCswtU7Bb6jbSo9YGY2OrU1boF3PZrv54a/7wLC+EcGJyf/ZnMPo94sJN+wJ092Yaw 8TRuAcm9AtwNhMPX5pqAgMWXD63acg+SakdRfeFB8pVCGZtA= X-Google-Smtp-Source: AGHT+IE1IXNFogZsGg7l0w1C+C+P2/SS+PrfyW4yxQYHY2Sb2abTWViZLwbonPCHJ94MujAdhqFhpVLQySBnhvYirOs= X-Received: by 2002:a05:6870:180d:b0:251:2685:c9f5 with SMTP id 586e51a60fabf-25514c703d1mr5762285fac.21.1718287879548; Thu, 13 Jun 2024 07:11:19 -0700 (PDT) MIME-Version: 1.0 From: David Barbour Date: Thu, 13 Jun 2024 09:11:01 -0500 Message-ID: Subject: Syntax on BEFORE Trigger - Cascade? To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000aeca39061ac6123e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000aeca39061ac6123e Content-Type: text/plain; charset="UTF-8" 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 * *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* *dbarbour@istation.com * *(214) 292-4096* Istation 8150 North Central Expressway, Suite 2000 Dallas, TX 75206 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. --000000000000aeca39061ac6123e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Good Morning,

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

The ch= ild table has a foreign key column called import_job_oid refe= rencing the primary key in import_file.

Whe= n a record in import_job is deleted, the child records (file records= ) in import_file need to be deleted first.=C2=A0 =C2=A0
The constraint in both Oracle and Postgres is similar (Postgre= s version):
ALTER TABLE IF EXI= STS idev.import_file
=C2=A0 = =C2=A0 ADD CONSTRAINT fk1_import_file FOREIGN KEY (import_job_oid)
=C2= =A0 =C2=A0 REFERENCES idev.import_job (oid) MATCH SIMPLE
=C2=A0 =C2=A0 O= N UPDATE NO ACTION
=C2=A0 =C2=A0 ON DELETE CASCADE;
=

The files are appropriately deleted in Oracle, but Postgre= s is returning the following:
ERROR: Attempt to suppress referential action with bef= ore trigger. CONTEXT: SQL statement "DELETE FROM ONLY "idev"."impor= t_file" WHERE $1 OPERATOR(pg_catalog.=3D) "import_job_oid"&q= uot;

There aren't any delete triggers for either table. Any idea wh= y 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 w= ith 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 ps= ql delete picked up by the trigger.

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

=
CREATE OR REPLACE FUNCTION idev."td_import_job$import= _job"() RETURNS trigger LANGUAGE 'p= lpgsql' VOLATILE NOT LE= AKPROOF AS $BODY$ BEGIN RAISE NOTICE '= Value %', new.oid DELETE FROM ide= v.import_file =20 WHERE impor= t_job_oid =3D new.oid; RETURN OLD; END; $BODY$; delete from idev.im= port_job where oid =3D 44949; NOTICE: Value <= NULL> ERROR: Attempt to = suppress referential action with before trigger. CON= TEXT: SQL statement "DELETE FROM ONLY "idev"."import_f= ile" WHERE $1 OPERATOR(pg_catalog.=3D) "import_job_oid"=C2=A0=C2=A0


<= br clear=3D"all">

-- <= /span>

David A. Barbour

dbarbour@istation.com

(214) 292-4096

Istation

8150 North Central Expr= essway, Suite 2000

Dallas, TX 75206

www.Istation.com

=C2=A0

CONFIDENTIALITY / PROPRIETARY NOTICE:

= The information contained in this e-mail, including any attachment(s), is c= onfidential information that may be privileged and exempt from disclosure u= nder applicable law. If the reader of this message is not the intended reci= pient, or if you received this message in error, then any direct or indirec= t disclosure, distribution or copying of this message is strictly prohibite= d. If you have received this message in error, please notify Istation <= span style=3D"font-size:8pt;font-family:Arial;background-color:transparent;= font-weight:700;font-style:italic;vertical-align:baseline;white-space:pre-w= rap">by calling 866-883-7323= immediately and by sending a return e-mail; delete this message; and destr= oy all copies, including attachments.

Thank you.

--000000000000aeca39061ac6123e--