Re: Bulk persistence strategy

On 22 May 2017 at 03:14, Riaan Stander <rstander(at)exa(dot)co(dot)za> wrote:
>
>> Riaan Stander <rstander(at)exa(dot)co(dot)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

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services