Subject: Re: Bulk persistence strategy
To: Simon Riggs <simon@2ndquadrant.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>
Cc: "pgsql-performa." <pgsql-performance@postgresql.org>
From: Riaan Stander <rstander@exa.co.za>
Message-ID: <85a35463-12e4-8647-e7fb-b11fdddbb122@exa.co.za>
Date: Mon, 22 May 2017 10:06:17 +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: 
 <CANP8+jL54J5_i6K3zX9Zm2wGirBbeh1YG0uLXBwARiLjyjenrg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> 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