On Thu, Jul 8, 2010 at 10:52 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "Daniel Grace" <dgr...@wingsnw.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs