Apostrophes in quoted identifiers break desired-state qualification and can produce an unexpected DROP TABLE plan #671

Summary

An apostrophe inside a valid double-quoted identifier breaks desired-state schema qualification handling. Two independent inputs expose the problem:

1. A schema named Tenant'B produces a successful plan that drops a table explicitly present in desired SQL.
2. An index named items'idx in an ordinary mixed-case schema makes scratch application fail with 42P01 when its table reference is qualified.

The controls use the same identifiers with unqualified desired SQL; native plan/apply then creates the index and the next plan is empty.

Environment and verification

• pgschema v1.13.4, the latest release checked on October 27, 2026.
• PostgreSQL 17.11, Linux, disposable local databases, external planning database.
• Native pgschema plan reproduced both failures. Native apply and an empty retry verified the unqualified controls. The unexpected DROP TABLE plan was not executed.
• The relevant source is unchanged on current main at 4b3bd0f693b22f7490b114e1c9e34f8670902470. This is source verification, not a claim that an unreleased-main binary was executed.

Use two fresh disposable databases and an empty working directory without .pgschemaignore. The examples assume a local PostgreSQL administrator; adjust host, port, and user as needed:

export PGHOST=127.0.0.1 PGPORT=5432 PGUSER=postgres PGSSLMODE=disable
createdb repro_target
createdb repro_plan

Reproduction A: apostrophe in the schema name

before.sql:

CREATE SCHEMA "Tenant'B";
CREATE TABLE "Tenant'B".entries(id integer);

desired.sql:

CREATE SCHEMA "Tenant'B";
CREATE TABLE "Tenant'B".entries(id integer);
CREATE INDEX "items_idx" ON "Tenant'B".entries(id);

psql -X -v ON_ERROR_STOP=1 -d repro_target -f before.sql
pgschema plan --db repro_target --schema "Tenant'B" \
  --file desired.sql \
  --plan-host "$PGHOST" --plan-port "$PGPORT" --plan-db repro_plan \
  --plan-user "$PGUSER" --plan-sslmode disable \
  --output-json plan.json --output-sql plan.sql --no-color

Actual result

plan exits 0 and writes only:

DROP TABLE IF EXISTS entries CASCADE;

Do not apply that plan. The target already contains entries, and desired SQL explicitly retains it and adds one index.

Expected result

Only creation of items_idx; the existing table must remain desired. If preprocessing cannot safely handle the identifier, planning should fail instead of returning a destructive plan.

Passing control

Keep the same target and --schema "Tenant'B", but use:

CREATE TABLE entries(id integer);
CREATE INDEX items_idx ON entries(id);

Plan/apply succeeds and a fresh plan is empty.

Reproduction B: apostrophe in the index name

Use fresh target and planning databases (or recreate the two disposable databases), with:

before.sql:

CREATE SCHEMA "TenantB";
CREATE TABLE "TenantB".entries(id integer);

desired.sql:

CREATE SCHEMA "TenantB";
CREATE TABLE "TenantB".entries(id integer);
CREATE INDEX "items'idx" ON "TenantB".entries(id);

psql -X -v ON_ERROR_STOP=1 -d repro_target -f before.sql
pgschema plan --db repro_target --schema TenantB \
  --file desired.sql \
  --plan-host "$PGHOST" --plan-port "$PGPORT" --plan-db repro_plan \
  --plan-user "$PGUSER" --plan-sslmode disable \
  --output-json plan.json --output-sql plan.sql --no-color

Actual result

Planning fails while applying desired SQL to its temporary schema:

ERROR: relation "TenantB.entries" does not exist (SQLSTATE 42P01)

Passing control

Keep this target and --schema TenantB, but use:

CREATE TABLE entries(id integer);
CREATE INDEX "items'idx" ON entries(id);

Plan/apply succeeds and a fresh plan is empty. Qualified desired SQL in TenantB also succeeds when the index has an ordinary name.

Related code and previous fixes

stripSchemaQualificationsPreservingStrings scans for single quotes while protecting string literals, without tracking double-quoted identifier regions. Source inspection and the contrasting inputs point to it treating an identifier's apostrophe as a string delimiter; that is a causal inference, rather than a tested fix.

#371/#372 address schema prefixes inside string literals; #196/#213 address dots inside quoted index names. These repros instead use apostrophes inside double-quoted identifiers. The v1.13.3 wait-literal escaping fix in #646 works for the unqualified controls and does not resolve this earlier desired-state processing failure.

🤖 Posted by Codex on jlao4394's behalf after the user requested upstream issue reports.