PostgreSQL implementation

PostgreSQL is a fully functional open source database available on Linux, Unix, Mac OS X and Windows. KCML supports PostgreSQL 8.4 and later using the native client library libpq. This is a networked client server implementation so the database server need not be on the same server and multiple KCML application servers can be attached to the same database.

A mapping layer has been added to KCML which translates the standard KDB7 database API operations into equivalent SQL which is then executed against the database. This has been done in such as way as to require almost no changes to existing KCML KDB7 programs in order to migrate KDB7 tables to PostgreSQL tables.

There is also a simple mapping of KDB datatypes into PostgreSQL native datatypes allowing the easy use of PostgreSQL tools for reporting and the like. However the KDB PostgreSQL tables are all owned by a special user, by default KCC, whose password is hidden and only known to the KCML runtime (and to the SYSDBA). Only the KCC user has been GRANTED update access. This is to guarantee referential integrity withing the application.

The PostgreSQL database must be Unicode with UTF-8 encoding.

Attaching to PostgreSQL

It is not necessary to perform a CREATE DATABASE for PostgreSQL.

A KI_CONNECT call using the database name "POSTGRES" and a connection string, specifying the location and name of the database, must be used to log into the PostgreSQL database and get a connection number for later use. This will load a shared library containing PostgreSQL support routines (kpostgres.dll on Windows and kpostgres.so on Unix). A licence file entry in your KCML licence file is needed to permit the use of this library. The library must be able to load the PostgreSQL Client library libpq.dll or libpq.so so this must be installed on the application server and visible on the library path.

When a table is opened it must be done on a handle allocated to that connection so that KCML knows to access PostgreSQL and not the native database. The userid and password fields will be required for PostgreSQL and generally you will want to have one secret special user with read and write permission for programatic access so that normal users do not have write access to tables under their own accounts.

PostgreSQL special considerations

Table, tablespace and column names are generally case insensitive and are forced to lower case in PostgreSQL by default. However if the name string is quoted then case of the name is fixed. By convention KCML will always uppercase and quote tablenames and tablespace names, to protect legacy illegal characters like minus signs, so they are fixed as uppercase in the schema and must be similarly quoted when referenced outside the KCML environment. Column names are not quoted and remain case insensitive.

PostgreSQL has no direct equivalent of the ROWID that uniquely identifies rows in a table. KCML will not generate PostgreSQL row OIDs for tables as they are not guaranteed to be unique and will change on an update. All tables created by KCML with CREATE TABLE have a hidden primary index on the hidden autoincrementing K$ROWID column which is of type UINT(4). This is returned in 4 bytes as the ROWID in statements such as KI_WRITE.

Oracle functionality not supported

Partitions are simulated. PostgreSQL does support basic partitioning using an inheritence mechanism so this may yet be implemented.

KDB functionality not supported

CREATE DATABASE does not apply.
KI_CREATE, KI_EXTEND, KI_GROW are not supported.

Implementation quirks

A KI_START_BEG with zero for the path will actually follow the internal index on K$ROWID but in a backwards direction to permit inserts while scanning the table.