Обсуждение: 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