Yugabyte compatibility issues when inserting new tasks

[jqueuniet]

We're investigating a migration from PostgreSQL to Yugabyte (a PostgreSQL-compatible distributed database re-using the PostgreSQL querier) and running into compatibility issues with the xmax column used during upserts, that is currently unsupported (see https://docs.yugabyte.com/stable/yugabyte-voyager/known-issues/postgresql/#system-columns-is-not-yet-supported and yugabyte/yugabyte-db#24843 ).

Exact symptoms are the following error at each task insertion: ERROR: system column "xmax" is not supported yet (SQLSTATE 0A000) . Root cause seems to be the SQL statement at

river/riverdriver/riverpgxv5/internal/dbsqlc/river_job.sql

Line 321 in 7ac5a2c

RETURNING sqlc.embed(river_job), (xmax != 0) AS unique_skipped_as_duplicate;

It seems a workaround is already in place for SQLite, could it be possible to expose it through an option or something similar?

[brandur]

Hmm, do you know if Yugabyte supports the new Postgres 18 convention where you can check against OLD? e.g.

INSERT INTO users (id, name, email) 
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) 
DO UPDATE SET 
    name = EXCLUDED.name,
    email = EXCLUDED.email
RETURNING *, (OLD IS NULL) AS is_new;

[brandur]

NM, looks like they max out at Postgres 15 compatibility.

[jqueuniet]

AFAIK, they plan to upgrade the PostgreSQL version supported, but not anytime soon.

[brandur]

Opened #1347 to see if we can get this working. Can you give us your SELECT version()? I just want to make sure the version matching I put in will work against that as expected.

[jqueuniet]

PostgreSQL 15.12-YB-2025.2.1.0-b0 on x86_64-pc-linux-gnu, compiled by clang version 19.1.0 (https://github.com/yugabyte/llvm-project.git a2a6b655e14e7fa1fcf1011a6cb29cb8575249c0), 64-bit. We're currently running the latest LTS.

Thanks for the MR, do you mind if we provide feedback from running it on our test environment once it is ready?

[brandur]

Thx.

Thanks for the MR, do you mind if we provide feedback from running it on our test environment once it is ready?

Yep that'd be great. In fact, if you get the opportunity, trying River off that branch to see it works properly would be amazing. I confirmed that we're not using any Postgres 16+ features (River still works fine against Postgres 15), but I can't tell if there are any other xmax-like quirks that we haven't found yet.

[jqueuniet]

Posted feedback on the MR, basically it seems to work as expected now.