Re: handling concurrency right why am i wrong?

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

On Mon, Jan 31, 2011 at 6:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Christian Brennsteiner <eingfoan(at)yahoo(dot)de> writes:
> > i have a simple updateable view V with a status field S.
> > ...
> > each clients tries to ----------------- update V set S ='$MYCLIENTID'
> where
> > S = 'TOBEPROCESSED'
> > in this way i try to reserve the current available data TOBEPROCESSED for
> > one client and then process it.
> > when i do this i sometimes (if they overlap) get the following exception:
> > Stacktrace: java.sql.SQLException: ERROR: deadlock detected
>
> There are no "simple updateable views" in Postgres. Your problem is
> probably traceable to some aspect of the view update rule, or possibly
> something about foreign keys or other actions that have to be taken
> pursuant to the update on the underlying table. But since you haven't
> shown us any of the schema details, it's impossible to do more than
> guess.
>
> In general it seems like you're trying to reinvent a queuing mechanism.
> You'd be better off adopting one of the existing ones, as getting this
> both right and high-performing is harder than one might think.
>
> regards, tom lane
>

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