Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable

>
>>
>>> rawi <only4com(at)web(dot)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.