[PDB-176] insert or update on table "certname_catalogs" violates foreign key constraint "certname_catalogs_catalog_fkey" Created: 2013/12/04  Updated: 2014/11/17  Resolved: 2013/12/18

Status: Closed
Project: PuppetDB
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Normal
Reporter: redmine.exporter Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: redmine
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Relates
relates to PDB-1031 ERROR: update or delete on table "fac... Closed
Template:

 Description   

The user 'ak0ska' has been seeing the following errors in his postgresql logs:

<pre>
[2013-03-12 05:51:19 CET]ERROR: insert or update on table "certname_catalogs" violates foreign key constraint "certname_catalogs_catalog_fkey"
[2013-03-12 05:51:19 CET]DETAIL: Key (catalog)=(c93825c7157cba8c10641b54d9dce17eb0ce618b) is not present in table "catalogs".
[2013-03-12 05:51:19 CET]STATEMENT: INSERT INTO certname_catalogs (certname,catalog,timestamp) VALUES ($1,$2,$3) RETURNING *
[2013-03-12 05:51:19 CET]ERROR: insert or update on table "certname_catalogs" violates foreign key constraint "certname_catalogs_catalog_fkey"
[2013-03-12 05:51:19 CET]DETAIL: Key (catalog)=(dd1ad1b27c063beab9beaded80f449a050fe11a6) is not present in table "catalogs".
[2013-03-12 05:51:19 CET]STATEMENT: INSERT INTO certname_catalogs (certname,catalog,timestamp) VALUES ($1,$2,$3) RETURNING *
</pre>

Although up until now we can't reproduce it directly with PuppetDB yet, we do believe the following SQL if for some reason was able to be reproduced in production would recreate this scenario:

<pre>
The test:

  1. create the precondition, while it is difficult to catch it is the only pre-condition yet that reproduces this issue.
    insert into catalogs values ('foo', 1, 1111);
  1. on psql shell 1 replicate a database gc:
    begin;
    DELETE FROM catalogs WHERE NOT EXISTS (SELECT * FROM certname_catalogs cc WHERE cc.catalog=catalogs.hash);
  2. While this query below is part of the full problem, it is not the thing creating the lock ... if ran in isolation this problem isn't reproduced. Also - if it is ommitted - the problem still happens.
    DELETE FROM resource_params WHERE NOT EXISTS (SELECT * FROM catalog_resources cr WHERE cr.resource=resource_params.resource);
  1. then on psql shell 2 replicate the update of a catalog to new revision (cut and paste it in):
    begin;
    SELECT 1 FROM certnames WHERE name='puppetdb1.vm' LIMIT 1;
    UPDATE certnames SET deactivated=NULL WHERE name='puppetdb1.vm' AND (deactivated<'2013-03-13 12:59:53.762' OR deactivated IS NULL);
    SELECT timestamp FROM certname_catalogs WHERE certname='puppetdb1.vm' ORDER BY timestamp DESC LIMIT 1;
    SELECT 1 FROM catalogs WHERE hash='foo';
    UPDATE catalogs SET api_version=1, catalog_version='1363208780' WHERE hash='foo';
  2. it will block here ... so just skip ahead to the psql1 part
    DELETE FROM certname_catalogs WHERE certname='puppetdb1.vm';
    INSERT INTO certname_catalogs (certname,catalog) VALUES ('puppetdb1.vm','foo');
    commit;
  1. on psql shell 1 finish the original delete transaction
    commit;

should get error "ERROR: insert or update on table "certname_catalogs" violates foreign key constraint "certname_catalogs_catalog_fkey""
</pre>

So the net result after the constraint error is that the replace catalog request goes back into the queue, tries again and we see no data loss. The concern is that those requests block until the GC is complete, so ideally we should try to minimize this.

The original google groups discussion is here: <https://groups.google.com/forum/?fromgroups=#!topic/puppet-users/yRMI1J_y6Ps>



 Comments   
Comment by Deepak Giridharagopal [ 2013/12/18 ]

This is handled by PDB-69

Generated at Wed Jul 17 05:35:30 PDT 2019 using JIRA 7.7.1#77002-sha1:e75ca93d5574d9409c0630b81c894d9065296414.