Aster Web Data / Connect your data

PostgreSQL

How Aster Web Data uses PostgreSQL, and how PostgreSQL's types, collations and results shape what clients receive.

If your data is in PostgreSQL 14 or later, Aster Web Data serves it from the tables you already have. A filter, a sort, a page, a count and an $expand each run in PostgreSQL, usually as one statement, with every comparison made under the column's own collation. The indexes your DBA has built serve those statements, and the answers are the ones psql would give. Where PostgreSQL's answer differs from OData's, PostgreSQL's stands; Departures from OData lists those places.

Three choices on this page change what your users see: how the connection verifies TLS, which collation each text column uses, and which indexes you add for the navigations and sorts your screens use.

Connecting

Declare the database once under databases:, and name it from each data source stored on it:

databases:
  main:
    kind: postgres
    url_env: MAIN_DB_URL        # postgres://user:password@host:5432/database
    tls: verify-full
    ca_file: ca.pem             # optional: otherwise the Mozilla roots

The URL comes from an environment variable (url_env) or a file (url_file), or you give its parts: host, port, user, password_env or password_file, and database. It is read again on every attempt to connect, so a rotated password takes effect without a restart. Secrets explains the rules, and the configuration reference lists every key with its default, including the pool and the retry schedule.

These keys behave in a way particular to PostgreSQL:

• schema is public when absent, and a data source's own schema wins. Every statement names its schema, so the service does not depend on search_path. Data sources in two schemas of one database entry can be joined in one statement.
• tls takes the five modes in TLS. A sslmode or sslrootcert parameter in the URL is refused, because tls sets the same thing. TLS is built on rustls, without OpenSSL, and SCRAM authentication works without channel binding.
• read_only: true also opens every session with default_transaction_read_only = on. Read-only databases and privileges gives the rest, and the grants the database user needs.
• options.single_statement is true by default. Set it to false to read each $expand level with a statement of its own and to run nested inserts member by member. The answers are the same; the statement count is higher.

Two databases: entries are two connection pools, even on one server, and a request that reads from both is combined in the service, as Reads across databases describes.

The service checks the server version when it opens a connection and refuses a server older than 14, naming the version it found. PostgreSQL 14 is the oldest version the PostgreSQL project supports. The tests pass on PostgreSQL 14, 15, 16, 17 and 18.

Start-up check

When the service opens the database, it checks each data source's table against its configuration. The table and each stored column must exist, each column must hold the field's values exactly (the accepted types are listed under Types), and the column's precision, scale, collation and default must match what the configuration declares. A mismatch lists every problem and says what to declare, for example declare precision: 3 on Order.created.

A failed check makes only that database's sets unavailable, and the check runs again on the retry schedule, so fixing the table brings them back without a restart. A database goes down describes what clients see meanwhile and how to make the service stop instead. To run the check on its own, use the check command.

Types

When setup: recreate creates a table, each field gets the column type below. An existing table must use one of the column types the start-up check accepts, listed after the table.

A 28-digit decimal here is one whose digits fit a 96-bit mantissa: 28 digits always fit, and 29 only up to 79,228,162,514,264,337,593,543,950,335. A non-zero digit past the 28th decimal place also fails.

The start-up check accepts these column types, and refuses any other:

• Text: text or character varying. character is refused, because it pads.
• Booleans: boolean. Integers: smallint, integer or bigint.
• Edm.Single: real or double precision. Edm.Double: double precision.
• Decimals: numeric. 128-bit unsigned: numeric(39,0) or wider.
• Dates: date. Times: time without time zone. Timestamps: timestamp with time zone, or timestamp without time zone, read as UTC.
• GUIDs: uuid. Binary: bytea.

Every session runs in UTC with ISO date style. Values are bound as their PostgreSQL types, and results are read in binary where that is exact. Inside the JSON of a nested $expand, a decimal or a double is written as text, so it reads back exactly.

Collation and case

A comparison, a sort and like use the column's own collation. name eq 'x' is name = $1 and $orderby=name is ORDER BY name, with no cast and no COLLATE, so an ordinary index on the column serves them. $metadata publishes each text field's collation as AsterData.Collation: the field's own, else the database's collation: key, else the column's. The start-up check refuses a column whose collation differs from the one declared.

• Deterministic collations (libc and ICU locales, "C", pg_c_utf8): equality and like compare bytes. Ordering follows the locale, so en_GB.UTF-8 sorts a before B, and "C" sorts B first.
• Nondeterministic collations (an ICU collation created with deterministic = false): equality and ordering follow the collation, so a case-insensitive one makes eq case-insensitive. like, and with it contains, startswith and endswith, runs under one from PostgreSQL 18. On PostgreSQL 17 and earlier, and for indexof on every version, the request is refused, naming the column.
• Case-insensitive search. AsterData.icontains, istartswith, iendswith and ilike run as ILIKE, which folds case under the column's collation. Under a nondeterministic collation they are refused, naming the column.
• Case mapping. tolower and toupper are LOWER and UPPER under the argument's collation, and the collation's provider decides the result. In a probe of 34 inputs on PostgreSQL 17, "C" left every non-ASCII character unchanged. pg_c_utf8 (Unicode 15.1) applied the simple mappings, so ß does not uppercase to SS and a word-final Σ lowercases to σ. und-x-icu matched the full Unicode mappings on all 34. en_US.UTF-8, which uses the C library, differed from them on İ, a word-final Σ, and the uppercase of ß, ʼn and the ligatures.
• Padding. text and varchar keep trailing spaces, and compare them.

Where each operation runs

Each OData operator becomes the PostgreSQL operator with the same meaning, so PostgreSQL's comparison, arithmetic, rounding and errors give the answer. This request reads a customer with their two largest invoices and their invoice count:

/* aster read customers */ SELECT c."id", c."name", c."email", c."region", CAST(c.xmin AS text) AS c5, q2.j AS "invoices", (SELECT COUNT(*) FROM "public"."invoices" AS i2 WHERE i2."customerId" = c."id") AS c7 FROM "public"."customers" AS c LEFT JOIN LATERAL (SELECT COALESCE(json_agg(json_build_array(q."issued", CAST(q."amount" AS text), q."id", q.c4) ORDER BY q."amount" DESC, q."id"), '[]'::json) AS j FROM (SELECT i."issued", i."amount", i."id", CAST(i.xmin AS text) AS c4 FROM "public"."invoices" AS i WHERE i."customerId" = c."id" ORDER BY i."amount" DESC, i."id" LIMIT $1) AS q) AS q2 ON TRUE WHERE c."id" = $2
-- binds: 2, 3

The invoices come from a LEFT JOIN LATERAL that builds them as JSON, in the same statement as the customer. PostgreSQL reads an $expand this way where the navigation's foreign key leads an index and the table has at most 48 columns; otherwise each level is read by a second statement that takes the parents' keys as one array, = ANY($1).

These run in PostgreSQL:

• Comparisons on every type, and ordering on every type except binary (equality only) and JSON (no ordering). ne against a value on a nullable column is x <> $1 OR x IS NULL, and two nullable columns compare with IS [NOT] DISTINCT FROM, so the answers are OData's two-valued ones.
• in lists, like, case, exists and sub-queries, including correlated ones.
• contains, startswith, endswith, indexof, length, concat, substring, trim, tolower and toupper, on tables without a collection column, and the case-insensitive functions above.
• year, month and day of a date.
• 64-bit integer arithmetic (div truncates toward zero), decimals on numeric, doubles on float8, divby, round, ceiling and floor.
• Sorting, offset and keyset paging, exact counts, grouping, distinct, aggregates, windows, unions, and inner, left, full, semi and anti joins.
• Inserts, updates, deletes and upserts, writes selected by criteria, RETURNING, preconditions on expected values, and atomic increments.

These run in the service, within the limits on rows it may hold: mod of doubles (PostgreSQL has no float8 remainder), 128-bit arithmetic, date, time and duration arithmetic, regular expressions, hour, minute and second, casts from string, lambda expressions over collections, and full-text search.

Errors

Nulls and ordering

OData puts nulls first in ascending order and last in descending order, and the service writes that as NULLS FIRST or NULLS LAST. A column the table declares NOT NULL sorts with no NULLS clause, so a plain index can serve it.

