SCM

[#1010939] EF4 Computed ConcurrencyToken (timestamp with now()) generates invalid currval sequence

View Trackers | Bugs | Download .csv | Monitor

Date:
2010-10-28 21:40
Priority:
3
State:
Open
Submitted by:
Atam Panday (atamgp)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.10
Category:
Group:
Resolution:
None
Summary:
EF4 Computed ConcurrencyToken (timestamp with now()) generates invalid currval sequence

Detailed description
Using Entity Framework 4 Code Only, i map my entities like:

public CountryMap()
{
HasKey(x => x.ID);
Property(x => x.ID).IsIdentity().StoreGeneratedPattern = StoreGeneratedPattern.None;
Property(x => x.creationDate);
Property(x => x.lastChangedDate) .IsConcurrencyToken().IsComputed().HasStoreType("timestamp");

....
MapSingleType(t => new
{
id = t.ID,
creationdate = t.creationDate,
creationuserid = t.creationUserID,
lastchangeddate = t.lastChangedDate,
lastchangeduserid = t.lastChangedUserID,

name = t.name,
code = t.code
}).ToTable("public.country");
}

SO, focus on the lastchangeddate property. This is meant for concurrency. On the pg server it is declared as:
ALTER TABLE country ADD COLUMN lastchangeddate timestamp without time zone;
ALTER TABLE country ALTER COLUMN lastchangeddate SET NOT NULL;
ALTER TABLE country ALTER COLUMN lastchangeddate SET DEFAULT now();


I want to mimic the mySQL/MS SQL DateTime. On Every update, i want this property to be part of the where clause in the Update statement. Like a Version kind of thing, very comon.

So, i use these:

Database["PostgreSQL"].ExecuteNonQuery(
@"CREATE OR REPLACE FUNCTION update_lastChangedDate_column()
RETURNS TRIGGER AS $BODY$
BEGIN
IF NEW.lastChangedDate = OLD.lastChangedDate THEN NEW.lastChangedDate := now(); END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';

CREATE TRIGGER trg_Country_update_lastChangedDate_column BEFORE UPDATE ON country FOR EACH ROW EXECUTE PROCEDURE update_lastChangedDate_column();

This works, and is ok. Only supplied so the reader has a beter idea of the whole.

When I insert a row, a lastchangeddate is filled with now(). On every update, its value is updated with now().

When i do insert in .Net, EF (see EF mapping above) and Npgsql generate this:

INSERT INTO "public"."country"("code","creationdate","creationuserid","id","lastchangeduserid","name") VALUES ('SR',TIMESTAMP '2010-10-28T23:29:27.9988733+02:00',NULL,'6d01a577-1baf-4454-aa16-9e1d01831f22',NULL,'Suriname');SELECT currval(pg_get_serial_sequence('"public"."country"', 'lastchangeddate')) AS "lastchangeddate"

If i look in the database, everything seems ok. lastchangeddate has a correct value!. But the entity which is saved, is not updated with the lastchangeddate.
It stays NULL in c#.

When i copy past the above SQL in pgAdmin, and execute it (with another GUID offcource) The result is empty. The data in de db is ok, with a correct lastchangeddate, but the select statement return empty.

Why?
Because it doesn't make sence:

SELECT currval(pg_get_serial_sequence('"public"."country"', 'lastchangeddate')) AS "lastchangeddate"

I have NO sequence. I have a timestamp column with a default value of now().
That's it. SO pg_get_serial_sequence('"public"."country"', 'lastchangeddate') returns nothing, and so does currval.

I think my point is made and understandable.
Thanks for your efforts and great work.

Followup

Message
Date: 2010-10-28 22:06
Sender: Atam Panday


ok, last addition:
the select with curval return nothing.
The following query does work:
INSERT INTO
"public"."country"("code","creationdate","creationuserid",
"id","lastchangeduserid","name") VALUES ('SR',TIMESTAMP
'2010-10-28T23:29:27.9988733+02:00',NULL,'6d01a577-1baf-
4454-aa16-9e1d01831f22',NULL,'Suriname');
SELECT
country.lastchangeddate
FROM
public.country
WHERE
country.id = '6d01a577-1baf-4454-aa16-9e1d01831f22';

Date: 2010-10-28 22:02
Sender: Atam Panday


My country is mapped like:

HasOptional<Country>(x => x.country)
.WithMany()
.HasConstraint((a, c) => a.countryID ==
c.ID);

So i have a country and a countryID .
Date: 2010-10-28 22:00
Sender: Atam Panday

I have a 2nd problem. Building on above, I have an address
entity, which has a reference to a country object ( the
above one, which is stored ok in the DB).

I removed all the funtions and triggers, and changed the
lastchangeddate mapping in EF to application side :
Property(x =>
x.lastChangedDate).IsConcurrencyToken().StoreGeneratedPatter
n = StoreGeneratedPattern.None;

This works ok, but:

The address entity is new yet, and its countryID is set to
the saved/sored country. When i perform a save, I expect a

Insert statement also. But somehow, the country is updated (
in a non-valid way) with an exception :
ERROR: 42601: syntax error at or near "WHERE" :

UPDATE "public"."country" WHERE
("id"='d9bd1374-95d0-4fa2-9b96-9e1d01895dee') AND
("lastchangeddate"=TIMESTAMP '2010-10-
28T23:52:13.0299486+02:00')

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge