Re: BUG #5548: ERROR: invalid attnum ## for rangetable entry on EXPLAIN VERBOSE, not on EXPLAIN

On Thu, Jul 8, 2010 at 10:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Daniel Grace" <dgrace(at)wingsnw(dot)com> writes:
>> I apologize for not including detailed schema information.  It took a lot to
>> get this to reduce to the point it did, and hopefully this is enough
>> information to find a bug.
>
> It is not.  You haven't provided anywhere near enough information
> for someone else to reproduce the failure.  We're not going to
> guess at the tables or views that underlie your query ...
>
>                        regards, tom lane
>

So I've spent the greater portion of the last two hours trying to slim
down the schema and query enough to provide something that can
reproduce this.

While I can reproduce it 100% of the time with live data, I can't get
it to reproduce at all with test data -- though I've included a few
schemas below. It seems to be based on what plan ends up being
constructed for the query.

This doesn't really affect me at this point -- but my concern is that
it might cause actual problems when paired with the auto_explain
contrib module.

Basic stripped-down schema:
DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
SET SEARCH_PATH=test,public;

CREATE TABLE allocation_calendar
(
id serial NOT NULL,
"year" smallint NOT NULL,
"name" character varying(64) NOT NULL,
countdate date,
availabledate date NOT NULL,
weight integer NOT NULL,
daterange integer NOT NULL, -- Was a PERIOD, but not required to reproduce
CONSTRAINT allocation_calendar_pkey PRIMARY KEY (id),
CONSTRAINT allocation_calendar_ux_year UNIQUE (year, name)
);
CREATE INDEX allocation_calendar_ix_year_3 ON allocation_calendar
(year, countdate);
CREATE INDEX allocation_calendar_ix_year_4 ON allocation_calendar
(year, availabledate);

CREATE TABLE yearinfo (
id serial NOT NULL,
year smallint NOT NULL,
CONSTRAINT yearinfo_ux_year UNIQUE (year)
);

INSERT INTO yearinfo (year) SELECT * FROM GENERATE_SERIES(1000, 2000);
INSERT INTO allocation_calendar (year, name, countdate, availabledate,
weight, daterange) SELECT f.v, 'Year ' || f.v, NULL, '-infinity', 1, 2
FROM GENERATE_SERIES(1000, 5000) AS f(v);

ANALYZE allocation_calendar;
REINDEX TABLE yearinfo;
REINDEX TABLE allocation_calendar;

CREATE OR REPLACE VIEW allocation_calculated_dates AS
SELECT acal.id AS acalid, acal.year AS year, null::integer AS tstime
FROM
allocation_calendar AS acal
;

EXPLAIN ANALYZE SELECT t.*
FROM (
SELECT
FIRST_VALUE(acd.tstime) OVER nextdate AS foo
FROM
allocation_calculated_dates AS acd
INNER JOIN allocation_calendar AS acal ON acd.acalid=acal.id
WINDOW
nextdate AS ( PARTITION BY acd.acalid ORDER BY acd.tstime ASC ROWS
BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
) AS t
CROSS JOIN yearinfo AS yi -- ON t.year=yi.year
WHERE t.foo IS NULL

Plan that fails on EXPLAIN VERBOSE:
"Nested Loop (cost=0.00..132.35 rows=24 width=4)"
" -> Subquery Scan on t (cost=0.00..131.00 rows=6 width=4)"
" Filter: (t.foo IS NULL)"
" -> WindowAgg (cost=0.00..119.50 rows=1150 width=4)"
" -> Merge Join (cost=0.00..102.25 rows=1150 width=4)"
" Merge Cond: (acal.id = acal.id)"
" -> Index Scan using allocation_calendar_pkey on
allocation_calendar acal (cost=0.00..42.50 rows=1150 width=4)"
" -> Index Scan using allocation_calendar_pkey on
allocation_calendar acal (cost=0.00..42.50 rows=1150 width=4)"
" -> Materialize (cost=0.00..1.06 rows=4 width=0)"
" -> Seq Scan on yearinfo yi (cost=0.00..1.04 rows=4 width=0)"
--> ERROR: invalid attnum 2 for rangetable entry t
Note: The attnum in question always seems to be 1 more than the number
of columns in t.

Plan that succeeds on EXPLAIN VERBOSE:
"Nested Loop (cost=0.00..827.88 rows=20020 width=4) (actual
time=0.036..2566.818 rows=4005001 loops=1)"
" -> Seq Scan on yearinfo yi (cost=0.00..15.01 rows=1001 width=0)
(actual time=0.007..0.429 rows=1001 loops=1)"
" -> Materialize (cost=0.00..562.67 rows=20 width=4) (actual
time=0.000..0.850 rows=4001 loops=1001)"
" -> Subquery Scan on t (cost=0.00..562.57 rows=20 width=4)
(actual time=0.026..14.731 rows=4001 loops=1)"
" Filter: (t.foo IS NULL)"
" -> WindowAgg (cost=0.00..522.56 rows=4001 width=4)
(actual time=0.025..12.637 rows=4001 loops=1)"
" -> Merge Join (cost=0.00..462.55 rows=4001
width=4) (actual time=0.016..7.715 rows=4001 loops=1)"
" Merge Cond: (acal.id = acal.id)"
" -> Index Scan using
allocation_calendar_pkey on allocation_calendar acal
(cost=0.00..201.27 rows=4001 width=4) (actual time=0.007..1.481
rows=4001 loops=1)"
" -> Index Scan using
allocation_calendar_pkey on allocation_calendar acal
(cost=0.00..201.27 rows=4001 width=4) (actual time=0.006..2.035
rows=4001 loops=1)"
"Total runtime: 3288.843 ms"

Hope this helps at least somewhat. I know it's not as clear cut as
I'd like. (Normally I'd like to send a 100% working reproduce case,
but I just can't seem to get one here)

--
Daniel Grace