Re: Built-in connection pooler

Hi, Konstantin

I test the patch-16 on postgresql master branch, and I find the
temporary table
cannot removed when we re-connect to it. Here is my test:

japin(at)ww-it:~/WwIT/postgresql/Debug/connpool$ initdb
The files belonging to this database system will be owned by user "japin".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/japin/WwIT/postgresql/Debug/connpool/DATA ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /home/japin/WwIT/postgresql/Debug/connpool/DATA -l
logfile start

japin(at)ww-it:~/WwIT/postgresql/Debug/connpool$ pg_ctl -l /tmp/log start
waiting for server to start.... done
server started
japin(at)ww-it:~/WwIT/postgresql/Debug/connpool$ psql postgres
psql (13devel)
Type "help" for help.

postgres=# ALTER SYSTEM SET connection_proxies TO 1;
ALTER SYSTEM
postgres=# ALTER SYSTEM SET session_pool_size TO 1;
ALTER SYSTEM
postgres=# \q
japin(at)ww-it:~/WwIT/postgresql/Debug/connpool$ pg_ctl -l /tmp/log restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
japin(at)ww-it:~/WwIT/postgresql/Debug/connpool$ psql -p 6543 postgres
psql (13devel)
Type "help" for help.

postgres=# CREATE TEMP TABLE test(id int, info text);
CREATE TABLE
postgres=# INSERT INTO test SELECT id, md5(id::text) FROM
generate_series(1, 10) id;
INSERT 0 10
postgres=# select * from pg_pooler_state();
 pid  | n_clients | n_ssl_clients | n_pools | n_backends |
n_dedicated_backends | n_idle_backends | n_idle_clients | tx_bytes |
rx_bytes | n_transactions
------+-----------+---------------+---------+------------+----------------------+-----------------+----------------+----------+----------+----------------
 3885 |         1 |             0 |       1 |          1
|                    0 |               0 |              0 | 1154 |    
2880 |              6
(1 row)

postgres=# \q
japin(at)ww-it:~/WwIT/postgresql/Debug/connpool$ psql -p 6543 postgres
psql (13devel)
Type "help" for help.

postgres=# \d
        List of relations
  Schema   | Name | Type  | Owner
-----------+------+-------+-------
 pg_temp_3 | test | table | japin
(1 row)

postgres=# select * from pg_pooler_state();
 pid  | n_clients | n_ssl_clients | n_pools | n_backends |
n_dedicated_backends | n_idle_backends | n_idle_clients | tx_bytes |
rx_bytes | n_transactions
------+-----------+---------------+---------+------------+----------------------+-----------------+----------------+----------+----------+----------------
 3885 |         1 |             0 |       1 |          1
|                    0 |               0 |              0 | 2088 |    
3621 |              8
(1 row)

postgres=# select * from test ;
 id |               info
----+----------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b
  2 | c81e728d9d4c2f636f067f89cc14862c
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
  4 | a87ff679a2f3e71d9181a67b7542122c
  5 | e4da3b7fbbce2345d7772b0674a318d5
  6 | 1679091c5a880faf6fb5e6087eb1b2dc
  7 | 8f14e45fceea167a5a36dedd4bea2543
  8 | c9f0f895fb98ab9159f51fd0297e236d
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
 10 | d3d9446802a44259755d38e6d163e820
(10 rows)

I inspect the code, and find the following code in DefineRelation function:

if (stmt->relation->relpersistence != RELPERSISTENCE_TEMP
        && stmt->oncommit != ONCOMMIT_DROP)
        MyProc->is_tainted = true;

For temporary table, MyProc->is_tainted might be true, I changed it as
following:

if (stmt->relation->relpersistence == RELPERSISTENCE_TEMP
        || stmt->oncommit == ONCOMMIT_DROP)
        MyProc->is_tainted = true;

For temporary table, it works. I not sure the changes is right.

On 8/2/19 7:05 PM, Konstantin Knizhnik wrote:
>
>
> On 02.08.2019 12:57, DEV_OPS wrote:
>> Hello Konstantin
>>
>>
>> would you please re-base this patch? I'm going to test it, and back port
>> into PG10 stable and PG9 stable
>>
>>
>> thank you very much
>>
>>
>
> Thank you.
> Rebased patch is attached.
>
>