agora inbox for pgsql-interfaces@postgresql.org
help / color / mirror / Atom feedExtended Query, flush or sync ?
12+ messages / 3 participants
[nested] [flat]
* Extended Query, flush or sync ?
@ 2009-12-18 21:44 ` Raimon Fernandez <coder@montx.com>
2009-12-18 21:55 ` Re: Extended Query, flush or sync ? Tom Lane <tgl@sss.pgh.pa.us>
2009-12-19 03:31 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
1 sibling, 2 replies; 12+ messages in thread
From: Raimon Fernandez @ 2009-12-18 21:44 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hello,
It's not clear for me if I have to issue a flush or sync after each process of an extended query.
It's almost working for me only when I send a sync, but not when I send a flush. With the flush, the connection seems freezed, or at least, I don't get any data from postgre.
- Send the parse command
- sync
- Receive the ParseComplete
-sync
- Send the Bind
- sync
- Receive the BincComplete
- send the Execute
- receive an error => "portal xxxxxxx does not exist"
thanks,
regards,
r.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
@ 2009-12-18 21:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-12-18 23:40 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2009-12-18 21:55 UTC (permalink / raw)
To: Raimon Fernandez <coder@montx.com>; +Cc: pgsql-general@postgresql.org
Raimon Fernandez <coder@montx.com> writes:
> It's not clear for me if I have to issue a flush or sync after each process of an extended query.
Basically, you send one of these at the points where you're going to
wait for an answer back. Sync is different from Flush in that it also
provides a resynchronization point after an error: when the backend hits
an error while processing a given message, it ignores following messages
up to the next Sync.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-18 21:55 ` Re: Extended Query, flush or sync ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-12-18 23:40 ` Raimon Fernandez <coder@montx.com>
0 siblings, 0 replies; 12+ messages in thread
From: Raimon Fernandez @ 2009-12-18 23:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org
On 18/12/2009, at 22:55, Tom Lane wrote:
> Raimon Fernandez <coder@montx.com> writes:
>> It's not clear for me if I have to issue a flush or sync after each process of an extended query.
>
> Basically, you send one of these at the points where you're going to
> wait for an answer back. Sync is different from Flush in that it also
> provides a resynchronization point after an error: when the backend hits
> an error while processing a given message, it ignores following messages
> up to the next Sync.
So I have to send on of these after sending a Parsing comand, a Bind comand, and Execute ?
It's normal that I don't receive nothing if I send a Flush instead of a Sync ?
regards and thanks,
raimon
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
@ 2009-12-19 03:31 ` John DeSoi <desoi@pgedit.com>
2009-12-19 07:40 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
1 sibling, 1 reply; 12+ messages in thread
From: John DeSoi @ 2009-12-19 03:31 UTC (permalink / raw)
To: Raimon Fernandez <coder@montx.com>; +Cc: pgsql-general List <pgsql-general@postgresql.org>
On Dec 18, 2009, at 4:44 PM, Raimon Fernandez wrote:
> It's not clear for me if I have to issue a flush or sync after each process of an extended query.
>
> It's almost working for me only when I send a sync, but not when I send a flush. With the flush, the connection seems freezed, or at least, I don't get any data from postgre.
>
>
> - Send the parse command
> - sync
> - Receive the ParseComplete
> -sync
> - Send the Bind
> - sync
> - Receive the BincComplete
> - send the Execute
> - receive an error => "portal xxxxxxx does not exist"
I send:
parse
bind
describe
execute
sync
and then loop on the connection stream to receive the responses.
John DeSoi, Ph.D.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 03:31 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
@ 2009-12-19 07:40 ` Raimon Fernandez <coder@montx.com>
2009-12-19 15:32 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
0 siblings, 1 reply; 12+ messages in thread
From: Raimon Fernandez @ 2009-12-19 07:40 UTC (permalink / raw)
To: pgsql-general List <pgsql-general@postgresql.org>; +Cc: John DeSoi <desoi@pgedit.com>
Hello,
On 19/12/2009, at 4:31, John DeSoi wrote:
>
> On Dec 18, 2009, at 4:44 PM, Raimon Fernandez wrote:
>
>> It's not clear for me if I have to issue a flush or sync after each process of an extended query.
>>
>> It's almost working for me only when I send a sync, but not when I send a flush. With the flush, the connection seems freezed, or at least, I don't get any data from postgre.
>>
>>
>> - Send the parse command
>> - sync
>> - Receive the ParseComplete
>> -sync
>> - Send the Bind
>> - sync
>> - Receive the BincComplete
>> - send the Execute
>> - receive an error => "portal xxxxxxx does not exist"
>
>
> I send:
>
> parse
> bind
> describe
> execute
> sync
>
> and then loop on the connection stream to receive the responses.
And do you get the parseComplete after sending the parse or after sending the sync ?
I'm not getting parseComplete, bindComplete if I don't send a sync after each command.
If I follow your advice, after the sync, I get the parseComplete, bincComplete, and portalSuspended (beacuse I've reach the max rows)
Don't know if your correct approach is the correct, but why send a Bind if we don't know if the parse has been successfully created ...
From the docs:
"A Flush must be sent after any extended-query command except Sync, if the frontend wishes to examine the results of that command before issuing more commands.
Without Flush, messages returned by the backend will be combined into the minimum possible number of packets to minimize network overhead."
Ok, I see that both approachs should work, but for me, sending a flush after each extended query command like parse, bind, ... doesn't do nothing ...
And also from the docs:
"If Execute terminates before completing the execution of a portal (due to reaching a nonzero result- row count), it will send a PortalSuspended message; t
he appearance of this message tells the frontend that another Execute should be issued against the same portal to complete the operation. "
If I execute with a row limit of 1000, and I know there are more than 1000 rows, I get the portalSuspended as described.
But, If a issue a new Execute, postgresql says that myPortal doesn't exist anymore.
How I can get those 1000 rows ?
thanks !
regards,
raimon
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 03:31 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
2009-12-19 07:40 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
@ 2009-12-19 15:32 ` John DeSoi <desoi@pgedit.com>
2009-12-20 19:11 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-22 16:09 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
0 siblings, 2 replies; 12+ messages in thread
From: John DeSoi @ 2009-12-19 15:32 UTC (permalink / raw)
To: Raimon Fernandez <coder@montx.com>; +Cc: pgsql-general List <pgsql-general@postgresql.org>
On Dec 19, 2009, at 2:40 AM, Raimon Fernandez wrote:
>> I send:
>>
>> parse
>> bind
>> describe
>> execute
>> sync
>>
>> and then loop on the connection stream to receive the responses.
>
> And do you get the parseComplete after sending the parse or after sending the sync ?
I don't really know or care. I send the entire sequence above and then read the results handling each possible case. In other words, I don't read anything after each message; I only read after sending the sync.
> And also from the docs:
>
> "If Execute terminates before completing the execution of a portal (due to reaching a nonzero result- row count), it will send a PortalSuspended message; t
> he appearance of this message tells the frontend that another Execute should be issued against the same portal to complete the operation. "
>
> If I execute with a row limit of 1000, and I know there are more than 1000 rows, I get the portalSuspended as described.
>
> But, If a issue a new Execute, postgresql says that myPortal doesn't exist anymore.
>
> How I can get those 1000 rows ?
Are you using a named portal? Are you reading all responses until you receive a ready for query response? There are a lot of details - it really helped me to look at the psql source.
John DeSoi, Ph.D.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 03:31 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
2009-12-19 07:40 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 15:32 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
@ 2009-12-20 19:11 ` Raimon Fernandez <coder@montx.com>
2009-12-21 01:30 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
1 sibling, 1 reply; 12+ messages in thread
From: Raimon Fernandez @ 2009-12-20 19:11 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: pgsql-general List <pgsql-general@postgresql.org>
Hi John,
I'm not seeing my e-mails on the PostgreSQL General List ...
??????
On 19/12/2009, at 16:32, John DeSoi wrote:
>
> On Dec 19, 2009, at 2:40 AM, Raimon Fernandez wrote:
>
>>> I send:
>>>
>>> parse
>>> bind
>>> describe
>>> execute
>>> sync
>>>
>>> and then loop on the connection stream to receive the responses.
>>
>> And do you get the parseComplete after sending the parse or after sending the sync ?
>
> I don't really know or care. I send the entire sequence above and then read the results handling each possible case. In other words, I don't read anything after each message; I only read after sending the sync.
I see, I don't know why I was sending each command in a separate communication, I can pack all of them and send them at the same time, except de Parse, that will go at the connection beggining in my case.
>> And also from the docs:
>>
>> "If Execute terminates before completing the execution of a portal (due to reaching a nonzero result- row count), it will send a PortalSuspended message; t
>> he appearance of this message tells the frontend that another Execute should be issued against the same portal to complete the operation. "
>>
>> If I execute with a row limit of 1000, and I know there are more than 1000 rows, I get the portalSuspended as described.
>>
>> But, If a issue a new Execute, postgresql says that myPortal doesn't exist anymore.
>>
>> How I can get those 1000 rows ?
>
> Are you using a named portal? Are you reading all responses until you receive a ready for query response? There are a lot of details - it really helped me to look at the psql source.
I'm using Portals with my own name, I'll give a shot later ...
thanks !
regards,
r.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 03:31 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
2009-12-19 07:40 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 15:32 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
2009-12-20 19:11 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
@ 2009-12-21 01:30 ` John DeSoi <desoi@pgedit.com>
0 siblings, 0 replies; 12+ messages in thread
From: John DeSoi @ 2009-12-21 01:30 UTC (permalink / raw)
To: Raimon Fernandez <coder@montx.com>; +Cc: pgsql-general List <pgsql-general@postgresql.org>
Hi Raimon,
On Dec 20, 2009, at 2:11 PM, Raimon Fernandez wrote:
>
> I'm not seeing my e-mails on the PostgreSQL General List ...
>
> ??????
Yes, my last message did not make it to the list yesterday (you obviously received it). I double checked and it was cc to the list.
> I can pack all of them and send them at the same time, except de Parse, that will go at the connection beggining in my case.
I have two routines, prepare and exec_prepare.
To prepare a named statement for multiple uses, I use prepare (parse, describe, sync).
exec_prepare can take a statement from prepare OR you can pass it the unparsed SQL instead (along with the parameters). In the second case it performs the parse first with the unnamed prepared statement (empty string) and then executes it. This is nice because if you don't need multiple executions, you can build and execute with a single network write and read. You get the safety of parameterized execution and you don't have a prepared statement to dispose of in another operation.
John DeSoi, Ph.D.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-18 21:44 ` Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 03:31 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
2009-12-19 07:40 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-19 15:32 ` Re: Extended Query, flush or sync ? John DeSoi <desoi@pgedit.com>
@ 2009-12-22 16:09 ` Raimon Fernandez <coder@montx.com>
1 sibling, 0 replies; 12+ messages in thread
From: Raimon Fernandez @ 2009-12-22 16:09 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: pgsql-general List <pgsql-general@postgresql.org>
On 19/12/2009, at 16:32, John DeSoi wrote:
>> If I execute with a row limit of 1000, and I know there are more than 1000 rows, I get the portalSuspended as described.
>>
>> But, If a issue a new Execute, postgresql says that myPortal doesn't exist anymore.
>>
>> How I can get those 1000 rows ?
>
> Are you using a named portal? Are you reading all responses until you receive a ready for query response? There are a lot of details - it really helped me to look at the psql source.
Yes, I'm using a named portal.
The new question is:
When I get the PortalSuspended, I get the 1000 rows, and for fetching the others, I have to send a new Execute to the same Portal:
"If Execute terminates before completing the execution of a portal (due to reaching a nonzero result- row count), it will send a PortalSuspended message; the appearance of this message tells the frontend that another Execute should be issued against the same portal to complete the operation. "
But the portal isn't destroyed after a sync ?
I'm getting a "Portal 'myPortal' doesn't exist "when sending the next Execute ...
1. Parse the Select with some $1, $2
2. Send a Bind + Describe + Execute + Sync
3. received the portalSuspended
4. Send the Execute
5. Receive the error "Portal 'myPortal' doesn't exist "
thanks,
regards,
raimon
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
@ 2009-12-22 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-12-22 18:56 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
2009-12-23 09:24 ` Re: Extended Query, flush or sync ? Raimon Fernandez <coder@montx.com>
1 sibling, 2 replies; 12+ messages in thread
From: Tom Lane @ 2009-12-22 17:15 UTC (permalink / raw)
To: Raimon Fernandez <coder@montx.com>; +Cc: John DeSoi <desoi@pgedit.com>; pgsql-general List <pgsql-general@postgresql.org>
Raimon Fernandez <coder@montx.com> writes:
> But the portal isn't destroyed after a sync ?
Not directly by a Sync, no.
> I'm getting a "Portal 'myPortal' doesn't exist "when sending the next Execute ...
End of transaction would destroy portals --- are you holding a
transaction open for this? It's basically just like a cursor.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-22 17:15 ` Re: Extended Query, flush or sync ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-12-22 18:56 ` Raimon Fernandez <coder@montx.com>
1 sibling, 0 replies; 12+ messages in thread
From: Raimon Fernandez @ 2009-12-22 18:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John DeSoi <desoi@pgedit.com>; pgsql-general List <pgsql-general@postgresql.org>
On 22/12/2009, at 18:15, Tom Lane wrote:
> Raimon Fernandez <coder@montx.com> writes:
>> But the portal isn't destroyed after a sync ?
>
> Not directly by a Sync, no.
ok,
>> I'm getting a "Portal 'myPortal' doesn't exist "when sending the next Execute ...
>
> End of transaction would destroy portals --- are you holding a
> transaction open for this? It's basically just like a cursor.
no that I'm aware of it ...
I'll investigate it further ...
thanks!
regards,
raimon
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Extended Query, flush or sync ?
2009-12-22 17:15 ` Re: Extended Query, flush or sync ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-12-23 09:24 ` Raimon Fernandez <coder@montx.com>
1 sibling, 0 replies; 12+ messages in thread
From: Raimon Fernandez @ 2009-12-23 09:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John DeSoi <desoi@pgedit.com>; pgsql-general List <pgsql-general@postgresql.org>
On 22/12/2009, at 18:15, Tom Lane wrote:
> Raimon Fernandez <coder@montx.com> writes:
>> But the portal isn't destroyed after a sync ?
>
> Not directly by a Sync, no.
>
>> I'm getting a "Portal 'myPortal' doesn't exist "when sending the next Execute ...
>
> End of transaction would destroy portals --- are you holding a
> transaction open for this? It's basically just like a cursor.
OK, after re-reading your email and the docs again and again, I see that portals must be inside a transaction, now it's working ...
Here are my steps:
- parse the Selects
...
- start transaction
- bind using a prepared statement name and a portal name
- execute x n
- close transaction
...
is this the correct way ?
And in the case I limit the execute, how I can get the pending rows ?
I'm using a CURSOR with the portal just created, and it works perfectly.
Using a new execute, I'm getting again the previous rows plus the new ones, and with the CURSOR, only the pending rows ...
Is this the correct way ?
And, where I can get more info about when it's better to use an extended query, a portal, a cursor, a simple query, ... ?
thanks!
regards,
raimon
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2009-12-23 09:24 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-18 21:44 ` Raimon Fernandez <coder@montx.com>
2009-12-18 21:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-12-18 23:40 ` Raimon Fernandez <coder@montx.com>
2009-12-19 03:31 ` John DeSoi <desoi@pgedit.com>
2009-12-19 07:40 ` Raimon Fernandez <coder@montx.com>
2009-12-19 15:32 ` John DeSoi <desoi@pgedit.com>
2009-12-20 19:11 ` Raimon Fernandez <coder@montx.com>
2009-12-21 01:30 ` John DeSoi <desoi@pgedit.com>
2009-12-22 16:09 ` Raimon Fernandez <coder@montx.com>
2009-12-22 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-12-22 18:56 ` Raimon Fernandez <coder@montx.com>
2009-12-23 09:24 ` Raimon Fernandez <coder@montx.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox