Process 1 (massive update): update table A set column1=0, column2=0
Process 2 (multiple delete): perform delete_row(user_name, column1, column2)
from table A where user_name=YYY
The pgsql function delete_row delete the row and do other business logic not
related to table A.
-Mensaje origi
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
11 matches
Mail list logo
