From: | Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Locking question |
Date: | 2015-02-26 11:23:03 |
Message-ID: | 54EF0217.6060802@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Smith | 2015-02-26 11:55:20 | "JSON does not support infinite date values" |
Previous Message | Andres Freund | 2015-02-26 11:12:35 | Re: [HACKERS] BDR Multiple database |