PostgreSQL implementation details

Blobs

Blobs are stored in files in folders outside of the database. Internally, they are stored as the composite type `webhare_blob`, which consists of a text id identifying the storage location, and an int8 length with the length of the blob. All these blob references are stored in the table `webhare_internal.blobs`. Blob columns in normal tables are created with a foreign reference to that table. In HareScript, creation of blob files and registration in the blob table is taken care of automatically.

Mapping of NULLs

HareScript doesn't have the concept NULL baked into the language. To store these values, a distinction is made between value columns and reference columns. For value columns, NULL is never used. All values are stored as-is. For reference columns (columns that are a foreign reference to another table) NULL is stored for the default value of the keys. These must be annotated in the HareScript table definitions with `NULL := <value>`.

UTF-8

PostgreSQL enforces that only UTF-8 valid data can be stored in VARCHAR columns (which are used to store strings in WebHare). If string data needs to be stored that is not valid UTF-8, a BYTEA column is used. This is done when the column is marked binary in the schemadefinition. Also, the HareScript column needs to be annotated with `__ATTRIBUTE__(BINARY)`.

Prepared statements

If a SELECT or INSERT with the same query and argument types is repeated a number of times, a prepared statement is created for that query, and used to speed up execution of the statement.

Transaction management

The WebHare PostgreSQL database is configured to have a read-only session by default. The BeginWork calls will open a new, writable transaction. This can be a problem when connecting manually to the PostgreSQL database, use the statement `SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE` to enable auto-opening of writable transactions.

Isolation

The legacy database server did use a isolation mode roughly equivalent to the 'READ COMMITTED' isolation of PostgreSQL, and without the row-level locking, and some leaking of the effects of other transactions. Transactions for PostgreSQL now use the isolation level 'READ COMMITTED', where the individual statements in a transactions can see the effects of transactions that were committed between those statements. Make sure this doesn't affect the correctness of your code!