Обсуждение: temp_file_limit parameter ignored?
Hello, all.
I’m an experienced Oracle DBA who’s converting to PostgreSQL, so please be kind.
I’m running the 64-bit PostgreSQL 9.5 on Ubuntu 16.04. I’m trying to index some large tables, so I set temp_file_limit to -1 in postgresql.conf and restarted the server. My index creation calls consistently failed with “temporary file size exceeds temp_file_limit (4194304kB)”. Sure enough, when I connect as ‘postgres’ to the database and run “show temp_file_limit”, 4GB is the answer.
Is there something obvious I’m missing?
| Erwin Fritz |
| IS Operations Manager |
| GLJ Petroleum Consultants |
| Tel: +1 (403) 266-9570 |
This message is intended only for the recipient(s) to which it is addressed and may contain information that is privileged and confidential. If you have received this message in error, please notify the sender immediately and delete all copies.
Вложения
Hi Erwin, On Mon, Aug 20, 2018 at 01:06:33PM +0000, Erwin Fritz wrote: > [...] Sure enough, when I connect as ‘postgres’ to the database and run > “show temp_file_limit”, 4GB is the answer. > > Is there something obvious I’m missing? Nothing obvious, afaict - but if you connect to your database, and run SELECT * FROM pg_settings WHERE name = 'temp_file_limit'; you should be able to see where (which file and line, if any) the setting's value is read from. (You might want to switch to "Extended display mode" in psql, issuing "\x" first, to improve readability.) Hth! -- with best regards: - Johannes Truschnigg ( johannes@truschnigg.info ) www: https://johannes.truschnigg.info/ phone: +43 650 2 133337 xmpp: johannes@truschnigg.info Please do not bother me with HTML-email or attachments. Thank you.
Вложения
Hmm. I did that, and the sourcefile and sourceline values are blank. I then ran "show config_file" and the result has thecorrect location of the file. > ----------------------------- This message is intended only for the recipient(s) to which it is addressed and may contain information that is privilegedand confidential. If you have received this message in error, please notify the sender immediately and delete allcopies. ----------------------------- -----Original Message----- > From: Johannes Truschnigg <johannes@truschnigg.info> > Sent: Monday, August 20, 2018 07:16 > To: Erwin Fritz <efritz@gljpc.com> > Cc: pgsql-admin@postgresql.org > Subject: Re: temp_file_limit parameter ignored? > > Hi Erwin, > > On Mon, Aug 20, 2018 at 01:06:33PM +0000, Erwin Fritz wrote: > > [...] Sure enough, when I connect as ‘postgres’ to the database and run > > “show temp_file_limit”, 4GB is the answer. > > > > Is there something obvious I’m missing? > > Nothing obvious, afaict - but if you connect to your database, and run > > SELECT * FROM pg_settings WHERE name = 'temp_file_limit'; > > you should be able to see where (which file and line, if any) the setting's > value is read from. > > (You might want to switch to "Extended display mode" in psql, issuing "\x" > first, to improve readability.) > > Hth! > > -- > with best regards: > - Johannes Truschnigg ( johannes@truschnigg.info ) > > www: https://johannes.truschnigg.info/ > phone: +43 650 2 133337 > xmpp: johannes@truschnigg.info > > Please do not bother me with HTML-email or attachments. Thank you.
Вложения
On Mon, Aug 20, 2018 at 03:09:33PM +0000, Erwin Fritz wrote:
> Hmm. I did that, and the sourcefile and sourceline values are blank. I then
> ran "show config_file" and the result has the correct location of the file.
Hmm. That's strange, because if you properly edited and saved that file,
postgres should at the very least notice the changed reboot value for the
setting. Are you sure you succesfully restarted/reloaded postgresql after your
change?
SELECT pg_reload_conf();
as a superuser role ("postgres" is fine) should do. Make sure to check the
postgresql logs afterwards, since it will contain information on changed
settings and syntax errors/problems (if any)!
Since you're on pg9.5, you could also use the ALTER SYSTEM command to commit
configuration changes, that will have postgres take care of applying and
persisting the changes you make. You should read
https://www.postgresql.org/docs/9.5/static/sql-altersystem.html for more info
on that particular topic/feature.
Hth.
--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )
www: https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp: johannes@truschnigg.info
Please do not bother me with HTML-email or attachments. Thank you.
Вложения
Erwin Fritz <efritz@gljpc.com> writes:
> Hmm. I did that, and the sourcefile and sourceline values are blank. I then ran "show config_file" and the result has
thecorrect location of the file.
What does the "source" column say?
regards, tom lane
I ran "select pg_reload_conf()" and it returned "t".
Originally, I had edited postgresql.conf, changing the temp_file_limit parameter. After that I did "pg_ctl stop"
followedby "pg_ctl start". No errors were reported, but yet the 4GB limit is still reported by "show temp_file_limit".
I then ran this:
psql <db> postgres
alter system set temp_file_limit to -1;
show temp_file_limit;
And it STILL shows 4GB! However, as expected, the postgresql.auto.conf file has the -1 value.
Weird!
-----------------------------
This message is intended only for the recipient(s) to which it is addressed and may contain information that is
privilegedand confidential. If you have received this message in error, please notify the sender immediately and delete
allcopies.
-----------------------------
________________________________________
From: Johannes Truschnigg [johannes@truschnigg.info]
Sent: August 20, 2018 09:23
To: Erwin Fritz
Cc: pgsql-admin@postgresql.org
Subject: Re: temp_file_limit parameter ignored?
On Mon, Aug 20, 2018 at 03:09:33PM +0000, Erwin Fritz wrote:
> Hmm. I did that, and the sourcefile and sourceline values are blank. I then
> ran "show config_file" and the result has the correct location of the file.
Hmm. That's strange, because if you properly edited and saved that file,
postgres should at the very least notice the changed reboot value for the
setting. Are you sure you succesfully restarted/reloaded postgresql after your
change?
SELECT pg_reload_conf();
as a superuser role ("postgres" is fine) should do. Make sure to check the
postgresql logs afterwards, since it will contain information on changed
settings and syntax errors/problems (if any)!
Since you're on pg9.5, you could also use the ALTER SYSTEM command to commit
configuration changes, that will have postgres take care of applying and
persisting the changes you make. You should read
https://www.postgresql.org/docs/9.5/static/sql-altersystem.html for more info
on that particular topic/feature.
Hth.
--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )
www: https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp: johannes@truschnigg.info
Please do not bother me with HTML-email or attachments. Thank you.
Вложения
Here's the output:
select * from pg_settings where name = 'temp_file_limit';
-[ RECORD 1 ]---+-------------------------------------------------------------------
name | temp_file_limit
setting | 4194304
unit | kB
category | Resource Usage / Disk
short_desc | Limits the total size of all temporary files used by each session.
extra_desc | -1 means no limit.
context | superuser
vartype | integer
source | database
min_val | -1
max_val | 2147483647
enumvals |
boot_val | -1
reset_val | 4194304
sourcefile |
sourceline |
pending_restart | f
-----------------------------
This message is intended only for the recipient(s) to which it is addressed and may contain information that is
privilegedand confidential. If you have received this message in error, please notify the sender immediately and delete
allcopies.
-----------------------------
________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: August 20, 2018 09:26
To: Erwin Fritz
Cc: Johannes Truschnigg; pgsql-admin@postgresql.org
Subject: Re: temp_file_limit parameter ignored?
Erwin Fritz <efritz@gljpc.com> writes:
> Hmm. I did that, and the sourcefile and sourceline values are blank. I then ran "show config_file" and the result has
thecorrect location of the file.
What does the "source" column say?
regards, tom lane
Вложения
Erwin Fritz <efritz@gljpc.com> writes: > Here's the output: > > select * from pg_settings where name = 'temp_file_limit'; > -[ RECORD 1 ]---+------------------------------------------------------------------- > name | temp_file_limit > setting | 4194304 > unit | kB > category | Resource Usage / Disk > short_desc | Limits the total size of all temporary files used by each session. > extra_desc | -1 means no limit. > context | superuser > vartype | integer > source | database ^^^^^ This means someone/thing said... ALTER DATABASE $foodb SET temp_file_limit to '$whatever'; See psql \drds. Just do an explicit override in your own job prior to the code that errors out due to resource shortfall. SET temp_file_limit to -1; HTH > min_val | -1 > max_val | 2147483647 > enumvals | > boot_val | -1 > reset_val | 4194304 > sourcefile | > sourceline | > pending_restart | f > > > > > ----------------------------- > This message is intended only for the recipient(s) to which it is > addressed and may contain information that is privileged and > confidential. If you have received this message in error, please > notify the sender immediately and delete all copies. > ----------------------------- > > ________________________________________ > From: Tom Lane [tgl@sss.pgh.pa.us] > Sent: August 20, 2018 09:26 > To: Erwin Fritz > Cc: Johannes Truschnigg; pgsql-admin@postgresql.org > Subject: Re: temp_file_limit parameter ignored? > > Erwin Fritz <efritz@gljpc.com> writes: >> Hmm. I did that, and the sourcefile and sourceline values are blank. I then ran "show config_file" and the result hasthe correct location of the file. > > What does the "source" column say? > > regards, tom lane > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
Jerry Sievers <gsievers19@comcast.net> writes: > Erwin Fritz <efritz@gljpc.com> writes: > >> Here's the output: >> >> select * from pg_settings where name = 'temp_file_limit'; >> -[ RECORD 1 ]---+------------------------------------------------------------------- >> name | temp_file_limit >> setting | 4194304 >> unit | kB >> category | Resource Usage / Disk >> short_desc | Limits the total size of all temporary files used by each session. >> extra_desc | -1 means no limit. >> context | superuser >> vartype | integer >> source | database > > ^^^^^ > > This means someone/thing said... > > ALTER DATABASE $foodb SET temp_file_limit to '$whatever'; > > See psql \drds. > > Just do an explicit override in your own job prior to the code that > errors out due to resource shortfall. > > SET temp_file_limit to -1; oops! Pardon me. only superuser can frob that setting so this advice I gave immediately above might not help you. > > HTH > > >> min_val | -1 >> max_val | 2147483647 >> enumvals | >> boot_val | -1 >> reset_val | 4194304 >> sourcefile | >> sourceline | >> pending_restart | f >> >> >> >> >> ----------------------------- >> This message is intended only for the recipient(s) to which it is >> addressed and may contain information that is privileged and >> confidential. If you have received this message in error, please >> notify the sender immediately and delete all copies. >> ----------------------------- >> >> ________________________________________ >> From: Tom Lane [tgl@sss.pgh.pa.us] >> Sent: August 20, 2018 09:26 >> To: Erwin Fritz >> Cc: Johannes Truschnigg; pgsql-admin@postgresql.org >> Subject: Re: temp_file_limit parameter ignored? >> >> Erwin Fritz <efritz@gljpc.com> writes: >>> Hmm. I did that, and the sourcefile and sourceline values are blank. I then ran "show config_file" and the result hasthe correct location of the file. >> >> What does the "source" column say? >> >> regards, tom lane >> >> -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
Yeah, that was me. I now understand the relationship between "set database/system", postgresql.auto.conf, and postgresql.confmuch better than I did before. Thanks! Erwin > -----Original Message----- > From: Jerry Sievers <gsievers19@comcast.net> > Sent: Monday, August 20, 2018 14:44 > To: Erwin Fritz <efritz@gljpc.com> > Cc: Tom Lane <tgl@sss.pgh.pa.us>; Johannes Truschnigg > <johannes@truschnigg.info>; pgsql-admin@postgresql.org > Subject: Re: temp_file_limit parameter ignored? > > Erwin Fritz <efritz@gljpc.com> writes: > > > Here's the output: > > > > select * from pg_settings where name = 'temp_file_limit'; > > -[ RECORD 1 ]---+------------------------------------------------------------------- > > name | temp_file_limit > > setting | 4194304 > > unit | kB > > category | Resource Usage / Disk > > short_desc | Limits the total size of all temporary files used by each > session. > > extra_desc | -1 means no limit. > > context | superuser > > vartype | integer > > source | database > > ^^^^^ > > This means someone/thing said... > > ALTER DATABASE $foodb SET temp_file_limit to '$whatever'; > > See psql \drds. > > Just do an explicit override in your own job prior to the code that > errors out due to resource shortfall. > > SET temp_file_limit to -1; > > HTH > > > > min_val | -1 > > max_val | 2147483647 > > enumvals | > > boot_val | -1 > > reset_val | 4194304 > > sourcefile | > > sourceline | > > pending_restart | f > > > > > > > > > > ----------------------------- > > This message is intended only for the recipient(s) to which it is > > addressed and may contain information that is privileged and > > confidential. If you have received this message in error, please > > notify the sender immediately and delete all copies. > > ----------------------------- > > > > ________________________________________ > > From: Tom Lane [tgl@sss.pgh.pa.us] > > Sent: August 20, 2018 09:26 > > To: Erwin Fritz > > Cc: Johannes Truschnigg; pgsql-admin@postgresql.org > > Subject: Re: temp_file_limit parameter ignored? > > > > Erwin Fritz <efritz@gljpc.com> writes: > >> Hmm. I did that, and the sourcefile and sourceline values are blank. I then > ran "show config_file" and the result has the correct location of the file. > > > > What does the "source" column say? > > > > regards, tom lane > > > > > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consulting@comcast.net > p: 312.241.7800