Re: How to run in parallel in Postgres, EXECUTE_PARALLEL

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--pEDXpH4lfXJQWgv5oE6J12PFXSN64qRMW
Content-Type: multipart/mixed; boundary="arVSpE950WlE3ApcxPZ2IiTn8UaIWIFbP"

--arVSpE950WlE3ApcxPZ2IiTn8UaIWIFbP
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable

On 12/8/19 1:14 PM, Lars Aksel Opsahl wrote:
> Do you or anybody know if there are any plans for a function call that
> support the calling structure below or something like it and that then
> could finish in 1 second ? (If you are calling a void function, the
> return value should not be any problem.)
>=20
> DO
> $body$
> *DECLARE*=C2=A0
> command_string_list text[3];
> *BEGIN*
> command_string_list[0] =3D 'SELECT pg_sleep(1)';
> command_string_list[1] =3D 'SELECT pg_sleep(1)';
> command_string_list[2] =3D 'SELECT pg_sleep(1)';
> EXECUTE_PARALLEL command_string_list;
> *END*
> $body$;
>=20
> The only way to this today as I understand it, is to open 3 new
> connections back to the database which you can be done in different way=
s.=C2=A0

Yes, correct.

> If we had a=C2=A0parallel=C2=A0functions like the one above=C2=A0it's e=
asier to
> make=C2=A0parallel sql without using complex scripts, java, python or o=
ther
> system.

It does require one connection per statement, but with dblink it is not
necessarily all that complex. For example (granted, this could use more
error checking, etc.):

8<----------------
CREATE OR REPLACE FUNCTION
 execute_parallel(stmts text[])
RETURNS text AS
$$
declare
  i int;
  retv text;
  conn text;
  connstr text;
  rv int;
  db text :=3D current_database();
begin
  for i in 1..array_length(stmts,1) loop
    conn :=3D 'conn' || i::text;
    connstr :=3D 'dbname=3D' || db;
    perform dblink_connect(conn, connstr);
    rv :=3D dblink_send_query(conn, stmts[i]);
  end loop;
  for i in 1..array_length(stmts,1) loop
    conn :=3D 'conn' || i::text;
    select val into retv
    from dblink_get_result(conn) as d(val text);
  end loop;
  for i in 1..array_length(stmts,1) loop
    conn :=3D 'conn' || i::text;
    perform dblink_disconnect(conn);
  end loop;
  return 'OK';
  end;
$$ language plpgsql;
8<----------------

And then:

8<----------------
\timing
DO $$
  declare
    stmts text[];
  begin
    stmts[1] =3D 'select pg_sleep(1)';
    stmts[2] =3D 'select pg_sleep(1)';
    stmts[3] =3D 'select pg_sleep(1)';
  PERFORM execute_parallel(stmts);
  end;
$$ LANGUAGE plpgsql;
DO
Time: 1010.831 ms (00:01.011)
8<----------------

HTH,

Joe
--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


--arVSpE950WlE3ApcxPZ2IiTn8UaIWIFbP--

--pEDXpH4lfXJQWgv5oE6J12PFXSN64qRMW
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEg14x9eymXoJyHrH+N/L3QzX32GUFAl3tVzUACgkQN/L3QzX3
2GURlg//ZFeyASkZxbVv7gWCR29M0O2MlRMOTYUoZWlmDkANm8FlSHrO8DFdN8uc
tAHZ/V0/iJsQYEo91AImt7hcZ0t2lOfNpKz14xx0takCRdMEI/Mb0baPLS1aiiwG
iZ4o4dhNpsv4+Pjg0ao/ST4laITOUUNGSnt/WL0L5d3NnCMZyCWxFPscGenBfBZW
yQVXR1pQ94wG7UBQbDNyhEbnc7MqYaYhC6WjclLgcoy1cbXBQ5ZwY7EMeHg3mxCA
i2+u1C75g9nCwKwAefOiIOhx6SiDG7IONNFayYEOv+Bv9doNKjaihxKw0Y7thhUj
ATT2W8teCF01NME4ZlUpj/PERHmtH6AnSxyhypPzvy2UrwV50X+H519Jiy2W3N0W
ldhTcoPT4El2IzlKgvAgKdhvT+dhrT15aVARcePMbj1TjihdRQ73d13sJlahI9Us
qxzP6c+Y4V3QHoBJhldzhu6XZ15BUomi992dY15ju9Ib59Zzt8/ZoN0ejzsoz1I7
m7JdKMRhKTuUsAycOGiCUNfqOkJoG0bMlSLVqR1U1CGh3FBTulYpfCa4NUn0b3tm
gp1AU1eMktV14u5MBqN3x5oUrkeM/HXOBM4soCG2ByuJlUXAVLfG3bH5JSz/mdAQ
qWCx+PZyGZjf2TAlLzt2jiS8JN7dOIId6Y7U5ywiuJH3jBwPn7g=
=Qa1y
-----END PGP SIGNATURE-----

--pEDXpH4lfXJQWgv5oE6J12PFXSN64qRMW--