does record_eq() ignore user-defined operators?

From: Kurt <wazkelzu(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: does record_eq() ignore user-defined operators?
Date: 2010-09-05 08:31:24
Message-ID: 4C83555C.5000101@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear list,

i'm trying to replicate tables containing XML-fields using Pg 8.4.4 and
9.0B4 with Bucardo and got:
DBD::Pg::st execute failed: ERROR: could not identify an equality
operator for type xml

So i provided a primitive equality operator for the XML type in schema
pg_catalog:

CREATE OR REPLACE FUNCTION pg_catalog.eq_xml_xml(XML,XML) RETURNS
BOOLEAN AS
'SELECT CAST($1 AS TEXT)=CAST($2 AS TEXT);' LANGUAGE SQL IMMUTABLE;
DROP OPERATOR IF EXISTS pg_catalog.= (XML,XML);
CREATE OPERATOR pg_catalog.=
(LEFTARG=XML,RIGHTARG=XML,PROCEDURE=pg_catalog.eq_xml_xml,COMMUTATOR= = );
COMMENT ON OPERATOR pg_catalog.= (XML,XML) IS 'equal';

This works nicely when comparing XML - fields in normal SQL, but
obviously has no effect when comparing records - the error persists.
I tracked the error message down to function record_eq() in rowtypes.c,
where the comment says:
* Lookup the equality function if not done already
Thats why i'm suspecting that user-defined operators are not heeded here.

Test case:
Include code from above, then:

CREATE TABLE test(id INTEGER PRIMARY KEY, attr XML);
INSERT INTO test(id,attr) VALUES (1,'<x>test</x>');
SELECT * FROM test WHERE id=1 AND attr=attr;
id | attr
----+-------------
1 | <x>test</x>

CREATE OR REPLACE FUNCTION test_record_eq() RETURNS BOOLEAN AS $$
DECLARE
a RECORD;
b RECORD;
BEGIN
SELECT * INTO a FROM test WHERE id=1;
SELECT * INTO b FROM test WHERE id=1;
RETURN (a = b);
END;
$$ LANGUAGE plpgsql STABLE;

SELECT test_record_eq();
ERROR: could not identify an equality operator for type xml
CONTEXT: PL/pgSQL function "test_record_eq" line 7 at RETURN

Just FYI: I'm using the XML-fields just as free-format data store, not
for complete XML-trees, e.g. '<age>20</age><name>Smith</name>'
And, yes, eq_xml_xml() will return false if XML field contents are
identical but just ordered differently, but that doesn't matter in my
case, as it just triggers an unnecessary sync.

Thanks for your help
wz

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ovid 2010-09-05 09:13:54 stack depth limit exceeded
Previous Message Julia Jacobson 2010-09-04 16:24:36 psql '\copy' command for writing binary data from BYTEA column to file