pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16661: Changing columns after the rule is created leads to an error when the RETURNING is used
2+ messages / 2 participants
[nested] [flat]
* BUG #16661: Changing columns after the rule is created leads to an error when the RETURNING is used
@ 2020-10-08 12:26 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2020-10-08 12:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: drakonard@gmail.com
The following bug has been logged on the website:
Bug reference: 16661
Logged by: Konstantin Dyachenko
Email address: drakonard@gmail.com
PostgreSQL version: 12.4
Operating system: Debian
Description:
The SQL:
CREATE TABLE rule_bug_table (
id serial NOT NULL PRIMARY KEY,
archived boolean NOT NULL
);
CREATE OR REPLACE RULE rule_bug_table__archive_instead_of_delete__rule
AS ON DELETE TO rule_bug_table
DO INSTEAD
UPDATE rule_bug_table
SET archived = true
WHERE rule_bug_table."id" = OLD."id"
RETURNING OLD.*;
ALTER TABLE rule_bug_table ADD COLUMN select_allowed boolean NOT NULL
DEFAULT (false);
DELETE FROM rule_bug_table WHERE id = 1 RETURNING *;
gives an error "could not find replacement targetlist entry for attno 3".
Meaning rule cannot find a column with number 3 (select_allowed column).
In other words if after rule was created any column is changed usage of
RETURNING leads to an error.
The workaround is to recreated rule after the table's column was altered.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16661: Changing columns after the rule is created leads to an error when the RETURNING is used
@ 2020-10-08 17:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2020-10-08 17:30 UTC (permalink / raw)
To: drakonard@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> The SQL:
> CREATE TABLE rule_bug_table (
> id serial NOT NULL PRIMARY KEY,
> archived boolean NOT NULL
> );
> CREATE OR REPLACE RULE rule_bug_table__archive_instead_of_delete__rule
> AS ON DELETE TO rule_bug_table
> DO INSTEAD
> UPDATE rule_bug_table
> SET archived = true
> WHERE rule_bug_table."id" = OLD."id"
> RETURNING OLD.*;
> ALTER TABLE rule_bug_table ADD COLUMN select_allowed boolean NOT NULL
> DEFAULT (false);
> DELETE FROM rule_bug_table WHERE id = 1 RETURNING *;
> gives an error "could not find replacement targetlist entry for attno 3".
Hmm. The error message definitely needs to be more user-friendly,
but I don't think this is actually a bug. The rule's RETURNING clause
expands to this during parsing:
RETURNING old.id, old.archived
as you can see with "\d+ rule_bug_table". Then when you try to do
"RETURNING *" in the DELETE, that's asking for a column not available
from the rewritten query. So you *should* get an error, just not one
that looks like it's an internal failure.
The root issue here is that "*" is expanded at parse time for stored
rules and views. Unfortunately, that behavior is specifically demanded
by the SQL spec (at least for the view case), so I doubt we can change it.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-10-08 17:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-08 12:26 BUG #16661: Changing columns after the rule is created leads to an error when the RETURNING is used PG Bug reporting form <noreply@postgresql.org>
2020-10-08 17:30 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox