Thread: How best to implement a multi-table constraint?
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
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
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 >
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 >
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 -----
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 -----