Re: sequences and "addval('myseq', value)" - Mailing list pgsql-hackers

In the last exciting episode, pgsql@mohawksoft.com wrote:
>> pgsql@mohawksoft.com writes:
>>> Anyway, I'm not quite getting the idea of caching sequence values. I
>>> understand the performance benefits, but it seems problematic across
>>> multiple backends, almost ensuring "holes" in the sequence of numbers.
>>
>> The point is to reduce lock contention on the sequence table.  Since
>> lack-of-holes is explicitly *not* a design goal, there is no downside
>> that I see.
>>
> I knew that, but it wasn't until I thought of using a sequence as a shared
> variable that it sort of hit me.
>
> The question is, what do you think of an "addval" function for sequences.
> As used:
>
> Executed in a trigger:
>
> select addval('mysum', val);
>
> Executed:
> select currval('mysum');
>
> Instead of:
>
> select sum(val) from largetable;

That seems completely ridiculous, as far as I can see.

The fundamental problem with is that sequence updates (at least the
ones resulting from pulling values from them) "commit themselves"
irrespective of whether the transaction does.

> The problem I, and I know many other people are having, is that
> large sums can not be obtained without a table scan. A summary table
> can be created, but if you have any sort of volume, you have to
> vacuum the summary table constantly.
>
> Using the sequence construct as sort of an accumulator just might
> fit the bill for this sort of thing.

No, it would not.

What would fit the bill would be the use of some sort of "accumulator
table" that would get an insert every time the main table got touched.
The inserts would be within the same transaction context, and
therefore "MVCC-valid."

create table summary_table ( id serial not null unique, tname text not null, rows integer
);

create table large_table ( id serial not null unique, name text, txn_date timestamptz default now(), quantity integer
notnull, units text not null, price numeric(12,2), value numeric(12,2)
 
);

insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);
insert into large_table (name, quantity, units, price, value) values
('foo', 1, 'units', 25, 25);
insert into large_table (name, quantity, units, price, value) values
('foo', 2, 'units', 25, 50);
insert into large_table (name, quantity, units, price, value) values
('foo', 3, 'units', 25, 75);
insert into large_table (name, quantity, units, price, value) values
('foo', 4, 'units', 25, 100);


create or replace function tsummary_add() returns trigger as
'begin  insert into summary_table (tname, rows) values (''large_table'',
1);       return NULL;end;' language 'plpgsql';

create or replace function tsummary_del() returns trigger as
'begin  insert into summary_table (tname, rows) values (''large_table'',
-1);       return NULL;end;' language 'plpgsql';

create or replace function tsummary_get () returns integer as
'declare  prec record;begin  select sum(rows) as sum into prec from summary_table where tname =
''large_table'';  return prec.sum;end;' language 'plpgsql';

create or replace function tsummary_rewrite(integer) returns integer
as
'declare  prec record;  c_max integer;  total integer;begin  if $1 > 1 then     c_max := 1000;  else     c_max := $1;
endif;  for prec in select rows, id from summary_table where tname =
 
''large_table'' limit c_max loop     total := total + prec.rows;     delete from summary_table where id = prec.id;  end
loop; insert into summary_table (tname, rows) values (''large_table'',
 
total);  return c_max;end;' language 'plpgsql';

begin;
insert into summary_table (tname, rows) values ('large_table', (select count(*) from large_table));
create trigger tsummary_add after insert on large_table for each row
execute procedure tsummary_add(); 
create trigger tsummary_del after delete on large_table for each row
execute procedure tsummary_del(); 
commit;

Every time you add a row to large_table, an entry goes into
summary_table for it.  Once in a while, you want to run
tsummary_rewrite() to shorten summary_table.

This isn't _quite_ right, but it's the kind of approach that could
work.
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/nonrdbms.html
"Open  Software and  freeing source  code isn't  socialism.   It isn't
socialist.  It's neither socialist nor capitalist; it just is."
-- Arthur <afrain@usa.net>