Re: Lock ACCESS EXCLUSIVE and Select question !

On Mon, Feb 28, 2011 at 12:43:58PM -0500, Alan Acosta wrote:

> I'm using lock with ACCESS EXCLUSIVE in several of my tables to assure that
> only one process write in those tables at same time

Why are you doing that? It sounds like a bad idea to me.

But anyway, I believe that the SHARE lock (which is what CREATE INDEX
uses) ought to work. It should prevent any concurrent data
alterations in the table.

Also,

> May be a little newbie question, but i cannot find this answer in
> http://www.postgresql.org/docs/8.1/static/explicit-locking.html or similar

those are the docs for 8.1.x. Note that it was EOL'd last November:

http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

> pages, i really want to know if new rows inserted in an open transaction
> will be read it by another threads or this new rows are invisible no matter
> the mode of the transaction.

Rows inserted by an uncommitted transaction are invisible to everyone
else until the transaction commits. Postgres doesn't have dirty
reads. If you have an open transaction and you look at a table where
another transaction has committed, then you will or will not see the
resulting rows depending on whether you are in READ COMMITTED or
SERIALIZABLE isolation mode, respectively.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca