Re: Table locks

 --- 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