From: Will Furnass <will@thearete.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
Date: Sun, 7 Nov 2010 02:18:05 -0800 (PST)
Message-ID: <1289125085332-3253813.post@n5.nabble.com> (raw)
In-Reply-To: <1288380670846-3242676.post@n5.nabble.com>
References: <1288380670847-2154712.post@n5.nabble.com>
<1288380670849-2154713.post@n5.nabble.com>
<3854.1251386282@sss.pgh.pa.us>
<1288380670846-3242676.post@n5.nabble.com>
>
>>
>>> rawi <only4com@web.de> writes:
>>> The Error was caused because I used UNION in place of UNION ALL.
>>>
>>> I still don't understand why the ARRAY (path) could not be grouped...
>>
>> Yeah, it's an unimplemented feature --- there's no hashing support for
>> arrays. I hope to get that done for 8.5. In the meantime you have
>> to use UNION ALL there. If you really need to eliminate duplicate
>> rows, you can do that via DISTINCT in the outer query.
>
> I'm trying to do a similar sort of thing for a network containing ~9000
> edges. I'm using a WITH RECURSIVE subquery, an array to track
> visited edges and a test to see whether the id of the 'current' edge is
> already in that array, as per the examples in the PostgreSQL 9.0 docs.
> Initially my main query seemed to run indefinitely so I introduced a
> LIMIT. I then found that as the LIMIT was increased the number of
> non-distinct edges returned by the query grew at a far greater rate
> than the number of distinct edges (with LIMIT 50000 the number of
> distinct edges returned is only 628). Am I right in thinking that until
> arrays can be hashed that this issue could well limit the size of the
> networks that I can analyse, given a particular hardware config?
Got around the problem by learning pg/plsql and writing a non-recursive
breadth-first graph traversal function. It maintains an array of visited
nodes and a double-ended queue, implemented as an array, of encountered
nodes that require processing during the traversal. Not as fast as a 'WITH
RECURSIVE' SQL-only traversal for graphs containing only a few nodes but
much, much more efficient for graphs containing many nodes and a
considerable number of back edges.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/WITH-RECURSIVE-ARRAY-id-All-column-datatypes-must-be-hashabl...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: will@thearete.co.uk
Subject: Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
In-Reply-To: <1289125085332-3253813.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox