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.
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.
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.
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.
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 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 id, name
FROM customers
WHERE id = $1;
SELECT id, name
FROM customers
WHERE id = $1A 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.
SELECT id, name FROM customers WHERE id = $2Two 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.
SELECT id, name FROM customers WHERE id = $1Two 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.
UPDATE customers
SET status = 'lost'
RETURNING id, idDistinct names for a join
customer_id and account_id are different result names, so the query returns both values.
SELECT customers.id AS customer_id, accounts.id AS account_id
FROM customers
JOIN accounts ON accounts.customer_id = customers.id