0

I'm running update-or-insert query on a table of approximatelly 3mln rows, 4 columns. Query is part of every day process, and for same days, the query stucks forever. It's just blocked. It's deterministic, for a particular date, the query always stucks.

Query looks more or less like this:
[..] update part, not important, works fine
-- insert part:

insert into t (select * from tmp where col1, col2, col3 not in (select col1, col2, col3 from t));

There is only one potentially interesting row in pg_stat_activity. It's the above query, with column waiting equals to 'f'.

Any ideas what might be the cause? What is actually the semantics of that query? Is it first executing the whole subquery and remembers results or more like lazy evaluation? It is possible that subquery acquires a shared lock on a table level, and insert can't acquire its own lock? Why for some days query works fine?

Thanks!

  • Show us the execution plan. wiki.postgresql.org/wiki/Slow_Query_Questions – a_horse_with_no_name Mar 20 '15 at 17:38
  • Query plan looks like this: Seq Scan [t] => materialize => Seq Scan [tmp]. Problem solved - instead of "not in", we used "not exists". It appears, that "not in" leads to materializing the scan and processing the query and sub query row-by-row what causes locking problem. At least this is my guess. – Marcin Krupowicz Mar 20 '15 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.