pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtemp_file_limit parameter ignored?
10+ messages / 4 participants
[nested] [flat]
* temp_file_limit parameter ignored?
@ 2018-08-20 13:06 Erwin Fritz <efritz@gljpc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Erwin Fritz @ 2018-08-20 13:06 UTC (permalink / raw)
To: pgsql-admin
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.
________________________________
Attachments:
Erwin Fritz.vcf (text/x-vcard, 359B) download
BEGIN:VCARD
VERSION:2.1
N:Fritz;Erwin;;;
ORG:GLJ Petroleum Consultants
TITLE:IS Operations Manager
EMAIL;PREF;internet:efritz@gljpc.com
TEL;VOICE;WORK:+1 (403) 266-9570
TEL;FAX;WORK:+1 (403) 262-1855
ADR;WORK;PREF;ENCODING=QUOTED-PRINTABLE:;;Suite 4100, 400 Third Ave SW;Calgary;Alberta;T2P 4H2;Canada
FN:Erwin Fritz
REV:20180820T130635Z
END:VCARD
* Re: temp_file_limit parameter ignored?
@ 2018-08-20 13:15 Johannes Truschnigg <johannes@truschnigg.info>
parent: Erwin Fritz <efritz@gljpc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Johannes Truschnigg @ 2018-08-20 13:15 UTC (permalink / raw)
To: Erwin Fritz <efritz@gljpc.com>; +Cc: pgsql-admin
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.
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: temp_file_limit parameter ignored?
@ 2018-08-20 15:09 Erwin Fritz <efritz@gljpc.com>
parent: Johannes Truschnigg <johannes@truschnigg.info>
0 siblings, 2 replies; 10+ messages in thread
From: Erwin Fritz @ 2018-08-20 15:09 UTC (permalink / raw)
To: Johannes Truschnigg <johannes@truschnigg.info>; +Cc: pgsql-admin
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.
>
-----------------------------
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.
-----------------------------
-----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.
Attachments:
Erwin Fritz.vcf (text/x-vcard, 359B) download
BEGIN:VCARD
VERSION:2.1
N:Fritz;Erwin;;;
ORG:GLJ Petroleum Consultants
TITLE:IS Operations Manager
EMAIL;PREF;internet:efritz@gljpc.com
TEL;VOICE;WORK:+1 (403) 266-9570
TEL;FAX;WORK:+1 (403) 262-1855
ADR;WORK;PREF;ENCODING=QUOTED-PRINTABLE:;;Suite 4100, 400 Third Ave SW;Calgary;Alberta;T2P 4H2;Canada
FN:Erwin Fritz
REV:20180820T150935Z
END:VCARD
* Re: temp_file_limit parameter ignored?
@ 2018-08-20 15:23 Johannes Truschnigg <johannes@truschnigg.info>
parent: Erwin Fritz <efritz@gljpc.com>
1 sibling, 1 reply; 10+ messages in thread
From: Johannes Truschnigg @ 2018-08-20 15:23 UTC (permalink / raw)
To: Erwin Fritz <efritz@gljpc.com>; +Cc: pgsql-admin
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.
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: temp_file_limit parameter ignored?
@ 2018-08-20 15:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erwin Fritz <efritz@gljpc.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2018-08-20 15:26 UTC (permalink / raw)
To: Erwin Fritz <efritz@gljpc.com>; +Cc: Johannes Truschnigg <johannes@truschnigg.info>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: temp_file_limit parameter ignored?
@ 2018-08-20 20:06 Erwin Fritz <efritz@gljpc.com>
parent: Johannes Truschnigg <johannes@truschnigg.info>
0 siblings, 0 replies; 10+ messages in thread
From: Erwin Fritz @ 2018-08-20 20:06 UTC (permalink / raw)
To: Johannes Truschnigg <johannes@truschnigg.info>; +Cc: pgsql-admin
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" followed by "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 privileged and confidential. If you have received this message in error, please notify the sender immediately and delete all copies.
-----------------------------
________________________________________
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.
Attachments:
Erwin Fritz.vcf (text/x-vcard, 359B) download
BEGIN:VCARD
VERSION:2.1
N:Fritz;Erwin;;;
ORG:GLJ Petroleum Consultants
TITLE:IS Operations Manager
EMAIL;PREF;internet:efritz@gljpc.com
TEL;VOICE;WORK:+1 (403) 266-9570
TEL;FAX;WORK:+1 (403) 262-1855
ADR;WORK;PREF;ENCODING=QUOTED-PRINTABLE:;;Suite 4100, 400 Third Ave SW;Calgary;Alberta;T2P 4H2;Canada
FN:Erwin Fritz
REV:20180820T200620Z
END:VCARD
* RE: temp_file_limit parameter ignored?
@ 2018-08-20 20:09 Erwin Fritz <efritz@gljpc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Erwin Fritz @ 2018-08-20 20:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Johannes Truschnigg <johannes@truschnigg.info>; pgsql-admin
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 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
Attachments:
Erwin Fritz.vcf (text/x-vcard, 359B) download
BEGIN:VCARD
VERSION:2.1
N:Fritz;Erwin;;;
ORG:GLJ Petroleum Consultants
TITLE:IS Operations Manager
EMAIL;PREF;internet:efritz@gljpc.com
TEL;VOICE;WORK:+1 (403) 266-9570
TEL;FAX;WORK:+1 (403) 262-1855
ADR;WORK;PREF;ENCODING=QUOTED-PRINTABLE:;;Suite 4100, 400 Third Ave SW;Calgary;Alberta;T2P 4H2;Canada
FN:Erwin Fritz
REV:20180820T200905Z
END:VCARD
* Re: temp_file_limit parameter ignored?
@ 2018-08-20 20:44 Jerry Sievers <gsievers19@comcast.net>
parent: Erwin Fritz <efritz@gljpc.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jerry Sievers @ 2018-08-20 20:44 UTC (permalink / raw)
To: Erwin Fritz <efritz@gljpc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Johannes Truschnigg <johannes@truschnigg.info>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: temp_file_limit parameter ignored?
@ 2018-08-20 20:57 Jerry Sievers <gsievers19@comcast.net>
parent: Jerry Sievers <gsievers19@comcast.net>
1 sibling, 0 replies; 10+ messages in thread
From: Jerry Sievers @ 2018-08-20 20:57 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: Erwin Fritz <efritz@gljpc.com>; Tom Lane <tgl@sss.pgh.pa.us>; Johannes Truschnigg <johannes@truschnigg.info>; pgsql-admin
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: temp_file_limit parameter ignored?
@ 2018-08-21 14:04 Erwin Fritz <efritz@gljpc.com>
parent: Jerry Sievers <gsievers19@comcast.net>
1 sibling, 0 replies; 10+ messages in thread
From: Erwin Fritz @ 2018-08-21 14:04 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Johannes Truschnigg <johannes@truschnigg.info>; pgsql-admin
Yeah, that was me. I now understand the relationship between "set database/system", postgresql.auto.conf, and postgresql.conf much 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2018-08-21 14:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-20 13:06 temp_file_limit parameter ignored? Erwin Fritz <efritz@gljpc.com>
2018-08-20 13:15 ` Johannes Truschnigg <johannes@truschnigg.info>
2018-08-20 15:09 ` Erwin Fritz <efritz@gljpc.com>
2018-08-20 15:23 ` Johannes Truschnigg <johannes@truschnigg.info>
2018-08-20 20:06 ` Erwin Fritz <efritz@gljpc.com>
2018-08-20 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-08-20 20:09 ` Erwin Fritz <efritz@gljpc.com>
2018-08-20 20:44 ` Jerry Sievers <gsievers19@comcast.net>
2018-08-20 20:57 ` Jerry Sievers <gsievers19@comcast.net>
2018-08-21 14:04 ` Erwin Fritz <efritz@gljpc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox