🧱 Schema sync: what "the table already exists" means

Updated 22 Oct 2026

Before every CREATE, SchemaSynchronizer asks the database: does such a table already exist? The answer is obliged to match the one the runtime will get. The runtime reaches business tables through a quoted, unqualified identifier — SELECT … FROM "DeliveryTerm" — so "exists" means an identifier spelled exactly that way, resolvable through the current schema, and not "the catalog holds a row with a similar name".

The differences between the engines are carried by two members of ISqlDialect:

	SQL Server	PostgreSQL
`TableExistsQuery`	`OBJECT_ID(N'[Name]', N'U')`	`to_regclass('"Name"')`
`CurrentSchemaExpression`	`SCHEMA_NAME()`	`current_schema()`
`IdentifiersAreCaseSensitive`	`false`	`true`

There are physically two answers: the live query, and the catalogue snapshot that LoadCatalogAsync reads ONCE per detect pass (otherwise a pass would cost hundreds of round-trips). The snapshot is filtered by CurrentSchemaExpression and keyed by a comparer taken from IdentifiersAreCaseSensitive — that is, it is built out of those same two members. During detect only the snapshot is asked; TableExistsLiveAsync drops it for a moment wherever a live answer is needed.

Snapshot and live path must agree on case#

If the snapshot and the live path disagree on case, Postgres looks fine and then cannot read the table.

The table exists as deliveryterm (lower case — for example after hand-written DDL, a restore or a rename). A case-insensitive snapshot that reads INFORMATION_SCHEMA across ALL visible schemas answers "it is there". No CREATE is queued. POST /api/schema/sync answers "Schema synchronized successfully", because that answer means exactly "SyncAsync did not throw" — and doing nothing still does not throw. And any read gives:

42P01: relation "DeliveryTerm" does not exist

On the PG stand, rename the table to lower case → detect stops queueing a CREATE → sync returns 200 "successfully" → SELECT count(*) FROM "DeliveryTerm" fails with 42P01. With the dialect members matching case, the startup sync creates public."DeliveryTerm", the read goes through, and detect stays clean — 0 create / 0 alter / 0 drop across the dictionaries.

On SQL Server none of this is visible. The engine compares identifiers case-insensitively, both paths agree. The only local stand is SQL Server, and tenants do run on Postgres: a defect of this class reaches the customer invisible. Verify on the PG stand (the pg profile in docker-compose.yml).

What follows from this in practice#

"Schema synchronized" is no proof that the table appeared. If you want proof, call GET /api/schema/detect alongside POST /api/schema/sync and check that the queue is empty.
Any new check of "is there such an object in the database" is taken from ISqlDialect, not written by hand over INFORMATION_SCHEMA. A query to INFORMATION_SCHEMA with no filter by schema sees other people's schemas, and on Postgres it also compares differently from the engine.
The startup sync takes the container down with it. If the corrected comparison finds a conflicting table on a tenant, the CREATE can run into somebody else's constraint (42P07: relation "PK_X" already exists) — and the container will not come up. Before rolling out to a live tenant, look at detect there first: how many tables it is about to create.

The same class in a script's raw SQL#

The same mismatch hits a script that writes SQL by hand through ISqlService: SELECT … FROM MetaJobs without quotes is folded by Postgres into metajobs, while EF created the table as "MetaJobs" — 42P01. On SQL Server names are compared case-insensitively and nothing shows.

Hand-written filters and DML therefore quote names with [brackets]; NormalizeBrackets rewrites them into the engine's quotes. All four entry points (SelectAsync, ExecuteAsync, ExecuteScalarAsync, ExecuteListAsync) run the text through it. In scripts, write it like this:

await Sql.SelectAsync("SELECT [MetaId], [Name] FROM [MetaJobs] WHERE [Name] = 'X'");

A query that is green on SQL Server is not proof it is portable.

Literals travel the same road#

Brackets were the first thing NormalizeBrackets rewrote. Literals that SQL Server accepts by coercion are the next class: they are valid T-SQL, they die on PostgreSQL with 42883: operator does not exist, and the stand never sees them because it runs SQL Server.

Emission (T-SQL)	SQL Server	PostgreSQL without the rewrite	PostgreSQL after
`[IsDisabled] = CAST(1 AS BIT)`	`bit = bit`	`boolean = bit` (the PG `bit` is a bit-string)	`"IsDisabled" = TRUE`
`[ItemGroup] = N'<guid>'`	`uniqueidentifier = nvarchar`	`uuid = character`	`"ItemGroup" = '<guid>'` (untyped, coerces)

The group-tree list is the Guid case: click a node, the grid sends [ItemGroup] = N'<guid>' (or IN of the node plus descendants). PostgreSQL parses N'…' as type character; uuid has no = for that. The console shows HTTP 42883, the grid stays empty. Dropping the N prefix is enough — an untyped string constant coerces to uuid the way nvarchar coerces to uniqueidentifier. Do not rewrite Guid-shaped text into ::uuid: a Name that happens to hold a Guid string would then fail the other way.

The boolean token is a cast rather than a bare 1 for the same reason: a rewriter with no column types cannot tell [Quantity] = 1 (fine) from a flag. CAST(1 AS BIT) is unambiguous T-SQL and rewritable on sight. The prefix N is unambiguous the other way: PostgreSQL never needs it.

A query that is green on SQL Server is still not proof it is portable.

Where next#

Platform architecture — where the sync sits in the overall flow.
Model import: bundles and the diff — importing metadata does NOT create physical tables; that is always the work of the schema sync.

Was this page helpful?