Re: Killing a data modifying transaction

2009/6/22 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> William Temperley <willtemperley(at)gmail(dot)com> writes:
>> I've got two transactions I tried to kill 3 days ago using "select
>> pg_cancel_backend(<pid>)", then SIGTERM, and have since then been
>> using 100% of a cpu core each. They were supposed to insert the
>> results of large unions with PostGIS and appear to have failed.
>> Could someone tell me what's the least worst option here please? If I
>> kill -9 will I corrupt my data directory?
>
> No, you'll just lose all your open sessions.
>
> It might be worth trying to identify where they're looping before
> you zap them, though.  A stack trace from gdb would help.
>
>                        regards, tom lane
>

Thanks Tom.

I'm wondering if I happened as I'd started the same query twice.
The first had work_mem = 1MB so I tried to kill it and started another
with work_mem = 1000MB, but both were attempting to insert the same id
into a PK:
"insert into world (geom, id) select st_union(geom), 1 from adminunit
where admin_level = '0'".
Just now when I killed the first process, the other terminated.

I'll run the query again and see if it wasn't just my impatience that
caused it - and post the stack trace if it fails.

Thanks,

Will Temperley.