9.47.1

One PostgreSQL fix. Nothing public was added, removed or changed.

Indexes are read back in a schema whose name has to be quoted

#750 / #751, contributed by @marc-RocketRez, with #756 adding coverage.

The PostgreSQL index introspection query identified each row's table by regclassout's rendering of it, and matched that against four spellings of the table Weasel was asking about. None of the four quoted the schema, and regclassout quotes whatever has to be quoted:

schema	regclassout renders	matched
public, in the session's search_path	docs	yes, the bare candidate
reporting, not in search_path	reporting.docs	yes, the qualified candidate
order	"order".docs	no
MySchema	"MySchema".docs	no

So in a schema named for a reserved word — order, user — or in one carrying uppercase, no index was read back at all. Every declared index was then Missing rather than Different, so the patch was a bare create index with no preceding drop: the first run worked, and every run after it answered 42P07: relation already exists. A delta executes as one command, so that failure aborted everything ordered behind it, the schema never converged, and db-assert could never pass.

Found in Wolverine, which keeps its envelope tables in the service's own schema (wolverine#5215): an order or a user service started once and never again.

The query now returns the table's bare relname and matches it against Identifier.Name. nspname = :schema already pinned it to one schema, so there is no longer a rendering whose quoting anyone has to anticipate — and index readback no longer varies with the session's search_path.

The primary key constraint's name, in the same schemas

The primary key's name arrives on those same rows, so it was not read back either — and nothing noticed, because PrimaryKeyName falls back to the provider's derived default whenever nothing assigned it. For a table using the default name the stale actual and the expected agree by construction. Name the constraint yourself and the delta emits a rename off a name that was never in the database:

42704: constraint "pkey_docs_id" for table "docs" does not exist

Both are fixed by the one change.

Should you upgrade?

• Yes, if any of your schemas is a reserved word or carries uppercase. There is no workaround short of renaming the schema.
• Otherwise it changes nothing. For a schema that needs no quoting, relname and regclassout's rendering name the same table and the comparison lands where it always did.
• weasel#224 is unaffected — that is a mixed-case table in a lower-case schema, and it keeps working.

Full notes: https://weasel.jasperfx.net/release-9-47