Announcement

Collapse
No announcement yet.

Creating temporary tables from stored procedures. Is it possible?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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

    Comment


    • #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.

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment


          • #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?

            Comment


            • #7
              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.

              Comment

              Working...
              X