Display detailed explanation about tables affected by statement

Start by tracing the existing messages for foreign-key constraints, SET NOT NULL, and CREATE INDEX statements, using the SQL examples in the issue as entry points. Done means those cases report the affected table names, blocking behavior, and the requested suggestions, including the linked documentation where specified.

As mentioned in the adding-foreign-key-constraint rule, adding a foreign key like the following will lock both tables.

ALTER TABLE "email" ADD CONSTRAINT "fk_user"
    FOREIGN KEY ("user_id") REFERENCES "user" ("id");

The current error message is general and says,

help: Requires a table scan of the table you're altering and a SHARE ROW EXCLUSIVE lock on both tables, which blocks writes to both tables while your table is scanned

Instead, we can provide a more detailed error message:

help: statement will block writes to the "user" and "email" tables while the "email" table is scanned to verify the foreign key constraint.

ALTER TABLE "core_recipe" ALTER COLUMN "foo" SET NOT NULL;

current message:

note: Setting a column NOT NULL blocks reads while the table is scanned.
help: Use a check constraint instead.

better message:

note: Setting the column NOT NULL will block reads and writes to the "core_recipe" table while the table is scanned to verify the NOT NULL constraint.
suggestion: Use a check constraint added as NOT VALID instead. See https://squawkhq.com/docs/adding-not-nullable-field for examples.

CREATE INDEX CONCURRENTLY "email_idx" ON "app_user" ("email");

current message:

help: Consider wrapping in a transaction or adding a IF NOT EXISTS clause if the statment supports it.

better message:

note: Statement will error if index already exists
suggestion: create index with IF NOT EXISTS. For example, CREATE INDEX CONCURRENTLY IF NOT EXISTS ...

CREATE INDEX "email_idx" ON "app_user" ("email");

current message:

note: Creating an index blocks writes.
help: Create the index CONCURRENTLY.

better message:

note: Writes will be blocked for the "app_user" table while the index in built.
suggestion: Create the index CONCURRENTLY to prevent blocking writes. For example, CREATE INDEX CONCURRENTLY ...