From: Marc G. Fournier <scrappy@postgresql.org>
To: Marc G. Fournier <scrappy@postgresql.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: FOREIGN KEYs ... I think ...
Date: Thu, 5 Jan 2006 21:23:18 -0400 (AST)
Message-ID: <20060105212040.X1088@ganymede.hub.org> (raw)
In-Reply-To: <20060104213210.A1088@ganymede.hub.org>
References: <20060104213210.A1088@ganymede.hub.org>
Thanks to everyone for the responses ... ended up doing a trigger on the
comments table that updates another table to maintain a "pointer" to the
active record ... sped up the query that was hampering us from ~26 000ms
to 47ms ... the killer part of the query was that each time it was havin
gto figure out the 'active remark record' doing a 'max(create_time)' ...
On Wed, 4 Jan 2006, Marc G. Fournier wrote:
>
> I'm not sure if I'm looking at (for) the right thing or not, but if I am,
> then I'm not finding any useful examples :(
>
> I have two tables, simplified as:
>
> CREATE TABLE incident_summary (
> id serial,
> subject text,
> status boolean
> );
>
> CREATE TABLE incident_comments (
> id serial,
> incident_id int4,
> body text,
> comment_date timestamp,
> status boolean
> );
>
> Now, what I want to do is add a FOREIGN KEY (again, I think) that when
> incident_summary.status is changed (either closed, or reopened), the
> associated records in incident_comments are changed to the same state ...
>
> It *looks* like it should be simple enough, I want incident_comments.status
> to change to incident_summary.status whenever incident_summary.status changes
> ... since I'm finding nothing searching on FOREIGN KEYS, I'm guessing that
> I'm looking at the wrong thing ...
>
> So, what should I be searching on / reading for this one? Pointers
> preferred, especially one with some good examples :)
>
> Thanks ...
>
>
> ----
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
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: scrappy@postgresql.org
Subject: Re: FOREIGN KEYs ... I think ...
In-Reply-To: <20060105212040.X1088@ganymede.hub.org>
* 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