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
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.state FROM staging_area.integration_staging_data sd,
((((((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));
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?
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'
> > 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
> 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
Salzburg / Austria / Europe
In response to
pgsql-novice by date
|Next:||From: Karen Castillo||Date: 2011-02-01 09:47:14|
|Subject: New table|
|Previous:||From: YAMAMOTO Takashi||Date: 2011-02-01 08:35:02|
|Subject: Re: [NOVICE] systable_getnext_ordered |