Re: BUG #7758: pg_dump does not correctly dump operators.

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 = text_natsort_lt,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = #>#,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel);

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

DROP OPERATOR IF EXISTS #>=#(text,text) CASCADE;
CREATE OPERATOR #>=#(
PROCEDURE = text_natsort_gt,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = #<=#,
RESTRICT = scalargtsel,
JOIN = 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 this before.

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

dmigowski(at)ikoffice(dot)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