pg_admin startup queries

[mjgarton]

Pg admin does not currently connect ( https://www.pgadmin.org/ )

The first issue is that select version() currently returns a column named version() but pg_admin expects it to be called version because that's what postgresql does.

[mjgarton]

After applying #179 the next failing query is :

SELECT CASE
    WHEN (SELECT count(extname) FROM pg_catalog.pg_extension WHERE extname='bdr') > 0
    THEN 'pgd'                                         
    WHEN (SELECT COUNT(*) FROM pg_replication_slots) > 0
    THEN 'log'                                                                       
    ELSE NULL                                                                        
END as type;

[sunng87]

postgres uses this subquery aggressively, but datafusion doesn't allow this because the subqury may return multiple results. I'm also running this issue with psql compatibility, I had a rewrite rule that remove subquery completely: https://github.com/datafusion-contrib/datafusion-postgres/blob/master/datafusion-postgres/src/sql/rules.rs#L734

Can we remove the subquery in this case as well?

[mjgarton]

Another failing pg_admin query:

SELECT
    db.oid as did, db.datname, db.datallowconn,
    pg_encoding_to_char(db.encoding) AS serverencoding,
    has_database_privilege(db.oid, 'CREATE') as cancreate,
    datistemplate
FROM
    pg_catalog.pg_database db
WHERE db.datname = current_database()

This fails because the has_database_privilege is missing.

[mjgarton]

postgres uses this subquery aggressively, but datafusion doesn't allow this because the subqury may return multiple results. I'm also running this issue with psql compatibility, I had a rewrite rule that remove subquery completely: https://github.com/datafusion-contrib/datafusion-postgres/blob/master/datafusion-postgres/src/sql/rules.rs#L734

Can we remove the subquery in this case as well?

I'm not sure I understand, but I can get the query containing pg_replication_slots to work simply by adding support for that view to the catalog. I should have a PR for that soon.

[mjgarton]

See #181 which fixes one of the pg_admin query failures.

[mjgarton]

See #182 which fixes the query that relies on the has_database_privilege function.

[mjgarton]

Next failing query:

        SELECT
             gss_authenticated, encrypted
        FROM
            pg_catalog.pg_stat_gssapi
        WHERE pid = pg_backend_pid()

because pg_stat_gssapi is not found.

[mjgarton]

I'm not sure about the best approach for implementing pg_stat_gssapi and pg_backend_pid() to get this query to work. One approach would be to actually track connections (backends in postgresql terminology), keeping track of the pid of each and creating entries in pg_stat_gssapi as connections come and go. This is what I would describe as a complete implementation.

A simpler approach would be to always show a single row in pg_stat_gssapi with a hard coded pid, and implement pg_backend_pid() to always return the same hard coded pid.

Maybe there are other approaches I can't think of.

Any thoughts @sunng87 ?

[sunng87]

Yes, I suggest we just return hard coded pid for both functions. The backend pid function is expected to return backend child process id for current connection, it will be the id client to kill query. But since our rust based application uses a completely different concurrency model with vanilla postgres, so I don't think these functions make much sense in our environment, at least for now.

[mjgarton]

More fixes #185

[mjgarton]

Next failing query:

SELECT
            roles.oid as id, roles.rolname as name,
            roles.rolsuper as is_superuser,
            CASE WHEN roles.rolsuper THEN true ELSE roles.rolcreaterole END as
            can_create_role,
            CASE WHEN roles.rolsuper THEN true
            ELSE roles.rolcreatedb END as can_create_db,
            CASE WHEN 'pg_signal_backend'=ANY(ARRAY(WITH RECURSIVE cte AS (
            SELECT pg_roles.oid,pg_roles.rolname FROM pg_roles
                WHERE pg_roles.oid = roles.oid
            UNION ALL
            SELECT m.roleid,pgr.rolname FROM cte cte_1
                JOIN pg_auth_members m ON m.member = cte_1.oid
                JOIN pg_roles pgr ON pgr.oid = m.roleid)
            SELECT rolname  FROM cte)) THEN True
            ELSE False END as can_signal_backend
        FROM
            pg_catalog.pg_roles as roles
        WHERE
            rolname = current_user;

[mjgarton]

The above query fails for multiple reasons, the first of which is that pg_roles does not exist.