Обсуждение: Locking question

Hi,

given a query like this:

select *
  from account a
 cross join lateral (
       select rate
         from exchange
        where target='USD'
          and source=a.currency
        order by date desc
        limit 1) e
 where a.id=19
   for update;

If I understand the documentation correctly, both rows, the one from
exchange and the one from account are locked, right?

In fact, I have tried it. This query blocks (currency is 'AUD' for
account #19):

select *
  from exchange
 where target='USD'
   and source='AUD'
 order by date desc
 limit 1
   for update;

However, if I create a SQL function like this:

CREATE OR REPLACE FUNCTION
exchangetousd_rate(
    cur CHAR(3),
    tm TIMESTAMP DEFAULT now()
) RETURNS TABLE(rate NUMERIC)
AS $def$

    SELECT rate
      FROM exchange
     WHERE source = $1
       AND target = 'USD'
       AND date <= $2::TIMESTAMP
  ORDER BY date DESC
     LIMIT 1

$def$ LANGUAGE sql STABLE;

and use it here:

select *
  from account a
 cross join exchangeToUSD_rate(a.currency) e
 where a.id=19
   for update;

Then the 2nd query above does not block. So, the row from the exchange
table is not locked.

Is that documented somewhere? Can I rely on it?

The plan for the last query tells me the function call is inlined. So,
in principle it's not different from the first one.

Thanks,
Torsten

=?UTF-8?B?VG9yc3RlbiBGw7ZydHNjaA==?= <torsten.foertsch@gmx.net> writes:
> given a query like this:

> select *
>   from account a
>  cross join lateral (
>        select rate
>          from exchange
>         where target='USD'
>           and source=a.currency
>         order by date desc
>         limit 1) e
>  where a.id=19
>    for update;

> If I understand the documentation correctly, both rows, the one from
> exchange and the one from account are locked, right?

A look at the plan for this suggests that all rows returned by the
sub-select will end up row-locked (whether or not they actually join
to "a").  Note the LockRows node in the sub-select.

> However, if I create a SQL function like this: [ no locking happens ]

FOR UPDATE locking doesn't propagate into functions.  For a moment
I felt like this was a planner bug, but really it isn't: the locking
would certainly not have propagated into a non-inlined function, so
if the planner were to make it happen when inlining, that would make
inlining change the semantics, which it should not.

            regards, tom lane