Using the SQL Relay drop-in replacement library for PostgreSQL


What is a drop-in replacement library?

The SQL Relay drop-in replacement library for PostgreSQL clients allows you to run an application that was written using the native PostgreSQL client API against SQL Relay without rewriting the application.

The drop-in replacement library is a shared object library that implements PostgreSQL client library functions as calls to similar SQL Relay API functions which then map the results back into native PostgreSQL client data structures.

There are a variety of reasons that you might want to use the SQL Relay drop-in replacement library for PostgreSQL clients. An application written for PostgreSQL could, for instance, be made to run queries against an Oracle or Microsoft SQL Server database without modification. Or you could simply put SQL Relay between an application and the PostgreSQL database that it normally runs against to take advantage of SQL Relay's persistence, load balancing or throttling facilities.


Using the drop-in replacement library with command-line programs

You can use the SQL Relay drop-in replacement library for PostgreSQL clients by loading the drop-in library and running your program.

The parameters that would ordinarily indicate which host, port, socket, username and password to use to connect to PostgreSQL will be used as parameters to connect to SQL Relay. The parameter that would ordinarily indicate which database to connect to will be ignored. Instances of SQL Relay are configured to connect to a single database, and that database will be used by the client program.

In the following example, we're running the "psql" program against an instance of SQL Relay running on the localhost, port 8009 against an Oracle database. This instance of SQL Relay is configured with a username/password of oracle8user/oracle8pass.

For sh-based shells:


LD_PRELOAD=/usr/local/firstworks/lib/libpqsqlrelay.so
export LD_PRELOAD
psql -h localhost -p 8009 -U oracle8user -W
Password: oracle8pass

For csh-based shells:


setenv LD_PRELOAD /usr/local/firstworks/lib/libpqsqlrelay.so
psql -h localhost -p 8009 -U oracle8user -W
Password: oracle8pass

The LD_PRELOAD environment variable instructs the dynamic loader to load libpqsqlrelay.so before loading any other libraries for any programs. The psql client program will still load the native PostgreSQL client library, but since it loaded the SQL Relay drop-in replacement library first, function calls that would normally be fulfilled by the native PostgreSQL client library are fulfilled by the SQL Relay drop-in replacement library instead.

Below is a sample session using the psql command line client against an Oracle database through SQL Relay.

ORA-06550: line 1, column 6:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge
   <a single-quoted SQL string> pipe
The symbol "exit" was substituted for ";" to continue.
ORA-06550: line 1, column 84:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ; comment work <a SQL statement>

Attempted Query:
BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'oracle8test'; COMMIT
Welcome to psql 7.3.4-RH, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

=> create table testtable (col1 varchar2(60), col2 number(5,2));

=> insert into testtable values ('hello',123.45);

=> select * from testtable;
 COL1  |  COL2
-------+--------
 hello | 123.45
(1 row)

=> \q


Using the drop-in replacement library with daemons

Using the SQL Relay drop-in replacement library for PostgreSQL with daemons is simlar to using it on the command line. You just need to add the LD_PRELOAD command to the startup script for the daemon before the command that starts the daemon itself.


Using the drop-in replacement library with inetd/xinetd helper programs

Inetd and xinetd are daemons that listen on ports and run helper programs to service requests on those ports. The helper programs die off after the request is serviced.

The easist way to get an inetd helper program to use the SQL Relay drop-in replacement library for PostgreSQL is to add the LD_PRELOAD command to the startup script for inetd/xinetd. Any command that inetd/xinetd runs will also preload the library.

However, if some of the helper programs need to actually run against PostgreSQL and not against SQL Relay, then you will have to do something different. The easiest thing to do is create a script for each helper program that needs to run against SQL Relay that runs the LD_PRELOAD command and then runs the actual helper program, passing it all the necessary command line arguments.

For example, lets say you have a pop3 server called pop3d that uses PostgreSQL for user identification and you wanted to use SQL Relay instead of PostgreSQL. The inetd.conf entry might look like this:

pop3 stream tcp nowait root /usr/local/bin/pop3d

An /etc/xinetd.d entry might look like this:

service pop3
{
	socket_type	= stream
	wait		= no
	user		= root
	server		= /usr/local/bin/pop3d
}

You could write the a script called /usr/local/bin/pop3d-sqlrelay as follows:

#!/bin/sh
LD_PRELOAD=/usr/local/firstworks/lib/libpqsqlrelay.so
export LD_PRELOAD
/usr/local/bin/pop3d $@

And modify the entries to call the script instead of pop3d as follows:

pop3 stream tcp nowait root /usr/local/bin/pop3d-sqlrelay

Or for xinetd:

service pop3
{
	socket_type	= stream
	wait		= no
	user		= root
	server		= /usr/local/bin/pop3d-sqlrelay
}

