John Naylor <[email protected]> writes:
> Claude offered to search for other bugs of this kind and found this:
> CREATE TABLE emp (id int, valid daterange, salary int);
> CREATE TABLE t (x int);
> CREATE RULE r AS ON UPDATE TO t DO INSTEAD
> UPDATE emp FOR PORTION OF valid FROM '2020-01-01' TO '2021-01-01'
> SET salary = 1;
> -- rename column
> ALTER TABLE emp RENAME COLUMN valid TO validity;
> dump and restore:
> ERROR: column "valid" of relation "emp" does not exist
> LINE 2: ...ic.t DO INSTEAD UPDATE public.emp FOR PORTION OF valid FROM...
Yeah, you can demonstrate this bug without the dump-and-restore step:
postgres=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
x | integer | | | | plain | |
|
Rules:
r AS
ON UPDATE TO t DO INSTEAD UPDATE emp FOR PORTION OF valid FROM
'2020-01-01' TO '2021-01-01' SET salary = 1
Access method: heap
postgres=# ALTER TABLE emp RENAME COLUMN valid TO validity;
ALTER TABLE
postgres=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
x | integer | | | | plain | |
|
Rules:
r AS
ON UPDATE TO t DO INSTEAD UPDATE emp FOR PORTION OF valid FROM
'2020-01-01' TO '2021-01-01' SET salary = 1
Access method: heap
I didn't check the code, but it looks like someone thought it'd be a
good idea to record the FOR PORTION OF target column by name rather
than column number. That'll be an initdb-forcing catalog change, so
good thing we found it now.
regards, tom lane
