pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedresolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
5+ messages / 4 participants
[nested] [flat]
* resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
@ 2009-08-27 14:16 rawi <only4com@web.de>
0 siblings, 2 replies; 5+ messages in thread
From: rawi @ 2009-08-27 14:16 UTC (permalink / raw)
To: pgsql-sql
me again...
I figured it out...
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...
Bye
rawi
--
View this message in context: http://www.nabble.com/WITH-RECURSIVE%3A-ARRAY-id--All-column-datatypes-must-be-hashable-tp25167538p2...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
@ 2009-08-27 15:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: rawi <only4com@web.de>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2009-08-27 15:18 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
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.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
@ 2009-08-27 15:27 Rob Sargent <robjsargent@gmail.com>
parent: rawi <only4com@web.de>
1 sibling, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2009-08-27 15:27 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
In so much as id-ma-pa is near and dear to my heart, I would really
appreciate and performance metrics you might be able to share.
Especially size of person table, typical pedigree size and pedigree
retrieval time (tainted by hardware specs of course).
Thanks
rjs
rawi wrote:
> me again...
>
> I figured it out...
>
> 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...
>
> Bye
> rawi
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
@ 2010-10-29 19:31 Will Furnass <will@thearete.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Will Furnass @ 2010-10-29 19:31 UTC (permalink / raw)
To: pgsql-sql
>> 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? Can anyone think of a way to use a temporary table rather than an
array to store visited entities during a graph traversal as a means for
overcoming this problem?
FYI I've been running the following query as a test on PostgreSQL 9.0.
WITH RECURSIVE upstream_pipes( downstream_end, upstream_end, name, depth,
path, cycle) AS (
SELECT p.down_node, p.up_node, p.name, 1, ARRAY[p.name],
False
FROM pipes_table AS p
WHERE p.downstream_end = '61ESI5R0WC'
UNION ALL
SELECT p.downstream_end, p.upstream_end, p.name,
roi.depth + 1,
path || p.name, p.name = ANY(path)
FROM pipes_table AS p, upstream_pipes AS usp
WHERE p.downstream_end = usp.upstream_end
AND NOT cycle
AND ( NOT ( p.end_1_impassible AND p.node_1 =
p.downstream_end ) )
AND ( NOT ( p.end_2_impassible AND p.node_2 =
p.downstream_end ) )
),
q as (
SELECT name FROM build_upstream_roi limit 20000
)
SELECT COUNT(DISTINCT name), COUNT(name) FROM q
Cheers,
Will
--
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
@ 2010-11-07 10:18 Will Furnass <will@thearete.co.uk>
parent: Will Furnass <will@thearete.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Will Furnass @ 2010-11-07 10:18 UTC (permalink / raw)
To: pgsql-sql
>
>>
>>> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-07 10:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-27 14:16 resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable rawi <only4com@web.de>
2009-08-27 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-29 19:31 ` Will Furnass <will@thearete.co.uk>
2010-11-07 10:18 ` Will Furnass <will@thearete.co.uk>
2009-08-27 15:27 ` Rob Sargent <robjsargent@gmail.com>
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