Search documentation

Search for a topic, a control, or an event type.

    Docs / Databases / Schema and migrations

    Schema and migrations

    Change tables, types, indexes, views, functions and triggers through Owner migrations. Ordinary migrations commit together or roll back together.

    Clear a table in a migration

    To clear an existing customers table, run this SQL as an Owner migration. It removes all its rows. Use DELETE when the operation must follow row policies.

    Truncate customers
    TRUNCATE customers

    TRUNCATE is a schema statement. An Owner runs it in a migration. It removes every row, skips row policies, and records no row event. The same statement sent as a write is refused. The message says the statement is outside what a managed database query or write runs: it uses TruncateStmt, and a write is one insert, update, or delete.

    Schema and transactions

    Schema
    The table structure, column types, indexes, constraints, views, functions, and triggers. An Owner changes it through a migration, an ordered set of SQL statements. The changes run together in a transaction, so a failed statement rolls back the set. A migration whose only statement is CREATE INDEX CONCURRENTLY runs outside a transaction, as described in Extensions and performance.
    Rows
    The stored records. View access reads them. Edit access also inserts, updates, and deletes them. Changes must satisfy the table’s types and constraints.
    Row rules
    PostgreSQL policies limit which rows an account can read or change. They are separate from the grant that lets the account reach the database.

    The schema view shows tables and relationships. A table view presents rows, and a query returns the result of SQL. Grid editing needs a primary key to identify the row being changed.

    A migration changes the schema. Apart from TRUNCATE, it writes no rows. OAIZ refuses a migration that inserts, updates, or deletes rows, including rows a trigger or function writes while the migration runs. To fill a new column or table, apply the migration first, then write the rows with Write to Database or the Storage Execute tool.

    Three example orders with a customer reference, total and status.
    Example orders in the table view. View full size

    Migration rules

    An Owner runs a migration. Ordinary migration statements run together in one transaction, and a failed statement rolls the set back. One named CREATE INDEX CONCURRENTLY runs alone outside a transaction. A migration can create and alter permanent tables, indexes, sequences, enums, composite types and domains. It can rename those objects, drop them, truncate a table, and create, alter, rename or comment on a row policy. It can create, replace and drop views, functions and triggers, and rename views and triggers, under the rules in the sections that follow. After OAIZ admits the statement, PostgreSQL decides column types, defaults, checks and index expressions. Admitting the syntax does not make every PostgreSQL facility available. An Owner can install the 12 vetted extensions with CREATE EXTENSION.

    Schema statements OAIZ refuses
    ChangeResultWorking form
    SELECT, INSERT, UPDATE, DELETE, unsupported extensions, procedures, materialized views, CREATE SCHEMA, CREATE DATABASE, CREATE ROLE, GRANT, REVOKE, session commands or transaction commandsRefused before PostgreSQL runs the statement. The message names the statement kind and says it is not a schema change the platform runs.Use a query or write for rows, and OAIZ grants for access. Use a function in place of a procedure, and an ordinary view in place of a materialized view. Changes to platform or session authority have no SQL form. Supported extensions belong to Owner migrations.
    An object outside data, a name in another database, or a name that starts with oaiz_ or pg_Refused. A schema change reaches the data schema. Those prefixes belong to the platform and to the PostgreSQL catalogue.Use a bare name, or data. followed by a name outside those prefixes.
    A temporary or unlogged table, a partition, ON COMMIT, or a tablespaceRefused. The message names the partition, the ON COMMIT action, or the tablespace.Use an ordinary permanent table in data.
    Table inheritanceRefused, because the same rows would answer to both tables’ policies.Use separate tables, or one table with a discriminator column.
    A concurrent index build beside another statement, an unnamed concurrent index, or DROP INDEX CONCURRENTLYRefused. A concurrent build must name its index and be the only statement in its migration.Use one named CREATE INDEX CONCURRENTLY in its own migration, or omit CONCURRENTLY to build inside the transaction. Use DROP INDEX without CONCURRENTLY.
    Disabling row-level security, changing FORCE, naming a new owner, or enabling or disabling triggersRefused. The platform owns row-level security posture, ownership and row-change capture. ENABLE ROW LEVEL SECURITY itself is accepted.Express row access as customer policies and sharing as OAIZ grants.
    A policy that names a role with TORefused. A policy applies to PUBLIC. The message says it names a role, and to leave out TO.Leave out TO, or write TO PUBLIC, and decide who in the expression with the identity functions.
    Session settings, indirect SQL, cluster lookups or role-naming types inside a default, check, generated column, index or domainRefused by the same guards as a query or write.Use an ordinary column expression. A migration cannot change the session identity or inspect the cluster.
    Empty or unreadable SQL, a NUL byte, more than one statement in one slot, or more than 256 operator characters in a rowRefused before the migration runs. Operator characters are counted inside comments and string literals too.Correct the script and stay inside the bounds in Limits, events and backups.

    Views

    A view is a saved SELECT that reads like a table. An Owner creates one in a migration with CREATE VIEW in the data schema. A view always reads as its caller, so the caller’s row rules apply to every table it reads. OAIZ sets security_invoker=true on each view, including one changed with CREATE OR REPLACE VIEW. It refuses a view that turns this setting off, security_barrier views, temporary views, and materialized views.

    The view, function, and trigger examples use a tasks table. The open_tasks view lists the tasks that are not done.

    Create a table and a view
    CREATE TABLE data.tasks (
        id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        owner_id uuid NOT NULL,
        title text NOT NULL,
        status text NOT NULL DEFAULT 'open'
    );
    
    CREATE VIEW data.open_tasks AS
        SELECT id, owner_id, title FROM data.tasks WHERE status <> 'done';

    SQL reads and the query builder read a view by its name, with the same columns, filters, order, and limits as a table. The query builder treats a view as read only and refuses an insert, update, upsert, or delete on it.

    Functions

    A function saves SQL in the data schema so that apps, SQL, and triggers can run it by name. Write it in sql or plpgsql. Each function carries SET search_path = data, extensions, pg_temp. It can also carry the planner settings that SET LOCAL accepts, once per name and within the same ranges, after the extension that defines the setting is installed. A body reads and writes rows with SELECT, INSERT, UPDATE, and DELETE, together with PL/pgSQL variables, conditions, and loops.

    OAIZ refuses other function bodies. This covers EXECUTE and other SQL built from text, SET, RESET, COMMIT, ROLLBACK, CALL, COPY, MERGE, TRUNCATE, and schema changes. It also refuses procedures, a second function with the same name, LEAKPROOF and WINDOW functions, names that start with oaiz_ or pg_, and the names of PostgreSQL built-in and installed extension functions, such as format or similarity. In plpgsql, a record variable takes no default: declare it without one and assign it in the body. Call another function in the data schema with its schema, such as data.score(amount). An unqualified name is checked as a PostgreSQL function, so score(amount) is refused. A body can read the caller’s identity with the oaiz_private identity functions, such as oaiz_private.oaiz_subject() and oaiz_private.oaiz_teams(), and reaches no other platform object.

    Add a task the caller owns
    CREATE FUNCTION data.add_task(p_title text) RETURNS bigint LANGUAGE sql VOLATILE
        SET search_path = data, extensions, pg_temp AS $$
        INSERT INTO data.tasks (owner_id, title)
        VALUES (oaiz_private.oaiz_subject(), p_title)
        RETURNING id
    $$;

    A function runs as its caller, with the caller’s access level and row rules, unless it is SECURITY DEFINER (see below). Its volatility decides who can call it. Mark a function that only reads as STABLE, or IMMUTABLE when its result depends only on its arguments. VOLATILE is the PostgreSQL default and is required for a function that changes rows. An IMMUTABLE function can also compute a generated column or an index.

    PostgreSQL does not inline a function that carries a setting. Because of the required search_path, a sql function runs as its own step and its body is not merged into the query that calls it.

    In plpgsql, RAISE EXCEPTION with a message raises SQLSTATE P0001, and RAISE NOTICE, ASSERT, and a bare RAISE that passes on a caught error work as in PostgreSQL. To raise another SQLSTATE, write it as a five-character code of digits and capital letters in RAISE SQLSTATE or USING ERRCODE, in class 0A, 20, 21, 22, 23, 25, 2D, 2F, 42, 44, 54, or P0. The caller receives the error with its message and hint. OAIZ refuses a condition name such as unique_violation in RAISE or ERRCODE, a code taken from a variable or a query, and a code in any other class, because OAIZ reports those classes as its own failures.

    An EXCEPTION handler can catch any error except the class 57 conditions, such as query_canceled, which a statement raises when it times out or its caller cancels it. OAIZ refuses a handler that names one of them, by name or by SQLSTATE, so a call always stops at its time limit. WHEN OTHERS stays allowed, because PostgreSQL never lets it catch query_canceled.

    Refuse a missing task
    CREATE FUNCTION data.close_task(p_id bigint) RETURNS void LANGUAGE plpgsql VOLATILE
        SET search_path = data, extensions, pg_temp AS $$
    BEGIN
        UPDATE data.tasks SET status = 'done' WHERE id = p_id;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'task % does not exist', p_id USING ERRCODE = 'P0002';
        END IF;
    END
    $$;

    Triggers and audit tables

    A trigger runs a function when rows in a data table change. OAIZ accepts BEFORE and AFTER triggers on INSERT, UPDATE, and DELETE, for each row or for each statement. The trigger function is written in plpgsql and named as data.function_name. OAIZ refuses INSTEAD OF, TRUNCATE, constraint, and deferrable triggers.

    A trigger function runs as the account whose write fired it, so its own writes follow that account’s row rules. The rows a trigger writes commit with the write that fired it and appear in the same row event. OAIZ records each statement’s changes when that statement ends, and each image shows a row as its own statement left it. The rows a row-level trigger writes therefore come before the change that fired it. An AFTER trigger declared FOR EACH STATEMENT fires in name order alongside OAIZ’s capture triggers, whose names start with oaiz_row_capture, so its rows come first when its name sorts before that and after the change otherwise. To change the row being written, use a BEFORE trigger that edits NEW.

    An audit table records each change, and only a trigger adds to it. pg_trigger_depth() is 0 for a write from a request and 1 or more inside a trigger, so pg_trigger_depth() > 0 in a row rule means written by any trigger. The default rules of a new table include everyone_can_add, everyone_can_change, and everyone_can_delete. PostgreSQL accepts a row that passes any permissive rule, so a permissive trigger rule beside everyone_can_add still lets a member add rows directly. Restrictive rules apply on top of the permissive rules and close this path.

    Audit every task change
    CREATE TABLE data.task_audit (
        id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        task_id bigint NOT NULL,
        changed_by uuid,
        operation text NOT NULL
    );
    
    CREATE FUNCTION data.task_audit_write() RETURNS trigger LANGUAGE plpgsql
        SET search_path = data, extensions, pg_temp AS $$
    BEGIN
        INSERT INTO data.task_audit (task_id, changed_by, operation)
        VALUES (NEW.id, oaiz_private.oaiz_subject(), TG_OP);
        RETURN NULL;
    END
    $$;
    
    CREATE TRIGGER task_audit AFTER INSERT OR UPDATE ON data.tasks
        FOR EACH ROW EXECUTE FUNCTION data.task_audit_write();
    
    CREATE POLICY only_triggers_add ON data.task_audit AS RESTRICTIVE FOR INSERT
        WITH CHECK (pg_trigger_depth() > 0);
    CREATE POLICY no_one_changes ON data.task_audit AS RESTRICTIVE FOR UPDATE
        USING (false);
    CREATE POLICY no_one_deletes ON data.task_audit AS RESTRICTIVE FOR DELETE
        USING (false);

    Members with Edit access add and change tasks as before, and each change adds an audit row with the member’s ID. OAIZ refuses a member’s direct insert into task_audit, and a direct update or delete changes no rows.