Custom ordering operator for type xid

Поиск
Список
Период
Сортировка
От Alexander Lipatov
Тема Custom ordering operator for type xid
Дата
Msg-id 2EA9BF93-EE2B-4B84-B21D-5EA86FA63913@mindbox.cloud
обсуждение исходный текст
Ответы Re: Custom ordering operator for type xid
Список pgsql-admin
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Achilleas Mantzios
Дата:
Сообщение: Re: Urgent: Segmentation Fault in PostgreSQL postmaster Process
Следующее
От: Veerendra Pulapa
Дата:
Сообщение: Re: Urgent: Segmentation Fault in PostgreSQL postmaster Process