Using the drop-in replacement library with modules

You may want to use the SQL Relay drop-in replacement library for PostgreSQL clients with a program that isn't compiled against the native PostgreSQL client library but rather loads it as a module such as a program that uses ODBC or Perl DBI, or an Apache/PHP application.

Using the SQL Relay drop-in replacement library with programs that load the native PostgreSQL client library as a module is simlar to using it on the command line. You just need to make sure that the LD_PRELOAD command is run before the program starts.

If the program is a command line program, then run the LD_PRELOAD command before running your program. Even though the program ultimately loads the native PostgreSQL client library, all of it's functions will be overriden by the SQL Relay drop-in replacement library.

If the program is a daemon then add the LD_PRELOAD command to the startup script for the daemon.

If the program runs in the address space of a daemon, such as a PHP application running under Apache's mod_php, then add the LD_PRELOAD command to the startup script for the daemon. The caveat here is that all applications running in the address space of the daemon will use the drop-in replacement library instead of the native PostgreSQL library. It is not possible, for example for a web server to run one PHP application directly against PostgreSQL and another PHP application against SQL Relay using the drop-in replacement library; if the drop-in replacement library is loaded, both applications will end up using it.

If the program is spawned by a daemon, such as a cgi spawned by a web-server or an inetd/xinetd helper program, then you can either add the LD_PRELOAD command to the daemon's startup script or write a script to run the LD_PRELOAD command and pass along the command line arguments (see the section Using the drop-in replacement library with inetd/xinetd helper programs above)).


Function support

The SQL Relay drop-in replacement library for PostgreSQL implements most of the native PostgreSQL client library's functions, but there are a few functions that aren't implemented because SQL Relay doesn't have a good way to support them. These functions return safe values or a failure condition.

Here is a list of functions that are implemented and functions that are not. If your application uses one of the functions that is not implemented, you may or may not be able to use it with the SQL Relay drop-in replacement library for PostgreSQL.

FunctionImplemented?
PQsetdbLoginyes, but ignores the db, options and tty parameters
PQsetdbyes
PQconnectdbyes
PQfinishyes
PQresetyes
PQdbyes
PQuseryes
PQpassyes
PQhostyes
PQportyes
PQttyyes
PQoptionsyes
PQstatusyes
PQerrorMessageyes
PQsocketno, always returns -1
PQbackendPIDno, always returns -1
PQgetsslno, always returns 0
PQclientEncodingyes
PQsetClientEncodingyes
PQsetNoticeProcessoryes
PQescapeStringyes
PQescapeByteayes
PQunescapeByteayes
PQclearyes
PQexecyes
PQresultStatusyes
PQresStatusyes
PQresultErrorMessageyes, returns the SQL Relay error string
PQntuplesyes
PQnfieldsyes
PQbinaryTuplesyes
PQfnameyes
PQfnumberyes
PQftypeyes
PQfsizeyes
PQfmodyes
PQcmdStatusyes
PQoidStatusyes
PQoidValueyes
PQcmdTuplesyes
PQgetvalueyes
PQgetlengthyes
PQgetisnullyes
PQmakeEmptyPGresultyes
PQprintyes
PQdisplayTuplesyes
PQprintTuplesyes
PQconndefaultsalways returns NULL
PQconninfoFreeyes
PQtracesort of, just calls sqlrconnection::debugOn()
PQuntracesort of, just calls sqlrconnection::debugOff()
PQgetlineno, NULL terminates string and returns EOF
PQputlineno, always returns EOF
PQgetlineAsyncno, NULL terminates buffer and returns EOF
PQputnbytesno, always returns EOF
PQendcopyno, always returns -1 for failure
PQmblenonly for UTF8, always returns 1
PQenv2encodingonly for UTF8
PQfnno, always returns NULL
lo_openno, always returns -1
lo_closeno, always returns -1
lo_readno, always returns -1
lo_writeno, always returns -1
lo_lseekno, always returns -1
lo_creatno, always returns -1
lo_tellno, always returns -1
lo_unlinkno, always returns -1
lo_importno, always returns -1
lo_exportno, always returns -1
PQnotifiesno, always returns NULL
PQfreeNotifyno, does nothing
The following functions implement the PostgreSQL asynchronous query API. SQL Relay doesn't have an asynchronous query API so they are implemented as calls to synchronous query functions. They work, but in a synchronous manner.
PQconnectStartyes
PQconnectPollyes
PQresetStartyes
PQresetPollyes
PQrequestCancelyes
PQsendQueryyes
PQgetResultyes
PQisBusyyes
PQconsumeInputyes
PQsetnonblockingyes
PQisnonblockingyes
PQflushyes
PQsendSomeyes