Re: [PERFORM] Bulk persistence strategy

> 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