Thread: create a temp table in SPI

create a temp table in SPI

From
黄宁
Date:
I want to create some temporary tables in SPI, but after I created the table and inserted the data, I can’t query any data, why?

the postgres version:
PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
code:


int ret = 0;
    SPI_connect();
    ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int, value text)", false, 0);
    if(ret != SPI_OK_UTILITY)
    {
        elog(ERROR, "%s: create failed", __FUNCTION__);
    }
    ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2, 'b')", false, 0);
    if(ret != SPI_OK_INSERT)
    {
        elog(ERROR, "%s: insert failed", __FUNCTION__);
    }
    // SPI_commit();
    ret = SPI_execute("SELECT * FROM temp_table", true, 0);
    if(ret != SPI_OK_SELECT)
    {
        elog(ERROR, "%s: select failed", __FUNCTION__);
    }

    if(SPI_processed > 0)
    {

    }
    else {
        // elog(ERROR, "%s: find nothing", __FUNCTION__);
    }

    // SPITupleTable *tuptable = SPI_tuptable;
    // TupleDesc tupdesc = tuptable->tupdesc;
    // // the numvals is 0,but I insert two records
    // if (tuptable->numvals > 0)
    // {
    //  HeapTuple tuple = tuptable->vals[0];
    //  char *tupleval1 = SPI_getvalue(tuple, tupdesc, 1);
    //  char *tupleval2 = SPI_getvalue(tuple, tupdesc, 2);
    //  pfree(tupleval1);
    //  pfree(tupleval2);
    // }
    // else
    // {
    //  elog(ERROR, "%s: can not query something", __FUNCTION__);
    // }
    // do something with tuptable
    SPI_finish();

Re: create a temp table in SPI

From
Laurenz Albe
Date:
On Thu, 2023-07-13 at 13:12 +0800, 黄宁 wrote:
> I want to create some temporary tables in SPI, but after I created the table and inserted the data, I can’t query any
data,why? 
>
> the postgres version:
> PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
> code:
>
>
>  int ret = 0;
>     SPI_connect();
>     ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int, value text)", false, 0);
>
>     ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2, 'b')", false, 0);
>
>     ret = SPI_execute("SELECT * FROM temp_table", true, 0);
>
>     if(SPI_processed > 0)
         ...

That's because you set "read_only" to "true" when you executed the query,
so that the command counter is not incremented, and the query cannot see
the results from the previous statement.

The documentation is quite clear here:

  It is generally unwise to mix read-only and read-write commands within
  a single function using SPI; that could result in very confusing behavior,
  since the read-only queries would not see the results of any database
  updates done by the read-write queries.

Yours,
Laurenz Albe



Re: create a temp table in SPI

From
黄宁
Date:
Thanks for your explanation. 

Laurenz Albe <laurenz.albe@cybertec.at> 于2023年7月13日周四 17:48写道:
On Thu, 2023-07-13 at 13:12 +0800, 黄宁 wrote:
> I want to create some temporary tables in SPI, but after I created the table and inserted the data, I can’t query any data, why?
>
> the postgres version:
> PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
> code:
>
>
>  int ret = 0;
>     SPI_connect();
>     ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int, value text)", false, 0);
>
>     ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2, 'b')", false, 0);
>
>     ret = SPI_execute("SELECT * FROM temp_table", true, 0);
>
>     if(SPI_processed > 0)
         ...

That's because you set "read_only" to "true" when you executed the query,
so that the command counter is not incremented, and the query cannot see
the results from the previous statement.

The documentation is quite clear here:

  It is generally unwise to mix read-only and read-write commands within
  a single function using SPI; that could result in very confusing behavior,
  since the read-only queries would not see the results of any database
  updates done by the read-write queries.

Yours,
Laurenz Albe