Re: Query on Postgres locks

On Thu, Sep 24, 2015 at 11:33 AM, Porwal, Utkarsh <utkarsh(dot)porwal(at)emc(dot)com>
wrote:

> Yes and hence my original question- why would the queries and the locks
> waiting to be granted disappear when there is no timeout?
>
> Do you have a timeout set in DBI ?

--Scott

> Sent from my Android phone using Symantec TouchDown (www.symantec.com)
>
>
> -----Original Message-----
> *From:* Jan Harasym [jan(dot)harasym(at)massive(dot)se]
> *Received:* Thursday, 24 Sep 2015, 20:31
> *To:* Porwal, Utkarsh [utkarsh(dot)porwal(at)emc(dot)com]; Wei Shan [
> weishan(dot)ang(at)gmail(dot)com]
> *CC:* pgsql-admin(at)postgresql(dot)org [pgsql-admin(at)postgresql(dot)org]
> *Subject:* RE: [ADMIN] Query on Postgres locks
>
> For many things “0” is “disabled”
>
>
>
> So there is no timeout, it will wait forever.
>
>
>
> *From:* pgsql-admin-owner(at)postgresql(dot)org [mailto:
> pgsql-admin-owner(at)postgresql(dot)org] *On Behalf Of *Porwal, Utkarsh
> *Sent:* September 24, 2015 12:37 PM
> *To:* Wei Shan
> *Cc:* pgsql-admin(at)postgresql(dot)org
> *Subject:* Re: [ADMIN] Query on Postgres locks
>
>
>
> Hi Wei shan,
>
>
>
> For me the statement_timeout is defaulted to zero
>
>
>
> statement_timeout | 0
> | ms
>
>
>
> *Thanks and Regards,*
>
> *Utkarsh Porwal*
>
>
>
>
>
> *From:* Wei Shan [mailto:weishan(dot)ang(at)gmail(dot)com <weishan(dot)ang(at)gmail(dot)com>]
> *Sent:* Thursday, September 24, 2015 3:49 PM
> *To:* Porwal, Utkarsh
> *Cc:* pgsql-admin(at)postgresql(dot)org
> *Subject:* Re: [ADMIN] Query on Postgres locks
>
>
>
> Hi,
>
>
>
> If you look at pg_settings table, you can see the default value for
> statement_timeout. Only from 9.3, there's a parameter called lock_timeout.
>
>
>
> Cheers.
>
>
>
> On 24 September 2015 at 17:37, Porwal, Utkarsh <utkarsh(dot)porwal(at)emc(dot)com>
> wrote:
>
> The postgres version is –
>
>
>
> psql (9.0.7)
>
>
>
>
>
> *From:* pgsql-admin-owner(at)postgresql(dot)org [mailto:
> pgsql-admin-owner(at)postgresql(dot)org] *On Behalf Of *Porwal, Utkarsh
> *Sent:* Thursday, September 24, 2015 3:04 PM
> *To:* pgsql-admin(at)postgresql(dot)org
> *Subject:* [ADMIN] Query on Postgres locks
>
>
>
> Dear All,
>
>
>
> I have a perl script which will attempt to drop/recreate some intermediate
> tables. This works by trying to acquire an access exclusive lock on the
> intermediate tables.
>
>
>
> When there is a backup running through pg_dump, I see an entry in
> pg_locks/pg_stat_activity table for the above which is waiting for pg_dump
> to finish, which is expected.
>
>
>
> Since backup on huge environment takes around 8 hrs to complete, I noticed
> that the entries waiting for locks are removed after certain amount of time
> which I couldn’t record even though the perl script is still running.
>
> Do you guys know if the lock waiting will ultimately timeout and when? I
> don’t have any statement_timeout specified.
>
>
>
> Any pointers on this behavior?
>
>
>
> Regards,
>
> Utkarsh
>
>
>
>
>
> --
>
> Regards,
> Ang Wei Shan
>