hi, how to let the inserted tuple visible to other backend when current backend hasn't finish?

On Sun, Sep 12, 2010 at 3:02 AM, sunpeng <bluevaley@gmail.com> wrote:

hi, These codes are in the postgresql engine, just assume they are in
PortalRun() function:
//1.create table structure
char *relname = "test";
...
relOid = heap_create_with_catalog(relname, ....);
CommandCounterIncrement();
...
//2.then i can use SPI_execute to create index on this created table
SPI_connect();
char *sqlCreate_index ="create index on test....."
int ret = SPI_execute(sqlCreate_index , false, 1);
SPI_finish();
.....
//3.until now it performs well,but after i insert a tuple in this table
....
Relation mcir_relation = relation_open(relOid); //the relation just created
HeapTupleData htup;
....
simple_heap_insert(relation, &htup);
CommandCounterIncrement();
...
//4.then again want to invoke SPI_execute("select...."), it seems the
inserted tuple is not visible to SPI_execute()
SPI_connect();
int ret =  SPI_execute("select * from test;", true, 1);
if (ret == SPI_OK_SELECT && SPI_processed == 1 ) {
....
}

the ret is SPI_OK_SELECT ,but  SPI_processed == 0, the inserted tuple is not
visible to SPI_execute() .
 i've used these methods to try to let it visible to SPI_execute() :
simple_heap_insert()....
CommandCounterIncrement();
or:
BeginInternalSubTransaction(NULL);
simple_heap_insert()...
ReleaseCurrentSubTransaction();
....
but they all don't work, how to resolve it?

This may or may not have anything do do with your problem, but why are
you bypassing the SPI interface to insert the tuple? Have you tried
inserting the record via regular SPI query?

merlin