BUG #7758: pg_dump does not correctly dump operators.

From: dmigowski(at)ikoffice(dot)de
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7758: pg_dump does not correctly dump operators.
Date: 2012-12-19 10:31:59
Message-ID: E1TlGwR-0000lm-N4@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 7758
Logged by: Daniel Migowski
Email address: dmigowski(at)ikoffice(dot)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 = text_natsort_lt,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = #>#,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel);

CREATE OPERATOR #>#(
PROCEDURE = text_natsort_gt,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = #<#,
RESTRICT = scalargtsel,
JOIN = 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 = text_natsort_lt,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = 705344,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);

Doing this in PostgreSQL 9.1.7 works correctly with my example:

CREATE OPERATOR #<# (
PROCEDURE = text_natsort_lt,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = #>#,
RESTRICT = scalarltsel,
JOIN = 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 = text_natsort_lt,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = 1865126,
RESTRICT = scalarltsel,
JOIN = 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).

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message acummings 2012-12-19 17:22:05 BUG #7760: records with duplicate primary keys
Previous Message Jeff Davis 2012-12-19 01:42:35 Re: Arbitrary whitespace restrictions on range types