Path alias update to remove index fails on postgresql

This update is in 10.4 too, so moving there, we can backport to 10.3 and possibly 10.2 from there, and retitling for the actual failure.

I have checked for the 10.4.X also, there it's not working. It's giving the same error.

@shahin-raza I have tried to reproduce this issue/ error message but been unsuccessful. I have done an upgrade from 10.0.9 to 10.2.3 while using PostgreSQL version 13. Then I have run updb and had no error message.

I also created an article node before the migration, following the steps to reproduce in the related issue #3376421.

If there are other steps I need to take to reproduce it can you please describe them by editing the issue description. Thank you in advance.

After running drush updb here are the postges index details for the postgres 13 database:

db=# \d path_alias;
Table "public.path_alias"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('path_alias_id_seq'::regclass)
revision_id | bigint | | |
uuid | character varying(128) | | not null |
langcode | character varying(12) | | not null |
path | character varying(255) | | |
alias | character varying(255) | | |
status | smallint | | not null |
Indexes:
"path_alias____pkey" PRIMARY KEY, btree (id)
"path_alias__path_alias__alias_langcode_id_status__idx" btree (alias, langcode, id, status)
"path_alias__path_alias__path_langcode_id_status__idx" btree (path, langcode, id, status)
"path_alias__path_alias__revision_id__key" UNIQUE CONSTRAINT, btree (revision_id)
"path_alias__path_alias_field__uuid__value__key" UNIQUE CONSTRAINT, btree (uuid)
Check constraints:
"path_alias_id_check" CHECK (id >= 0)
"path_alias_revision_id_check" CHECK (revision_id >= 0)

It seems clear that this issue was fixed by https://www.drupal.org/project/drupal/issues/3376421 🐛 Remove suboptimal path_alias__status index from path_alias table Fixed . So I am closing it.

Re #6 isn't the issue here that the update hook from that referenced issue fails to run? It should only fail of the index doesn't exist. I don't know why the index wouldn't exist though, I guess you could try manually delete the index before running the update to see if that fails.

@mstrelan Thank you for the feedback. I am seeing this in the description above: "Exception thrown while performing a schema update. constraint path_alias__path_alias__revision_id__key of relation "path_alias" does not exist". Even with your explanation I am still not exactly clear what to look for when trying to reproduce.

I was expecting to see an error message ie an 'Exception thrown.' When running drush updb you get a list of the update operations to be performed and you have to confirm by choosing 'yes'. Within that list is the operation to remove the 'path_alias__status' index.

At #8 where you mention 'the index doesn't exist' can you clarify you mean the 'path_alias__path_alias__revision_id__key' index?

If so then I will try manually removing it prior to the upgrade/ update. Should I then expect to see an error message?

I have dropped the constraint which seems to have removed the index from the path_alias table:

db=# drop index path_alias__path_alias__revision_id__key;
ERROR: cannot drop index path_alias__path_alias__revision_id__key because constraint path_alias__path_alias__revision_id__key on table path_alias requires it
HINT: You can drop constraint path_alias__path_alias__revision_id__key on table path_alias instead.
db=# alter table path_alias drop constraint path_alias__path_alias__revision_id__key;
ALTER TABLE
db=# \d path_alias;
Table "public.path_alias"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('path_alias_id_seq'::regclass)
revision_id | bigint | | |
uuid | character varying(128) | | not null |
langcode | character varying(12) | | not null |
path | character varying(255) | | |
alias | character varying(255) | | |
status | smallint | | not null |
Indexes:
"path_alias____pkey" PRIMARY KEY, btree (id)
"path_alias__path_alias__alias_langcode_id_status__idx" btree (alias, langcode, id, status)
"path_alias__path_alias__path_langcode_id_status__idx" btree (path, langcode, id, status)
"path_alias__path_alias__status__idx" btree (status, id)
"path_alias__path_alias_field__uuid__value__key" UNIQUE CONSTRAINT, btree (uuid)
Check constraints:
"path_alias_id_check" CHECK (id >= 0)
"path_alias_revision_id_check" CHECK (revision_id >= 0)

I have repeated the same update but removing the index first. Here is the output of 'drush updb':

