Re: ERROR: attribute number 6 exceeds number of columns 5

Run the attached script and you'll get:

psql -f error.sql -d test
psql:error.sql:37: ERROR: attribute number 6 exceeds number of columns 5

Splitting up the alter-table like this makes it work:

alter table access
add column start_timestamp timestamp not null DEFAULT CURRENT_TIMESTAMP,
add column end_timestamp timestamp
;
alter table access add column tsrange TSRANGE NOT NULL GENERATED ALWAYS AS
(tsrange(start_timestamp, end_timestamp, '[)')) STORED
;

--

Andreas Joseph Krogh

Andreas Joseph Krogh <andreas(at)visena(dot)com> writes:
> Run the attached script and you'll get:

> psql -f error.sql -d test
> psql:error.sql:37: ERROR: attribute number 6 exceeds number of columns 5

Hmm, interesting. IMO, that *should* have thrown an error, but of
course not that one. The ADD COLUMN operations are all processed
in parallel, so it's not okay for one of them to have a GENERATED
expression that refers to another one of the new columns. But you
should have gotten a "no such column" type of error, not a run-time
cross-check failure.

regards, tom lane

At Tue, 26 Nov 2019 10:49:11 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in
> Andreas Joseph Krogh <andreas(at)visena(dot)com> writes:
> > Run the attached script and you'll get:
>
> > psql -f error.sql -d test
> > psql:error.sql:37: ERROR: attribute number 6 exceeds number of columns 5
>
> Hmm, interesting. IMO, that *should* have thrown an error, but of
> course not that one. The ADD COLUMN operations are all processed
> in parallel, so it's not okay for one of them to have a GENERATED
> expression that refers to another one of the new columns. But you
> should have gotten a "no such column" type of error, not a run-time
> cross-check failure.

Something like this works?

ALTER TABLE gtest25 ADD COLUMN x int, ADD COLUMN y int GENERATED ALWAYS AS (x * 4) STORED;
ERROR: column "x" does not exist
DETAIL: An expression cannot reference columns added in the same command.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> writes:
> At Tue, 26 Nov 2019 10:49:11 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in
>> Hmm, interesting. IMO, that *should* have thrown an error, but of
>> course not that one. The ADD COLUMN operations are all processed
>> in parallel, so it's not okay for one of them to have a GENERATED
>> expression that refers to another one of the new columns. But you
>> should have gotten a "no such column" type of error, not a run-time
>> cross-check failure.

> Something like this works?

I started to look at this, but it felt a bit brute-force to me.
After awhile I began to think that my offhand comment above was
wrong --- why *shouldn't* this case work? When we insert or
update a tuple, we expect that GENERATED columns should be
computed based on the new tuple values, so why is the executor
evidently evaluating them based on the old tuple?

That thought soon led me to realize that there's an adjacent
bug that this patch fails to fix:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values(1),(2);
INSERT 0 2
regression=# alter table foo alter column f1 type float8, add column f2 int generated always as (f1 * 2) stored;
ERROR: attribute 1 of type foo has wrong type
DETAIL: Table has type integer, but query expects double precision.

So I believe that the real problem here is that the executor is
evaluating GENERATED expressions at the wrong time. It's evaluating
them against the pre-conversion tuples when it should be evaluating
them against the post-conversion tuples. We need to go fix that,
rather than inserting arbitrary restrictions in the DDL code.

regards, tom lane

I wrote:
> So I believe that the real problem here is that the executor is
> evaluating GENERATED expressions at the wrong time. It's evaluating
> them against the pre-conversion tuples when it should be evaluating
> them against the post-conversion tuples. We need to go fix that,
> rather than inserting arbitrary restrictions in the DDL code.

I looked at that more closely, and realized that blaming the executor
is wrong: the real issue is that ALTER TABLE itself supposes that it
need only evaluate expressions against the old tuple. That's easy
to fix with a bit more code though. I propose the attached.

(Note that this should also allow relaxing the existing implementation
restriction against changing types of columns that GENERATED columns
depend on: all we have to do is re-parse the generation expression
and schedule it for evaluation. I've not looked into that, and it
doesn't seem like a bug fix anyway.)

regards, tom lane