[pgpool-hackers: 4329] Re: minimal parser and multi-statement query

[Tatsuo Ishii]

> Hi Ishii San,
> 
> The patch looks good, and I believe the approach covers the performance
> concerns.

Thanks for review. I have pushed the patch to from 4.4 to 4.1 (master
has already this fix). 4.1 is the version to introduce the minimal
parser.

Note that 4.1 needed more commit:
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=fd32f5ef996cad36d5b1554e92a33ea7a815419a

which has been already pushed to from master to 4.2. The reason why
4.1 did not have that is, it was just hard to solve the conflict at
that time. Since without this commit, the psqlscan patch does not work
effectively.

Attached is a test script from master branch's 001.load_balance
test. Note that in order to run the test, you need to set the load
balance weight of node 0 to 0. After the commit, from master to 4.1
all pass the test.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

> Thanks
> Best regards
> Muhammad Usama
> 
> 
> On Sat, Apr 15, 2023 at 5:06 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> >> On 14-Apr-2023, at 5:37 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >>
>> >> Hi Usama,
>> >>
>> >>> Thanks for conducting the tests.
>> >>
>> >> No prolem.
>> >>
>> >>> 3-4 % seems quite a significant degradation. So one possible way would
>> bet
>> >>> retire the
>> >>> minimal parser and always use the standard parser so that we can save
>> the
>> >>> extra scanning
>> >>> needed for the detection of multi-statement queries.
>> >>>
>> >>> It (removing minimal parser altogether) may be less of a performance
>> >>> overhead than this. What is your opinion?
>> >>
>> >> Is it possible to run the extra scanning only for lengthy queries(say,
>> >> longer than 10k)? i.e.
>> >>
>> >> If (lengthy query)
>> >>  extra scanning;
>> >>  if (single-statement)
>> >>    minimal_parser = true;
>> >>  else
>> >>    minimal_parser = false;
>> >> else
>> >>  minimal_parser = false;
>> >> fi
>> >> raw_parser (minimal_parser);
>> >>
>> >> With this, we can avoid the extra scanning for most queries. I think
>> >> the minimal parser is best for lengthy queries.
>> >>
>> >
>> > Agreed, I guess that makes total sense
>>
>> Attached is a patch for this direction. What do you think?
>>
>> Best reagards,
>> --
>> Tatsuo Ishii
>> SRA OSS LLC
>> English:
>> https://url.avanan.click/v2/___http://www.sraoss.co.jp/index_en/___.YXAzOnBlcmNvbmE6YTpnOjNmMjQwMWRlZTIzYjlkODcyYWYyNTNkNWQ0ZmNjMjg0OjY6M2ZiYjo0ZDAzZjNiODFhNDhiMTA0MTdiMjBmOGJlOTA5YjBmZDEyYmZmNjM4YzRlYjQ5MWM3NDY3YzhhNjhiZDY3NDViOnA6VA
>> Japanese:
>> https://url.avanan.click/v2/___http://www.sraoss.co.jp___.YXAzOnBlcmNvbmE6YTpnOjNmMjQwMWRlZTIzYjlkODcyYWYyNTNkNWQ0ZmNjMjg0OjY6YjQ4ZjphZDYzYTEyODA1YTNmMTZkOTAwZDAyNWFhODZkY2EyNzVlZTc3NTFlZDQyNmIxYTYyMjE3MDA5N2I0NTA4MDFmOnA6VA
>>
-------------- next part --------------
\set SHOW_ALL_RESULTS off
-- ordinary read only SELECT: load balance expected
SELECT 1;
-- multi-statement query including BEGIN
BEGIN\;SELECT 1;
-- ordinary read only SELECT: load balance expected
SELECT 2;
-- tx started by multi-statement query ends
END;
-- multi-statement query including BEGIN
BEGIN\;SELECT 1;
-- SAVEPOINT
SAVEPOINT a;
-- PREPARE
PREPARE foo AS SELECT 2;
-- EXECUTE
EXECUTE foo;
-- DEALLOCATE
DEALLOCATE foo;
-- ROLLBACK TO
ROLLBACK TO a;
-- tx started by multi-statement query ends
END;
-- multi-statement query including BEGIN and INSERT
DROP TABLE t1;
CREATE TABLE t1(i INT);
BEGIN\;INSERT INTO t1 VALUES(1);
-- SAVEPOINT
SAVEPOINT a;
-- PREPARE
PREPARE foo AS SELECT 2;
-- EXECUTE
EXECUTE foo;
-- DEALLOCATE
DEALLOCATE foo;
-- ROLLBACK TO
ROLLBACK TO a;
-- tx started by multi-statement query ends
END;
-- multi-statement query including BEGIN and ROLLBACK
BEGIN\;SELECT 1\;ROLLBACK;
-- ordinary read only SELECT: load balance expected
SELECT 1;
-- multi-statement query including BEGIN and invalid query
BEGIN\;SELECT 1\;FOO;
-- ordinary read only SELECT: load balance expected
SELECT 1;