Hi there,

I have written a pltcl function which works on one table only and it does on this table a select, then insert or update back to this table conditionally according to the select results. This function would be accessed concurrently, and because result of one call of this function would be affected by previous calls, I wonder how data integrity could be ensured.

I figured a transaction with table locking inside the function would do the job but when I tried to wrap the sql with the following it fails and gives me "SPI_ERROR_TRANSACTION" error:

spi_exec "begin work"


spi_exec "lock table table_name"


spi_exec "select....from table_name"


if {$some_condition} {




  spi_exec "insert into table_name ..."




} else {




  spi_exec "update table_name ..."




}




spi_exec "commit work"

I was wondering if pltcl functions has its own locking mechanism and would it be safe to use this function concurrently. If not, how should I handle this case?

Thanks for your help in advance.

Sincerely,

In pl/pgsql at least it is not necessary to begin a transaction inside a function call since each pl/pgsql/ function is an implied transaction.  I assume that this is also true for pltcl.  Try doing it without the begin/end transaction statements.

Hi all,

Thank you for all the inputs. So it seems it is safe to use this function concurrently. And for the implicit transaction that is implied by a function, what is the type of the table lock it would be? Is it restrictive enough?

I am so concerned about this because any call of this function would yield the same row in its select. I guess I would have to be more detailed with my implementation so that you guys could help. Basically, I am using a table to store the values for a pseudo-random number assignment counter. The table stores some pregenerated random values (1000 of them, 0 - 999) and it doesn't have to generate another set of random values until the counter hit 999. The random values are generated by a table column defaulted to another shuffling function.

The function in question here get the values and increment the $counter column, check to see if it exceed 999, if so, insert another new row to the table so that another set of random values can become handy, also the $counter column of the new row is set to 0.

Again, here is more details of what my code is doing:

#select the latest row

spi_exec "select counter,slot_id,randomize from table_name order by slot_id desc limit 1"

#check the counter to see if it is smaller than 999

if {$counter >= 999} {

# insert a new row cuz the counter already exceed 999


spi_exec "insert into table_name (counter) values (0)"


} else {


set counter_new [expr $counter + 1]


spi_exec "update table_name set counter = $counter_new where slot_id = $slot_id"


}


# no more db access beyond this point


# some more logic and return values

I am doing this all in the db because I would like the values survive from any nsd crash. Originally I was thinking to do this with nsv vars and sync the counters back to db with ns_atshutdown but then this only handles server shutdown but not server crash.

Thank you again.

Sincerely,