Search Postgresql Archives

Foreign keys fails with partitioned table.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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?



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux