Custom ordering operator for type xid

From: Alexander Lipatov <lipatov(at)mindbox(dot)cloud>
To: pgsql-admin(at)postgresql(dot)org
Subject: Custom ordering operator for type xid
Date: 2024-06-17 10:49:53
Message-ID: 2EA9BF93-EE2B-4B84-B21D-5EA86FA63913@mindbox.cloud
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

**Context**: We use Entity Framework Core as an ORM for Postgres. This ORM uses the `xmin` system column to implement optimistic concurrency 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 but I 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 could confuse 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Veerendra Pulapa 2024-06-17 12:54:23 Re: Urgent: Segmentation Fault in PostgreSQL postmaster Process
Previous Message Achilleas Mantzios 2024-06-17 05:14:07 Re: Urgent: Segmentation Fault in PostgreSQL postmaster Process