The following routines deal with making a connection to a PostgreSQL backend server. The application program can have several backend connections open at one time. (One reason to do that is to access more than one database.) Each connection is represented by a PGconn object which is obtained from PQ-connectdb or PQ-setdb-login. Note that these functions will always return a non-#f object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check whether a connection was successfully made before queries are sent via the connection object.
PQ-connectdb makes a new connection to the database server.
(PQ-connectdb conninfo)
This routine opens a new database connection using the parameters taken from the string conninfo. Unlike PQ-setdb-login below, the parameter set can be extended without changing the function signature, so use either of this routine or the nonblocking analogues PQ-connect-start and PQ-connect-poll is preferred for application programming. The passed string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace.
Each parameter setting is in the form keyword = value. (To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, e.g., \' or \\.) Spaces around the equal sign are optional. The currently recognized parameter keywords are:
Name of host to connect to. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default is to connect to a Unix-domain socket in /tmp.
IP address of host to connect to. This should be in standard numbers-and-dots form, as used by the BSD functions inet_aton et al. If a nonzero-length string is specified, TCP/IP communication is used.
Using hostaddr instead of host allows the application to avoid a host name look-up, which may be important in applications with time constraints. However, Kerberos authentication requires the host name. The following therefore applies. If host is specified without hostaddr, a host name lookup is forced. If hostaddr is specified without host, the value for hostaddr gives the remote address; if Kerberos is used, this causes a reverse name query. If both host and hostaddr are specified, the value for hostaddr gives the remote address; the value for host is ignored, unless Kerberos is used, in which case that value is used for Kerberos authentication. Note that authentication is likely to fail if libpq is passed a host name that is not the name of the machine at hostaddr.
Without either a host name or host address, libpq will connect using a local Unix domain socket.
Port number to connect to at the server host, or socket file name extension for Unix-domain connections.
The database name.
User name to connect as.
Password to be used if the server demands password authentication.
Time space in seconds given to connect routine. Zero or not set means infinite.
Trace/debug options to be sent to the server.
A file or tty for optional debug output from the backend.
Set to 1 to require SSL connection to the server. Libpq will then refuse to connect if the server does not accept an SSL connection. Set to 0 (default) to negotiate with server. This option is only available if PostgreSQL is compiled with SSL support.
If any parameter is unspecified, then the corresponding environment variable (see Section 1.10) is checked. If the environment variable is not set either, then hardwired defaults are used. The return value is a pointer to a foreign struct PGconn representing the connection to the backend.
PQ-setdb-login makes a new connection to the database server.
(PQ-setdb-login pghost pgport pgoptions pgtty db-name login pwd)
This is the predecessor of PQ-connectdb with a fixed number of parameters but the same functionality.
PQ-setdb makes a new connection to the database server.
(PQ-setdb pghost pgport pgoptions pgtty db-name)
This is a macro that calls PQ-setdb-login with #f for the login and pwd parameters. It is provided primarily for backward compatibility with old programs.
PQ-connect-start and PQ-connect-poll make a connection to the database server in a nonblocking manner.
(PQ-connect-start conninfo)
(PQ-connect-poll conn)
These two routines are used to open a connection to a database server such that your application's thread of execution is not blocked on remote I/O whilst doing so.
The database connection is made using the parameters taken from the string conninfo, passed to PQ-connect-start. This string is in the same format as described above for PQ-connectdb.
Neither PQ-connect-start nor PQ-connect-poll will block, as long as a number of restrictions are met:
The hostaddr and host parameters are used appropriately to ensure that name and reverse name queries are not made. See the documentation of these parameters under PQ-connectdb above for details.
If you call PQ-trace, ensure that the stream object into which you trace will not block.
To begin, call (set! conn (PQ-connect-start "connection_info_string"). If conn is #f, then libpq has been unable to allocate a new PGconn structure. Otherwise, a valid PGconn pointer is returned (though not yet representing a valid connection to the database). On return from PQ-connect-start, call (set! status (PQ-status conn). If status equals CONNECTION-BAD, PQ-connect-start has failed.
If PQ-connect-start succeeds, the next stage is to poll libpq so that it may proceed with the connection sequence. Loop thus: Consider a connection ``inactive'' by default. If PQ-connect-poll last returned PGRES-POLLING-ACTIVE, consider it ``active'' instead. If (PQ-connect-poll conn) last returned PGRES-POLLING-READING, perform a select() for reading on PQsocket(conn). If it last returned PGRES-POLLING-WRITING, perform a select() for writing on PQsocket(conn). If you have yet to call PQ-connect-poll, i.e. after the call to PQ-connect-start, behave as if it last returned PGRES-POLLING-WRITING. If the select() shows that the socket is ready, consider it ``active''. If it has been decided that this connection is ``active'', call PQ-connect-poll conn) again. If this call returns PGRES-POLLING-FAILED, the connection procedure has failed. If this call returns PGRES-POLLING-OK, the connection has been successfully made.
Note that the use of select() to ensure that the socket is ready is merely a (likely) example; those with other facilities available, such as a poll() call, may of course use that instead.
At any time during connection, the status of the connection may be checked, by calling PQ-status. If this is CONNECTION-BAD, then the connection procedure has failed; if this is CONNECTION-OK, then the connection is ready. Either of these states should be equally detectable from the return value of PQ-connect-poll, as above. Other states may be shown during (and only during) an asynchronous connection procedure. These indicate the current stage of the connection procedure, and may be useful to provide feedback to the user for example. These statuses may include:
Waiting for connection to be made.
Connection OK; waiting to send.
Waiting for a response from the server.
Received authentication; waiting for connection start-up to continue.
Negotiating environment (part of the connection start-up).
Note that, although these constants will remain (in order to maintain compatibility), an application should never rely upon these appearing in a particular order, or at all, or on the status always being one of these documented values. An application may do something like this:
(require (lib "etc.ss"))
(let ((feedback
(evcase (PQ-status conn)
(CONNECTION-STARTED "Connecting...")
(CONNECTION-MADE "Connected to server...")
.
.
.
(else "Connecting..."))))
...
Note that if PQ-connect-start returns a non-#f pointer, you must call PQ-finish when you are finished with it, in order to dispose of the structure and any associated memory blocks. This must be done even if a call to PQ-connect-start or PQ-connect-poll failed.
PQ-connect-poll will currently block if libpq is compiled with USE_SSL defined. This restriction may be removed in the future.
These functions leave the socket in a nonblocking state as if PQsetnonblocking had been called.
PQ-finish closes the connection to the backend. Also frees memory used by the foreign PGconn object.
(PQ-finish conn)
Note that even if the backend connection attempt fails (as indicated by PQ-status), the application should call PQ-finish to free the memory used by the foreign PGconn object. The foreign PGconn pointer should not be used after PQ-finish has been called.
PQ-reset resets the communication port with the backend.
(PQ-reset conn)
This function will close the connection to the backend and attempt to reestablish a new connection to the same server, using all the same parameters previously used. This may be useful for error recovery if a working connection is lost.
PQ-reset-start and PQ-resetpoll reset the communication port with the backend, in a nonblocking manner.
(PQ-reset-start conn)
(PQ-reset-poll conn)
These functions will close the connection to the backend and attempt to reestablish a new connection to the same server, using all the same parameters previously used. This may be useful for error recovery if a working connection is lost. They differ from PQ-reset (above) in that they act in a nonblocking manner. These functions suffer from the same restrictions as PQ-connect-start and PQ-connect-poll.
Call PQ-reset-start. If it returns 0, the reset has failed. If it returns 1, poll the reset using PQ-reset-poll in exactly the same way as you would create the connection using PQ-connect-poll.
Libpq application programmers should be careful to maintain the PGconn abstraction. Use the accessor functions below to get at the contents of PGconn. Avoid directly referencing the fields of the PGconn structure because they are subject to change in the future. (Beginning in PostgreSQL release 6.4, the definition of struct PGconn is not even provided in libpq-fe.h. If you have old code that accesses PGconn fields directly, you can keep using it by including libpq-int.h too, but you are encouraged to fix the code soon.)
PQ-db returns the database name of the connection.
(PQ-db conn)
PQ-db and the next several functions return the values established at connection. These values are fixed for the life of the PGconnobject.
PQ-user returns the user name of the connection.
(PQ-user conn)
PQ-pass returns the password of the connection.
(PQ-pass conn)
PQ-host returns the server host name of the connection.
(PQ-host conn)
PQ-port returns the port of the connection.
(PQ-port conn)
PQ-tty returns the debug tty of the connection.
(PQ-tty conn)
PQ-options returns the backend options used in the connection.
(PQ-options conn)
PQ-status returns the status of the connection.
(PQ-status conn)
The status can be one of a number of values. However, only two of these are seen outside of an asynchronous connection procedure - CONNECTION-OK or CONNECTION-BAD. A good connection to the database has the status CONNECTION-OK. A failed connection attempt is signaled by status CONNECTION-BAD. Ordinarily, an OK status will remain so until PQ-finish, but a communications failure might result in the status changing to CONNECTION-BAD prematurely. In that case the application could try to recover by calling PQ-reset.
See the entry for PQ-connect-start and PQ-connect-poll with regards to other status codes that might be seen.
PQ-error-message returns the error message most recently generated by an operation on the connection.
(PQ-error-message conn)
Nearly all libpq functions will set PQ-error-message if they fail. Note that by libpq convention, a non-empty PQ-error-message will include a trailing newline.