Horton

Thread

  1. how to cache subtotals -- with subselect?

    Will Trillich <will@serensoft.com> — 2003-03-03T06:44:25Z

    i'm having some trouble finding the error here -- we're trying
to cache subtotals from subset tables:

db=# select
db-#    -- person last-name:
db-#    p.lname,
db-#    -- CACHED subtotals where team involvement == 'client':
db-#    p.been_client,
db-#    -- LIVE subtotals where team involvement == 'client':
db-#    (
db(#      select count(*)
db(#      from team t join involv_v v on (t.involv_v=v.id)
db(#      where t.person=p.id and v.name='Client'
db(#    ) as ct
db-# from _person p;

  lname   | been_client | ct 
----------+-------------+----
 Trillich |           4 |  0 <== whoops
 Tharp    |           0 |  0
 Stiles   |           0 |  0
(3 rows)

here, 'trillich' has person.been_client = 4 which should instead
be 0 (that is, of the projects he's involved with, none are
as a 'client'). so...

db=#  update _person set
db-#  been_client = (
db(#     select count(*) from team t join involv_v v on (v.id=t.involv_v)
db(#     where v.name='Client' and t.person=person.id
db(#  );
NOTICE:  Adding missing FROM-clause entry in subquery for table "person"
UPDATE 3

three? that's not right. and what's up with adding a from
clause? the outer query already has a reference to _person which
the inner needs to use.

db=# select lname,been_client from person;
  lname   | been_client 
----------+-------------
 Trillich |           4
 Tharp    |           4
 Stiles   |           4
(3 rows)

ah. it used a whole 'nother instance of '_person' in the
subquery, not linking the inner query to the outer. maybe i can
alias the update?

db=#  update _person as p set
db-#  been_client = (
db(#     select count(*) from team t join involv_v v on (v.id=t.involv_v)
db(#     where v.name='Client' and t.person=p.id
db(#  );
ERROR:  parser: parse error at or near "as"

that's not it, either. clue-club welcome.


===


we're also trying to keep up-to-date with inserts and updates via
the view-and-"do instead"-rule theory:

create view team as
select
	t.id,
	t.project,
	t.person, -- foreign key into _person.id
	-- involv_v is a validation table, containing only
	-- 'vendor' and 'partner' and 'client'
	t.involv_v -- foreign key into involv_v.id
from
	_team t
;


CREATE RULE team_add AS
ON INSERT TO team
DO INSTEAD (
	INSERT INTO _team (
		project,
		person,
		involv_v
	) VALUES (
		NEW.project,
		NEW.person,
		NEW.involv_v
	);
	-- if adding a client-involvement, bump person's counter
	UPDATE _person SET
		been_client = been_client + 1
	WHERE
		id            = NEW.person
		AND
		involv_v.id   = NEW.involv_v
		AND
		involv_v.name = 'Client'
	;
);


CREATE RULE team_edit AS
ON UPDATE TO team
DO INSTEAD (
	UPDATE _team SET
		project  = NEW.project,
		person   = NEW.person,
		involv_v = NEW.involv_v
	WHERE
		id       = OLD.id
	;
	-- if OLD record was client involvement, decrement
	-- person's counter:
	UPDATE _person SET
		been_client = been_client - 1
	WHERE
		id            = OLD.person
		AND
		involv_v.id   = OLD.involv_v
		AND
		involv_v.name = 'Client'
	;
	-- if NEW record will be client involvement, increment
	-- person's counter:
	UPDATE _person SET
		been_client = been_client + 1
	WHERE
		id            = NEW.person
		AND
		involv_v.id   = NEW.involv_v
		AND
		involv_v.name = 'Client'
	;
);

the insert works, but the update doesn't -- probably endemic to
the beast (guessing that an update here isn't allowed to flow
into an insert there, at least not via a rule?)

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

  2. Re: how to cache subtotals -- with subselect?

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-03-03T07:17:45Z

    On Mon, 3 Mar 2003, Will Trillich wrote:

> i'm having some trouble finding the error here -- we're trying
> to cache subtotals from subset tables:
>
> db=# select
> db-#    -- person last-name:
> db-#    p.lname,
> db-#    -- CACHED subtotals where team involvement == 'client':
> db-#    p.been_client,
> db-#    -- LIVE subtotals where team involvement == 'client':
> db-#    (
> db(#      select count(*)
> db(#      from team t join involv_v v on (t.involv_v=v.id)
> db(#      where t.person=p.id and v.name='Client'
> db(#    ) as ct
> db-# from _person p;
>
>   lname   | been_client | ct
> ----------+-------------+----
>  Trillich |           4 |  0 <== whoops
>  Tharp    |           0 |  0
>  Stiles   |           0 |  0
> (3 rows)
>
> here, 'trillich' has person.been_client = 4 which should instead
> be 0 (that is, of the projects he's involved with, none are
> as a 'client'). so...
>
> db=#  update _person set
> db-#  been_client = (
> db(#     select count(*) from team t join involv_v v on (v.id=t.involv_v)
> db(#     where v.name='Client' and t.person=person.id
> db(#  );
> NOTICE:  Adding missing FROM-clause entry in subquery for table "person"
> UPDATE 3
>
> three? that's not right. and what's up with adding a from
> clause? the outer query already has a reference to _person which
> the inner needs to use.

But the inner doesn't have a reference to _person, it has a reference to
person, was it intentional to use different tables in those two places?

  3. Re: how to cache subtotals -- with subselect?

    Will Trillich <will@serensoft.com> — 2003-03-03T07:58:21Z

    On Sun, Mar 02, 2003 at 11:17:45PM -0800, Stephan Szabo wrote:
> On Mon, 3 Mar 2003, Will Trillich wrote:
> > db=#  update _person set
> > db-#  been_client = (
> > db(#     select count(*) from team t join involv_v v on (v.id=t.involv_v)
> > db(#     where v.name='Client' and t.person=person.id
> > db(#  );
> > NOTICE:  Adding missing FROM-clause entry in subquery for table "person"
> > UPDATE 3
> >
> But the inner doesn't have a reference to _person, it has a reference to
> person, was it intentional to use different tables in those two places?

<blush>
um, no. sure wasn't.
</blush>

i'll go away now. (thanks for the eyeballs!)

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !