DB Error after upgrading to OJS 3.2.1.1

Software Support
,
1

Hi,

I’ve upgraded to OJS version 3.2.1.1 and now I’m getting those errors on PostgreSQL logs:

2020-07-08 13:12:53 -03 [25813]: [502-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [503-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionDiscipline, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [504-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)
VALUES
( $1, $2, $3)
2020-07-08 13:12:53 -03 [25813]: [505-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [506-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionLanguage, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [507-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)
VALUES
( $1, $2, $3)
2020-07-08 13:12:53 -03 [25813]: [508-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [509-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionAgency, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [510-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)
VALUES
( $1, $2, $3)
2020-07-08 13:12:53 -03 [25813]: [511-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [512-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionKeyword, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [513-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)
VALUES
( $1, $2, $3)
2020-07-08 13:12:53 -03 [25813]: [514-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [515-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionSubject, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [516-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)
VALUES
( $1, $2, $3)
2020-07-08 13:12:53 -03 [25813]: [517-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [518-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionDiscipline, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [519-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)
VALUES
( $1, $2, $3)
2020-07-08 13:12:53 -03 [25813]: [520-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [521-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionLanguage, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [522-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)
VALUES
( $1, $2, $3)
2020-07-08 13:12:53 -03 [25813]: [523-1] user=revistas,db=revistas ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
2020-07-08 13:12:53 -03 [25813]: [524-1] user=revistas,db=revistas DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionAgency, 1048588, 130353) already exists.
2020-07-08 13:12:53 -03 [25813]: [525-1] user=revistas,db=revistas STATEMENT: INSERT INTO controlled_vocabs
(symbolic, assoc_type, assoc_id)

A lot of these errors.

Regards,
Tarcisio Pereira

2

Hi @Tarcisio_Pereira,

OJS intentionally uses index collisions to determine whether an entry already exists for a controlled vocabulary – i.e. this is expected. Is there a way to configure PostgreSQL not to log these?

Regards,
Alec Smecher
Public Knowledge Project Team

3

Hi @asmecher

Maybe something like this PostgreSQL: Documentation: 9.4: Control Structures
It’s like a try catch on database.
I had never seen this applied to the database.

Regards,
Tarcisio Pereira

4

Dear Tarcisio / Dear @asmecher,

Did you solve this problem?
I am receiving this error, which is causing the Ojs-3.2.1.1 internal search to fail, there is an error to insert some entries, for example “keywords”, also other entries using the “Quick Submissions Plug-in”.
Can you share the resolution?
Tks
Augusto Torres

===
postgres[67332]: [9-2] DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionSubject, 1048588, 1138) already exists.
postgres[67332]: [9-3] STATEMENT: INSERT INTO controlled_vocabs
postgres[67332]: [9-4] (symbolic, assoc_type, assoc_id)
postgres[67332]: [9-5] VALUES
postgres[67332]: [9-6] ( $1, $2, $3)
postgres[67332]: [10-1] ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”

postgres[67332]: [10-2] DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionDiscipline, 1048588, 1138) already exists.
postgres[67332]: [10-3] STATEMENT: INSERT INTO controlled_vocabs
postgres[67332]: [10-4] (symbolic, assoc_type, assoc_id)
postgres[67332]: [10-5] VALUES
postgres[67332]: [10-6] ( $1, $2, $3)
postgres[67332]: [11-1] ERROR: duplicate key value violates unique constraint “controlled_vocab_symbolic”
postgres[67332]: [11-2] DETAIL: Key (symbolic, assoc_type, assoc_id)=(submissionLanguage, 1048588, 1138) already exists.

5

Hi @torres.

No. Its was a test installation. I did not upgraded yet.

6

Thanks Tarcisio,
I believe that the Alec Smecher @asmecher can guide us on how to resolve this issue. I have been using this version working reasonably well since July, but some interventions (like this one) appear and interventions are needed.
[ ]'s
Augusto Torres

7

Hi all,

You can avoid those error/warning messages by applying this change: Avoid index collisions if possible · pkp/pkp-lib@b193290 · GitHub

This will be released with OJS/OMP/OPS 3.2.1-2.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like