PostgreSQL

PostgreSQL is an open-source full ACID SQL database system. Pronounced "post-grehs-Q-L", though in the case of MS SQL Server, SQL is pronounced as "sequel".

Description edit

PostgreSQL is a derivitive of POSTGRES 4.2, converted to ANSI C. POSTGRES is a database management system. It is compliant with ANSI SQL92 and SQL89. It supports a number of enhancements, such as inheritance, declaritive queries, optimization, concurrency control, transactions, multi-user support, user defined operators, types, functions, and access methods. APIs exist for C, C++, Java, Perl4, Perl5, Python, SQL and Tcl. A JDBC driver is also included in the main distribution. Free drivers for ODBC are available as a separate download. A commercial version of PostgreSQL is available via Illustra, Inc. The most recent version is 7.4.

PostgreSQL was previously distributed with various language bindings, but current versions no longer include those. They are available separately.

Starting with version 8.0, the server side of Postgres is also available for native Windows (NT, 2000, XP, etc. variants only). Previous versions required Cygwin to run the PostgreSQL server. Client access libraries have always been available on Win32.

PostgreSQL and Tcl have a long history with each other. Tcl bindings such as pgtcl are available for communicating with the server from Tcl scripts, while

One of PG's technical advantages among databases is its asynchronous notification feature. Tcl's event loop nicely complements this.

Resources edit

pgoundry: projects server for PostgreSQL

Utilities edit

PgBrowse: a MacOS-oriented client
pltcl: script triggers and stored procedures in the PostgreSQL server
libtclpq ,by Colin McCormack: is a quick/experimental Swig-generated hack with itcl wrapper to permit asynchronous execution of PostgreSQL commands and cancellation of commands in progress

Bindings edit

TDBC: This standard package (from a Tcl viewpoint), shipped with Tcl, includes a PostgreSQL driver
SQL Relay
pgtcl: This standard package (from a Postgres viewpoint) uses the libpq library, which allows to use "listen", so that a Tcl proc will be called automatically when some other database session issues a "notify". The current version is maintained by Flightaware.
pgtclng: This package is a fork of an earlier version pg pgtcl and also allow to use "listen".
pgintcl: A pure Tcl interface to, which is itself written entirely in Tcl and does not rely on libpq.
nsdbipg: A native Postgresql driver for the easy-to-use nsdbi interface: dbi_1row {select a, b, from table where key = :k} (That's all you need to know!)
TclODBC: This works fine for PostgreSQL. Artur Trzewik 2003-12-11: TclODBC lacks some functions that are possible with Pgtcl. That are: movable cursor (seek command on cursor position) and nested queries in one database handle. I think they are possible in ODBC but not implemented in TclODBC.
PQTcl
Postgres_pqatcl

pgtcl and pgtcl-ng have binaries available for windows, but aren't current (2018-01), but a couple of years old and only 32 bit. pgintcl is platform-independent and therefore can be used with any current Tcl version.

Convenience Command: pg edit

I found, that the syntax of the Pgtcl commands are not the Tcl way. So I wrote a little wrapper around the commands provided by the Pgtcl package to make them more tcl-like:

proc pg {cmd args} {
    switch $cmd {
       connect {pg_connect [lindex $args end]}
       disconnect {pg_disconnect [lindex $args end]}
       execute {pg_exec [lindex $args 0] [lindex $args 1]}
       getrow {pg_result [lindex $args 0] -getTuple [lindex $args 1]}
       clear {pg_result [lindex $args 0] -clear}
       status {pg_result [lindex $args 0] -status}
       rowcount {pg_result [lindex $args 0] -numTuples}
    }
}

This adds a command pg to Tcl which I like far more than the original one. Instead of saying

pg_connect "mydb"
pg_disconnect $myHandle
pg_result $myResult -clear

I just type

pg connect "mydb"
pg disconnect $myHandle
pg clear $myResult

Please note, that there is no error handling and checking in the command pg and you can easily type in wrong things. It would be best to put this command in it's own namespace and provide for error checking etc. You could even add more funcionality like a loop command for query results like in nstcl

pg_execute Performance Trick edit

MB 2003-11-13:

I got caught out by a problem using pg_execute. pg_execute evaluates the supplied script for each row of the result. I was appending each result to a list. The problem was that the speed scaled very poorly as the number of records increased. I boiled it down to this test case:

set a {} 
time [list pg_execute pgsql5 {SELECT * FROM journal LIMIT 1000} {
append a {23456789012345678901234567890123456789012345678901234567890}}]

It turned out to be because append was, of course, returning the whole variable, which was quite big. pg_execute was internally doing something slow with what was returned. Changing the test case to:

set a {} 
time [list pg_execute pgsql5 {SELECT * FROM journal LIMIT 1000} {
append a {23456789012345678901234567890123456789012345678901234567890} ; list}]

fixed the problem. This reduced the time to read 10,000 records from about half a minute to 170 milliseconds!

Category PostgreSQL

Category Database