pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: drakonard@gmail.com
Subject: BUG #16661: Changing columns after the rule is created leads to an error when the RETURNING is used
Date: Thu, 08 Oct 2020 12:26:59 +0000
Message-ID: <16661-01a98a6c58c8054a@postgresql.org> (raw)

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.



view thread (2+ messages)  latest in thread

Message-ID: <16661-01a98a6c58c8054a@postgresql.org>
Permalink:  ../16661-01a98a6c58c8054a@postgresql.org/
Also on:    postgresql.org/message-id/16661-01a98a6c58c8054a@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, drakonard@gmail.com
  Subject: Re: BUG #16661: Changing columns after the rule is created leads to an error when the RETURNING is used
  In-Reply-To: <16661-01a98a6c58c8054a@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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