[PDB-1031] ERROR: update or delete on table "fact_paths" violates foreign key constraint "fact_values_path_id_fk" on table "fact_values" Created: 2014/11/17  Updated: 2015/02/13  Resolved: 2015/02/10

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

Type: Bug Priority: Normal
Reporter: Erik Lattimore Assignee: Unassigned
Resolution: Duplicate Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
relates to PDB-337 _timestamp fact Closed
relates to PDB-1205 facts gc is too slow Closed
relates to PDB-1224 Move fact path reference from the fac... Closed
relates to PDB-1225 Update fact storage and querying to u... Closed
relates to PDB-1226 Create GC code for disassociated fact... Closed
relates to PDB-1227 Test new table structure and fact pat... Closed
relates to PDB-176 insert or update on table "certname_c... Closed
Epic Link: PDB Support TIckets
Story Points: 5
Sprint: PuppetDB 2014-12-17, PuppetDB 2014-12-31, PuppetDB 2015-02-11


Hi guys, ever since an upgrade to puppet 3.7 (and a corresponding upgrade of puppetdb at the same time) we have been getting postgres errors. We seem to see the following error on the puppetdb server after every invocation:

Nov 17 11:01:52 postgres[6379]: [1859-1] 2014-11-17 11:01:52 EST ERROR:  update or delete on table "fact_paths" violates foreign key constraint "fact_values_path_id_fk" on table "fact_values"
Nov 17 11:01:52 postgres[6379]: [1859-2] 2014-11-17 11:01:52 EST DETAIL:  Key (id)=(78) is still referenced from table "fact_values".
Nov 17 11:01:52 postgres[6379]: [1859-3] 2014-11-17 11:01:52 EST STATEMENT:  COMMIT

It's always the same key ID (78) that it complains about. We are running on postgres 9.1.

From the fact_paths table, the entry with ID 78 is:

 id | value_type_id | depth |    name    |    path    
 78 |             0 |     0 | _timestamp | _timestamp

I didn't think _timestamp was supposed to make it into puppetdb? This could be a relic from the upgrade though.

In the fact_values table, there are 3 entries which are referencing the _timestamp fact:

  id    | path_id | value_type_id |                value_hash                | value_integer | value_float |          value_string          | value_boolean | value_json 
 2134865 |      78 |             0 | 226b3439f249f4fccfc7637e853c2b4a5e967160 |               |             | Tue Oct 14 13:48:04 -0400 2014 |               | 
 2229630 |      78 |             0 | 14f01c68a6a7621cdcbc1bce80fb7e791b10a7df |               |             | Thu Oct 16 13:59:42 -0400 2014 |               | 
 2172152 |      78 |             0 | a4c93348ed4c2bb4fa6399512221512bf111f04b |               |             | Wed Oct 15 08:38:29 -0400 2014 |               | 

Comment by Ken Barber [ 2014/11/26 ]

Paul Seymour has also seen this here: https://groups.google.com/d/msgid/puppet-users/abee638f-5e75-410a-8fe2-7ff5acc8b7b2%40googlegroups.com?utm_medium=email&utm_source=footer

Comment by Erik Lattimore [ 2014/12/19 ]

I simply deleted the foreign keys to the _timestamp value that puppet was trying to delete. This allowed puppet to then delete the _timestamp. I feel like if puppet really wants to delete the _timestamp, then it should have just deleted the child references for me and never produced the postgres error. However, I may just have a simplified view of the world. Either way I have no more information as the rows have long since been deleted and it is no longer generating errors so I am happy to close this issue.

Comment by Wyatt Alt [ 2014/12/19 ]

Erik Lattimore Hey, I actually just started looking at this ticket. Like Ken mentioned above we had another user report essentially the same issue on the mailing list a few weeks ago, but with a different fact. Sounds like you did the same thing, but for posterity I ended up having him run these two queries to clear the issue:

delete from fact_values where id not in (select distinct fact_value_id from facts);
delete from fact_paths where id not in (select path_id from fact_values);

We used those queries in the 2.2.0 release to garbage collect facts, but switched to garbage collection on fact updates for performance reasons. I think the issues you and Paul had indicate a bug, but I haven't been able to replicate it yet.

I think it's just a strange coincidence that _timestamp is causing the issue. _timestamp would be present immediately after a migration from PDB 1.6.3, but would be deleted on the first fact update after the upgrade. It just happens that the update failed to run because the upgrade got your db into a bad state.

In any case, thanks for the update and no need to close. Seems like a bug.

Comment by Erik Lattimore [ 2014/12/19 ]

Ok. We did have an odd install for a while. We were pinned to puppet 2.7 for quite a while as we converted our codebase to support puppet 3.x. During that time I believe we had a newer version of puppetdb installed but an older version of the terminus (or vice versa, I forget exactly) due to the dependencies and the nature of our apt:pin. This odd configuration might have been how we got into a state that you didn't expect. I also know that _timestamp had a few bugs relating to it in puppetdb (PDB-337) so it might be hard to determine how we got into this bad state.

Comment by Ryan Senior [ 2015/02/10 ]

Thanks for reporting this and giving us so much good information. This problem is a concurrency issue. If there are two factsets that are submitted, where the nodes share a fact value and now are both changing their fact value, it can leave a fact value that is disassociated from any other node. This value sits around until there's an attempt to delete a fact path that it's associated with and then fails.

There are several tickets that I have created to fix this: PDB-1224, PDB-1225, PDB-1226 and PDB-1227. These tickets will be a the top of our priority list in the next sprint.

Generated at Thu Jan 21 20:07:40 PST 2021 using Jira 8.5.2#805002-sha1:a66f9354b9e12ac788984e5d84669c903a370049.