Thread: Odd PL/PgSQL Error -- relation "X" does not exist when using index expression

Hi,

I've tried this on 8.2.1, .2 and .3:

I'm having a strange problem with a PL/PGSQL query that executes some 
dynamic SQL code.  The code basically creates a dynamically named table, 
some indexes, etc.

The problem seems to be the an index expression. If I remove it and do a 
plain index on the column, all works correctly.  If I keep it, I get a 
"relation does not exist" error.

If I were to take the generated code and run it manually, it works fine. It 
only fails when run inside the stored procedure.

---------------------------------------
-- This one works
CREATE OR REPLACE FUNCTION init_testdata_a(sourceid_ integer) RETURNS void AS
$DELIM$
DECLARE   sqlquery_ varchar;
BEGIN   sqlquery_ := '
DROP TABLE IF EXISTS testdata_' || sourceid_ || ' CASCADE;
CREATE TABLE testdata_' || sourceid_ || ' (   id SERIAL PRIMARY KEY,   data text NOT NULL
);
CREATE INDEX testdata_' || sourceid_ || '_content_idx on testdata_' || 
sourceid_ || ' (data);
';   --RAISE NOTICE '%', sqlquery_;   EXECUTE sqlquery_;
END;
$DELIM$ LANGUAGE PLPGSQL;

-- Adding the lower() causes it to not work
CREATE OR REPLACE FUNCTION init_testdata_b(sourceid_ integer) RETURNS void AS
$DELIM$
DECLARE   sqlquery_ varchar;
BEGIN   sqlquery_ := '
DROP TABLE IF EXISTS testdata_' || sourceid_ || ' CASCADE;
CREATE TABLE testdata_' || sourceid_ || ' (   id SERIAL PRIMARY KEY,   data text NOT NULL
);
CREATE INDEX testdata_' || sourceid_ || '_content_idx on testdata_' || 
sourceid_ || ' ( lower(data) );
';   --RAISE NOTICE '%', sqlquery_;   EXECUTE sqlquery_;
END;
$DELIM$ LANGUAGE PLPGSQL;
---------------------------------------


For example, running:

=> select init_testdata_a(1);
....works....

=> select init_testdata_b(2);
....
"
PL/pgSQL function "init_testdata_b" line 13 at execute statement
ERROR:  relation "testdata_2" does not exist
CONTEXT:  SQL statement "
...


Any thoughts?

-Greg

On Wed, Feb 07, 2007 at 02:43:13PM -0800, Greg Wittel wrote:
> I'm having a strange problem with a PL/PGSQL query that executes some 
> dynamic SQL code.  The code basically creates a dynamically named table, 
> some indexes, etc.
> 
> The problem seems to be the an index expression. If I remove it and do a 
> plain index on the column, all works correctly.  If I keep it, I get a 
> "relation does not exist" error.

The error appears to happen for anything that uses SPI.  A C function
that executes the following fails with the same error:

SPI_exec("CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo (lower(t))", 0);

In 8.2.3 the error location is:

LOCATION:  RangeVarGetRelid, namespace.c:200

-- 
Michael Fuhr

Michael Fuhr wrote:
> On Wed, Feb 07, 2007 at 02:43:13PM -0800, Greg Wittel wrote:
> > I'm having a strange problem with a PL/PGSQL query that executes some 
> > dynamic SQL code.  The code basically creates a dynamically named table, 
> > some indexes, etc.
> > 
> > The problem seems to be the an index expression. If I remove it and do a 
> > plain index on the column, all works correctly.  If I keep it, I get a 
> > "relation does not exist" error.
> 
> The error appears to happen for anything that uses SPI.  A C function
> that executes the following fails with the same error:
> 
> SPI_exec("CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo (lower(t))", 0);

Hmm, are we short of a CommandCounterIncrement in the middle of both
commands?  Does the same error show up if you do

SPI_exec("CREATE TABLE foo (t text);", 0);
SPI_exec("CREATE INDEX foo_idx ON foo (lower(t))", 0);
?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Feb 08, 2007 at 11:14:33AM -0300, Alvaro Herrera wrote:
> Michael Fuhr wrote:
> > The error appears to happen for anything that uses SPI.  A C function
> > that executes the following fails with the same error:
> > 
> > SPI_exec("CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo (lower(t))", 0);
> 
> Hmm, are we short of a CommandCounterIncrement in the middle of both
> commands?  Does the same error show up if you do
> 
> SPI_exec("CREATE TABLE foo (t text);", 0);
> SPI_exec("CREATE INDEX foo_idx ON foo (lower(t))", 0);

