Fails to detect ACCESS EXCLUSIVE lock risk when DDL is inside DO $$...$$; PL/pgSQL block

[nathiesha-maddage]

Problem Description:

Squawk does not appear to detect issues, when a problematic DDL is embedded within a do $$...$$; PL/pgSQL block.

Observed Behavior:

Direct DDL (Detected): When Squawk is run on the following direct DDL, it correctly identifies the ACCESS EXCLUSIVE lock risk:

SQL

ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(512);

warning[changing-column-type]: Changing a column type requires an ACCESS EXCLUSIVE lock on the table which blocks reads and writes while the table is rewritten. Changing the type of the column may also break other clients reading from the table.
  |
1 | ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(512);
  |                                      -----------------
  |

Find detailed examples and solutions for each rule at https://squawkhq.com/docs/rules
Found 1 issue in 1 file (checked 1 source file)

DDL within do $$...$$; Block (NOT Detected): When the same DDL is wrapped in a do $$...$$; block with a conditional check, Squawk does not return a warning/error.

SQL

do $$
    begin
        if ( SELECT character_maximum_length < 512
                 FROM information_schema.columns
                 where table_name = 'users'
                   and column_name = 'email')
        then ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(512);
        end if;
    end;
$$;

Are there any configurations/flags that need to be included to analyse complex queries like the above?

[sbdchd]

Thanks for the report!

We don't currently parse PL/pgSQL but it's on the roadmap.

I was looking at the grammar for it and it's thankfully only 4k LOC:

https://github.com/postgres/postgres/blob/f9b1192190aac56a319a1e3e93cf3c29d9f722dd/src/pl/plpgsql/src/pl_gram.y#L4244

Instead of PostgreSQL's 19k LOC: https://github.com/postgres/postgres/blob/f9b1192190aac56a319a1e3e93cf3c29d9f722dd/src/backend/parser/gram.y#L19798

related: https://github.com/sbdchd/squawk/issues/411