agora inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

Custom ordering operator for type xid
4+ messages / 3 participants
[nested] [flat]

* Custom ordering operator for type xid
@ 2024-06-17 10:49  Alexander Lipatov <lipatov@mindbox.cloud>
  0 siblings, 1 reply; 4+ messages in thread

From: Alexander Lipatov @ 2024-06-17 10:49 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Custom ordering operator for type xid
@ 2024-06-17 15:03  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alexander Lipatov <lipatov@mindbox.cloud>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2024-06-17 15:03 UTC (permalink / raw)
  To: Alexander Lipatov <lipatov@mindbox.cloud>; +Cc: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Custom ordering operator for type xid
@ 2024-06-17 15:33  Rui DeSousa <rui.desousa@icloud.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Rui DeSousa @ 2024-06-17 15:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alexander Lipatov <lipatov@mindbox.cloud>; pgsql-admin



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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Custom ordering operator for type xid
@ 2024-06-17 15:40  Alexander Lipatov <lipatov@mindbox.cloud>
  parent: Rui DeSousa <rui.desousa@icloud.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Alexander Lipatov @ 2024-06-17 15:40 UTC (permalink / raw)
  To: Rui DeSousa <rui.desousa@icloud.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2024-06-17 15:40 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-17 10:49 Custom ordering operator for type xid Alexander Lipatov <lipatov@mindbox.cloud>
2024-06-17 15:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-17 15:33   ` Rui DeSousa <rui.desousa@icloud.com>
2024-06-17 15:40     ` Alexander Lipatov <lipatov@mindbox.cloud>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox