6

I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid introducing another column to my temp table to mark all rows inserted by a particular execution of the function. If I used a global table that would be required, and due to nature of my queries, it'd kill performance.

I want to port this piece of code to VoltDB, but it does not support creating temp tables from stored procedures. This made me think about other approaches. If a stored procedure would start a transaction and delete all rows in a table (not temp), then what would happen to other executions of the same stored procedure running at that moment?

Or if after the delete, the stored procedure begins inserting rows, and another session invokes the same sp, which will want to delete all rows again, what then?

In short, can I create a local (to sp execution) view of data within a transaction by deleting all rows, inserting some rows, then performing queries on the inserted rows, then deleting all rows again before the sp execution ends (and transaction commits)?

This would let me avoid using a rowset_id column for the temporary rows, avoiding much slower joins (this is an EAV implementation).

  • 1
    Does VoltDB support isolation levels? This should work using SERIALIZABLE (if it's implemented correctly) – a_horse_with_no_name Dec 18 '12 at 14:00
  • 2
    @a_horse_with_no_name: according to this, it does: voltdb.com/dig-deeper/technology.php – mahonya Dec 18 '12 at 14:26
  • Correction: Postgres temp tables are not “local to functions”. They persist until the end of that session. The temp tables are available to any code running during that session, in and out of function calls, in and out of transactions (if so defined). – Basil Bourque Aug 16 '17 at 18:04
3

The comment by a_horse_with_no_name is actually the answer, but since it was not posted as an answer, I'm not able to mark it so. I've completely forgotten about transaction isolation levels, I've asked the question in VoltDB forums and it appears that as long as make sure that stored procedures are local to nodes (sorry, this is a bit of a VoltDB terminology), that is, they don't process data across partitioned tables, this should work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.