| => ddev drush updb
--------------- ------------------------------------------------- --------------- -----------------------------------------------------------------------------
Module Update ID Type Description
--------------- ------------------------------------------------- --------------- -----------------------------------------------------------------------------
system 10100 hook_update_n 10100 - Remove the year 2038 date limitation.
system 10101 hook_update_n 10101 - Change the {batch} table [bid] field to serial.
system 10201 hook_update_n 10201 - Clear left over entries in the revision data table.
pgsql 10101 hook_update_n 10101 - Update sequences' owner created from serial columns in PostgreSQL.
help 10200 hook_update_n 10200 - Install search index table for help topics.
dblog 10100 hook_update_n 10100 - Remove the year 2038 date limitation.
dblog 10101 hook_update_n 10101 - Converts the 'wid' of the 'watchdog' table to a big integer.
block_content 10100 hook_update_n 10100 - Update entity definition to handle revision routes.
block_content 10200 hook_update_n 10200 - Remove the unique values constraint from block content info fields.
comment 10100 hook_update_n 10100 - Remove the year 2038 date limitation.
history 10100 hook_update_n 10100 - Remove the year 2038 date limitation.
big_pipe html5_placeholders post-update Clear the render cache.
block_content block_library_view_permission post-update Update block_content 'block library' view permission.
block_content move_custom_block_library post-update Moves the custom block library to Content.
block_content sort_permissions post-update Update permissions for users with "administer blocks" permission.
ckeditor5 code_block post-update Updates Text Editors using CKEditor 5 Code Block.
ckeditor5 list_multiblock post-update Updates Text Editors using CKEditor 5.
ckeditor5 list_start_reversed post-update Updates Text Editors using CKEditor 5 to native List "start" functionality.
editor image_lazy_load post-update Enable filter_image_lazy_load if editor_file_reference is enabled.
file add_default_filename_sanitization_configuration post-update Add default filename sanitization configuration.
file add_permissions_to_roles post-update Grant all non-anonymous roles the 'delete own files' permission.
filter sort_filters post-update Sorts filter format filter configuration.
help add_permissions_to_roles post-update Grant all admin roles the 'access help pages' permission.
help help_topics_search post-update Install or update config for help topics if the search module installed.
help help_topics_uninstall post-update Uninstall the help_topics module if installed.
path_alias drop_path_alias_status_index post-update Remove the path_alias__status index.
system add_description_to_entity_form_mode post-update Update description for form modes.
system add_description_to_entity_view_mode post-update Update description for view modes.
system enable_password_compatibility post-update Enable the password compatibility module.
system linkset_settings post-update Add new menu linkset endpoint setting.
system mailer_dsn_settings post-update Add new default mail transport dsn.
system mailer_structured_dsn_settings post-update Add new default mail transport dsn.
system remove_asset_entries post-update Remove redundant asset state and config.
system remove_asset_query_string post-update Remove redundant asset query string state.
system set_blank_log_url_to_null post-update Updates system.theme.global:logo.url config if it's still at the default.
system timestamp_formatter post-update Update timestamp formatter settings for entity view displays.
text allowed_formats post-update Add allowed_formats setting to existing text fields.
update set_blank_fetch_url_to_null post-update Updates update.settings:fetch.url config if it's still at the default.
views add_missing_labels post-update Add labels to views which don't have one.
views boolean_custom_titles post-update Update Views config schema to make boolean custom titles translatable.
views fix_revision_id_part post-update Fix '-revision_id' replacement token syntax.
views oembed_eager_load post-update Add eager load option to all oembed type field configurations.
views remove_default_argument_skip_url post-update Remove default_argument_skip_url setting.
views remove_skip_cache_setting post-update Remove the skip_cache settings.
views responsive_image_lazy_load post-update Add lazy load options to all responsive image type field configurations.
views taxonomy_filter_user_context post-update Removes User context from views with taxonomy filters.
views timestamp_formatter post-update Update timestamp formatter settings for views.
--------------- ------------------------------------------------- --------------- -----------------------------------------------------------------------------

Do you wish to run the specified pending updates? (yes/no) [yes]:
>

> [notice] Update started: system_update_10100
> [notice] Update completed: system_update_10100
> [notice] Update started: system_update_10101
> [notice] Update completed: system_update_10101
> [notice] Update started: dblog_update_10100
> [notice] Update completed: dblog_update_10100
> [notice] Update started: block_content_update_10100
> [notice] Added revision routes to Content block entity type.
> [notice] Update completed: block_content_update_10100
> [notice] Update started: system_update_10201
> [notice] Update completed: system_update_10201
> [notice] Update started: pgsql_update_10101
> [notice] Update completed: pgsql_update_10101
> [notice] Update started: help_update_10200
> [notice] Update completed: help_update_10200
> [notice] Update started: dblog_update_10101
> [notice] Update completed: dblog_update_10101
> [notice] Update started: block_content_update_10200
> [notice] Update completed: block_content_update_10200
> [notice] Update started: comment_update_10100
> [notice] Update completed: comment_update_10100
> [notice] Update started: history_update_10100
> [notice] Update completed: history_update_10100
> [notice] Update started: big_pipe_post_update_html5_placeholders
> [notice] Update completed: big_pipe_post_update_html5_placeholders
> [notice] Update started: block_content_post_update_block_library_view_permission
> [notice] Update completed: block_content_post_update_block_library_view_permission
> [notice] Update started: block_content_post_update_move_custom_block_library
> [notice] Update completed: block_content_post_update_move_custom_block_library
> [notice] Update started: block_content_post_update_sort_permissions
> [notice] Update completed: block_content_post_update_sort_permissions
> [notice] Update started: ckeditor5_post_update_code_block
> [notice] Update completed: ckeditor5_post_update_code_block
> [notice] Update started: ckeditor5_post_update_list_multiblock
> [notice] Update completed: ckeditor5_post_update_list_multiblock
> [notice] Update started: ckeditor5_post_update_list_start_reversed
> [notice] Update completed: ckeditor5_post_update_list_start_reversed
> [notice] Update started: editor_post_update_image_lazy_load
> [notice] Update completed: editor_post_update_image_lazy_load
> [notice] Update started: file_post_update_add_default_filename_sanitization_configuration
> [notice] Update completed: file_post_update_add_default_filename_sanitization_configuration
> [notice] Update started: file_post_update_add_permissions_to_roles
> [notice] Update completed: file_post_update_add_permissions_to_roles
> [notice] Update started: filter_post_update_sort_filters
> [notice] Update completed: filter_post_update_sort_filters
> [notice] Update started: help_post_update_add_permissions_to_roles
> [notice] Update completed: help_post_update_add_permissions_to_roles
> [notice] Update started: help_post_update_help_topics_search
> [notice] Update completed: help_post_update_help_topics_search
> [notice] Update started: help_post_update_help_topics_uninstall
> [notice] Update completed: help_post_update_help_topics_uninstall
> [notice] Update started: path_alias_post_update_drop_path_alias_status_index
> [notice] Update completed: path_alias_post_update_drop_path_alias_status_index
> [notice] Update started: system_post_update_add_description_to_entity_form_mode
> [notice] Update completed: system_post_update_add_description_to_entity_form_mode
> [notice] Update started: system_post_update_add_description_to_entity_view_mode
> [notice] Update completed: system_post_update_add_description_to_entity_view_mode
> [notice] Update started: system_post_update_enable_password_compatibility
> [notice] Update completed: system_post_update_enable_password_compatibility
> [notice] Update started: system_post_update_linkset_settings
> [notice] Update completed: system_post_update_linkset_settings
> [notice] Update started: system_post_update_mailer_dsn_settings
> [notice] Update completed: system_post_update_mailer_dsn_settings
> [notice] Update started: system_post_update_mailer_structured_dsn_settings
> [notice] Update completed: system_post_update_mailer_structured_dsn_settings
> [notice] Update started: system_post_update_remove_asset_entries
> [notice] Update completed: system_post_update_remove_asset_entries
> [notice] Update started: system_post_update_remove_asset_query_string
> [notice] Update completed: system_post_update_remove_asset_query_string
> [notice] Update started: system_post_update_set_blank_log_url_to_null
> [notice] Update completed: system_post_update_set_blank_log_url_to_null
> [notice] Update started: system_post_update_timestamp_formatter
> [notice] Update completed: system_post_update_timestamp_formatter
> [notice] Update started: text_post_update_allowed_formats
> [notice] Update completed: text_post_update_allowed_formats
> [notice] Update started: update_post_update_set_blank_fetch_url_to_null
> [notice] Update completed: update_post_update_set_blank_fetch_url_to_null
> [notice] Update started: views_post_update_add_missing_labels
> [notice] Update completed: views_post_update_add_missing_labels
> [notice] Update started: views_post_update_boolean_custom_titles
> [notice] Update completed: views_post_update_boolean_custom_titles
> [notice] Update started: views_post_update_fix_revision_id_part
> [notice] Update completed: views_post_update_fix_revision_id_part
> [notice] Update started: views_post_update_oembed_eager_load
> [notice] Update completed: views_post_update_oembed_eager_load
> [notice] Update started: views_post_update_remove_default_argument_skip_url
> [notice] Update completed: views_post_update_remove_default_argument_skip_url
> [notice] Update started: views_post_update_remove_skip_cache_setting
> [notice] Update completed: views_post_update_remove_skip_cache_setting
> [notice] Update started: views_post_update_responsive_image_lazy_load
> [notice] Update completed: views_post_update_responsive_image_lazy_load
> [notice] Update started: views_post_update_taxonomy_filter_user_context
> [notice] Update completed: views_post_update_taxonomy_filter_user_context
> [notice] Update started: views_post_update_timestamp_formatter
> [notice] Update completed: views_post_update_timestamp_formatter
[success] Finished performing updates.

Seems we can't easily reproduce this, setting to PMNMI for this. @shahin-raza are you able to reproduce this on a vanilla Drupal installation?