NaN is PostgreSQL's: NaN = NaN is true, and NaN sorts after every other number.

A next page is a keyset query from the last row's values for every sort key, including the entity key, so a row inserted or deleted before the cursor moves no other row across a page boundary. Offset paging is used only where the order is an expression, or with $apply or /$all. $count=true is exact and agrees with the page, as A page with its total shows.

Departures from OData

Where OData 4.01 defines a result and PostgreSQL gives another, PostgreSQL's result stands. These are the places:

• Division by zero raises an error for every numeric type. OData gives INF, -INF or NaN for div on doubles and for divby.
• NaN equals itself and sorts greatest. OData compares NaN as IEEE does, unequal to everything.
• round of a double rounds ties to even on common platforms. OData rounds half away from zero. round of a decimal rounds half away from zero, as OData does.
• trim removes spaces only. OData's trim removes whitespace.
• tolower and toupper follow the collation's provider, not the full Unicode mappings.
• String comparison follows the column's collation. A nondeterministic, case-insensitive collation makes eq case-insensitive.
• Substring search under a nondeterministic collation is refused on PostgreSQL 17 and earlier, naming the column. PostgreSQL 18 runs it. On 17, give the column a deterministic collation and write the filter with tolower, for example contains(tolower(name), 'x').
• substring with a negative start counts positions before the first character, so substring('abc', -2) is 'abc'.

Writes and transactions

• Isolation. A read outside an atomic group runs in its own transaction at the server's default level, read committed. Repeatable read and serializable are available.
• Atomic groups. A $batch atomicity group runs in one transaction on one connection. A group that checks a rule runs at serializable isolation, with up to four attempts (10, 20 and 30 ms apart) within the request deadline. A serialisation failure or deadlock rolls the group back and runs it again from the first member. A failed check is not retried.
• One statement for a group. The leading inserts and checks of a group run as one statement when they are literal rows that refer only to earlier inserts. A nested insert runs as one statement on a table of at most 48 columns. A table with a trigger or a rewrite rule takes no part in this, and runs member by member. If the statement fails, the group runs member by member, so the result and the error are the same as running them one at a time.
• Row locks. An update or delete locks the rows it checked its precondition on.
• ETags. etag: native, the default, uses the row's xmin, which changes with every update and needs nothing added to the table. A restore that rewrites rows, such as pg_restore or logical replication, changes it, so each tag handed out before answers 412 once; a physical standby keeps it. A view has no xmin, so a set over a view names token columns or an expression. A version column that a trigger advances takes the table out of the one-statement write, and versionField: version, which Aster advances in the write itself, keeps it in. ETags and concurrent edits explains the tokens.
• Caller settings. Every transaction sets aster.user and aster.roles, which a row-level security policy can read, and sets statement_timeout from limits.statement_timeout_ms. They are local to the transaction, so a pooled connection never carries one request's settings into the next, even behind a transaction-pooling proxy. The service enforces access itself; a policy is an extra layer.

A request waits for a free connection from the pool; Limits and A database goes down give the timeouts and the 503 answers.

Indexes and sort order

Indexes gives the indexes that serve a sorted page and a navigation, including NULLS FIRST for a nullable sort column. Two more rules are particular to PostgreSQL:

• A one-statement $expand needs an index that leads with the navigation's foreign key. Without one, each level is read by a second statement with the same answer, and the service logs each such navigation at start-up so you can add the index.
• A filter that wraps a column in an expression, such as length(S) eq 3, can lead PostgreSQL to scan the table in key order and test each row. When few rows match, that scan reads much of the table. Index the expression (CREATE INDEX ON t ((length("S")))), add a selective indexed condition beside it, or sort by an indexed column. tolower(name) eq 'x' is LOWER(name) = $1, which an index on LOWER(name) serves.

Scaffold

The scaffold command reads PostgreSQL's catalog and writes a starting data-source configuration for the columns it can map exactly, leaving the others as comments with the reason. It needs CONNECT, USAGE on each schema and SELECT on the tables, and runs no DDL and no write. Connect your own database walks through it, and Model your data covers what to add once it has run.