Version 4.1.0 call powa_delete_and_purge_server function fails with powa_extensions ..is still referenced from table

[banlex73]

Hello
When trying
select powa_delete_and_purge_server (9)
getting error:
powa_extensions" Detail: Key (id)=(9) is still referenced from table "powa_extensions". Where: SQL statement "DELETE FROM public.powa_servers WHERE id = _srvid" PL/pgSQL function powa_delete_and_purge_server(integer) line 9 at SQL statement

But was able to fix it running:
ALTER TABLE public.powa_extensions drop constraint IF EXISTS powa_extensions_srvid_fkey; ALTER TABLE public.powa_extensions ADD CONSTRAINT powa_extensions_srvid_fkey FOREIGN KEY (srvid) REFERENCES powa_servers(id) on DELETE CASCADE;

[rjuju]

What is the content of powa_delete_and_purge_server ?

[banlex73]

Here's what I have: *CREATE* *OR* *REPLACE* *FUNCTION* public.powa_delete_and_purge_server(_srvid *integer*) *RETURNS* *boolean* *LANGUAGE* plpgsql *AS* *$function$* *DECLARE* v_rowcount *bigint*; *BEGIN* *IF* (_srvid = 0) *THEN* *RAISE* *EXCEPTION* 'Local server cannot be deleted'; *END* *IF*; *DELETE* *FROM* public.powa_servers *WHERE* id = _srvid; *GET* *DIAGNOSTICS* v_rowcount = ROW_COUNT;

…

-- pg_track_settings is an autonomous extension, so it doesn't have a FK to

-- powa_servers. It therefore needs to be processed manually *SELECT* *COUNT*(*) *FROM* pg_extension *WHERE* extname = 'pg_track_settings' *INTO* v_rowcount; *IF* (v_rowcount = 1) *THEN* *DELETE* *FROM* pg_track_settings_list *WHERE* srvid = _srvid; *DELETE* *FROM* pg_track_settings_history *WHERE* srvid = _srvid; *DELETE* *FROM* pg_track_db_role_settings_list *WHERE* srvid = _srvid; *DELETE* *FROM* pg_track_db_role_settings_history *WHERE* srvid = _srvid; *DELETE* *FROM* pg_reboot *WHERE* srvid = _srvid; *END* *IF*; *RETURN* v_rowcount = 1; *END*; *$function$* ; нд, 13 груд. 2020 о 18:23 Julien Rouhaud <notifications@github.com> пише:

What is the content of powa_delete_and_purge_server ? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#120 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYCKH3M373GCNVZFBHDSUVZKFANCNFSM4UZ7LFYQ> .

[rjuju]

I see, thanks. Would you like me to push a function to remove all data from a specific server in pg_track_settings so you don't have to maintain it yourself? I'm also wondering if we should add a trigger on DELETE on powa_server, to automatically delete data from pg_track_settings if installed. The idea is that you shouldn't need a specific function to clean up a server, just remove the row. It'll still cause problems if you add additional extensions, but that can also be fixed if needed.

And indeed I forgot the ON UPDATE / ON DELETE cascade clause, ouch. I'll push a fix shortly.

[banlex73]

I think the best option, at least as I see it - *ON UPDATE / ON DELETE cascade clause* PS: I am actively testing 4.1.0 going to deploy it on TST on Monday and later on PRD too нд, 13 груд. 2020 о 19:06 Julien Rouhaud <notifications@github.com> пише:

…

I see, thanks. Would you like me to push a function to remove all data from a specific server in pg_track_settings so you don't have to maintain it yourself? I'm also wondering if we should add a trigger on DELETE on powa_server, to automatically delete data from pg_track_settings if installed. The idea is that you shouldn't need a specific function to clean up a server, just remove the row. It'll still cause problems if you add additional extensions, but that can also be fixed if needed. And indeed I forgot the ON UPDATE / ON DELETE cascade clause, ouch. I'll push a fix shortly. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#120 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYC5LXSC5OQDV4PHK4DSUV6LTANCNFSM4UZ7LFYQ> .

[rjuju]

PS: I am actively testing 4.1.0 going to deploy it on TST on Monday and
later on PRD too

Thanks a lot!

Also, I just pushed powa-team/powa-archivist@59bcbc6 which should fix the issue once version 4.1.2 is released.

[banlex73]

Great! нд, 13 груд. 2020 о 19:43 Julien Rouhaud <notifications@github.com> пише:

…

PS: I am actively testing 4.1.0 going to deploy it on TST on Monday and later on PRD too Thanks a lot! Also, I just pushed ***@***.*** <powa-team/powa-archivist@59bcbc6> which should fix the issue once version 4.1.2 is released. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#120 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYFMIDZLYF5FAEH6PA3SUWCVDANCNFSM4UZ7LFYQ> .