[Bucardo-general] Sync of fields with user-function as default, Sync of XML fields

[Franz Dürr]

  On 02.09.2010 21:00, Greg Sabino Mullane wrote:
> On Thu, Sep 02, 2010 at 06:47:28PM +0200, Franz Dürr wrote:
>>>> Does DBD ignore user defined operators or am i using the wrong schema?
>>>> I'm stuck at this point and would greatly appreciate any help
>>> As far as I know, that should work. I'd try running some sql at the
>>> command line to see if you can duplicate that error, then ask for
>>> help on #postgresql on irc.freenode.net.
>>>
>> I'd be quite astonished if sync of XML-fields work, as there is no
>> XML-equality operator neither in Pg 8.4.4 nor in 9.0B4 and Bucardo
>> doesn't provide one either
> No, I meant your custom casting solution should work. DBD is agnostic
> about user defined operators. As long as they are in your search path
> it should all just work.
>
Just for the records:

I managed to solve the 'DBD::Pg::st execute failed: ERROR:  could not 
identify an equality operator for type xml' - problem (thanks to Tom 
Lane for pointing me into the right direction): Bucardo compares 
complete records for equality, and the underlying PostgreSQL function 
'record_eq()' doesn't care about user-defined operators. To let it work 
with XML-fields, you have to create an operator class for XML:

---------------------------
CREATE OR REPLACE FUNCTION pg_catalog.xml_ops_lt(XML,XML) RETURNS BOOLEAN AS
'SELECT CAST($1 AS TEXT) < CAST($2 AS TEXT);' LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION pg_catalog.xml_ops_le(XML,XML) RETURNS BOOLEAN AS
'SELECT CAST($1 AS TEXT) <= CAST($2 AS TEXT);' LANGUAGE SQL IMMUTABLE 
STRICT;
CREATE OR REPLACE FUNCTION pg_catalog.xml_ops_eq(XML,XML) RETURNS BOOLEAN AS
'SELECT CAST($1 AS TEXT) = CAST($2 AS TEXT);' LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION pg_catalog.xml_ops_ge(XML,XML) RETURNS BOOLEAN AS
'SELECT CAST($1 AS TEXT) >= CAST($2 AS TEXT);' LANGUAGE SQL IMMUTABLE 
STRICT;
CREATE OR REPLACE FUNCTION pg_catalog.xml_ops_gt(XML,XML) RETURNS BOOLEAN AS
'SELECT CAST($1 AS TEXT) > CAST($2 AS TEXT);' LANGUAGE SQL IMMUTABLE STRICT;

CREATE OPERATOR pg_catalog.< (
    leftarg = xml, rightarg = xml, procedure = pg_catalog.xml_ops_lt,
    commutator = > , negator = >= ,
    restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR pg_catalog.<= (
    leftarg = xml, rightarg = xml, procedure = pg_catalog.xml_ops_le,
    commutator = >= , negator = > ,
    restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR pg_catalog.= (
    leftarg = xml, rightarg = xml, procedure = pg_catalog.xml_ops_eq,
    commutator = = , -- no negator
    restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR pg_catalog.>= (
    leftarg = xml, rightarg = xml, procedure = pg_catalog.xml_ops_ge,
    commutator = <= , negator = < ,
    restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR pg_catalog.> (
    leftarg = xml, rightarg = xml, procedure = pg_catalog.xml_ops_gt,
    commutator = < , negator = <= ,
    restrict = scalargtsel, join = scalargtjoinsel
);

CREATE OR REPLACE FUNCTION pg_catalog.xml_ops_cmp(XML,XML) RETURNS 
INTEGER AS
'SELECT CASE CAST($1 AS TEXT)=CAST($2 AS TEXT) WHEN true THEN 0 ELSE
   CASE CAST($1 AS TEXT)<CAST($2 AS TEXT) WHEN true THEN -1 ELSE 1 END
  END;' LANGUAGE SQL IMMUTABLE STRICT;

CREATE OPERATOR CLASS pg_catalog.xml_ops DEFAULT FOR TYPE XML USING BTREE AS
  OPERATOR        1 < ,
  OPERATOR        2 <= ,
  OPERATOR        3       = ,
  OPERATOR        4 >= ,
  OPERATOR        5 > ,
  FUNCTION 1 pg_catalog.xml_ops_cmp(XML,XML);
---------------------------

Please note that this operator class is not for general usage like 
indexing (there's hardly any sense in ordering XML - trees).
I also have to admit, that I didn't dive into the restriction parameter, 
just copied them from tutorial/complex.source, so if you know better, 
tell me please!

Another thing that gave me errors during syncs was that user 'bucardo' 
doesn't have the public-schema in it's search_path. (I call functions 
from schema public in my table constraints)
'ALTER USER bucardo SET search_path = bucardo,public;'
fixed that ok. I just wonder, why bucardo.schema sets bucardo's search 
path to 'bucardo' only, as 'public' should normally always be in the 
search_path.

Thanks again, Greg, for your great work and support
fd