Thread: 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.
Yes exactly. And in Postgres they use xmin as an analogue of SQL Server rowversion/timestamp ([docs](https://www.npgsql.org/efcore/modeling/concurrency.html?tabs=data-annotations)).
Ok, I think I'll wait for their response and won't do anything questionable on the part of Postgres itself. Thank you!
17 июня 2024 г., в 18:33, Rui DeSousa <rui.desousa@icloud.com> написал(а):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 these operators?
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 table where 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.