failed to build any 8-way joins: SQLSTATE: XX000 on PostgreSQL 8.2.6
Hi all, I've seen other mentions of this happening but this instance is
with a newer server version than the ones that had a patch applied.
Basically, 8 LEFT JOINs, and removing any one of them stop the symptom.
I'll provide more data if the query in itself doesn't not indicate
what's causing the problem...
-Ken
SELECT
NOW() AS a,
host.hostname AS b,
host.serverdom AS c,
host.sitename AS d,
host.appname AS e,
site.rowid AS f,
site.namefirst AS g,
pers.rowid AS h,
pers.uuid AS i,
pers.namefirst AS j,
pers.nameLast AS k,
logon.token AS l,
siteTz.timezone_id AS o,
siteTz.id AS p,
persTz.timezone_id AS q,
persTz.id AS r,
compTz.timezone_id AS s,
compTz.id AS t
FROM http_host AS host
LEFT JOIN contact AS site ON host.join_contact = site.rowid
LEFT JOIN contact_timezones AS siteTz ON site.timezoneId =
siteTz.timezone_id
LEFT JOIN contact AS pers ON LOWER(pers.logonName) = LOWER('ken')
LEFT JOIN contact_timezones AS persTz ON pers.timezoneId =
persTz.timezone_id
LEFT JOIN contact_rela AS persParent ON pers.rowid = persParent.childId
LEFT JOIN contact AS comp ON persParent.parentId = comp.rowid
LEFT JOIN contact_timezones AS compTz ON comp.timezoneId =
compTz.timezone_id
LEFT JOIN contact_logon AS logon ON pers.rowid = logon.join_contact
WHERE host.hostname = 'localhost'
Ken Johanson <pg-user@kensystem.com> writes:
Hi all, I've seen other mentions of this happening but this instance is
with a newer server version than the ones that had a patch applied.
Please see if this patch fixes it for you:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php
If not, we'll need a self-contained test case.
regards, tom lane
Tom Lane wrote:
Please see if this patch fixes it for you:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.phpIf not, we'll need a self-contained test case.
Please pardon my non-awareness, what comprises a test case for this; is
DDL sufficent? I'm electing to go that route for having only RPMs on
hand, slow CPU and unfamiliarity with the optimal configure options.
Also from the query can you tell if specific changes to it will prove
whether the patch will work? I see "clauseless joins of relations that
have unexploited join clauses", however I (interpreted this as needing)
adding a column from "persParent" to the select list, but the problem
persists. Also, I can remove any one of the timezone joins and the query
works.
Ken
Ken Johanson <pg-user@kensystem.com> writes:
Sorry Tom, I just missed you and responded on list, also asking if there
were a test implied in adjusting the query. Anyway, here is trimmed-down
DDL.
Well, if I just load this script into an empty DB in 8.2-CVS-tip and
then run the given query, it does what I'd expect (return a zero-row
result). If the same case fails for you, that suggests we fixed it.
If increasing join_collapse_limit to 9 or more makes the problem go away
for you, that would be additional evidence that what you are seeing is
the same problem already diagnosed. That might be a usable workaround
for you until 8.2.7 comes out...
regards, tom lane
Import Notes
Reply to msg id not found: 47A7F6C9.4010108@kensystem.com
Tom Lane wrote:
If increasing join_collapse_limit to 9 or more makes the problem go away
for you, that would be additional evidence that what you are seeing is
the same problem already diagnosed. That might be a usable workaround
for you until 8.2.7 comes out...
Thank Tom. That worked around it.
I always knew I hated arbitrary limits. I set it to 1 ("# 1 disables
collapsing of explicit JOINS") and all seems OKAY. That was 3 hours of
my life (experimenting, researching, communicating) under the bridge.
Anyway, back in business.
Ken