Hi,
**Context**: We use Entity Framework Core as an ORM for Postgres. This ORM uses the `xmin` system column to implement
optimisticconcurrency control. However, the absence of sorting operators for `xid` in Postgres breaks some parts of the
ORM,such as grouping that relies on sorting data by all columns. I have already opened an issue in the EF repository
butI am looking for any workarounds.
**Question**: Is it safe to create custom ordering operators for the `xid` type and a default operator class with these
operators?For example, it could cast `xid` to `bigint` (using `xmin::text::bigint`) for comparison.
([Docs](https://www.postgresql.org/docs/current/sql-createopclass.html)says `an erroneous operator class definition
couldconfuse or even crash the server`)
Something like this:
```
CREATE OR REPLACE FUNCTION xid_to_bigint(xid) RETURNS bigint AS $$
BEGIN
RETURN $1::text::bigint;
end;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION xid_less_than(xid, xid) RETURNS boolean AS $$
DECLARE
"$1_converted" bigint = xid_to_bigint($1);
"$2_converted" bigint = xid_to_bigint($2);
BEGIN
RETURN "$1_converted" < "$2_converted";
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION xid_less_than_or_equal(xid, xid) RETURNS boolean AS $$
DECLARE
"$1_converted" bigint = xid_to_bigint($1);
"$2_converted" bigint = xid_to_bigint($2);
BEGIN
RETURN "$1_converted" <= "$2_converted";
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION xid_greater_than(xid, xid) RETURNS boolean AS $$
DECLARE
"$1_converted" bigint = xid_to_bigint($1);
"$2_converted" bigint = xid_to_bigint($2);
BEGIN
RETURN "$1_converted" > "$2_converted";
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION xid_greater_than_or_equal(xid, xid) RETURNS boolean AS $$
DECLARE
"$1_converted" bigint = xid_to_bigint($1);
"$2_converted" bigint = xid_to_bigint($2);
BEGIN
RETURN "$1_converted" >= "$2_converted";
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION xid_compare(xid, xid) RETURNS integer AS $$
DECLARE
"$1_converted" bigint = xid_to_bigint($1);
"$2_converted" bigint = xid_to_bigint($2);
BEGIN
IF "$1_converted" < "$2_converted" THEN
RETURN -1;
ELSIF "$1_converted" > "$2_converted" THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OPERATOR < (
LEFTARG = xid,
RIGHTARG = xid,
PROCEDURE = xid_less_than
);
CREATE OPERATOR <= (
LEFTARG = xid,
RIGHTARG = xid,
PROCEDURE = xid_less_than_or_equal
);
CREATE OPERATOR > (
LEFTARG = xid,
RIGHTARG = xid,
PROCEDURE = xid_greater_than
);
CREATE OPERATOR >= (
LEFTARG = xid,
RIGHTARG = xid,
PROCEDURE = xid_greater_than_or_equal
);
CREATE OPERATOR CLASS xid_ops DEFAULT FOR TYPE xid USING btree AS
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 xid_compare(xid, xid);
```
Thank you for your help!
Kind regards,
Alexander Lipatov