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 –  sarikan Dec 18 '12 at 14:26
add comment

2 Answers

up vote 1 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
add comment

I am not sure what you are using for a front end to the database. In MS Access when we use that as a frontend it is quite easy to create temp tables on the fly with VBA code. I use temp tables created on demand quite often. In .NET its a bit trickier but it can be done there too. Let me know if you want to see a code example of the process.

share|improve this answer
    
Welcome to dba.se Sorry for the downvote but the question is asking about PostgreSQL and changing to VoltDB. I don't see how creating temporary tables in Access is going to help them. –  billinkc Dec 19 '12 at 18:25
add comment

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.