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/>
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
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 <tel:%2B33%20%280%294%2078%2095%2005%2080> Fax +33 (0)4 78 95 00 17 <tel:%2B33%20%280%294%2078%2095%2000%2017> 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 -- 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/>
participants (2)
-
Brian Hood -
Sébastien RAILLARD (PASSMAN)