information_schema.columns cannot be queried with `dolt_show_system_tables` set

[NathanGabrielson]

Doltgresql version 0.55.5, macOS

Issue with Prisma integration. With dolt_show_system_tables set to 1, any query to information_schema.columns will fail. You'll get an error that looks something like this, with a different table in the error message each time:

postgres=> select(dolt_show_system_tables);
 (dolt_show_system_tables)
---------------------------
                         1
(1 row)

postgres=> select * from information_schema.columns;
ERROR:  table not found: commits (errno 1146) (sqlstate HY000)
postgres=> select * from information_schema.columns;
ERROR:  table not found: remote_branches (errno 1146) (sqlstate HY000)

I've spent some time looking into this and the problem points seem to be as follows, but I'm a little lost to what the solution is:

In AllColumns in GMS here. This for loop will iterate over entries in the databases slice that each look something like the following:

Database one: Catalog name "postgres" with schema name "dolt"

Database two: Catalog name "postgres" with schema name "public".

Then in database.go here in Dolt, the results will cause an issue. this function produces a list of system tables that contains 2 copies of each, one with and one without the dolt (dolt_ namespace). So for instance there will be a dolt_commits and a commits, as well as a dolt_remote_branches and a remote_branches.

This then causes an issue when combined with the databases I listed above. When checking if each table does in fact exist, it gets to this check in Dolt.

The check for whether something is a system table returns true when either the name begins with dolt_ or if the pattern [schema_name]_[table_name] is a valid system table.

Considering the two databases I listed above, it works in the first case but not in the second. In the first case, if you have a table like commits, it will see that it does not begin with dolt_, but can see that [schema_name]_[table_name] matches the system table dolt_commits.

However, in the second database, neither pattern will match, since [schema_name]_[table_name] will be public_commits, which is not a existing system table. This then produces a table does not exist error after which the query fails.