Variable binding problems in PostgreSQL extension v.4.4.3 plus

[curtis18]

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a simple script for reproducing the error. Binding variable is not working on some queries on 4.4.3 postgresql extension. The 4.4.3+ with latest version of postgresql extension also cannot work properly for binding variables.

   $pg -> prepare("my_query", "INSERT INTO b SELECT * FROM a WHERE a.testid = $1 AND a.logid IN (SELECT logid FROM a EXCEPT SELECT logid FROM b);"); $res = $pg->execute("my_query", array(2));

2. What did you expect to see? Running result correctly

3. What did you see instead? Binding cannot work

4. What version of Swoole are you using (show your php --ri swoole)? Swoole => enabled Author => Swoole Team team@swoole.com Version => 4.4.3 Built => Sep 10 2019 12:30:01 coroutine => enabled epoll => enabled eventfd => enabled signalfd => enabled cpu_affinity => enabled spinlock => enabled rwlock => enabled sockets => enabled openssl => OpenSSL 1.1.1 11 Sep 2018 http2 => enabled pcre => enabled mutex_timedlock => enabled pthread_barrier => enabled futex => enabled mysqlnd => enabled async_redis => enabled

Directive => Local Value => Master Value swoole.enable_coroutine => On => On swoole.enable_library => On => On swoole.enable_preemptive_scheduler => Off => Off swoole.display_errors => On => On swoole.use_shortname => On => On swoole.unixsock_buffer_size => 8388608 => 8388608

5. What is your machine environment used (including version of kernel & php & gcc) ? Linux b27e0f0f8a99 4.15.0-43-generic swoole/swoole-src#46-Ubuntu SMP Thu Dec 6 14:45:28 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

PHP 7.2.19-0ubuntu0.18.04.2 (cli) (built: Aug 12 2019 19:34:28) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies with Zend OPcache v7.2.19-0ubuntu0.18.04.2, Copyright (c) 1999-2018, by Zend Technologies

[yunnian]

let me see

[yunnian]

The bug has been fixed. Please use the latest master code.

https://github.com/swoole/ext-postgresql

[curtis18]

Thank you very much and I modified the line #include into #include <postgresql/libpq-fe.h> on swoole_postgresql_coro.h in order to compile properly.

[yunnian]

Thank you very much and I modified the line #include into #include <postgresql/libpq-fe.h> on swoole_postgresql_coro.h in order to compile properly.

in Mac os right way is #include , Let me think about the optimization of this case.

[curtis18]

I have just created a PR to handle this case. Please check. In addition, it is found that more than 1 variables for binding may not work. e.g. $pg->prepare("my_query", "UPDATE tableA SET status = 0 WHERE id = $1 AND last_id = $2;"); $res = $pg->execute("my_query", array(3, 2));

[yunnian]

I have just created a PR to handle this case. Please check. In addition, it is found that more than 1 variables for binding may not work. e.g. $pg->prepare("my_query", "UPDATE tableA SET status = 0 WHERE id = $1 AND last_id = $2;"); $res = $pg->execute("my_query", array(3, 2));

pr，我已经合了，非常感谢 然后你说的这种情况超过一个变量不work的 我并没有复现，能找到稳定复现的场景吗

[curtis18]

Thanks for your kindly help. I believe I find the reason finally. It should not be related to the number of binding variables. The reason should be caused by the binding variables for many queries in a connection. The numbers of binding variables for different queries in a connection must be equal to each others, or it will have error.

e.g. Case 1 $pg -> prepare("my_query","select from test where id > $1"); $res = $pg->execute("my_query", array(1)); $arr = $pg -> fetchAll($res); / the following query will have error since the first query is assigned 1 binding variable, but query below has 2 binding variables/ $pg -> prepare("my_query","select from test where id > $1 and id < $2"); $res = $pg->execute("my_query", array(1,3)); $arr = $pg -> fetchAll($res);

e.g. Case 2 $pg -> prepare("my_query","select from test where id > $1 and id < $2"); $res = $pg->execute("my_query", array(1,3)); $arr = $pg -> fetchAll($res); / the following query will have error since the first query is assigned 2 binding variables, but query below has 1 binding variable/ $pg -> prepare("my_query","select from test where id > $1"); $res = $pg->execute("my_query", array(1)); $arr = $pg -> fetchAll($res);

e.g. Case 3 (OK, working) $pg -> prepare("my_query","select from test where id > $1 and id < $2"); $res = $pg->execute("my_query", array(1,3)); $arr = $pg -> fetchAll($res); / the following query will be fine as both queries have 2 binding variables / $pg -> prepare("my_query","select from test where id > $1 and id < $2"); $res = $pg->execute("my_query", array(2,4)); $arr = $pg -> fetchAll($res);

[yunnian]

不能在一段程序里 使用两次相同的query名字，php官方也是这样，你可以使用不一样的参数比如my_query1、my_query2、my_query3 等。

You can't use the same query name twice in a program, PHP's official is the same too, You can use different parameters such as my_query1, my_query2, my_query3, etc.