7. Commands

Commands are the basic building blocks out of which Steps are defined. Every Step is defined as a series of Commands, and every Instance runs this series of Commands until one fails.

7.1. Limit your output

Note that PgQuartz basically keeps track of all StdOut and StdErr, and that can require a lot of memory. PgQuartz has no special handling, flushing to disk capabilities, or other options. Therefore it is crucial for developers of PgQuartz jobs to make sure output is limited options like tail and LIMIT!!!

7.2. Configuration options

7.2.1. Name

Every Command has a name, which is used in output and logging. Note that emptystring (default) is accepted as Name as well.

7.2.2. Role

Roles can be set per Connection, but can be overruled per Command. For more information, please refer to Roles on connections.

7.2.3. BatchMode

As a convenience option SQL Queries (Command bodies run against PostgreSQL connections) can be run in batchMode, which means they are split by semicolons and then run one at the time. This splitting is done in a very crude manner, and as such below examples also act as separator characters

such semicolons within SQL strings (like “My text with ;”)
SQL Names (‘my name with ;’)
semicolons used inside PL/PgSQL and other (SQL) code blocks

Note that specifying batchMode: true is not encouraged because:

As mentioned before, the implementation is very crude and can easily mess up commands
there is no technical downfall to specifying every Query as a separate Command
there is upside to specifying every Query as a separate Command, because all other configuration option like Name and Role can be set differently for every separate Commands

7.2.4. Command types

The type field of a command can have 2 types of values:

shell (default), which means ‘execute this command in a terminal shell’
Any name of a Connection, which means run this command as a SQL Query on the specified connection.

Note that PgQuartz verifies the Job definition before running the job, and errors out if anything else is specified as a Command type.

7.2.5. Inline or file

Command bodies can be either specified inline, and the effect depends on the type of command:

Inline type bodies against PostgreSQL connections are directly run against the connection
File type bodies against PostgreSQL connections are read from file into memory and then run against the connection
Inline type bodies of type shell are written to a file from memory and the file is executed in a shell
File type bodies of type shell are directly run in a shell

7.3. Example

We make the ‘Commands concept’ more tangible with an example:

7.3.1. Example config

steps:
  step 1:
    commands:
      - name: Run command 1.1
        type: pg
        inline: |
          CREATE TABLE IF NOT EXISTS t1 (id int, txt text);
          CREATE TABLE IF NOT EXISTS t2 (id int, id2 int);
      - name: Run command 1.2
        type: shell
        inline: "echo 'Done that' > /tmp/beenhere.txt"
      - name: Run command 1.3
        type: pg
        file: ./sql/step_1.3.sql
      - name: Run command 1.4
        type: shell
        file: ./bash/step_1.4.sh
connections:
  pg:
    type: postgresql
    conn_params:
      host: /tmp

7.3.2. What does it do?

When running a job with a specification as shown in the example, PgQuartz will do the following:

PgQuartz will create a work queue and add only one Instance of step 1 to that queue.
PgQuartz will create at least one Runner.
the Runner starts processing the Instance:
- Command 1.1 is run against the PostgreSQL connection and
  - (unless it already exists) the table t1 is created
  - (unless it already exists) the table t2 is created
- If Command 1.1 succeeded, the runner will run Command 1.2 in a shell and add a line to the file
- If Command 1.2 succeeded, the runner will run Command 1.3, read the actual command from the specified file and run it against the PostgreSQL connection
- If Command 1.3 succeeded, the runner will run Command 1.3, read the actual command from the specified file and run it in a shell