Re: Table locks

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribió:
> Andreas Seltenreich <seltenreich(at)gmx(dot)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