Let us learn Oracle Database Administration skills: Part 3: Steps to move data from a non partitioned table to partitioned table in postgresql

Wednesday, April 5, 2023

Part 3: Steps to move data from a non partitioned table to partitioned table in postgresql

Objective: Steps to move a nonpartition table to partition table with indexes & referential constraints in postgresql

Notice unlike part 1 and 2, the referential constraint make a real code change. Kindly read the blog to understand further..

A) Table Setup:

primary table:

create table test_idxfk as
with t as
(
select generate_series(0,100) lvl
)
select t.lvl
,(mod(t.lvl,100)+date '1/1/2022') as dob
,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
,substr(md5(random()::text),15) as fn
,substr(md5(random()::text),15) as ln
,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as POSCODE
from t;

alter table test_idxfk add constraint test_idxfk_lvl_pk primary key (lvl);

child table:

create table test_idxfk_child as
with t as
(
select generate_series(0,100) lvl
)
select t.lvl
,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as HOSTLCODE
from t;

alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);

analyze verbose test_idxfk;
analyze verbose test_idxfk_child;

Output:

postgres=# create table test_idxfk as
postgres-# with t as
postgres-# (
postgres(# select generate_series(0,100) lvl
postgres(# )
postgres-# select t.lvl
postgres-# ,(mod(t.lvl,100)+date '1/1/2022') as dob
postgres-# ,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
postgres-# ,substr(md5(random()::text),15) as fn
postgres-# ,substr(md5(random()::text),15) as ln
postgres-# ,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as POSCODE
postgres-# from t;
SELECT 101
Time: 17.071 ms

postgres=# alter table test_idxfk add constraint test_idxfk_lvl_pk primary key (lvl);
ALTER TABLE
Time: 8.048 ms
postgres=#

postgres=# create table test_idxfk_child as
postgres-# with t as
postgres-# (
postgres(# select generate_series(0,100) lvl
postgres(# )
postgres-# select t.lvl
postgres-# ,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
postgres-# ,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as HOSTLCODE
postgres-# from t;
SELECT 101
Time: 8.698 ms

postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);
ALTER TABLE
Time: 6.412 ms
postgres=#

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test_idxfk%' order by relname;
relname | relpages | reltuples | relallvisible | relispartition
-------------------+----------+-----------+---------------+----------------
test_idxfk | 2 | 101 | 0 | f
test_idxfk_child | 1 | 101 | 0 | f
test_idxfk_lvl_pk | 2 | 101 | 0 | f
(3 rows)
Time: 0.680 ms

B) In part 1 we already identified the partition key, hence we will proceed with hosptl column as our partition key.

C) Let us collect dependency diagram (ER), we have a table dependency now because of the introduction of the referential constraint.

D) Let us get the source table ddl

/usr/pgsql-14/bin/pg_dump --table='public.test_idxfk' --username=postgres --dbname=postgres --schema-only --file=/tmp/test_idxfk_05apr23.sql --verbose

-bash-4.2$ /usr/pgsql-14/bin/pg_dump --table='public.test_idxfk' --username=postgres --dbname=postgres --schema-only --file=/tmp/test_idxfk_05apr23.sql --verbose
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
...
pg_dump: creating TABLE "public.test_idxfk"
pg_dump: creating CONSTRAINT "public.test_idxfk test_idxfk_lvl_pk"

-bash-4.2$

Output:

-- TOC entry 245 (class 1259 OID 16735)
-- Name: test_idxfk; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.test_idxfk (
lvl integer NOT NULL,
dob date,
hosptl text,
fn text,
ln text,
poscode text
);

ALTER TABLE public.test_idxfk OWNER TO postgres;
--
-- TOC entry 4007 (class 2606 OID 16741)
-- Name: test_idxfk test_idxfk_lvl_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.test_idxfk
ADD CONSTRAINT test_idxfk_lvl_pk PRIMARY KEY (lvl);

-- Completed on 2023-04-05 16:14:05 BST

E) Let us create the table...

CREATE TABLE public.test_part_idxfk (
lvl integer,
dob date,
hosptl text,
fn text,
ln text,
poscode text
) partition by list(hosptl);
CREATE TABLE public.test_part_idxfk_a partition of public.test_part_idxfk for values in ('AAAAAAAAAAAAAAAAAAAA');
CREATE TABLE public.test_part_idxfk_b partition of public.test_part_idxfk for values in ('BBBBBBBBBBBBBBBBBBBA');
CREATE TABLE public.test_part_idxfk_c partition of public.test_part_idxfk for values in ('CCCCCCCCCCCCCCCCCCCA');
CREATE TABLE public.test_part_idxfk_d partition of public.test_part_idxfk for values in ('DDDDDDDDDDDDDDDDDDDA');
CREATE TABLE public.test_part_idxfk_default partition of public.test_part_idxfk default;

Output:
postgres=# CREATE TABLE public.test_part_idxfk (
postgres(# lvl integer,
postgres(# dob date,
postgres(# hosptl text,
postgres(# fn text,
postgres(# ln text,
postgres(# poscode text
postgres(# ) partition by list(hosptl);
CREATE TABLE
Time: 6.714 ms
postgres=# CREATE TABLE public.test_part_idxfk_a partition of public.test_part_idxfk for values in ('AAAAAAAAAAAAAAAAAAAA');
CREATE TABLE public.test_part_idxfk_default partition of public.test_part_idxfk default;CREATE TABLE
Time: 9.639 ms
postgres=# CREATE TABLE public.test_part_idxfk_b partition of public.test_part_idxfk for values in ('BBBBBBBBBBBBBBBBBBBA');
CREATE TABLE
Time: 9.963 ms
postgres=# CREATE TABLE public.test_part_idxfk_c partition of public.test_part_idxfk for values in ('CCCCCCCCCCCCCCCCCCCA');
CREATE TABLE
Time: 14.759 ms
postgres=# CREATE TABLE public.test_part_idxfk_d partition of public.test_part_idxfk for values in ('DDDDDDDDDDDDDDDDDDDA');
CREATE TABLE
Time: 8.914 ms
postgres=# CREATE TABLE public.test_part_idxfk_default partition of public.test_part_idxfk default;
CREATE TABLE
Time: 6.651 ms
postgres=#

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test%idxfk%' order by relname;
relname | relpages | reltuples | relallvisible | relispartition
-------------------------+----------+-----------+---------------+----------------
test_idxfk | 2 | 101 | 0 | f
test_idxfk_child | 1 | 101 | 0 | f
test_idxfk_lvl_pk | 2 | 101 | 0 | f
test_part_idxfk | 0 | -1 | 0 | f
test_part_idxfk_a | 0 | -1 | 0 | t
test_part_idxfk_b | 0 | -1 | 0 | t
test_part_idxfk_c | 0 | -1 | 0 | t
test_part_idxfk_d | 0 | -1 | 0 | t
test_part_idxfk_default | 0 | -1 | 0 | t
(9 rows)
Time: 0.513 ms

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test%idxfk%' order by relname;
relname | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
-------------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
test_idxfk | 3 | 303 | 101 | 0 | 0 | 101 | 0
test_idxfk_child | 1 | 101 | 101 | 0 | 0 | 101 | 0
test_part_idxfk | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_idxfk_a | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_idxfk_b | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_idxfk_c | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_idxfk_d | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_idxfk_default | 0 | 0 | 0 | 0 | 0 | 0 | 0
(8 rows)
Time: 12.640 ms
postgres=#

F) Let us copy the data.

copy test_idxfk to '/tmp/test_idxfk_05apr23.dmp';
copy test_part_idxfk from '/tmp/test_idxfk_05apr23.dmp';

Output:
postgres=# copy test_idxfk to '/tmp/test_idxfk_05apr23.dmp';
COPY 101
Time: 0.971 ms

postgres=# copy test_part_idxfk from '/tmp/test_idxfk_05apr23.dmp';
COPY 101
Time: 2.772 ms
postgres=#

postgres=# analyze verbose test_idxfk;
INFO: analyzing "public.test_idxfk"
INFO: "test_idxfk": scanned 2 of 2 pages, containing 101 live rows and 0 dead rows; 101 rows in sample, 101 estimated total rows
ANALYZE
Time: 2.955 ms

postgres=# analyze verbose test_part_idxfk;
INFO: analyzing "public.test_part_idxfk" inheritance tree
INFO: "test_part_idxfk_a": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO: "test_part_idxfk_b": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO: "test_part_idxfk_c": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO: "test_part_idxfk_d": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
INFO: analyzing "public.test_part_idxfk_a"
INFO: "test_part_idxfk_a": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO: analyzing "public.test_part_idxfk_b"
INFO: "test_part_idxfk_b": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO: analyzing "public.test_part_idxfk_c"
INFO: "test_part_idxfk_c": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO: analyzing "public.test_part_idxfk_d"
INFO: "test_part_idxfk_d": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
INFO: analyzing "public.test_part_idxfk_default"
INFO: "test_part_idxfk_default": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
ANALYZE
Time: 12.780 ms

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test%idxfk%' order by relname;
relname | relpages | reltuples | relallvisible | relispartition
-------------------------+----------+-----------+---------------+----------------
test_idxfk | 2 | 101 | 0 | f
test_idxfk_child | 1 | 101 | 0 | f
test_idxfk_lvl_pk | 2 | 101 | 0 | f
test_part_idxfk | -1 | 101 | 0 | f
test_part_idxfk_a | 1 | 26 | 0 | t
test_part_idxfk_b | 1 | 26 | 0 | t
test_part_idxfk_c | 1 | 26 | 0 | t
test_part_idxfk_d | 1 | 23 | 0 | t
test_part_idxfk_default | 0 | 0 | 0 | t
(9 rows)
Time: 0.711 ms

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test%idxfk%' order by relname;
relname | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
-------------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
test_idxfk | 4 | 404 | 101 | 0 | 0 | 101 | 0
test_idxfk_child | 1 | 101 | 101 | 0 | 0 | 101 | 0
test_part_idxfk | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_idxfk_a | 0 | 0 | 26 | 0 | 0 | 26 | 0
test_part_idxfk_b | 0 | 0 | 26 | 0 | 0 | 26 | 0
test_part_idxfk_c | 0 | 0 | 26 | 0 | 0 | 26 | 0
test_part_idxfk_d | 0 | 0 | 23 | 0 | 0 | 23 | 0
test_part_idxfk_default | 0 | 0 | 0 | 0 | 0 | 0 | 0
(8 rows)
Time: 12.508 ms
postgres=#

G) Let us add the primary key now.
Test the error - postgresql says you need to include the partition column in the primary key...

ALTER TABLE public.test_part_idxfk
ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl);
Error:
postgres=# ALTER TABLE public.test_part_idxfk
postgres-# ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "test_part_idxfk" lacks column "hosptl" which is part of the partition key.
Time: 2.294 ms
postgres=#

So the document is correct.

Now let us try again with primary key including the partition key column:

ALTER TABLE public.test_part_idxfk
ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl,hosptl);
postgres=# ALTER TABLE public.test_part_idxfk
postgres-# ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl,hosptl);
ALTER TABLE
Time: 32.099 ms

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test%idxfk%' order by relname;
relname | relpages | reltuples | relallvisible | relispartition
------------------------------+----------+-----------+---------------+----------------
test_idxfk | 2 | 101 | 0 | f
test_idxfk_child | 1 | 101 | 0 | f
test_idxfk_lvl_pk | 2 | 101 | 0 | f
test_part_idxfk | -1 | 101 | 0 | f
test_part_idxfk_a | 1 | 26 | 0 | t
test_part_idxfk_a_pkey | 2 | 26 | 0 | t
test_part_idxfk_b | 1 | 26 | 0 | t
test_part_idxfk_b_pkey | 2 | 26 | 0 | t
test_part_idxfk_c | 1 | 26 | 0 | t
test_part_idxfk_c_pkey | 2 | 26 | 0 | t
test_part_idxfk_d | 1 | 23 | 0 | t
test_part_idxfk_d_pkey | 2 | 23 | 0 | t
test_part_idxfk_default | 0 | 0 | 0 | t
test_part_idxfk_default_pkey | 1 | 0 | 0 | t
test_part_idxfk_lvl_pk | 0 | 0 | 0 | f
(15 rows)
Time: 0.760 ms

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test%idxfk%' order by relname;
relname | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
-------------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
test_idxfk | 4 | 404 | 101 | 0 | 0 | 101 | 0
test_idxfk_child | 1 | 101 | 101 | 0 | 0 | 101 | 0
test_part_idxfk | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_idxfk_a | 2 | 52 | 26 | 0 | 0 | 26 | 0
test_part_idxfk_b | 2 | 52 | 26 | 0 | 0 | 26 | 0
test_part_idxfk_c | 2 | 52 | 26 | 0 | 0 | 26 | 0
test_part_idxfk_d | 2 | 46 | 23 | 0 | 0 | 23 | 0
test_part_idxfk_default | 2 | 0 | 0 | 0 | 0 | 0 | 0
(8 rows)
Time: 13.192 ms

postgres=# select * from pg_indexes where tablename like 'test%idxfk%' order by tablename,indexname;
schemaname | tablename | indexname | tablespace | indexdef
------------+-------------------------+------------------------------+------------+--------------------------------------------------------------------------------------------------------------
public | test_idxfk | test_idxfk_lvl_pk | | CREATE UNIQUE INDEX test_idxfk_lvl_pk ON public.test_idxfk USING btree (lvl)
public | test_part_idxfk | test_part_idxfk_lvl_pk | | CREATE UNIQUE INDEX test_part_idxfk_lvl_pk ON ONLY public.test_part_idxfk USING btree (lvl, hosptl)
public | test_part_idxfk_a | test_part_idxfk_a_pkey | | CREATE UNIQUE INDEX test_part_idxfk_a_pkey ON public.test_part_idxfk_a USING btree (lvl, hosptl)
public | test_part_idxfk_b | test_part_idxfk_b_pkey | | CREATE UNIQUE INDEX test_part_idxfk_b_pkey ON public.test_part_idxfk_b USING btree (lvl, hosptl)
public | test_part_idxfk_c | test_part_idxfk_c_pkey | | CREATE UNIQUE INDEX test_part_idxfk_c_pkey ON public.test_part_idxfk_c USING btree (lvl, hosptl)
public | test_part_idxfk_d | test_part_idxfk_d_pkey | | CREATE UNIQUE INDEX test_part_idxfk_d_pkey ON public.test_part_idxfk_d USING btree (lvl, hosptl)
public | test_part_idxfk_default | test_part_idxfk_default_pkey | | CREATE UNIQUE INDEX test_part_idxfk_default_pkey ON public.test_part_idxfk_default USING btree (lvl, hosptl)
(7 rows)
Time: 2.620 ms
postgres=#

H) Let us now rename the table and index...

alter table test_idxfk rename to test_idxfk_old;
alter index test_idxfk_lvl_pk rename to test_idxfk_lvl_pk_old;

Let us rename the partitioned table:

alter table test_part_idxfk rename to test_idxfk;
alter table test_part_idxfk_a rename to test_idxfk_a;
alter table test_part_idxfk_b rename to test_idxfk_b;
alter table test_part_idxfk_c rename to test_idxfk_c;
alter table test_part_idxfk_d rename to test_idxfk_d;

Output:

alter table test_part_idxfk_default rename to test_idxfk_default;
Time: 0.833 ms
postgres=# alter table test_part_idxfk rename to test_idxfk;
ALTER TABLE
Time: 2.950 ms
postgres=# alter table test_part_idxfk_a rename to test_idxfk_a;
ALTER TABLE
Time: 3.892 ms
postgres=# alter table test_part_idxfk_b rename to test_idxfk_b;
ALTER TABLE
Time: 8.017 ms
postgres=# alter table test_part_idxfk_c rename to test_idxfk_c;
ALTER TABLE
Time: 8.148 ms
postgres=# alter table test_part_idxfk_d rename to test_idxfk_d;
ALTER TABLE
Time: 4.647 ms
postgres=# alter table test_part_idxfk_default rename to test_idxfk_default;
ALTER TABLE
Time: 1.826 ms
postgres=#

Let us rename the index:

alter index test_part_idxfk_lvl_pk rename to test_idxfk_lvl_pk;
alter index test_part_idxfk_a_pkey rename to test_idxfk_a_pkey;
alter index test_part_idxfk_b_pkey rename to test_idxfk_b_pkey;
alter index test_part_idxfk_c_pkey rename to test_idxfk_c_pkey;
alter index test_part_idxfk_d_pkey rename to test_idxfk_d_pkey;
alter index test_part_idxfk_default_pkey rename to test_idxfk_default_pkey;

Output:
postgres=# alter index test_part_idxfk_lvl_pk rename to test_idxfk_lvl_pk;
ALTER INDEX
Time: 2.022 ms
postgres=# alter index test_part_idxfk_a_pkey rename to test_idxfk_a_pkey;
ALTER INDEX
Time: 1.796 ms
postgres=# alter index test_part_idxfk_b_pkey rename to test_idxfk_b_pkey;
ALTER INDEX
Time: 3.128 ms
postgres=# alter index test_part_idxfk_c_pkey rename to test_idxfk_c_pkey;
ALTER INDEX
Time: 2.552 ms
postgres=# alter index test_part_idxfk_d_pkey rename to test_idxfk_d_pkey;
ALTER INDEX
Time: 2.542 ms
postgres=# alter index test_part_idxfk_default_pkey rename to test_idxfk_default_pkey;
ALTER INDEX
Time: 2.303 ms
postgres=#

I) Let us test the constraint first in nonpart table for enforcement:

insert into test_idxfk_child values (101,'AAAAA','PPPPP');
postgres=# \d test_idxfk_child
Table "public.test_idxfk_child"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
lvl | integer | | |
hosptl | text | | |
hostlcode | text | | |
Foreign-key constraints:
"test_idxfk_child_lvl_fk" FOREIGN KEY (lvl) REFERENCES test_idxfk_old(lvl)
postgres=# insert into test_idxfk_child values (101,'AAAAA','PPPPP');
ERROR: insert or update on table "test_idxfk_child" violates foreign key constraint "test_idxfk_child_lvl_fk"
DETAIL: Key (lvl)=(101) is not present in table "test_idxfk_old".
Time: 0.924 ms
postgres=#

So it works fine.

J) Now let us drop and add the constraint back again to the partition table.

Drop routine:

select * from pg_constraint where conname='test_idxfk_child_lvl_fk';

postgres=# select * from pg_constraint where conname='test_idxfk_child_lvl_fk';
-[ RECORD 1 ]-+------------------------
oid | 16749
conname | test_idxfk_child_lvl_fk
connamespace | 2200
contype | f
condeferrable | f
condeferred | f
convalidated | t
conrelid | 16744 <<< cons rel id
contypid | 0
conindid | 16740 <<< cons pk idx id
conparentid | 0
confrelid | 16735 <<< cons pk rel id
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {1}
confkey | {1}
conpfeqop | {96}
conppeqop | {96}
conffeqop | {96}
conexclop |
conbin |
Time: 0.681 ms
postgres=#

select reloid,relname,reltype from pg_class where oid in (16744,16740,16735) order by oid;

Output:

alter table test_idxfk_child drop constraint test_idxfk_child_lvl_fk;

Output:

postgres=# alter table test_idxfk_child drop constraint test_idxfk_child_lvl_fk;
ALTER TABLE
Time: 4.431 ms

postgres=#

Add routine:

alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);
postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);
ERROR: there is no unique constraint matching given keys for referenced table "test_idxfk"
Time: 0.571 ms
postgres=#

postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl,hosptl);
ERROR: number of referencing and referenced columns for foreign key disagree
Time: 0.545 ms
postgres=#

