Re: Query Jumbling for CALL and SET utility statements - Mailing list pgsql-hackers

From Fujii Masao
Subject Re: Query Jumbling for CALL and SET utility statements
Date
Msg-id 37d32e91-4a08-afaf-a3a8-fd0578e4db50@oss.nttdata.com
Whole thread Raw
In response to Re: Query Jumbling for CALL and SET utility statements  ("Drouvot, Bertrand" <bdrouvot@amazon.com>)
Responses Re: Query Jumbling for CALL and SET utility statements
Re: Query Jumbling for CALL and SET utility statements
List pgsql-hackers

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



pgsql-hackers by date:

Previous
From: Japin Li
Date:
Subject: Re: Error for WITH options on partitioned tables
Next
From: Japin Li
Date:
Subject: Re: Error for WITH options on partitioned tables