Visidata: How to access non-public schema in Postgres DB?

[p3k]

Small description

I maintain a Postgres database having a non-public schema, i.e. I either need to explicitely provide the schema name or set the search_path:

# either one of these works
select * from foo.bar;

set search_path = foo;
select * from bar;

alter database foobar set search_path to foo; # this makes it persistent
select * from bar;

Trying to connect to this database with Visidata works but returns an empty set of tables – even with the persistent search_path using alter database:

$ vd postgres://user:pw@localhost/foobar
 table_name         ‖ ncols             #| nrows             #‖

Expected result

Something along the lines of this output:

 table_name   ‖ ncols  #| nrows  #‖
 tag_hub      ‖       5 |        …‖
 log          ‖       7 |        …‖
 metadata     ‖       6 |        …‖
 skin         ‖       9 |        …‖
 layout       ‖       8 |        …‖
 content      ‖      16 |        …‖
 choice       ‖       5 |        …‖
 site         ‖      10 |        …‖
 poll         ‖       9 |        …‖
 account      ‖       7 |        …‖
 vote         ‖       7 |        …‖
 membership   ‖       8 |        …‖
 image        ‖      10 |        …‖
 file         ‖      12 |        …‖
 tag          ‖       4 |        …‖

I achieved this output by temporarily changing line 61 of loaders/postgres.py to the desired schema name:

64c64
<         qstr = "SELECT table_name, COUNT(column_name) AS ncols FROM information_schema.columns WHERE table_schema = 'public' GROUP BY table_name"
---
>         qstr = "SELECT table_name, COUNT(column_name) AS ncols FROM information_schema.columns WHERE table_schema = 'antville' GROUP BY table_name"

Although this shows the correct list of tables, it still wouldn’t seem sufficient to somehow inject the correct schema name dynamically because of the missing nrows values (…) – entering one of the rows shows an empty output.

Actual result with screenshot

Additional context

→ vd -v
saul.pw/VisiData v1.0

[p3k]

Also tested this with v2 and here I generally get a full-blown error:

Traceback (most recent call last):
  File "/home/tobi/.virtualenvs/visidata2/bin/vd", line 6, in <module>
    visidata.main.vd_cli()
  File "/home/tobi/.virtualenvs/visidata2/lib/python3.6/site-packages/visidata/main.py", line 215, in vd_cli
    rc = main_vd()
  File "/home/tobi/.virtualenvs/visidata2/lib/python3.6/site-packages/visidata/main.py", line 139, in main_vd
    vs = openSource(src)
  File "/home/tobi/.virtualenvs/visidata2/lib/python3.6/site-packages/visidata/vdobj.py", line 37, in _vdfunc
    return func(visidata.vd, *args, **kwargs)
  File "/home/tobi/.virtualenvs/visidata2/lib/python3.6/site-packages/visidata/data.py", line 193, in openSource
    return vd.openPath(Path(p), filetype=filetype)  # convert to Path and recurse
  File "/home/tobi/.virtualenvs/visidata2/lib/python3.6/site-packages/visidata/data.py", line 158, in openPath
    return getGlobals()[openfunc](p, filetype=filetype)
  File "/home/tobi/.virtualenvs/visidata2/lib/python3.6/site-packages/visidata/loaders/postgres.py", line 21, in openurl_postgres
    dbname = url.path[1:]
  File "/home/tobi/.virtualenvs/visidata2/lib/python3.6/site-packages/visidata/path.py", line 77, in __getattr__
    r = getattr(self._path, k)
AttributeError: 'PosixPath' object has no attribute 'path'

[anjakefala]

Hi @p3k!

Thanks for submitting a bug report. ^^

Are you open to trying to approach the postgres loader, if I help explain the PosixPath issue?

The Postgres loader currently does not have a champion, and I think the likelihood is low that either @saulpw or I will have the space to prioritise it before we release v2.0. If you want to take it on, that would be welcome!

[p3k]

I gladly will take a look and evaluate if I feel capable of “taking it on” :smile_cat:

Where should I start my exploration?

[anjakefala]

