Results 1 to 7 of 7

Thread: Creating temporary tables from stored procedures. Is it possible?

  1. #1

    Creating temporary tables from stored procedures. Is it possible?

    Greetings,
    At the moment, I have functions in PostgreSql that create temporary tables, fill them and then drop them at the end of the function (which for Postgresql is the end of transaction)
    The key benefit Postgresql offers me is that these temp tables are local to function execution scope, that is, two simultaneous calls to same postgres function would create the temp table with the same name, but they would not see/touch each others data.

    I was wondering if I can do the same thing with VoldDB. Create and drop temp tables visible only to the execution context of a stored procedure.

    Regards
    Seref

    PS:
    Apologies for being lazy and asking this question instead of setting up voltdb and trying it out. Your response will save me at least a few hours, which would mean a lot for me at the moment.

  2. #2
    Quote Originally Posted by sarikan View Post
    Greetings,
    At the moment, I have functions in PostgreSql that create temporary tables, fill them and then drop them at the end of the function (which for Postgresql is the end of transaction)
    The key benefit Postgresql offers me is that these temp tables are local to function execution scope, that is, two simultaneous calls to same postgres function would create the temp table with the same name, but they would not see/touch each others data.

    I was wondering if I can do the same thing with VoldDB. Create and drop temp tables visible only to the execution context of a stored procedure.

    Regards
    Seref

    PS:
    Apologies for being lazy and asking this question instead of setting up voltdb and trying it out. Your response will save me at least a few hours, which would mean a lot for me at the moment.
    Hi,

    We do not currently support the execution of a create table within a stored procedure. Here's a link to the complete list of support sql statements: http://voltdb.com/docs/UsingVoltDB/AppxSQL.php

  3. #3
    Thanks,
    Can you think of any other way of achieving what I've described? I can always resort to a normal table with an extra column but due to nature of my data and queries, having separate tables would work much faster.

  4. #4
    Quote Originally Posted by sarikan View Post
    Thanks,
    Can you think of any other way of achieving what I've described? I can always resort to a normal table with an extra column but due to nature of my data and queries, having separate tables would work much faster.
    You can add new tables using a catalog update, which doesn't really seem like the way you'd want to go.

    Your thought of adding a perpetual work table though will work just fine. Just be sure that it is partitioned the same as your destination table so that everything will run as a signle partition query, thus giving you the best possible performance.

  5. #5
    Super Moderator
    Join Date
    Feb 2010
    Posts
    133
    You are not limited to storing temporary data for procedure execution in the database. You can store data in Java data structures on the stack, the only limitation there is that it must fit in the Java heap space available. It is not safe to store data in Java across multiple procedure invocations because it will break replication.

    As Andy mentioned, creating a partitioned table to use as scratch space is also fine, make sure to clear it on entry into the procedure and not exit. As Andy mentioned it will have to be a partitioned table and it has to be partitioned on the same key you used to route the procedure otherwise inserting rows will fail.

  6. #6
    It is pretty clear now, thanks. What is the problem with deleting rows on exit? Deleting during entry would actually ensure that each execution gets a clean table, but I kinda acted paranoid and considered delete on exit.
    Is your warning based on performance only or is there another issue that I may be missing?

  7. #7
    Super Moderator
    Join Date
    Feb 2010
    Posts
    133
    It's a belt and suspenders correctness issue.

    I was thinking that you want to make sure you don't have an exit path from the procedure that misses the delete on exit because the procedure might return at multiple points. It's not a good idea to use try/finally because the finally block might execute on exception and attempt to do SQL. I think we might even allow you to do the SQL, but you are in undefined behavior territory because partial rollback is not currently supported. The database should detect the condition and prevent you from running any more SQL. I created https://issues.voltdb.com/browse/ENG-4100 to track the issue.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •