Postgres version 12 error due to BEFORE row triggers in partitioned tables

[jthetzel]

Running pypgstac migrate --toversion 0.3.5 against a clean Postgres version 12 database fails with message:

asyncpg.exceptions.WrongObjectTypeError: "items" is a partitioned table
DETAIL:  Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.

This might be because BEFORE row level triggers for partitioned tables are not supported for Postgres version 12, but are supported for Postgres version 13 (found some hints here: https://www.depesz.com/2020/03/31/waiting-for-postgresql-13-enable-before-row-level-triggers-for-partitioned-tables/ ).

Upgrading to Postgres 13 resolved the error.

Full stack trace:

No pgstac version set, installing 0.3.5 from scratch.
Running migrations for ['/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/pypgstac/migrations/pgstac.0.3.5.sql'].
Traceback (most recent call last):
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/bin/pypgstac", line 8, in <module>
    sys.exit(app())
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/typer/main.py", line 214, in __call__
    return get_command(self)(*args, **kwargs)
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/typer/main.py", line 497, in wrapper
    return callback(**use_params)  # type: ignore
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/pypgstac/pypgstac.py", line 31, in migrate
    version = asyncio.run(run_migration(dsn, toversion))
  File "/home/jthetzel/.pyenv/versions/3.9.6/lib/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/home/jthetzel/.pyenv/versions/3.9.6/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/pypgstac/migrate.py", line 163, in run_migration
    await conn.execute(migration_sql)
  File "/home/jthetzel/.cache/pypoetry/virtualenvs/c-stac-api-4u_-WkNV-py3.9/lib/python3.9/site-packages/asyncpg/connection.py", line 297, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 336, in query
asyncpg.exceptions.WrongObjectTypeError: "items" is a partitioned table
DETAIL:  Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/home/jthetzel/src/c-core-labs/c-stac-api/scripts.py", line 53, in migrate
    check_call(["pypgstac", "migrate"])
  File "/home/jthetzel/.pyenv/versions/3.9.6/lib/python3.9/subprocess.py", line 373, in check_call
    raise CalledProcessError(retcode, cmd)
subprocess.CalledProcessError: Command '['pypgstac', 'migrate']' returned non-zero exit status 1.

[jthetzel]

Forgot to install pg_partman.

[captaincoordinates]

@jthetzel can you please clarify - is pg_partman required for Postgres 12? This PR recently removed it as a requirement from the README