From: | Edson Richter <edsonrichter(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index seems "lost" after consecutive deletes |
Date: | 2016-06-14 02:36:52 |
Message-ID: | BLU436-SMTP25210DAE790310845459109CF540@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Em 13/06/2016 23:18, rob stone escreveu:
> On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote:
>> Em 13/06/2016 22:33, Edson Richter escreveu:
>>> 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?
>>>
>>>
>> Of course:
>> Version string PostgreSQL 9.4.8 on x86_64-unknown-linux-gnu,
>> compiled
>> by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
>> Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
>> Computer is Dell R420 with mirrored disks, 80GB of RAM (database has
>> <
>> 40GB in total).
>>
>> Sorry for not putting the info in the first e-mail.
>>
>> Edson
>>
>>
> What does:-
>
> SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;
>
> return?
>
> Is it a many-to-one or a one-to-one relationship?
>
>
SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;
count
-------
1
(1 registro)
A.id is primary key of A table. Each table has its own primary key.
Relationship to others table is 1-N, being N = {0,1}
A.id -> B.a_id (being B.a_id unique but not enforced by unique key)
A.id -> C.a_id (being C.a_id unique but not enforced by unique key)
A.id -> D.a_id (being D.a_id unique but not enforced by unique key)
Regards,
Edson
From | Date | Subject | |
---|---|---|---|
Next Message | Edson Richter | 2016-06-14 03:22:13 | Re: Index seems "lost" after consecutive deletes |
Previous Message | Melvin Davidson | 2016-06-14 02:24:44 | Re: Automate copy - Postgres 9.2 |