Decided to see what DBAs have to say about this because developers have no idea. I need to execute 2 DMLs in one transaction. First DML takes records from
Table1, changes some values and inserts back into the same table a subset of the originals. Second DML selects the newly inserted records, joins them with the original records that were used to generate the subset, and inserts some values into
When I ran this from c# code under one transaction, first statement executed fine and then second kept going forever. So, I decided to check directly in the DB. - same thing.
Using Postgre SQL, if I run the block below, it just keeps running but not completing.. for hours. But the execution shows as
If I Run these 2 statement separately, in separate transactions - takes few seconds. What do I miss here? I am not very experienced with Postgres.
BTW, if I add
SELECT after first statement and just run
SELECT under transaction,
select runs fine and then if there is no
commit, records are gone, as expected. It is running that second
INSERT in the same transaction that is troublesome.
begin; INSERT INTO table1 (fields...) SELECT <some fields and couple hard-coded values> FROM table1 WHERE <few conditions> GROUP BY <same fields as SELECT>; INSERT INTO table2 (f1, f2) -- INSERT SUBSET based on uncommitted data from ^^^ select c.id, a.id FROM table1 t1 Inner JOIN -- SELF JOIN table1 t2 a On <condition> --(uuid, int, int) where <where conditions>; COMMIT;
-- Explain plan
Insert on table2 (cost=220982.08..222767.83 rows=0 width=0) (actual time=1497.900..1497.902 rows=0 loops=1) -> Subquery Scan on "*SELECT*" (cost=220982.08..222767.83 rows=28181 width=40) (actual time=770.711..986.875 rows=94731 loops=1) -> Merge Join (cost=220982.08..222415.56 rows=28181 width=70) (actual time=770.635..934.304 rows=94731 loops=1) Merge Cond: (((a.someid)::text = (c.someid)::text) AND ((a.type)::text = (c.type)::text)) -> Sort (cost=127241.69..127555.13 rows=125375 width=46) (actual time=673.203..737.628 rows=94731 loops=1) Sort Key: a.someid, a.type Sort Method: external merge Disk: 5208kB -> Seq Scan on table1 a (cost=0.00..112768.01 rows=125375 width=46) (actual time=0.006..258.759 rows=94731 loops=1) Filter: ((MustDoSome IS TRUE) AND (batchnumber = '2b86d81c-d76a-4184-b04f-6bdbeb78b51b'::uuid) AND (keytype = 1) AND (datarecordtype = 2)) Rows Removed by Filter: 588905 -> Sort (cost=93740.39..93787.02 rows=18650 width=46) (actual time=97.419..112.265 rows=94731 loops=1) Sort Key: c.someid, c.type Sort Method: external sort Disk: 3448kB -> Bitmap Heap Scan on statementdata c (cost=727.30..92417.46 rows=18650 width=46) (actual time=8.072..44.686 rows=58677 loops=1) Recheck Cond: (stmtkeytype = 2) Filter: ((batchnumber = '2b86d81c-d76a-4184-b04f-6bdbeb78b51b'::uuid) AND (datarecordtype = 2)) Rows Removed by Filter: 58677 Heap Blocks: exact=16204 -> Bitmap Index Scan on fki_fk_table1_stmt_key_type (cost=0.00..722.63 rows=58428 width=0) (actual time=5.263..5.263 rows=117354 loops=1) Index Cond: (stmtkeytype = 2) Planning Time: 0.557 ms Trigger for constraint fk_xxx_zz_yyy_id: time=899.588 calls=94731 Trigger for constraint fk_xxx_yyy_id: time=837.080 calls=94731 Trigger for constraint fk_RRRR_yyyt_id: time=1001.268 calls=94731