Django + PostgreSQL trigger causes "violates foreign key constraint"

Question

In my Django... schema? I have a model structure for Products, their prices and a historic (kind of a log) of the prices the products went through.

A bit like the following (simplified for this question):

class ProductPricing(models.Model):
    price = models.DecimalField(max_digits=8, decimal_places=2,
                                help_text='price in dollars')
    product = models.OneToOneField('app.Product', related_name='pricing',
                                    null=False, on_delete=models.CASCADE)

class Product(models.Model):
    name = models.CharField(max_length=100)
    # .pricing --> related name from 'ProductPricing'

To keep the history of the prices, I have an OldProductPricing model, which is pretty much a copy of ProductPricing but allows multiple OldProductPricing(s) per product:

class OldProductPricing(models.Model):
    valid_until = models.DateTimeField(null=False)
    product = models.ForeignKey('app.Product', related_name='+',
                                null=False, on_delete=models.CASCADE)

To make sure that each and every time that a product's price is modified or deleted, I try to create an entry in my OldProductPricing table.

In order to do that, I have created a Posgresql trigger on update or delete:

CREATE TRIGGER price_change_trigger AFTER UPDATE OR DELETE ON app.productpricing
FOR EACH ROW EXECUTE PROCEDURE price_change_trigger_func();

The part of the trigger function that creates the insertion is as follows:

CREATE OR REPLACE FUNCTION price_change_trigger_func() RETURNS TRIGGER LANGUAGE plpgsql AS $$
    DECLARE
       retval RECORD;
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            retval := OLD;
        ELSE
            retval := NEW;
        END IF;

        RAISE LOG 'Inserting in old_prices table copy of Pricing record ID=%% for product_id=%% because of %%', OLD.id, OLD.product_id, TG_OP;
        old_to_insert.product_id := OLD.product_id;
        old_to_insert.price := OLD.price;
        old_to_insert.valid_until := now() at time zone 'utc';
        old_to_insert.id := nextval('app_oldproductpricing_id_seq');

        IF EXISTS(SELECT 1 FROM app_product WHERE app_product.id=old_to_insert.product_id)) THEN
            INSERT INTO app_oldproductpricing VALUES(old_to_insert.*);
        END IF;
        RETURN retval;
    EXCEPTION WHEN others THEN
        -- Do nothing...
        RAISE WARNING 'Got error on %% %% %%', TG_OP, SQLERRM, SQLSTATE;
        RETURN NULL;
    END;

For the most part, it works fine, but when a Product is deleted, and the CASCADES start happening (from Product --to--> ProductPricing), the EXISTS(SELECT 1 FROM app_product WHERE app_product.id=old_to_insert.product_id)) doesn't seem to be effective: Even-though I check that the Product exists, I get:

insert or update on table "app_oldproductpricing" violates foreign key constraint "app_oldproductpricing_product_id_bb078367_fk_app_product_id"
DETAIL:  Key (product_id)=(5) is not present in table "app_product".

What I think it's happening is that the trigger is run in a transaction where Product --> ProductPricing are to be deleted, and the EXISTS check happens before actually removing the Product?? (I'm not really sure, though... this is just a suspicion) so a new instance of OldProductPricing is created, but after that the Product this OldProductPricing row references to is removed, leaving the database in an inconsistent state.

Is my theory correct? and most importantly... Is there any way to fix this?

Thank you in advance. Any hint or suggestion will be appreciated.

PS: I'm using Django 1.11 and Postgresql 9.5.

Answer 1

This problem is by a "mutating table", when DJANGO emulate the ON_CASCADE, first delete the row on table "ProductPricing" and the insert on "OldProductPricing" is over a row in "Product" which will disappear. You have other options:

1. Delete the trigger and manage the logic in the Product.save() and ProductPricing.save(), and change on_delete=CASCADE by on_delete=DO_NOTHING.

or

2. A log table ("OldProductPricing") should keep the data for ever, and show all the changes in the history from "Product", thus you should delete the foreign key from "OldProductPricing" to "Product.