Обсуждение: 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