search

powa-team / powa-web

PoWA user interface
http://powa.readthedocs.io/
73 stars 31 forks source link

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

Closed banlex73 closed 3 years ago

banlex73 commented 3 years ago

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 commented 3 years ago

What is the content of powa_delete_and_purge_server ?

banlex73 commented 3 years ago

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 https://github.com/powa-team/powa-web/issues/120#issuecomment-744125764, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYCKH3M373GCNVZFBHDSUVZKFANCNFSM4UZ7LFYQ .

rjuju commented 3 years ago

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 commented 3 years ago

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 https://github.com/powa-team/powa-web/issues/120#issuecomment-744136740, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYC5LXSC5OQDV4PHK4DSUV6LTANCNFSM4UZ7LFYQ .

rjuju commented 3 years ago

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 https://github.com/powa-team/powa-archivist/commit/59bcbc689a78698c8e41e6ebbaa8c5b4807819db which should fix the issue once version 4.1.2 is released.

banlex73 commented 3 years ago

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 https://github.com/powa-team/powa-archivist/commit/59bcbc689a78698c8e41e6ebbaa8c5b4807819db 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 https://github.com/powa-team/powa-web/issues/120#issuecomment-744146363, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYFMIDZLYF5FAEH6PA3SUWCVDANCNFSM4UZ7LFYQ .