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