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