Re: handling concurrency right why am i wrong? - Mailing list pgsql-novice

hi tom,

thanks for your answer!
we have now tried to set TRANSACTION_SERIALIZEABLE and we are seeing a behaviour that we can deal with.
if we set TRANSACTION_SERIALIZEABLE on the database connection we get exceptions if someone else is dealing with the data at the same time.

might this be sufficient? or do we have to to dig deeper into this?
right now this behaviour is acceptable for us.

the schema would be:

CREATE VIEW staging_area.integration_staging
AS
SELECT
sd.id,
sd.creationtimestamp,
country.name AS country,
myDepartment.name AS myDepartmentname,
det.detectorobjectname AS areaid,
CASE WHEN (det.intervalduration <= 0) THEN sd.eventtimestamp ELSE (sd.eventtimestamp - ('00:00:01'::interval * (det.intervalduration)::double precision)) END AS eventtimefrom,
sd.eventtimestamp AS eventtimeto,
(sd.actionid)::character varying(10) AS actionid,
sd.actionvalue,
sd.state FROM staging_area.integration_staging_data sd,
detectorobject det,
locations country,
locations myDepartment,
locations platform,
meassurepoints,
meassurepoint_associations
WHERE
((((((sd.fk_do_id = det.id)
AND (myDepartment.parent_id = country.id))
AND (platform.parent_id = myDepartment.id))
AND (meassurepoints.fk_location_id = platform.id))
AND (meassurepoint_associations.fk_meassurepoint_id = meassurepoints.id))
AND (meassurepoint_associations.fk_do_id = det.id));
GO

we always just update state from TOBEPROCESSED to MYID and then to DONE.

@reinventing queueing --> can you give me a hint to documentation where something like that is described?

regards chris




--
----------
Christian Brennsteiner
Salzburg / Austria / Europe