original-
De: Claudio Freire [mailto:klaussfre...@gmail.com]
Enviado el: viernes, 05 de octubre de 2012 10:27 a.m.
Para: Jeff Janes
CC: Anibal David Acosta; pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] how to avoid deadlock on masive update with multiples
delete
On Thu, Oct 4, 2012 at 1
On Friday, October 05, 2012 05:46:05 PM Tom Lane wrote:
> Andres Freund writes:
> > On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
> >> There's no guarantee that the planner won't re-sort the rows coming from
> >> the sub-select, unfortunately.
> >
> > More often than not you can prevent
Merlin Moncure writes:
> Can't it be forced like this (assuming it is in fact a vanilla order
> by problem)?
> EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY
> ctid FOR UPDATE) x where x.g = test.g;
> (emphasis on 'for update')
Hm ... yeah, that might work, once you redefine the pr
On Fri, Oct 5, 2012 at 12:46 PM, Tom Lane wrote:
>
> FOR c IN SELECT ctid FROM table WHERE ... ORDER BY ... LOOP
> DELETE FROM table WHERE ctid = c;
> END LOOP;
Maybe, in that sense, it would be better to optimize client-server
protocol for batch operations.
PREPA
On Fri, Oct 5, 2012 at 10:46 AM, Tom Lane wrote:
> Andres Freund writes:
>> On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
>>> There's no guarantee that the planner won't re-sort the rows coming from
>>> the sub-select, unfortunately.
>
>> More often than not you can prevent the planner
Andres Freund writes:
> On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
>> There's no guarantee that the planner won't re-sort the rows coming from
>> the sub-select, unfortunately.
> More often than not you can prevent the planner from doing that by putting a
> OFFSET 0 in the query. No
On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
> Maciek Sakrejda writes:
> > Presumably something like this?:
> > maciek=# CREATE TABLE test AS SELECT g, random() FROM
> > generate_series(1,1000) g;
> > CREATE
> > maciek=# EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY
> > ct
Maciek Sakrejda writes:
> Presumably something like this?:
> maciek=# CREATE TABLE test AS SELECT g, random() FROM
> generate_series(1,1000) g;
> CREATE
> maciek=# EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY
> ctid) x where x.g = test.g;
There's no guarantee that the planner won't
Presumably something like this?:
maciek=# CREATE TABLE test AS SELECT g, random() FROM
generate_series(1,1000) g;
CREATE
maciek=# EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY
ctid) x where x.g = test.g;
QUERY PLAN
--
On Thu, Oct 4, 2012 at 1:10 PM, Jeff Janes wrote:
> The bulk update could take an Exclusive (not Access Exclusive) lock.
> Or the delete could perhaps be arranged to delete the records in ctid
> order (although that might still deadlock). Or you could just repeat
> the failed transaction.
How do
From: Anibal David Acosta [mailto:a...@devshock.com]
Sent: Thursday, October 04, 2012 10:01 AM
To: pgsql-performance@postgresql.org
Subject: how to avoid deadlock on masive update with multiples delete
.
.
.
The other situation could be that update process while blocking rows scale
On Thu, Oct 4, 2012 at 7:01 AM, Anibal David Acosta wrote:
> Hi,
>
> I have a table with about 10 millions of records, this table is update and
> inserted very often during the day (approx. 200 per second) , in the night
> the activity is a lot less, so in the first seconds of a day (00:00:01) a
>
Hi,
I have a table with about 10 millions of records, this table is update and
inserted very often during the day (approx. 200 per second) , in the night
the activity is a lot less, so in the first seconds of a day (00:00:01) a
batch process update some columns (used like counters) of this table
13 matches
Mail list logo
