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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
