Run, cancel, and transactions

Read this before you run something that writes, or when a query has been running longer than you expected. It covers what each key runs, how to stop it, and how to keep a change open until you have looked at the result.

Run a statement

1. Put the cursor anywhere inside the statement, or select the text you want.
2. Press ⌘↩ on macOS or Ctrl+Enter on Windows.

That is Run selection or current statement, the Run button in the editor's toolbar. To run every statement in the editor, press ⇧⌘↩ or Ctrl+Shift+Enter: Run entire script, the Run all button. Run all sends the whole script as one request and the server runs its statements in order.

Read the result pane

The editor's footer carries the run: Running with a timer counting up, then Completed, Failed or Cancelled, with the rows loaded, the rows affected, the time, and N notices when the server sent any.

The pane under the editor is headed Result and Plan, then N rows · M columns. A script with several result sets shows a Result set group numbered 1, 2, 3; pick one to see its rows. Notices, such as a RAISE NOTICE from a function, are listed under Run details as N notices, each marked with its Statement N.

A failed run keeps its time and shows the error above the grid. On PostgreSQL that is the message, its SQLSTATE, and the Detail, Hint and Context the server sent, with a marker in the editor at the position it reported. A script failure reads "Statement 2 of 3 failed at line 4", then the message.

Plan switches from the rows to how the database got them. Plan is Pro, and Why is this query slow? reads one. Sorting and filtering a result happen in memory. Nothing is re-run.

The row cap

Seqora keeps at most Rows kept per result rows of each result, and stops earlier if the rows grow too large in bytes. The default is 10,000, and the other choices are 1,000, 50,000 and 100,000. Run SELECT * FROM commerce.orders and the grid holds the first 10,000 of 12,000,000, under the banner "Showing the first 10,000 rows · 11,990,000 more were not kept." The banner's Change limit button opens the setting.

What happens to the rest depends on where you ran it. On a locked connection, outside a transaction, Seqora stops a plain read on the server at the cap, with pg_cancel_backend on PostgreSQL and KILL QUERY on MySQL and MariaDB, and the footer reads Row limit reached · stopped on the server after 10,000 rows. The total is unknown then. Anywhere else the server sends every row and Seqora reads and drops them, so the footer can say 11,990,000 more not kept. On PostgreSQL the stop also waits until the session has written nothing, so a SELECT that calls a writing function is never cut short. Add a LIMIT when you only want to look.

Cancel

1. Click Cancel run. It sits where Run was while a query runs.

The button reads Cancellation requested… until the server stops, and then the statement's status becomes Cancelled. The palette has the same action as Cancel running query.

For a limit that doesn't need you watching, set Settings › Connections and Safety › Statement timeout: Off by default, or 30 seconds up to an hour. On PostgreSQL it is statement_timeout for the editor's session. On MySQL, MariaDB and SQLite, Seqora stops the statement at the deadline the same way Cancel run does.

Hold a change in a transaction

The badge next to the run buttons shows the editor's transaction state, and only the buttons that make sense for that state are shown.

Refresh transaction asks the server again in any state.

1. Click Begin. The badge reads Transaction open.
2. Run UPDATE commerce.orders SET status = 'cancelled' WHERE id = 81723;.
3. Check the result: one row affected, and a SELECT shows the new status.
4. Click Commit to keep it, or Rollback to "Discard every change since Begin".

Begin and Commit are disabled on a locked connection. Unlock it first, as Connect explains.

On MySQL and MariaDB the server's error replies don't carry the transaction state. A plain statement that fails in autocommit leaves the badge at Autocommit. An error inside a transaction, with autocommit off, or in a CALL or EXECUTE, leaves it at State unknown: Run, Run all, Begin and Commit are disabled until Rollback or Refresh transaction settles it.

An editor with an open transaction can't be closed quietly. Closing its tab, or its pane in a split, opens Transaction still open with Keep editing and Roll back and close; a query still running opens Query still running with Cancel query and close. Changing the editor's connection, or disconnecting, is refused with "Commit or roll back the open transaction before changing this editor’s connection."

Review high-risk SQL

Some statements stop and ask before they run, in every environment:

• statements starting with DROP, TRUNCATE, ALTER, CREATE, GRANT, REVOKE, COPY, DO, CALL, VACUUM or EXPLAIN ANALYZE
• a WITH query that writes
• an UPDATE or DELETE with no top-level WHERE: "Mutation has no WHERE clause."
• anything Seqora can't classify, ANALYZE among them: "Seqora could not prove this statement is read-only."

Run UPDATE commerce.orders SET status = 'cancelled'; and this happens:

1. Read the Review high-risk SQL panel, which opens inside the editor. It gives the reason and the target as environment, connection and database, for example prod / commerce-primary / commerce, with the schema after it when one is selected.
2. Type RUN in the box under "Type RUN to confirm". The box has focus, so nothing you type lands in the SQL.
3. Press Enter or click Confirm and run. Esc, Cancel or the toolbar's Cancel run leaves the statement unrun.

Settings › Connections and Safety › Confirm high-risk SQL changes the second step: Type RUN to confirm is the default, Confirm with a click drops the typing, and Run without asking turns the panel off. A locked connection still refuses writes either way.

Format and save

Format in the toolbar, ⇧⌥F or Shift+Alt+F, re-indents the editor. ⌘S or Ctrl+S is Save SQL, which names the editor and keeps it as a .sql file. Both are covered in History and SQL files.

Caveats

• On PostgreSQL each SQL editor holds two server connections: the editor's own and a read-only one that watches its transaction. Ten editors use twenty of max_connections.
• In MySQL and MariaDB, a DDL statement such as ALTER TABLE commits the open transaction. Rollback won't undo it.
• The MySQL and MariaDB editor refuses SET NAMES and other character set changes, RESET, a bare DELIMITER, and executable comments, because they would change how Seqora reads the connection. A stored program with a BEGIN … END body must be wrapped in DELIMITER $$ … $$, and a SET sql_mode must be the last statement of its script. SET session variables, PREPARE and EXECUTE run after the high-risk review.
• SQLite refuses ATTACH and DETACH, allows a fixed list of PRAGMAs on a locked connection, and rejects statements with unbound parameters: "Unbound SQL parameters are not supported."
• Cancelling stops the statement that is running. In autocommit, the statements of a script that finished before it stay committed, as they would in psql.