From: Riaan Stander <rstander@exa.co.za>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bulk persistence strategy
Date: Mon, 22 May 2017 04:14:35 +0200
Message-ID: <de75eb5b-be39-d3f4-ae5f-65d67f53ec59@exa.co.za> (raw)
In-Reply-To: <30684.1495406234@sss.pgh.pa.us>
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>
<30684.1495406234@sss.pgh.pa.us>
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
The commands are generated from a complex object/type in the
application. Some of them can be quite large. With modifications they do
state tracking too, so that we only update fields that actually changed
and can do optimistic concurrency checking.
It'll probably make more sense to try create a function per type of
object that deals with the query generation. That way I can create a
Postgres type that maps from the application object.
Thanks for the advice. I'll give that a shot.
Regards
Riaan Stander
--
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: rstander@exa.co.za, tgl@sss.pgh.pa.us
Subject: Re: Bulk persistence strategy
In-Reply-To: <de75eb5b-be39-d3f4-ae5f-65d67f53ec59@exa.co.za>
* 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