> -----Original Message----- > From: Jeff Davis [mailto:[EMAIL PROTECTED] > Sent: 19 January 2005 21:33 > To: Alvaro Herrera > Cc: Mark Cave-Ayland; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Much Ado About COUNT(*) > > > > To fill in some details I think what he's saying is this: > > => create table foo(...); > => create table foo_count(num int); > => insert into foo_count values(0); > => create table foo_change(num int); > > then create a trigger "after delete on foo" that does "insert > into foo_change values(-1)" and a trigger "after insert on > foo" that inserts a +1 into foo_change. > > Periodically, do: > => begin; > => set transaction isolation level serializable; > => update foo_count set num=num+(select sum(num) from > foo_change); => delete from foo_change; => commit; => VACUUM; > > And then any time you need the correct count(*) value, do > instead: => select sum(num) from (select num from foo_count > union select num from foo_change); > > And that should work. I haven't tested this exact example, so > I may have overlooked something. > > Hope that helps. That way, you don't have huge waste from the > second table, and also triggers maintain it for you and you > don't need to think about it. > > Regards, > Jeff Davis
Hi Jeff, Thanks for the information. I seem to remember something similar to this being discussed last year in a similar thread. My only real issue I can see with this approach is that the trigger is fired for every row, and it is likely that the database I am planning will have large inserts of several hundred thousand records. Normally the impact of these is minimised by inserting the entire set in one transaction. Is there any way that your trigger can be modified to fire once per transaction with the number of modified rows as a parameter? Many thanks, Mark. ------------------------ WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
