From: Tom Lane <tgl@sss.pgh.pa.us>
To: Riaan Stander <rstander@exa.co.za>
cc: pgsql-performance@postgresql.org
Subject: Re: Bulk persistence strategy
In-reply-to: <0e47ae52-e80a-fd8e-021c-7c8cc0495b26@exa.co.za>
References: <04c901d2a753$f55de020$e019a060$@exa.co.za>
 <15c29aeedc8.27e1.e05fafc80f8daf1488764675043d44fb@exa.co.za>
 <32403.1495377186@sss.pgh.pa.us>
 <0e47ae52-e80a-fd8e-021c-7c8cc0495b26@exa.co.za>
Comments: In-reply-to Riaan Stander <rstander@exa.co.za>
	message dated "Sun, 21 May 2017 21:29:53 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <30683.1495406234.1@sss.pgh.pa.us>
Date: Sun, 21 May 2017 18:37:14 -0400
Message-ID: <30684.1495406234@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Riaan Stander <rstander@exa.co.za> writes:
> 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.

Hm, well, feeding data forward to the next query without a network
round trip is a valid concern.

How stylized are these commands?  Have you considered pushing the
generation logic into the function, so that you just have one (or
a few) persistent functions, and the variability slack is taken
up through EXECUTE'd strings?  That'd likely be significantly
more efficient than one-use functions.  Even disregarding the
pg_proc update traffic, plpgsql isn't going to shine in that usage
because it's optimized for repeated execution of functions.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance