Insert procedure not working.

Moderator: NorbertKrupa

Post Reply
robert.str
Newbie
Newbie
Posts: 3
Joined: Fri Aug 30, 2013 1:07 pm

Insert procedure not working.

Post by robert.str » Thu Sep 19, 2013 1:12 pm

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
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Insert procedure not working.

Post by scutter » Thu Sep 19, 2013 3:25 pm

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.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

robert.str
Newbie
Newbie
Posts: 3
Joined: Fri Aug 30, 2013 1:07 pm

Re: Insert procedure not working.

Post by robert.str » Thu Sep 19, 2013 3:44 pm

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
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Insert procedure not working.

Post by scutter » Thu Sep 19, 2013 4:23 pm

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
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Insert procedure not working.

Post by id10t » Thu Sep 19, 2013 5:50 pm

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
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Insert procedure not working.

Post by id10t » Thu Sep 19, 2013 6:15 pm

[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 :)

Post Reply

Return to “Vertica External Procedures”