Work on production without breaking it

A connection in the production environment opens read-only, asks before risky statements, and stops statements that run or wait too long. This page shows how to make one change there and get back to safe.

To put a connection in production, set its environment. See Projects, environments and production.

Read-only until you unlock

A production connection shows a red Production banner for as long as it is open. While the connection is locked, the banner reads:

Read-only. Nothing you do here can change data.

Locked: the banner while the connection is read-only.

The status bar says Read-only too. While it is locked, you can browse, run queries that only read, and explain them. The grid does not offer editing, and actions that write, such as Terminate session in Activity, are left out of their menus. A statement that writes is refused before it reaches the server: an INSERT, UPDATE, DELETE, MERGE, COPY or a structure change. See ALC-3001 for every message.

Alcuin also opens the connection’s sessions with default_transaction_read_only on, so a write it cannot see in the text, such as a function that writes, is refused by the server itself.

Make one change

Run a single write once

When you run a write on a locked connection, Alcuin asks:

Run this once on production?

This connection is read-only. Run this once? The connection stays read-only afterward. A read-only database role is the only hard guarantee.

• Run once runs that one request on a separate session with read-only off, then closes it. The connection stays locked.
• Unlock connection opens the unlock dialog below.

Run once runs this statement and leaves the connection read-only.

When your rules also ask about the statement, the same dialog says so, for example It also drops, truncates or changes every row.

Committing staged changes asks the same question, titled Apply this once on production?, with Apply once.

Run once is not offered inside an open transaction, or for a statement that would turn read-only off.

Unlock for a while

1. Click Unlock editing… in the banner, or choose it in the command palette or the connection’s menu.
2. Read the dialog, Unlock editing on production?, and confirm with Unlock.

The banner turns to Editing is unlocked: read-only again in 14:59. Changes here reach real data. and counts down.

Unlocked: the banner counts down to read-only.

When the time is up, the connection locks again and a message says The connection is read-only again. If a query tab has a transaction open then, the banner says Editing is unlocked. A tab has a transaction open: the connection locks again once it ends. Alcuin looks again every 15 seconds, so the connection locks within 15 seconds of your commit or rollback.

Lock again

Click Lock in the banner, or choose Lock editing in the command palette, as soon as you are done. Closing the connection locks it too. Nothing is remembered: the next time you open it, it is read-only again. It also locks again if Alcuin’s engine restarts.

The questions that guard writes

Even unlocked, Alcuin shows you certain statements and waits for a yes. On production, it asks before:

• Destructive statements: DROP, TRUNCATE, an UPDATE or DELETE with no WHERE, deleted rows and dropped structure.
• Structure changes: CREATE, ALTER, COMMENT and GRANT.
• Code that cannot be read: a DO block, CALL or EXECUTE, whose effect is not in its text.
• Scripts that cannot be parsed: when the parser cannot read a script, nothing about it is known.
• Large results: a query with no LIMIT that the planner expects to return more than 100,000 rows.

The dialog is titled Run this on production? or Apply this to production?. It says what the statements do and lists them. Read them, then confirm with Run on production or Apply to production, or cancel. A cancel runs nothing, and staged changes stay staged. ALC-3004 lists every wording.

You can add Ask before: every write, which asks before any INSERT, UPDATE, DELETE, MERGE, COPY … FROM or edited row.

Staged edits go through the review before any of this: you see the exact statements before you commit. See Edit, add and delete rows.

Time limits

On production, Alcuin sets two limits on every session:

SET statement_timeout = 30000;
SET lock_timeout = 3000;

• Stop statements after, 30 s: a statement still running then is stopped by Postgres, and you see ALC-2002.
• Give up waiting for a lock after, 3 s: a statement that would queue behind another session’s lock fails instead. A query tab shows Postgres’ canceling statement due to lock timeout (SQLSTATE 55P03). A commit of staged changes says Another transaction holds a lock these changes need, so nothing was changed. Structure changes that run in steps say …so it stopped there. when some steps had already committed. Without the limit, an ALTER TABLE waiting on a busy table would block every query that comes after it. The status bar shows lock timeout 3 s while it applies.

To see who holds the lock, open Activity. See Find what is stuck.

A profile can set a shorter statement limit of its own in Stop queries after, in the connection’s settings.

Alcuin also says when a query tab’s transaction has been open for 1 minute: the status bar shows it, and a message offers Commit, Roll back or Keep open. Nothing ends it on its own.

Change the rules

Open Settings, then Safety, and choose Production in Rules for. Production’s rules can only be made stricter: you can add questions, shorten the limits or the unlock time, never loosen them. Safety rules per environment lists the defaults for each environment.

Read-only and the time limits apply to connections opened next. The questions apply from the next statement. The Settings reference lists every row.

Caution

Every check here is Alcuin’s. A role with only the grants it needs is the one guarantee the server keeps whatever client connects. For reading production, connect with a role that cannot write.

How you know it worked

• The banner reads Read-only. Nothing you do here can change data. whenever you are not making a change.
• Each write you made was either a Run once, or happened while the banner counted down.
• Every risky statement was shown to you before it ran.

If a write was refused, the message tells you which guard stopped it: ALC-3001 for read-only, ALC-3004 for a confirmation, ALC-2002 for a time limit. How Alcuin protects your data explains the whole model.