launchpad-dev team mailing list archive

[Gary Poster <gary.poster@xxxxxxxxxxxxx>]

On Apr 30, 2011, at 4:36 AM, Stuart Bishop wrote:

> 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

I believe that would be appropriate.  If someone (Stuart? Yellow squad?) could make this change, I would appreciate it.  We had our baby Saturday, so I will be out this week.

Thank you

Gary