Date: Sun, 7 Nov 2010 02:18:05 -0800 (PST)
From: Will Furnass <will@thearete.co.uk>
To: pgsql-sql@postgresql.org
Message-ID: <1289125085332-3253813.post@n5.nabble.com>
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>
Subject: Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must
	be hashable
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


> 
>> 
>>> 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-hashable-tp2154712p3253813.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.