The following bug has been logged on the website:
Bug reference: 7758
Logged by: Daniel Migowski
Email address: dmigowski@ikoffice.de
PostgreSQL version: 9.1.7
Operating system: Windows, bug Linux seems also affected
Description: =
Hi,
here is a small example of operator definitions:
CREATE FUNCTION text_natsort_gt(text, text) RETURNS boolean AS
'SELECT bttext_pattern_cmp($1,$2) > 0'
LANGUAGE 'sql' IMMUTABLE STRICT COST 1;
CREATE FUNCTION text_natsort_lt(text, text) RETURNS boolean AS
'SELECT bttext_pattern_cmp($1,$2) < 0'
LANGUAGE 'sql' IMMUTABLE STRICT COST 1;
CREATE OPERATOR #<#(
PROCEDURE =3D text_natsort_lt,
LEFTARG =3D text,
RIGHTARG =3D text,
COMMUTATOR =3D #>#,
RESTRICT =3D scalarltsel,
JOIN =3D scalarltjoinsel);
CREATE OPERATOR #>#(
PROCEDURE =3D text_natsort_gt,
LEFTARG =3D text,
RIGHTARG =3D text,
COMMUTATOR =3D #<#,
RESTRICT =3D scalargtsel,
JOIN =3D scalargtjoinsel);
When inserted into an empty DB on an 9.1.2 system, then pg_dump will
generate wrong CUMMUTATOR clauses, but only for the first operator:
CREATE OPERATOR #<# (
PROCEDURE =3D text_natsort_lt,
LEFTARG =3D text,
RIGHTARG =3D text,
COMMUTATOR =3D 705344,
RESTRICT =3D scalarltsel,
JOIN =3D scalarltjoinsel
);
Doing this in PostgreSQL 9.1.7 works correctly with my example:
CREATE OPERATOR #<# (
PROCEDURE =3D text_natsort_lt,
LEFTARG =3D text,
RIGHTARG =3D text,
COMMUTATOR =3D #>#,
RESTRICT =3D scalarltsel,
JOIN =3D scalarltjoinsel
);
Sadly, when I use these operators in my real world database running on
9.1.7, the problem is still there:
CREATE OPERATOR #<# (
PROCEDURE =3D text_natsort_lt,
LEFTARG =3D text,
RIGHTARG =3D text,
COMMUTATOR =3D 1865126,
RESTRICT =3D scalarltsel,
JOIN =3D scalarltjoinsel
);
Could you please have a look if there are cases where this bug could still
have survived? I would eagerly try new versions of postgres to solve this
issue, or do anything else of help (like providing a dump of my real world
db without data to the developer).