Thread: function fails where individual statements succeed

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)
whereprevacct 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
isnot 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

while you weren't looking, Josh Berkus wrote:

> Statement 14 should be that one.  Can you test it, and the statements
> immediately preccedeing and succeeding it on the command line again?

Just running the CREATE INDEXes (after issuing the appropriate DROPs,
of course) succeeds.

> Also, can you try the whole run without the COPY statement?   I'm curious to
> see if something in the COPY file is messing things up.

Running without the COPY yields a substantively similar error message.

Again, this error only happens when the statements are executed in the
context of a function; outside, even issued all at once separated by
semicolons, the behavior is correct.

I was just reminded that there is another difference between this
function and the working one: this one is rather more heavily indexed.
(None of the compound, conditional indices exist in the earlier,
working version.) That might point in the direction of the error,
though it doesn't explain why it only fails in a function; I'll do
some digging.

/rls

-- 
:wq

Rosser Schwarz <rosser.schwarz@gmail.com> writes:
> 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

I believe what is happening is that the entire SQL function is parsed,
analyzed, and planned before we start to execute it.  By the time
control arrives at the CREATE INDEX command, the table named
certegy.masterpcb is not the same one that that name referred to when
the function's statements were parsed.  I haven't bothered to work out
exactly how that leads to this particular error message rather than some
other failure, but I can say that this isn't gonna work out well in general.

If you want to encapsulate this set of operations in a function, I'd
suggest using plpgsql and being careful to EXECUTE each query rather
than letting plpgsql try to cache a plan for it.
        regards, tom lane

while you weren't looking, Tom Lane wrote:

[...]

> If you want to encapsulate this set of operations in a function, I'd
> suggest using plpgsql and being careful to EXECUTE each query rather
> than letting plpgsql try to cache a plan for it.

We've opted to simply script issuing the statements serially, rather
than as a function for this one.  Your explanation makes sense, Tom,
though I'm curious why it works in our long-running case (beyond the
issue of fewer indices there).  Given that, I'm going to move that we
adopt the serial issuance approach for that process, to save off the
error eventually cropping up there, too.

Dankeschoen,

/rls

-- 
:wq

Rosser Schwarz <rosser.schwarz@gmail.com> writes:
> We've opted to simply script issuing the statements serially, rather
> than as a function for this one.  Your explanation makes sense, Tom,
> though I'm curious why it works in our long-running case (beyond the
> issue of fewer indices there).

Does the other case have any partial indexes?  The particular error
was about an index predicate condition; it might be that you don't
happen to fall foul of the issue when dealing with simple CREATE
INDEX commands.  I'd describe that as "miraculously managing not to
fail", though, not as a behavior I'd care to rely on.
        regards, tom lane

while you weren't looking, Tom Lane wrote:

> Does the other case have any partial indexes?

No it doesn't, and I caught it on reflection.

> I'd describe that as "miraculously managing not to
> fail", though, not as a behavior I'd care to rely on.

Most definitely not; it's already changed.

/rls

-- 
:wq