Pg-Meta DO Blocks Fail When Identifier Contains $$
pgMeta.tables.update (primary_keys path) and pgMeta.columns.update (is_unique:false and check paths) build DO $$ ... $$ blocks that embed identifiers via ident(), causing syntax error when any identifier contains the literal byte sequence $$.
The DO blocks use a fixed dollar-quote delimiter $$ without checking if any embedded identifier (table/schema/column name) contains that same delimiter sequence. PostgreSQL's dollar-quote parser terminates the outer block at the first occurrence of $$ inside an identifier, truncating the statement and leading to a syntax error.
1. Create a table with a name containing $$, e.g., CREATE TABLE public."weird$$name" (id int primary key, val text);
2. Call pgMeta.tables.update({ name: 'weird$$name', schema: 'public' }, { primary_keys: [] });
3. Observe error: syntax error at or near "name".
4. Similarly, call pgMeta.columns.update for a column on such a table with { is_unique: false } or { check: ... } to trigger the column update paths.
Fixing Code Block
// In pg-meta-tables.ts add helper:
function getDoBlockDelimiter(values: string[]): string {
let i = 1;
while (values.some(v => v.includes(`$${i}$`))) {
i++;
}
return `$${i}$`;
}
// In tables.update primary_keys path, replace the DO block delimiter:
const delimiter = getDoBlockDelimiter([old.schema, old.name]);
const sql = `
do ${delimiter}
declare r record;
begin
select conname into r from pg_constraint
where contype = 'p' and conrelid = ${old.schema}.${old.name}::regclass;
if r is not null then
execute 'ALTER TABLE ${ident(old.schema)}."${old.name}" DROP CONSTRAINT ' || quote_ident(r.conname);
end if;
end
${delimiter};
`;
// In pg-meta-columns.ts add the same helper.
// In columns.update is_unique:false path, use:
const delimiter = getDoBlockDelimiter([old.schema, old.table]);
// replace do $$ with do ${delimiter} and trailing $$ with ${delimiter} in the UNIQUE-drop DO block.
// In columns.update check path, use:
const delimiter = getDoBlockDelimiter([old.schema, old.table, old.name]);
// similarly replace the DO block delimiters in the CHECK-replace block.
The helper generates a dollar-quote delimiter (e.g., $1$, $2$, ...) that does not appear in any of the provided identifier strings. By using this dynamic delimiter in the DO blocks, the outer block remains intact even when identifiers contain $$, preventing premature termination.
Edge Case Audit
Ensure getDoBlockDelimiter is called with all values that will be embedded into the DO block body. Future modifications adding new embedded identifiers must include them in the values array, otherwise the bug may resurface. The helper loops until a delimiter is found; with many unique delimiters or a large number of values this could theoretically be O(n^2) but is negligible for typical identifier counts. Rollback should revert to the previous fixed $$ delimiters if issues arise, but that reintroduces the original bug; instead prefer keeping this fix and extending the helper coverage if new embedding points are added.
