Insert procedure not working.

[robert.str]

Hello everyone.

I tried to create external insert procedure.
Everything looks fine(without errors) except that actually it's now working.
Procedure is not inserting any rows.

Code: Select all

#!/bin/bash 

echo "first arg: $1 second arg : $2"

for i in {1..5}
do
vsql -c "insert into testext select RANDOMINT($1 - (1) + 1) + (1), '$2';"
echo "Inserted"

done

vsql -c "commit;"
exit 0  

And one more thing.
As I have read so far. It is not possible to create procedure inside vertica right ? For example something like

Code: Select all

CREATE OR REPLACE PROCEDURE insertpd(userid IN USER.USER_ID%TYPE)
IS
BEGIN
 
  INSERT INTO DBUSER ("USER_ID") VALUES (userid);
  COMMIT;
 
END;

[scutter]

Hi Robert,

Add the commit after the semi-colon for the insert:

vsql -c "insert into testext select RANDOMINT($1 - (1) + 1) + (1), '$2'; commit;"

vsql has autocommit off by default. The vsql -c "commit" doesn't work because that's executing in a new session that is distinct from the original session.

You are correct - vertica does not have built-in support for stored procedures.

[robert.str]

Yeah It worked that way but I thought the performance of this solution will be very low.
So next thing:

create table (id auto_increment, fk1 int , name varchar);
vsql -c "select ins_testext(10,5)"

Data in table

id | fk1 | name
----------+-----+-------
1 | 2 | fa37J
250001 | 5 | fa37J
500001 | 5 | fa37J
750001 | 10 | fa37J
1000001 | 8 | fa37J
1250001 | 2 | fa37J
1500001 | 6 | fa37J
1750001 | 5 | fa37J
2000001 | 7 | fa37J
2250001 | 4 | fa37J

The question is obvious. What is going on with auto increment ?
By the way I am trying to put in testext 1 mln random fk1 and name values. Is there any better way to do it ?

Anyway thanks scutter.



ins_testext.sh

Code: Select all

#!/bin/bash 

for i in {1..100}
do
vsql -c "insert into testext (fk1,name) select RANDOMINT($1 - (1) + 1) + (1), RandomString($2);commit;"
done

exit 0 

[scutter]

You're right about lots of individual insert statements being slow. You could switch it over to a COPY statement. Generate your random values and pipe them into vsql using COPY FROM STDIN. A simplified version of this for the sake of an example:

cat mydata.txt | vsql -c "COPY MYTABLE FROM STDIN DIRECT;"

That's expected behavior for the auto_increment. Each node gets a cache of values. It works through the cached values, and then asks for a another set of values when it runs out. You're guaranteed unique values, there's no guarantee about the values being consecutive.

--Sharon

[id10t]

Hi!

FYI: "vsql -c" can execute only a single command at once (how many times can I repeat it, I don't know, but you people MUST validate each executed statement)
viewtopic.php?f=4&t=1232&p=3973#p3973
viewtopic.php?f=49&t=1074&p=3413#p3413

Example:

Code: Select all

daniel@synapse:~$ vsql -c "select 1; select 2;"
 ?column? 
----------
        2
(1 row)

How to set AUTOCOMMIT properly:

Code: Select all

vsql -v AUTOCOMMIT=ON -c 'QUERY'

Example:

Code: Select all

daniel@synapse:~$ vsql -c '\set'
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'daniel'
USER = 'daniel'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

Code: Select all

daniel@synapse:~$ vsql -v AUTOCOMMIT=ON -c '\set'
VERSION = 'vsql'
AUTOCOMMIT = 'ON'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'daniel'
USER = 'daniel'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

[id10t]

[Continue]
About performance

Of cause it's slow, because its a worst case, but it may be optimized and I believe it can be optimized. For example instead of each INSERT/COMMIT
write to pipe all and after it COMMIT aka BATCH INSERT.
Vertica is analytical database, not transactional