Re: patch to implement ECPG side tracing / tracking ...

Michael Meskes wrote:
> Hans,
>
> nce Jaime already asked for a use case, just a few small comments from
> me.
>
>   
>> @@ -4,6 +4,7 @@
>>  #include "postgres_fe.h"
>>
>>  #include <ctype.h>
>> +#include <inttypes.h>
>>     
>
> This is not portable. You don't want to include this header.
>
> Did I see this right that you use the statement cache for auto-prepared
> statements even if the statement is not auto prepared? Some statements are not
> profiled, how did you decide which one to do? 
>
> There is no test case.
>
> Before looking into it in detail I think we should first figure out if this
> feature really has a benefit.
>
> Michael
>   

hello ...

the use cases for this thing are quite simple: we are currently porting 
hundreds (!) of complex Informix terminal applications to PostgreSQL. 
these are basically terminal applications used to perform a certain 
tasks. given the vast amount of code, we simply cannot change a single 
program because if we have to dig into the actual application code, we 
are dead before actually starting (business logic is a nightmare). so, 
to get around the problem we are basically adding all extensions to ECPG 
we need to make this work. this is why we did all this SQLDA stuff and 
so on you have seen recently.

the current problems are a bit more delicate: we have this vast number 
of programs and some of them perform better than Informix and some 
simply don't. Informix has some sort of "explain mode" (I forgot the 
exact name) which allows you to see which query is executed how by the 
system. effectively, you can use it to performance tune your precompiler 
application. in PostgreSQL it is currently a little hard to get from the 
log what is executed how often by which application in which speed and 
so on. so, we came up with the idea of adding a flag to the precompiler 
which essential keep stats for us and display it on exit (could be sent 
to a file then or so without anybody's notice). this would give 
excellent data to start with and it would make checking the database 
part of the application easily.
why for prepared queries: we found out that Informix is heavily using 
prepared queries internally. we already fixed something in this area 
(patch sent some time ago) and we were finally able to catch up with 
Informix performance-wise in this area (mostly cursor work). before this 
auto_prepare fix, we were sometimes 2-3 times slower than Informix. 
saving on network time solved the job. now we are left with many many 
programs performing somehow strange and we need to check for every 
program why. a decent summary on exit would be gold here.

it seems we will also come up with a server-side extension soon which 
basically compares and logs planner / executor starts the way we do it 
for stored procedures now (thanks to martin pilhak). we simply need it 
so that we can figure out which of our XXX programs did what then. 
testing one after the other is not so easy, some of them depend on each.

to make it short: it is impossible to port hundreds of applications to 
PostgreSQL without having the chance to trace what the precompiler is 
doing how often in which program via which connection. it is simply 
impossible. so, we really and desparately need this patch in.

many thanks,

hans


-- 
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de