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.
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 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.
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 }
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)).
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.
Function | Implemented? |
---|---|
PQsetdbLogin | yes, but ignores the db, options and tty parameters |
PQsetdb | yes |
PQconnectdb | yes |
PQfinish | yes |
PQreset | yes |
PQdb | yes |
PQuser | yes |
PQpass | yes |
PQhost | yes |
PQport | yes |
PQtty | yes |
PQoptions | yes |
PQstatus | yes |
PQerrorMessage | yes |
PQsocket | no, always returns -1 |
PQbackendPID | no, always returns -1 |
PQgetssl | no, always returns 0 |
PQclientEncoding | yes |
PQsetClientEncoding | yes |
PQsetNoticeProcessor | yes |
PQescapeString | yes |
PQescapeBytea | yes |
PQunescapeBytea | yes |
PQclear | yes |
PQexec | yes |
PQresultStatus | yes |
PQresStatus | yes |
PQresultErrorMessage | yes, returns the SQL Relay error string |
PQntuples | yes |
PQnfields | yes |
PQbinaryTuples | yes |
PQfname | yes |
PQfnumber | yes |
PQftype | yes |
PQfsize | yes |
PQfmod | yes |
PQcmdStatus | yes |
PQoidStatus | yes |
PQoidValue | yes |
PQcmdTuples | yes |
PQgetvalue | yes |
PQgetlength | yes |
PQgetisnull | yes |
PQmakeEmptyPGresult | yes |
PQprint | yes |
PQdisplayTuples | yes |
PQprintTuples | yes |
PQconndefaults | always returns NULL |
PQconninfoFree | yes |
PQtrace | sort of, just calls sqlrconnection::debugOn() |
PQuntrace | sort of, just calls sqlrconnection::debugOff() |
PQgetline | no, NULL terminates string and returns EOF |
PQputline | no, always returns EOF |
PQgetlineAsync | no, NULL terminates buffer and returns EOF |
PQputnbytes | no, always returns EOF |
PQendcopy | no, always returns -1 for failure |
PQmblen | only for UTF8, always returns 1 |
PQenv2encoding | only for UTF8 |
PQfn | no, always returns NULL |
lo_open | no, always returns -1 |
lo_close | no, always returns -1 |
lo_read | no, always returns -1 |
lo_write | no, always returns -1 |
lo_lseek | no, always returns -1 |
lo_creat | no, always returns -1 |
lo_tell | no, always returns -1 |
lo_unlink | no, always returns -1 |
lo_import | no, always returns -1 |
lo_export | no, always returns -1 |
PQnotifies | no, always returns NULL |
PQfreeNotify | no, 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. | |
PQconnectStart | yes |
PQconnectPoll | yes |
PQresetStart | yes |
PQresetPoll | yes |
PQrequestCancel | yes |
PQsendQuery | yes |
PQgetResult | yes |
PQisBusy | yes |
PQconsumeInput | yes |
PQsetnonblocking | yes |
PQisnonblocking | yes |
PQflush | yes |
PQsendSome | yes |