Subject: Re: Bulk persistence strategy
To: Tom Lane <tgl@sss.pgh.pa.us>
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>
Cc: pgsql-performance@postgresql.org
From: Riaan Stander <rstander@exa.co.za>
Message-ID: <de75eb5b-be39-d3f4-ae5f-65d67f53ec59@exa.co.za>
Date: Mon, 22 May 2017 04:14:35 +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: <30684.1495406234@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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

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