From: Riaan Stander <rstander@exa.co.za>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performa. <pgsql-performance@postgresql.org>
Subject: Re: Bulk persistence strategy
Date: Mon, 22 May 2017 10:06:17 +0200
Message-ID: <85a35463-12e4-8647-e7fb-b11fdddbb122@exa.co.za> (raw)
In-Reply-To: <CANP8+jL54J5_i6K3zX9Zm2wGirBbeh1YG0uLXBwARiLjyjenrg@mail.gmail.com>
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>
<de75eb5b-be39-d3f4-ae5f-65d67f53ec59@exa.co.za>
<CANP8+jL54J5_i6K3zX9Zm2wGirBbeh1YG0uLXBwARiLjyjenrg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
> On 22 May 2017 at 03:14, Riaan Stander <rstander@exa.co.za> wrote:
>>> 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.
> It sounds like you don't know about anonymous code blocks with DO
> https://www.postgresql.org/docs/devel/static/sql-do.html
>
Yes I do know about that feature. My first implemented generated an
anonymous code block, but to my utter dismay once I tried actually doing
parameter binding from the application it did not work. This seems to be
a Postgres limitation actually stated in the documentation. The
anonymous code block is treated as a function body with no parameters.
Thanks for the suggestion though.
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, simon@2ndquadrant.com
Subject: Re: Bulk persistence strategy
In-Reply-To: <85a35463-12e4-8647-e7fb-b11fdddbb122@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