[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records
From:       Bèrto ëd Sèra <berto.d.sera () gmail ! com>
Date:       2013-02-01 11:54:26
Message-ID: CAKwGa_8z25aqgqtsqOeS4wvJBQNQkhqtKiQm69wdrYd6t6ZjhQ () mail ! gmail ! com
[Download RAW message or body]

righto. You need a CTE to do that

create table deletable (
  id bigint not null primary key);

create table condition ( id bigint not null primary key);

insert into deletable select generate_series(1,500);

insert into condition select generate_series(1,50);


WITH target AS (
select id
from deletable d
where
  d.id not in (select id from condition)
limit 10 )
delete
from deletable
where
  id in (select id from target);

This will open a hole from 51 to 60, next time from 61 to 70 etc...

Cheers
Bèrto

On 1 February 2013 11:30, Alexander Farber <alexander.farber@gmail.com> wrote:
> Thanks, I will add an index(id, yw) and check.
>
> The limit suggestion (yes, I already have a "vacuum"
> cronjob to purge spam-users from drupal_users etc.)
> doesn't work:
>
> # delete from pref_users
> where id not in (select distinct id from pref_money) limit 10;
> ERROR:  syntax error at or near "limit"
> LINE 2: ...ere id not in (select distinct id from pref_money) limit 10;
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]