Re: ECPG patch N+1, fix auto-prepare

From: Boszormenyi Zoltan <zb(at)cybertec(dot)at>
To: Michael Meskes <meskes(at)postgresql(dot)org>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Dan Colish <dan(at)unencrypted(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org, Hans-Juergen Schoenig <hs(at)cybertec(dot)at>
Subject: Re: ECPG patch N+1, fix auto-prepare
Date: 2009-12-15 13:19:19
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


here's another patch that aims to fix auto-prepare.

The reason is, that in the project porting from Informix,
a small test case that used a cursor and two small SELECTs
issued for every record retrieved by the cursor showed that
for this case, the ESQL compiled binary finished about 60%
faster then the ECPG compiled counterpart running against
PostgreSQL. The cursor retrieved a little over 60'000 records.

We have modified the test code to prepare the two SELECTs
and now the new test code was faster then the ESQL/Informix
code, parsing and planning the two small SELECTs had such
an accumulated runtime effect.

Then we looked at ECPG and discovered that it already has
the auto-prepare feature, and tried it using "ecpg -r prepare".

However, it turned out that the auto-prepare feature is
over-zealous, it tries to prepare statements that are rejected
by the server, returning -400 (ECPG_PGSQL). One example is

char *sqlstr = "SELECT ...";

EXEC SQL PREPARE stmt1 FROM :sqlstr;

The attached patch is an attempt to make the preprocessor
only pass ECPGst_prepnormal when it's definitely appropriate,
i.e. only for DELETE, INSERT, UPDATE and SELECT(-like)
statements in the grammar.


Best regards,
Zoltán Böszörményi

Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH

Attachment Content-Type Size
6-pg85-fix-autoprepare-1-ctxdiff.patch text/x-patch 34.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2009-12-15 13:22:14 Re: Fwd: pgAdmin III: timestamp displayed in what time zone?
Previous Message Greg Stark 2009-12-15 13:09:02 Re: Range types