DEFINITIONS

Definitions More Info.
Definition ID4.030
TitlePOSTGRESQL
CategoryNOTES
DefinitionDELETE fonksiyonu function icin Postgresql otomatik crontab olustuma sirasi
Definition Description-- Once DB icinde DELETE FONKSIYONUNU OLUSTUR

-- Parametresiz, ASlinda Default parametre degeri ile,
------------------------------------------------------------
CREATE OR REPLACE FUNCTION public."DeleteFunctionApplicationlogNoParam"(
)
RETURNS integer
LANGUAGE 'plpgsql'

COST 100
VOLATILE
AS $BODY$
DECLARE i INTEGER;
BEGIN i := 0;
WHILE( i <=3 ) loop
DELETE FROM "public"."ApplicationLog"
Where "Id" in (SELECT "Id"
FROM "public"."ApplicationLog"
Where "Date"<=(CURRENT_TIMESTAMP - INTERVAL '30 day')
limit 10000);
i = i + 1;
END loop;
RETURN i;
END;
$BODY$;

ALTER FUNCTION public."DeleteFunctionApplicationlogNoParam"()
OWNER TO postgres;

-- Parametreli, Parametre Degerlerini Sen kendin belirle, Bir turda (s parametresi kac tur), kac kayit silecel (J parametresi)
------------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public."DeleteFunctionApplicationlogTwoParam"(
s integer,
j integer)
RETURNS integer
LANGUAGE 'plpgsql'

COST 100
VOLATILE
AS $BODY$
DECLARE i INTEGER;
BEGIN i := 0;
WHILE( i <=s ) loop
DELETE FROM "public"."ApplicationLog"
Where "Id" in (SELECT "Id"
FROM "public"."ApplicationLog"
Where "Date"<=(CURRENT_TIMESTAMP - INTERVAL '30 day')
limit j);
i = i + 1;
END loop;
RETURN i;
END;
$BODY$;

ALTER FUNCTION public."DeleteFunctionApplicationlogTwoParam"(integer, integer)
OWNER TO postgres;

-- Bu fonksiyonlari calistirmak icin, bir script dosyasi olustur.
-------------------------------------------------------------------
touch /storage/pgscripts/DeleteLogTables.sh

-- icine silme komutlarini yaz
----------------------------------------------------------------------------------
psql -U postgres -d BlueCollarPushNotificationLog -c 'SELECT "PushNotification"."DeletePushNotificationLogWithTwoParameter"(10,10000);'
psql -U postgres -d BlueCollarLog -c 'SELECT "public"."DeleteFunctionApplicationlogTwoParam"(10,10000);'
psql -U postgres -d BlueCollarLog -c 'SELECT "public"."DeleteFunctionSmsBlackListServiceLogTwoParam"(10,10000);'

-- POSTGRESQL KULLANCİSİ ile crontab olustur.
---------------------------------------------------------------------------------
crontab -e

# Her 45 dakikada calisacak
*/45 * * * * /storage/pgscripts/DeleteLogTables.sh

-- CRONTAB service restart et. ROOT kullancisina geri don, islemi oyle yap
--------------------------------------------------------------------------
service crond stop
service crond start

RecordBycunay
Record Date07-09-2020 15:27:15
Düzenle
Kopyala
Sil