Strange row locking - question

Vaclav Kulakovsky <vaclav.kulakovsky@definity.cz> writes:

When I update row in table with foring key, referenced row is locked. I
can see it in log file:

DEBUG: query: update entry set sometext = 'aaa' where id = 1;
DEBUG: query: SELECT 1 FROM ONLY "languages" WHERE "id" = $1 FOR UPDATE
OF "languages"

When I execute update second time I receive only this:

query: update entry set sometext = 'aaa' where id = 1;

Referenced row is locked only first time ? This behavior looks me little
bit strange. Can anybody explain it ?

You are not seeing the RI query get *executed*, only get *parsed*.
After the first time through, the RI code re-uses a cached query plan.
Since it's not fed through the parser again, no log message.

plpgsql shows similar behavior, BTW. Some day it might be an idea to
make these things a little more debugging-friendly by having them emit
suitable log messages when they re-execute cached plans.

regards, tom lane

On Thu, 11 Apr 2002, Vaclav Kulakovsky wrote:

Hi all,

When I update row in table with foring key, referenced row is locked. I
can see it in log file:

DEBUG: query: update entry set sometext = 'aaa' where id = 1;
DEBUG: query: SELECT 1 FROM ONLY "languages" WHERE "id" = $1 FOR UPDATE
OF "languages"

When I execute update second time I receive only this:

query: update entry set sometext = 'aaa' where id = 1;

Referenced row is locked only first time ? This behavior looks me little
bit strange. Can anybody explain it ?

I believe this is just the query plan caching. It only plans the query
once for each session on its first use. The query is run from that saved
plan from then on.