case for lock_timeout

[mallah]

Hi,

With reference to:

http://archives.postgresql.org/pgsql...6/msg00935.php

Consider a case

1. wherein a database dump takes 2 hrs to complete.
2. A cron gets fired which drops a certain table and recreates it.
3. A set of frequently requested web pages depends on reading from the table in 2

Since 2 will wait for 1 till it finishes, and 3 will wait for 2 this means those
set of webpages will become inaccessible while the backup is in progress.

Whereas if we have something like a lock_timeout 2 will abort within a limited
time duration automatically and the sys admin could get notified to reschedule
it and this problem can get averted.

I feel lock_timeout it will be a nice feature . Lemme know what would be
the solution of the above problem from existing set of features.

Regds
Mallah.



-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

<ma****@trade-india.com> writes:

I feel lock_timeout it will be a nice feature . Lemme know what would be
the solution of the above problem from existing set of features.

AFAICS statement_timeout would work just fine for that; or you could
use NOWAIT.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Rajesh Kumar Mallah]

Tom Lane wrote:

<ma****@trade-india.com> writes:

I feel lock_timeout it will be a nice feature . Lemme know what would be
the solution of the above problem from existing set of features.
AFAICS statement_timeout would work just fine for that; or you could
use NOWAIT.

ok, you mean we should put statement timeout with statements
that potentially lock table exclusively. eg

OTHER SQLS 1....
set statement_timeout = <some reasonable time>
DROP TABLE table_name;
set statement_timeout = 0;
OTHER SQLS 2....
BTW does drop table or alter table have any other reasons
to timeout other than waiting for a lock ? In former case
the query will get cancelled for an invalid reason.
Regds
mallah.
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Rajesh Kumar Mallah <ma****@trade-india.com> writes:

ok, you mean we should put statement timeout with statements
that potentially lock table exclusively. eg

Actually I think it'd work better to put NOLOCK on the read-only
operations. Those guys should never fail to get the lock they need
under ordinary circumstances. If you happen to be running some kind
of schema-altering process in parallel, then the read-only guys will
fail immediately instead of waiting, but AFAICT that's what you wanted.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org