Re: BUG #4096: PG 8.3.1. confused about remaining disk space

The following bug has been logged online:

Bug reference: 4096
Logged by:
Email address: j6m(at)adm(dot)estp(dot)fr
PostgreSQL version: 8.3.1
Operating system: Linux
Description: PG 8.3.1. confused about remaining disk space
Details:

I ran today a legacy application with PG 8.3.1 on a test machine.

The DB is some ~30 MB. Default tables do have OIDs and BLCKSZ is set
system-wide to 16384 (because of another DB).

A lot of messages like these do appear wich tend to indicate PG 8.3.1 is
confused. This set of SQL scripts used to run from PG 7.0.3 to PG 8.2.6 with
no problem.

WARNING: could not create relation-cache initialization file
"base/19693/pg_internal.init.29329": No space left on device
DETAIL: Continuing anyway, but there's something wrong.
create temp table param0 (max0 date, datelb date) : ERROR: could not create

relation 1663/19693/634161: No space left on device
row number 0 is out of range 0..-1
row number 0 is out of range 0..-1
WARNING: could not create relation-cache initialization file
"base/19693/pg_internal.init.29330": No space left on device
DETAIL: Continuing anyway, but there's something wrong.
create temp table param0 (max0 date, datelb date) : ERROR: could not create

relation 1663/19693/634162: No space left on device
row number 0 is out of range 0..-1
row number 0 is out of range 0..-1

Disk saturation, you think ? No at all, as in the meantime :

Filesystem Size Used Avail Use% Mounted on
/dev/sda3 9.9G 8.5G 874M 91% /
udev 125M 88K 125M 1% /dev
/dev/sda1 54M 15M 37M 29% /boot
/dev/sda4 8.3G 7.4G 966M 89% /home
/dev/sdb1 38G 31G 7.2G 81% /var/lib/pgsql

As you can see, there is some 200 times the active DB size that remains
available.

j6m(at)adm(dot)estp(dot)fr napsal(a):

> I ran today a legacy application with PG 8.3.1 on a test machine.
>
> The DB is some ~30 MB. Default tables do have OIDs and BLCKSZ is set
> system-wide to 16384 (because of another DB).

Can you tried your application with standard BLKSZ? Or Can you send testcase?

>
> A lot of messages like these do appear wich tend to indicate PG 8.3.1 is
> confused. This set of SQL scripts used to run from PG 7.0.3 to PG 8.2.6 with
> no problem.

What was BLCKSZ on these servers?

> WARNING: could not create relation-cache initialization file
> "base/19693/pg_internal.init.29329": No space left on device
> DETAIL: Continuing anyway, but there's something wrong.
> create temp table param0 (max0 date, datelb date) : ERROR: could not create
>
> relation 1663/19693/634161: No space left on device
> row number 0 is out of range 0..-1
> row number 0 is out of range 0..-1
> WARNING: could not create relation-cache initialization file
> "base/19693/pg_internal.init.29330": No space left on device
> DETAIL: Continuing anyway, but there's something wrong.
> create temp table param0 (max0 date, datelb date) : ERROR: could not create
>
> relation 1663/19693/634162: No space left on device
> row number 0 is out of range 0..-1
> row number 0 is out of range 0..-1
>
> Disk saturation, you think ? No at all, as in the meantime :
>
> Filesystem Size Used Avail Use% Mounted on
> /dev/sda3 9.9G 8.5G 874M 91% /
> udev 125M 88K 125M 1% /dev
> /dev/sda1 54M 15M 37M 29% /boot
> /dev/sda4 8.3G 7.4G 966M 89% /home
> /dev/sdb1 38G 31G 7.2G 81% /var/lib/pgsql
>
> As you can see, there is some 200 times the active DB size that remains
> available.

User quota or super user space reservation could take affect. Could you create
file as a postgres user on pgsql filesystem?

Zdenek

"Zdenek Kotala" <Zdenek(dot)Kotala(at)Sun(dot)COM> writes:

> j6m(at)adm(dot)estp(dot)fr napsal(a):
>> relation 1663/19693/634162: No space left on device
...
>> Disk saturation, you think ? No at all, as in the meantime :
>>
>> Filesystem Size Used Avail Use% Mounted on
>> /dev/sda3 9.9G 8.5G 874M 91% /
>> udev 125M 88K 125M 1% /dev
>> /dev/sda1 54M 15M 37M 29% /boot
>> /dev/sda4 8.3G 7.4G 966M 89% /home
>> /dev/sdb1 38G 31G 7.2G 81% /var/lib/pgsql
>>
>> As you can see, there is some 200 times the active DB size that remains
>> available.
>
> User quota or super user space reservation could take affect. Could you create
> file as a postgres user on pgsql filesystem?