The postgres loader is located in visidata/loaders/postgres.py. We have developed a home-grown Path that is in visidata/path.py. We developed our own in order to handle urls.

All this is doing: return getGlobals()[openfunc](p, filetype=filetype) is basically calling the open_postgres in visidata/loaders/postgres.py. It is determining which loader to call based on the assessed filetype of the file it is loading.

You can use vd.status() to print to the VisiData status the contents of any variable you are investigating (I would look at url). Ctrl+P opens the Status Sheet and can be used to scroll through the status history.

We are on #visidata on Freenode, and feel free to come there if you have any questions!

[anjakefala]

Thanks for being game to take a look! :blush:

[p3k]

Just to be sure I am looking into the right branch: is it still v2.-3.0 as stated on the Visidata website? Or should I rather inspect develop?

[p3k]

I am able to fix the error and open the PG database adding a urlparse() and and import string statement to loaders/postgres.py in the v2.-3.0 branch…

…which exactly is done in the develop branch on lines 41 and 58 :cat:

(However, the dev branch complains about a missing plugin module, so this seems to open another can of worms…)

[anjakefala]

The develop branch is what you want to look at! I guess I fixed that bug. No wonder it looked familiar.

Can you give me the develop branch stacktrace?

[anjakefala]

I guess this is the error you are noticing: https://github.com/saulpw/visidata/issues/614

Okay. Once we fix that error, I will give you a poke to re-rest postgres! =) Thanks for taking a look.

[anjakefala]

Though, if you make the directory mentioned, then you can see if your postgres issue is addressed sooner! =)

[p3k]

Right, after creating the directory .visidata/plugins everything works fine.

Regarding my original issue: any suggestions how this could be approached? Could it be solved with a configuration setting? Added to the URL somehow? Or is there a generic method to give Postgres the correct schema name?

[anjakefala]

You have a choice between adding it to the url somehow, or creating a configuration option.

I would create an option, and have it default to its current default behaviour. We have an option() function that you can take a look for adding that. =)

Or is there a generic method to give Postgres the correct schema name?

I do not know the answer to this question!

[saulpw]

Seems like this should be an option, postgres_schema, that defaults to public. Would this work for you, @p3k?

[p3k]

Yes I think an option would probably be the easiest way to go. What do you think, maybe along the lines of this solution?

conn = psycopg2.connect(
    dbname=conn_config['dbname'],
    user=conn_config['user'],
    host=conn_config['host'],
    password=conn_config['password'],
    port=conn_config['port'],
    options=f'-c search_path={schema}' ### Here we go!
    #options=f'--search_path={schema}' ### Considered in a follow-up comment
)

[p3k]

Ah, I assume we also need this option in line 85, the one I hard-coded for testing, as well as line 87:

def reload(self):
    qstr = '''
        SELECT relname table_name, column_count.ncols, reltuples::bigint est_nrows
            FROM pg_class, pg_namespace, (
                SELECT table_name, COUNT(column_name) AS ncols FROM information_schema.COLUMNS WHERE table_schema = 'public' GROUP BY table_name
                ) AS column_count
            WHERE  pg_class.relnamespace = pg_namespace.oid AND pg_namespace.nspname = 'public' AND column_count.table_name = relname;
    '''

[saulpw]

Yes, we'd need it in both places. Is adding the search_path option still necessary if we use the option in both of those SQL queries?

[p3k]

I am not sure… initial display of the tables works without search_path but I don’t know if it might be necessary for Visidata / psycopg2 to retrieve further data like table rows etc.

[p3k]

Basically I got it working and can run vd like this now:

 vd --postgres-schema=foo postgres://user:pw@localhost/foobar

:woman_dancing:

Time for a PR? Or do you prefer a patch file?

[saulpw]

PR please! We'll review and when it seems good, we can merge with the click of a button.

[p3k]

Cool! It’s already past midnight here, so I will take a break and review everything again tomorrow, then publish the PR. Thanks for your assistance @anjakefala and @saulpw :sleepy:

[p3k]

Just for the record: PR was filed and already merged, closing this issue.

It was a pleasure to do this little task, thanks to the very helpful maintainers and the IMO very well written code.