search

stac-utils / pgstac

Schema, functions and a python library for storing and accessing STAC collections and items in PostgreSQL
MIT License
151 stars 36 forks source link

Migration fails from 0.7.4 #180

Closed mneagul closed 1 year ago

mneagul commented 1 year ago

Hello,

We are trying to upgrade from 0.7.4 to newer versions and we see this error during pypgstac migrate:

pypgstac migrate
Traceback (most recent call last):
  File "/srv/conda/bin/pypgstac", line 8, in <module>
    sys.exit(cli())
  File "/srv/conda/lib/python3.10/site-packages/pypgstac/pypgstac.py", line 118, in cli
    fire.Fire(PgstacCLI)
  File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 141, in Fire
    component_trace = _Fire(component, args, parsed_flag_args, context, name)
  File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 466, in _Fire
    component, remaining_args = _CallAndUpdateTrace(
  File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
    component = fn(*varargs, **kwargs)
  File "/srv/conda/lib/python3.10/site-packages/pypgstac/pypgstac.py", line 57, in migrate
    return migrator.run_migration(toversion=toversion)
  File "/srv/conda/lib/python3.10/site-packages/pypgstac/migrate.py", line 144, in run_migration
    cur.execute(migration_sql)
  File "/srv/conda/lib/python3.10/site-packages/psycopg/cursor.py", line 557, in execute
    raise ex.with_traceback(None)
psycopg.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

No further error messages are available.

mmcfarland commented 1 year ago

Hi @mneagul can you add the --debug flag to the migrate command and paste the output here? Also, what version of PostgreSQL are you using?

mneagul commented 1 year ago

Hi @mmcfarland,

pypgstac migrate --debug
DEBUG:pypgstac.db:PG VERSION: 14.5.
INFO:pypgstac.migrate:Migrating PGStac on PostgreSQL Version 14.5
DEBUG:pypgstac.db:VERSION: 0.7.4
INFO:pypgstac.migrate:Migrating from 0.7.4 to 0.7.6.
DEBUG:pypgstac.migrate:Running migration file pgstac.0.7.4-0.7.5.sql.
DEBUG:smart_open.smart_open_lib:{'uri': '/srv/conda/lib/python3.10/site-packages/pypgstac/migrations/pgstac.0.7.4-0.7.5.sql', 'mode': 'r', 'buffering': -1, 'encoding': None, 'errors': None, 'newline': None, 'closefd': True, 'opener': None, 'compression': 'infer_from_extension', 'transport_params': None}
Traceback (most recent call last):
  File "/srv/conda/bin/pypgstac", line 8, in <module>
    sys.exit(cli())
  File "/srv/conda/lib/python3.10/site-packages/pypgstac/pypgstac.py", line 118, in cli
    fire.Fire(PgstacCLI)
  File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 141, in Fire
    component_trace = _Fire(component, args, parsed_flag_args, context, name)
  File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 466, in _Fire
    component, remaining_args = _CallAndUpdateTrace(
  File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
    component = fn(*varargs, **kwargs)
  File "/srv/conda/lib/python3.10/site-packages/pypgstac/pypgstac.py", line 57, in migrate
    return migrator.run_migration(toversion=toversion)
  File "/srv/conda/lib/python3.10/site-packages/pypgstac/migrate.py", line 144, in run_migration
    cur.execute(migration_sql)
  File "/srv/conda/lib/python3.10/site-packages/psycopg/cursor.py", line 557, in execute
    raise ex.with_traceback(None)
psycopg.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

Regarding the postgresql version:

PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
mneagul commented 1 year ago

The postgresql server is from the bitnami helm chart postgresql-11.9.13

mneagul commented 1 year ago

Just restored from a clean backup before any upgrade attempts and the error is more verbose (the previous version is 0.6.13 ):


Traceback (most recent call last):
--
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/bin/pypgstac", line 8, in <module>
Sun, May 7 2023 10:52:52 pm | sys.exit(cli())
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/lib/python3.10/site-packages/pypgstac/pypgstac.py", line 118, in cli
Sun, May 7 2023 10:52:52 pm | fire.Fire(PgstacCLI)
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 141, in Fire
Sun, May 7 2023 10:52:52 pm | component_trace = _Fire(component, args, parsed_flag_args, context, name)
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 466, in _Fire
Sun, May 7 2023 10:52:52 pm | component, remaining_args = _CallAndUpdateTrace(
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/lib/python3.10/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
Sun, May 7 2023 10:52:52 pm | component = fn(*varargs, **kwargs)
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/lib/python3.10/site-packages/pypgstac/pypgstac.py", line 57, in migrate
Sun, May 7 2023 10:52:52 pm | return migrator.run_migration(toversion=toversion)
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/lib/python3.10/site-packages/pypgstac/migrate.py", line 144, in run_migration
Sun, May 7 2023 10:52:52 pm | cur.execute(migration_sql)
Sun, May 7 2023 10:52:52 pm | File "/srv/conda/lib/python3.10/site-packages/psycopg/cursor.py", line 557, in execute
Sun, May 7 2023 10:52:52 pm | raise ex.with_traceback(None)
Sun, May 7 2023 10:52:52 pm | psycopg.errors.ObjectInUse: cannot REFRESH MATERIALIZED VIEW "partitions" because it is being used by active queries in this session
Sun, May 7 2023 10:52:52 pm | CONTEXT: SQL statement "REFRESH MATERIALIZED VIEW partitions"
Sun, May 7 2023 10:52:52 pm | PL/pgSQL function update_partition_stats(text,boolean) line 69 at SQL statement
Sun, May 7 2023 10:52:52 pm | SQL statement "SELECT update_partition_stats('_items_11', 'f');"
Sun, May 7 2023 10:52:52 pm | PL/pgSQL function run_or_queue(text) line 21 at EXECUTE
Sun, May 7 2023 10:52:52 pm | SQL statement "SELECT run_or_queue(
Sun, May 7 2023 10:52:52 pm |  
Sun, May 7 2023 10:52:52 pm | format('SELECT update_partition_stats(%L, %L);', _partition, istrigger)
Sun, May 7 2023 10:52:52 pm |  
Sun, May 7 2023 10:52:52 pm | )"
Sun, May 7 2023 10:52:52 pm | PL/pgSQL function update_partition_stats_q(text,boolean) line 7 at PERFORM
asjohnston-asf commented 1 year ago

Also encountering the same 0.7.4 -> 0.7.5 migration error in https://github.com/ASFHyP3/asf-stac :

pypgstac migrate --debug
DEBUG:pypgstac.db:PG VERSION: 14.4.
INFO:pypgstac.migrate:Migrating PGStac on PostgreSQL Version 14.4
DEBUG:pypgstac.db:VERSION: 0.7.4
INFO:pypgstac.migrate:Migrating from 0.7.4 to 0.7.6.
DEBUG:pypgstac.migrate:Running migration file pgstac.0.7.4-0.7.5.sql.
DEBUG:smart_open.smart_open_lib:{'uri': '/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/pypgstac/migrations/pgstac.0.7.4-0.7.5.sql', 'mode': 'r', 'buffering': -1, 'encoding': None, 'errors': None, 'newline': None, 'closefd': True, 'opener': None, 'compression': 'infer_from_extension', 'transport_params': None}
INFO:pypgstac.db:NOTICE - role "pgstac_admin" already exists, skipping
INFO:pypgstac.db:NOTICE - role "pgstac_read" already exists, skipping
INFO:pypgstac.db:NOTICE - role "pgstac_ingest" already exists, skipping
INFO:pypgstac.db:NOTICE - role "postgres" is already a member of role "pgstac_admin"
INFO:pypgstac.db:NOTICE - schema "pgstac" already exists, skipping
INFO:pypgstac.db:NOTICE - role "pgstac_ingest" is already a member of role "pgstac_read"
INFO:pypgstac.db:NOTICE - function analyze_items() does not exist, skipping
INFO:pypgstac.db:NOTICE - function validate_constraints() does not exist, skipping
Traceback (most recent call last):
  File "/home/asjohnston/mambaforge/envs/asf-stac/bin/pypgstac", line 8, in <module>
    sys.exit(cli())
  File "/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/pypgstac/pypgstac.py", line 118, in cli
    fire.Fire(PgstacCLI)
  File "/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/fire/core.py", line 141, in Fire
    component_trace = _Fire(component, args, parsed_flag_args, context, name)
  File "/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/fire/core.py", line 466, in _Fire
    component, remaining_args = _CallAndUpdateTrace(
  File "/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
    component = fn(*varargs, **kwargs)
  File "/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/pypgstac/pypgstac.py", line 57, in migrate
    return migrator.run_migration(toversion=toversion)
  File "/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/pypgstac/migrate.py", line 144, in run_migration
    cur.execute(migration_sql)
  File "/home/asjohnston/mambaforge/envs/asf-stac/lib/python3.9/site-packages/psycopg/cursor.py", line 723, in execute
    raise ex.with_traceback(None)
psycopg.errors.ObjectInUse: cannot REFRESH MATERIALIZED VIEW "partitions" because it is being used by active queries in this session
CONTEXT:  SQL statement "REFRESH MATERIALIZED VIEW partitions"
PL/pgSQL function update_partition_stats(text,boolean) line 69 at SQL statement
SQL statement "SELECT update_partition_stats('_items_1', 'f');"
PL/pgSQL function run_or_queue(text) line 21 at EXECUTE
SQL statement "SELECT run_or_queue(

        format('SELECT update_partition_stats(%L, %L);', _partition, istrigger)

    )"
PL/pgSQL function update_partition_stats_q(text,boolean) line 7 at PERFORM
bitner commented 1 year ago

There is an issue with the 0.7.4->0.7.5 migration path that does not show up in the migration tests and a fix will need to be backported.

You can migrate up to 0.7.4 from any previous version using pypgstac migrate 0.7.4

Until I get a fix figured out, you might be able to get past the issue by enabling the query queue in the pgstac settings temporarily while running the migrations (please comment here if this method works for you).

Make sure you have the use_queue setting and that it is set to 'true': INSERT INTO pgstac.pgstac_settings (name, value) VALUES ('use_queue', 'true') ON CONFLICT (name) DO UPDATE value=EXCLUDED.value;

Run the migration: pypgstac migrate

Change the setting for use_queue back to false and run all queries that were in the queue:

UPDATE pgstac.pgstac_settings SET value='false' WHERE name='use_queue';
CALL pgstac.run_queued_queries();
mrob95 commented 1 year ago

For future reference I had the same issue and the above fix worked, though there is a typo in the first query - should be DO UPDATE SET ...