Unhandled exception when creating WC in a Postrges working copy which doesn't have PostGIS extension

[pfw]

Describe the bug
If the PostGIS extension isn't enabled on a database then there will be an unhandled exception after init'ing a repo with a PG working copy and then importing data.

To Reproduce
Steps to reproduce the behaviour:

1. Create an empty PG database and don't enable PostGIS
2. mkdir pgrepo
3. cd pgrepo
4. kart init --workingcopy postgresql://localhost/nopostgis/public
5. kart import /Volumes/GIS/data.gov.au/mdb-boundaries.gpkg
6. See error

Expected behaviour

1. Kart should allow you to checkout a non-spatial working copy into a non-postgis database.
2. If there are spatial datasets in the repo, then the checkout should error out, with a message suggesting you install postGIS in your database.

Output

Starting git-fast-import...
Importing 1 features from mdb-boundaries.gpkg:mdbboundaries to mdbboundaries/ ...
Added 1 Features to index in 0.0s
Overall rate: 36 features/s)
Closed in 0s
Creating PostGIS working copy at postgresql://localhost/nopostgis/public ...
Traceback (most recent call last):
  File "sqlalchemy/engine/base.py", line 1901, in _execute_context
  File "sqlalchemy/engine/default.py", line 732, in do_execute
psycopg2.errors.UndefinedTable: relation "spatial_ref_sys" does not exist
LINE 1: SELECT auth_name, srtext FROM spatial_ref_sys WHERE srid = 4...
                                      ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "kart_cli.py", line 4, in <module>
  File "kart/cli.py", line 392, in entrypoint
  File "click/core.py", line 1130, in __call__
  File "click/core.py", line 1055, in main
  File "kart/cli_util.py", line 68, in invoke
  File "click/core.py", line 1657, in invoke
  File "click/core.py", line 1404, in invoke
  File "click/core.py", line 760, in invoke
  File "click/decorators.py", line 26, in new_func
  File "kart/tabular/import_.py", line 350, in import_
  File "kart/working_copy.py", line 262, in reset_to_head
  File "kart/working_copy.py", line 299, in reset
  File "kart/working_copy.py", line 192, in create_parts_if_missing
  File "kart/tabular/working_copy/base.py", line 1249, in reset
  File "kart/tabular/working_copy/base.py", line 915, in write_full
  File "kart/tabular/working_copy/postgis.py", line 126, in _write_meta
  File "sqlalchemy/orm/session.py", line 1712, in execute
  File "sqlalchemy/engine/base.py", line 1705, in _execute_20
  File "sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
  File "sqlalchemy/engine/base.py", line 1582, in _execute_clauseelement
  File "sqlalchemy/engine/base.py", line 1944, in _execute_context
  File "sqlalchemy/engine/base.py", line 2125, in _handle_dbapi_exception
  File "sqlalchemy/util/compat.py", line 208, in raise_
  File "sqlalchemy/engine/base.py", line 1901, in _execute_context
  File "sqlalchemy/engine/default.py", line 732, in do_execute
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "spatial_ref_sys" does not exist
LINE 1: SELECT auth_name, srtext FROM spatial_ref_sys WHERE srid = 4...
                                      ^

[SQL: SELECT auth_name, srtext FROM spatial_ref_sys WHERE srid = %(srid)s;]
[parameters: {'srid': 4283}]
(Background on this error at: https://sqlalche.me/e/14/f405)
[49093] Failed to execute script 'kart_cli' due to unhandled exception!

Version Info

❯ kart --version
Kart v0.11.5, Copyright (c) Kart Contributors
» GDAL v3.4.0; PROJ v6.3.0
» PyGit2 v1.9.0; Libgit2 v1.4.2; Git v2.33.GIT
» SQLAlchemy v1.4.40; pysqlite3 v2.6.0/v3.31.1; SpatiaLite v5.0.1; Libpq v14.0.5
» SpatialIndex v1.9.3

[rcoup]

Is this different from #728 ?

[olsen232]

Yes - one is "can't import FROM database" (where database is PostGRES but not PostGIS)
the other is "can't checkout TO working copy database" (where WC database is PostGRES but not PostGIS)

[rcoup]

If the dataset isn't spatial then PostGIS shouldn't really be a requirement.

But that's a future feature, we should fix the error messages first 😃

[AMANISPEACE]

The bug is that when initializing a repo with a PostgreSQL working copy and then importing data, if the PostGIS extension is not enabled on the database, an unhandled exception will occur. The expected behavior is that there should be an error requiring PostGIS to be enabled at the step of importing data. The error message in the output states that there is a "psycopg2.errors.UndefinedTable: relation "spatial_ref_sys" does not exist." which indicates that the "spatial_ref_sys" table is missing from the database. This table is specific to PostGIS and is used to store spatial reference system information. Therefore, the error is caused by the lack of PostGIS extension on the empty PG database.