Re: Fix missing FORMAT when deparsing JSON_ARRAY(query)

John Naylor <johncnaylorls(at)gmail(dot)com> 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