From: | Edmundo Robles <edmundo(at)sw-argos(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Foreign keys fails with partitioned table. |
Date: | 2016-12-21 15:12:40 |
Message-ID: | CAOXzpYBgCWLwb5hQqdBp0+KDY2xvYMgWQVz=ERa2iAVHmebgDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a big,big table, this big table is referenced by another tables
by foreign keys.
I have a performance issues so i decide partition it by month ->
...,table_201610,table_201611,table_201612,...
after i have been created the partition tables i try to insert data but
foreign key constraint doesn't allow
the insertion.
I try set to deferrable those constraints but doesn work. only works if i
drop those constraints :(
What can i do to set foreign keys referencing a big table from partition
table?
Here the process in detail:
0. I have a big_table with many constraint.
I have a tables, T1,T2,T3,T4 referencing id from bigtable like foreign
key.
1. Backup T1..T4 tables.
2. Delete records from T1..T4 tables. I must delete if not then point 6
fails.
3. Rename bigtable to temp_bigtable.
4. Create master bigtable with same DDL
5. Create child/partitionated tables inherits from big_table. and create
the same constraint and indexes like big_table
6. Reset sequences, views and constraint to the new bigtable. When you
rename a table the views and sequences related to bigtable are renamed to
temp_bigtable too.
7. Create a function to insert data to the right partition table using
copy.
8. insert data with the function created.
and I got:
ERROR: insert or update on table "T1" violates foreign key constraint
"fk-lstl-stl"
DETAIL: Key (id_stl_msg)=(26874097) is not present in table "bigtable".
CONTEXT: SQL statement "WITH upsert AS (
UPDATE T1 SET id_stl_msg = v_id_stl_msg WHERE id_trun = in_idtrun
RETURNING *
)
--INSERT
INSERT INTO T1 (id_trun, id_stl_msg)
SELECT in_idtrun, v_id_stl_msg WHERE NOT EXISTS (SELECT * FROM upsert)"
But if drop the constraint works fine ....
What can i do to set foreign keys refrencing a id from bigtable or
partition table?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-12-21 15:36:06 | Re: error updating a tuple after promoting a standby |
Previous Message | marty kulma | 2016-12-21 14:53:06 | archive_command called for preallocated/recycled WAL? |