From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: Drouvot, Bertrand <bdrouvot@amazon.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Jeremy Schneider <schnjere@amazon.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Imseih (AWS), Sami <simseih@amazon.com>
Subject: Re: Query Jumbling for CALL and SET utility statements
Date: Fri, 16 Sep 2022 21:53:36 +0900
Message-ID: <37d32e91-4a08-afaf-a3a8-fd0578e4db50@oss.nttdata.com> (raw)
In-Reply-To: <a0d66747-3f8c-5346-5baa-61299e2cb4da@amazon.com>
References: <46ca04f9-1e37-c862-9a28-c5dffaee412f@amazon.com>
<20220831190604.l2luuhj3xkau7dvk@awork3.anarazel.de>
<e43a0e02-4ef4-d510-a00a-cf39d2a9df19@amazon.com>
<cbd28368-a5ae-f141-4c3f-28ba9f608ba0@amazon.com>
<BBB9A13D-BBE2-44A4-9CA7-6E189120E5A2@amazon.com>
<09c04aba-7f12-e524-20f7-3f92c1479fdf@amazon.com>
<9aee54f2-5cc1-5b64-1f73-6961932e7bd2@amazon.com>
<Yxl8R8mAUG2vqAcC@paquier.xyz>
<20220908065012.l3bd2np7wj52cxco@jrouhaud>
<c65f1c67-bae4-9658-9206-f11e9c6f7d11@amazon.com>
<20220908112919.2ytxpkitiw6lt2u6@jrouhaud>
<a16d303f-39ad-319c-18ff-85b78b106fe1@amazon.com>
<a0d66747-3f8c-5346-5baa-61299e2cb4da@amazon.com>
On 2022/09/09 19:11, Drouvot, Bertrand wrote:
>>> IME if your application relies on 2PC it's very likely that you will hit the
>>> exact same problems described in your original email.
The utility commands for cursor like DECLARE CURSOR seem to have the same issue
and can cause lots of pgss entries. For example, when we use postgres_fdw and
execute "SELECT * FROM <foreign table> WHERE id = 10" five times in the same
transaction, the following commands are executed in the remote PostgreSQL server
and recorded as pgss entries there.
DECLARE c1 CURSOR FOR ...
DECLARE c2 CURSOR FOR ...
DECLARE c3 CURSOR FOR ...
DECLARE c4 CURSOR FOR ...
DECLARE c5 CURSOR FOR ...
FETCH 100 FROM c1
FETCH 100 FROM c2
FETCH 100 FROM c3
FETCH 100 FROM c4
FETCH 100 FROM c5
CLOSE c1
CLOSE c2
CLOSE c3
CLOSE c4
CLOSE c5
Furthermore, if the different query on foreign table is executed in the next
transaction, it may reuse the same cursor name previously used by another query.
That is, different queries can cause the same FETCH command like
"FETCH 100 FROM c1". This would be also an issue.
I'm not sure if the patch should also handle cursor cases. We can implement
that separately later if necessary.
I don't think that the patch should include the fix for cursor cases. It can be implemented separately later if necessary.
> Attached v5 to normalize 2PC commands too, so that we get things like:
+ case T_VariableSetStmt:
+ {
+ VariableSetStmt *stmt = (VariableSetStmt *) node;
+
+ /* stmt->name is NULL for RESET ALL */
+ if (stmt->name)
+ {
+ APP_JUMB_STRING(stmt->name);
+ JumbleExpr(jstate, (Node *) stmt->args);
With the patch, "SET ... TO DEFAULT" and "RESET ..." are counted as the same query.
Is this intentional? Which might be ok because their behavior is basically the same.
But I'm afaid which may cause users to be confused. For example, they may fail to
find the pgss entry for RESET command they ran and just wonder why the command was
not recorded. To avoid such confusion, how about appending stmt->kind to the jumble?
Thought?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: masao.fujii@oss.nttdata.com, bdrouvot@amazon.com, rjuju123@gmail.com, michael@paquier.xyz, schnjere@amazon.com, pgsql-hackers@lists.postgresql.org, pavel.stehule@gmail.com, andres@anarazel.de, simseih@amazon.com
Subject: Re: Query Jumbling for CALL and SET utility statements
In-Reply-To: <37d32e91-4a08-afaf-a3a8-fd0578e4db50@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox