Re: Custom ordering operator for type xid

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.