Обсуждение: BUG #7758: pg_dump does not correctly dump operators.

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).

dmigowski@ikoffice.de writes:
> 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:
> ...
> Sadly, when I use these operators in my real world database running on
> 9.1.7, the problem is still there:

Indeed, I do not see a problem with this example on 9.1.7.  Can you
extract a self-contained example that does fail with 9.1.7?

            regards, tom lane

I found an example that breaks on Postgres 9.1.7! Just define TWO operators=
! Try this:

-------------- Start of SQL script -----------
begin;

CREATE OR REPLACE FUNCTION text_natsort_gt(text, text) RETURNS boolean AS
'SELECT bttext_pattern_cmp($1,$2) > 0'
LANGUAGE 'sql' IMMUTABLE STRICT COST 1;

CREATE OR REPLACE FUNCTION text_natsort_lt(text, text) RETURNS boolean AS
'SELECT bttext_pattern_cmp($1,$2) < 0'
LANGUAGE 'sql' IMMUTABLE STRICT COST 1;

DROP OPERATOR IF EXISTS #<#(text,text) CASCADE;
CREATE OPERATOR #<#(
  PROCEDURE =3D text_natsort_lt,
  LEFTARG =3D text,
  RIGHTARG =3D text,
  COMMUTATOR =3D #>#,
  RESTRICT =3D scalarltsel,
  JOIN =3D scalarltjoinsel);

DROP OPERATOR IF EXISTS #<=3D#(text,text) CASCADE;
CREATE OPERATOR #<=3D#(
  PROCEDURE =3D text_natsort_lt,
  LEFTARG =3D text,
  RIGHTARG =3D text,
  COMMUTATOR =3D #>=3D#,
  RESTRICT =3D scalarltsel,
  JOIN =3D scalarltjoinsel);
 =20
DROP OPERATOR IF EXISTS #>#(text,text) CASCADE;
CREATE OPERATOR #>#(
  PROCEDURE =3D text_natsort_gt,
  LEFTARG =3D text,
  RIGHTARG =3D text,
  COMMUTATOR =3D #<#,
  RESTRICT =3D scalargtsel,
  JOIN =3D scalargtjoinsel);

DROP OPERATOR IF EXISTS #>=3D#(text,text) CASCADE;
CREATE OPERATOR #>=3D#(
  PROCEDURE =3D text_natsort_gt,
  LEFTARG =3D text,
  RIGHTARG =3D text,
  COMMUTATOR =3D #<=3D#,
  RESTRICT =3D scalargtsel,
  JOIN =3D scalargtjoinsel);

commit;

-------------- End of SQL script -----------

Then do a plain dump and see pg_dump fail!

PS: The second mail just has the mailing list in CC, sorry for forgetting t=
his before.


-----Urspr=FCngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Gesendet: Mittwoch, 19. Dezember 2012 20:09
An: Daniel Migowski
Cc: pgsql-bugs@postgresql.org
Betreff: Re: [BUGS] BUG #7758: pg_dump does not correctly dump operators.

dmigowski@ikoffice.de writes:
> When inserted into an empty DB on an 9.1.2 system, then pg_dump will=20
> generate wrong CUMMUTATOR clauses, but only for the first operator:
> ...
> Sadly, when I use these operators in my real world database running on=20
> 9.1.7, the problem is still there:

Indeed, I do not see a problem with this example on 9.1.7.  Can you extract=
 a self-contained example that does fail with 9.1.7?

            regards, tom lane

Daniel Migowski <dmigowski@ikoffice.de> writes:

> DROP OPERATOR IF EXISTS #<#(text,text) CASCADE;
> CREATE OPERATOR #<#(
>   PROCEDURE = text_natsort_lt,
>   LEFTARG = text,
>   RIGHTARG = text,
>   COMMUTATOR = #>#,
>   RESTRICT = scalarltsel,
>   JOIN = scalarltjoinsel);

> DROP OPERATOR IF EXISTS #>#(text,text) CASCADE;
> CREATE OPERATOR #>#(
>   PROCEDURE = text_natsort_gt,
>   LEFTARG = text,
>   RIGHTARG = text,
>   COMMUTATOR = #<#,
>   RESTRICT = scalargtsel,
>   JOIN = scalargtjoinsel);

The second DROP removes the "shell" operator that was created as a
placeholder by the first operator's COMMUTATOR reference.  Then when
you create the #># operator for real, it's not linked to the #<#
operator, at least not in that direction.  pg_dump is not at fault
here; it's just reporting what's in the catalogs, which is to say a
dangling commutator link.

I believe we've looked at this in the past, and not found any cure
that wasn't worse than the disease.  For example, if we were to treat
the first operator's COMMUTATOR reference as a hard dependency, then
the second DROP CASCADE would cascade to remove the first operator,
hardly the outcome you'd want.

My recommendation for the moment is that if you want to write the
script in this style, put all the DROPs first and then create the
operators.

            regards, tom lane

I wrote:
> I believe we've looked at this in the past, and not found any cure
> that wasn't worse than the disease.

BTW, the previous discussion that I was vaguely remembering is here:
http://archives.postgresql.org/pgsql-hackers/2010-09/msg02035.php

            regards, tom lane