Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/6/1 Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
Tom Lane wrote: Ian Barwick barw...@gmail.com writes: Apologies, slight cp 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_version ov1 (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_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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 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
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/5/9 David Fetter da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
Ian Barwick barw...@gmail.com writes: 2010/5/9 David Fetter da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
Hi 2010/5/10 Tom Lane t...@sss.pgh.pa.us: Ian Barwick barw...@gmail.com writes: 2010/5/9 David Fetter da...@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 NULL DEFAULT 0, object_status_idINT NOT NULL, parent_id INT DEFAULT NULL, owner_idINT NOT NULL, created TIMESTAMP(0) NOT NULL DEFAULT NOW(), langCHAR(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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/5/10 Ian Barwick barw...@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 cp 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'; FWIW the test case works fine in 8.4.3 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
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
Ian Barwick barw...@gmail.com writes: Apologies, slight cp 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_version ov1 (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_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. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
