The following bug has been logged on the website:
Bug reference: 19543
Logged by: Adam Pickering
Email address: adamkpickering@gmail.com
PostgreSQL version: 18.4
Operating system: Debian (using an official postgres docker image)
Description:
PostgreSQL version
Official postgres container from dockerhub (Debian 18.4-1.pgdg13+1) on
aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(also observed on 17.10 and current 14)
Platform
aarch64, Debian 13-based (official postgres:18 image), Linux 6.6, default
server config.
Description
CREATE RULE rejects the name _RETURN for a rule that is not an ON SELECT
rule. ALTER RULE ... RENAME does not: an existing ON UPDATE/INSERT/DELETE
rule on an ordinary table can be renamed to _RETURN and the server accepts
it. A subsequent pg_dump emits a CREATE RULE "_RETURN" ... statement the
server rejects on reload, so the database can be dumped but not restored.
Exact steps to reproduce (run with psql -X, no ~/.psqlrc)
CREATE TABLE t (a int);
CREATE RULE r AS ON UPDATE TO t DO ALSO SELECT 1;
-- rejected
CREATE RULE "_RETURN" AS ON UPDATE TO t DO ALSO SELECT 1;
-- accepted; this is the bug
ALTER RULE r ON t RENAME TO "_RETURN";
then:
$ createdb fresh
$ pg_dump -t t --no-owner | psql -X -d fresh -v ON_ERROR_STOP=1
Output I got:
CREATE TABLE
CREATE RULE
ERROR: 42P17: non-view rule for "t" must not be named "_RETURN"
LOCATION: DefineQueryRewrite, rewriteDefine.c:456
ALTER RULE
The CREATE RULE "_RETURN" is rejected (expected); the ALTER RULE ... RENAME
TO "_RETURN" reports ALTER RULE, i.e. it succeeds. pg_dump then produces
CREATE RULE "_RETURN" AS ON UPDATE TO public.t DO SELECT 1 ..., and
reloading it fails with the same 42P17 error.
Output I expected:
ALTER RULE ... RENAME TO "_RETURN" rejected the same way CREATE RULE rejects
it, so _RETURN can only ever be a view's ON SELECT rule and any relation
that dumps cleanly can be reloaded.
Context
The guard on CREATE RULE was added deliberately (commit by Tom Lane,
released in 14.3 / 13.7 / 12.11 / 11.16 / 10.23, "Disallow rules named
_RETURN that are not ON SELECT"; the in-tree comment states it "prevents
accidentally or maliciously replacing a view's ON SELECT rule with some
other kind of rule").
