Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

PostgreSQL unique constraint violation when editing an entry #12167

Closed
2 tasks done
ytzemih opened this issue Nov 9, 2024 · 6 comments · Fixed by #12373
Closed
2 tasks done

PostgreSQL unique constraint violation when editing an entry #12167

ytzemih opened this issue Nov 9, 2024 · 6 comments · Fixed by #12373
Assignees
@LoayGhreeb
Labels
component: search [outdated] type: bug Confirmed bugs or reports that are very likely to be bugs
Milestone
6.0-beta

Comments

@ytzemih
Copy link

ytzemih commented Nov 9, 2024

JabRef version

Latest development branch build (please note build date below)

Operating system

GNU / Linux

Details on version and operating system

Linux Mint Debian 12

Checked with the latest development build (copy version output from About dialog)

  • I made a backup of my libraries before testing the latest development version.
  • I have tested the latest development version and the problem persists

Steps to reproduce the behaviour

  1. Open bib file and the entry editor
  2. Change some field (it happened to me when editing the "Publisher" field, but could be other fields as well)
  3. At some point you should observe an exception (see below) in the command line
  4. Doesn't seem to have relevant effects ...

Appendix

...

Log File 
2024-11-09 10:22:37 [pool-2-thread-1] org.jabref.logic.search.indexing.BibFieldsIndexer.insertField()
ERROR: Could not add an entry to the index.: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO bib_fields."yks1w6neg84c" ("entryid", "field_name", "field_value_literal", "field_value_transformed")
VALUES (('00229933'), ('publisher'), ('Springer'), ('Springer'))
 was aborted: ERROR: duplicate key value violates unique constraint "yks1w6neg84c_pkey"
  Detail: Key (entryid, field_name)=(00229933, publisher) already exists.  Call getNextException to see other errors in the batch.
	at [email protected]/org.postgresql.jdbc.BatchResultHandler.handleCompletion(Unknown Source)
	at [email protected]/org.postgresql.core.v3.QueryExecutorImpl.execute(Unknown Source)
	at [email protected]/org.postgresql.jdbc.PgStatement.internalExecuteBatch(Unknown Source)
	at [email protected]/org.postgresql.jdbc.PgStatement.executeBatch(Unknown Source)
	at [email protected]/org.postgresql.jdbc.PgPreparedStatement.executeBatch(Unknown Source)
	at [email protected]/org.jabref.logic.search.indexing.BibFieldsIndexer.insertField(Unknown Source)
	at [email protected]/org.jabref.logic.search.indexing.BibFieldsIndexer.updateEntry(Unknown Source)
	at [email protected]/org.jabref.logic.search.IndexManager$8.call(Unknown Source)
	at [email protected]/org.jabref.gui.util.UiTaskExecutor$1.call(Unknown Source)
	at [email protected]/javafx.concurrent.Task$TaskCallable.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "yks1w6neg84c_pkey"
  Detail: Key (entryid, field_name)=(00229933, publisher) already exists.
	at [email protected]/org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(Unknown Source)
	at [email protected]/org.postgresql.core.v3.QueryExecutorImpl.processResults(Unknown Source)
	... 15 more
@ytzemih ytzemih changed the title PostgreSQL unique constraint exception thrown when editing an entry PostgreSQL unique constraint violation when editing an entry Nov 9, 2024
@koppor koppor added this to the 6.0-alpha milestone Nov 9, 2024
@ytzemih
Copy link
Author

ytzemih commented Nov 9, 2024 •
edited
Loading

Btw, the same seems to be true for the "Comments" field.

Also, quite consistently, the postgres server in the background takes 100% CPU for a while, but it stops at some point (... or hmm, perhaps it keeps running).

580847 xxx 25 5 233276 34952 30948 R 99.7 0.2 4:04.63 postgres

@ytzemih
Copy link
Author

ytzemih commented Nov 9, 2024

Closing the corresponding file stops the postgres daemon from hazarding but throws (obviously) an exception

