| From: | Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general <pgsql-general(at)postgresql(dot)org> | 
| Subject: | Issue in Improving the performance using prepared plan | 
| Date: | 2010-04-22 09:46:53 | 
| Message-ID: | j2mc11950271004220246u5f694988n3aae14f991f77de3@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hi,
I have written following trigger and trying to improve the performance by
using prepared query everytime. I have used spi_prepare to prepare the query
and $_SHARED global hash to persist the prepared plan but it doesn't seem to
work. Though $query will be same always in following trigger, it prepares
query everytime and never uses prepared plan.
Could anyone tell me what's wrong going on?
CREATE OR REPLACE FUNCTION techdb_table_trigger()
  RETURNS trigger AS
$BODY$
our ($id, $query, $plan, $change_log_table);
$change_log_table = "ChangeLogTable";
$id = $_TD->{new}{'id'};
$query   = (<<ENDQUERY);
    INSERT INTO $change_log_table(id)
    SELECT \$1
    EXCEPT SELECT id
    FROM $change_log_table
    WHERE id = \$1
    AND txid = txid_current()
    AND txtime = transaction_timestamp();
ENDQUERY
if (exists($_SHARED{$query})) {
   $plan  = $_SHARED{$query};
  * elog(INFO, "########## Preparing the query ###########");  --> Always
comes here. Don't know why?*
} else {
   $plan  = spi_prepare($query, 'INTEGER');
   $_SHARED{$query} = $plan;
 *  elog(INFO, "###############Using already prepared the
query##############");  --> Never comes here.*
}
spi_exec_prepared($plan, $id);
$BODY$
  LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
Thanks,
Jignesh
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dave Page | 2010-04-22 09:50:13 | Re: [GENERAL] Byte order mark added by (the envelope please...) pgAdmin3 !! | 
| Previous Message | John Gage | 2010-04-22 09:41:22 | Re: [GENERAL] Byte order mark added by (the envelope please...) pgAdmin3 !! |