Uploaded image for project: 'PuppetDB'
  1. PuppetDB
  2. PDB-176

insert or update on table "certname_catalogs" violates foreign key constraint "certname_catalogs_catalog_fkey"

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Normal
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • 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>

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                redmine.exporter redmine.exporter
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Zendesk Support