Thread: query creates a huge toast tables
Hi,
This query (postgis) didn't endup after 4 hours....and eat 40gig of disk space
SELECT id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as geom32198_s
into potentialite
FROM province as a, potentialite_tmp as b;
I stopped the execution and cancel request sent appears, but after an hour the request wasn't stopped yet.
So I killed the process and restat postgres.
The database is up and running but I didn't get the 40gig of disk space back. And only 5gig remains on the server
It is this table that is so huge
pg_toast.pg_toast_11037520
I tried vaccuumdb without success. Vacuumdb full didn't work because only 5 gig left on the server
What can I do to get the disk space back ?
Can I simply drop pg_toast.pg_toast_11037520?
I'm using
"PostgreSQL 9.1.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit"
"POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.6" USE_STATS"
thanks in advance for your help
Steve
Steve Toutant, M. Sc.
Analyste en géomatique
Secteur environnement
Direction de la santé environnementale et de la toxicologie
Institut national de santé publique du Québec
Tél.: 418 646-6777 poste 30015
Téléc.: 418 644-4593
steve.toutant@inspq.qc.ca
http://www.inspq.qc.ca
http://www.monclimatmasante.qc.ca
Pour me rejoindre par courrier
Service de l'infrastructure et du réseau
Direction des technologies de l'information
Ministère de la Sécurité Publique
2525 boul. Laurier, 2è étage
Tour Laurentides
Québec, G1V 2L2
On Thu, Nov 28, 2013 at 8:25 AM, <Steve.Toutant@inspq.qc.ca> wrote:
Hi,
This query (postgis) didn't endup after 4 hours....and eat 40gig of disk space
SELECT id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as geom32198_s
into potentialite
FROM province as a, potentialite_tmp as b;
I stopped the execution and cancel request sent appears, but after an hour the request wasn't stopped yet.
So I killed the process and restat postgres.
The database is up and running but I didn't get the 40gig of disk space back. And only 5gig remains on the server
It is this table that is so huge
pg_toast.pg_toast_11037520
What table (if any) does it belong to? "select relname from pg_class where oid=11037520"
Cheers,
Jeff
Hi Jeff
This query returns
"tmpcarteprotection4"
tmpcarteprotection4 is old and useless, it could be dropped. Before dropping it is there something else I should do?
Thank you very much for your help
Steve
| Jeff Janes <jeff.janes@gmail.com> 2013-12-02 18:23 |
|
On Thu, Nov 28, 2013 at 8:25 AM, <Steve.Toutant@inspq.qc.ca> wrote:
Hi,
This query (postgis) didn't endup after 4 hours....and eat 40gig of disk space
SELECT id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as geom32198_s
into potentialite
FROM province as a, potentialite_tmp as b;
I stopped the execution and cancel request sent appears, but after an hour the request wasn't stopped yet.
So I killed the process and restat postgres.
The database is up and running but I didn't get the 40gig of disk space back. And only 5gig remains on the server
It is this table that is so huge
pg_toast.pg_toast_11037520
What table (if any) does it belong to? "select relname from pg_class where oid=11037520"
Cheers,
Jeff