Syntax on BEFORE Trigger - Cascade?

[David Barbour]

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 SIMPLEON UPDATE NO ACTIONON
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*

*dbarb...@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.

Re: Oracle to Postgres - Transform Hash Partition - Thanks!

[David Barbour]

First post to this list, and am sure it won't be my last, but you guys rock!

Took a bit from all three replies and got the transformation working
seamlessly.
Created temp table.
Copied data into temp from original (just in case!)
Created partitioned table using modulus 8.
Imported the data into the partitioned table.
Dropped the original table and renamed the partitioned table.

Easy, no problems.  Also no 'attach'.

Thanks.

On Fri, Jun 7, 2024 at 3:31 AM Laurenz Albe 
wrote:

> On Thu, 2024-06-06 at 11:28 -0500, David Barbour wrote:
> > Been an Oracle DBA for quite a while and we're moving from Oracle to
> Postgres.
> >
> > I have a table that I need to transform into a hash partitioned table.
> > I've gone through all the steps to create a hash partitioned table, with
> 8 partitions such as
> >
> > create table idev.assessment_result_2023_dab_part (like
> idev.assessment_result_2023_dab)partition by hash (district_oid);
> >
> > CREATE TABLE
> idev.assessment_result_2023_dab_part_assessment_result_2023_p1 PARTITION OF
> idev.assessment_result_2023_dab_part
> > FOR VALUES WITH (modulus 64, remainder 0)
> >
> > etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remainder
> 7)
>
> That won't do.  If you use a modulus of 64, you need 64 partitions, one for
> each possible division remainder.
>
> If you want 8 partitions, you have to use modulus 8.
>
> > Now I need to 'attach' the original table.  The problem I'm running into
> is
> > there are no good examples of how to define the values.
> >
> > I've tried several iterations of various 'for values', 'values', 'for
> values with',
> > etc. but they all error out.
> >
> > Here's an example:
> >  alter table idev.assessment_result_2023_dab_part
> >  attach partition idev.assessment_result_2023_dab for values
> with(modulus 8, remainder 1) to (modulus 8, remainder 7)
> >
> > ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values
> with(modulus 8, remainder 1) to (modulu...
>
> I think you are confused about hash partitioning.
>
> One partition is only for a single remainder.  You cannot have a partition
> for
> several remainders.
>
> Hash partitioning is mostly for splitting up a table into several parts of
> roughly equal size.  You decide how many partitions you want; that will
> become
> the modulus.  Then you have to create that many partitions, one for each
> remainder.
>
> If you want to attach an existing table as a partition, that will only
> work if
> all rows in the table belong into that partition.  Otherwise, you will get
> an
> error.
> So you typically won't be able to attach an existing table as a hash
> partition.
>
> To convert an existing table into a hash partitioned table, you have to
>
> - create a new, empty partitioned table with *all* its partitions
>
> - transfer the data with "INSERT INTO hash_part_tab SELECT * FROM tab"
>
> Yours,
> Laurenz Albe
>


-- 

*David A. Barbour*

*dbarb...@istation.com *

*(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.

Oracle to Postgres - Transform Hash Partition

[David Barbour]

Hi,

New to this list and to Postgres.  Been an Oracle DBA for quite a while and
we're moving from Oracle to Postgres.

I have a table that I need to transform into a hash partitioned table.
I've gone through all the steps to create a hash partitioned table, with 8
partitions such as

create table idev.assessment_result_2023_dab_part (like
idev.assessment_result_2023_dab)partition by hash (district_oid);

CREATE TABLE idev.assessment_result_2023_dab_part_assessment_result_2023_p1
PARTITION OF idev.assessment_result_2023_dab_part
FOR VALUES WITH (modulus 64, remainder 0)

etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remainder 7)

Now I need to 'attach' the original table.  The problem I'm running into is
there are no good examples of how to define the values.

I've tried several iterations of various 'for values', 'values', 'for
values with', etc. but they all error out.

Here's an example:
 alter table idev.assessment_result_2023_dab_part
attach partition idev.assessment_result_2023_dab for values with(modulus 8,
remainder 1) to (modulus 8, remainder 7)

ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values
with(modulus 8, remainder 1) to (modulu...

Any assistance would be appreciated.
-- 

*David A. Barbour*

*dbarb...@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.