Re: BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crash with "ERROR: unknown error"

Yes, I am sorry, dblink is involved - I just did not see it as significant.
We start several processes in parallel to speed up whole billing
calculation otherwise it would take 10+ hours to calculate everything in
serial.
Ok, so at least "unknown error" is explained.
But problem with "NOT IN" remains.
When I replaced "NOT IN" with "NOT EXISTS" query ended after ~3 hours
without any problems. Even over dblink.
Partitions have from 1M to 3M records each and now we use partitions for
present year only.
So it is not small task but also not something really big (we work with
much more data when we calculate statistics from web metrics).
And I run billing on 2 different GCE instances with pg 9.6.1 to have
comparison. Behaviour was the same.
Unfortunately data are confident so it would be quite hard to give you some
access to test it even with anonymous data because even structure of the
query contains a lot of know-how.
Therefore I could sent only so crazy looking skeleton of the query. Sorry
about it.

On 21 December 2016 at 16:26, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> josef(dot)machytka(at)gmail(dot)com writes:
> > Looks like PostgreSQL 9.6 now very probably has some bug connected with
> "NOT
> > IN" command. We have select which actually crashes after several hours
> (!)
> > of run with message "ERROR: unknown error". Although according to
> explain
> > plan it should be done in ~40 minutes.
>
> This is an interesting report, but without enough information to replicate
> the problem, we're unlikely to be able to help you. A fragment of a
> query, with zero information about the underlying tables, is far from
> enough.
>
> The only occurrences of the string "unknown error" that I can find in the
> source code are in dblink and postgres_fdw (both reflecting cases where
> the remote server did not return an error message, itself a "shouldn't
> happen" situation). If you were using either, you didn't say so; but if
> you were, maybe taking a look in the remote server's log would be useful.
>
> regards, tom lane
>