Обсуждение: Temp files on Commit

Поиск
Список
Период
Сортировка

Temp files on Commit

От
"Nicholson, Brad (Toronto, ON, CA)"
Дата:

Hi,


I’m seeing cases where I have temp files being written on commit, such as.

 

2013-08-16 12:09:02 GMT [14480]: [588-1] user=dbuser,db=dbname STATEMENT:  COMMIT

2013-08-16 12:09:02 GMT [14480]: [589-1] user= dbuser,db=dbname LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14480.263", size 814822

 

Is this a case of having work_mem set to low, or something else?  I haven’t seen temp files on commit before.

 

Thanks,

Brad.

 

Re: Temp files on Commit

От
Tom Lane
Дата:
"Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hp.com> writes:
> I'm seeing cases where I have temp files being written on commit, such as.

> 2013-08-16 12:09:02 GMT [14480]: [588-1] user=dbuser,db=dbname STATEMENT:  COMMIT
> 2013-08-16 12:09:02 GMT [14480]: [589-1] user= dbuser,db=dbname LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp14480.263",size 814822 

> Is this a case of having work_mem set to low, or something else?  I haven't seen temp files on commit before.

They're not being written on commit, they're being cleaned up.  That
message about tempfile usage isn't written until the file is deleted,
since we don't know its maximum size for sure until then.

There is some setting that controls whether such messages appear at
all, but I'm too lazy to go look it up right now.

            regards, tom lane

Re: Temp files on Commit

От
bricklen
Дата:
On Thu, Aug 22, 2013 at 8:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

There is some setting that controls whether such messages appear at
all

Is it "log_temp_files"?

Re: Temp files on Commit

От
Michael Paquier
Дата:
On Fri, Aug 23, 2013 at 12:44 AM, bricklen <bricklen@gmail.com> wrote:
> On Thu, Aug 22, 2013 at 8:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>>
>> There is some setting that controls whether such messages appear at
>> all
>
>
> Is it "log_temp_files"?
Exactly. More reference here:
http://www.postgresql.org/docs/devel/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
--
Michael

Update quey

От
"Hall, Samuel L (Sam)"
Дата:
I have a table (pubacc_lo) from the US government with 500,00+ rows. It has latitude and longitude in three columns
eachfor degrees, minutes and seconds. I need a Point geometry column. So I wrote this query: 

with mydata AS (SELECT (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat ,
(pubacc_lo.long_degrees+ pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long FROM pubacc_lo) 
UPDATE pubacc_lo SET lonlat_84 = ST_SetSRID(ST_makePOINT(long,lat),4326) FROM mydata;

It appears to work, but is going to take days it seems to finish. Anybody have a faster way?

Re: Update quey

От
bricklen
Дата:

On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) <sam.hall@alcatel-lucent.com> wrote:
I have a table (pubacc_lo) from the US government with 500,00+ rows. It has latitude and longitude in three columns each for degrees, minutes and seconds. I need a Point geometry column. So I wrote this query:

with mydata AS (SELECT (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long FROM pubacc_lo)
UPDATE pubacc_lo SET lonlat_84 = ST_SetSRID(ST_makePOINT(long,lat),4326) FROM mydata;

It appears to work, but is going to take days it seems to finish. Anybody have a faster way?


Create a new table, rather than updating the existing one.

CREATE TABLE pubacc_lo_new AS
select *, (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long
from pubacc_lo;

Then either rename them, or use the new table.

Re: Update quey

От
"Hall, Samuel L (Sam)"
Дата:

Thank you! That worked fine.

 

From: bricklen [mailto:bricklen@gmail.com]
Sent: Friday, August 23, 2013 10:08 AM
To: Hall, Samuel L (Sam)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Update quey

 

 

On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) <sam.hall@alcatel-lucent.com> wrote:

I have a table (pubacc_lo) from the US government with 500,00+ rows. It has latitude and longitude in three columns each for degrees, minutes and seconds. I need a Point geometry column. So I wrote this query:

with mydata AS (SELECT (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long FROM pubacc_lo)
UPDATE pubacc_lo SET lonlat_84 = ST_SetSRID(ST_makePOINT(long,lat),4326) FROM mydata;

It appears to work, but is going to take days it seems to finish. Anybody have a faster way?

 

Create a new table, rather than updating the existing one.

CREATE TABLE pubacc_lo_new AS
select *, (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 + pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees + pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long

from pubacc_lo;

Then either rename them, or use the new table.