Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

PostgreSQL+PostGIS: not found table 'public.topology' (non-default schema issue) #181

Closed
rafi opened this issue Mar 12, 2020 · 7 comments · Fixed by #184
Closed

PostgreSQL+PostGIS: not found table 'public.topology' (non-default schema issue) #181

rafi opened this issue Mar 12, 2020 · 7 comments · Fixed by #184
Assignees
@k1LoW
Labels
bug Something isn't working

Comments

@rafi
Copy link

rafi commented Mar 12, 2020

What happened

$ tbls doc
not found table 'public.topology'

Seems related to #28 and #153

You can reproduce with docker image mdillon/postgis:9.5

What you expected to happed

I expected a generated documentation.

What stacktrace or error message from tbls did you see?

not found table 'public.topology'

Anything else we need to know?

PostGIS is enabled and manages the topology schema. This is the output of \dt:

foobar> \dt
+----------+-------------------+--------+--------+
| Schema   | Name              | Type   | Owner  |
|----------+-------------------+--------+--------|
| public   | migration         | table  | dbuser |
| public   | spatial_ref_sys   | table  | dbuser |
| public   | subscriber        | table  | dbuser |
| public   | user              | table  | dbuser |
| public   | user_role         | table  | dbuser |
| public   | users_homes       | table  | dbuser |
| public   | users_roles       | table  | dbuser |
| public   | users_tokens      | table  | dbuser |
| topology | layer             | table  | dbuser |
| topology | topology          | table  | dbuser |
+----------+-------------------+--------+--------+

Environment

  • tbls version: 1.28.2
  • PostgreSQL v9.5.16
  • PostGIS v2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
  • .tbls.yml:
---

name: foobar
dsn: postgres://dbuser:dbpass@localhost:5432/foobar?sslmode=disable
docPath: docs/schema

Stacktrace

$ DEBUG=1 tbls doc
not found table 'public.topology'
github.com/k1LoW/tbls/schema.(*Schema).FindTableByName
        /Users/k1low/src/github.com/k1LoW/tbls/schema/schema.go:110
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze
        /Users/k1low/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:337
github.com/k1LoW/tbls/datasource.Analyze
        /Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:85
github.com/k1LoW/tbls/cmd.glob..func4
        /Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:69
github.com/spf13/cobra.(*Command).execute
        /Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:844
github.com/spf13/cobra.(*Command).ExecuteC
        /Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:945
github.com/spf13/cobra.(*Command).Execute
        /Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:885
github.com/k1LoW/tbls/cmd.Execute
        /Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:59
main.main
        /Users/k1low/src/github.com/k1LoW/tbls/main.go:33
runtime.main
        /Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/proc.go:203
runtime.goexit
        /Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/asm_amd64.s:1357
@rafi rafi added the bug Something isn't working label Mar 12, 2020
@k1LoW
Copy link
Owner

k1LoW commented Mar 13, 2020 •
edited
Loading

Hi @rafi ! Thank you for your report !!!

Could you provide an example DDL please?

I can't reproduce the error you're seeing now.

I tried as below.

docker-compose.yml

version: '3.4'

services:
  postgis:
    image: mdillon/postgis:9.5
    ports:
      - "55433:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=pgpass
      - POSTGRES_DB=testdb
      - USE_GEOS=1
      - USE_PROJ=1
      - USE_STATS=1

Example DDL

CREATE EXTENSION IF NOT EXISTS postgis;

DROP TABLE IF EXISTS test_table;

CREATE TABLE IF NOT EXISTS test_table
(
    gid    INTEGER PRIMARY KEY,
    geom   GEOMETRY(POINT, 4612)
);

PostgreSQL/PostDIS version

testdb=# SELECT * FROM postgis_version();
            postgis_version
---------------------------------------
 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

testdb=# SELECT * FROM version();
                                                               version
