Postgres 10&11 data processing error

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgres 10&11 data processing error

Александр Денежкин
Hello, i guess there is the error in Postgresql 10.x and 11.x. I'm using russian ERP platform 1C with Postgres for data processing and storing.

Postgres 10 and 11 (both Linux and Windows) logs error:
2019-08-14 09:05:10.758 MSK [31696] ERROR:  index key does not match expected index column

On query:

2019-08-14 07:40:03.417 MSK [12538] STATEMENT:  SELECT
T1._IDRRef,
T1._Fld745RRef,
T1._Date_Time,
T1._Fld740
FROM _Document148 T1
LEFT OUTER JOIN _Document148 T2
ON (T1._IDRRef = T2._IDRRef) AND (T2._Fld376 = CAST(0 AS NUMERIC))
WHERE ((T1._Fld376 = CAST(0 AS NUMERIC))) AND (((T2._Fld734RRef = '\\237%\\014\\272J\\012\\005\\234B\\214\\177\\232/Ri\\350'::bytea) OR (T2._Fld734RRef = '\\244\\221k}F\\256\\031AML\\306!x\\376\\353\\234'::bytea)) AND T1._Posted = TRUE AND (T1._Fld745RRef IN ('\\274\\215\\364M0o\\375\\250\\021\\351C\\024Z(`\\236'::bytea)) AND (((T1._Date_Time >= '2019-08-14 00:00:00'::timestamp) AND (T1._Date_Time < '2019-09-10 23:59:00'::timestamp)) OR ((T1._Fld740 > '2019-08-14 00:00:00'::timestamp) AND (T1._Fld740 <= '2019-09-10 23:59:00'::timestamp))) AND (T1._Fld733RRef = '\\317\\200\\364M0o\\375\\250\\021\\351<\\016\\331\\310\\247\\022'::bytea) AND (T1._Fld754RRef = '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea))
ORDER BY (T1._Date_Time)

On Postges 9.6 and earlier works fine....

You can download DB dump from http://89.169.110.237/fitness1c.sql

Let me know if you need any additional information

Thank a lot...


postgresql-2019-08-14_070252.log (11K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 10&11 data processing error

konstantin knizhnik
Hello,

On 14.08.2019 15:44, Александр Денежкин wrote:

> Hello, i guess there is the error in Postgresql 10.x and 11.x. I'm
> using russian ERP platform 1C with Postgres for data processing and
> storing.
>
> Postgres 10 and 11 (both Linux and Windows) logs error:
> 2019-08-14 09:05:10.758 MSK [31696] ERROR:  index key does not match
> expected index column
>
> On query:
>
> 2019-08-14 07:40:03.417 MSK [12538] STATEMENT:  SELECT
> T1._IDRRef,
> T1._Fld745RRef,
> T1._Date_Time,
> T1._Fld740
> FROM _Document148 T1
> LEFT OUTER JOIN _Document148 T2
> ON (T1._IDRRef = T2._IDRRef) AND (T2._Fld376 = CAST(0 AS NUMERIC))
> WHERE ((T1._Fld376 = CAST(0 AS NUMERIC))) AND (((T2._Fld734RRef =
> '\\237%\\014\\272J\\012\\005\\234B\\214\\177\\232/Ri\\350'::bytea) OR
> (T2._Fld734RRef =
> '\\244\\221k}F\\256\\031AML\\306!x\\376\\353\\234'::bytea)) AND
> T1._Posted = TRUE AND (T1._Fld745RRef IN
> ('\\274\\215\\364M0o\\375\\250\\021\\351C\\024Z(`\\236'::bytea)) AND
> (((T1._Date_Time >= '2019-08-14 00:00:00'::timestamp) AND
> (T1._Date_Time < '2019-09-10 23:59:00'::timestamp)) OR ((T1._Fld740 >
> '2019-08-14 00:00:00'::timestamp) AND (T1._Fld740 <= '2019-09-10
> 23:59:00'::timestamp))) AND (T1._Fld733RRef =
> '\\317\\200\\364M0o\\375\\250\\021\\351<\\016\\331\\310\\247\\022'::bytea)
> AND (T1._Fld754RRef =
> '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea))
> ORDER BY (T1._Date_Time)
>
> On Postges 9.6 and earlier works fine....
>
> You can download DB dump from http://89.169.110.237/fitness1c.sql
>
> Let me know if you need any additional information
>
> Thank a lot...
>

Are you sure that you are not using PgPRO version of Postgres for 1C?
It includes self join patch where the bug with similar symptoms was
recently fixed.

Please try to execute:

set enable_self_join_removal=off;

and repeat the query.
If such GUC assignment works then you definitely have version of
Postgres with self join patch.
Unfortunately lack of this GUC doesn't mean that your version of
Postgres doesn't include this optimization.
"enable_self_join_removal" is available only in PgPRO-EE but not in
patch published at commitfest.





--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company