<div>Hello, everyone!</div><div><br></div><div>I've added new features and improvements to pgpool (for parallel mode) which I would like to contribute as well as get feedback from the community.</div><div>Features:</div>
<div> - Transaction support added (for SELECT * FROM distributed_table);</div><div> - CURSORS support added (for distributed tables);</div><div> - Dynamic connections to System DB (optional);</div><div> - SELECT rewriting fixes (some improvements and some bugfixes).</div>
<div> </div><div>1) Transactions.</div><div>When client sends 'BEGIN' statement, pgpool child connects to another child and resend all client statements to it, except for 'SELECT ... FROM distributed_table', which executes in usual way. In this case, 'SELECT ... FROM distributed_table' executes inside the same transaction block as well as other statements.</div>
<div>The better way would be to use the same child's connections as it uses for non-distributed SELECTs, but I haven't found the means of doing it.</div><div><br></div><div>2) Cursors.</div><div>For example, we have two nodes and distributed table:</div>
<div> Node 0 Node 1</div><div> </div><div> value value</div><div>------- -------</div><div> 4 3</div><div> 6 1</div><div> 8 7</div><div>
2 9</div><div> 0 5</div><div> </div><div> a. BEGIN;</div><div> b. DECLARE cursor_name CURSOR FOR SELECT value FROM distributed_table ORDER BY value;</div><div> c. MOVE FORWARD 2 IN cursor_name;</div>
<div> d. FETCH FORWARD 3 IN cursor_name:</div><div> </div><div> value</div><div>-------</div><div> 2</div><div> 3</div><div> 4</div><div> </div><div> CURSORS work for FORWARD/BACKWARD ALL/(positive number).</div><div> Cursors work for any type of distribution, e.g. round-robin.</div>
<div> </div><div> 3) Dynamic connections (optional).</div><div> If number of clients is not usually too high, it is not necessary to have connections to System DB for free childs.</div><div> New parameter 'system_db_dynamic_connection' added to config.</div>
<div> If value of parameter is 0, child works as usual.</div><div> If value > 0, child connects to System DB when client connects. Also, child without System DB connections sleeps for 'system_db_dynamic_connection' mcs before accepting new client. So new client will be accepted by child, which already has connections to System DB.</div>
<div> </div><div> 4) SELECT.</div><div> SELECT rewriting logic was changed a little, and now more complex queries work correctly, such as:</div><div> a. SELECT field_1, (SELECT field_2 FROM replicated_table WHERE field_3=S1.field_3) AS field_2 FROM distributed_table S1;</div>
<div> b. (SELECT field_1, (SELECT field_2 FROM replicated_table_1) AS field_2 FROM distributed_table_1 S1) UNION ALL (SELECT field_1, (SELECT field_3 FROM replicated_table_2) AS field_3 FROM distributed_table_2 S2) ORDER BY field_1;</div>
<div> c. Also, 'ORDER BY + LIMIT + OFFSET' optimization added:</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT field FROM distributed_table ORDER BY field LIMIT 10 OFFSET 100;</div><div>
rewrites into:</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT "pool_c$1" AS field FROM dblink('host=localhost.localdomain dbname=dbname port=9999 user=username','SELECT pool_parallel("SELECT distributed_table.time FROM distributed_table ORDER BY field LIMIT 10 + 100")',false) AS pool_t$0("pool_c$1" field_type) ORDER BY "pool_c$1" OFFSET 100 LIMIT 10;</div>
<div> d. some bugfixes.</div><div><br></div><div>If you are interested in, I could explain each change in detail.</div><div><br></div><div>Source code is available at <a href="https://github.com/afedonin/repo-test.git">https://github.com/afedonin/repo-test.git</a></div>
<div><br></div><div>P.S. All changes was made and tested for parallel_mode.</div><div>P.P.S. I don't know pgpool as good as you, so I appreciate any feedback.</div><div> </div><div>Best regards,</div><div>Alexey Fedonin.</div>