Also check "df -i"

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

----- Original Message -----
From: "Zdenek Kotala" <Zdenek(dot)Kotala(at)Sun(dot)COM>
To: <j6m(at)adm(dot)estp(dot)fr>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Sent: Monday, April 07, 2008 12:51 PM
Subject: Re: [BUGS] BUG #4096: PG 8.3.1. confused about remaining disk space

> j6m(at)adm(dot)estp(dot)fr napsal(a):
>
>> I ran today a legacy application with PG 8.3.1 on a test machine.
>>
>> The DB is some ~30 MB. Default tables do have OIDs and BLCKSZ is set
>> system-wide to 16384 (because of another DB).
>
> Can you tried your application with standard BLKSZ? Or Can you send
> testcase?
>
>>
>> A lot of messages like these do appear wich tend to indicate PG 8.3.1 is
>> confused. This set of SQL scripts used to run from PG 7.0.3 to PG 8.2.6
>> with
>> no problem.
>
> What was BLCKSZ on these servers?

---8<-- SNIP

>
> User quota or super user space reservation could take affect. Could you
> create file as a postgres user on pgsql filesystem?
>

BLCKSZ was 16384 too. Actually, it ran with no problem on PG 8.2.6 with the
very same Linux distro (openSuSE 10.3) on the very same machine. So, it
does not seem the system settings are faulty (permission, user quotas, etc.
etc.).

Last week, I was running POG 8.2.6 on it. When 8.3.1 went out, I decided to
give it a try. I did pg_dumpall, recompiled PG 8.1.3 with the options that
were given by pg_config --configure and restored the dumpall file.

Needless to say, all regression tests were OK and I also checked the
libpq.so symbolic links tree in /usr/lib

----- Original Message -----
From: "Gregory Stark" <stark(at)enterprisedb(dot)com>
To: "Zdenek Kotala" <Zdenek(dot)Kotala(at)Sun(dot)COM>
Cc: <j6m(at)adm(dot)estp(dot)fr>; <pgsql-bugs(at)postgresql(dot)org>
Sent: Monday, April 07, 2008 1:14 PM
Subject: Re: [BUGS] BUG #4096: PG 8.3.1. confused about remaining disk space

>
> Also check "df -i"
>

This might be an issue :

Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/sda3 1311552 302829 1008723 24% /
udev 31875 1318 30557 5% /dev
/dev/sda1 14056 40 14016 1% /boot
/dev/sda4 0 0 0 - /home
/dev/sdb1 0 0 0 - /var/lib/pgsql

However /dev/sda4 and /dev/sdb1 use reiserfs

/dev/sda3 on / type ext3 (rw,acl,user_xattr)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
debugfs on /sys/kernel/debug type debugfs (rw)
udev on /dev type tmpfs (rw)
devpts on /dev/pts type devpts (rw,mode=0620,gid=5)
/dev/sda1 on /boot type ext3 (rw,acl,user_xattr)
/dev/sda4 on /home type reiserfs (rw)
/dev/sdb1 on /var/lib/pgsql type reiserfs (rw)
securityfs on /sys/kernel/security type securityfs (rw)

Hence, free inode counts are not relevant (at least AFAIK on reiserfs
internals).

"" <j6m(at)adm(dot)estp(dot)fr> writes:
> A lot of messages like these do appear wich tend to indicate PG 8.3.1 is
> confused. This set of SQL scripts used to run from PG 7.0.3 to PG 8.2.6 with
> no problem.

> WARNING: could not create relation-cache initialization file
> "base/19693/pg_internal.init.29329": No space left on device

This is just PG reporting what the kernel told it. If there's any
"confusion" it's at the kernel level.

I agree with Zdenek's diagnosis of user quota problems ...

regards, tom lane

OK. So why did this not occur when I was running 8.2.6 ?

To avoid any ambiguity, here we go

