Your users table seems to be missing a unique index on the username column. What do you get for \d+ users in the PostgreSQL client? It should look like…
ojs-stable-3_3_0=> \d+ users;
Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------------+--------------------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
user_id | bigint | | not null | nextval('users_user_id_seq'::regclass) | plain | |
username | character varying(32) | | not null | | extended | |
password | character varying(255) | | not null | | extended | |
email | character varying(255) | | not null | | extended | |
url | character varying(2047) | | | | extended | |
phone | character varying(32) | | | | extended | |
mailing_address | character varying(255) | | | | extended | |
billing_address | character varying(255) | | | | extended | |
country | character varying(90) | | | | extended | |
locales | character varying(255) | | | | extended | |
gossip | text | | | | extended | |
date_last_email | timestamp(0) without time zone | | | | plain | |
date_registered | timestamp(0) without time zone | | not null | | plain | |
date_validated | timestamp(0) without time zone | | | | plain | |
date_last_login | timestamp(0) without time zone | | not null | | plain | |
must_change_password | smallint | | | | plain | |
auth_id | bigint | | | | plain | |
auth_str | character varying(255) | | | | extended | |
disabled | smallint | | not null | '0'::smallint | plain | |
disabled_reason | text | | | | extended | |
inline_help | smallint | | | | plain | |
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_email" UNIQUE CONSTRAINT, btree (email)
"users_username" UNIQUE CONSTRAINT, btree (username)
Regards,
Alec Smecher
Public Knowledge Project Team
That’s interesting. I’m not a frequent PostgreSQL user these days, but I know it often has several interrelated entities to go with constraints/sequences/foreign keys, and it looks like your database might contain the index but not the constraint, or something similar.
I don’t recommend adding IF EXISTS to the CREATE UNIQUE statement, because the index is being recreated differently, and it looks as though it’s not being properly dropped first.
According to the PostgreSQL documentation, DROP INDEX should implicitly drop the contraint that goes with it (see the docs):
Currently DROP CONSTRAINT drops only CHECK constraints. To remove a PRIMARY or UNIQUE constraint, drop the relevant index using the DROP INDEX command.
…so I think this might be something peculiar with your database.
You might be able to work around this by modifying the users table to drop any unique constraint (and/or index) on username before running the upgrade.
Thanks,
Alec Smecher
Public Knowledge Project Team
I had to change the file: lib/pkp/classes/migration/upgrade/v3_4_0/I7249_UpdateUsersUniqueIndex.php
by changing these lines:
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_username
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email;
The upgrade works but when i check “\d+ users;” I get this:
Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------+-----------------------------+-----------+----------+----------------------------------------+----------+-------------+--------------+-------------
user_id | bigint | | not null | nextval('users_user_id_seq'::regclass) | plain | | |
username | character varying(32) | | not null | | extended | | |
password | character varying(255) | | not null | | extended | | |
email | character varying(255) | | not null | | extended | | |
url | character varying(2047) | | | | extended | | |
phone | character varying(32) | | | | extended | | |
mailing_address | character varying(255) | | | | extended | | |
billing_address | character varying(255) | | | | extended | | |
country | character varying(90) | | | | extended | | |
locales | character varying(255) | | not null | '[]'::character varying | extended | | |
date_last_email | timestamp without time zone | | | | plain | | |
date_registered | timestamp without time zone | | not null | | plain | | |
date_validated | timestamp without time zone | | | | plain | | |
date_last_login | timestamp without time zone | | | | plain | | |
must_change_password | smallint | | | | plain | | |
auth_id | bigint | | | | plain | | |
auth_str | character varying(255) | | | | extended | | |
disabled | smallint | | not null | 0 | plain | | |
disabled_reason | text | | | | extended | | |
inline_help | smallint | | | | plain | | |
gossip | text | | | | extended | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_email" UNIQUE, btree (lower(email::text))
"users_username" UNIQUE, btree (lower(username::text))
It seems that there is no CONSTRAINT for users_email and users_username. Is that correct and would it cause any problems?
