Hi all,
we have a theory for the root of all evil which causes a reproducable
deadlock which is not detected by Postgre:
The DELETE statement contains a select which waits for a sharelock
(according to pg_locks and pg_stat_activity) on rows locked by the
UPDATE statement. The UPDATE itself waits to get a lock for some rows
which are exclusively locked by the DELETE statement (got from its
sub-SELECT).
What do you think about this theory?
thx,
Peter
2006/10/19, Peter Bauer <[EMAIL PROTECTED]>:
thank you very much, we will test it
br,
Peter
2006/10/19, Jim C. Nasby <[EMAIL PROTECTED]>:
> On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote:
> In the update statement, don't wrap the ID values in quotes. At best
> it's extra work; at worse it will fool the planner into not using the
> index.
>
> > shared_buffers = 1000 # min 16 or max_connections*2, 8KB each
>
> This is *way* too small for what you're trying to do. Try a minimum of
> 10% of memory, and 50% of memory may be a better idea.
>
> > #temp_buffers = 1000 # min 100, 8KB each
> > #max_prepared_transactions = 5 # can be 0 or more
> > # note: increasing max_prepared_transactions costs ~600 bytes of shared
memory
> > # per transaction slot, plus lock space (see max_locks_per_transaction).
> > work_mem = 20480 # min 64, size in KB,
>
> Making that active might help a lot, but beware of running the machine
> out of memory...
>
> > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
>
> Probably needs to get increased.
>
> > #bgwriter_delay = 200 # 10-10000 milliseconds between rounds
> > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
> > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
> > #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
> > #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
>
> The background writer might need to be tuned more aggressively.
>
> > #checkpoint_warning = 30 # in seconds, 0 is off
>
> I'd set that closer to 300 to make sure you're not checkpointing a lot,
> though keep in mind that will impact failover time.
>
> > effective_cache_size = 44800 # typically 8KB each
>
> The machine only has 1/2G of memory?
>
> > #autovacuum_naptime = 60 # time between autovacuum runs, in secs
>
> I'd drop that to 30.
>
> > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
> > # vacuum
> > #autovacuum_analyze_threshold = 500 # min # of tuple updates before
> > # analyze
> > #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
> > # vacuum
> > #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
> > # analyze
>
> I'd cut the above 4 in half.
>
> --
> Jim Nasby [EMAIL PROTECTED]
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
