launchpad-dev team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

On Fri, Apr 29, 2011 at 11:16 PM, Francis J. Lacoste
<francis.lacoste@xxxxxxxxxxxxx> wrote:
> Hi Stuart,
>
> The staging ugprade started failing on Friday with patch 2208-65-0. It seems
> Slonik doesn't like it. We retried it several time, and we remove the lines of
> hyphens that I wildly guess could confuse Slonik (who had issues with comments
> in the past, but to no avail.)
>
> Original failure: https://pastebin.canonical.com/46931/
>
> After removing the comments and trying again:
> https://pastebin.canonical.com/46948/
>
> Unless this can be fixed and QA before Tuesday, it's likely that this revision
> won't be part of the next DB deployment. We would merge db-stable 10480.
>
> Let me know if you have any idea what was going on.

The statement is unable to remove some rows because they are still
referenced by BugSubscriptionFilter.

launchpad_prod_3=# begin;
BEGIN
Time: 811.285 ms
launchpad_prod_3=# DELETE FROM StructuralSubscription WHERE
launchpad_prod_3-#     StructuralSubscription.id IN
launchpad_prod_3-#         (WITH duped_values AS
launchpad_prod_3(#             (SELECT Subscription.product,
launchpad_prod_3(#                     Subscription.subscriber,
launchpad_prod_3(#                     Subscription.id
launchpad_prod_3(#              FROM StructuralSubscription AS Subscription
launchpad_prod_3(#              WHERE EXISTS (
launchpad_prod_3(#                 SELECT StructuralSubscription.product,
launchpad_prod_3(#                        StructuralSubscription.subscriber
launchpad_prod_3(#                 FROM StructuralSubscription
launchpad_prod_3(#                 WHERE
launchpad_prod_3(#                     StructuralSubscription.product
= Subscription.product
launchpad_prod_3(#                     AND
StructuralSubscription.subscriber = Subscription.subscriber
launchpad_prod_3(#                 GROUP BY StructuralSubscription.product,
launchpad_prod_3(#                          StructuralSubscription.subscriber
launchpad_prod_3(#                 HAVING Count(*)>1))
launchpad_prod_3(#          SELECT duped_values.id
launchpad_prod_3(#          FROM duped_values
launchpad_prod_3(#          WHERE duped_values.id NOT IN
launchpad_prod_3(#             (SELECT MAX(duped_values.id)
launchpad_prod_3(#              FROM duped_values
launchpad_prod_3(#              GROUP BY duped_values.product,
duped_values.subscriber));
ERROR:  update or delete on table "structuralsubscription" violates
foreign key constraint
"bugsubscriptionfilter_structuralsubscription_fkey" on table
"bugsubscriptionfilter"
DETAIL:  Key (id)=(7570) is still referenced from table "bugsubscriptionfilter".


We need to decide what to do with these rows. Should they be removed?
Should we set BugSubscriptionFilter.structuralsubscription to NULL?
Are we removing the wrong StructuralSubscription rows?

If they should be removed, it might be sane (and the simplest fix) to
make the BugSubscriptionFilter.structuralsubscription foreign key ON
DELETE CASCADE




-- 
Stuart Bishop <stuart@xxxxxxxxxxxxxxxx>
http://www.stuartbishop.net/