CREATE TEMP TABLE transaction auto commit issue

Moderator: NorbertKrupa

User avatar
jinimg
Newbie
Newbie
Posts: 5
Joined: Tue Jul 21, 2015 11:35 am

CREATE TEMP TABLE transaction auto commit issue

Post by jinimg » Tue Jul 21, 2015 12:08 pm

Hi Friends,

I am using Vertica .Net data provider 4.1.7 in my .NET appliation. I am facing a strange issue on TEMP table creation.

I have started a new TRANSACTION and exectuing couple of insert and update queries. After that I am creating a TEMP table using the same transaction. But after the CREATE TEMP table ExecuteNonQuery method the transaction automatically committed. I want to commit the transaction after executing couple of statements. My code is as below:

Code: Select all

using (var conn = VerticaHelper.GetConnection())
            {
               conn.Open();
               VerticaTransaction trans = conn.BeginTransaction();
              ExecuteNonQuery("insert into testschema.mytable(id,name) values(1,'fdfd')",conn,trans);

             string createTempTable = "CREATE LOCAL TEMP TABLE temp_1022(id int,name varchar(100)) ON COMMIT PRESERVE ROWS;";
             ExecuteNonQuery(createTempTable,conn,trans); //After executing this command the previous statement also commited why?????
              trans.Commit();
           }


function ExecuteNonQuery(query,conn,trans){
           using (VerticaCommand cmd = new VerticaCommand(query, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = trans;                    
                    int numChanged = cmd.ExecuteNonQuery();                   

                }
   }


Please help me

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: CREATE TEMP TABLE transaction auto commit issue

Post by JimKnicely » Tue Jul 21, 2015 1:32 pm

Hi,

SQL commands like CREATE, ALTER and DROP issue an implicit commit to the database. These commands are changing the structure of the database and are often referred to as DDL (Data Definition Language) commands as opposed to DML (Data Definition Language) commands like INSERT, UPDATE and DELETE.

Example in vsql:

Code: Select all

dbadmin=> begin transaction;
BEGIN
dbadmin=> insert into testschema.mytable(id,name) values(1,'fdfd');
 OUTPUT
--------
      1
(1 row)

dbadmin=> CREATE LOCAL TEMP TABLE temp_1022(id int,name varchar(100)) ON COMMIT PRESERVE ROWS;
CREATE TABLE
dbadmin=> rollback;
INFO 2499:  Cannot rollback; no transaction in progress
ROLLBACK
dbadmin=> select * from testschema.mytable;
 id | name
----+------
  1 | fdfd
(1 row)
I tried to rollback the INSERT statement, but there was nothing to "rollback" as the transaction (i.e. INSERT statement) was already committed when the CREATE statement was executed...
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
jinimg
Newbie
Newbie
Posts: 5
Joined: Tue Jul 21, 2015 11:35 am

Re: CREATE TEMP TABLE transaction auto commit issue

Post by jinimg » Tue Jul 21, 2015 3:46 pm

Hi Jim,
Thanks for the quick response.

Is there any option to turn OFF the auto commit feature before issuing CREATE statement?

Actually we are using this temp table concept for inserting bulk data to a physical table after some validations in the temp table itself. If you can suggest any other method to insert huge data without using the temp table. So that we can execute all SQL statements in a single transaction.

Thanks,
MG

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: CREATE TEMP TABLE transaction auto commit issue

Post by JimKnicely » Tue Jul 21, 2015 4:24 pm

Hi,

I do not think you can turn off the implicit commits made by DDL.

Maybe you can use permanent as opposed to temporary tables for data validation/transformation? That's what I've done in the past. We called them build and/or staging tables.

Basically, we truncate the build table, load some data, check the data/transform it if needed, then load it into the main table. Keep in mind that the TRUNCATE command is a DDL command that will issue an implicit commit, so you'd have to run it very early on in the process. But if you are going to that can you create the temporary tables earlier on in the process (prior to the inserts)?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
jinimg
Newbie
Newbie
Posts: 5
Joined: Tue Jul 21, 2015 11:35 am

Re: CREATE TEMP TABLE transaction auto commit issue

Post by jinimg » Thu Jul 23, 2015 9:06 am

Thanks for the update Jim. But creating physical table instead of temp table is very costly in our application. We have almost 50 or more temp table in entire application.

What we are thinking is to change the order of execution and issue all temp table creation in the beginning of transaction. Appreciate your valuable inputs. Thanks again.

MG

User avatar
jinimg
Newbie
Newbie
Posts: 5
Joined: Tue Jul 21, 2015 11:35 am

Re: CREATE TEMP TABLE transaction auto commit issue

Post by jinimg » Thu Jul 23, 2015 12:39 pm

Hi Jim,

I moved the create temp table statement just after the begin transaction. But when I am trying to insert data using "COPY temp_table_name FROM STDIN DIRECT DELIMITER '|' RECORD TERMINATOR '\r\n';" I am facing the same issue of auto commit. The transaction committed automatically while issuing the COPY command also??? Is this an expected behaviour ??

MG

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: CREATE TEMP TABLE transaction auto commit issue

Post by scutter » Thu Jul 23, 2015 1:07 pm

You can use COPY .. NO COMMIT to defer the commit.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “ADO.NET”