SQLAlchemy Postrgresql XID type comparison

I have some code which protects from the concurrent updates using xmin value. In model it's mapped to the Integer like following

class MyTable(Base):
   version = mapped_column(Integer, name="xmin", system=True)

and then I compare it with:

update(MyTable).where(MyTable.version == int(row.version))

But I recently found that really it fails with an error

(psycopg.errors.NumericValueOutOfRange) integer out of range [SQL: ...xmin = %(xmin_1)s::INTEGER]

I checked String as recommended in the documentation but it also fails because operator does not exist: xid = character varying. And the same for the BigInteger type.

So what's the proper type for the xmin?

Just in case, I'm using psycopg 3.1.18 and sqlalchemy 2.0.29

something like xmin = value::XID but not sure how to do that

Why not go the other way and do xmin::text = value.

class MyTable(Base):
   version = mapped_column(String, name="xmin", system=True)

update(MyTable).where(String(MyTable.version) == String(row.version))

That should do it. Still, I agree with @Frank Heikens' remark that it might be easier for you to use the explicit or advisory locks, rather than try and tap into an internal MVCC machanics.

FOR UPDATE doesn't really work.

It does. Explicit locks are widely adopted, pretty easy to use and maintain. If it's your first time trying them, the typical beginner error is

• forgetting that they are only held until the end of transaction, commit/rollback
• if you don't switch off autocommit, they'll get released quietly by your client
• if you use a pool where subsequent queries end up in a different sessions, your next query may end up in a different session from the one that acquired the lock earlier
• locking the wrong thing and expecting something else to be locked

Advisory locks don't lock anything - they work like semaphores. You add requests for them in parts of your app that might interfere with each other and request a specific advisory lock before you attempt further operations. If there's currently a parallel worker doing that thing, it must've acquired the lock before you, so you'll wait (or get rejected, or skip to the nearest unlocked thing, depending on how you configure it).

---

If you take a look at pg_cast catalog, you'll find xid has only a single, explicit cast defined from xid8 to xid and nothing else:
_{demo at db<>fiddle}

select castsource::regtype
      ,casttarget::regtype
      ,castfunc::regproc
      ,case castcontext when 'e' then 'explicit'
                        when 'a' then 'assignment'
                        when 'i' then 'implicit' end as castcontext
      ,case castmethod when 'f' then 'cast function'
                       when 'i' then 'input/output function'
                       when 'b' then 'binary coercion' end as castmethod
from pg_cast 
where casttarget::regtype='xid'::regtype 
   or castsource::regtype='xid'::regtype;

Behind = operator there's a bunch of functions defined to work with different types on each side:

SELECT n.nspname as "Schema",
  o.oprname AS "Name",
  CASE WHEN o.oprkind='l' THEN NULL 
       ELSE pg_catalog.format_type(o.oprleft, NULL) END AS "Left arg type",
  CASE WHEN o.oprkind='r' THEN NULL 
       ELSE pg_catalog.format_type(o.oprright, NULL) END AS "Right arg type",
  pg_catalog.format_type(o.oprresult, NULL) AS "Result type",
  o.oprcode AS "Function",
  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),
           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS "Description"
FROM pg_catalog.pg_operator o
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
WHERE o.oprname OPERATOR(pg_catalog.~) '^(=)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_operator_is_visible(o.oid)
ORDER BY 1, 2, 3, 4;

If Postgres doesn't see a combination that matches your pair of arguments, it'll try to see if there's an implicit cast defined that would convert one of them, forming a pair it knows how to compare. Interestingly enough, there is a xid=int4 comparison in there:

Which is why that error message didn't complain about the operator but the integer format. Problem is, it's mostly there for convenience, so that you can type things like where xmin=12345 - as pointed out earlier, there's no cast, because these types are incompatible. They are both 32 bits, but xid is unsigned, and that means half its values are above int4's upper limit. The xid you got and tried to cast must've been in that range.

You can kind of always rely on text:

“automatic I/O conversion casts”, those performed using a data type's own I/O functions to convert to or from text or other string types, are not explicitly represented in pg_cast.

But that's a bit misleading. If you try any of these, it'll fail:

select xmin = xmin::text from test;
select xmin = 260498::text from test;
select xmin = '260498'::text from test;

ERROR:  operator does not exist: xid = text
LINE 1: select xmin=xmin::text from test;

That's because an explicit cast will disable further guesswork on Postgres side - it will not attempt to convert that text on the right further, to match the xid on the left. This will work though:

select xmin = '260498' from test;

Because that's not really a text yet. It's an unknown that Postgres will guess needs to be converted or rather assigned to match the left operand, like so:

select xmin = xid('260498') from test;

If you can get sqlalchemy to translate your request to one of these, it'll work. Or, you can circle back to the idea up top:

select xmin::text = '260498'::text from test;

The pg_cast catalog doesn't mention that there's a cast (conversion, assignment) method from an initially unknown input to every type, but it also doesn't mention every type can be cast to text. So you can keep your value in text, and cast the other thing to text also, before comparison.