PDOException while recreating missing indexes

Describe the problem you’re having:

Recreate missing indexes fails with an exception

If possible include steps to reproduce the problem:

Run php update.php --indexes

tt-rss version (including git commit id): 6eb94f1e1397ae97ed9a0e78d0edc2d04b0915a6 (latest as of now)

Platform (i.e. Linux distro, PHP, PostgreSQL, etc) versions:
Debian 10, PHP 7.4.7, PostgreSQL 11.8

Please provide any additional information below:

$ php update.php --indexes
[19:42:10/10507] Lock: update.lock
[19:42:10/10507] PLEASE BACKUP YOUR DATABASE BEFORE PROCEEDING!
[19:42:10/10507] Type 'yes' to continue.
yes
[19:42:12/10507] clearing existing indexes...
[19:42:12/10507] DROP INDEX ttrss_entries_guid_key
PHP Fatal error:  Uncaught PDOException: SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop index ttrss_entries_guid_key because constraint ttrss_entries_guid_key on table ttrss_entries requires it
HINT:  You can drop constraint ttrss_entries_guid_key on table ttrss_entries instead. in /var/www/tt-rss/update.php:293
Stack trace:
#0 /var/www/tt-rss/update.php(293): PDO->query()
#1 {main}
  thrown in /var/www/tt-rss/update.php on line 293

Shall I drop the constraint manually with
ALTER TABLE ttrss_entries DROP CONSTRAINT ttrss_entries_guid_key
or is this something which should be fixed in the update script?

  1. recreating indexes is really not something that you should normally ever do. can you tell me, why did you run it in the first place?

  2. unless i’m misreading this you’ve just dropped a unique constraint off a column. this is a very bad idea, you should add it back and never do stuff like that in the future.

1 & 2 aside, this particular command should probably skip indexes which sit on constraints. or the whole thing should be removed because i’m not sure why is it even exposed like that. :thinking:

  1. I run it, because it could be that there were some broken migrations from the past and therefore recreating missing schema indexes sounds like a good idea

  2. it was not dropped, because ttrss_entries requires it as you can see in the error message. It tried to drop it, but it couldnt. From postgresql

2020-06-24 23:44:37.718 EEST [8450] ERROR:  canceling autovacuum task
2020-06-24 23:44:37.718 EEST [8450] CONTEXT:  automatic vacuum of table "tt_rss.public.ttrss_entries"
2020-06-24 23:44:49.331 EEST [12073] tt_rss@tt_rss ERROR:  cannot drop index ttrss_entries_guid_key because constraint ttrss_entries_guid_key on table ttrss_entries requires it                                                                                              
2020-06-24 23:44:49.331 EEST [12073] tt_rss@tt_rss HINT:  You can drop constraint ttrss_entries_guid_key on table ttrss_entries instead.                                                                                                                                      
2020-06-24 23:44:49.331 EEST [12073] tt_rss@tt_rss STATEMENT:  DROP INDEX ttrss_entries_guid_key

ah, yes, good. for some reason i misread this as you actually dropping the constraint manually.

maybe this script should continue if index fails to be deleted. it’s very probable that this is how this worked initially, i.e. before the big PDO rewrite, when failed SQL queries didn’t necessarily stop execution.