title: Django + PostgreSQL trigger causes "violates foreign key constraint"
body: <p>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. </p>
  
  <p>A bit like the following (simplified for this question):</p>
  
  <pre><code>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 --&gt; related name from 'ProductPricing'
  </code></pre>
  
  <p>To keep the history of the prices, I have an <code>OldProductPricing</code> model, which is pretty much a copy of <code>ProductPricing</code> but allows multiple <code>OldProductPricing(s)</code> per product:</p>
  
  <pre><code>class OldProductPricing(models.Model):
      valid_until = models.DateTimeField(null=False)
      product = models.ForeignKey('app.Product', related_name='+',
                                  null=False, on_delete=models.CASCADE)
  </code></pre>
  
  <p>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 <code>OldProductPricing</code> table. </p>
  
  <p>In order to do that, I have created a Posgresql trigger on update or delete:</p>
  
  <pre><code>CREATE TRIGGER price_change_trigger AFTER UPDATE OR DELETE ON app.productpricing
  FOR EACH ROW EXECUTE PROCEDURE price_change_trigger_func();
  </code></pre>
  
  <p>The part of the trigger function that creates the insertion is as follows:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>For the most part, it works fine, but when a <code>Product</code> is deleted, and the <code>CASCADES</code> start happening (from Product --to--> ProductPricing), the <code>EXISTS(SELECT 1 FROM app_product WHERE app_product.id=old_to_insert.product_id))</code> doesn't seem to be effective: Even-though I check that the <code>Product</code> exists, I get:</p>
  
  <pre><code>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".
  </code></pre>
  
  <p>What I <em>think</em> it's happening is that the trigger is run in a transaction where Product --> ProductPricing are to be deleted, and the <code>EXISTS</code> check happens before <em>actually</em> removing the <code>Product</code>?? (I'm not really sure, though... this is just a suspicion) so a new instance of <code>OldProductPricing</code> is created, but after that the <code>Product</code> this <code>OldProductPricing</code> row references to is removed, leaving the database in an inconsistent state.</p>
  
  <p>Is my theory correct? and most importantly... Is there any way to fix this?</p>
  
  <p>Thank you in advance. Any hint or suggestion will be appreciated.</p>
  
  <p>PS: I'm using Django 1.11 and Postgresql 9.5.</p>