Thread: Extended Query Protocol Question

Extended Query Protocol Question

From
Greg
Date:
Hi all. I cant get my head around Extended Query message flow in Frontend/Backend protocol v3.

From documentation I understand that I have to sent Parse, Bind and Execute messages and expect responses: ParseComplete & ReadyForQuery for Prase, BindComplete & ReadyForQuery for Bind and as for Execute reponse I should get DataRow(s).
so far I'm getting proper reponses for Prase & Bind but when I send Execute message I get an error "ERROR C34000M portal "MyPortal" does not exist F.\src\backend\tcop\postgres.c L1810Rexec_execute_message'.

Why this message is been displayed after I get ParseComplete & BindComplete messages?

Thanks.

Re: Extended Query Protocol Question

From
Tom Lane
Date:
Greg <grigorey@yahoo.co.uk> writes:
> Hi all. I cant get my head around Extended Query message flow in Frontend/Backend protocol v3.
>> From documentation I understand that I have to sent Parse, Bind and Execute messages and expect responses:
ParseComplete& ReadyForQuery for Prase, BindComplete & ReadyForQuery for Bind and as for Execute reponse I should get
DataRow(s). 
> so far I'm getting proper reponses for Prase & Bind but when I send Execute message I get an error "ERROR C34000M
portal"MyPortal" does not exist F.\src\backend\tcop\postgres.c L1810Rexec_execute_message'. 

> Why this message is been displayed after I get ParseComplete & BindComplete messages?

The two most likely explanations I can think of are
(1) you didn't give the same portal name in Bind as in Execute; or
(2) you did something between those two commands that ended the
transaction and thus caused the portal to be closed.

            regards, tom lane

Re: Extended Query Protocol Question

From
Greg
Date:
Thanks for quick response.
 
Well, portal name is the same for both bind and execute messages as for 2nd point: I tried composing Parse, Bind and Execute in the same buffer (with Sync message ending each) and then send data to the database as well as sending those messages one by one and reading their response and still getting "portal does not exists" error when Execute message sent to the database. Is there are way to check weather the portal been created for parsed query using pgAdmin III (idea is to send Parse & Bind messages and then freeze the application to see weather portal exists)?
 
Thanks.


--- On Tue, 13/7/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [NOVICE] Extended Query Protocol Question
To: "Greg" <grigorey@yahoo.co.uk>
Cc: pgsql-novice@postgresql.org
Date: Tuesday, 13 July, 2010, 15:55

Greg <grigorey@yahoo.co.uk> writes:
> Hi all. I cant get my head around Extended Query message flow in Frontend/Backend protocol v3.
>> From documentation I understand that I have to sent Parse, Bind and Execute messages and expect responses: ParseComplete & ReadyForQuery for Prase, BindComplete & ReadyForQuery for Bind and as for Execute reponse I should get DataRow(s).
> so far I'm getting proper reponses for Prase & Bind but when I send Execute message I get an error "ERROR C34000M portal "MyPortal" does not exist F.\src\backend\tcop\postgres.c L1810Rexec_execute_message'.

> Why this message is been displayed after I get ParseComplete & BindComplete messages?

The two most likely explanations I can think of are
(1) you didn't give the same portal name in Bind as in Execute; or
(2) you did something between those two commands that ended the
transaction and thus caused the portal to be closed.

            regards, tom lane

Re: Extended Query Protocol Question

From
Tom Lane
Date:
Greg <grigorey@yahoo.co.uk> writes:
> Well, portal name is�the same�for both bind and execute messages
> as for 2nd point: I tried�composing Parse, Bind and Execute in the
> same buffer (with Sync message ending each)�and then send data to
> the database as well as sending those messages one by one�and
> reading their response and still getting "portal does not exists"
> error when Execute message sent to the database.

Sync ends the transaction, if you haven't created a transaction block by
executing BEGIN.  It seems rather pointless to put more than one Sync
in an outgoing packet anyway.  Normally what you'd want is to send
Parse/Bind/Execute/Sync as one packet, because if the Parse or Bind
fails you don't want to try the Execute.

            regards, tom lane

Re: Extended Query Protocol Question

From
Greg
Date:
Ok, tried sending combination of Parse/Bind/Exec/Sync messages - works like a charm.
 
Thanks Tom. Very appreciated. I couldn't get my head around that for few days now!
 


--- On Tue, 13/7/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [NOVICE] Extended Query Protocol Question
To: "Greg" <grigorey@yahoo.co.uk>
Cc: pgsql-novice@postgresql.org
Date: Tuesday, 13 July, 2010, 16:18

Greg <grigorey@yahoo.co.uk> writes:
> Well, portal name is the same for both bind and execute messages
> as for 2nd point: I tried composing Parse, Bind and Execute in the
> same buffer (with Sync message ending each) and then send data to
> the database as well as sending those messages one by one and
> reading their response and still getting "portal does not exists"
> error when Execute message sent to the database.

Sync ends the transaction, if you haven't created a transaction block by
executing BEGIN.  It seems rather pointless to put more than one Sync
in an outgoing packet anyway.  Normally what you'd want is to send
Parse/Bind/Execute/Sync as one packet, because if the Parse or Bind
fails you don't want to try the Execute.

            regards, tom lane