estp-intranet:~ # su - postgres
postgres(at)estp-intranet:~> cd /var/lib/pgsql
postgres(at)estp-intranet:~> cd data
postgres(at)estp-intranet:~/data> ls
base pg_hba.conf pg_multixact pg_twophase postgresql.conf
global pg_ident.conf pg_subtrans PG_VERSION postmaster.opts
pg_clog pg_log pg_tblspc pg_xlog postmaster.pid
postgres(at)estp-intranet:~/data> cd pg_xlog
postgres(at)estp-intranet:~/data/pg_xlog> ls
000000010000000F00000040 000000010000000F00000055 000000010000000F0000006A
000000010000000F00000041 000000010000000F00000056 000000010000000F0000006B
000000010000000F00000042 000000010000000F00000057 000000010000000F0000006C
000000010000000F00000043 000000010000000F00000058 000000010000000F0000006D
000000010000000F00000044 000000010000000F00000059 000000010000000F0000006E
000000010000000F00000045 000000010000000F0000005A 000000010000000F0000006F
000000010000000F00000046 000000010000000F0000005B 000000010000000F00000070
000000010000000F00000047 000000010000000F0000005C 000000010000000F00000071
000000010000000F00000048 000000010000000F0000005D 000000010000000F00000072
000000010000000F00000049 000000010000000F0000005E 000000010000000F00000073
000000010000000F0000004A 000000010000000F0000005F 000000010000000F00000074
000000010000000F0000004B 000000010000000F00000060 000000010000000F00000075
000000010000000F0000004C 000000010000000F00000061 000000010000000F00000076
000000010000000F0000004D 000000010000000F00000062 000000010000000F00000077
000000010000000F0000004E 000000010000000F00000063 000000010000000F00000078
000000010000000F0000004F 000000010000000F00000064 000000010000000F00000079
000000010000000F00000050 000000010000000F00000065 000000010000000F0000007A
000000010000000F00000051 000000010000000F00000066 000000010000000F0000007B
000000010000000F00000052 000000010000000F00000067 000000010000000F0000007C
000000010000000F00000053 000000010000000F00000068 000000010000000F0000007D
000000010000000F00000054 000000010000000F00000069 archive_status
postgres(at)estp-intranet:~/data/pg_xlog> cp -p 000000010000000F000000* ..
postgres(at)estp-intranet:~/data/pg_xlog>

As you can see, there is no problem to create new files on the filesystem
with user postgres.

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: <j6m(at)adm(dot)estp(dot)fr>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Sent: Monday, April 07, 2008 4:09 PM
Subject: Re: [BUGS] BUG #4096: PG 8.3.1. confused about remaining disk space

> "" <j6m(at)adm(dot)estp(dot)fr> writes:
>> A lot of messages like these do appear wich tend to indicate PG 8.3.1 is
>> confused. This set of SQL scripts used to run from PG 7.0.3 to PG 8.2.6
>> with
>> no problem.
>
>> WARNING: could not create relation-cache initialization file
>> "base/19693/pg_internal.init.29329": No space left on device
>
> This is just PG reporting what the kernel told it. If there's any
> "confusion" it's at the kernel level.
>
> I agree with Zdenek's diagnosis of user quota problems ...
>
> regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

What version of linux and reiserfs?

see also:

http://osdir.com/ml/file-systems.reiserfs.general/2004-01/msg00116.html

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Sorry, this is the URL I meant to send:
http://www.mail-archive.com/reiserfs-list(at)namesys(dot)com/msg19905.html

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

Linux estp-intranet 2.6.22.5-31-default #1 SMP 2007/09/21 22:29:00 UTC i686
i686 i386 GNU/Linux

How can I precisely answer to the reiserfs version question ?

(Although I presume the 2005 fix mentioned in the link you provided has been
applied, as OpenSuSE 10.3 was published on the 4th of October 2007).

----- Original Message -----
From: "Gregory Stark" <stark(at)enterprisedb(dot)com>
To: "J6M" <j6m(at)adm(dot)estp(dot)fr>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-bugs(at)postgresql(dot)org>
Sent: Monday, April 07, 2008 5:40 PM
Subject: Re: BUG #4096: PG 8.3.1. confused about remaining disk space

>
>
> What version of linux and reiserfs?
>
> see also:
>
> http://osdir.com/ml/file-systems.reiserfs.general/2004-01/msg00116.html
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's 24x7 Postgres support!

debugreiserfs /dev/sdb1
debugreiserfs 3.6.19 (2003 www.namesys.com)

Filesystem state: consistency is not checked after last mounting

Reiserfs super block in block 16 on 0x811 of format 3.6 with standard
journal
Count of blocks on the device: 9769520
Number of bitmaps: 299
Blocksize: 4096
Free blocks (count of blocks - used [journal, bitmaps, data, reserved]
blocks): 1985622
Root block: 32771
Filesystem is NOT clean
Tree height: 4
Hash function used to sort names: "r5"
Objectid map size 248, max 972
Journal parameters:
Device [0x0]
Magic [0x1299a7cb]
Size 8193 blocks (including 1 for journal header) (first block 18)
Max transaction length 1024 blocks
Max batch size 900 blocks
Max commit age 30
Blocks reserved by journal: 0
Fs state field: 0x0:
sb_version: 2
inode generation number: 45860267
UUID: 5d934e7e-190e-4997-a67e-d420eae09295
LABEL:
Set flags in SB:
ATTRIBUTES CLEAN

