Received: from www.actarg.com (root@[209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA60653 for ; Thu, 18 May 2000 12:53:51 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from actarg.com (root@[192.168.2.16]) by www.actarg.com (8.8.7/8.8.7) with ESMTP id JAA26503 for ; Thu, 18 May 2000 09:55:07 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by actarg.com (8.9.3/8.9.3) with ESMTP id KAA13926 for ; Thu, 18 May 2000 10:53:51 -0600 Message-ID: <3924201E.636E13F8@actarg.com> Date: Thu, 18 May 2000 10:53:51 -0600 From: Kyle Bateman Organization: Action Target Inc X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Pre-toast toast to toast Content-Type: multipart/mixed; boundary="------------8B8987CCDEF1B1DB74CA14C0" X-Archive-Number: 200005/59 This is a multi-part message in MIME format. --------------8B8987CCDEF1B1DB74CA14C0 Content-Type: multipart/alternative; boundary="------------C890E416807C9E009E665373" --------------C890E416807C9E009E665373 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I'm posting this for the benefit of others who want to store large data but can't wait for 7.1. This doesn't have all the benefits of TOAST, but can store large data without any effort in the front end. This example is from our newly created document storage tables. With the advent of unlimited query size in 7.0, we can send a large chunk of data to a TCL function. The function slices it up into smaller pieces and stores it in a related data table. An analogous function puts it back together when requested. I successfully stored and retrieved /usr/dict/words without any problems. I haven't tried anything bigger yet. Thanks to Tom, Jan, and Brook who helped me get this far. create table prd_doc ( ------------------------------------- -- Primary Keys: base, type, class, annot -- Foreign Keys: crt_by - references empl -- lockuid - references empl -- Columns: base varchar(18) not null, type varchar(6) not null CHECK (type in ('notes', 'fig', 'burn', 'mill', 'lathe', 'punch', 'arch')), class char(4) not null CHECK (class in ('assy', 'inst', 'rang', 'ship', 'oper')), annot varchar(24) not null, format varchar(18) not null, crt_date timestamp not null, crt_by int4 not null references empl, mod_date timestamp not null, mod_by int4 not null references empl, lockuid int4 null references empl, primary key (base, type, class, annot) ); create table prd_doc_data ( ------------------------------------- -- Primary Keys: base, type, class, annot, seq -- Foreign Keys: base, type, class, annot - references prd_doc -- Columns: base varchar(18) not null, type varchar(6) not null, class char(4) not null, annot varchar(24) not null, seq int4 not null CHECK (seq >= 0), data text, primary key (base, type, class, annot, seq), foreign key (base, type, class, annot) references prd_doc on update cascade on delete cascade ); -- This view has access to the whole doc table but we still use a view -- so the crt_by/mod_by fields get updated properly. -------------------------------------------------------------------------- create view prd_doc_v as select base, type, class, annot, format, crt_date, crt_by, mod_date, mod_by, oid as _oid from prd_doc; create rule prd_doc_r_v_insert as on insert to prd_doc_v do instead insert into prd_doc (base, type, class, annot, format, mod_by, crt_by, mod_date, crt_date) values (new.base, new.type, new.class, new.annot, new.format, getpguid(), getpguid(), current_timestamp, current_timestamp); create rule prd_doc_r_v_update as on update to prd_doc_v do instead update prd_doc set base = new.base, type = new.type, class = new.class, annot = new.annot, format = new.format, mod_date = current_timestamp, mod_by = getpguid() where base = old.base and type = old.type and class = old.class and annot = old.annot; create rule prd_doc_r_v_delete as on delete to prd_doc_v do instead delete from prd_doc where base = old.base and type = old.type and class = old.class and annot = old.annot; -- Store the specified document data, slicing it into pieces small enough -- to fit into a tuple. Update prd_doc_v$1 to show the modified date. -- Calling sequence: prd_doc_store(base,type,class,annot,format,data) create function prd_doc_store(text,text,text,text,text,text) returns varchar as ' set odata $6 set _oid {} spi_exec "select _oid from prd_doc_v where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'" if {$_oid == {}} { spi_exec "insert into prd_doc_v (base,type,class,annot,format) values (\'$1\',\'$2\',\'$3\',\'$4\',\'$5\')" } else { spi_exec "update prd_doc_v set format = \'$5\' where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'" } spi_exec "delete from prd_doc_data where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'" set i 0; while {$odata != {}} { set rec [string range $odata 0 8000] set odata [string range $odata [expr 8000 + 1] end] spi_exec "insert into prd_doc_data (base,type,class,annot,seq,data) values (\'$1\',\'$2\',\'$3\',\'$4\',$i,\'$rec\')" incr i } return 1 ' LANGUAGE 'pltcl'; -- Fetch the specified document data, reassembling the bits back together -- in the right order. -- Calling sequence: prd_doc_fetch(base,type,class,annot) create function prd_doc_fetch(text,text,text,text) returns text as ' set odata {} spi_exec -array d "select data from prd_doc_data where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\' order by seq" { append odata $d(data) } return $odata ' LANGUAGE 'pltcl'; --------------C890E416807C9E009E665373 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I'm posting this for the benefit of others who want to store large data but can't wait for 7.1.  This doesn't have all the benefits of TOAST, but can store large data without any effort in the front end.  This example is from our newly created document storage tables.  With the advent of unlimited query size in 7.0, we can send a large chunk of data to a TCL function.  The function slices it up into smaller pieces and stores it in a related data table.  An analogous function puts it back together when requested.  I successfully stored and retrieved /usr/dict/words without any problems.  I haven't tried anything bigger yet.

Thanks to Tom, Jan, and Brook who helped me get this far.

create table prd_doc (
-------------------------------------
-- Primary Keys:        base, type, class, annot
-- Foreign Keys:        crt_by - references empl
--                      lockuid - references empl

-- Columns:
   base varchar(18) not null,
   type varchar(6) not null CHECK (type in ('notes', 'fig', 'burn', 'mill', 'lathe', 'punch', 'arch')),
   class char(4) not null CHECK (class in ('assy', 'inst', 'rang', 'ship', 'oper')),
   annot varchar(24) not null,
   format varchar(18) not null,
   crt_date timestamp not null,
   crt_by int4 not null references empl,
   mod_date timestamp not null,
   mod_by int4 not null references empl,
   lockuid int4 null references empl,

   primary key (base, type, class, annot)
);

create table prd_doc_data (
-------------------------------------
-- Primary Keys:        base, type, class, annot, seq
-- Foreign Keys:        base, type, class, annot - references prd_doc

-- Columns:
   base varchar(18) not null,
   type varchar(6) not null,
   class char(4) not null,
   annot varchar(24) not null,
   seq int4 not null CHECK (seq >= 0),
   data text,

   primary key (base, type, class, annot, seq),
   foreign key (base, type, class, annot)
       references prd_doc
       on update cascade
       on delete cascade
);

-- This view has access to the whole doc table but we still use a view
-- so the crt_by/mod_by fields get updated properly.
--------------------------------------------------------------------------
create view prd_doc_v as select base, type, class, annot, format, crt_date, crt_by, mod_date, mod_by, oid as _oid from prd_doc;
create rule prd_doc_r_v_insert as on insert to prd_doc_v
    do instead
        insert into prd_doc (base, type, class, annot, format, mod_by, crt_by, mod_date, crt_date)
        values (new.base, new.type, new.class, new.annot, new.format, getpguid(), getpguid(), current_timestamp, current_timestamp);
create rule prd_doc_r_v_update as on update to prd_doc_v
    do instead
        update prd_doc set base = new.base, type = new.type, class = new.class, annot = new.annot, format = new.format, mod_date = current_timestamp, mod_by = getpguid()
        where base = old.base and type = old.type and class = old.class and annot = old.annot;
create rule prd_doc_r_v_delete as on delete to prd_doc_v
    do instead
        delete from prd_doc
        where base = old.base and type = old.type and class = old.class and annot = old.annot;

-- Store the specified document data, slicing it into pieces small enough
-- to fit into a tuple.  Update prd_doc_v$1 to show the modified date.
-- Calling sequence: prd_doc_store(base,type,class,annot,format,data)
create function prd_doc_store(text,text,text,text,text,text) returns varchar as '
    set odata $6
    set _oid {}
    spi_exec "select _oid from prd_doc_v where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'"
    if {$_oid == {}} {
        spi_exec "insert into prd_doc_v (base,type,class,annot,format) values (\'$1\',\'$2\',\'$3\',\'$4\',\'$5\')"
    } else {
        spi_exec "update prd_doc_v set format = \'$5\' where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'"
    }
    spi_exec "delete from prd_doc_data where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'"
    set i 0; while {$odata != {}} {
        set rec [string range $odata 0 8000]
        set odata [string range $odata [expr 8000 + 1] end]
        spi_exec "insert into prd_doc_data (base,type,class,annot,seq,data) values (\'$1\',\'$2\',\'$3\',\'$4\',$i,\'$rec\')"
        incr i
    }
    return 1
    ' LANGUAGE 'pltcl';

-- Fetch the specified document data, reassembling the bits back together
-- in the right order.
-- Calling sequence: prd_doc_fetch(base,type,class,annot)
create function prd_doc_fetch(text,text,text,text) returns text as '
    set odata {}
    spi_exec -array d "select data from prd_doc_data where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\' order by seq" {
        append odata $d(data)
    }
    return $odata
    ' LANGUAGE 'pltcl';
 
  --------------C890E416807C9E009E665373-- --------------8B8987CCDEF1B1DB74CA14C0 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Bateman Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard --------------8B8987CCDEF1B1DB74CA14C0--