Add pg_database of system catalogs

[ghost]

Bug Report

Summary

The catalog pg_database stores information about the available databases. Databases are created with the CREATE DATABASE command. \l is an introspection command, which is, under the hood, a way to execute queries against the PostgreSQL system catalogs. So, I think that the pg_database may be added in noisepage.

In PostgreSQL,

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(4 rows)

postgres=# \c test;
You are now connected to database "test" as user "postgres".
test=# \c a;
FATAL:  database "a" does not exist
Previous connection kept

In noisepage,

noisepage=# \l 
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1), server 9.5devel)
noisepage=# \c test;   --  This error is not  suitable. Actually, the database namely test does not exist, which is not the same as PostgreSQL.
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
Previous connection kept
noisepage=# select * from pg_database;
ERROR:  relation "pg_database" does not exist

[apavlo]

Thanks for the issue. The pg_database table does exist (https://github.com/cmu-db/noisepage/blob/master/src/include/catalog/postgres/pg_database.h), but we are incorrectly not exposing it to the binder. That's why the SELECT query does not work.

This should be (hopefully) be an easy fix.

We are not going to support the psql shortcuts (\d, \l) any time soon because they require some additional features that we do not support right now.

[ghost]

Thanks for the issue. The pg_database table does exist (https://github.com/cmu-db/noisepage/blob/master/src/include/catalog/postgres/pg_database.h), but we are incorrectly not exposing it to the binder. That's why the SELECT query does not work.

This should be (hopefully) be an easy fix.

We are not going to support the psql shortcuts (\d, \l) any time soon because they require some additional features that we do not support right now.

Thank you, Professor Andy.

noisepage=# \set VERBOSITY verbose
noisepage=# ;
noisepage=# select * from pg_database;
ERROR:  42P01: relation "pg_database" does not exist
LOCATION:  ../src/binder/bind_node_visitor.cpp:825

[mbutrovich]

So I spent a few minutes last week looking at this. It yielded one PR that fixed some unrelated stuff in the Catalog. I managed to get SELECT * FROM pg_database through the binder, but it blows up in the optimizer trying to find stats for this table. pg_database is weird in that it's scoped above each database, so I'm not sure how to handle this scenario. We don't add a reference to pg_database in each database's respective pg_class, so to be consistent I guess we shouldn't have it in each pg_statistic, but that might lead to more special-case code. This is mostly just opening the floor to discussion.