COMMIT 在一個 plpgsql 程式碼塊中工作，但在另一個？

每隔一段時間，我們就需要根據一些複雜的計算回填一堆數據。因為我們有很多數據，所以我們需要確保我們不會長時間鎖定一個表，並分塊進行更新。為此，我們有這種類型的腳本：

DO $$
DECLARE
   affected_count integer;
   start_time timestamp;
   end_time timestamp;
   elapsed_interval interval;
BEGIN
 LOOP    
   select now() into start_time;

   -- Add code here, make sure it limit it, below is an example
   -- Make sure that the query eventually updates 0 rows, 
   -- otherwise this script will run forever
   
   update checklists
   set last_active_date = updated_date
   where id in ( 
     select id 
     from checklists 
     where last_active_date is null
     limit 10000
   );
   
   -- End of code
   
   COMMIT;

   GET DIAGNOSTICS affected_count = ROW_COUNT;
   
   select now() into end_time;
   elapsed_interval := end_time - start_time;
   
   RAISE NOTICE 'Updated % row(s) in %.', affected_count, elapsed_interval;
   IF affected_count = 0 THEN
     EXIT;
   END IF;
 END LOOP;
END; $$

注意COMMIT裡面的腳本。

這種方法效果很好！通常。

最近，我們有一些更複雜的邏輯來驅動這種更新。後者包括交叉​​表的使用。看起來像這樣：

DO $$
DECLARE
   affected_count integer;
   start_time timestamp;
   end_time timestamp;
   elapsed_interval interval;
BEGIN
 LOOP  
   select now() into start_time;
   
insert into permissions_stats(
 id,
 organization_id,
 reference_type,
 folder_id,
 template_id,
 all_members_groups_count,
 members_count,
 groups_count,
 guests_count,
 inherited_all_members_groups_count,
 inherited_members_count,
 inherited_groups_count,
 inherited_guests_count,
 template_shared)
select
 uuid_generate_v4() as id,
 organization_id,
 'Folder' as reference_type,
 folder_id,
 null as template_id,
 sum(all_members_groups_count) as all_members_groups_count,
 sum(members_count) as members_count,
 sum(groups_count) as groups_count,
 sum(guests_count) as guests_count,
 sum(inherited_all_members_groups_count) as inherited_all_members_groups_count,
 sum(inherited_members_count) as inherited_members_count,
 sum(inherited_groups_count) as inherited_groups_count,
 sum(inherited_guests_count) as inherited_guests_count,
 false as template_shared
from (
 select
   folder_id,
   organization_id,
   coalesce(all_members_groups_count, 0) as all_members_groups_count,
   coalesce(members_count, 0) as members_count,
   coalesce(groups_count, 0) as groups_count,
   coalesce(guests_count, 0) as guests_count,
   coalesce(inherited_all_members_groups_count, 0) as inherited_all_members_groups_count,
   coalesce(inherited_members_count, 0) as inherited_members_count,
   coalesce(inherited_groups_count, 0) as inherited_groups_count,
   coalesce(inherited_guests_count, 0) as inherited_guests_count
 from crosstab('
   
with recursive folders_path_to_root(start_id, id, parent_folder_id, organization_id) as (
 select
   f.id as start_id,
   f.id,
   f.parent_folder_id,
   f.organization_id
 from folders f
 where deleted_by_id is null

 union all

 select
   pf.start_id,
   f.id,
   f.parent_folder_id,
   f.organization_id
 from folders_path_to_root pf
   inner join folders f on (f.id = pf.parent_folder_id and f.parent_folder_id is not null)
),
folder_ids as (
   select f.id as id from folders f
   where not exists (select * from permissions_stats where reference_type = ''Folder'' and folder_id = f.id)
   order by f.id asc
   limit 1000
),
counts_cte as (
 select
   rp.start_id as folder_id,
   rp.organization_id as organization_id,
   case
     when u.user_type = ''Standard'' and om.guest then ''inherited_guests_count''
     when u.user_type = ''Group'' and g.group_type = ''Standard'' then ''inherited_groups_count''
     when u.user_type = ''Group'' and g.group_type = ''AllMembers'' then ''inherited_all_members_groups_count''
     else ''inherited_members_count''
   end as permit_type,
   count (u) as count
 from folder_permits fp
   inner join folders_path_to_root rp on (rp.id = fp.folder_id and rp.organization_id = fp.organization_id)
   inner join organization_memberships om on (fp.organization_membership_id = om.id)
   inner join users u on (u.id = om.user_id)
   left join groups g on (g.user_id = u.id and u.user_type = ''Group'')
 where rp.start_id in (select id from folder_ids)
   and rp.id not in (select id from folder_ids)
 group by permit_type, rp.start_id, rp.organization_id
)
select
 folder_id as folder_id,
 organization_id as organization_id,
 permit_type,
 count
from counts_cte


   union all

   
select
 f.id as folder_id,
 f.organization_id as organization_id,
 case
   when u.user_type = ''Standard'' and om.guest then ''guests_count''
   when u.user_type = ''Group'' and g.group_type = ''Standard'' then ''groups_count''
   when u.user_type = ''Group'' and g.group_type = ''AllMembers'' then ''all_members_groups_count''
   else ''members_count''
 end as permit_type,
 count (u) as count
from folders f
 left join folder_permits fp on (fp.folder_id = f.id)
 left join organization_memberships om on (fp.organization_membership_id = om.id)
 left join users u on (u.id = om.user_id and u.user_type in (''Standard'', ''Group''))
 left join groups g on (g.user_id = u.id and u.user_type = ''Group'')
where f.id in (select id from folder_ids)
group by permit_type, f.id, f.organization_id

 ',
 '
   values
     (''all_members_groups_count''),
     (''members_count''),
     (''guests_count''),
     (''groups_count''),
     (''inherited_all_members_groups_count''),
     (''inherited_members_count''),
     (''inherited_groups_count''),
     (''inherited_guests_count'')
 '
 ) as grouped_counts (
   "folder_id" uuid,
   "organization_id" uuid,
   "all_members_groups_count" int,
   "members_count" int,
   "guests_count" int,
   "groups_count" int,
   "inherited_all_members_groups_count" int,
   "inherited_members_count" int,
   "inherited_groups_count" int,
   "inherited_guests_count" int
 )
) as pivot
group by pivot.folder_id, pivot.organization_id
on conflict (reference_type, folder_id) where reference_type = 'Folder'
do update set
 all_members_groups_count = excluded.all_members_groups_count,
 members_count = excluded.members_count,
 groups_count = excluded.groups_count,
 guests_count = excluded.guests_count,
 inherited_all_members_groups_count = excluded.inherited_all_members_groups_count,
 inherited_members_count = excluded.inherited_members_count,
 inherited_groups_count = excluded.inherited_groups_count,
 inherited_guests_count = excluded.inherited_guests_count;

-- End of code
   
   COMMIT;

   GET DIAGNOSTICS affected_count = ROW_COUNT;
   
   select now() into end_time;
   elapsed_interval := end_time - start_time;
   
   RAISE NOTICE 'Updated % row(s) in %.', affected_count, elapsed_interval;
   IF affected_count = 0 THEN
     EXIT;
   END IF;
 END LOOP;
END; $$ 

內部查詢工作得很好。但是，當我執行整個循環時，我收到以下錯誤：

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function inline_code_block line 168 at SQL statement

現在，深入研究文件和一堆其他文章，我發現 plpgsql 匿名函式不支持送出，因為該函式將使用自己的事務執行。這有點道理。但是，為什麼第一個查詢沒有任何問題。

此過程的主要目標是允許以塊的形式插入/更新大量數據，並可選擇在中間停止並從同一點重新啟動。沒有commit它，它每次都會從頭開始。

我錯過了什麼？

我發現commitplpgsql 匿名函式不支持

好。你的發現在歷史上是正確的。（但術語不是。）DO命令執行……

匿名程式碼塊

請注意術語“塊”，而不是“功能”。這是這裡的關鍵，因為FUNCTION仍然不能像COMMITPostgres 13 那樣執行事務控制語句——而且可能永遠不會。但是一個PROCEDURE罐頭，它是用Postgres 11添加的。從相同版本開始，使用DO命令執行的匿名塊也可以做到這一點。

有關的：

• PL/pgSQL 函式會自行自動送出嗎？

您是否使用不同版本的 Postgres 執行了這兩個測試？

引用自：https://dba.stackexchange.com/questions/290931