Re: insert multi values limitations

--00000000000035adfc0593361b6b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Bonjour,

D'apr=C3=A8s
https://stackoverflow.com/questions/47664889/jdbc-batch-operations-understa=
nding/48349524#48349524

*"It is smart enough to use 2 statements in order to insert 10 rows. The
first one is 8-valued statement, and the second one is 2-valued statement.
Usage of powers of two enables pgjdbc to keep the number of distinct
statements sane, and that improves performance as often-used statements are
server-prepared (see What's the life span of a PostgreSQL server-side
prepared statement
<https://stackoverflow.com/questions/32297503/whats-the-life-span-of-a-post=
gresql-server-side-prepared-statement/32300222#32300222>
)" *

Si tu souhaites envoyer exactement le nombre de valeurs pr=C3=A9cis=C3=A9es=
 dans
batch_size, alors il faudra mettre une puissance de 2, sous peine de voir
l'INSERT splitt=C3=A9.

Anthony


On Mon, Sep 23, 2019, 11:24 CRUMEYROLLE Pierre <[email protected]>
wrote:

>
> bonjour
>
> je teste une insertion multi valeurs via spring boot jpa hibernate jdbc ,
> en positionnant les param=C3=A8tres suivants :
>
> spring.jpa.properties.hibernate.jdbc.batch_size=3D10
> reWriteBatchedInsereWriteBatchedInserts=3Dtrue  dans ma connexion jdbc
>
> spring.datasource.url=3Djdbc:postgresql://localhost:5432/postgres?reWrite=
BatchedInserts=3Dtrue
>
> normalement ceci doit g=C3=A9n=C3=A9rer des insert multi valeurs par paqu=
ets de
> 10 =3D> insert into mytable (id , data) values
> (1,),(,),(,),(,),(,),(,),(,),(,),(,),(10,)
>
> mais je constate que les insert multi values  sont "split=C3=A9s" de la
> fa=C3=A7on suivante
>
> insert into mytable (id , data) values (1,),(,),(,),(,),(,),(,),(,),(8,)
> insert into mytable (id , data) values (9,),(10,),
>
> il semblerait que le driver postgres split les insert multi values
> suivant des r=C3=A8gles particuli=C3=A8res
> et en effet je retombe dans le sc=C3=A9nario d=C3=A9crit en fin de cette =
page =3D>
>
> https://vladmihalcea.com/postgresql-multi-row-insert-rewritebatchedinsert=
s-property/
> qui dit "the PostgreSQL driver will split it into smaller ones to
> address TCP deadlock issues"
>
> quelqu'un peut il me donner une petite explication confirmation sur ce
> comportement
>
> Merci
>
>
>

--00000000000035adfc0593361b6b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div>Bonjour,<div dir=3D"auto"><br></div><div dir=3D"auto=
">D&#39;apr=C3=A8s=C2=A0<a href=3D"https://stackoverflow.com/questions/4766=
4889/jdbc-batch-operations-understanding/48349524#48349524">https://stackov=
erflow.com/questions/47664889/jdbc-batch-operations-understanding/48349524#=
48349524</a></div><div dir=3D"auto"><br></div><div dir=3D"auto"><i><span st=
yle=3D"color:rgb(12,13,14);font-family:&quot;helvetica neue&quot;,helvetica=
,arial,sans-serif;font-size:15px;background-color:rgb(255,255,255)">&quot;I=
t is smart enough to use 2 statements in order to insert 10 rows. The first=
 one is 8-valued statement, and the second one is 2-valued statement. Usage=
 of powers of two enables pgjdbc to keep the number of distinct statements =
sane, and that improves performance as often-used statements are server-pre=
pared (see=C2=A0</span><a href=3D"https://stackoverflow.com/questions/32297=
503/whats-the-life-span-of-a-postgresql-server-side-prepared-statement/3230=
0222#32300222" style=3D"margin:0px;padding:0px;border:0px;line-height:inher=
it;font-family:&quot;helvetica neue&quot;,helvetica,arial,sans-serif;font-s=
ize:15px;vertical-align:baseline;color:rgb(0,119,204);text-decoration-line:=
none;background-color:rgb(255,255,255)">What&#39;s the life span of a Postg=
reSQL server-side prepared statement</a><span style=3D"color:rgb(12,13,14);=
font-family:&quot;helvetica neue&quot;,helvetica,arial,sans-serif;font-size=
:15px;background-color:rgb(255,255,255)">=C2=A0)&quot;=C2=A0</span></i><br>=
</div><div dir=3D"auto"><font color=3D"#0c0d0e" face=3D"helvetica neue, hel=
vetica, arial, sans-serif"><span style=3D"font-size:15px"><i><br></i></span=
></font></div><div dir=3D"auto">Si tu souhaites envoyer exactement le nombr=
e de valeurs pr=C3=A9cis=C3=A9es dans batch_size, alors il faudra mettre un=
e puissance de 2, sous peine de voir l&#39;INSERT splitt=C3=A9.</div><div d=
ir=3D"auto"><br></div><div dir=3D"auto">Anthony</div><br><br><div class=3D"=
gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Sep 23, 2019, 11=
:24 CRUMEYROLLE Pierre &lt;<a href=3D"mailto:[email protected]" tar=
get=3D"_blank" rel=3D"noreferrer">[email protected]</a>&gt; wrote:<=
br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;borde=
r-left:1px #ccc solid;padding-left:1ex"><br>
bonjour<br>
<br>
je teste une insertion multi valeurs via spring boot jpa hibernate jdbc ,<b=
r>
en positionnant les param=C3=A8tres suivants :<br>
<br>
spring.jpa.properties.hibernate.jdbc.batch_size=3D10<br>
reWriteBatchedInsereWriteBatchedInserts=3Dtrue=C2=A0 dans ma connexion jdbc=
=C2=A0 =C2=A0<br>
spring.datasource.url=3Djdbc:postgresql://localhost:5432/postgres?reWriteBa=
tchedInserts=3Dtrue<br>
<br>
normalement ceci doit g=C3=A9n=C3=A9rer des insert multi valeurs par paquet=
s de=C2=A0 <br>
10 =3D&gt; insert into mytable (id , data) values=C2=A0 <br>
(1,),(,),(,),(,),(,),(,),(,),(,),(,),(10,)<br>
<br>
mais je constate que les insert multi values=C2=A0 sont &quot;split=C3=A9s&=
quot; de la=C2=A0 <br>
fa=C3=A7on suivante<br>
<br>
insert into mytable (id , data) values (1,),(,),(,),(,),(,),(,),(,),(8,)<br=
>
insert into mytable (id , data) values (9,),(10,),<br>
<br>
il semblerait que le driver postgres split les insert multi values=C2=A0 <b=
r>
suivant des r=C3=A8gles particuli=C3=A8res<br>
et en effet je retombe dans le sc=C3=A9nario d=C3=A9crit en fin de cette pa=
ge =3D&gt;=C2=A0 <br>
<a href=3D"https://vladmihalcea.com/postgresql-multi-row-insert-rewritebatc=
hedinserts-property/" rel=3D"noreferrer noreferrer noreferrer" target=3D"_b=
lank">https://vladmihalcea.com/postgresql-multi-row-insert-rewritebatchedin=
serts-property/</a><br>
qui dit &quot;the PostgreSQL driver will split it into smaller ones to=C2=
=A0 <br>
address TCP deadlock issues&quot;<br>
<br>
quelqu&#39;un peut il me donner une petite explication confirmation sur ce=
=C2=A0 <br>
comportement<br>
<br>
Merci<br>
<br>
<br>
</blockquote></div>
</div></div>

--00000000000035adfc0593361b6b--