Search Postgresql Archives

Index seems "lost" after consecutive deletes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I've a table "A" with 4,000,000 records.

I've decided to delete records from oldest to newest but I can't delete records that have references in tables "B", "C" or "D".


so, I've


with qry as (

    select A.id

      from A

    where not exists (select 1 from B where B.a_id = A.id)

       and not exists (select 1 from C where C.a_id = A.id)

       and not exists (select 1 from D where D.a_id = A.id)

       and A.creation_date < (now()::date - interval '12 month')

  order by A.id DESC

  limit 2000

)

delete from A where id in (select id from qry);


All three referenced tables have indexes (B.a_id; C.a_id; D.a_id) in order to make query faster.

So for first 2 million rows it worked really well, taking about 1 minute to delete each group of 2000 records.

Then, after a while I just started to get errors like:


ERROR: update or delete in "A" violates foreign key "fk_C_A" in "C".

DETAIL:  Key (id)=(3240124) is still referenced by table "C".


Seems to me that indexes got lost in the path - the query is really specific and no "C" referenced records can be in my deletion.

Has anyone faced a behavior like this?

Am I doing something wrong?


--
Atenciosamente,

Edson Carlos Ericksson Richter



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux