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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general