Re: Understanding of LOCK and pg_sleep interaction

OK, so I try the same scripts with pgAdminIII and they work as expected.

Sorry for the noise.

David J.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, March 07, 2011 1:20 PM
To: David Johnston
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Understanding of LOCK and pg_sleep interaction

"David Johnston" <polobo(at)yahoo(dot)com> writes:
> In trying to setup a test for a LOCK 'table' algorithm I attempt to
> execute two transactions where the first one issues a pg_sleep(10)
> while 'table' is locked and the second one attempts LOCK 'table'
> during the time when the pg_sleep is executing. When pg_sleep()
> returns in the first transaction the subsequent statement is not
> executed. Meanwhile, the second transaction continues to wait for the
> lock. Thus, a deadlock has occurred. I am doing my testing within
> PostGreSQL Maestro running as a "script" and issuing BEGIN and COMMIT
statements around the desired transaction commands.

I don't know anything about PostGreSQL Maestro, but what it sounds like from
this description is that it's not committing the transaction right away when
the script finishes. You might try turning on log_statement on the server
side so you can see exactly what commands are being sent and when.

regards, tom lane