Re: BUG #5548: ERROR: invalid attnum ## for rangetable entry on EXPLAIN VERBOSE, not on EXPLAIN - Mailing list pgsql-bugs

On Thu, Jul 8, 2010 at 10:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Daniel Grace" <dgrace@wingsnw.com> writes:
>> I apologize for not including detailed schema information. =A0It 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. =A0You haven't provided anywhere near enough information
> for someone else to reproduce the failure. =A0We're not going to
> guess at the tables or views that underlie your query ...
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, 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=3Dtest,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=3Dacal.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=3Dyi.year
WHERE t.foo IS NULL



Plan that fails on EXPLAIN VERBOSE:
"Nested Loop  (cost=3D0.00..132.35 rows=3D24 width=3D4)"
"  ->  Subquery Scan on t  (cost=3D0.00..131.00 rows=3D6 width=3D4)"
"        Filter: (t.foo IS NULL)"
"        ->  WindowAgg  (cost=3D0.00..119.50 rows=3D1150 width=3D4)"
"              ->  Merge Join  (cost=3D0.00..102.25 rows=3D1150 width=3D4)"
"                    Merge Cond: (acal.id =3D acal.id)"
"                    ->  Index Scan using allocation_calendar_pkey on
allocation_calendar acal  (cost=3D0.00..42.50 rows=3D1150 width=3D4)"
"                    ->  Index Scan using allocation_calendar_pkey on
allocation_calendar acal  (cost=3D0.00..42.50 rows=3D1150 width=3D4)"
"  ->  Materialize  (cost=3D0.00..1.06 rows=3D4 width=3D0)"
"        ->  Seq Scan on yearinfo yi  (cost=3D0.00..1.04 rows=3D4 width=3D0=
)"
--> 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=3D0.00..827.88 rows=3D20020 width=3D4) (actual
time=3D0.036..2566.818 rows=3D4005001 loops=3D1)"
"  ->  Seq Scan on yearinfo yi  (cost=3D0.00..15.01 rows=3D1001 width=3D0)
(actual time=3D0.007..0.429 rows=3D1001 loops=3D1)"
"  ->  Materialize  (cost=3D0.00..562.67 rows=3D20 width=3D4) (actual
time=3D0.000..0.850 rows=3D4001 loops=3D1001)"
"        ->  Subquery Scan on t  (cost=3D0.00..562.57 rows=3D20 width=3D4)
(actual time=3D0.026..14.731 rows=3D4001 loops=3D1)"
"              Filter: (t.foo IS NULL)"
"              ->  WindowAgg  (cost=3D0.00..522.56 rows=3D4001 width=3D4)
(actual time=3D0.025..12.637 rows=3D4001 loops=3D1)"
"                    ->  Merge Join  (cost=3D0.00..462.55 rows=3D4001
width=3D4) (actual time=3D0.016..7.715 rows=3D4001 loops=3D1)"
"                          Merge Cond: (acal.id =3D acal.id)"
"                          ->  Index Scan using
allocation_calendar_pkey on allocation_calendar acal
(cost=3D0.00..201.27 rows=3D4001 width=3D4) (actual time=3D0.007..1.481
rows=3D4001 loops=3D1)"
"                          ->  Index Scan using
allocation_calendar_pkey on allocation_calendar acal
(cost=3D0.00..201.27 rows=3D4001 width=3D4) (actual time=3D0.006..2.035
rows=3D4001 loops=3D1)"
"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)

--=20
Daniel Grace