(OJS 3.2.0-3) DB Error: duplicate key value violates unique constraint "publication_galleys_pkey"

Software Support
1

Hi,
I can’t submit a file on Publication → Galleys.

This error appears in the log:

PHP Fatal error:  Uncaught Exception: DB Error: ERROR:  duplicate key value violates unique constraint "publication_galleys_pkey"\nDETALHE:  Key (galley_id)=(657) already exists. Query: INSERT INTO publication_galleys (locale, label, publication_id, url_path, remote_url) VALUES (?, ?, ?, ?, ?) in /var/www/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php:703\nStack trace:\n#0 /var/www/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_postgres7), 'INSERT INTO pub...')\n#1 /var/www/ojs-3.2.0-3/lib/pkp/classes/db/SchemaDAO.inc.php(79): DAO->update('INSERT INTO pub...', Array)\n#2 /var/www/ojs-3.2.0-3/controllers/grid/articleGalleys/form/ArticleGalleyForm.inc.php(165): SchemaDAO->insertObject(Object(ArticleGalley))\n#3 /var/www/ojs-3.2.0-3/controllers/grid/articleGalleys/ArticleGalleyGridHandler.inc.php(405): ArticleGalleyForm->execute()\n#4 /var/www/ojs-3.2.0-3/lib/pkp/classes/core/PKPRouter.inc.php(391): ArticleGalleyGridHandler->updateGalley(Array, Object(Request))\n#5 /var/www/ojs-3.2.0-3/lib/pkp/classes/core/PKP in /var/www/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php on line 703, referer: http://periodicos.ufes.br/rbps/workflow/index/21469/5

could you help me?

2

Hi @Adriano_Jose,

What indexes do you have on the publication_galleys_pkey table?

Regards,
Alec Smecher
Public Knowledge Project Team

3

Hi asmecher
I have the same problem.

The table “publication_galleys” has this structure:
Table “public.publication_galleys”
Column | Type | Modifiers
----------------±------------------------±------------------------------------------------------------------------
galley_id | integer | not null default nextval(‘publication_galleys_galley_id_seq’::regclass)
locale | character varying(14) |
publication_id | bigint | not null
label | character varying(255) |
file_id | bigint |
seq | double precision | not null default 0
remote_url | character varying(2047) |
is_approved | smallint | not null default 0
url_path | character varying(64) |
Indexes:
“publication_galleys_pkey” PRIMARY KEY, btree (galley_id)
“publication_galleys_publication_id” btree (publication_id)
“publication_galleys_url_path” btree (url_path)

and this is the specification of the index “publication_galleys_pkey”:
Index “public.publication_galleys_pkey”
Column | Type | Definition
-----------±--------±-----------
galley_id | integer | galley_id
primary key, btree, for table “public.publication_galleys”

Any help will be appreciated.
Regards,
Gabriel Lence

1 Like
4

Hi @gabriellence

Are it already solved? Same error here.

Regards,
Tarcisio Pereira;

5

Hi Tarcisio

No.
error log from postgresql 9.6 shows:

INSERT INTO publication_galleys (locale, label, publication_id, url_path, remote_url) VALUES ( $1, $2, $3, $4, $5)
DETAIL: parameters: $1 = ‘es_ES’, $2 = ‘PDF’, $3 = ‘247’, $4 = ‘’, $5 = ‘’
ERROR: duplicate key value violates unique constraint “publication_galleys_pkey”
DETAIL: Key (galley_id)=(22) already exists.

and doing:
SELECT max(galley_id) FROM publication_galleys;
returns:
max

190
…
so, I think this is needing an update on serial because:
SELECT * FROM publication_galleys_galley_id_seq ;
returns:
sequence_name | publication_galleys_galley_id_seq
last_value | 22
start_value | 1
increment_by | 1
max_value | 9223372036854775807
min_value | 1
cache_value | 1
log_cnt | 32
is_cycled | f
is_called | t

If somebody tells me it’s okey to do:
ALTER SEQUENCE publication_galleys_galley_id_seq RESTART WITH 190;
I go with it.

Thanks for asking. Any help is appreciated. Regards

1 Like
6

Hi @gabriellence

I’m in a testing environment.
I run this query:

ALTER SEQUENCE publication_galleys_galley_id_seq RESTART WITH MY_MAX_ID;

I’m able to create galleys after that.
But I don’t know if it’s ok to run this query.
Maybe @asmecher can answer us.
If it’s ok, I will search for more broken sequences.

Regards,
Tarcisio Pereira

7

thanks Tarcisio.

I saw more duplicate keys in postgresql log in other tables.
I’m in production so I prefer to wait for a second opinion updating
the sequence.

Regards,
Gabriel

8

Hi all,

Resetting the sequence is the correct solution! Note that we’ve rewritten our upgrade script toolset, so starting with 3.3, upgrades should be much smoother (including for you PostgreSQL users).

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like