Auto-DDL queues DROP of temp relations (CREATE is skipped)

[ilexius]

Auto-DDL queues DROP of temp relations (CREATE is skipped) — guaranteed apply failure on every subscriber; 5.0.10 logs mislabel replayed-OK operations as "discarded"

Seen on spock 5.0.9 (provider) with 5.0.9 and 5.0.10 subscribers
(pgedge-postgres:17-spock5-standard, PG 17.10). Reproduced 2026-07-16.

Problem 1 — capture asymmetry for temp relations

With spock.enable_ddl_replication = on, the auto-DDL capture correctly
skips CREATE TEMP TABLE — but the later DROP TABLE of that same temp
relation is queued and replicated. The subscriber never had the
relation, so the replayed DROP fails with [SQLSTATE 42P01] on every
subscriber, every time.

Verbatim session on the provider (spock 5.0.9):

BEGIN;
CREATE TEMP TABLE tmp_ddl_repro(a int);
WARNING:  This DDL statement will not be replicated.     <-- temp CREATE skipped
INSERT INTO tmp_ddl_repro VALUES (1);
DROP TABLE tmp_ddl_repro;
INFO:  DDL statement replicated.                         <-- temp DROP queued!
UPDATE res_partner_title SET name = name || ' [repro]' WHERE id = 1;
COMMIT;

SELECT message_type, replication_sets, left(message::text, 80) FROM spock.queue
ORDER BY queued_at DESC LIMIT 1;
-- D | {default_insert_only} | "SET search_path TO \"$user\", public; DROP TABLE tmp_ddl_repro"

Subscriber spock.exception_log (exception_behaviour=discard,
exception_logging=all):

operation | rel                      | error_message
INSERT    | spock.queue              | unavailable
DDL       | -                        | table "tmp_ddl_repro" does not exist
UPDATE    | public.res_partner_title | unavailable

The trailing UPDATE of the same transaction was applied on the
subscriber (verified by content) — statement-level discard isolated the
failing DDL correctly. The damage is the guaranteed exception per DROP and
the resulting log flood (below), not data loss.

Why (source)

autoddl_can_proceed() (spock_autoddl.c) gates only on statement class and
utility context — there is no persistence check. The CREATE path detects
the temp relation (hence the "will not be replicated" WARNING; a
CreateStmt carries relpersistence in the parse tree). A DropStmt
carries only names; deciding temp-ness needs a catalog lookup of the
target, which the capture path does not do — so the DROP is queued
unconditionally.

Real-world impact

Odoo (and anything using temp staging tables) hits this constantly: every
translation load during a module install/update runs
CREATE TEMP TABLE tmp_ir_translation_import … DROP TABLE tmp_ir_translation_import, once per language file, inside the big upgrade
transaction. On our 4-country fleet one evening of routine module updates
produced 20,732 and 20,714 exception-log entries on two subscribers —
all tracing to this single pattern (exception_logging = all records
every operation of an errored transaction, so a few upgrade transactions
of ~2,000 commands each flood the log).

With the 5.0.9/5.0.10 default exception_behaviour = transdiscard
this is not just noise: a single temp-table DROP would throw away the
whole Odoo upgrade transaction on the subscriber.

Suggested fix

In the auto-DDL capture path for DropStmt (and other name-only DDL such
as ALTER TABLE if reachable for temp relations): resolve the target
relation(s) (RangeVarGetRelid / isAnyTempNamespace(RelationGetNamespace))
and skip queueing when every target is a temp relation, mirroring the
existing CREATE behaviour. A mixed DROP TABLE a, tmp_b would need the
statement split or rewritten to the non-temp subset.

Workaround (verified)

Capture-side there is none at SQL level (ProcessUtility hook — no event
trigger to replace, unlike the table_data_filtered case). Subscriber-side
the problem can be inverted: create a permanent decoy table with the
temp table's name so the replayed DROP succeeds, plus a sql_drop event
trigger that recreates the decoy immediately — ENABLE ALWAYS, because the
apply worker runs with session_replication_role = replica:

SET spock.enable_ddl_replication = off;
CREATE TABLE IF NOT EXISTS public.tmp_ir_translation_import (shim int);
CREATE OR REPLACE FUNCTION public.spock_fleet_temp_drop_shim()
RETURNS event_trigger LANGUAGE plpgsql
SET spock.enable_ddl_replication TO off
AS $$
DECLARE obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF obj.object_type = 'table' AND obj.schema_name = 'public'
       AND obj.object_name = 'tmp_ir_translation_import' THEN
      CREATE TABLE IF NOT EXISTS public.tmp_ir_translation_import (shim int);
    END IF;
  END LOOP;
END $$;
CREATE EVENT TRIGGER spock_fleet_temp_drop_shim ON sql_drop
  EXECUTE FUNCTION public.spock_fleet_temp_drop_shim();
ALTER EVENT TRIGGER spock_fleet_temp_drop_shim ENABLE ALWAYS;

Verified on 5.0.9: two temp create/drop cycles plus DML in one provider
transaction apply with zero exception-log entries; the decoy survives
repeated drops within the same apply transaction; unrelated DML is
untouched. Cost: one extra (schema-only) table on the subscriber.