Tom Lane wrote:
No, the locks would be held till end of transaction. It is a little odd
that you have so many conflicts, though, unless the referenced table is
pretty small and/or this loop manages to touch a large fraction of the
possible keys.
Ah, well, if the locks are held to the end of th
Mott Leroy <[EMAIL PROTECTED]> writes:
> I'm still confused by the behavior however. The locking behaves as if
> its some kind of *table* level lock, because while the function is
> executing (a long time), a dozen updates and inserts build up waiting
> for some lock to be released. If the loop
Tom Lane wrote:
SELECTs don't lock any rows. INSERTs don't create any lockable rows
in themselves (other backends can't even see the rows yet). If that's
all that your transaction is doing, then I think the only explanation
is that the INSERTs are in a table that has foreign keys (correct?)
an
Mott Leroy <[EMAIL PROTECTED]> writes:
> Will postgres lock on all rows as it goes through this loop? Or can you
> give me a better idea of what I can expect to be locked in my example?
SELECTs don't lock any rows. INSERTs don't create any lockable rows
in themselves (other backends can't even s
Tom Lane wrote:
No, the ShareLock is the means used when transaction A needs to wait for
transaction B to complete --- it tries to take a share lock on xact B's
XID, which of course is blocked as along as B is holding its
ExclusiveLock. This is used when there is a row-update conflict, ie,
A wan
Mott Leroy <[EMAIL PROTECTED]> writes:
> One thing I noticed is it seems like for every transaction lock there's
> an "ExclusiveLock" (to be expected, a lock on the transaction num) as
> well as a "ShareLock" on the same transaction which has not been granted
> the lock -- does this mean that tw
Recently we discovered that a stored procedure that we run locks some
table(s) and prevents some SQL from running.
We discovered this because doing a simple grep against postgres
processes revealed several processes "WAITING":
-- snip --
00:04:31 postgres: dataman our_db 10.0.0.103 INSERT wai
