Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.
От | Amit Langote |
---|---|
Тема | Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table. |
Дата | |
Msg-id | CA+HiwqHbPJ0PPBsK55g0yAt34BxWVa6Yi1AUB9UsM9AoLCPNmQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table. (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>) |
Ответы |
Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table.
(Alvaro Herrera <alvherre@2ndquadrant.com>)
Re: Getting ERROR with FOR UPDATE/SHARE for partitioned table. (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>) |
Список | pgsql-hackers |
On Thu, May 28, 2020 at 11:08 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote: > On Wed, May 27, 2020 at 6:51 PM Amit Langote <amitlangote09@gmail.com> wrote: > > So in Rajkumar's example, the cursor is declared as: > > > > CURSOR IS SELECT * FROM tbl WHERE c1< 5 FOR UPDATE; > > > > and the WHERE CURRENT OF query is this: > > > > UPDATE tbl SET c2='aa' WHERE CURRENT OF cur; > > Thanks for the clarification. So it looks like we expand UPDATE on > partitioned table to UPDATE on each partition (inheritance_planner for > DML) and then execute each of those. If CURRENT OF were to save the > table oid or something we could run the UPDATE only on that partition. Are you saying that the planner should take into account the state of the cursor specified in WHERE CURRENT OF to determine which of the tables to scan for the UPDATE? Note that neither partition pruning nor constraint exclusion know that CurrentOfExpr can possibly allow to exclude children of the UPDATE target. > I am possibly shooting in dark, but this puzzles me. And it looks like > we can cause wrong rows to be updated in non-partition inheritance > where the ctids match? I don't think that hazard exists, because the table OID is matched before the TID. Consider this example: drop table if exists p cascade; create table p (a int); create table c (check (a = 2)) inherits (p); insert into p values (1); insert into c values (2); begin; declare c cursor for select * from p; fetch c; update p set a = a where current of c; QUERY PLAN ------------------------------------------------------------ Update on p (cost=0.00..8.02 rows=2 width=10) Update on p Update on c p_1 -> Tid Scan on p (cost=0.00..4.01 rows=1 width=10) TID Cond: CURRENT OF c -> Tid Scan on c p_1 (cost=0.00..4.01 rows=1 width=10) TID Cond: CURRENT OF c (7 rows) Whenever the TID scan evaluates the CURRENT OF qual, it passes the table being scanned to execCurrentOf(). execCurrentOf() then fetches the ExecRowMark or the ScanState for *that* table from the cursor's ("c") PlanState via its portal. Only if it confirms that such a ExecRowMark or a ScanState exists and is valid/active that it returns the TID found therein as the cursor's current TID. -- Amit Langote EnterpriseDB: http://www.enterprisedb.com
В списке pgsql-hackers по дате отправления: