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
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 |