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.

References to the blobs are stored as a webhare_blob type (CREATE TYPE webhare_blob AS (id text, size int8)). When using psq you would select '(columnname).id' and '(columname).size', eg:

# Get the biggest file from_fsobjects
SELECT id, (data).size AS len FROM system.fs_objects 
       ORDER BY (data).size DESC NULLS LAST LIMIT 1;

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!

Previous Next