sql: add support for concurrency token column

[giangpham712]

Is your feature request related to a problem? Please describe.
Entity Framework Core supports the concept of optimistic concurrency - a property on your entity is designated as a concurrency token, and EF Core detects concurrent modifications by checking whether that token has changed since the entity was read.

In PostgreSQL, the xmin holds the ID of the latest updating transaction. Since this value automatically gets updated every time the row is changed, it is ideal for use as a concurrency token.

https://www.npgsql.org/efcore/modeling/concurrency.html?tabs=data-annotations

This xmin column doesn't exist in CockroachDB

Additional context

This issue affects some tests in efcore.pg library

Jira issue: CRDB-30401

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[fqazi]

@giangpham712 Would crdb_internal_mvcc_timestamp be sufficient here? It's a timestamp each row has for when it was last modified. We can't support the full Postgres equivalent here, since it is leaky and specific to their implementation.

[rafiss]

Closing as a duplicate of #33296, but I don't think we will work on this soon.

[giangpham712]

@fqazi There are several challenges with using crdb_internal_mvcc_timestamp.

One issue I'm having right now is this SQL

INSERT INTO "Owners" ("Id", "Name")
VALUES ('GIANG', 'Giang')
RETURNING crdb_internal_mvcc_timestamp;

is returning error column "crdb_internal_mvcc_timestamp" does not exist

[fqazi]

@giangpham712 So, we can get equivalent behaviour by using SHOW COMMIT TIMESTAMP or cluster_logical_timestamp(). The first option is preferred because of performance reasons. See: #79591