2024-11-09 10:38:14 [pool-2-thread-2] org.jabref.logic.search.indexing.BibFieldsIndexer.removeField()
ERROR: Error deleting field from entry in index: org.postgresql.util.PSQLException: This connection has been closed.
	at [email protected]/org.postgresql.jdbc.PgConnection.checkClosed(Unknown Source)
	at [email protected]/org.postgresql.jdbc.PgConnection.createStatement(Unknown Source)
	at [email protected]/org.postgresql.jdbc.PgConnection.createStatement(Unknown Source)
	at [email protected]/org.jabref.logic.search.indexing.BibFieldsIndexer.removeField(Unknown Source)
	at [email protected]/org.jabref.logic.search.indexing.BibFieldsIndexer.updateEntry(Unknown Source)
	at [email protected]/org.jabref.logic.search.IndexManager$8.call(Unknown Source)
	at [email protected]/org.jabref.gui.util.UiTaskExecutor$1.call(Unknown Source)
	at [email protected]/javafx.concurrent.Task$TaskCallable.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)

@subhramit subhramit added the [outdated] type: bug Confirmed bugs or reports that are very likely to be bugs label Dec 1, 2024
@Siedlerchr
Copy link
Member

I can confirm this when editing an entry and inserting somethingin a comment:



Could not add an entry to the index.
java.sql.BatchUpdateException: Batch entry 2 INSERT INTO bib_fields."l1qwtdactnpi" ("entryid", "field_name", "field_value_literal", "field_value_transformed")
VALUES (('00006342'), ('comment-christophs'), ('asdfasdf'), ('asdfasdf'))
 was aborted: ERROR: duplicate key value violates unique constraint "l1qwtdactnpi_pkey"
  Detail: Key (entryid, field_name)=(00006342, comment-christophs) already exists.  Call getNextException to see other errors in the batch.
	at [email protected]/org.postgresql.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:186)
	at [email protected]/org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:591)
	at [email protected]/org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:888)
	at [email protected]/org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:912)
	at [email protected]/org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1739)
	at [email protected]/org.jabref.logic.search.indexing.BibFieldsIndexer.addToIndex(BibFieldsIndexer.java:246)
	at [email protected]/org.jabref.logic.search.indexing.BibFieldsIndexer.addToIndex(BibFieldsIndexer.java:178)
	at [email protected]/org.jabref.logic.search.indexing.BibFieldsIndexer.updateOnStart(BibFieldsIndexer.java:162)
	at [email protected]/org.jabref.logic.search.IndexManager$2.call(IndexManager.java:92)
	at [email protected]/org.jabref.gui.util.UiTaskExecutor$1.call(UiTaskExecutor.java:191)
	at [email protected]/javafx.concurrent.Task$TaskCallable.call(Task.java:1401)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
	at java.base/java.lang.Thread.run(Thread.java:1575)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "l1qwtdactnpi_pkey"
  Detail: Key (entryid, field_name)=(00006342, comment-christophs) already exists.
	at [email protected]/org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
	at [email protected]/org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
	at [email protected]/org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:580)
	... 15 more

@ytzemih
Copy link
Author

ytzemih commented Dec 15, 2024

@Siedlerchr Thanks for confirming. Also, in the meanwhile, I am wondering whether this issue is a side effect of #12190?

@Siedlerchr
Copy link
Member

From this discussion it seems that this happens when the sequence is out of sync https://stackoverflow.com/questions/4448340/postgresql-duplicate-key-violates-unique-constraint

@github-project-automation github-project-automation bot moved this to Normal priority in Prioritization Dec 16, 2024
@Siedlerchr Siedlerchr moved this from Normal priority to High priority in Prioritization Dec 16, 2024
@koppor koppor modified the milestones: 6.0-alpha, 6.0-beta Dec 21, 2024
@LoayGhreeb LoayGhreeb self-assigned this Jan 9, 2025
@LoayGhreeb LoayGhreeb mentioned this issue Jan 9, 2025
Merged
7 tasks
@ytzemih
Copy link
Author

ytzemih commented Jan 16, 2025

Ok, this issue seems to be fixed for me with the @LoayGhreeb 's PR.

@github-project-automation github-project-automation bot moved this from High priority to Done in Prioritization Jan 17, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@LoayGhreeb LoayGhreeb

Labels
component: search [outdated] type: bug Confirmed bugs or reports that are very likely to be bugs
Projects
Prioritization
Archived in project
Milestone
6.0-beta
Development

Successfully merging a pull request may close this issue.

Fix postgres unique constraint exception JabRef/jabref
5 participants
@Siedlerchr @koppor @ytzemih @LoayGhreeb @subhramit