[Dbix-class] ResultSet->delete() unexpectedly fetches and enumerates PKs of affected cells

[Daniel Böhmer]

Hi,

this is part of a long running debug story. I can't exactly recall on 
which
channels I've already asked questions related to this issue. The real 
case
is about deleting a "project" in a database with all its related records
before I applied ON-DELETE-triggers.

However I recently compiled a minimal example of the issue and I'd like 
to
understand what actually happens and get a workaround or a fix.

The problem occurs with a resultset of all records of 2nd-level 
relationship.

A has many B.
B has many C.
Delete all C of all B of A(42).

For other actions than delete(), i.e. select a whole column, it works as
I had expected:

> SELECT c.x FROM b me  JOIN c c ON c.b = me.id WHERE ( me.a = ? ): '1'

For delete() it starts a transaction, fetches all PKs and enumerates
them in a new statement. This made an actual app explode when the
query size increased over the maximum allowed length because of all
the IDs listed.

> BEGIN WORK
> SELECT c.b, c.x FROM b me  JOIN c c ON c.b = me.id WHERE ( me.a = ? ) 
> GROUP BY c.b, c.x: '1'
> DELETE FROM c WHERE ( ( ( b = ? AND x = ? ) OR ( b = ? AND x = ? ) OR ( 
> b = ? AND x = ? ) ) ): '1', '1001', '1', '1002', '1', '1003'
> COMMIT

See my attached delete.pl for an test case with commented example calls.
Start it with

> $ DBIC_TRACE=1 perl delete.pl

Why does delete() behave differently?

How to efficently delete a 2nd-level relationship resultset?

Kind regards
Daniel
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: delete.out
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20190209/5d3a66e1/attachment.asc>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: delete.pl
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20190209/5d3a66e1/attachment.txt>