Re: [BUGS] BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crashwith "ERROR: unknown error"

Поиск
Список
Период
Сортировка
От Josef Machytka
Тема Re: [BUGS] BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crashwith "ERROR: unknown error"
Дата
Msg-id CAGvVEFs1QxgFLUmc2aasrLYQS5MQhW_v4oV13i15Mph9Cwiugg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crash with "ERROR: unknown error"  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: [BUGS] BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crash with "ERROR: unknown error"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
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@sss.pgh.pa.us> wrote:
josef.machytka@gmail.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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [BUGS] BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crash with "ERROR: unknown error"
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [BUGS] BUG #14471: PostgreSQL 9.6 "NOT IN" in select causes crash with "ERROR: unknown error"