Re: [PATCHES] Another Plpgsql trigger example - summary table
Mark Kirkwood wrote: 2) Perhaps leave the trigger + plpgsql function as a plpgsql example, and refer to it in the (new) data warehouse section/chapter. Looking at option 2, it seems reasonable to add a trimmed trigger example into the plpgsql examples section now, and leave the data warehouse introductory stuff for its own chapter at some later stage. --- plpgsql.sgml.orig Tue Jan 11 12:39:17 2005 +++ plpgsql.sgmlTue Jan 11 13:43:35 2005 @@ -2646,6 +2646,162 @@ FOR EACH ROW EXECUTE PROCEDURE process_emp_audit(); + + +An area where triggers can be useful is maintaining a summary table +of another table. The resulting summary can be used in place of the +original table for certain queries - with often vastly reduced run +times. + + + +This technique is commonly used in Data Warehousing, where the tables +of measured or observed data (called fact tables) can be extremely large. + shows an example of a +trigger procedure in PL/pgSQL that maintains +a summary table for a fact table in a data warehouse. + + + + +A PL/pgSQL Trigger Procedure For Maintaining A Summary Table + + + The schema detailed here is partly based on the Grocery Store + example from The Data Warehouse Toolkit + by Ralph Kimball. + + + +-- +-- Main tables - time dimension and sales fact. +-- +CREATE TABLE time_dimension ( +time_keyinteger NOT NULL, +day_of_week integer NOT NULL, +day_of_monthinteger NOT NULL, +month integer NOT NULL, +quarter integer NOT NULL, +yearinteger NOT NULL +); +CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key); + +CREATE TABLE sales_fact ( +time_keyinteger NOT NULL, +product_key integer NOT NULL, +store_key integer NOT NULL, +amount_sold numeric(12,2) NOT NULL, +units_sold integer NOT NULL, +amount_cost numeric(12,2) NOT NULL +); +CREATE INDEX sales_fact_time ON sales_fact(time_key); + +-- +-- Summary table - sales by time. +-- +CREATE TABLE sales_summary_bytime ( +time_keyinteger NOT NULL, +amount_sold numeric(15,2) NOT NULL, +units_sold numeric(12) NOT NULL, +amount_cost numeric(15,2) NOT NULL +); +CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key); + +-- +-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE. +-- +CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$ +DECLARE +delta_time_key integer; +delta_amount_sold numeric(15,2); +delta_units_soldnumeric(12); +delta_amount_cost numeric(15,2); +BEGIN + +-- Work out the increment/decrement amount(s). +IF (TG_OP = 'DELETE') THEN + +delta_time_key = OLD.time_key; +delta_amount_sold = -1 * OLD.amount_sold; +delta_units_sold = -1 * OLD.units_sold; +delta_amount_cost = -1 * OLD.amount_cost; + +ELSIF (TG_OP = 'UPDATE') THEN + +-- forbid updates that change the time_key - +-- (probably not too onerous, as DELETE + INSERT is how most +-- changes will be made). +IF ( OLD.time_key != NEW.time_key) THEN +RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key; +END IF; + +delta_time_key = OLD.time_key; +delta_amount_sold = NEW.amount_sold - OLD.amount_sold; +delta_units_sold = NEW.units_sold - OLD.units_sold; +delta_amount_cost = NEW.amount_cost - OLD.amount_cost; + +ELSIF (TG_OP = 'INSERT') THEN + +delta_time_key = NEW.time_key; +delta_amount_sold = NEW.amount_sold; +delta_units_sold = NEW.units_sold; +delta_amount_cost = NEW.amount_cost; + +END IF; + + +-- Update the summary row with the new values. +UPDATE sales_summary_bytime +SET amount_sold = amount_sold + delta_amount_sold, +units_sold = units_sold + delta_units_sold, +amount_cost = amount_cost + delta_amount_cost +WHERE time_key = delta_time_key; + + +-- There might have been no row with this time_key (e.g new data!). +IF (NOT FOUND) THEN +BEGIN +INSERT INTO sales_summary_bytime ( +time_key, +amount_sold, +units_sold, +amount_cost) +SELECT f.time_key, +sum(f.amount_sold), +sum(f.unit
[PATCHES] "SMgrRelation hashtable corrupted" fix, phase 1
Attached is a patch that fixes the recently isolated "SMgrRelation
hashtable corrupted" bug by allowing smgrclose() to happen safely
without being called directly by the relcache. The reason this is
an issue is that the relcache might contain a pointer to the
SMgrRelation entry, and if that pointer isn't cleared then we have a
dangling pointer, which on use will cause the "hashtable corrupted"
failure if not worse things. The fix is for smgr.c to be aware of
the possible existence of a long-lived pointer to each SMgrRelation
entry, and to zero out any such pointer when smgrclose() is called.
I believe this is considerably more robust than the previous approach.
It's a bit uglier than before, but it's not quite tantamount to smgr
depending on relcache --- smgr only knows there is a pointer, not what
sort of structure it's stored in.
Note that this problem doesn't exist pre-8.0 since the separate smgr
cache table didn't exist in prior releases. Basically what's at stake
here is keeping the relcache and smgr cache in step.
The bulk of the patch consists of replacing the common patterns for
smgropen() and smgrclose() calls associated with relcache entries
with two new macros RelationOpenSmgr and RelationCloseSmgr, which is
doubtless the way it should have been coded in the first place.
Ignoring that, the changes reduce to the addition of smgrsetowner(),
changing smgrclose() to clear the reference pointer if set,
simplifying RelationCacheInvalidateEntry back to the way it was in 7.4,
and simplifying LocalExecuteInvalidationMessage to treat relcache and
smgr cache inval as independent operations.
With the last change, there is no longer a reason for shared inval
messages to handle relcache and smgr invals as a single operation;
we can send those as two separate messages instead. I want to do
this because it'll allow sizeof(SharedInvalidationMessage) to go back
to 16 bytes as it was in prior releases instead of 24 bytes as it is
now, saving a fair amount of shared memory space. I'm going to post
that as a separate patch though, since it's logically separate.
regards, tom lane
*** src/backend/access/transam/xlogutils.c.orig Fri Dec 31 17:45:32 2004
--- src/backend/access/transam/xlogutils.c Mon Jan 10 13:41:19 2005
***
*** 125,132
if (hentry == NULL)
elog(PANIC, "_xl_remove_hash_entry: file was not found in
cache");
! if (rdesc->reldata.rd_smgr != NULL)
! smgrclose(rdesc->reldata.rd_smgr);
memset(rdesc, 0, sizeof(XLogRelDesc));
memset(tpgc, 0, sizeof(FormData_pg_class));
--- 125,131
if (hentry == NULL)
elog(PANIC, "_xl_remove_hash_entry: file was not found in
cache");
! RelationCloseSmgr(&(rdesc->reldata));
memset(rdesc, 0, sizeof(XLogRelDesc));
memset(tpgc, 0, sizeof(FormData_pg_class));
***
*** 233,239
hentry->rdesc = res;
res->reldata.rd_targblock = InvalidBlockNumber;
! res->reldata.rd_smgr = smgropen(res->reldata.rd_node);
/*
* Create the target file if it doesn't already exist. This
lets
--- 232,239
hentry->rdesc = res;
res->reldata.rd_targblock = InvalidBlockNumber;
! res->reldata.rd_smgr = NULL;
! RelationOpenSmgr(&(res->reldata));
/*
* Create the target file if it doesn't already exist. This
lets
***
*** 278,284
rdesc = hentry->rdesc;
! if (rdesc->reldata.rd_smgr != NULL)
! smgrclose(rdesc->reldata.rd_smgr);
! rdesc->reldata.rd_smgr = NULL;
}
--- 278,282
rdesc = hentry->rdesc;
! RelationCloseSmgr(&(rdesc->reldata));
}
*** src/backend/catalog/heap.c.orig Fri Dec 31 17:45:32 2004
--- src/backend/catalog/heap.c Mon Jan 10 12:38:30 2005
***
*** 322,328
if (create_storage)
{
Assert(rel->rd_smgr == NULL);
! rel->rd_smgr = smgropen(rel->rd_node);
smgrcreate(rel->rd_smgr, rel->rd_istemp, false);
}
--- 322,328
if (create_storage)
{
Assert(rel->rd_smgr == NULL);
! RelationOpenSmgr(rel);
smgrcreate(rel->rd_smgr, rel->rd_istemp, false);
}
***
*** 1186,1195
if (rel->rd_rel->relkind != RELKIND_VIEW &&
rel->rd_rel->relkind != RELKIND_COMPOSITE_TYPE)
{
! if (rel->rd_smgr == NULL)
! rel->rd_smgr = smgropen(rel->rd_node);
smgrscheduleunlink(rel->rd_smgr, rel->rd_istemp);
- rel->rd_smgr = NULL;
}
/*
--- 1186,1193
if (rel->rd_rel->relkind != RELKIND_VIEW &&
rel->rd_rel->relkind != RELKIND_COMPOSITE_TYPE)
{
!
Re: [PATCHES] re-enable MSVC builds
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Magnus Hagander wrote: >> I'm sure it still eneds the shell32 linking. > I don't see where to put it in the bcc.mak file anyway. Yeah, I looked in bcc32.mak and didn't see any reference to the other *32.dll files that win32.mak references. So either Borland does not need any of them, or the Borland build has been broken for awhile. If someone steps up to fix it, great, otherwise it's on the path to extinction... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] re-enable MSVC builds
Magnus Hagander wrote: The attached patch allows MSCV builds to complete. Applied. (I wonder whether the Borland build still works...) I'm sure it still eneds the shell32 linking. The suorce part should be the same - AFAIK they ship the MS headers. But I don't have a way to test it, so... Me either, and I don't have time to acquire/test before release. I don't see where to put it in the bcc.mak file anyway. So, either it's not broken, or it is broken and will stay that way, or it is broken and somebody else will step in *real* quick to fix it :-) I am not betting on this last. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Turkish translation update
Devrim GUNDUZ wrote: > http://postgresql.gunduz.org/translation/PostgreSQL-8.0/PgSQL-DilDosy >alari/postgres-tr.po > Could you please apply it to -HEAD? Done. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] re-enable MSVC builds
> > The attached patch allows MSCV builds to complete. > > Applied. > > (I wonder whether the Borland build still works...) I'm sure it still eneds the shell32 linking. The suorce part should be the same - AFAIK they ship the MS headers. But I don't have a way to test it, so... > > The warnings still shown (see recent -hackers email) are > identical to > > those that were present in RC2, so they are not the > products of recent > > homedir changes. > > With the possible exception of the mkdir warning, these seem > cosmetic and not worth worrying about at this stage of the > release cycle. Definitly. There have been cosmetic warnings in the win32 build ever since it was created. //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
