From: Jaime Casanova <systemguards@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Table locks
Date: Wed, 6 Oct 2004 15:36:42 -0500 (CDT)
Message-ID: <20041006203642.19308.qmail@web50004.mail.yahoo.com> (raw)
--- Tom Lane <tgl@sss.pgh.pa.us> escribió:
> Andreas Seltenreich <seltenreich@gmx.de> writes:
> > Jake Stride writes:
> >> I thought of doing:
> >> SELECT max(jobno) from jobs where companyid=1;
>
> > I think SELECT FOR UPDATE should work fine here.
>
> Nope; he'll get something like
>
> regression=# select max(unique1) from tenk1 for
> update;
> ERROR: SELECT FOR UPDATE is not allowed with
> aggregate functions
>
> His best bet is probably
>
> BEGIN;
> LOCK TABLE jobs;
> SELECT max(jobno) from jobs where companyid=1;
> INSERT INTO jobs ...
> COMMIT;
>
> This is pretty horrid from a concurrency point of
> view but I don't think
> there's any other way to meet the "no gaps"
> requirement.
>
> You could reduce the strength of the lock a bit, for
> instance
> LOCK TABLE jobs IN EXCLUSIVE MODE;
> which would allow readers of the jobs table to
> proceed concurrently,
> but not writers. If you were willing to assume that
> all inserters into
> jobs are cooperating by explicitly obtaining the
> correct lock, you
> could reduce it to
> LOCK TABLE jobs IN SHARE UPDATE EXCLUSIVE MODE;
> which is the lowest self-conflicting table lock
> type. This would allow
> unrelated updates to the jobs table to proceed
> concurrently too (though
> not VACUUMs). See
>
>
http://www.postgresql.org/docs/7.4/static/explicit-locking.html>
> regards, tom lane
>
Hi,
Talking about lock tables there is a way to do a
select ... for update
and then a
update .. where current of ...
I think it require a select for update in a cursor.
Thanx in advance,
Jaime Casanova
_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: systemguards@yahoo.com
Subject: Re: Table locks
In-Reply-To: <20041006203642.19308.qmail@web50004.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox