Re: ECPG regression with DECLARE STATEMENT support

On Thu, Mar 7, 2019 at 9:56 AM Michael Meskes <meskes(at)postgresql(dot)org> wrote:

> Hi,
>
> > Commit bd7c95f0c1a38becffceb3ea7234d57167f6d4bf add DECLARE
> > STATEMENT support to ECPG. This introduced the new rule
> > for EXEC SQL CLOSE cur and with that it gets transformed into
> > ECPGclose().
> >
> > Now prior to the above commit, someone can declare the
> > cursor in the SQL statement and "CLOSE cur_name" can be
> > also, execute as a normal statement.
>
> That still works, the difference in your test case is that the DECLARE
> statement is prepared.
>
> > Example:
> >
> > EXEC SQL PREPARE cur_query FROM "DECLARE cur1 CURSOR WITH HOLD FOR
> > SELECT count(*) FROM pg_class";
> > EXEC SQL PREPARE fetch_stmt FROM "FETCH next FROM cur1";
> > EXEC SQL EXECUTE cur_query;
> > EXEC SQL EXECUTE fetch_stmt INTO :c;
> > EXEC SQL CLOSE cur1;
> >
> > With commit bd7c95f0c1, "EXEC SQL CLOSE cur1" will fail
> > and throw an error "sqlcode -245 The cursor is invalid".
> >
> > I think the problem here is ECPGclose(), tries to find the
> > cursor into "connection->cursor_stmts" and if it doesn't
> > find it there, just throws an error. Maybe require fix
> > into ECPGclose() - rather than throwing an error continue
> > executing statement "CLOSE cur_name" with ecpg_do().
>
> The problem as I see it is that the cursor is known to the backend but
> not the library.

Exactly. So maybe we should add logic into ECPGclose() if
it doesn't find a cursor in the library - just send a query to the
backend rather than throwing an error.

> Takaheshi-san, Hayato-san, any idea how to improve the
> situation to not error out on statements that used to work?
>
> Michael
> --
> Michael Meskes
> Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
> Meskes at (Debian|Postgresql) dot Org
> Jabber: michael at xmpp dot meskes dot org
> VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
>
>
>

--
Rushabh Lathia