Stumpped!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

So an ASIS datamodel change with just partition change cant happen!
We have to adjust the foriegn key column to include the hosptl code as well, which is a big surprise!!

If you dont have this hostpl column in child table, the situation is more worse!

alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl,hosptl) references test_idxfk(lvl,hosptl);

Output:

postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl,hosptl) references test_idxfk(lvl,hosptl);

ALTER TABLE

Time: 5.872 ms

postgres=#

postgres=# select * from pg_constraint where conname='test_idxfk_child_lvl_fk';
-[ RECORD 1 ]-+------------------------
oid | 16801
conname | test_idxfk_child_lvl_fk
connamespace | 2200
contype | f
condeferrable | f
condeferred | f
convalidated | t
conrelid | 16744
contypid | 0
conindid | 16789
conparentid | 0
confrelid | 16756
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {1,2}
confkey | {1,3}
conpfeqop | {96,98}
conppeqop | {96,98}
conffeqop | {96,98}
conexclop |
conbin |
Time: 0.483 ms

postgres=#
Test : FK is in enforcement mode
postgres=# insert into test_idxfk_child values (101,'AAAAA','PPPPP');
ERROR: insert or update on table "test_idxfk_child" violates foreign key constraint "test_idxfk_child_lvl_fk"
DETAIL: Key (lvl, hosptl)=(101, AAAAA) is not present in table "test_idxfk".
Time: 0.854 ms
postgres=#

Probably a stackover flow suggestion needs to be sought for the condition we faced.

Update:

Even in oracle it is the same situation, a partition table should include its partition key column in the primary key. So when we decide to perform a parition on the table, we have to give this due consideration as a data model change.

Thanks

Let us learn Oracle Database Administration skills

Wednesday, April 5, 2023

Part 3: Steps to move data from a non partitioned table to partitioned table in postgresql

No comments:

Post a Comment

Flashback data archive steps