Calculated fileds in pg

On Sun, 24 Nov 2002, Jean-Christian Imbeault wrote:

Is it possible to set up a field during table creation to be a
"calculated" field, i.e. the value is calculated from other values.

For example:

Table A:

id integer primary key,
B_id integer references B(id),
cost integer default 0

Table B:

id integer primary key,
total integer -- select sum(cost) from A where B_id=id;

If not should I be looking at implementing this with a view or maybe a
trigger?

I'd probably do B as a view if it was this simple, because writing the
triggers for A sound to be problematic from a getting the concurrency
right without potential deadlocks standpoint.

Stephan Szabo wrote:

Table A:

id integer primary key,
B_id integer references B(id),
cost integer default 0

Table B:

id integer primary key,
total integer -- select sum(cost) from A where B_id=id;

I'd probably do B as a view if it was this simple, because writing the
triggers for A sound to be problematic from a getting the concurrency
right without potential deadlocks standpoint.

The tables are not *that* simple. Both have about 15 columns, *but*
there is just the one calculated column *and* it is as in the example,
just the sum of matching rows in the other table.

Here are the actual table schemas, would you still recommend a view? (I
know nothing about views so this would be my first attempt at them). And
you are right about the triggers being a problem for concurrency. I have
implemented the above as a trigger on every insert/update to Table B and
it is slow *and* I want to add other triggers but the concurrency aspect
is a little problematic.

Jc

Oops, forgot to post the schemas:

create table INVOICES (

id serial primary key,
member_id integer references MEMBERS(id),
submit_time timestamp (0) without time zone default
current_timestamp(0),
customer_charged boolean not null default false,
order_filled boolean not null default false,
double_checked boolean not null default false,
cancelled boolean not null default false,
cancel_time timestamp (0) ,
payment_returned boolean default false,
payment_type integer references payment_types(id),
member_comment text ,
addressee text ,
planned_pay_date date ,
payment_rcvd_date date ,
shipping_addr text not null,
contents_dsc text ,
req_del_date1 date ,
req_del_date2 date ,
req_del_date3 date ,
req_del_time1 integer references DEL_TIMES(id),
req_del_time2 integer references DEL_TIMES(id),
req_del_time3 integer references DEL_TIMES(id),
escalation_level int2 not null default 0,
cc_name text ,
cc_number text ,
cc_exp_date date ,
cc_holder_name text ,
payment_rcvd boolean default false,
total_price integer , -- this is the sum(price) of
matching columns in invoice_li
shipping_fee integer ,
delivery_method integer references DEL_METHODS(id),
admin_shipping_memo integer references ADMIN_SHIPPING_MEMOS(id)
);

AND

create table INVOICE_LI (

invoice_id integer references INVOICES(id) on
delete cascade,
prod_id text references PRODUCTS(id),
dist_id integer references distributors(id),
shop_id integer references CHARISMA_SHOPS(id)
default NULL,
quantity int2 not null,
old_qty int2 default 0,
price integer not null,
shipped boolean not null default false,
date_shipped timestamp (0) ,
dist_invoice integer references DIST_ORDERS(id)
default NULL,
item_status integer references DIST_ITEM_STATUS,
received boolean default false,
ship_now boolean default false,
exp_rcvd_date timestamp (0) ,
cancelled boolean default false,
cancel_time timestamp (0) ,
credited integer default 0,
need_to_credit integer default 0,

primary key (invoice_id, prod_id)
);

On Mon, 25 Nov 2002, Jean-Christian Imbeault wrote:

Stephan Szabo wrote:

Table A:

id integer primary key,
B_id integer references B(id),
cost integer default 0

Table B:

id integer primary key,
total integer -- select sum(cost) from A where B_id=id;

I'd probably do B as a view if it was this simple, because writing the
triggers for A sound to be problematic from a getting the concurrency
right without potential deadlocks standpoint.

The tables are not *that* simple. Both have about 15 columns, *but*
there is just the one calculated column *and* it is as in the example,
just the sum of matching rows in the other table.

Here are the actual table schemas, would you still recommend a view? (I

One question is how often do you actually look for INVOICES.total_price in
comparison to modifications to INVOICES.id and INVOICE_LI? If you tend
not to do lots of modifications but do alot of selects, then you probably
would want to consider a trigger solution. Otherwise, a base table and an
INVOICES view that adds the field seems reasonable. If you do lots of
selects on INVOICES but often don't need total_price then you can choose
whether to use the view or the base table depending on whether you care
about total_price or not.

know nothing about views so this would be my first attempt at them). And
you are right about the triggers being a problem for concurrency. I have
implemented the above as a trigger on every insert/update to Table B and
it is slow *and* I want to add other triggers but the concurrency aspect
is a little problematic.

It's fundamentally the same problem as foreign keys (except slightly
worse) in that updates to the cost of an line item row, an update of
which invoice it's associated with, an update of the id of an invoice, the
insertion or deletion of a line item all potentially make updates to an
invoice row and grab locks that might cause deadlock (although the
likelihood of a deadlocking pattern may be low) or at the very least you
might end up with serialization of concurrent transactions.