----- Original Message -----
From: "J6M" <j6m(at)adm(dot)estp(dot)fr>
To: "Gregory Stark" <stark(at)enterprisedb(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-bugs(at)postgresql(dot)org>
Sent: Monday, April 07, 2008 5:50 PM
Subject: Re: [BUGS] BUG #4096: PG 8.3.1. confused about remaining disk space

> Linux estp-intranet 2.6.22.5-31-default #1 SMP 2007/09/21 22:29:00 UTC
> i686 i686 i386 GNU/Linux
>
> How can I precisely answer to the reiserfs version question ?
>
> (Although I presume the 2005 fix mentioned in the link you provided has
> been applied, as OpenSuSE 10.3 was published on the 4th of October 2007).
>
>
> ----- Original Message -----
> From: "Gregory Stark" <stark(at)enterprisedb(dot)com>
> To: "J6M" <j6m(at)adm(dot)estp(dot)fr>
> Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-bugs(at)postgresql(dot)org>
> Sent: Monday, April 07, 2008 5:40 PM
> Subject: Re: BUG #4096: PG 8.3.1. confused about remaining disk space
>
>
>>
>>
>> What version of linux and reiserfs?
>>
>> see also:
>>
>> http://osdir.com/ml/file-systems.reiserfs.general/2004-01/msg00116.html
>>
>> --
>> Gregory Stark
>> EnterpriseDB http://www.enterprisedb.com
>> Ask me about EnterpriseDB's 24x7 Postgres support!
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

J6M wrote:
> OK. So why did this not occur when I was running 8.2.6 ?

I would advise to install some very detailed monitoring on your
diskspace usage and look for spikes that correlate with your database
errors -. I have seen this issue with bad queries that are resulting in
enormous on-disk sorts and running the box temporary out of diskspace.

Stefan

The df was made while the error messages occured and the scripts were
running. So it is not a temporary 'mammothean' table.

When I thought of this, two possible elads came to my mind :

- A mistake when I tuned the postgresql.conf file (resource is scarce, as
this test machine is a PIII 700 with 256 MB RAM dating from Q4 2000)

- The default for this particular DB has been set to keep OIDs (I "pour"
several INSERTs sets arising from different files in an auxiliary table, and
keep the last entered entry for any relevant keys, Those entries then go to
a production table).

----- Original Message -----
From: "Stefan Kaltenbrunner" <stefan(at)kaltenbrunner(dot)cc>
To: "J6M" <j6m(at)adm(dot)estp(dot)fr>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-bugs(at)postgresql(dot)org>
Sent: Monday, April 07, 2008 8:31 PM
Subject: Re: [BUGS] BUG #4096: PG 8.3.1. confused about remaining disk space

> J6M wrote:
>> OK. So why did this not occur when I was running 8.2.6 ?
>
> I would advise to install some very detailed monitoring on your diskspace
> usage and look for spikes that correlate with your database errors -. I
> have seen this issue with bad queries that are resulting in enormous
> on-disk sorts and running the box temporary out of diskspace.
>
>
> Stefan
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

After further investigations, it seems the second hypothesis is correct.
This particular DB was created with "with OIDs" as default.

The script I was using had no SET for this option and it creates a few
temporary tables (which are quite small in size as they are created by
SELECT .. INTO TEMP TABLE .. FROM .. GROUP BY .., so they are smaller than
their origins).

Adding SET default_with_oids = true to the script seems to solve the
problem.

Also, when run with the former releases, AUTOVACUUMING was off. Now it is
on.

Regards
J6M
----- Original Message -----
From: "J6M" <j6m(at)adm(dot)estp(dot)fr>
To: "Stefan Kaltenbrunner" <stefan(at)kaltenbrunner(dot)cc>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-bugs(at)postgresql(dot)org>
Sent: Tuesday, April 08, 2008 12:37 PM
Subject: Re: [BUGS] BUG #4096: PG 8.3.1. confused about remaining disk space

> The df was made while the error messages occured and the scripts were
> running. So it is not a temporary 'mammothean' table.
>
> When I thought of this, two possible elads came to my mind :
>
> - A mistake when I tuned the postgresql.conf file (resource is scarce, as
> this test machine is a PIII 700 with 256 MB RAM dating from Q4 2000)
>
> - The default for this particular DB has been set to keep OIDs (I "pour"
> several INSERTs sets arising from different files in an auxiliary table,
> and keep the last entered entry for any relevant keys, Those entries then
> go to a production table).
>