Re: [GENERAL] Extended Query, flush or sync ?
On 22/12/2009, at 18:15, Tom Lane wrote: Raimon Fernandez co...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
Raimon Fernandez co...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
On 22/12/2009, at 18:15, Tom Lane wrote: Raimon Fernandez co...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
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 xxx 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
On 18/12/2009, at 22:55, Tom Lane wrote: Raimon Fernandez co...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
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 xxx 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
Raimon Fernandez co...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended Query, flush or sync ?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extended Query, flush or sync ?
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 xxx does not exist thanks, regards, r. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
