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

Hi again.
So I checked all logs and it turned out one of databases involved vent into
recovery mode because some of its connections was killed.
Probably out of memory killer did it. So it caused chain reaction. And
billing query was canceled "due to administration command" and it was shown
through dblink as "unknown error".
Which looked quite horrible...

Regarding NOT IN - you were right there are NULL values.

Best regards

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

> Josef Machytka <josef(dot)machytka(at)gmail(dot)com> writes:
> > 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.
>
> Well, we have a theory about where it came from, but still not enough
> information to improve the behavior. Did you look to see what happened
> on the remote server?
>
> > 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.
>
> You do know that NOT IN and NOT EXISTS behave quite differently with
> respect to nulls? I'm suspicious that the real problem here is that
> your query is just wrong when written with NOT IN, and it specifies
> some unreasonable amount of computation. Possibly something is running
> out of memory and not dealing with the case very well, leading to the
> unhelpful error message.
>
> FWIW, just about every bug report I've ever seen about NOT IN boiled
> down to the complainant's subquery returning one or more nulls and
> the complainant not understanding what will happen if it does.
> Unfortunately, that's not a bug, it's the behavior required by the
> SQL standard.
>
> regards, tom lane
>