Using separate calls to SPI_exec() works.  Using a single call to
SPI_exec() works if the index is on (t) instead of (lower(t)):

SPI_exec("CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo (t)", 0);

-- 
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> Using separate calls to SPI_exec() works.  Using a single call to
> SPI_exec() works if the index is on (t) instead of (lower(t)):

> SPI_exec("CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo (t)", 0);

It's only by chance that it works in that case: the current coding of
transformIndexStmt happens not to try to touch the underlying table
if there aren't any expressions to analyze.  You can make a large number
of variants that will fail, eg, create the table and try to insert into
it in one command string.

My advice is not to try to execute multiple commands in the same EXECUTE
string --- if we were going to do anything to "fix" this, I think it
would be along the lines of enforcing that advice.  Trying to make the
world safe for it doesn't sound productive.
        regards, tom lane

On Thu, Feb 08, 2007 at 10:32:25AM -0500, Tom Lane wrote:
> My advice is not to try to execute multiple commands in the same EXECUTE
> string --- if we were going to do anything to "fix" this, I think it
> would be along the lines of enforcing that advice.  Trying to make the
> world safe for it doesn't sound productive.

The SPI_execute() documentation does mention that multiple commands
are allowed:

http://www.postgresql.org/docs/8.2/interactive/spi-spi-execute.html

"You may pass multiple commands in one string. SPI_execute returns
the result for the command executed last. The count limit applies
to each command separately, but it is not applied to hidden commands
generated by rules.

"When read_only is false, SPI_execute increments the command counter
and computes a new snapshot before executing each command in the
string."

Should that documentation be modified?

-- 
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Feb 08, 2007 at 10:32:25AM -0500, Tom Lane wrote:
>> My advice is not to try to execute multiple commands in the same EXECUTE
>> string --- if we were going to do anything to "fix" this, I think it
>> would be along the lines of enforcing that advice.  Trying to make the
>> world safe for it doesn't sound productive.

> The SPI_execute() documentation does mention that multiple commands
> are allowed:

Well, the point here is that there's one pass of parsing and one of
execution, and you won't get far if the parsing pass requires an earlier
command to have already been executed.  So maybe the appropriate warning
is something about not using interdependent DDL commands.  Feel free to
draft up a docs patch.
        regards, tom lane

Michael Fuhr wrote:
> On Thu, Feb 08, 2007 at 10:32:25AM -0500, Tom Lane wrote:
> > My advice is not to try to execute multiple commands in the same EXECUTE
> > string --- if we were going to do anything to "fix" this, I think it
> > would be along the lines of enforcing that advice.  Trying to make the
> > world safe for it doesn't sound productive.
>
> The SPI_execute() documentation does mention that multiple commands
> are allowed:
>
> http://www.postgresql.org/docs/8.2/interactive/spi-spi-execute.html
>
> "You may pass multiple commands in one string. SPI_execute returns
> the result for the command executed last. The count limit applies
> to each command separately, but it is not applied to hidden commands
> generated by rules.
>
> "When read_only is false, SPI_execute increments the command counter
> and computes a new snapshot before executing each command in the
> string."
>
> Should that documentation be modified?

Done, and attached.

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/spi.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/spi.sgml,v
retrieving revision 1.52
diff -c -c -r1.52 spi.sgml
*** doc/src/sgml/spi.sgml    1 Feb 2007 19:10:24 -0000    1.52
--- doc/src/sgml/spi.sgml    18 Feb 2007 01:45:45 -0000
***************
*** 321,327 ****
    </para>

    <para>
!    You can pass multiple commands in one string.
     <function>SPI_execute</function> returns the
     result for the command executed last.  The <parameter>count</parameter>
     limit applies to each command separately, but it is not applied to
--- 321,328 ----
    </para>

    <para>
!    You can pass multiple commands in one string, but later commands cannot
!    depend on the creation of objects earlier in the string.
     <function>SPI_execute</function> returns the
     result for the command executed last.  The <parameter>count</parameter>
     limit applies to each command separately, but it is not applied to