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
