Em 14/06/2016 12:02, Edson Richter escreveu:
Em 14/06/2016 10:32, David G. Johnston escreveu:
On Tue, Jun 14, 2016 at 12:51 AM, Edson Richter
<edsonrich...@hotmail.com>wrote:
Em 14/06/2016 01:33, David G. Johnston escreveu:
On Monday, June 13, 2016, Edson Richter
<edsonrich...@hotmail.com <mailto:edsonrich...@hotmail.com>> wrote:
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;
Why do you assume 3240124 is within the first 2000 qualified
records that the CTE is limited to checking?
Because this is the key causing the error on delete.
Wasn't the point though I do suspect your expectations are
acceptable in this instance. If you truly want to see if qry
contains 3240124 you should lose the LIMIT 2000.
Running the risk to deviate the focus, if records are ordered in the
query, limiting them will always produce same result.
Total query runtime: 2.2 secs
0 rows retrieved.
Why delete causes error, but querying don't?
Given the error message this answer seems self-evident...
Not self-evident to me: delete says I'm trying to delete the
record with id = 3240124 and I can't. But the select says this
record with id = 3240124 is not there!!!
This error:
ERROR: update or delete in "A" violates foreign key "fk_C_A" in
"C".
is impossible to encounter when executing a pure select...
Yes, but is also impossible to get this error if the record is not in
the subquery results. That's why I've executed the query filtering
id=3240124.
If this record is not in the subquery, why does the "delete..." is
trying to remove it?
Would it be a bug when using delete ... where id in (subquery)?
I'm unsure regarding the solution but I suspect the problem is
that between selecting the A row and deleting it another
concurrent process added a record to C that, if you were to
re-run the select would cause the row from A to be skipped. But
the single query doesn't have that option so it ends up failing.
There is a FOR UPDATE clause you can add to the select but I
don't think that works here since table C is the one being
altered and at the time of the query there is nothing to lock.
I'm doubting this is a bug, just poor concurrency
understanding. Sorry I cannot enlighten further at the moment.
There is not other process adding/updating records - and even
there is, the ID would be so high (because it is a sequence) that
cannot be the 3240124 (this record has been generated more than 2
years ago - that's why my query has the "creationdate" filter - I
don't touch records created within last 12 months).
Also, I've tried the same with "for update": same error!
Have you confirmed that
"fk_C_A" is referencing the columns you think it is?
Yes, first thing. All references are maintained by a automated system.
If the relation is not there, then it will be automatically created.
But your question raised another interesting line of investigation: if
there is any other cascading foreign keys pointing to A table.
Until now, I've been concentrated in the related tables, but would be
possible that another FK is cascading, which in turn would have
another cascade that is causing the error.
Is there any "debug level log" I can enable so I can see the chain of
cascading delete/update for foreign keys?
Edson
What's the history of this machine? Did you pass through 9.3
(especially early releases) on your way to 9.4?
Started with 9.0, then 9.1, 9.2, 9.3 and now 9.4.
Nevertheless, for every migration I've used a "dump" and "restore" to
avoid the "upgrade" caveats.
For example, when migrating from 9.3 to 9.4, I've used "9.4" pg_dump
to create the dump, and then "9.4" pg_restore to restore it in the new
cluster.
Edson
David J.
