Thread: Determine if FOR UPDATE or FOR SHARE was used?

Hi,

Given a Portal, or an _SPI_plan, is there a practical way to tell whether
it came from a query with FOR UPDATE or FOR SHARE?

Regards,
-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> Given a Portal, or an _SPI_plan, is there a practical way to tell whether
> it came from a query with FOR UPDATE or FOR SHARE?

In principle, you could do something like drilling down into the plan
tree to see if there's a LockRows node, but this wouldn't necessarily
be a great idea from a modularity or maintainability standpoint.

I think it would help to take two steps back and ask why you want
to know this, and what exactly is it that you want to know, anyhow.
What does it matter if there's FOR SHARE in the query?  Does it
matter if the FOR SHARE targets only some tables (and do you
care which ones?)  How would your answer change if the FOR SHARE
were buried down in a CTE subquery?  Why are you only interested
in these cases, and not INSERT/UPDATE/DELETE?

            regards, tom lane

On 03/18/19 00:45, Tom Lane wrote:
> I think it would help to take two steps back and ask why you want
> to know this, and what exactly is it that you want to know, anyhow.
> What does it matter if there's FOR SHARE in the query?  Does it

I was looking at an old design decision in PL/Java, which implements
java.sql.ResultSet by grabbing a pile of tuples at a time from
SPI_cursor_fetch, and then letting the ResultSet API iterate through
those, until the next pile needs to be fetched.

It seemed like the kind of optimization probably very important in a
client/server connection over RFC 2549, but I'm not sure how important
it is for code running right in the backend.

Maybe it does save a few cycles, but I don't want to be watching when
somebody tries to do UPDATE or DELETE WHERE CURRENT OF.

It occurred to me that positioned update/delete could be made to work
either by simply having the Java ResultSet row fetch operations correspond
directly to SPI fetches, or by continuing to SPI-fetch multiple rows at
a time, but repositioning with SPI_cursor_move as the Java ResultSet
pointer moves through them. (Is one of those techniques common in other
PLs?)

But it also occurred to me that there might be a practical way to
examine the query to see it's one that could be used for positioned
update or delete at all, and avoid any special treatment if it isn't.

Regards,
-Chap