SEO

The following bug has been logged online:

Bug reference: 4096
Logged by:
Email address: j6m@adm.estp.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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

j6m@adm.estp.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



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Zdenek Kotala" <Zdenek.Kotala@Sun.COM> writes:

> j6m@adm.estp.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!

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"" <j6m@adm.estp.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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@estp-intranet:~> cd /var/lib/pgsql
postgres@estp-intranet:~> cd data
postgres@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@estp-intranet:~/data> cd pg_xlog
postgres@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@estp-intranet:~/data/pg_xlog> cp -p 000000010000000F000000* ..
postgres@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@sss.pgh.pa.us>
To: <j6m@adm.estp.fr>
Cc: <pgsql-bugs@postgresql.org>
Sent: Monday, April 07, 2008 4:09 PM
Subject: Re: [BUGS] BUG #4096: PG 8.3.1. confused about remaining disk space


> "" <j6m@adm.estp.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.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.rei.../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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Sorry, this is the URL I meant to send:
http://www.mail-archive.com/reiserfs.../msg19905.html


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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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@enterprisedb.com>
To: "J6M" <j6m@adm.estp.fr>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-bugs@postgresql.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.rei.../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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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@adm.estp.fr>
To: "Gregory Stark" <stark@enterprisedb.com>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-bugs@postgresql.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@enterprisedb.com>
> To: "J6M" <j6m@adm.estp.fr>
> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-bugs@postgresql.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.rei.../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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.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


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs