Closing Out Collaborate 2014

oracle-collaborate-2014

Well. It’s the end to yet another Collaborate. It’s been fun. I’m thankful to IOUG for accepting my submission, “Optimizing Database Network Protocols,” which covered the basics of networking, the OSI model, Oracle/MySQL protocols, and their optimization. Likewise, after such a long hiatus, it was great to see a few of my Collaborate cronies, such as Craig Shallahamer, Arup NandaAlex Gorbachev, and many others, in addition to finally meeting Frits Hoogland and Maxym Kharchenko. I also dropped by OakTable World Las Vegas for a couple sessions, including a moving personal story from Kellyn Pot’Vin, a discussion with Graham Wood about the increasing difficulty in hiring strong C.S. graduates, and the market needs for a good, modern C programming book with Jonathan Gennick.

Data Access APIs–Part 1: Fun with UPI

First, I’d like to apologize to our good friend SQLLIB.  Those of you who have been working with the Oracle Database for some time will notice that, while it too is a common data access library, I’ve omitted it from this series of posts.

No, it’s not because of some personal vendetta against SQLLIB.  In fact, I like SQLLIB (primarily for internals-related reasons), but I’ve excluded it because it’s not a user-oriented data access library and as such, I don’t consider it a first class citizen.  And, for those of you who may find yourself asking, “what the hell is this SQLLIB thing Jonah’s going on about?”, it’s a library commonly used by the Oracle Precompiler suite, not something people develop directly against.  Though, if there’s enough interest, I may do a post on SQLLIB in the future.

Regardless, in this series of articles, I’m going to discuss and demonstrate the evolution of Oracle’s UPI and OCI data access APIs.  The articles are arranged as follows:

  • Article 1: The User Program Interface (UPI)
  • Article 2: The Oracle Call Interface Version 7 (OCI7)
  • Article 3: The Oracle Call Interface Version 8 (OCI8)

But, before we get too deep into UPI, we need to start at the beginning.

What is a Data Access Layer?
A data access layer is a programming interface which allows developers to write programs capable of interacting with a database.  So, whether you’re using ToadSQL DeveloperSQL*Plus, or a custom application, each relies on a data access layer to talk to the Oracle Database.  However, in this set of articles, we won’t be discussing standard data access APIs like ODBC or JDBC.  Instead, we’ll be taking a low-level look at proprietary Oracle APIs.

Oracle-supported APIs can be classified into two sets, public and internal-only.

Public Oracle Data Access APIs
These are APIs which Oracle has documented, supported, and wants customers to use.

Public APIs include:

  • Oracle Call Interface (OCI)
  • Oracle C++ Call Interface (OCCI)
  • Standard APIs (ODBC, JDBC, .NET, OLE DB, …)

Private, internal-only Oracle Data Access APIs
These are APIs which Oracle uses internally, does not provide external documentation for, and does not want customers using for various reasons.

Private APIs include:

  • User Program Interface (UPI)
  • SQL Library Runtime (SQLLIB)
  • Even older stuff than this…

In this article, we’ll focus on the User Program Interface, as it has been the core of Oracle’s data access for some time.

The User Program Interface
While UPI was once a popular internal-only API used within Oracle, it is in a constant state of being phased out in favor of the Oracle Call Interface (OCI).  Now, you may be asking, “why are you doing a blog on something that’s being phased out?”  Well, I’m doing it for three reasons: 1. Over the years, quite a few people have asked me about it, 2. UPI is still in use, and 3. I think it’s good for people to understand the evolutionary progress of how things got to where they are now.

So, how are we going to see some basic UPI in action?  Bring on the example!

A UPI-based Example Program
Because I strongly believe concepts are best demonstrated using an applied method, I’ve taken an old Oracle OCI demonstration program and turned it into a very basic interactive SQL utility that allows you to execute basic SQL queries.  The code kinda sucks because I spent very little time cleaning up the original, merged using a different style, don’t do great error checking, and could do a few things more correctly.  But, it’s still a basic working example.

However, before we get into the fun stuff, we have to deal with the legal issues.

The Inevitable Disclaimer
It must be noted that I haven’t (yet) worked for Oracle, had access to the source code, or can guarantee proper execution of this on your database version or platform.  The following is based on years of personal observation and research and while I’ve tested it on 8i, 9i, 10g, and 11g, I make no guarantees.  THE SOFTWARE AND EXAMPLES PROVIDED ON THIS PAGE ARE PROVIDED “AS IS” AND WITHOUT WARRANTY OF ANY KIND.  THE AUTHOR DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES.  THIS SOFTWARE IS PROVIDED FOR DEMONSTRATION PURPOSES ONLY AND IS NOT ENDORSED, VERIFIED, OR SUPPORTED BY ORACLE CORPORATION.  THIS SOFTWARE SHOULD NOT BE USED IN A PRODUCTION ENVIRONMENT UNDER ANY CIRCUMSTANCES.

OK, now we can get to the fun stuff :-)

Prerequisite Knowledge
This blog entry assumes that you have a basic understanding of the C programming language as well as a rough idea of data access concepts such as parsing, binding, defining, and execution.

OK, let’s start!

Including the Oracle C Data Types
Before we can use UPI, we must first include the Oracle platform type definitions, oratypes.h.  If you’ve done any OCI programming, you’ve no doubt run across this file.  This file, located in $ORACLE_HOME/rdbms/public, can easily be included using:

#include "oratypes.h"                      /* Oracle-declared Platform Types */

Next, we need the C complier to know something about UPI.

Including the UPI Definitions
In order for our program to make use of UPI, we must now include the UPI function prototypes and definitions, which are contained in a file called upidef.h.

While I’ve made several requests for a copy of Oracle’s official upidef.h, they have been true to their internal-only mission and have never graced me with a copy.  Consequently, it has taken me almost ten years to collect and research only a subset of the entire API.  However, looking (optimistically) toward the future, I’d like to avoid changing anything in my code should I eventually get a copy of the real upidef.h (hint, hint).  Accordingly, I’ve named my version of the definitions identically, and included the header file as follows:

#include "upidef.h"                        /* Jonah's Spiffy UPI Definitions */

Now that we’ve included the UPI definitions and function prototypes, we’re ready to start building our program.  The first step is to define an HDA.

Defining UPI’s Host Data Area Structure
Similar to the concept of opaque handles, UPI uses an old technique of having the developer allocate a block of data which is passed back-and-forth to UPI.  Internally, UPI maps offsets within this block to members of a C structure.  As for definition, we’re going to define an array containing 512 elements of type ub1 (unsigned char/uint8_t).  Depending on the platform and alignment-related issues, the HDA structure may also be allocated using an array of ub1, ub2, or ub4 with array-length changes being made accordingly.

static ub1      hda[512] = { 0 };                  /* Host Data Area for UPI */

Defining a Cursor Using UPI
In UPI, cursors are represented simply by a number.  Accordingly, we define a cursor as an unsigned word.

static uword    cnum;                                       /* Cursor Number */

Logon to Oracle
Next, we need to make a connection to the Oracle database.  UPI provides a couple different logon functions, but we’ll use upilog.  upilog will connect to the orcl database and create a session using scott/tiger.  During this process, the HDA structure will be updated to contain some information about this connection.

    if (upilog(&hda[0],
               (text *) "scott/tiger@orcl",
               -1, 0,
               -1, 0,
               -1, UPILFDEF, UPILMDEF))

Opening a Cursor
Now that we’re connected to the server, we need to get ready to process a statement.  However, before we can process a statement, we must open a cursor.  UPI provides the upiopn call for this.

    if (upiopn(&hda[0], &cnum, -1))

Parsing a SQL Statement
Now that the user has passed-in a query for processing, we need to parse the query.  UPI provides several functions for parsing, including the upipse and upiosd functions.  In our example, we’ll use upiosd, which parses the SQL statement and associates it with our cursor.  In our usage, we’re telling UPI to defer the actual parse [using (ub4) 1] until we call a describe/execution function.  Similarly, rather than specific version 6/7 mode, we’re telling the system to parse the statement in standard (default) mode [via (sword) 1].

        if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,
                   (ub4) 1, (sword) 1))

Describing the Select-List
As we’re allowing the user to enter dynamic SQL queries, we need to describe the select-list prior to defining the output variables and fetching the data.  UPI provides several functions for describing the select list, including the upidpr, upidqr, and upidsc functions.  In our example, we’ll rely on upidsc, which returns the maximum size, datatype, column name, length of the column name, maximum display size, precision of numeric items, scale of numerics, whether null values are allowed, and a couple other things.

            ret = upidsc(&hda[0], cnum, pos,
                         &pmxl[pos], &pvll[pos],
                         &perc[pos], &podt[pos], (text *)&coln[pos],
                         &coll[pos], &pmxdisl[pos],
                         &ppre[pos], &pscl[pos],
                         &pnul[pos]);

Defining Output Variables
The next step is to define the output variables for each select-list item in the query.  To define output variables, UPI provides the upidfc, upidfn, and upidfps functions.  In the example, we’ll use upidfn, which is a non-piecewise define for a select-list item by position.  In our call, we’re making sure to pass reference to our indicator value for this item as well.  Failure to do so will result in errors when attempting to fetch a column containing a NULL value.

            if (upidfn(&hda[0], cnum, (sword) i,
                       obuf[i], coll[i],
                       (sword) SQLT_STR, (b2 *) &oind[i],
                       (text *) 0, (size_t) 0,
                       (ub2 *) 0, (ub2 *) 0,
                       (sword) 0))

Executing the Query
Now that we’ve defined our output variables, we’re ready to execute the query.  The order of describe/define/execution can be different in different situations.  Regardless, UPI provides several calls for execution including, upiefn, upiexe, and upiexn.  In our example, we’ll use upiexe.

    if (upiexe(&hda[0], cnum))

Fetching Data
So, we’ve defined our output variables and executed the statement.  If all has gone well so-far, we can start fetching data.  UPI provides several different calls including upifch and upifcn.  We could also use upiefn (execute+fetch), but are instead going to use the simplest version, upifch.  The upifch function performs a row-at-a-time fetch on our cursor.

        while (upifch(&hda[0], cnum) == 0)

Conclusion
This has been a brief example of writing a simple application using Oracle’s UPI data access API.  In the next two articles, you’ll see how this same application can be improved and made more descriptive using OCI7 and the current version of OCI, OCI8.

Full Program Listing
The following is the entire source code to the extremely simple interactive SQL client built using the User Program Interface.  Given that Oracle still considers UPI proprietary, I will not be attaching nor sending my UPI definitions header file, upidef.h.  If you’d like to actually run the program below, you can fairly easily recreate the UPI function prototypes based on the data types and argument ordering provided.

/* ========================================================================= */
/* INTERACTIVE SQL EXAMPLE                                                   */
/* Oracle User Program Interface (UPI)                                       */
/*                                                                           */
/*                                  Jonah H. Harris <jonah.harris@gmail.com> */
/*                                          http://www.oracle-internals.com/ */
/* ========================================================================= */

/* Standard Headers */
#include <stdio.h>                                /* Standard C Input/Output */
#include <stdlib.h>                                    /* Standard C Library */

/* Oracle-specific Headers */
#include <oratypes.h>                      /* Oracle-declared Platform Types */
#include "upidef.h"                                    /* My UPI Definitions */

/* ========================================================================= */
/* << DEFINITIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

#define MAXATTR             1024  /* Max number of attributes in select-list */

/* ========================================================================= */
/* << STATIC VARIABLES >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

static ub1      hda[512] = { 0 };                  /* Host Data Area for UPI */
static uword    cnum;                                       /* Cursor Number */

/* ========================================================================= */
/* << FORWARD DECLARATIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

static void upi_error (void);

/* ========================================================================= */
/* << PUBLIC FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

int
main (int argc, char **argv)
{
    char    sql_statement[256];
    sword   i, pos;
    sword   ret;
    ub1     podt[MAXATTR], ppre[MAXATTR], pnul[MAXATTR];
    ub2     pmxl[MAXATTR], coll[MAXATTR],
            pmxdisl[MAXATTR], pvll[MAXATTR];
    sb1     pscl[MAXATTR];
    sb2     perc[MAXATTR];
    text    coln[MAXATTR][31];
    text   *obuf[MAXATTR];
    sb2     oind[MAXATTR];

    /* Logon */
    if (upilog(&hda[0],
               (text *) "scott/tiger@orcl",
               -1, 0,
               -1, 0,
               -1, UPILFDEF, UPILMDEF))
    {
        printf("Cannot connect as scott. Exiting...\n");
        exit(1);
    }

    /* Open a cursor */
    if (upiopn(&hda[0], &cnum, -1))
    {
        printf("couldn't open cursor!\n");
        upi_error();
        exit(1);
    }

    /* Main loop */
    for (;;)
    {
        ub4 rnum = 0;   /* A generic record number counter */

        printf("\nEnter a query or \"exit\"> ");
        fgets(sql_statement, sizeof(sql_statement), stdin);

        /* If user typed, "exit", bail! */
        if (strncmp(sql_statement, "exit", 4) == 0)
            break;

        /* Parse the statement */
        if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,
                   (ub4) 1, (sword) 1))
        {
            upi_error();
            continue;
        }

        /* Iterate over each attribute in the select-list */
        for (pos = 1; pos < MAXATTR; pos++)
        {
            coll[pos] = sizeof(coln[pos]);

            /* Describe the select-list item */
            ret = upidsc(&hda[0], cnum, pos,
                         &pmxl[pos], &pvll[pos],
                         &perc[pos], &podt[pos], (text *)&coln[pos],
                         &coll[pos], &pmxdisl[pos],
                         &ppre[pos], &pscl[pos],
                         &pnul[pos]);

            if (coll[pos] > sizeof(coln[pos]))
                coll[pos] = sizeof(coln[pos]);

            /* Make sure this string is null-terminated */
            coln[pos][coll[pos]] = '\0';

            if (ret)
            {
                if (ret == 1007)
                    break;
                upi_error();
                continue;
            }
        }

        /*
         * Print out the total count and the names of the select-list
         * items, column sizes, and datatype codes.
         */
        pos--;
        printf("\nThere were %d select-list items.\n", pos);
        printf("Item name                     Length   Datatype\n");
        printf("\n");
        for (i = 1; i <= pos; i++)
        {
            printf("%*.*s", coll[i], coll[i], coln[i]);
            printf("%*c", 31 - coll[i], ' ');
            printf("%6d   %8d\n", coll[i], podt[i]);

            /* Allocate the output */
            obuf[i] = (text *) malloc(coll[i]+1);
            if (obuf[i] == NULL)
            {
                fprintf(stderr, "Failed to allocate %u bytes!\n",
                    coll[i]+1);
                exit(EXIT_FAILURE);
            }

            /* Define the output parameter */
            if (upidfn(&hda[0], cnum, (sword) i,
                       obuf[i], coll[i],
                       (sword) SQLT_STR, (b2 *) &oind[i],
                       (text *) 0, (size_t) 0,
                       (ub2 *) 0, (ub2 *) 0,
                       (sword) 0))
            {
                upi_error();
                break;
            }
        }

        /* Execute */
        if (upiexe(&hda[0], cnum))
        {
            upi_error();
            continue;
        }

        /* Fetch & Display */
        while (upifch(&hda[0], cnum) == 0)
        {
            printf("\n-- RECORD %04u -------------------------\n", ++rnum);
            for (i = 1; i <= pos; i++)
            {
                printf("%s", coln[i]);
                printf("%*c ", 31 - coll[i], ':');
                printf("%s\n", (oind[i]) ? "NULL" : obuf[i]);
            }
        }

        /* Display the summary */
        printf("\n%u record%s returned.\n", rnum, (rnum == 1) ? "" : "s");

        /* Cleanup */
        for (i = 1; i <= pos; i++)
            if (obuf[i] != NULL)
                free(obuf[i]);

    } /* for (;;) */

    /* Close the cursor */
    upicls(&hda[0], cnum);

    /* Logoff */
    upilof(&hda[0]);

    /* Bail! */
    exit(EXIT_SUCCESS);

} /* main() */

/* ========================================================================= */
/* << PRIVATE FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */

static void
upi_error (void)
{
    text msg[512] = { 0 };

    fprintf(stderr, "\nOracle ERROR\n");
    upigem(&hda[0], (text *) &msg);
    fprintf(stderr, "%s", msg);
    fflush(stderr);

} /* upi_error() */

UPI is fun! :)

NEXTGRES Gateway: MySQL Emulator for Oracle

So, a few people have asked me what NEXTGRES Gateway is.  My short answer, the ultimate database compatibility server.

Sorry if this blog entry sounds very marketing-oriented, but I’ve been working on this personal project non-stop for the last 8 months and am really excited about it.

NEXTGRES Gateway in a nutshell:

  • Designed to assist in database/application migration
  • Written in C
  • Supports TCP and UDP sockets
  • Runs in multi-threaded or multi-process mode (depending on configuration)
  • Runs on Windows, UNIX, Linux
  • Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) server-side wire-level protocols
  • Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) client-side wire-level protocols, as well as ODBC
  • Supports pass-through SQL or syntax translation between MySQL, PostgreSQL, Oracle, and SQL Server
  • Supports native procedural language execution via translation of PL/SQL and T-SQL to C (no C complier is needed on your system though, thanks to TCC)
  • Supports data type conversion to/from MySQL, PostgreSQL, Oracle, and SQL Server/Sybase
  • Supports local statement/result-set caching

In this entry, I’m going to focus on MySQL server emulation.

MySQL Server Emulation
Now that I’ve fully completed the MySQL server emulation component, something I discussed with the Oracle Data Access guys at OpenWorld, here’s a couple examples for you.

Say you have an application that runs on MySQL and you’d like to migrate it to Postgres, but don’t want to do any code changes.  Well, if you’re using fairly standard ODBC/JDBC, you don’t have much to worry about.  But what if it’s a PHP application using the mysql_* calls, or an application using the MySQL client libraries, or a third-party application you don’t have the code for?  The answer is to use NEXTGRES Gateway.

NEXTGRES Gateway allows you migrate your data to another database transparently to the application.  The general process for using NEXTGRES Gateway is as follows:

  • Migrate Data (MySQL to Postgres/Oracle/SQL Server)
  • Shutdown MySQL
  • Start NEXTGRES Gateway in MySQL Emulation mode and point it to the Postgres/Oracle/SQL Server data
  • Test
  • You’re done!

Unlike other databases which claim to be compatible, NEXTGRES Gateway allows you to migrate an application to another database server with no application changes.

In the following example, I’m using the native MySQL client to connect to a PostgreSQL 8.3 database.  It’s important to note that no changes have been made to the MySQL client, it’s just connecting to NEXTGRES Gateway which is emulating the MySQL server by performing SQL syntax and protocol translation to Postgres.

jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 pgdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from pg_tables;
+-------+
| count |
+-------+
| 51    |
+-------+
1 row in set (0.01 sec)

mysql> select tablename from pg_tables limit 5;
+-------------------------+
| tablename               |
+-------------------------+
| sql_features            |
| sql_implementation_info |
| pg_statistic            |
| sql_languages           |
| sql_packages            |
+-------------------------+
5 rows in set (0.01 sec)

That’s cool and all, but say we want to move our MySQL application to Oracle:

jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 oradb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from all_tables;
+----------+
| COUNT(*) |
+----------+
| 108      |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from user_tables;
+----------+
| COUNT(*) |
+----------+
| 9        |
+----------+
1 row in set (0.01 sec)

mysql> select user from dual;
+-----------+
| USER      |
+-----------+
| AUTOGRAPH |
+-----------+
1 row in set (0.01 sec)

mysql> select table_name from all_tables limit 5;
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| DUAL                  |
| SYSTEM_PRIVILEGE_MAP  |
| TABLE_PRIVILEGE_MAP   |
| STMT_AUDIT_OPTION_MAP |
| AUDIT_ACTIONS         |
+-----------------------+
5 rows in set (0.02 sec)

For those that didn’t catch it, NEXTGRES Gateway performed a simple SQL translation from MySQL to Oracle syntax on:

SELECT table_name FROM all_tables LIMIT 5;

to

SELECT table_name FROM all_tables WHERE ROWNUM < 6;

If you want to see more, I’ll be happy to demonstrate MySQL, Postgres, and Oracle emulation at theSoutheastern Oracle Users Conference, February 24 & 25 in Charlotte, North Carolina.  I’ll also be presenting my session, “Listening In: Passive Capture and Analysis of Oracle Network Traffic”.  This session is designed to help you diagnose issues with and optimize applications for, the Oracle network protocol.

Added on 2010-01-30 per Baron’s question:

