Why does query count return zero rows when table is being loaded?

Gareth_Gale

I'm loading a target cluster table from a corresponding table in a source cluster using the vsql technique described at http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ConnectingToHPVertica/vsql/CopyingDataUsingVsql.htm?Highlight=vsql ("Copying Data Using Vsql"). Essentially this is what is running:-

 

vsql -U username -w passwd -h testdb01 -d vmart -At -c "SELECT * from store.store_sales_fact"  \
| vsql -U username -w passwd -d vmart -c "COPY store.store_sales_fact FROM STDIN DELIMITER '|';"

 

As my version of this is running, I see the disk on the target cluster filling (so data is being copied), but when I issue a simple count query on the target cluster to see how many rows are present :-

 

SELECT COUNT(*) FROM target_table_name;

 

I always get zero rows returned. There's a large amount of data to copy, so it's a long running operation. However, running this query at various intervals ALWAYS yields zero rows - why? When will the data get flushed so this query reports accurately?

 

Thanks in advance!

Rahul_Choudhary

Hi Gareth

 

Just to specifically answer your question why the data is still not been seen when the copying statement is still loading data from source to destination table is because of the fact that in Vertica there is session-scoped isolation levels which determine transaction characteristics for transactions within a specific user session.

 

There are two types of isolation level (Read Committed and Serializable).

 

Now a transaction retains its isolation level until it completes, even if the session's transaction isolation level changes mid-transaction. HP Vertica internal processes (such as the Tuple Mover and refresh operations) and DDL operations are always run at SERIALIZABLE isolation level to ensure consistency and by default HP Vertica uses the READ COMMITTED isolation level which means if isolation level is set at the default (READ COMMITTED) you can query a table while a COPY is running but you would not see the data currently being inserted until it is committed.

 

 

To see the isolation level for the current session, run the command below:

vsql => SHOW ALL;

 

To see the default isolation level for the database, run the following command:

 

vsql => select get_config_parameter('TransactionIsolationLevel');

 

Also regarding the SERIALIZABLE isolation level it is the strictest level of SQL transaction isolation. Although this isolation level permits transactions to run concurrently, it creates the effect that transactions are running in serial order. It acquires locks for both read and write operations, which ensures that successive SELECT commands within a single transaction always produce the same results.

 

Hope this can prove to be the lead in answering your query.

 

You can also go through below documented link for detailed information on this:

 

http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ConceptsGuide/Other/Transactions.htm%3FTocPath%3DConcepts%2520Guide%7CTransactions%7C_____0

 

Regards

Rahul Choudhary