pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery to select a linked list
12+ messages / 6 participants
[nested] [flat]
* query to select a linked list
@ 2007-05-09 12:55 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 5 replies; 12+ messages in thread
From: Louis-David Mitterrand @ 2007-05-09 12:55 UTC (permalink / raw)
To: pgsql-sql
Hi,
To build a threaded forum application I came up the following schema:
forum
------
id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
id_parent| integer|
subject | text | not null
message | text |
Each message a unique id_forum and an id_parent pointing to the replied
post (empty if first post).
How can I build an elegant query to select all messages in a thread?
Thanks,
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 13:24 Gregory Stark <stark@enterprisedb.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
4 siblings, 2 replies; 12+ messages in thread
From: Gregory Stark @ 2007-05-09 13:24 UTC (permalink / raw)
To: pgsql-sql
"Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> writes:
> Each message a unique id_forum and an id_parent pointing to the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a thread?
You would need recursive queries which Postgres doesn't support. There is a
patch out there to add support but I don't think it's up-to-date with 8.2 and
in any case the resulting queries can be quite intense.
I would recommend you look into the contrib module named "ltree". It's easy to
use and works well with the gist indexes. It does require changing your data
model denormalizing it slightly which makes it hard to "reparent" children,
but if that isn't an operation you have to support I think it makes most other
operations you might want to do much easier to support.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 13:28 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Gregory Stark <stark@enterprisedb.com>
1 sibling, 0 replies; 12+ messages in thread
From: Louis-David Mitterrand @ 2007-05-09 13:28 UTC (permalink / raw)
To: pgsql-sql
On Wed, May 09, 2007 at 02:24:22PM +0100, Gregory Stark wrote:
> "Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> writes:
>
> > Each message a unique id_forum and an id_parent pointing to the replied
> > post (empty if first post).
> >
> > How can I build an elegant query to select all messages in a thread?
>
> You would need recursive queries which Postgres doesn't support. There is a
> patch out there to add support but I don't think it's up-to-date with 8.2 and
> in any case the resulting queries can be quite intense.
>
> I would recommend you look into the contrib module named "ltree". It's easy to
> use and works well with the gist indexes. It does require changing your data
> model denormalizing it slightly which makes it hard to "reparent" children,
> but if that isn't an operation you have to support I think it makes most other
> operations you might want to do much easier to support.
After looking around a little I came to the same conclusions.
Thanks for you help,
Cheers,
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 13:29 Aaron Bono <postgresql@aranya.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
4 siblings, 1 reply; 12+ messages in thread
From: Aaron Bono @ 2007-05-09 13:29 UTC (permalink / raw)
To: pgsql-sql
On 5/9/07, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
wrote:
>
> Hi,
>
> To build a threaded forum application I came up the following schema:
>
> forum
> ------
> id_forum | integer| not null default
> nextval('forum_id_forum_seq'::regclass)
> id_parent| integer|
> subject | text | not null
> message | text |
>
> Each message a unique id_forum and an id_parent pointing to the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a thread?
>
> Thanks,
>
Unlike Oracle, PostgreSQL doesn't have anything like a connect by so you
would need to write your own stored procedure for that (if new versions of
PostgreSQL will have connect by, let me know guys).
What I did was add a little redundancy to my forum tables and had a table
structure kind of like this:
forum
forum_id BIGSERIAL PK,
name VARCHAR(50)
forum_topic
forum_topic_id BIGSERIAL PK,
forum_id BIGINT FK to forum
forum_post
forum_post_id BIGSERIAL PK,
create_dt TIMESTAMP,
subject VARCHAR(255),
message TEXT,
forum_topic_id BIGINT FK to forum_topic
and if you want threading, you add a parent_forum_post_id to forum_post
(this is where you get the redundancy since only the top forum_post record
needs a reference to forum_topic and forum_topic wouldn't even really be
needed.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 13:31 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
4 siblings, 0 replies; 12+ messages in thread
From: Achilleas Mantzios @ 2007-05-09 13:31 UTC (permalink / raw)
To: pgsql-sql
Στις Τετάρτη 09 Μάιος 2007 15:55, ο/η Louis-David Mitterrand έγραψε:
> Hi,
>
> To build a threaded forum application I came up the following schema:
>
> forum
> ------
> id_forum | integer| not null default
> nextval('forum_id_forum_seq'::regclass) id_parent| integer|
> subject | text | not null
> message | text |
>
> Each message a unique id_forum and an id_parent pointing to the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a thread?
>
Your question is about storing hierarchies in postgresql.
One way is to use the genealogical approach, where we store for
any node the path to its root.
I have used this technique to store description of tanker vessels machinery
(over 1M items) and the plan maintenance on them, and the performance is very
good, while the representation is highly intuitive and flexible,
unlike some wierd approcahes i have hit on.
When i did a small research on the complexity/index usage on various
operations (UPDATE, INSERT, DELETE, SELECT), the performance
was at least as good as the "nested pair" approch that many seemed to
promote.
You add a column "parents" (rather than just the parentid) as an integer[].
For every node you store the path to the root node starting from the most
immediate ancestor.
Then you just make an index on this column using the intarray contrib package.
Then you can easily query for nodes under a specific node, or for nodes just
one level below a specific node, nodes with no descendents (leaf nodes)
etc...
Of course you could do smth simpler, but in the long run,
representing data in the correct way will certainly pay off.
> Thanks,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 14:30 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
4 siblings, 1 reply; 12+ messages in thread
From: Louis-David Mitterrand @ 2007-05-09 14:30 UTC (permalink / raw)
To: pgsql-sql
On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote:
> Hi,
>
> To build a threaded forum application I came up the following schema:
>
> forum
> ------
> id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
> id_parent| integer|
> subject | text | not null
> message | text |
>
> Each message a unique id_forum and an id_parent pointing to the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a thread?
I am trying to write a recursive pl/sql function to return all thread
children:
create or replace function forum_children(integer) returns setof forum as $$
declare
rec record;
begin
for rec in select * from forum where $1 in (id_parent,id_forum) loop
select * from forum_children(rec.id_forum);
return next rec;
end loop;
return;
end;
$$ language 'plpgsql';
But it does not work as intended (infinite loop?).
What did I miss?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 14:33 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 12+ messages in thread
From: Louis-David Mitterrand @ 2007-05-09 14:33 UTC (permalink / raw)
To: pgsql-sql
On Wed, May 09, 2007 at 04:30:21PM +0200, Louis-David Mitterrand wrote:
> On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote:
> > Hi,
> >
> > To build a threaded forum application I came up the following schema:
> >
> > forum
> > ------
> > id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
> > id_parent| integer|
> > subject | text | not null
> > message | text |
> >
> > Each message a unique id_forum and an id_parent pointing to the replied
> > post (empty if first post).
> >
> > How can I build an elegant query to select all messages in a thread?
>
> I am trying to write a recursive pl/sql function to return all thread
> children:
>
> create or replace function forum_children(integer) returns setof forum as $$
> declare
> rec record;
> begin
>
> for rec in select * from forum where $1 in (id_parent,id_forum) loop
Oops, I meant :
for rec in select * from forum where id_parent=$1 loop
which works fine.
Sorry,
> select * from forum_children(rec.id_forum);
> return next rec;
>
> end loop;
>
> return;
>
> end;
> $$ language 'plpgsql';
>
>
> But it does not work as intended (infinite loop?).
>
> What did I miss?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 18:03 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Gregory Stark <stark@enterprisedb.com>
1 sibling, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2007-05-09 18:03 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: pgsql-sql
On Wed, 2007-05-09 at 08:24, Gregory Stark wrote:
> "Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> writes:
>
> > Each message a unique id_forum and an id_parent pointing to the replied
> > post (empty if first post).
> >
> > How can I build an elegant query to select all messages in a thread?
>
> You would need recursive queries which Postgres doesn't support. There is a
> patch out there to add support but I don't think it's up-to-date with 8.2 and
> in any case the resulting queries can be quite intense.
>
> I would recommend you look into the contrib module named "ltree". It's easy to
> use and works well with the gist indexes. It does require changing your data
> model denormalizing it slightly which makes it hard to "reparent" children,
> but if that isn't an operation you have to support I think it makes most other
> operations you might want to do much easier to support.
Are you sure the tablefunc functions, which include both connectby and
crosstab functions, aren't up to date with 8.2? They certainly are up
to 8.1, where I'm running them right now on my workstation. They built
for 8.2 and installed, but I haven't tried using them.
I would think that connectby is at least worth looking into.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 18:04 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 12+ messages in thread
From: Scott Marlowe @ 2007-05-09 18:04 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
On Wed, 2007-05-09 at 08:29, Aaron Bono wrote:
> On 5/9/07, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
> wrote:
> Hi,
>
> To build a threaded forum application I came up the following
> schema:
>
> forum
> ------
> id_forum | integer| not null default
> nextval('forum_id_forum_seq'::regclass)
> id_parent| integer|
> subject | text | not null
> message | text |
>
> Each message a unique id_forum and an id_parent pointing to
> the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a
> thread?
>
> Thanks,
>
> Unlike Oracle, PostgreSQL doesn't have anything like a connect by so
> you would need to write your own stored procedure for that (if new
> versions of PostgreSQL will have connect by, let me know guys).
Take a look at the tablefunc in contrib, that seems to provide
connectby. I've only ever used the crosstab functions in there, but I
can't see what would have broken in connectby with 8.2 or anything.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 23:16 Gregory Stark <stark@enterprisedb.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 12+ messages in thread
From: Gregory Stark @ 2007-05-09 23:16 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-sql
"Scott Marlowe" <smarlowe@g2switchworks.com> writes:
> Are you sure the tablefunc functions, which include both connectby and
> crosstab functions, aren't up to date with 8.2? They certainly are up
> to 8.1, where I'm running them right now on my workstation. They built
> for 8.2 and installed, but I haven't tried using them.
>
> I would think that connectby is at least worth looking into.
Uhm, no, I guess I'm not sure. I didn't realize it was in the tablefunc module
either.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-09 23:49 Robert Edwards <bob@cs.anu.edu.au>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
4 siblings, 1 reply; 12+ messages in thread
From: Robert Edwards @ 2007-05-09 23:49 UTC (permalink / raw)
To: pgsql-sql
Hi Louis-David,
I also have written a forum application using PostgreSQL.
My schema has a "threadid" for each posting, which is actually also the
"messageid" of the first posting in the thread, but that is irrelevant.
I can then just select all messages belonging to that thread. The actual
hierarchy of messages (which posting is in response to which) is dealt
with by a "parentid", identifying the messageid of the post being
responded to. Sorting that out is done by the middleware (PHP in this
case) - the SQL query simply returns all messages in the thread in a
single query. Because our database is somewhat busy, I have opted to
keep the queries to the database simple and let the middleware sort
out the heirarchical structure (which it is quite good at).
I hope this helps.
Bob Edwards.
Louis-David Mitterrand wrote:
> Hi,
>
> To build a threaded forum application I came up the following schema:
>
> forum
> ------
> id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
> id_parent| integer|
> subject | text | not null
> message | text |
>
> Each message a unique id_forum and an id_parent pointing to the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a thread?
>
> Thanks,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: query to select a linked list
@ 2007-05-10 08:05 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Robert Edwards <bob@cs.anu.edu.au>
0 siblings, 0 replies; 12+ messages in thread
From: Louis-David Mitterrand @ 2007-05-10 08:05 UTC (permalink / raw)
To: pgsql-sql
On Thu, May 10, 2007 at 09:49:32AM +1000, Robert Edwards wrote:
>
> Hi Louis-David,
>
> I also have written a forum application using PostgreSQL.
>
> My schema has a "threadid" for each posting, which is actually also the
> "messageid" of the first posting in the thread, but that is irrelevant.
>
> I can then just select all messages belonging to that thread. The actual
> hierarchy of messages (which posting is in response to which) is dealt
> with by a "parentid", identifying the messageid of the post being
> responded to. Sorting that out is done by the middleware (PHP in this
> case) - the SQL query simply returns all messages in the thread in a
> single query. Because our database is somewhat busy, I have opted to
> keep the queries to the database simple and let the middleware sort
> out the heirarchical structure (which it is quite good at).
>
> I hope this helps.
This helps a lot, thanks.
I just wrote a little pl/sql function to compensate for the absence of a
threadid in my schema:
create or replace function forum_children(integer) returns setof forum as $$
declare
rec record;
subrec record;
begin
for rec in select * from forum where id_parent=$1 loop
return next rec;
for subrec in select * from forum_children(rec.id_forum) loop
return next subrec;
end loop;
end loop;
return;
end;
$$ language 'plpgsql';
But in the end it might just be more convenient and clear to have that
threadid column as you did.
Sorting in middleware (perl in my case) also seems like good compromise.
Cheers,
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2007-05-10 08:05 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-09 12:55 query to select a linked list Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-05-09 13:24 ` Gregory Stark <stark@enterprisedb.com>
2007-05-09 13:28 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-05-09 18:03 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-05-09 23:16 ` Gregory Stark <stark@enterprisedb.com>
2007-05-09 13:29 ` Aaron Bono <postgresql@aranya.com>
2007-05-09 18:04 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-05-09 13:31 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-05-09 14:30 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-05-09 14:33 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-05-09 23:49 ` Robert Edwards <bob@cs.anu.edu.au>
2007-05-10 08:05 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
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