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
Date: Sun, 21 May 2017 18:37:14 -0400
Message-ID: <30684.1495406234@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, rstander@exa.co.za
Subject: Re: Bulk persistence strategy
In-Reply-To: <30684.1495406234@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox