Thread: How best to implement a multi-table constraint?

How best to implement a multi-table constraint?

From
"Karl Nack"
Date:
Hello all,

I'm a bit of a newb designing a database to hold landcover information for
properties in a city. Here's some simple sample data:

property:
property_name*, property_area
-----------------------------
sample house, 2500


property_landcover:
property_name*, landcover_name*, landcover_area
-----------------------------------------------
sample house, building, 1000
sample house, grass, 1000
sample house, concrete, 500


Now, I need to check that the sum of landcover_area for a property matches
the property_area.

It seems like I have three obvious options:

1. A constraint trigger that sums up landcover area and compares it to the
property area.

Downside: The trigger will run for every row that's updated in these two
tables, although it only needs to run once for each property.


2. A statement-level trigger that does the same thing as #1.

Downside: Since I don't have access to the updated rows, I'll have to
check the entire property table against the entire property_landcover
table. It seems like this could get expensive if either of these tables
gets very large.


3. Use a 3rd table to hold the total landcover area for each property. Use
row-level triggers to keep this 3rd table updated. Use a statement-level
trigger (or table constraint) to ensure the total landcover area matches
the property area.

Downside: Although I avoid redundant checks, my understanding is that
UPDATE is a fairly expensive operation, so it might not actually perform
any better.


Although my tables are small right now, they may potentially have to hold
an entire city's worth of properties, so I'm interested in finding a
solution that scales.

Can anyone offer some feedback or suggestions on which of these options to
use? Or perhaps even another solution that hasn't occurred to me?

Thanks!

-Karl

Can PL/PGSQL function return multiple SETOFs

From
Date:
Is something like this possible?
CREATE testsetof( IN toad_id integer)   RETURNS SETOF road_table, SETOF int4
AS $BODY$....

If yes, is this the way to do 'RETURN NEXT'?
    RETURN NEXT road_table_row, an_integer;

Thanks
CYW



Re: Can PL/PGSQL function return multiple SETOFs

From
"Pavel Stehule"
Date:
Hello

plpgsql should return only one set. You should to returns set of
cursors - that is real multisets.
http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

regards
Pavel Stehule

2008/10/21  <cyw@dls.net>:
> Is something like this possible?
> CREATE testsetof( IN toad_id integer)   RETURNS SETOF road_table, SETOF int4
> AS $BODY$....
>
> If yes, is this the way to do 'RETURN NEXT'?
>   RETURN NEXT road_table_row, an_integer;
>
> Thanks
> CYW
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: How best to implement a multi-table constraint?

From
"Matthias Karlsson"
Date:
Why do you need to store the total area at all (property_area)? This
value can easily be calculated with an group by query.

On Mon, Oct 20, 2008 at 10:56 PM, Karl Nack <pglists@futurityinc.com> wrote:
> Hello all,
>
> I'm a bit of a newb designing a database to hold landcover information for
> properties in a city. Here's some simple sample data:
>
> property:
> property_name*, property_area
> -----------------------------
> sample house, 2500
>
>
> property_landcover:
> property_name*, landcover_name*, landcover_area
> -----------------------------------------------
> sample house, building, 1000
> sample house, grass, 1000
> sample house, concrete, 500
>
>
> Now, I need to check that the sum of landcover_area for a property matches
> the property_area.
>
> It seems like I have three obvious options:
>
> 1. A constraint trigger that sums up landcover area and compares it to the
> property area.
>
> Downside: The trigger will run for every row that's updated in these two
> tables, although it only needs to run once for each property.
>
>
> 2. A statement-level trigger that does the same thing as #1.
>
> Downside: Since I don't have access to the updated rows, I'll have to
> check the entire property table against the entire property_landcover
> table. It seems like this could get expensive if either of these tables
> gets very large.
>
>
> 3. Use a 3rd table to hold the total landcover area for each property. Use
> row-level triggers to keep this 3rd table updated. Use a statement-level
> trigger (or table constraint) to ensure the total landcover area matches
> the property area.
>
> Downside: Although I avoid redundant checks, my understanding is that
> UPDATE is a fairly expensive operation, so it might not actually perform
> any better.
>
>
> Although my tables are small right now, they may potentially have to hold
> an entire city's worth of properties, so I'm interested in finding a
> solution that scales.
>
> Can anyone offer some feedback or suggestions on which of these options to
> use? Or perhaps even another solution that hasn't occurred to me?
>
> Thanks!
>
> -Karl
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: How best to implement a multi-table constraint?

From
"Karl Nack"
Date:
I agree, but it seems to me that property_area isn't a strictly derived
value.

It's possible to change the makeup of a property's landcover -- for
example, remove some concrete and plant more grass, or add an extension to
the building -- but the overall property area should remain constant. I
feel like I should probably include some kind of constraint to enforce
this.

