Materialize: Listing databases with "\l" throws Parse error when running against fresh materialized install

[theronic]

On OSX, terminal 1:

brew install MaterializeInc/materialize/materialized
materialized

Terminal 2:

psql -h localhost -p 6875 materialize                                                                                  ✔  10150  13:00:00
psql (10.4, server 9.5.0)
Type "help" for help.

materialize=> \l
ERROR:  Parse error:
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
                                             ^^^^
Expected ), found: '\n'
materialize=>

Furthermore, if I replace E'\n' with just '\n', I get the following:

materialize=> select * from pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges";
ERROR:  Parse error:
select * from pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges";
                                                    ^^^^
Expected ), found: '\n'
materialize=> select * from pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges";
ERROR:  qualified function names are not supported

[umanwizard]

Confirmed, thanks for the report.

[umanwizard]

This is one instance of a more general issue: (currently) incomplete support in Materialize for certain Postgres-specific queries generated by psql.

To get unblocked in the immediate term: show databases will let you list the user databases in Materialize.

[theronic]

Thanks. Curiously, \c works:

[umanwizard]

That is expected, since the \c meta-command does not cause the client to send any SQL to the server, but instead just causes psql to print some of its internal state.

[umanwizard]

@theronic At the company, many of us are using https://github.com/materializeinc/mzcli instead of psql, which you might find does a better job of avoiding sending postgres-specific stuff (but @quodlibetor would know for sure).

Anyway, I recommend trying it -- it has nice features like syntax highlighting.

[rjnn]

@awang we should solve this before going further down pg_catalog for more complicated things like Hasura. This gives a good first impression to users, and at this point we should just burn down the various queries like \l, ld since each emits a fixed pg_catalog query

[mjibson]

In addition to the pg_catalog additions, this also demonstrates a need to improve literal handling to support tokens like E'\n'.

[JLDLaughlin]

The query issued by psql when \l is run:

SELECT d.datname                                  AS "Name", 
       pg_catalog.Pg_get_userbyid(d.datdba)       AS "Owner", 
       pg_catalog.Pg_encoding_to_char(d.encoding) AS "Encoding", 
       d.datcollate                               AS "Collate", 
       d.datctype                                 AS "Ctype", 
       pg_catalog.Array_to_string(d.datacl, ' ') 
       AS "Access privileges" 
FROM   pg_catalog.pg_database AS d 
ORDER  BY 1 

[JLDLaughlin]

It looks like supporting \l will require supporting:

• [x] Qualified function names: https://github.com/MaterializeInc/materialize/issues/4258
• [x] pg_catalog.Pg_get_userbyid(): https://github.com/MaterializeInc/materialize/issues/4272
• [x] pg_catalog.Pg_encoding_to_char(): https://github.com/MaterializeInc/materialize/issues/4273
• [x] pg_catalog.Array_to_string: https://github.com/MaterializeInc/materialize/issues/4274
• [x] pg_catalog.pg_database: https://github.com/MaterializeInc/materialize/issues/4275

[JLDLaughlin]

On second thought, qualified function names aren't necessary to complete this issue!

[quodlibetor]

theronic At the company, many of us are using https://github.com/materializeinc/mzcli instead of psql, which you might find does a better job of avoiding sending postgres-specific stuff

mzcli is unlikely to help in this case: it will still send this specific command if you request it by typing a literal \l, but it is easier for us to intercept and send something that materialized can handle. Currently we don't intercept any of the backslash commands, as we've always intended to support pg_catalog and no one has asked specifically, yet.

That said, the fact that mzcli has autocomplete that generally works means that exploring databases is less reliant on psql magic commands (in my experience). And any bugs in it are much easier for us to fix than in psql.

[benesch]

In addition to the pg_catalog additions, this also demonstrates a need to improve literal handling to support tokens like E'\n'.

Good news on this front: support for E string literals was added in v0.3.1, which shipped after this issue was filed!

[JLDLaughlin]

We now support \l! \l works currently off main, and will be included in the next release.