--------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.16 on x86_64-pc-linux-gnu (Debian 9.5.16-1.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

\dt

testdb=# \dt
                  List of relations
 Schema |           Name           | Type  |  Owner
--------+--------------------------+-------+----------
 public | spatial_ref_sys          | table | postgres
 public | test_table               | table | postgres
 tiger  | addr                     | table | postgres
 tiger  | addrfeat                 | table | postgres
 tiger  | bg                       | table | postgres
 tiger  | county                   | table | postgres
 tiger  | county_lookup            | table | postgres
 tiger  | countysub_lookup         | table | postgres
 tiger  | cousub                   | table | postgres
 tiger  | direction_lookup         | table | postgres
 tiger  | edges                    | table | postgres
 tiger  | faces                    | table | postgres
 tiger  | featnames                | table | postgres
 tiger  | geocode_settings         | table | postgres
 tiger  | geocode_settings_default | table | postgres
 tiger  | loader_lookuptables      | table | postgres
 tiger  | loader_platform          | table | postgres
 tiger  | loader_variables         | table | postgres
 tiger  | pagc_gaz                 | table | postgres
 tiger  | pagc_lex                 | table | postgres
 tiger  | pagc_rules               | table | postgres
 tiger  | place                    | table | postgres
 tiger  | place_lookup             | table | postgres
 tiger  | secondary_unit_lookup    | table | postgres
 tiger  | state                    | table | postgres
 tiger  | state_lookup             | table | postgres
 tiger  | street_type_lookup       | table | postgres
 tiger  | tabblock                 | table | postgres
 tiger  | tract                    | table | postgres
 tiger  | zcta5                    | table | postgres
 tiger  | zip_lookup               | table | postgres
 tiger  | zip_lookup_all           | table | postgres
 tiger  | zip_lookup_base          | table | postgres
 tiger  | zip_state                | table | postgres
 tiger  | zip_state_loc            | table | postgres
(35 rows)

tbls doc

✅ Success

$ tbls version
1.28.2
$ tbls doc pg://postgres:pgpass@localhost:55433/testdb\?sslmode=disable -f
dbdoc/schema.png
dbdoc/public.spatial_ref_sys.png
dbdoc/public.geography_columns.png
dbdoc/public.geometry_columns.png
dbdoc/public.raster_columns.png
dbdoc/public.raster_overviews.png
dbdoc/topology.topology.png
dbdoc/topology.layer.png
dbdoc/tiger.geocode_settings.png
dbdoc/tiger.geocode_settings_default.png
dbdoc/tiger.direction_lookup.png
dbdoc/tiger.secondary_unit_lookup.png
dbdoc/tiger.state_lookup.png
dbdoc/tiger.street_type_lookup.png
dbdoc/tiger.place_lookup.png
dbdoc/tiger.county_lookup.png
dbdoc/tiger.countysub_lookup.png
dbdoc/tiger.zip_lookup_all.png
dbdoc/tiger.zip_lookup_base.png
dbdoc/tiger.zip_lookup.png
dbdoc/tiger.county.png
dbdoc/tiger.state.png
dbdoc/tiger.place.png
dbdoc/tiger.zip_state.png
dbdoc/tiger.zip_state_loc.png
dbdoc/tiger.cousub.png
dbdoc/tiger.edges.png
dbdoc/tiger.addrfeat.png
dbdoc/tiger.faces.png
dbdoc/tiger.featnames.png
dbdoc/tiger.addr.png
dbdoc/tiger.zcta5.png
dbdoc/tiger.loader_platform.png
dbdoc/tiger.loader_variables.png
dbdoc/tiger.loader_lookuptables.png
dbdoc/tiger.tract.png
dbdoc/tiger.tabblock.png
dbdoc/tiger.bg.png
dbdoc/tiger.pagc_gaz.png
dbdoc/tiger.pagc_lex.png
dbdoc/tiger.pagc_rules.png
dbdoc/public.test_table.png
dbdoc/README.md
dbdoc/public.spatial_ref_sys.md
dbdoc/public.geography_columns.md
dbdoc/public.geometry_columns.md
dbdoc/public.raster_columns.md
dbdoc/public.raster_overviews.md
dbdoc/topology.topology.md
dbdoc/topology.layer.md
dbdoc/tiger.geocode_settings.md
dbdoc/tiger.geocode_settings_default.md
dbdoc/tiger.direction_lookup.md
dbdoc/tiger.secondary_unit_lookup.md
dbdoc/tiger.state_lookup.md
dbdoc/tiger.street_type_lookup.md
dbdoc/tiger.place_lookup.md
dbdoc/tiger.county_lookup.md
dbdoc/tiger.countysub_lookup.md
dbdoc/tiger.zip_lookup_all.md
dbdoc/tiger.zip_lookup_base.md
dbdoc/tiger.zip_lookup.md
dbdoc/tiger.county.md
dbdoc/tiger.state.md
dbdoc/tiger.place.md
dbdoc/tiger.zip_state.md
dbdoc/tiger.zip_state_loc.md
dbdoc/tiger.cousub.md
dbdoc/tiger.edges.md
dbdoc/tiger.addrfeat.md
dbdoc/tiger.faces.md
dbdoc/tiger.featnames.md
dbdoc/tiger.addr.md
dbdoc/tiger.zcta5.md
dbdoc/tiger.loader_platform.md
dbdoc/tiger.loader_variables.md
dbdoc/tiger.loader_lookuptables.md
dbdoc/tiger.tract.md
dbdoc/tiger.tabblock.md
dbdoc/tiger.bg.md
dbdoc/tiger.pagc_gaz.md
dbdoc/tiger.pagc_lex.md
dbdoc/tiger.pagc_rules.md
dbdoc/public.test_table.md

@k1LoW k1LoW self-assigned this Mar 13, 2020
@rafi
Copy link
Author

rafi commented Mar 14, 2020

Hi @k1LoW, thank you for trying to reproduce this so thoroughly. It indeed does work from a fresh installation. It might be the default, but you need to also create the topology extension:

CREATE EXTENSION IF NOT EXISTS postgis_topology

Nevertheless, I've found the culprit and difference, my current database has this search path:

foobar> SHOW search_path;
+---------------------------+
| search_path               |
|---------------------------|
| "$user", public, topology |
+---------------------------+

After changing the search_path for this database with:

ALTER ROLE dbuser in DATABASE foobar SET search_path TO "$user",public;

(You will need to restart database connections to apply changes)

It works!

Details 
$ tbls doc
docs/schema/schema.png
docs/schema/public.spatial_ref_sys.png
docs/schema/public.geography_columns.png
docs/schema/public.geometry_columns.png
docs/schema/public.raster_columns.png
docs/schema/public.raster_overviews.png
docs/schema/topology.topology.png
docs/schema/topology.layer.png
docs/schema/public.migration.png
docs/schema/public.user.png
docs/schema/public.user_role.png
docs/schema/public.users_homes.png
docs/schema/public.users_roles.png
docs/schema/public.users_tokens.png
docs/schema/public.subscriber.png
docs/schema/README.md
docs/schema/public.spatial_ref_sys.md
docs/schema/public.geography_columns.md
docs/schema/public.geometry_columns.md
docs/schema/public.raster_columns.md
docs/schema/public.raster_overviews.md
docs/schema/topology.topology.md
docs/schema/topology.layer.md
docs/schema/public.migration.md
docs/schema/public.user.md
docs/schema/public.user_role.md
docs/schema/public.users_homes.md
docs/schema/public.users_roles.md
docs/schema/public.users_tokens.md
docs/schema/public.subscriber.md

I'm not sure (1) how I ended up with this weird search_path, (2) if it's OK to permanently change it like this, and (3) why I don't have the tiger schema and tables. Weird.

But bottom-line it seems that tbls gets confused with non-default search_path settings.

Beside this "bug", maybe it would be a nice idea adding a search_path option to config, this will inadvertently allow people to exclude entire schemas they don't want tbls to include, like PostGIS metadata schemas, and change the default operational search_path just for tbls execution.

@k1LoW
Copy link
Owner

k1LoW commented Mar 14, 2020

@rafi

I was able to reproduce the bug thanks to your advice !

https://github.com/k1LoW/tbls/runs/507899665?check_suite_focus=true

@k1LoW k1LoW mentioned this issue Mar 14, 2020
Merged
@k1LoW
Copy link
Owner

k1LoW commented Mar 14, 2020

Released as v1.28.3.

@rafi
Copy link
Author

rafi commented Mar 14, 2020

Thanks @k1LoW, works great!
Looks like you released 1.29.0 yesterday, so 1.28.3 isn't sequential :)

@k1LoW
Copy link
Owner

k1LoW commented Mar 14, 2020

Oh... I mistake..

@k1LoW
Copy link
Owner

k1LoW commented Mar 14, 2020

@rafi

Released as v1.29.1.

Thank you for your comment !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@k1LoW k1LoW

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[PostgreSQL]Detect full table name using search_path and information_schema k1LoW/tbls
2 participants
@k1LoW @rafi