Обсуждение: Custom ordering operator for type xid

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

Alexander Lipatov <lipatov@mindbox.cloud> writes:
> **Question**: Is it safe to create custom ordering operators for the `xid` type and a default operator class with
theseoperators? 

I wouldn't do it, mainly because the semantics of what you've written
have nothing to do with the actual behavior of xids.  (The real
comparison behavior is "circular", which can't be modeled as a total
order, which is why there's not a built-in opclass already.)

What is that ORM doing with XIDs anyway, and is there a good reason
not to run away screaming from such an ill-thought-out product?
I don't believe for a minute that this is going to be the only
semantic issue you'll run into with an ORM that thinks it knows
how XIDs behave despite a clear lack of even the most minimal
investigation into the question.

            regards, tom lane

> On Jun 17, 2024, at 11:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Alexander Lipatov <lipatov@mindbox.cloud> writes:
>> **Question**: Is it safe to create custom ordering operators for the `xid` type and a default operator class with
theseoperators? 
>
> I wouldn't do it, mainly because the semantics of what you've written
> have nothing to do with the actual behavior of xids.  (The real
> comparison behavior is "circular", which can't be modeled as a total
> order, which is why there's not a built-in opclass already.)
>
> What is that ORM doing with XIDs anyway, and is there a good reason
> not to run away screaming from such an ill-thought-out product?
> I don't believe for a minute that this is going to be the only
> semantic issue you'll run into with an ORM that thinks it knows
> how XIDs behave despite a clear lack of even the most minimal
> investigation into the question.
>
>             regards, tom lane
>
>


Assuming they are using it for opportunist locking.  I have use this method before but not with a system column.

i.e.

1. Application fetches the record with an xmin of 55, no need to maintain an open transaction:  select xmin, * from
tablewhere id = 8;   
2. Application edits record
3. Application saves record:  update table set col1 = ‘x’ where id = 8 and xmin = 55;
4. If the record was updated by another session then xmin would be different, the save would fail by updating zero
records,and user would have to reedit the record. 

Not a fan of ORMs myself but I think opportunist locking has its place.