Re: [Postgres-xc-developers] prepare statement bug

[Mason S. <mas...@en...>]

On 12/3/10 7:10 PM, Andrei.Martsinchyk wrote:
> Mason,
>
> 2010/12/3 Mason Sharp<mas...@en...>:
>    
>>
>>
>> Sent from my IPhone
>>
>> On Dec 3, 2010, at 5:31 PM, "Andrei.Martsinchyk"<and...@en...>  wrote:
>>
>>      
>>> Mason,
>>>
>>> 2010/12/3 Mason Sharp<mas...@en...>:
>>>        
>>>> On 12/1/10 1:53 PM, Andrei.Martsinchyk wrote:
>>>>          
>>>>> Hi Benny,
>>>>>
>>>>> Thanks for pointing this out. I tested with a program using Postgres C
>>>>> library and extended query protocol.
>>>>> For you and anyone else who want to test I am attaching the test
>>>>> program and simple Makefile.
>>>>> I fixed the segmentation fault (updated patch is attached), but
>>>>> anyway, PREPARE / EXECUTE commands do not work properly.
>>>>>
>>>>>            
>>>> It looks like it is still not quite right:
>>>>
>>>>
>>>> mds1=# create table mytab (col1 int, col2 int);
>>>> CREATE TABLE
>>>> mds1=# prepare p (int, int) AS INSERT INTO mytab VALUES ($1, $2);
>>>> PREPARE
>>>> mds1=# execute p (1,2);
>>>> INSERT 0 1
>>>> mds1=# select * from mytab;
>>>>   col1 | col2
>>>> ------+------
>>>> (0 rows)
>>>>
>>>> It does not find the row that should have been inserted.
>>>>
>>>>          
>>> Yes, I mentioned the PREPARE command does not work properly.
>>> In this particular case it is inserting row into Coordinator database
>>> and not visible for select.
>>>
>>>        
>> Oh. So, only SELECT is currently handled?
>>
>>      
> Some SELECTs work properly, not all.
>    

Only single-step ones? That is fine. Are any other SELECT statements 
problematic?

How about UPDATE and DELETE? I just ran a simple UPDATE, and it failed, too.

>    
>>>> Also, one other question- the session seems to retain the fact that there
>>>> are associated prepared statements. Does that mean that the pooler will not
>>>> put these back in the pool until all are deallocated?
>>>>
>>>>          
>>> Coordinator does not prepare statements on datanodes, so connection
>>> can be released at the transaction end.
>>>        
>> It converts it into a simple statement? I think we need to support prepare and execute on the data nodes for performance.
>>
>>      
> It does not seem straightforward. Prepared statement is a cached plan,
> and it is cached on coordinator. If the plan contains multiple
> RemoteQuery nodes we should prepare each, and should not release these
> until all they are closed. We should be holding the data node
> connections all this time.
>    

If it is just a matter of holding on to the connections, that is fine, 
we can persist those for the duration of the session. Do you need the 
RemoteQuery nodes to persist, too, or just the connections? I understand 
that we may have to track which connections a statement has already been 
prepared on, and which ones it has not yet. At EXECUTE time, if a data 
node has not been prepared yet, we send down the prepare message first.

>    
>>>        
>>>> On a related note, for the WITH HOLD cursors you implemented, did you also
>>>> do something to hold on to the connections?
>>>>
>>>>          
>>> I did not implement WITH HOLD.
>>>        
>> Let me retest this, and look at old emails later when back at my laptop.
>>
>>      
> I remember someone told me it works, but I never tested, and never did
> anything to handle it.
>
>    
I tried it out:

mds1=# begin;
BEGIN
mds1=#  declare c cursor with hold for select * from mds1;
DECLARE CURSOR
mds1=# fetch c;
  col1 | col2
------+------
     1 |   10
(1 row)

mds1=# commit;
COMMIT
mds1=# fetch c;
  col1 | col2
------+------
     3 |   30
(1 row)


This worries me a bit that it got "fixed" unintentionally.

We may have gotten lucky in that we refetched the same connection(s) 
from the pooler. Meanwhile, the Coordinator still knows about cursor c, 
so it did not object.  It may be that the only thing we need to do is, 
if we have any open hold cursors, we do not return the connections to 
the pool but persist them.

I also expect we would add to this over time, like, if the user created 
any temp tables (and has not dropped them), persist the connections 
(similar to GridSQL).

Thanks,

Mason


>> Mason
>>      
>>>        
>>>> Thanks,
>>>>
>>>> Mason
>>>>
>>>>
>>>>
>>>>
>>>> Mason Sharp
>>>> EnterpriseDB Corporation
>>>> The Enterprise Postgres Company
>>>>
>>>>
>>>> This e-mail message (and any attachment) is intended for the use of
>>>> the individual or entity to whom it is addressed. This message
>>>> contains information from EnterpriseDB Corporation that may be
>>>> privileged, confidential, or exempt from disclosure under applicable
>>>> law. If you are not the intended recipient or authorized to receive
>>>> this for the intended recipient, any use, dissemination, distribution,
>>>> retention, archiving, or copying of this communication is strictly
>>>> prohibited. If you have received this e-mail in error, please notify
>>>> the sender immediately by reply e-mail and delete this message.
>>>>
>>>>
>>>>          
>>>
>>>
>>> --
>>> Andrei Martsinchyk
>>>
>>> EntepriseDB Corporation
>>> The Enterprise Postgres Company
>>>
>>> Website: www.enterprisedb.com
>>> EnterpriseDB Blog: http://blogs.enterprisedb.com/
>>> Follow us on Twitter: http://www.twitter.com/enterprisedb
>>>
>>> This e-mail message (and any attachment) is intended for the use of
>>> the individual or entity to whom it is addressed. This message
>>> contains information from EnterpriseDB Corporation that may be
>>> privileged, confidential, or exempt from disclosure under applicable
>>> law. If you are not the intended recipient or authorized to receive
>>> this for the intended recipient, any use, dissemination, distribution,
>>> retention, archiving, or copying of this communication is strictly
>>> prohibited. If you have received this e-mail in error, please notify
>>> the sender immediately by reply e-mail and delete this message.
>>>        
>>      
>
>
>    


-- 
Mason Sharp
EnterpriseDB Corporation
The Enterprise Postgres Company


This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.