Jira (PDB-1546) 'replace facts' failing due to foreign key constraint issue

[Zack Smith (JIRA)]

Title: Message Title

Simon Oxwell created an issue   PuppetDB / PDB-1546 'replace facts' failing due to foreign key constraint issue Issue Type: Bug Affects Versions: PDB 2.3.3 Assignee: Unassigned Attachments: Screen Shot 2015-04-30 at 4.53.45 PM.png, Screen Shot 2015-04-30 at 4.54.03 PM.png, Screen Shot 2015-04-30 at 4.58.05 PM.png Created: 2015/05/26 4:40 AM Environment: CentOS 7, Postgres 9.2.7 Fix Versions: PDB 2.3.4 Priority: Normal Reporter: Simon Oxwell Hi, We're seeing an issue with our puppetdb since it was upgraded to 2.3.1, with some nodes not updating. We get errors like the following the the puppetdb.log 015-04-23 14:45:10,223 ERROR [c.p.p.command] [b7ba3fa5-c61b-4461-a2b5-4317d00a92c8] [replace facts] Retrying after attempt 4, due to: java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause. java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746) ~[puppetdb.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887) ~[puppetdb.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405) ~[puppetdb.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893) ~[puppetdb.jar:na] at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469) ~[puppetdb.jar:na] at clojure.java.jdbc.internal$do_prepared_STAR_$fn__7259.invoke(internal.clj:356) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) ~[na:na] at clojure.java.jdbc.internal$do_prepared_STAR_.doInvoke(internal.clj:356) ~[na:na] at clojure.lang.RestFn.applyTo(RestFn.java:139) ~[puppetdb.jar:na] at clojure.core$apply.invoke(core.clj:626) ~[puppetdb.jar:na] at clojure.java.jdbc$do_prepared.doInvoke(jdbc.clj:206) ~[na:na] at clojure.lang.RestFn.invoke(RestFn.java:423) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.scf.storage$eval10818$delete_pending_value_id_orphans_BANG___10819$fn__10820.invoke(storage.clj:797) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10818$delete_pending_value_id_orphans_BANG___10819.invoke(storage.clj:777) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996$fn__11000$fn__11002.invoke(storage.clj:993) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996$fn__11000.invoke(storage.clj:956) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996.invoke(storage.clj:949) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198$fn__11202$fn__11204.invoke(storage.clj:1212) ~[na:na] at com.puppetlabs.puppetdb.scf.storage.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198$fn__11202.invoke(storage.clj:1209) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198.invoke(storage.clj:1202) ~[na:na] at com.puppetlabs.puppetdb.command$eval13392$fn__13395$fn__13408.invoke(command.clj:381) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670$fn__7671$fn__7672.invoke(jdbc.clj:290) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:204) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670$fn__7671.invoke(jdbc.clj:290) ~[na:na] at clojure.java.jdbc.internal$with_connection_STAR_.invoke(internal.clj:186) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670.invoke(jdbc.clj:287) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649$fn__7650$fn__7651.invoke(jdbc.clj:259) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649$fn__7650.invoke(jdbc.clj:258) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649.invoke(jdbc.clj:250) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn.invoke(jdbc.clj:286) ~[na:na] at com.puppetlabs.puppetdb.command$eval13392$fn__13395.invoke(command.clj:379) ~[na:na] at clojure.lang.MultiFn.invoke(MultiFn.java:231) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$produce_message_handler$fn__13564.invoke(command.clj:647) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_discard$fn__13513$fn__13517.invoke(command.clj:554) ~[na:na] at com.puppetlabs.puppetdb.command.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$wrap_with_discard$fn__13513.invoke(command.clj:553) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_exception_handling$fn__13498$fn__13499.invoke(command.clj:507) ~[na:na] at com.puppetlabs.puppetdb.command.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$wrap_with_exception_handling$fn__13498.invoke(command.clj:506) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_command_parser$fn__13508.invoke(command.clj:529) [na:na] at com.puppetlabs.puppetdb.command$wrap_with_meter$fn__13488.invoke(command.clj:467) [na:na] at com.puppetlabs.puppetdb.command$wrap_with_thread_name$fn__13522.invoke(command.clj:569) [na:na] at com.puppetlabs.mq$create_message_listener$reify__11783.onMessage(mq.clj:270) [na:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:560) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:498) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947) [puppetdb.jar:na] at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75] and the corresponding error in the postgres.log: RROR: update or delete on table "fact_values" violates foreign key constraint "fact_value_id_fk" on table "facts" DETAIL: Key (id)=(1524348) is still referenced from table "facts". STATEMENT: DELETE FROM fact_values fv WHERE fv.id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in ($76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,$133,$134,$135,$136,$137,$138,$139,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in (($151,$152),($153,$154),($155,$156),($157,$158),($159,$160),($161,$162),($163,$164),($165,$166),($167,$168),($169,$170),($171,$172),($173,$174),($175,$176),($177,$178),($179,$180),($181,$182),($183,$184),($185,$186),($187,$188),($189,$190),($191,$192),($193,$194),($195,$196),($197,$198),($199,$200),($201,$202),($203,$204),($205,$206),($207,$208),($209,$210),($211,$212),($213,$214),($215,$216),($217,$218),($219,$220),($221,$222),($223,$224),($225,$226),($227,$228),($229,$230),($231,$232),($233,$234),($235,$236),($237,$238),($239,$240),($241,$242),($243,$244),($245,$246),($247,$248),($249,$250),($251,$252),($253,$254),($255,$256),($257,$258),($259,$260),($261,$262),($263,$264),($265,$266),($267,$268),($269,$270),($271,$272),($273,$274),($275,$276),($277,$278),($279,$280),($281,$282),($283,$284),($285,$286),($287,$288),($289,$290),($291,$292),($293,$294),($295,$296),($297,$298),($299,$300))) I've been able to get things moving along by performing delete from facts where fact_value_id in ( <list of numbers from failing fact_values delete>) and I've updated to 2.3.3, but new 'factsets' keep developing the same error. We're on CentOS 7 and postgres 9.2.7. We have about 110 puppet agents. Add Comment   This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.