Обсуждение: 9.0b1: "ERROR: btree index keys must be ordered by attribute"
Hi
I've just compiled the 9.0 beta1 source tarball and am testing my
custom application against it (which has been running on PostgreSQL
since 7.3 or so).
The below statement results in the following error message:
"ERROR: btree index keys must be ordered by attribute"
evidently in relation to the subselect. The statement works fine on
previous versions up to 8.4.3. I can provide more details later if
required:
SELECT o.object_id FROM object o INNER JOIN class c ON (o.class_id = c.class_id) INNER
JOINobject_version ov ON (o.object_id = ov.object_id) INNER JOIN site ON
(o.site_id=site.site_id) WHERE o.object_id = '3143' AND ov.version = '0' AND
o.site_id= '2' AND ov.object_status_id = (SELECT MAX(ov1.object_status_id) FROM
object_versionov1 WHERE o.object_id=ov1.object_id AND ov1.version = ov.version
AND ov1.lang = ov.lang ) AND ov.lang = 'en'
SELECT version():
PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu
4.3.2-1ubuntu12) 4.3.2, 32-bit
Ubuntu 8.10 running on a "VIA C7-M" Processor (netbook).
Regards
Ian Barwick
On Sun, May 09, 2010 at 05:48:27PM +0900, Ian Barwick wrote: > Hi > > I've just compiled the 9.0 beta1 source tarball and am testing my > custom application against it (which has been running on PostgreSQL > since 7.3 or so). > > The below statement results in the following error message: > > "ERROR: btree index keys must be ordered by attribute" > > evidently in relation to the subselect. The statement works fine on > previous versions up to 8.4.3. I can provide more details later if > required: A self-contained way to reproduce this, ideally small, would be fantastic :) Cheers, David. > > SELECT o.object_id > FROM object o > INNER JOIN class c > ON (o.class_id = c.class_id) > INNER JOIN object_version ov > ON (o.object_id = ov.object_id) > INNER JOIN site > ON (o.site_id=site.site_id) > WHERE o.object_id = '3143' > AND ov.version = '0' > AND o.site_id = '2' > AND ov.object_status_id = (SELECT MAX(ov1.object_status_id) > FROM object_version ov1 > WHERE o.object_id=ov1.object_id > AND ov1.version = ov.version > AND ov1.lang = ov.lang > ) > AND ov.lang = 'en' > > > SELECT version(): > PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu > 4.3.2-1ubuntu12) 4.3.2, 32-bit > Ubuntu 8.10 running on a "VIA C7-M" Processor (netbook). > > Regards > > Ian Barwick > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2010/5/9 David Fetter <david@fetter.org>: > On Sun, May 09, 2010 at 05:48:27PM +0900, Ian Barwick wrote: >> Hi >> >> I've just compiled the 9.0 beta1 source tarball and am testing my >> custom application against it (which has been running on PostgreSQL >> since 7.3 or so). >> >> The below statement results in the following error message: >> >> "ERROR: btree index keys must be ordered by attribute" >> >> evidently in relation to the subselect. The statement works fine on >> previous versions up to 8.4.3. I can provide more details later if >> required: > > A self-contained way to reproduce this, ideally small, would be > fantastic :) Unfortunately I'm a bit pressed for time right now :(, however in the meantime I have confirmed the same error crops up on OS X 10.5 and also with the query pruned to reference just one table: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = '3143' AND ov.version = '0' AND ov.object_status_id= ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; PostgreSQL 9.0beta1 on i386-apple-darwin9.8.0, compiled by GCC i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465), 32-bit I will see if I can knock together a reproducible test case, might take a day or so. At the moment all I can report is that creating a reduced version of the "object_version" table in a fresh DB with no data does not reproduce the error when running the above query. Ian Barwick
Ian Barwick <barwick@gmail.com> writes:
> 2010/5/9 David Fetter <david@fetter.org>:
>> A self-contained way to reproduce this, ideally small, would be
>> fantastic :)
s/fantastic/absolutely required to do anything with this report/
> I will see if I can knock together a reproducible test case, might
> take a day or so. At the moment all I can report is that creating a reduced
> version of the "object_version" table in a fresh DB with no data does
> not reproduce the error when running the above query.
It probably depends on a specific plan being chosen for the query,
and with no data loaded you'd most likely not get the same plan.
regards, tom lane
Hi 2010/5/10 Tom Lane <tgl@sss.pgh.pa.us>: > Ian Barwick <barwick@gmail.com> writes: >> 2010/5/9 David Fetter <david@fetter.org>: >>> A self-contained way to reproduce this, ideally small, would be >>> fantastic :) > > s/fantastic/absolutely required to do anything with this report/ Yes, I appreciate that :) I am a bit pressed for time and as googling the error message didn't produce any kind of result I thought it better to at least give a heads-up on the offchance someone might be able to do something with it as is, and / or events overtake me and I never end up doing anything about it at all. "Luckily" this is easy to reproduce with a stripped-down version of the original table and minimal data set: CREATE TABLE object_version ( object_version_id SERIAL, object_id INT NOT NULL, version INT NOT NULLDEFAULT 0, object_status_id INT NOT NULL, parent_id INT DEFAULT NULL, owner_id INT NOT NULL,created TIMESTAMP(0) NOT NULL DEFAULT NOW(), lang CHAR(2) NOT NULL, PRIMARY KEY (object_version_id),UNIQUE (object_id, version, object_status_id, lang) ); INSERT INTO object_version VALUES (DEFAULT, 1, 0, 0, NULL, 1, DEFAULT,'en'), (DEFAULT, 1, 0, -1, NULL, 1, DEFAULT,'en'), (DEFAULT, 1, 1, -1, NULL, 1, DEFAULT,'en'); SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version ='0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version= ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; SELECT version();PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 64-bit HTH Ian Barwick
2010/5/10 Ian Barwick <barwick@gmail.com>: > SELECT ov.object_id > FROM object_version ov > WHERE ov.object_id = 1 > AND ov.version ='0 > AND ov.object_status_id = ( > SELECT MAX(ov1.object_status_id) > FROM object_version ov1 > WHERE ov1.object_id=ov.object_id > AND ov1.version = ov.version > AND ov1.lang = ov.lang > ) > AND ov.lang = 'en'; Apologies, slight c&p error; correct version of query: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version =0 AND ov.object_status_id = ( SELECTMAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; FWIW the test case works fine in 8.4.3 Ian Barwick
Ian Barwick <barwick@gmail.com> writes:
> Apologies, slight c&p error; correct version of query:
> SELECT ov.object_id
> FROM object_version ov
> WHERE ov.object_id = 1
> AND ov.version =0
> AND ov.object_status_id = (
> SELECT MAX(ov1.object_status_id)
> FROM object_version ov1
> WHERE ov1.object_id=ov.object_id
> AND ov1.version = ov.version
> AND ov1.lang = ov.lang
> )
> AND ov.lang = 'en';
Ah, I see it:
-> Index Scan Backward using object_version_object_id_version_object_status_id_lang_key on
object_versionov1 (cost=0.00..8.27 rows=1 width=4) Index Cond: ((object_id = $0) AND (version =
$1)AND (lang = $2) AND (object_status_id IS NOT NULL))
where
regression=# \d object_version_object_id_version_object_status_id_lang_key
Index "public.object_version_object_id_version_object_status_id_lang_key" Column | Type |
Definition
------------------+--------------+------------------object_id | integer | object_idversion |
integer | versionobject_status_id | integer | object_status_idlang | character(2) | lang
unique, btree, for table "public.object_version"
The index-based-max code is throwing in the IS NOT NULL condition
without thought for where it has to go in the index condition order.
Will look into fixing this tomorrow.
regards, tom lane
Tom Lane wrote: > Ian Barwick <barwick@gmail.com> writes: > > Apologies, slight c&p error; correct version of query: > > > SELECT ov.object_id > > FROM object_version ov > > WHERE ov.object_id = 1 > > AND ov.version =0 > > AND ov.object_status_id = ( > > SELECT MAX(ov1.object_status_id) > > FROM object_version ov1 > > WHERE ov1.object_id=ov.object_id > > AND ov1.version = ov.version > > AND ov1.lang = ov.lang > > ) > > AND ov.lang = 'en'; > > Ah, I see it: > > -> Index Scan Backward using object_version_object_id_version_object_status_id_lang_key on object_versionov1 (cost=0.00..8.27 rows=1 width=4) > Index Cond: ((object_id = $0) AND (version = $1) AND (lang = $2) AND (object_status_id IS NOTNULL)) > > where > > regression=# \d object_version_object_id_version_object_status_id_lang_key > Index "public.object_version_object_id_version_object_status_id_lang_key" > Column | Type | Definition > ------------------+--------------+------------------ > object_id | integer | object_id > version | integer | version > object_status_id | integer | object_status_id > lang | character(2) | lang > unique, btree, for table "public.object_version" > > The index-based-max code is throwing in the IS NOT NULL condition > without thought for where it has to go in the index condition order. > Will look into fixing this tomorrow. FYI, this no longer throws an error in current CVS so was fixed by Tom. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
2010/6/1 Bruce Momjian <bruce@momjian.us>: > Tom Lane wrote: (...) >> The index-based-max code is throwing in the IS NOT NULL condition >> without thought for where it has to go in the index condition order. >> Will look into fixing this tomorrow. > > FYI, this no longer throws an error in current CVS so was fixed by Tom. Thanks for the update, I can confirm the issue no longer occurs in beta2. Regards Ian Barwick