pista apply¶
Make the database match the schema files.
Synopsis¶
pista apply [option...] file...
Description¶
pista apply computes the same plan that pista plan prints, and runs it. The files, the scope options and the statement options are the same as for plan. So pista plan followed by pista apply with the same arguments runs what the plan showed.
The statements run in this order:
- the pre-SQL,
- the concurrently-pre-SQL,
- the
-- pista:execute-firststatements, - the DDL,
- the
-- pista:executestatements.
When the plan contains no statement, not even a -- pista:execute statement, nothing runs and the output ends with -- No changes. Otherwise the output ends with the time that the apply took:
-- Connected to postgres://postgres@localhost:5432/postgres
-- Apply to schema public (1 table, 0 views, 0 enums, 0 domains, 0 composite types, 0 sequences)
ALTER TABLE public.users ADD COLUMN email text;
-- Apply finished in 12ms
The output is written when the run ends. It lists the statements in the order in which they ran. A failure stops the run. The output then ends at the statement that failed. The statements that already ran remain applied, unless they ran in a transaction. The connection sets search_path to the target schemas plus public, so that an unqualified reference in the DDL resolves.
A drop that the desired schema implies runs only when --allow-drop includes its type. Otherwise the drop is written as a -- skipped: comment.
If an index is invalid (pg_index.indisvalid is false), the output has a -- Warning: index ... is invalid line under the object count. PostgreSQL does not use an invalid index. pistachio does not replace it, because its definition matches. plan writes the same warning. Two common causes:
- A failed
CREATE INDEX CONCURRENTLYleaves an invalid index. Drop it and runapplyagain. - An index created with
ON ONLYon a partitioned table stays invalid until every partition has a matching index attached withALTER INDEX ... ATTACH PARTITION.
Options¶
The general options apply as well.
Connection¶
-cconnstr,--conn-string=connstr- The PostgreSQL connection string, in either form that libpq accepts. The default is
postgres://postgres@localhost/postgres. The environment variable isPISTA_CONN_STR. -ddbname,--dbname=dbname- The database name. It overrides the one in the connection string. The environment variable is
PISTA_DBNAME. --password=password- The password, kept out of the connection string. The environment variable is
PISTA_PASSWORD.
Scope¶
-nschema,--schemas=schema- The schemas to inspect and modify. This option can be given more than once. A name in the files that has no schema is qualified with the first one. The default is
public. The environment variable isPISTA_SCHEMAS. -mold=new,--schema-map=old=new- Read schema old from the database as new. Files written against new then apply to old. This option can be given more than once. Several pairs can also be separated by
;. --search-path=path- The
search_pathfor the connection. The catalog reports an object that is reachable through thesearch_pathwithout its schema. So this option decides which names are reported without a schema. An empty value qualifies every name. The default ispublic. The environment variable isPISTA_SEARCH_PATH. See the notes onplan. --engine=engine- The server the schema is applied to:
postgres, ordsqlfor Amazon Aurora DSQL. The default ispostgres. The environment variable isPISTA_ENGINE. See Aurora DSQL. -Ipattern,--include=pattern- Manage only the objects whose name matches the pattern.
*and?are wildcards. A wildcard pattern must match the whole name./re/is a regular expression. It matches anywhere in the name unless it is anchored. The pattern is matched against the name alone, without the schema. This option can be given more than once. The environment variable isPISTA_INCLUDE. -Epattern,--exclude=pattern- Leave out the objects whose name matches the pattern. The patterns are the same as for
--include. This option can be given more than once. The environment variable isPISTA_EXCLUDE. --enable=type- Manage only these object types:
table,view,enum,domain,composite_type,sequence,routine. This option can be given more than once. It takes precedence over--disable. This option is meant for inspection. It can leave out an object that another object depends on. The environment variable isPISTA_ENABLE. --disable=type- Leave out these object types. The values are the same as for
--enable. This option can be given more than once. The environment variable isPISTA_DISABLE. --manage-routine- Manage functions and procedures. This option is off by default. Dropping them still requires
--allow-drop routine. The environment variable isPISTA_MANAGE_ROUTINE. See Routines. --manage-storage-param- Manage the storage parameters of tables and materialized views, that is, the
WITH (...)clause. This option is off by default. Without it, the clause is ignored on both sides. Thesecurity_barrierandsecurity_invokeroptions of a plain view are managed in both cases. The environment variable isPISTA_MANAGE_STORAGE_PARAM. See Storage parameters. --skip-partition-child- Manage a partitioned table without its partitions. An
INHERITSchild is not affected. The environment variable isPISTA_SKIP_PARTITION_CHILD. See Skipping partition children.
Statements¶
--allow-drop=type- Allow dropping these object types:
all,table,view,enum,domain,composite_type,sequence,routine,column,constraint,foreign_key,index,policy,trigger. This option can be given more than once. Without it, no drop runs. Each drop that is not allowed is written as a-- skipped:comment.constraintcovers CHECK, UNIQUE, PRIMARY KEY and EXCLUSION constraints.foreign_keycovers foreign keys.DROP ATTRIBUTEalso requirescomposite_type. Recreating a view, a routine or a trigger also requires its type. The environment variable isPISTA_ALLOW_DROP. See Controlling drops. --pre-sql=sql- The SQL to run before the DDL. It runs inside the transaction when there is one. This option cannot be used with
--pre-sql-file. The environment variable isPISTA_PRE_SQL. --pre-sql-file=file- The file whose SQL runs before the DDL. The environment variable is
PISTA_PRE_SQL_FILE. --concurrently-pre-sql=sql- The SQL to run after the pre-SQL when the plan contains a
CONCURRENTLYindex statement, for exampleSET lock_timeout. It runs outside any transaction. ASETis session-scoped, so it remains in effect for every later statement of the run. This option cannot be used with--concurrently-pre-sql-file. The environment variable isPISTA_CONCURRENTLY_PRE_SQL. --concurrently-pre-sql-file=file- The file whose SQL runs after the pre-SQL when the plan contains a
CONCURRENTLYindex statement. The environment variable isPISTA_CONCURRENTLY_PRE_SQL_FILE. --disable-index-concurrently- Ignore every
CONCURRENTLYopt-in, both the-- pista:concurrentlydirective and an inlineCREATE INDEX CONCURRENTLY. Run plainCREATE INDEXandDROP INDEXinstead. With this option a schema can keep its directives while one run uses a transaction. This option cannot be used with--force-index-concurrently. The environment variable isPISTA_DISABLE_INDEX_CONCURRENTLY. --force-index-concurrently- Run
CONCURRENTLYon everyCREATE INDEXandDROP INDEX. This includes the drop of an index that the desired schema no longer contains, which no directive can reach. This option cannot be used with--with-tx. The environment variable isPISTA_FORCE_INDEX_CONCURRENTLY. --bulk-alter- Merge consecutive
ALTER TABLEactions on one table into one statement. Only column and constraint actions are merged. Foreign keys,RENAME,VALIDATE CONSTRAINT, row-level security toggles, storage parameters and skipped drops remain separate. The-- pista:bulk-alterdirective does the same for one table. The environment variable isPISTA_BULK_ALTER. --not-null-via-check- Do what
-- pista:not-null-via-checkdoes for every column. This option cannot be used with--with-tx. The environment variable isPISTA_NOT_NULL_VIA_CHECK. --assume-validated- Treat every table constraint, domain constraint and foreign key as validated.
NOT VALIDin the desired schema is ignored. NeitherNOT VALIDnorVALIDATE CONSTRAINTis run. The environment variable isPISTA_ASSUME_VALIDATED. --evaluate-literals- Compare a string literal with a cast by value, not by text. This applies to the
DEFAULTof a column or domain, the expression of a generated column, and aCHECKconstraint on a table or domain. PostgreSQL prints a literal in the output format of its type. For example,DEFAULT '1 hour'on anintervalcolumn is read back as'01:00:00'::interval, so without this option every plan writesSET DEFAULTfor the column. With this option, the literals whose text differs are sent to the server in one query and compared by value. The statements use the text of the desired schema. If a column changes type in the same run, its table is compared by text. The same holds for a domain whose base type changes. The environment variable isPISTA_EVALUATE_LITERALS. --refuse-hazard=kind[,...]- Refuse the plan if it contains a statement of one of the given kinds. kind is
rewrite,scan,blocks-readsorblocks-writes. Nothing is run. The error lists the refused statements, and the exit status is 3. This option cannot be used with--engine dsql. The environment variable isPISTA_REFUSE_HAZARD. The-- pista:refuse-hazarddirective does the same for one table. See Refusing statements.
Execution¶
--with-tx- Run the pre-SQL and the statements in one transaction. This option is refused when the plan contains a
CONCURRENTLYindex statement, because such a statement cannot run in a transaction. It is also refused when the plan addsNOT NULLthrough aCHECKconstraint. ACONCURRENTLYopt-in on an index that does not change does not block this option. This option cannot be used with--try-txor--lock-retries. The environment variable isPISTA_WITH_TX. --try-tx- This option is like
--with-tx. But when the plan contains aCONCURRENTLYindex statement or addsNOT NULLthrough aCHECKconstraint, the plan runs without a transaction instead of being refused. This option cannot be used with--lock-retries. The environment variable isPISTA_TRY_TX. --timing- Write the elapsed time of each statement after it, as a comment. The time is measured on the client, so it includes the round trip and any wait for a lock. The environment variable is
PISTA_TIMING. --exclusive- Make apply runs on the same database mutually exclusive. When another exclusive apply is running, fail at once, before the database is read. This option cannot be used with
--exclusive-wait. The environment variable isPISTA_EXCLUSIVE. See Preventing concurrent applies. --exclusive-wait=duration- This option is like
--exclusive, but it waits up to duration for the other apply to finish.0waits without limit. The value is a Go duration, for example30sor5m. The environment variable isPISTA_EXCLUSIVE_WAIT. --lock-timeout=duration- Run
SET lock_timeoutwith duration before the pre-SQL. A statement that waits longer than duration for a lock then fails. The value is a Go duration, for example500msor5s. It is rounded up to whole milliseconds.0turns the timeout off. Without this option, noSETruns, and the role or database setting applies. ASET lock_timeoutin the pre-SQL overrides this option. The environment variable isPISTA_LOCK_TIMEOUT. --lock-retries=n- Run a schema change again, up to n times, when it fails because
lock_timeoutran out (SQLSTATE55P03). Only the failed statement runs again. The pre-SQL and-- pista:executestatements are not retried. ACONCURRENTLYindex statement is not retried either, because a failed one can leave an invalid index. This option has no effect unlesslock_timeoutis set, by--lock-timeout, the pre-SQL or the role. It cannot be used with--with-txor--try-tx. The default is 0. The environment variable isPISTA_LOCK_RETRIES. --lock-retry-delay=duration- The time to wait before each retry of
--lock-retries. The default is1s. The environment variable isPISTA_LOCK_RETRY_DELAY. --statement-timeout=duration- Run
SET statement_timeoutwith duration before the pre-SQL. A statement that runs longer than duration, including any wait for a lock, then fails. It is not retried.0turns the timeout off. The timeout also applies to aCONCURRENTLYindex build. To give the build another value, set it in--concurrently-pre-sql. The environment variable isPISTA_STATEMENT_TIMEOUT. --dsql-no-wait-job- Do not wait for an index build or a constraint validation to finish before the next statement runs. This option requires
--engine dsql. The environment variable isPISTA_DSQL_NO_WAIT_JOB. See Aurora DSQL.
Exit status¶
The exit status is 0 when every statement ran, or when there was nothing to run. It is 1 on error, 3 when --refuse-hazard or -- pista:refuse-hazard refuses the plan, and 80 on a usage error.
Environment¶
Every option names its variable above. PISTA_CONFIG and PISTA_PAGER are described under Commands.
Notes¶
Transactions¶
Without --with-tx or --try-tx, each statement commits on its own. --with-tx wraps the pre-SQL and every statement in one transaction. The output marks it with -- Transaction started and -- Transaction committed. --with-tx is refused when the plan contains a CONCURRENTLY index statement. In that case --try-tx runs without a transaction and writes -- Transaction skipped: plan contains CONCURRENTLY index DDL. See Transactions and locks.
Timing¶
--timing writes a -- Time: comment after every statement that the run writes out:
- the pre-SQL,
- the concurrently-pre-SQL,
- the
-- pista:execute-firstand-- pista:executestatements, - the DDL,
BEGINandCOMMIT.
The search_path setup and the check SQL of a directive are not written out, so they are not timed.
-- Transaction started
-- Time: 0.264 ms
ALTER TABLE public.users ADD COLUMN email text;
-- Time: 0.326 ms
CREATE INDEX idx_users_email ON public.users USING btree (email);
-- Time: 4213.882 ms
-- Transaction committed
-- Time: 0.921 ms
With --with-tx, work that PostgreSQL defers to commit is counted on COMMIT, not on the statement that caused it. A statement that fails gets no time comment, so the output shows where the run stopped.
Examples¶
Apply, with drops of columns and tables allowed:
pista apply --allow-drop column,table schema.sql
Apply in one transaction, with a statement timeout:
pista apply --with-tx --statement-timeout=5s schema.sql
Stop a lock wait after 500 milliseconds, and try the statement again up to five times, 10 seconds apart:
pista apply --lock-timeout=500ms --lock-retries=5 --lock-retry-delay=10s schema.sql
Build indexes concurrently with a lock timeout, and time each statement:
pista apply --try-tx --concurrently-pre-sql "SET lock_timeout = '5s';" --timing schema.sql
Refuse to run beside another apply, or wait for it:
pista apply --exclusive schema.sql
pista apply --exclusive-wait=5m schema.sql
See also¶
pista plan, pista apply-from, Directives, Transactions and locks, Running arbitrary SQL