Re: Bulk persistence strategy

> 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.

Regards
Riaan Stander