Thread: Error 'Named portals'

Hello,
I use PsqlODBC 6.30.0248 with Access97.
My problem is illustrated by an example:
I have an Access97 base 'mybase'. It is edited on PC
under Win95. After all I want to have it and its backup
in Postgresql on SunOS. I connect it by PsqlODBC. I put
two commands in 'Connect settings'(Advanced options - connection):

DROP TABLE mybackup;ALTER TABLE mybase RENAME TO mybackup;

At the very beginning the Postgresql base is empty. After connecting
the base 'mybase' should be copied into Postgresql.
On first connection I get the following notes in logs (#-s are my
comments):

ERROR:  Relation mybackup Does Not Exist!
#I agree - this is correct. There is no relation 'mybackup' yet.
ERROR:  Named portals may only be used in begin/end transaction blocks
#I don't understand it. What does it mean? What happened with
#rename operation? There should appear error message, because
#there was no 'mybase' base yet.

On disconnect the sent base 'mybase' appears in Postgresql. It is OK.

On second connection I get this:

ERROR:  Relation mybackup Does Not Exist!
#I agree - this is correct. There is no relation mybackup yet.
ERROR:  Named portals may only be used in begin/end transaction blocks
#Still can't understand.
ERROR:  amcreate: mybase relation already exists
#It looks like the rename operation was not successed and this is
#the reason for this message.

My question:
1.What does this message mean:
ERROR:  Named portals may only be used in begin/end transaction blocks
Is it the reason of failing the rename operation? How can I avoid it?
2.(not question but suggestion)I think it would be a good idea to
equip driver with the ability of running SQL commands just before
disconnection from Postgresql.

BTW, I want to thank Byron Nikolaidis for quick response and useful
patch.

Thanks in advance
Wojciech Pietron

wojtek wrote:

> DROP TABLE mybackup;ALTER TABLE mybase RENAME TO mybackup;
>

One of the problems is because the driver is sending this as a single
command to Postgres.  Since the first part of the command errors out, the
second part is never even executed.  The solution to this is to break up
the commands into single commands and then send each to the backend, like
pgsql already does.

> ERROR:  Named portals may only be used in begin/end transaction blocks
> #I don't understand it. What does it mean? What happened with
> #rename operation? There should appear error message, because
> #there was no 'mybase' base yet.
>

A query is performed right after the connect settings is sent to discover
if there is a  large object oid type defined in your database.  It looks
like this:  declare SQL_CUR0123456 cursor for select oid from pg_type
where typname='lo'.  This query is inside the BEGIN which occurred prior
to the ERROR above.  Since the transaction is now in a bad state because
of that error, it complains about the declare/cursor not being inside a
transaction, which Postgres requires.

This is also caused by the driver.  It should have aborted the transaction
when the error occured.  Then begin another one for the next query.  It
normally does this, but not in the Connect Settings because they are
handled differently.  Or perhaps, none of the connection stuff should even
be in a transaction.

I am going to overhaul this ConnectSettings process.  It is going to send
each command separately now and handle errors better.


Byron