Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
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
1  
@a_horse_with_no_name: according to this, it does: voltdb.com/dig-deeper/technology.php –  mahonya Dec 18 '12 at 14:26

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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