Em 13/06/2016 23:36, Edson Richter escreveu:
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
Just in case, I've run:
- vacuum full analyze verbose;
- reindex index ix_c_a_id;
Result I get same error. So, I'm inclined to discard that this is a
index error.
Interesting:
with qry as (select A.id
from A
where creatingdate < (now()::date - interval '12 month')
and 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)
order by A.id limit 2000)
select * from qry where id = 3240124;
Total query runtime: 2.2 secs
0 rows retrieved.
Why delete causes error, but querying don't?
Would it be a bug when using delete ... where id in (subquery)?
Regards,
Edson
|
