Search documentation

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

    Docs / Databases / Troubleshooting

    Troubleshooting

    Find why a statement or row policy was refused, what stayed unchanged and which supported form to use.

    Compare an enum as text

    To filter an enum-valued status in a row policy, compare status as text. Run this migration as an Owner without an existing deal_status type or deals table.

    status::text stays on text, which the policy grammar allows. The migration keeps the type, the table, the index and the rule, and it removes everyone_can_see. An editor sees the open deal. A database Owner still sees the lost deal, because the platform Owner rule is permissive.

    Compare status as text
    CREATE TYPE deal_status AS ENUM ('open', 'won', 'lost');
    CREATE TABLE deals (
      id bigint PRIMARY KEY,
      status deal_status NOT NULL
    );
    CREATE INDEX deals_status_idx ON deals (status);
    DROP POLICY everyone_can_see ON deals;
    CREATE POLICY open_deals ON deals FOR SELECT
      USING (status::text <> 'lost');

    Common SQL mistakes

    Extra parentheses around the team list
    The migration is refused before PostgreSQL runs it. Use the direct team rule or the cast team rule.
    TRUNCATE sent as a write
    The write is refused and the rows stay. An Owner runs TRUNCATE in a migration. DELETE is the row operation.
    Two statements in one call
    The call is refused and the rows stay. Send one statement per call.
    An enum compared with a literal
    PostgreSQL stores a cast to the enum, and OAIZ then refuses the stored rule. Compare the column as text.
    TO current_user on a policy
    The migration is refused and the table is not created. Leave out TO and decide who in the expression.
    A table named oaiz_notes
    The name is refused. Use a name that does not start with oaiz_ or pg_.
    A gap in $1, $2, $3
    The call is refused before PostgreSQL runs it. Number the placeholders from $1 with no gap.
    More values than placeholders
    The call is refused and the rows stay. Supply one value per placeholder.
    RETURNING id, id
    PostgreSQL runs the statement, OAIZ refuses the result, and the write rolls back. Alias the columns.
    A narrower permissive rule beside everyone_can_see
    The migration succeeds, and the other account can still read and update the row. Replace the broad rule or add a restrictive rule.

    A team comparison read as a set

    The extra parentheses make ANY a comparison against a set of rows. OAIZ refuses the migration before PostgreSQL runs it. The message says the policy expression is outside the permitted grammar: it uses a subquery other than (SELECT oaiz_private.<identity function>()). The same comparison, outside a policy, fails in PostgreSQL with operator does not exist: uuid = uuid[]. That operator error is PostgreSQL’s own result. OAIZ does not offer that comparison as a supported statement. Use the direct team rule or the cast team rule.

    A team subquery with an extra pair of parentheses
    CREATE TABLE deals (
      id bigint PRIMARY KEY,
      title text NOT NULL,
      owner_team uuid NOT NULL
    );
    CREATE INDEX deals_owner_team_idx ON deals (owner_team);
    DROP POLICY everyone_can_see ON deals;
    DROP POLICY everyone_can_add ON deals;
    DROP POLICY everyone_can_change ON deals;
    DROP POLICY everyone_can_delete ON deals;
    CREATE POLICY deals_select_team ON deals FOR SELECT
      USING (owner_team = ANY ((SELECT oaiz_private.oaiz_teams())));

    An enum comparison stored as a cast

    OAIZ accepts a direct comparison of status with the literal lost at first. PostgreSQL stores the comparison as a cast to deal_status. OAIZ reads that stored rule and refuses the migration, so the type and the table are not kept. The message names the policy and the table, says the rule is as PostgreSQL stored it, and says the cast to deal_status is not a permitted type. Compare the column as text.

    Compare an enum value directly
    CREATE TYPE deal_status AS ENUM ('open', 'won', 'lost');
    CREATE TABLE deals (
      id bigint PRIMARY KEY,
      status deal_status NOT NULL
    );
    CREATE POLICY lost_deals ON deals FOR SELECT
      USING (status = 'lost');

    A policy that names a role

    TO current_user names a role. OAIZ refuses the script, and the table is not created. The message says the schema change statement is outside the permitted grammar: it names a role, and a policy applies to PUBLIC: leave out TO and decide who in the expression with the identity functions.

    Name the current role on a policy
    CREATE TABLE notes (
      id bigint PRIMARY KEY
    );
    CREATE POLICY readers ON notes FOR SELECT TO current_user
      USING (true);

    A name in a reserved prefix

    oaiz_notes starts with oaiz_. OAIZ refuses the name. The message says the table name "oaiz_notes" is inside the prefix the platform reserves for its own objects. A name that starts with pg_ is reserved for the PostgreSQL catalogue.

    Create a table in the platform prefix
    CREATE TABLE oaiz_notes (
      id bigint PRIMARY KEY
    );

    Two statements in one call

    A query or write holds one statement. The message says the statement is outside what a managed database query or write runs: it holds 2 statements, and a query or write is one. The rows stay. Send each statement in its own call. Those calls do not share a transaction.

    Select the same row twice
    SELECT id, name
    FROM customers
    WHERE id = $1;
    SELECT id, name
    FROM customers
    WHERE id = $1

    A placeholder that skips $1

    The statement uses $2 and the call supplies two values. OAIZ refuses it before PostgreSQL runs it. The message says the statement skips placeholder $1. Number the placeholders from $1 through the last one with no gap. The rows stay.

    Filter on $2 and skip $1
    SELECT id, name FROM customers WHERE id = $2

    Two values for one placeholder

    The statement uses $1 and the call supplies two values. The message says the statement is outside what a managed database query or write runs: its highest placeholder is $1, and the parameter count is 2. The rows stay.

    Bind one customer id
    SELECT id, name FROM customers WHERE id = $1

    Two columns with one name

    RETURNING id, id runs in PostgreSQL and is then refused. The message says the statement is outside what a managed database query or write runs: it returns two columns with the same name. The write rolls back, so the stored status stays unchanged.

    Return id twice
    UPDATE customers
    SET status = 'lost'
    RETURNING id, id

    Distinct names for a join

    customer_id and account_id are different result names, so the query returns both values.

    Alias both sides of a join
    SELECT customers.id AS customer_id, accounts.id AS account_id
    FROM customers
    JOIN accounts ON accounts.customer_id = customers.id