Rolling back `2021080408053529_fix_indices` in TO DB tests: `pys_location_last_updated_idx" already exists`

[zrhoffman]

This Bug Report affects these Traffic Control components:

• Traffic Ops

Current behavior:

If run using a database dump, the database tests fail to roll back the 2021080408053529_fix_indices migration:

+ ./db/admin --env=production down
Error running migrate down: migration failed: relation "pys_location_last_updated_idx" already exists in line 0: /*

Line it fails at:

trafficcontrol/traffic_ops/app/db/migrations/2021080408053529_fix_indices.down.sql

Line 18 in 308db85

ALTER INDEX phys_location_last_updated_idx RENAME TO pys_location_last_updated_idx;

Somehow, an index named pys_location_last_updated_idx still exists after running

trafficcontrol/traffic_ops/app/db/migrations/2021080408053529_fix_indices.up.sql

Line 18 in 308db85

ALTER INDEX pys_location_last_updated_idx RENAME TO phys_location_last_updated_idx;

I cannot reproduce this issue outside of the DB tests, but this is not a Migrate-specific issue. If you run the DB tests with Goose at 3cc78ea~ but add the Goose equivalent of 2021080408053529_fix_indices:

-- +goose Up
-- SQL in section 'Up' is executed when this migration is applied

ALTER INDEX pys_location_last_updated_idx RENAME TO phys_location_last_updated_idx;
DROP INDEX topology_last_updated_idx;
CREATE INDEX topology_last_updated_idx ON topology (last_updated DESC NULLS LAST);

-- +goose Down
-- SQL section 'Down' is executed when this migration is rolled back

ALTER INDEX phys_location_last_updated_idx RENAME TO pys_location_last_updated_idx;
DROP INDEX topology_last_updated_idx;
CREATE INDEX topology_last_updated_idx ON topology_cachegroup (last_updated DESC NULLS LAST);

then the TO DB tests will still fail.

+ ./db/admin --env=production down
Running goose down...
goose: migrating db environment 'production', current version: 2021080408053529, target: 2021070800000000
2021/08/19 18:52:11 FAIL 2021080408053529_fix_indices.sql (pq: relation "pys_location_last_updated_idx" already exists), quitting migration.
Can't run goose: exit status 1

Back to Migrate: If the ALTER INDEX RENAME in the up migration is changed to

DROP INDEX pys_location_last_updated_idx;
CREATE INDEX phys_location_last_updated_idx ON phys_location (last_updated DESC NULLS LAST);

then the pys_location_last_updated_idx still does not get removed in the up migration in the TO DB tests:

+ ./db/admin --env=production down
Error running migrate down: migration failed: relation "pys_location_last_updated_idx" already exists in line 0: /*

Expected behavior:

The Traffic Ops DB tests should succeed on all up/down migrations, with or without a database dump.

Steps to reproduce:

1. Dump the Traffic Ops database

2. Run the Traffic Ops DB tests using a database dump.

   cd traffic_ops_db/test/docker
   cp path-to-the-dump.dump initdb.d/
   docker-compose build --parallel
   docker-compose up trafficops-db-admin
   

[ocket8888]

Could it be a schema problem? It drops an index from the default schema, which isn't always the same as public depending on where it's run?

[zrhoffman]

Could it be a schema problem? It drops an index from the default schema, which isn't always the same as public depending on where it's run?

I don't think so. The DB dump references public, and if I add an additional migration dropping pys_location_last_updated_idx, it fails, saying it does not exist.

[zrhoffman]

I can reproduce it using dumps from CDN in a Box from PostgreSQL 9.6, but not from PostgreSQL 13.2. so yeah, probably a schema problem like you suggested.

#5673 included some logic to take out out CREATE SCHEMA public, which is from 9.6 dumps, if the TO DB tests are being run with a PostgreSQL version > 10:

trafficcontrol/traffic_ops_db/test/docker/initdb.d/90-load-dumps.sh

Lines 30 to 32 in 308db85

	if [[ "${POSTGRES_VERSION%%.*}" -gt 10 ]]; then
	sed -i '/^CREATE SCHEMA public;$/d' "$t"
	fi

Maybe there is something else to account for in 9.6 dumps that has only come up now that we have the index-renaming migration from #6081.