Hi Brian,
Thank you for the confirmation !
I agree, dropping and recreating index in production, and especially the primary key, isn’t something I would like to do.
It isn’t too long to recreate all the indexes?
I think we’ll be careful to minimize the update and delete from the tables to limit the fragmentation risk.
Sebastien
From: users-list [mailto:users-list-bounces+sebastien.raillard=passman.fr@monetdb.org] On Behalf Of Brian Hood
Sent: mardi 24 novembre 2015 17:58
To: Communication channel for MonetDB users
Subject: Re: Vacuum limitations
Hi Sebastien,
The way i have done this is DROP MY PRIMARY KEY constraint and all my indices and then vacuum and recreate them.
Don't like the idea of the dropping the constraint in especially a production environment.
sql>\d wifi_ippacket
CREATE TABLE "threatmonitor"."wifi_ippacket" (
"guid" CHAR(36) NOT NULL,
"recv_date" CHARACTER LARGE OBJECT,
"ip_df" VARCHAR(5),
"ip_dst" VARCHAR(15),
"ip_hlen" INTEGER NOT NULL,
"ip_id" INTEGER NOT NULL,
"ip_len" INTEGER NOT NULL,
"ip_mf" VARCHAR(5),
"ip_off" INTEGER NOT NULL,
"ip_proto" INTEGER NOT NULL,
"ip_src" VARCHAR(15),
"ip_sum" CHAR(10),
"ip_tos" INTEGER NOT NULL,
"ip_ttl" INTEGER NOT NULL,
"ip_ver" INTEGER NOT NULL,
CONSTRAINT "wifi_ippacket_guid_pkey" PRIMARY KEY ("guid")
);
CREATE INDEX "index_guid_wifiip" ON "threatmonitor"."wifi_ippacket" ("guid");
CREATE INDEX "index_ip_dst_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_dst");
CREATE INDEX "index_ip_src_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_src");
---
DROP INDEX "index_guid_wifiip";
DROP INDEX "index_ip_dst_wifiip";
DROP INDEX "index_ip_src_wifiip";
ALTER TABLE wifi_ippacket DROP CONSTRAINT wifi_ippacket_guid_pkey;
call sys.vacuum('threatmonitor', 'wifi_ippacket');
ALTER TABLE wifi_ippacket ADD CONSTRAINT wifi_ippacket_guid_pkey PRIMARY KEY (guid);
CREATE INDEX "index_guid_wifiip" ON "threatmonitor"."wifi_ippacket" ("guid");
CREATE INDEX "index_ip_dst_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_dst");
CREATE INDEX "index_ip_src_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_src");
sql>\d wifi_ippacket
CREATE TABLE "threatmonitor"."wifi_ippacket" (
"guid" CHAR(36) NOT NULL,
"recv_date" CHARACTER LARGE OBJECT,
"ip_df" VARCHAR(5),
"ip_dst" VARCHAR(15),
"ip_hlen" INTEGER NOT NULL,
"ip_id" INTEGER NOT NULL,
"ip_len" INTEGER NOT NULL,
"ip_mf" VARCHAR(5),
"ip_off" INTEGER NOT NULL,
"ip_proto" INTEGER NOT NULL,
"ip_src" VARCHAR(15),
"ip_sum" CHAR(10),
"ip_tos" INTEGER NOT NULL,
"ip_ttl" INTEGER NOT NULL,
"ip_ver" INTEGER NOT NULL,
CONSTRAINT "wifi_ippacket_guid_pkey" PRIMARY KEY ("guid")
);
CREATE INDEX "index_guid_wifiip" ON "threatmonitor"."wifi_ippacket" ("guid");
CREATE INDEX "index_ip_dst_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_dst");
CREATE INDEX "index_ip_src_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_src");
On Tue, Nov 24, 2015 at 3:35 PM, Sébastien RAILLARD (PASSMAN) <sebastien.raillard@passman.fr> wrote:
Dear all,
I have tried to call the vacuum function using this command: call
sys.vacuum('my_schema','my_table');
I have a message telling me "vacuum not allowed on tables with indices".
So, if I understand correctly, the table on which vacuum can be done must
not have any index, even a primary key?
Best regards,
Sebastien
--
Parc d'activité Tolstoï
4 rue Edouard Aynard
69100 Villeurbanne
Tél. +33 (0)4 78 95 05 80
Fax +33 (0)4 78 95 00 17
www.passman.fr
www.passman-hotels.com
www.passman-camping.com
www.passman-sante.com
<https://www.facebook.com/PASSMAN-187787814053/>
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list