Am I needlessly over-complicating this?

-Karl


----- Original Message -----
From: matthias@yacc.se
Sent: Tue, October 21, 2008 7:31
Subject:Re: [GENERAL] How best to implement a multi-table constraint?


Why do you need to store the total area at all (property_area)? This
value can easily be calculated with an group by query.

On Mon, Oct 20, 2008 at 10:56 PM, Karl Nack <pglists@futurityinc.com> wrote:
> Hello all,
>
> I'm a bit of a newb designing a database to hold landcover information for
> properties in a city. Here's some simple sample data:
>
> property:
> property_name*, property_area
> -----------------------------
> sample house, 2500
>
>
> property_landcover:
> property_name*, landcover_name*, landcover_area
> -----------------------------------------------
> sample house, building, 1000
> sample house, grass, 1000
> sample house, concrete, 500
>
>
> Now, I need to check that the sum of landcover_area for a property matches
> the property_area.
>
> It seems like I have three obvious options:
>
> 1. A constraint trigger that sums up landcover area and compares it to the
> property area.
>
> Downside: The trigger will run for every row that's updated in these two
> tables, although it only needs to run once for each property.
>
>
> 2. A statement-level trigger that does the same thing as #1.
>
> Downside: Since I don't have access to the updated rows, I'll have to
> check the entire property table against the entire property_landcover
> table. It seems like this could get expensive if either of these tables
> gets very large.
>
>
> 3. Use a 3rd table to hold the total landcover area for each property. Use
> row-level triggers to keep this 3rd table updated. Use a statement-level
> trigger (or table constraint) to ensure the total landcover area matches
> the property area.
>
> Downside: Although I avoid redundant checks, my understanding is that
> UPDATE is a fairly expensive operation, so it might not actually perform
> any better.
>
>
> Although my tables are small right now, they may potentially have to hold
> an entire city's worth of properties, so I'm interested in finding a
> solution that scales.
>
> Can anyone offer some feedback or suggestions on which of these options to
> use? Or perhaps even another solution that hasn't occurred to me?
>
> Thanks!
>
> -Karl
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

----- End of original message -----


Re: How best to implement a multi-table constraint?

From
"Karl Nack"
Date:
I agree, but it seems to me that property_area isn't a strictly derived
value.

It's possible to change the makeup of a property's landcover -- for
example, remove some concrete and plant more grass, or add an extension to
the building -- but the overall property area should remain constant. I
feel like I should probably include some kind of constraint to enforce
this.

Am I needlessly over-complicating this?

-Karl




Karl Nack

Futurity, Inc.
773-506-2007



----- Original Message -----
From: matthias@yacc.se
Sent: Tue, October 21, 2008 7:31
Subject:Re: [GENERAL] How best to implement a multi-table constraint?


Why do you need to store the total area at all (property_area)? This
value can easily be calculated with an group by query.

On Mon, Oct 20, 2008 at 10:56 PM, Karl Nack <pglists@futurityinc.com> wrote:
> Hello all,
>
> I'm a bit of a newb designing a database to hold landcover information for
> properties in a city. Here's some simple sample data:
>
> property:
> property_name*, property_area
> -----------------------------
> sample house, 2500
>
>
> property_landcover:
> property_name*, landcover_name*, landcover_area
> -----------------------------------------------
> sample house, building, 1000
> sample house, grass, 1000
> sample house, concrete, 500
>
>
> Now, I need to check that the sum of landcover_area for a property matches
> the property_area.
>
> It seems like I have three obvious options:
>
> 1. A constraint trigger that sums up landcover area and compares it to the
> property area.
>
> Downside: The trigger will run for every row that's updated in these two
> tables, although it only needs to run once for each property.
>
>
> 2. A statement-level trigger that does the same thing as #1.
>
> Downside: Since I don't have access to the updated rows, I'll have to
> check the entire property table against the entire property_landcover
> table. It seems like this could get expensive if either of these tables
> gets very large.
>
>
> 3. Use a 3rd table to hold the total landcover area for each property. Use
> row-level triggers to keep this 3rd table updated. Use a statement-level
> trigger (or table constraint) to ensure the total landcover area matches
> the property area.
>
> Downside: Although I avoid redundant checks, my understanding is that
> UPDATE is a fairly expensive operation, so it might not actually perform
> any better.
>
>
> Although my tables are small right now, they may potentially have to hold
> an entire city's worth of properties, so I'm interested in finding a
> solution that scales.
>
> Can anyone offer some feedback or suggestions on which of these options to
> use? Or perhaps even another solution that hasn't occurred to me?
>
> Thanks!
>
> -Karl
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

----- End of original message -----