jharris@jharris-desktop$ mysql -A -u testuser1 -h 127.0.0.1 postgres
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+------------------------------------------------------+
| Tables_in_postgres                                   |
+------------------------------------------------------+
| active_locks                                         |
| active_queries                                       |
| information_schema.administrable_role_authorizations |
| information_schema.applicable_roles                  |
| information_schema.attributes                        |
| information_schema.check_constraint_routine_usage    |
| information_schema.check_constraints                 |
| information_schema.column_domain_usage               |
| information_schema.column_privileges                 |
| information_schema.column_udt_usage                  |
| information_schema.columns                           |
| information_schema.constraint_column_usage           |
| information_schema.constraint_table_usage            |
| information_schema.data_type_privileges              |
| information_schema.domain_constraints                |
| information_schema.domain_udt_usage                  |
| information_schema.domains                           |
| information_schema.element_types                     |
| information_schema.enabled_roles                     |
| information_schema.information_schema_catalog_name   |
| information_schema.key_column_usage                  |
| information_schema.parameters                        |
| information_schema.referential_constraints           |
| information_schema.role_column_grants                |
| information_schema.role_routine_grants               |
| information_schema.role_table_grants                 |
| information_schema.role_usage_grants                 |
| information_schema.routine_privileges                |
| information_schema.routines                          |
| information_schema.schemata                          |
| information_schema.sequences                         |
| information_schema.sql_features                      |
| information_schema.sql_implementation_info           |
| information_schema.sql_languages                     |
| information_schema.sql_packages                      |
| information_schema.sql_parts                         |
| information_schema.sql_sizing                        |
| information_schema.sql_sizing_profiles               |
| information_schema.table_constraints                 |
| information_schema.table_privileges                  |
| information_schema.tables                            |
| information_schema.triggered_update_columns          |
| information_schema.triggers                          |
| information_schema.usage_privileges                  |
| information_schema.view_column_usage                 |
| information_schema.view_routine_usage                |
| information_schema.view_table_usage                  |
| information_schema.views                             |
| jhhdemotbl                                           |
| jhhtest                                              |
| pg_aggregate                                         |
| pg_am                                                |
| pg_amop                                              |
| pg_amproc                                            |
| pg_attrdef                                           |
| pg_attribute                                         |
| pg_auth_members                                      |
| pg_authid                                            |
| pg_autovacuum                                        |
| pg_cast                                              |
| pg_class                                             |
| pg_constraint                                        |
| pg_conversion                                        |
| pg_cursors                                           |
| pg_database                                          |
| pg_depend                                            |
| pg_description                                       |
| pg_enum                                              |
| pg_freespacemap_pages                                |
| pg_freespacemap_relations                            |
| pg_group                                             |
| pg_index                                             |
| pg_indexes                                           |
| pg_inherits                                          |
| pg_language                                          |
| pg_largeobject                                       |
| pg_listener                                          |
| pg_locks                                             |
| pg_namespace                                         |
| pg_opclass                                           |
| pg_operator                                          |
| pg_opfamily                                          |
| pg_pltemplate                                        |
| pg_prepared_statements                               |
| pg_prepared_xacts                                    |
| pg_proc                                              |
| pg_rewrite                                           |
| pg_roles                                             |
| pg_rules                                             |
| pg_settings                                          |
| pg_shadow                                            |
| pg_shdepend                                          |
| pg_shdescription                                     |
| pg_stat_activity                                     |
| pg_stat_all_indexes                                  |
| pg_stat_all_tables                                   |
| pg_stat_bgwriter                                     |
| pg_stat_database                                     |
| pg_stat_sys_indexes                                  |
| pg_stat_sys_tables                                   |
| pg_stat_user_indexes                                 |
| pg_stat_user_tables                                  |
| pg_statio_all_indexes                                |
| pg_statio_all_sequences                              |
| pg_statio_all_tables                                 |
| pg_statio_sys_indexes                                |
| pg_statio_sys_sequences                              |
| pg_statio_sys_tables                                 |
| pg_statio_user_indexes                               |
| pg_statio_user_sequences                             |
| pg_statio_user_tables                                |
| pg_statistic                                         |
| pg_stats                                             |
| pg_tables                                            |
| pg_tablespace                                        |
| pg_timezone_abbrevs                                  |
| pg_timezone_names                                    |
| pg_trigger                                           |
| pg_ts_config                                         |
| pg_ts_config_map                                     |
| pg_ts_dict                                           |
| pg_ts_parser                                         |
| pg_ts_template                                       |
| pg_type                                              |
| pg_user                                              |
| pg_views                                             |
| plproxy.cluster_partitions                           |
| plproxy.clusters                                     |
| testtbl                                              |
| utility.index_byte_sizes                             |
| utility.table_byte_sizes                             |
| utility.user_table_sizes                             |
+------------------------------------------------------+
132 rows in set (0.03 sec)

mysql> show databases;
+-----------+
| Database  |
+-----------+
| template1 |
| template0 |
| postgres  |
+-----------+
3 rows in set (0.01 sec)

More info to come!