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 1sHlON-00AbyS-FQ for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 14:25:31 +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 1sHlOL-002A4j-83 for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 14:25:30 +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 1sHlOK-0029yk-O7 for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 14:25:29 +0000 Received: from mail-ot1-x332.google.com ([2607:f8b0:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sHlOJ-0018GV-4I for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 14:25:28 +0000 Received: by mail-ot1-x332.google.com with SMTP id 46e09a7af769-6f8ffe1b65dso541864a34.0 for ; Thu, 13 Jun 2024 07:25:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718288726; x=1718893526; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ia7Vfsh30LAZ3Nh7sz1FtUxpODzsrWxUE1qiY3dfuUY=; b=U2zSIwfwAcn2R6uh0KjQUQ4Z6kJQD2F+mvofcD0+fdWlNGVNUuBe33HtI01bTVwjf5 YnOTIOJJlatDl33dGOqEblbXj0WPu/+5MQbV2pkcWK9hikypBCq5x0tscECsMcxc0u2m uNV2gWemcUJkY1DI6zP/feq5/DPyEjwb4TRPIeotsS7uJZ7S9yxGa3sf2tPUj2o++cTd gJ1aGhr82QJsN4mKrnqwmZ6s9IccUD8bwNUX+JZPOQ+A39GEIMiTfCnfDwqpErpQoAzl SF3QxbVFJsx+AfgUqg1IiIGPPuTefShZC0RqHqGQLkDZ1Atjio1Isb6TLrVB17uhVcCk dj6A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718288726; x=1718893526; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ia7Vfsh30LAZ3Nh7sz1FtUxpODzsrWxUE1qiY3dfuUY=; b=PELeQtntcwf27+puTzNWCA/iBDPqsodly4doxo9RA5mhepdnyNUyKYmxRR1MwmkAeG dXqNQukiMzxRpCd+jXa3yT64+luWua5+CIrgVRvYnJB805AzEkD14dEVnFx+oFBlO/Ht zUrkwHdG7/Z/Hdrt8sTeH4rS3hTl2w6nfH0RSP8miq7JivSLDne4Xocb6djfQFXFRkIN tlE51QtGTsUED3N2SPLxbtS94zytk9Nb5dJJ0qvNimQv+MuV5ZhSZRC0tX0Lq/yFOLpN c+Y4aEltSiOdpbRpiwXycN30TgjkRGq/Rvbn97lrJ20k7FqlseYRdGzlYjK6Vxy8uTn5 /ioQ== X-Gm-Message-State: AOJu0YyIWurcDOKC7V2gsqWrBv3nsuo1eouo1QCAKO64YV/iSjyRIuV7 jZWN2axvkzq/eb4lSRyDJ6ANchSGoZrHojZTRz2V+eheeVb9gpcNJiKIrzKAvtkTUC5ERdlKEjZ xufMp1VcJ3fStp6quPOrKl88Pl+U= X-Google-Smtp-Source: AGHT+IGmefHulOuJEnkjwxCGhjUvNJVeyM6tz2ZgPQCR+Gb4IOG1A2n/16kE12EU20r3WGJ/VeIr7MXdaIc0XfsDrNo= X-Received: by 2002:a9d:674a:0:b0:6f9:5b3e:845e with SMTP id 46e09a7af769-6fae2377f8emr1475806a34.19.1718288725814; Thu, 13 Jun 2024 07:25:25 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:6802:387:b0:530:392d:d678 with HTTP; Thu, 13 Jun 2024 07:25:25 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Thu, 13 Jun 2024 07:25:25 -0700 Message-ID: Subject: Re: Syntax on BEFORE Trigger - Cascade? To: David Barbour Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000001fb81e061ac64540" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001fb81e061ac64540 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, June 13, 2024, David Barbour wrote: > > 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 O= N > DELETE CASCADE;* > This, by itself, should work. If it isn=E2=80=99t, please provide a self-c= ontained test case demonstrating that fact so it can be investigated/explained. What version are you running? > There aren't any delete triggers for either table. Any idea why this isn'= t > 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. > > 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: > Delete only populates OLD. David J. --0000000000001fb81e061ac64540 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, June 13, 2024, David Barbour <dbarbour@istation.com> wrote:

When a record in import_job= is deleted, the child records (file records) in import_file nee= d to be deleted first.=C2=A0 =C2=A0

The constraint= in both Oracle and Postgres is similar (Postgres version):
ALTER TABLE IF EXISTS idev.import_file
=C2=A0 =C2=A0 ADD CONSTRAINT fk1_im= port_file FOREIGN KEY (import_job_oid)
=C2=A0 =C2=A0 REFERENCES idev.imp= ort_job (oid) MATCH SIMPLE
=C2=A0 =C2=A0 ON UPDATE NO ACTION
=C2=A0 = =C2=A0 ON DELETE CASCADE;

This, by itself, shoul= d work.=C2=A0 If it isn=E2=80=99t, please provide a self-contained test cas= e demonstrating that fact so it can be investigated/explained.
What version are you running?
=C2=A0
There aren't a= ny delete triggers for either table. Any idea why this isn't working? = Does cascade function differently in Postgres?

=C2=A0
Nope (to both)
=C2=A0

I'= ve also tried creating a before trigger on import_job,=C2=A0

Why?
=C2=A0
=
<= br>
but can't seem to get the right syntax f= or 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.
=C2=A0
<= br>
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:

Del= ete only populates OLD.

David J.

--0000000000001fb81e061ac64540--