From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josef Machytka <josef.machytka@gmail.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crash with "ERROR: unknown error"
Date: Wed, 21 Dec 2016 11:11:04 -0500
Message-ID: <18191.1482336664@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGvVEFs1QxgFLUmc2aasrLYQS5MQhW_v4oV13i15Mph9Cwiugg@mail.gmail.com>
References: <20161221094443.25614.47807@wrigleys.postgresql.org>
<6818.1482333970@sss.pgh.pa.us>
<CAGvVEFs1QxgFLUmc2aasrLYQS5MQhW_v4oV13i15Mph9Cwiugg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
Josef Machytka <josef.machytka@gmail.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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, josef.machytka@gmail.com
Subject: Re: BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crash with "ERROR: unknown error"
In-Reply-To: <18191.1482336664@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox