quarta-feira, 20 de maio de 2009

Echoing hidden psql statements

One of the greatest advantages of a Database Management System is its embedded data dictionary, also called metadata or system catalog [1]. On PostgreSQL DBMS it is not different, and its metadata are widely used by database handling tools.

A great and yet simple tool available to this relational database is psql [2], a command-line, bash-enabled application that permits issuing SQL instructions to PostgreSQL server instances.

Even when sending a simple command such as "\d" on psql, there might be one or several SQL instructions sent internally to that given instance. Knowing exactly what is being sent could help a database administrator or even a simple developer on common and ordinary daily tasks.

So, how could we know those SQL instructions? Simpler than expected!

There is an environment variable on psql called ECHO_HIDDEN, which once set echoes all hidden instructions, as its own name says. In order to enable that, simply execute the following command:


Then, try to issue some internal commands like "\l" (typing "\?" shows the entire list of options). Here is the output after enabling ECHO_HIDDEN:

# \l
********* QUERY **********
SELECT d.datname as "Name",
r.rolname as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_roles r ON d.datdba = r.oid

List of databases
Name | Owner | Encoding
auction5 | sa_auction5 | UTF8
postgres | postgres | UTF8
rodrigo | rodrigo | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(5 rows)

Note the contents enclosed by "QUERY" and "***". Here are the internal SQL instructions respectively performed.

When you're done, you could deactivate this variable by executing the command below:

# \unset ECHO_HIDDEN

That's it! :D

[1] PostgreSQL 8.3.8 Documentation - System Catalogs
[2] psql - PostgreSQL interactive terminal