From: Rosser Schwarz <rosser.schwarz@gmail.com>
To: pgsql-sql@postgresql.org
Subject: function fails where individual statements succeed
Date: Mon, 30 Aug 2004 12:17:29 -0500
Message-ID: <37d451f7040830101763682930@mail.gmail.com> (raw)
We have a SQL function that encapsulates a daily bulk import/update
process. The statements making up the function succeed when run
individually. When we group the statements together and wrap them in
a CREATE FUNCTION, running the function fails with:
ERROR: index expressions and predicates may refer only to the table
being indexed
CONTEXT: SQL function "masterfilepcb_load" statement 14
As well, when run in the function, the table masteryesterday is not
populated, while when the statements are run individually, it is.
The function is kind of a hack, but it should work; we have a similar
function running a similar process that we've used daily without
problem for months. It works like so: daily, we receive a file that
contains exhaustive data on our customers' accounts, which we use to
update our records of the accounts. These functions move the current
version of the imported data to a "yesterday" table and then bring in
the current day's data. I did some digging in indexcmds.c, after
grepping for the error message, but didn't see anything that might
explain the problem. The error suggests pg thinks we're looking at
another table.
The only difference between the functions, other than referring to
different schemas/tables (logically the same, details different) is
that this one returns void, while the one in production returns int,
and has "select 1" as its last statement. Given that the failure
occurs before that point, I'm somewhat doubtful that's the issue.
The function is:
CREATE OR REPLACE FUNCTION certegy.masterfilepcb_load()
RETURNS void AS
'
drop index certegy.ix_certegy_masterpcb_acctno;
drop index certegy.ix_certegy_masterpcb_acctno_prevacct_null;
drop index certegy.ix_certegy_masterpcb_prevacct_notnull;
drop index certegy.ix_certegy_masteryesterdaypcb_acctno;
drop index certegy.ix_certegy_masteryesterdaypcb_acctno_prevacct_null;
drop index certegy.ix_certegy_masteryesterdaypcb_prevacct_notnull;
alter table certegy.masteryesterdaypcb rename to masterpcb_x;
alter table certegy.masterpcb rename to masteryesterdaypcb;
alter table certegy.masterpcb_x rename to masterpcb;
truncate certegy.masterpcb;
copy certegy.masterpcb from \'/tmp/masterpcb.txt\' with delimiter \'|\' null
\'\';
create unique index ix_certegy_masterpcb_acctno on certegy.masterpcb using
btree (acctno);
create unique index ix_certegy_masteryesterdaypcb_acctno on
certegy.masteryesterdaypcb using btree (acctno);
create unique index ix_certegy_masterpcb_acctno_prevacct_null on
certegy.masterpcb using btree (acctno, prevacct) where prevacct is null;
create unique index ix_certegy_masteryesterdaypcb_acctno_prevacct_null on
certegy.masteryesterdaypcb using btree (acctno, prevacct) where prevacct is
null;
create unique index ix_certegy_masterpcb_prevacct_notnull on
certegy.masterpcb using btree (prevacct) where prevacct is not null;
create unique index ix_certegy_masteryesterdaypcb_prevacct_notnull on
certegy.masteryesterdaypcb using btree (prevacct) where prevacct is not
null;
'
LANGUAGE 'sql' STABLE;
We're using 7.4.2, built from source, on RH9.
Any help is appreciated.
/rls
--
:wq
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rosser.schwarz@gmail.com
Subject: Re: function fails where individual statements succeed
In-Reply-To: <37d451f7040830101763682930@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox