Entity Framework, PostgreSQL, Optimistic Concurrency with hidden xmin column

4
votes

I'm working with Entity Framework (Model First approach) against a PostgreSQL 9.1 database.

You probably all know that every table has a hidden column called xmin that I would use to help EF determine if the row has changed before performing an update.

I know that the internals of PostgreSQL may change and that this might not be a good idea at all for production code, but I would like to try.

What would be the required steps to manually update the model to include this column for one table and test its behavior ?

TIA.

EDIT 1 : Here is where I'm so far, using the Model First approach with Self Tracking Entities.

The model has been modified with the following :

CSDL : <Property Name="xmin" Type="Decimal" Nullable="false" Precision="15" Scale="0" ConcurrencyMode="Fixed" />
SSDL : <Property Name="xmin" Type="numeric" Nullable="false" Precision="15" Scale="0" StoreGeneratedPattern="Computed" />

The xmin column is effectively retrieved on SELECT, then used during UPDATE :

UPDATE "schema"."mytable" SET "column1"=FALSE WHERE ("pk"=cast(7526 as numeric)) AND ("xmin"=cast(1185877 as numeric))

The problem here is with the cast used on the xmin column. It fails. What I've found so far, is that it works with no cast at all. But I'm not sure how to tell EF to use no cast at all in the query for this column.

BTW the xmin column datatype is 'xid' which is unknown to the Npgsql ADO.NET provider.

EDIT 2 : This is the provider that generates the SQL text. After looking into the provider code, I can confirm that when using an integer datatype there is no cast. So, I've used an integer in my entity to store the value and tried to select and update the entity. It fails with a concurrency exception.

System.Data.OptimisticConcurrencyException: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)

the pgsql log follows :

CEST LOG:  instruction : BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
CEST LOG:  instruction : UPDATE "schema"."mytable" SET "column1"=TRUE WHERE ("pk"=cast(7526 as numeric)) AND ("xmin"=1185882)
CEST LOG:  instruction : ROLLBACK

Humm, may be there is problem with retreiving the affected rows because, if the same query is executed by hand, I have one row updated...

EDIT 3 : I've enabled the npgsql debug log. When xmin has not changed I have the following (UPDATE 1):

7/2/2012 12:00:38 PM    12380   Debug   Entering NpgsqlState.ProcessBackendResponses()
7/2/2012 12:00:38 PM    12380   Debug   Entering PGUtil.ReadString()
7/2/2012 12:00:38 PM    12380   Debug   Get NpgsqlEventLog.LogLevel
7/2/2012 12:00:38 PM    12380   Debug   String read: UPDATE 1.

When it has changed I have the following (UPDATE 0):

7/2/2012 1:50:06 PM 12700   Debug   Entering NpgsqlState.ProcessBackendResponses()
7/2/2012 1:50:06 PM 12700   Debug   Entering PGUtil.ReadString()
7/2/2012 1:50:06 PM 12700   Debug   Get NpgsqlEventLog.LogLevel
7/2/2012 1:50:06 PM 12700   Debug   String read: UPDATE 0.

But unfortunately, in both cases I have an OptimisticConcurrencyException...

EDIT 4 : After reviewing the npgsql log, it seems that entity Framework is internally using DbCommand.ExecuteReader(CommandBehavior.SequentialAccess) followed with a reader.Read() to determine how many rows were affected by the UPDATE statement.

I may be wrong but the provider is returning false during ForwardsOnlyDataReader.Read() which might be the source of the problem.

EDIT 5 : This is definitely a provider issue (version 2.0.11.93 and upcoming version 2.0.11.94).

For an INSERT Statement, the provider only support computed columns when the underlying datatype is SERIAL (int) or BIGSERIAL (bigint).

For an UPDATE statement, the provider does not handle the Returning property of a given DbUpdateCommandTree. This means that no computed column is ever returned. You have to manually refresh the entity object after the call to SaveChanges(). Optimistic concurrency is unsupported so far.

I'll try to implement minimum support in the provider and keep you posted.

EDIT 6 : The Returning property is now handled in my own version of the provider. I'd be happy to share my code. Don't hesitate to ask.

1
entity-frameworkpostgresqloptimistic-concurrency
Regarding the problems with casting xmin: while it's internal implementation is unsigned 32-bit integer, it was not put into the numeric family of types because it has some unusual properties -- like a circular value range which wraps around. Naive use of comparisons other than equality are sure to break when the values wrap around, so be sure not to do that (or to make any other assumptions about it being anything other than a unquie ID). An interim cast to text should allow you to cast it to bigint or numeric. Or just use it as text. – kgrittn
@kgrittn Will the SQL text shown above AND ("xmin"=1185882) work or should it be AND ("xmin"='1185882') instead ? – Olivier MATROT
Either should work, but the quoted version is probably better, because of the signed versus unsigned issues. In PostgreSQL a quoted literal isn't taken to be a character string literal; it is a literal of unknown type, which can be implicitly used as any type, including xid. If you are using variables rather than literals, you might want to explicitly cast to xid, first casting.to text if needed. – kgrittn

1 Answers

2
votes

xmin is quite unlikely to go away any time soon, and can be used to implement a simple form of optimistic concurrency control for updates. Be careful in that internally it is an unsigned 32-bit number, so don't try to store it in a signed 32-bit field, or it is likely to work for a while and then break badly.

As long as you map it to an appropriate data type, read it in up front, include it in the WHERE clause of your UPDATE and take appropriate action on a "not found" condition it is pretty straightforward.