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?