Lists: | pgsql-hackers |
---|
From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | pgsql-hackers Hackers <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Custom Operators Cannot be Found for Composite Type Values |
Date: | 2012-03-08 01:17:08 |
Message-ID: | C92C9BFA-AB22-42FF-B74C-D7760A92AE62@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hackers,
I’m doing some development with the new JSON type (actually, Andrew’s backport to 9.1) and needed to do some very basic equivalence testing. So I created a custom operator:
CREATE OR REPLACE FUNCTION json_eq(
json,
json
) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT $1::text = $2::text;
$$;
CREATE OPERATOR = (
LEFTARG = json,
RIGHTARG = json,
PROCEDURE = json_eq
);
With this in place, these work:
SELECT '{}'::json = '{}'::json;
SELECT ROW('{}'::json) = ROW('{}'::json);
However this does not:
create type ajson AS (a json);
SELECT ROW('{}'::json)::ajson = ROW('{}'::json)::ajson;
That last line emits an error:
ERROR: could not identify an equality operator for type json
To which my response was: WTF? Is this expected behavior? Is there something about custom operators that they can’t be used to compare the values of values in composite types?
I’ve worked around it by writing a separate operator to compare ajson types using
SELECT $1::text = $2::text
But it’s a bit annoying.
Thanks,
David
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
Cc: | pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Custom Operators Cannot be Found for Composite Type Values |
Date: | 2012-03-08 04:23:45 |
Message-ID: | 740.1331180625@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"David E. Wheeler" <david(at)justatheory(dot)com> writes:
> Im doing some development with the new JSON type (actually, Andrews backport to 9.1) and needed to do some very basic equivalence testing. So I created a custom operator:
> CREATE OR REPLACE FUNCTION json_eq(
> json,
> json
> ) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
> SELECT $1::text = $2::text;
> $$;
> CREATE OPERATOR = (
> LEFTARG = json,
> RIGHTARG = json,
> PROCEDURE = json_eq
> );
> With this in place, these work:
> SELECT '{}'::json = '{}'::json;
> SELECT ROW('{}'::json) = ROW('{}'::json);
> However this does not:
> create type ajson AS (a json);
> SELECT ROW('{}'::json)::ajson = ROW('{}'::json)::ajson;
> That last line emits an error:
> ERROR: could not identify an equality operator for type json
> To which my response was: WTF?
You have not told the system that your operator is equality for the
datatype. It's just a random operator that happens to be named "=".
We try to avoid depending on operator names as cues to semantics.
You need to incorporate it into a default hash or btree opclass before
the composite-type logic will accept it as the thing to use for
comparing that column.
regards, tom lane
From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Custom Operators Cannot be Found for Composite Type Values |
Date: | 2012-03-08 19:00:24 |
Message-ID: | 39D50797-720D-4614-81F5-BA6B0677B74C@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mar 7, 2012, at 8:23 PM, Tom Lane wrote:
> You have not told the system that your operator is equality for the
> datatype. It's just a random operator that happens to be named "=".
> We try to avoid depending on operator names as cues to semantics.
>
> You need to incorporate it into a default hash or btree opclass before
> the composite-type logic will accept it as the thing to use for
> comparing that column.
Ah, okay. Just need more stuff, I guess:
CREATE OR REPLACE FUNCTION json_cmp(
json,
json
) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT bttextcmp($1::text, $2::text);
$$;
CREATE OR REPLACE FUNCTION json_eq(
json,
json
) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT bttextcmp($1::text, $2::text) = 0;
$$;
CREATE OPERATOR = (
LEFTARG = json,
RIGHTARG = json,
PROCEDURE = json_eq
);
CREATE OPERATOR CLASS json_ops
DEFAULT FOR TYPE JSON USING btree AS
OPERATOR 3 = (json, json),
FUNCTION 1 json_cmp(json, json);
This seems to work.
Best,
David
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
Cc: | pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Custom Operators Cannot be Found for Composite Type Values |
Date: | 2012-03-08 19:16:33 |
Message-ID: | 27277.1331234193@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"David E. Wheeler" <david(at)justatheory(dot)com> writes:
> CREATE OPERATOR CLASS json_ops
> DEFAULT FOR TYPE JSON USING btree AS
> OPERATOR 3 = (json, json),
> FUNCTION 1 json_cmp(json, json);
> This seems to work.
Urk. You really ought to provide the whole opclass (all 5 operators).
I'm not sure what will blow up if you leave it like that, but it won't
be pretty.
regards, tom lane
From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "David E(dot) Wheeler" <david(at)justatheory(dot)com>, pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Custom Operators Cannot be Found for Composite Type Values |
Date: | 2012-03-08 19:27:08 |
Message-ID: | 4F59080C.1060201@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 03/08/2012 02:16 PM, Tom Lane wrote:
> "David E. Wheeler"<david(at)justatheory(dot)com> writes:
>> CREATE OPERATOR CLASS json_ops
>> DEFAULT FOR TYPE JSON USING btree AS
>> OPERATOR 3 = (json, json),
>> FUNCTION 1 json_cmp(json, json);
>> This seems to work.
> Urk. You really ought to provide the whole opclass (all 5 operators).
> I'm not sure what will blow up if you leave it like that, but it won't
> be pretty.
Yeah. Note too that this is at best dubious:
CREATE OR REPLACE FUNCTION json_cmp(
json,
json
) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT bttextcmp($1::text, $2::text);
$$;
Two pieces of JSON might well be textually different but semantically
identical (e.g. by one having additional non-semantic whitespace).
cheers
andrew
From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Custom Operators Cannot be Found for Composite Type Values |
Date: | 2012-03-08 19:31:19 |
Message-ID: | 080F7B0D-7A80-4595-87F4-854687BBA09C@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mar 8, 2012, at 11:16 AM, Tom Lane wrote:
>> This seems to work.
>
> Urk. You really ought to provide the whole opclass (all 5 operators).
> I'm not sure what will blow up if you leave it like that, but it won't
> be pretty.
Yes, I expect to have to fill in gaps as I go. These are just for unit tests, so I’m not too worried about it (yet).
David
From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Custom Operators Cannot be Found for Composite Type Values |
Date: | 2012-03-08 19:32:30 |
Message-ID: | 0D0EEF3A-7767-4206-9FFB-85C484F49C42@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mar 8, 2012, at 11:27 AM, Andrew Dunstan wrote:
> Yeah. Note too that this is at best dubious:
>
> CREATE OR REPLACE FUNCTION json_cmp(
> json,
> json
> ) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
> SELECT bttextcmp($1::text, $2::text);
> $$;
>
>
> Two pieces of JSON might well be textually different but semantically identical (e.g. by one having additional non-semantic whitespace).
Yes. This is just for unit tests, and is fine for the moment. If I end up with abnormalities, I will likely rewrite json_cmp() in Perl and use JSON::XS to do normalization. Not needed yet, though.
Thanks,
David