Subject: Re: Bulk persistence strategy
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <04c901d2a753$f55de020$e019a060$@exa.co.za>
 <15c29aeedc8.27e1.e05fafc80f8daf1488764675043d44fb@exa.co.za>
 <32403.1495377186@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
From: Riaan Stander <rstander@exa.co.za>
Message-ID: <0e47ae52-e80a-fd8e-021c-7c8cc0495b26@exa.co.za>
Date: Sun, 21 May 2017 21:29:53 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: <32403.1495377186@sss.pgh.pa.us>
Content-Type: multipart/alternative;
 boundary="------------4EE339DE6BAE5F3F171E8FDC"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------4EE339DE6BAE5F3F171E8FDC
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit


> Riaan Stander <rstander@exa.co.za> writes:
>> I've come up with generating functions on the go, but I'm concerned about
>> the performance impact of this. I first wanted to use an anonoumys code
>> block, but then I cannot do parameter binding from npgsql.
>> ...
>> Is there a better way I'm missing and is "temp" function creation in
>> Postgres a big performance concern, especially if a server is under load?
> The function itself is only one pg_proc row, but if you're expecting
> to do this thousands of times a minute you might have to adjust autovacuum
> settings to avoid bad bloat in pg_proc.
>
> If you're intending that these functions be use-once, it's fairly unclear
> to me why you bother, as opposed to just issuing the underlying SQL
> statements.
>
> 			regards, tom lane

The intended use is use-once. The reason is that the statements might 
differ per call, especially when we start doing updates. The ideal would 
be to just issue the sql statements, but I was trying to cut down on 
network calls. To batch them together and get output from one query as 
input for the others (declare variables), I have to wrap them in a 
function in Postgres. Or am I missing something? In SQL Server TSQL I 
could declare variables in any statement as required.


--------------4EE339DE6BAE5F3F171E8FDC
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=windows-1252"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <br>
    <blockquote cite="mid:32403.1495377186@sss.pgh.pa.us" type="cite">
      <pre wrap="">Riaan Stander <a class="moz-txt-link-rfc2396E" href="mailto:rstander@exa.co.za">&lt;rstander@exa.co.za&gt;</a> writes:
</pre>
      <blockquote type="cite">
        <pre wrap="">I've come up with generating functions on the go, but I'm concerned about 
the performance impact of this. I first wanted to use an anonoumys code 
block, but then I cannot do parameter binding from npgsql.
...
Is there a better way I'm missing and is "temp" function creation in 
Postgres a big performance concern, especially if a server is under load?
</pre>
      </blockquote>
      <pre wrap="">
The function itself is only one pg_proc row, but if you're expecting
to do this thousands of times a minute you might have to adjust autovacuum
settings to avoid bad bloat in pg_proc.

If you're intending that these functions be use-once, it's fairly unclear
to me why you bother, as opposed to just issuing the underlying SQL
statements.

			regards, tom lane
</pre>
    </blockquote>
    <font face="Courier New" size="-1"><br>
      The intended use is use-once. The reason is that the statements
      might differ per call, especially when we start doing updates. The
      ideal would be to just issue the sql statements, but I was trying
      to cut down on network calls. To batch them together and get
      output from one query as input for the others (declare variables),
      I have to wrap them in a function in Postgres. Or am I missing
      something? In SQL Server TSQL I could declare variables in any
      statement as required.</font><font size="-1"><br>
    </font><br>
  </body>
</html>

--------------4EE339DE6BAE5F3F171E8FDC--