Skip to content

fix: index assets table on urn column#165

Merged
StewartJingga merged 1 commit intoraystack:mainfrom
sudo-suhas:manage-assets-with-urn-migrations
Aug 16, 2022
Merged

fix: index assets table on urn column#165
StewartJingga merged 1 commit intoraystack:mainfrom
sudo-suhas:manage-assets-with-urn-migrations

Conversation

@sudo-suhas
Copy link
Contributor

@sudo-suhas sudo-suhas commented Aug 16, 2022

  • Drop unique index on (urn, type, service) concurrently. This ensures
    that we don't take an exclusive lock on the table which would lock out
    selects, inserts, updates and deletes on the index's table.
  • Create a unique index on urn column in the assets table.

Related to #162.

@sudo-suhas
fix(assets): Index assets table on urn column
1319449 
- Drop unique index on (urn, type, service) concurrently. This ensures
  that we don't take an exclusive lock on the table which would lock out
  selects, inserts, updates and deletes on the index's table.
- Create a unique index on urn column in the assets table.
@sudo-suhas sudo-suhas force-pushed the manage-assets-with-urn-migrations branch from 598b85a to 1319449 Compare August 16, 2022 06:45
@StewartJingga
Copy link
Contributor

StewartJingga commented Aug 16, 2022

@sudo-suhas I might've missed something here, but what is the reason for splitting the migration? can they be in one use case?

@sudo-suhas
Copy link
Contributor Author

sudo-suhas commented Aug 16, 2022 •
edited
Loading

Already included the reason as a comment in one of the migration files:

-- By default, golang-migrate wraps multiple SQL statements in a transaction.
-- Dropping index concurrently is not allowed in a transaction. So the drop
-- statement needs to be the only statement in the migration.

Command output for failed migrate run:

➜ go run main.go migrate
Preparing migration...
INFO[0000] compass is migrating                          version=
INFO[0000] Migrating Postgres...
INFO[0000] Initiating Postgres client...
Usage:  compass migrate [flags]
problem with migration migration failed: migration failed: DROP INDEX CONCURRENTLY cannot run inside a transaction block in line 0: DROP INDEX CONCURRENTLY IF EXISTS assets_idx_urn_type_service;

CREATE UNIQUE INDEX assets_idx ON assets(urn);
 (details: pq: DROP INDEX CONCURRENTLY cannot run inside a transaction block)
exit status 1

@coveralls
Copy link

coveralls commented Aug 16, 2022

Pull Request Test Coverage Report for Build 2865844096

  • 0 of 0 changed or added relevant lines in 0 files are covered.
  • No unchanged relevant lines lost coverage.
  • Overall coverage remained the same at 85.74%
Totals Coverage Status
Change from base Build 2794118351: 0.0%
Covered Lines: 4726
Relevant Lines: 5512
💛 - Coveralls

@ravisuhag ravisuhag changed the title Index assets table on urn column fix: index assets table on urn column Aug 16, 2022
@StewartJingga StewartJingga linked an issue Aug 16, 2022 that may be closed by this pull request
feat: managing Asset via URN instead of ID #162
Closed
@StewartJingga StewartJingga removed a link to an issue Aug 16, 2022
feat: managing Asset via URN instead of ID #162
Closed
@StewartJingga StewartJingga merged commit 0e568a1 into raystack:main Aug 16, 2022
@sudo-suhas sudo-suhas deleted the manage-assets-with-urn-migrations branch August 16, 2022 09:39
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@StewartJingga StewartJingga StewartJingga approved these changes

Assignees

@sudo-suhas sudo-suhas

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@sudo-suhas @StewartJingga @coveralls