osh is a great drop in bash replacement

Nine Reasons to Use OSH oils.pub

I’ve starting using osh for testing my bash scripts (mostly used in build and deployments). It’s been great for both giving better error messages and spotting problems shellcheck mightn’t catch.

This has been my favourite so far:

osh -o strict:all ./myscript.sh
    if assert_task_definition_is_older "${task_definition}" "deployed"; then
    ^~
myscript.sh:119: errexit was disabled for this construct

    if assert_task_definition_is_older "${task_definition}" "deployed"; then
       ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
myscript.sh:119: fatal: Can't run a proc while errexit is disabled. Use 'try' or wrap it in a process with $0 myproc

This leads to this interesting note on how errexit and if statements invoking shell functions lead to surprising behaviour (basically errexit is disabled).

PostgreSQL NOT NULL can be added without locking

There’s bit of an interesting rabbit hole with NOT NULL constraints vs locking up your DB.

It starts with “is a NOT NULL check contraint worse than a NOT NULL”?

The docs hint at some difference: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-NOT-NULL

SO thread: https://dba.stackexchange.com/questions/158499/postgres-how-is-set-not-null-more-efficient-than-check-constraint

Which points to https://dba.stackexchange.com/questions/66840/add-not-null-constraint-to-large-table-without-table-scan

Which finally points to https://dba.stackexchange.com/questions/267947/how-can-i-set-a-column-to-not-null-without-locking-the-table-during-a-table-scan/268128#268128

The big problem is adding a NOT NULL constraint can badly lock up the table and other sessions.

Using a constraint with NOT VALID and then validating later is a work around.

There’s a third step you can do: add a NOT NULL after this an PostgreSQL will do a no-lock update.

First add the constraint:

-- short-time exclusive lock
alter table foos 
  add constraint foos_not_null 
  check (bar1 is not null) not valid;

Next validate it:

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

Finally add not null and drop constraint:

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

There is also an approach of adding a NOT NULL with a default value, PostgreSQL optimizes this case: https://brandur.org/postgres-default

ALTER TABLE users
    ADD COLUMN credits bigint NOT NULL DEFAULT 0;