From Sergey.Zubkovsky@transas.com Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C99562E0065 for ; Thu, 13 Mar 2008 16:50:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 44470-04 for ; Thu, 13 Mar 2008 16:50:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.transas.com (mail.transas.com [82.140.75.150]) by postgresql.org (Postfix) with ESMTP id D4FAB2E0063 for ; Thu, 13 Mar 2008 16:50:10 -0300 (ADT) Received: from sol.transas.com (sol.transas.com [10.10.0.20]) by mail.transas.com (Postfix) with ESMTP id 76E684FFFC8 for ; Thu, 13 Mar 2008 22:50:03 +0300 (MSK) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C88543.6B301A08" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: pg_total_relation_size() and CHECKPOINT Date: Thu, 13 Mar 2008 22:49:58 +0300 Message-ID: <528853D3C5ED2C4AA8990B504BA7FB850106DF10@sol.transas.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: pg_total_relation_size() and CHECKPOINT Thread-Index: AciFQ2qohm2MQ5OPQVW+cl9KEijFhA== From: "Zubkovsky, Sergey" To: X-Transas-MailScanner: Found to be clean X-Transas-MailScanner-From: sergey.zubkovsky@transas.com X-Transas-MailScanner-To: pgsql-docs@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/40 X-Sequence-Number: 4852 This is a multi-part message in MIME format. ------_=_NextPart_001_01C88543.6B301A08 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, =20 I've detected that a result value of pg_total_relation_size() for an actively updated table might be significantly differ from a result that is returned after explicit CHECKPOINT command execution. =20 select version();=20 "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" =20 I understand the reasons of such behavior: cache buffers must be flushed in order to be sure that pg_total_relation_size() result will be like we expect. Is this right? If so I think it would be very useful to add a description of this peculiarity to the PG documentation. =20 =20 Thanks in advance, Zubkovsky Sergey ------_=_NextPart_001_01C88543.6B301A08 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello,

I’ve = detected that a result value of pg_total_relation_size() for an actively updated table = might be significantly differ from a result that is returned after explicit = CHECKPOINT command execution.

select = version();

"PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"

I understand = the reasons of such behavior: cache buffers must be flushed in order to be sure that = pg_total_relation_size() result will be like we expect.

Is this = right?

If so I think = it would be very useful to add a description of this peculiarity to the PG = documentation.

Thanks in = advance,

Zubkovsky Sergey

------_=_NextPart_001_01C88543.6B301A08-- From tgl@sss.pgh.pa.us Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 860722E00C6 for ; Thu, 13 Mar 2008 17:20:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 61434-02 for ; Thu, 13 Mar 2008 17:20:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5B1B82E0118 for ; Thu, 13 Mar 2008 17:20:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2DKKU1i000440; Thu, 13 Mar 2008 16:20:30 -0400 (EDT) To: "Zubkovsky, Sergey" cc: pgsql-docs@postgresql.org Subject: Re: pg_total_relation_size() and CHECKPOINT In-reply-to: <528853D3C5ED2C4AA8990B504BA7FB850106DF10@sol.transas.com> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF10@sol.transas.com> Comments: In-reply-to "Zubkovsky, Sergey" message dated "Thu, 13 Mar 2008 22:49:58 +0300" Date: Thu, 13 Mar 2008 16:20:30 -0400 Message-ID: <439.1205439630@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/41 X-Sequence-Number: 4853 "Zubkovsky, Sergey" writes: > I've detected that a result value of pg_total_relation_size() for an > actively updated table might be significantly differ from a result that > is returned after explicit CHECKPOINT command execution. Uh, can you show a specific example of what you mean? > I understand the reasons of such behavior: cache buffers must be flushed > in order to be sure that pg_total_relation_size() result will be like we > expect. I wouldn't think so. The space for a page is allocated immediately when needed --- its *contents* might not be up to date, but that shouldn't affect pg_total_relation_size. regards, tom lane From Sergey.Zubkovsky@transas.com Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A66B72E335D for ; Fri, 14 Mar 2008 12:24:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 89566-01-9 for ; Fri, 14 Mar 2008 12:23:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.transas.com (mail.transas.com [82.140.75.150]) by postgresql.org (Postfix) with ESMTP id 7D1B32E3163 for ; Fri, 14 Mar 2008 12:23:33 -0300 (ADT) Received: from sol.transas.com (sol.transas.com [10.10.0.20]) by mail.transas.com (Postfix) with ESMTP id 50C4E500714; Fri, 14 Mar 2008 18:23:22 +0300 (MSK) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C885E7.56CA5C26" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: pg_total_relation_size() and CHECKPOINT Date: Fri, 14 Mar 2008 18:23:22 +0300 Message-ID: <528853D3C5ED2C4AA8990B504BA7FB850106DF13@sol.transas.com> In-Reply-To: <439.1205439630@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [DOCS] pg_total_relation_size() and CHECKPOINT Thread-Index: AciFR8wzKZsRgQfoRO+rmBDARpbSCAAmDfdg From: "Zubkovsky, Sergey" To: "Tom Lane" Cc: X-Transas-MailScanner: Found to be clean X-Transas-MailScanner-From: sergey.zubkovsky@transas.com X-Transas-MailScanner-To: pgsql-docs@postgresql.org, tgl@sss.pgh.pa.us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/42 X-Sequence-Number: 4854 This is a multi-part message in MIME format. ------_=_NextPart_001_01C885E7.56CA5C26 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, =20 Here is my example. We are creating 2 tables: =20 create table t1 ( a int, b varchar( 30 ) ); create table t1_arh ( c text ); =20 and filling 't1': =20 insert into t1 select generate_series(1, 100000 ), generate_series(1, 100000 ); =20 The "arch_table_sp" user-function will be used for extracting data from 't1' and archiving it to 't1_arh'. =20 CREATE OR REPLACE FUNCTION "arch_table_sp" ( tblName name, arcTblName name ) RETURNS void AS $BODY$=20 DECLARE fn text; =20 chunk CONSTANT bigint :=3D 512*1024; off bigint :=3D 0; rdBytes bigint; buf text; =20 BEGIN SELECT setting INTO STRICT fn FROM pg_settings WHERE name =3D 'data_directory'; fn :=3D fn || '/tbldata.txt'; =20 PERFORM pg_file_unlink( fn ); =20 EXECUTE 'COPY ( SELECT * FROM ' || quote_ident( tblName ) || ' ) TO ' || quote_literal( fn ); =20 EXECUTE ' CREATE OR REPLACE FUNCTION "__InsertChunk__sp" ( data text ) RETURNS void AS $_$ INSERT INTO ' || quote_ident( arcTblName ) || ' ( c ) VALUES ( $1 ); $_$ LANGUAGE sql;'; =20 LOOP buf :=3D pg_file_read( fn, off, chunk ); =20 rdBytes :=3D length( buf ); =20 IF ( rdBytes > 0 ) THEN PERFORM "__InsertChunk__sp"( buf ); off :=3D off + rdBytes; END IF; =20 EXIT WHEN ( rdBytes <> chunk ); END LOOP; =20 PERFORM pg_file_unlink( fn ); END; $BODY$ LANGUAGE plpgsql; =20 Now we are executing the following statements in one transaction: =20 select "arch_table_sp"( 't1', 't1_arh' ); select pg_total_relation_size( 't1_arh' ); =20 The result is 417792 (in the general case it may be another value, for example, I received 303104, 573440 and etc). If we are executing these statements in separate transactions with a couple of seconds between them than we have received another value: =20 truncate table t1_arh; =20 select "arch_table_sp"( 't1', 't1_arh' ); =20 select pg_total_relation_size( 't1_arh' ); =20 The result is 688128! With explicit CHECKPOINT we will have one more value: =20 truncate table t1_arh; =20 select "arch_table_sp"( 't1', 't1_arh' ); CHECKPOINT; select pg_total_relation_size( 't1_arh' ); =20 The result is 696320! =20 It would be interesting why we have such results... =20 It's obviously that CHECKPOINT is not a good decision. Can you suggest some other approach instead of explicit CHECKPOINT? =20 Sorry for my English. I hope this example is quite clear. =20 =20 Thanks in advance, Zubkovsky Sergey =20 =20 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Thursday, March 13, 2008 11:21 PM To: Zubkovsky, Sergey Cc: pgsql-docs@postgresql.org Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT =20 "Zubkovsky, Sergey" writes: > I've detected that a result value of pg_total_relation_size() for an > actively updated table might be significantly differ from a result that > is returned after explicit CHECKPOINT command execution. =20 Uh, can you show a specific example of what you mean? =20 > I understand the reasons of such behavior: cache buffers must be flushed > in order to be sure that pg_total_relation_size() result will be like we > expect. =20 I wouldn't think so. The space for a page is allocated immediately when needed --- its *contents* might not be up to date, but that shouldn't affect pg_total_relation_size. =20 regards, tom lane ------_=_NextPart_001_01C885E7.56CA5C26 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

Here is my = example.

We are creating 2 = tables:

create table t1 ( a int, b = varchar( 30 ) );

create table t1_arh ( c text = );

and filling = 't1':

insert into t1 select = generate_series(1, 100000 ), generate_series(1, 100000 );

The = "arch_table_sp" user-function will be used for extracting data from ‘t1’ and archiving it = to ‘t1_arh’.

CREATE OR REPLACE FUNCTION "arch_table_sp" ( tblName name, arcTblName name = )

RETURNS = void

AS $BODY$ =

DECLARE

fn = text;

chunk = CONSTANT bigint :=3D 512*1024;

off bigint = :=3D 0;

rdBytes = bigint;

buf = text;

BEGIN

SELECT = setting INTO STRICT fn FROM pg_settings WHERE name =3D = 'data_directory';

fn :=3D fn || '/tbldata.txt';

PERFORM = pg_file_unlink( fn );

EXECUTE 'COPY = ( SELECT * FROM ' || quote_ident( tblName ) || ' ) TO ' || quote_literal( fn = );

EXECUTE = '

CREATE OR REPLACE FUNCTION "__InsertChunk__sp" ( data text )

RETURNS void = AS

$_$

INSERT INTO ' = || quote_ident( arcTblName ) || ' ( c ) VALUES ( $1 = );

$_$

LANGUAGE = sql;';

LOOP

buf :=3D pg_file_read( fn, off, chunk );

rdBytes :=3D length( buf );

IF ( rdBytes > 0 ) THEN

&nbs= p; PERFORM "__InsertChunk__sp"( buf = );

&nbs= p; off :=3D off + rdBytes;

END IF;

EXIT WHEN ( rdBytes <> chunk );

END = LOOP;

PERFORM = pg_file_unlink( fn );

END;

$BODY$ LANGUAGE = plpgsql;

Now we are executing the = following statements in one transaction:

select = "arch_table_sp"( 't1', 't1_arh' );

select pg_total_relation_size( = 't1_arh' );

The result is 417792 (in = the general case it may be another value, for example, I received 303104, = 573440 and etc).

If we are executing these statements in separate transactions with a couple of seconds between = them than we have received another value:

truncate table = t1_arh;

select = "arch_table_sp"( 't1', 't1_arh' );

select pg_total_relation_size( = 't1_arh' );

The result is = 688128!

With explicit CHECKPOINT = we will have one more value:

truncate table = t1_arh;

select = "arch_table_sp"( 't1', 't1_arh' );

CHECKPOINT;

select pg_total_relation_size( = 't1_arh' );

The result is = 696320!

It would be interesting = why we have such results...

It’s obviously that = CHECKPOINT is not a good decision.

Can you suggest some = other approach instead of explicit CHECKPOINT?

Sorry for my = English.

I hope this example is = quite clear.

Thanks in = advance,

Zubkovsky = Sergey

-----Original Message-----
From: Tom = Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 13, 2008 11:21 PM
To: Zubkovsky, Sergey
Cc: pgsql-docs@postgresql.org
Subject: Re: [DOCS] pg_total_relation_size() and = CHECKPOINT

"Zubkovsky, = Sergey" <Sergey.Zubkovsky@transas.com> = writes:

> I've detected that a result value of = pg_total_relation_size() for an

> actively updated table might be significantly differ from a = result that

> is returned after explicit CHECKPOINT command = execution.

Uh, can you show a specific example of what you = mean?

> I understand the reasons of such behavior: cache buffers = must be flushed

> in order to be sure that pg_total_relation_size() result = will be like we

> expect.

I wouldn't think so. The space for a page is allocated immediately when

needed --- its *contents* might not be up to date, but that = shouldn't

affect pg_total_relation_size.

= ; regards, tom lane

------_=_NextPart_001_01C885E7.56CA5C26-- From tgl@sss.pgh.pa.us Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2485D2E0145; Fri, 14 Mar 2008 13:20:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 05308-06; Fri, 14 Mar 2008 13:19:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 4C9F02E0164; Fri, 14 Mar 2008 13:19:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2EGJKcE019333; Fri, 14 Mar 2008 12:19:20 -0400 (EDT) To: "Zubkovsky, Sergey" cc: pgsql-docs@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: pg_total_relation_size() and CHECKPOINT In-reply-to: <528853D3C5ED2C4AA8990B504BA7FB850106DF13@sol.transas.com> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF13@sol.transas.com> Comments: In-reply-to "Zubkovsky, Sergey" message dated "Fri, 14 Mar 2008 18:23:22 +0300" Date: Fri, 14 Mar 2008 12:19:20 -0400 Message-ID: <19332.1205511560@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/43 X-Sequence-Number: 4855 [ moved to -hackers --- see original thread here http://archives.postgresql.org/pgsql-docs/2008-03/msg00039.php ] "Zubkovsky, Sergey" writes: > Here is my example. Hmm ... on my Fedora machine I get the same result (704512) in all these cases, which is what I'd expect. (The exact value could vary across platforms, of course.) You said you were using the MinGW build --- maybe MinGW's version of stat(2) isn't trustworthy? regards, tom lane From Sergey.Zubkovsky@transas.com Wed Jun 3 19:57:24 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 749E62E0191 for ; Fri, 14 Mar 2008 13:33:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 08928-03 for ; Fri, 14 Mar 2008 13:33:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.transas.com (mail.transas.com [82.140.75.150]) by postgresql.org (Postfix) with ESMTP id 4AF5A2E013F for ; Fri, 14 Mar 2008 13:33:47 -0300 (ADT) Received: from sol.transas.com (sol.transas.com [10.10.0.20]) by mail.transas.com (Postfix) with ESMTP id 5946A500970; Fri, 14 Mar 2008 19:33:38 +0300 (MSK) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT Date: Fri, 14 Mar 2008 19:33:37 +0300 Message-ID: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> In-Reply-To: <19332.1205511560@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [DOCS] pg_total_relation_size() and CHECKPOINT Thread-Index: AciF7zIgstWzM9H6QpetWspKomVOIQAAVezQ From: "Zubkovsky, Sergey" To: "Tom Lane" Cc: X-Transas-MailScanner: Found to be clean X-Transas-MailScanner-From: sergey.zubkovsky@transas.com X-Transas-MailScanner-To: pgsql-hackers@postgresql.org, tgl@sss.pgh.pa.us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/486 X-Sequence-Number: 115287 The previous results were received on PG 8.3 version: "PostgreSQL 8.3.0, compiled by Visual C++ build 1400" -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Friday, March 14, 2008 7:19 PM To: Zubkovsky, Sergey Cc: pgsql-docs@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT [ moved to -hackers --- see original thread here http://archives.postgresql.org/pgsql-docs/2008-03/msg00039.php ] "Zubkovsky, Sergey" writes: > Here is my example. Hmm ... on my Fedora machine I get the same result (704512) in all these cases, which is what I'd expect. (The exact value could vary across platforms, of course.) You said you were using the MinGW build --- maybe MinGW's version of stat(2) isn't trustworthy? regards, tom lane From tgl@sss.pgh.pa.us Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C85FD2E0233 for ; Fri, 14 Mar 2008 14:21:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 26726-03 for ; Fri, 14 Mar 2008 14:21:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 72DBC2E02C8 for ; Fri, 14 Mar 2008 14:21:50 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2EHLnLQ021562; Fri, 14 Mar 2008 13:21:49 -0400 (EDT) To: "Zubkovsky, Sergey" cc: pgsql-hackers@postgresql.org Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT In-reply-to: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> Comments: In-reply-to "Zubkovsky, Sergey" message dated "Fri, 14 Mar 2008 19:33:37 +0300" Date: Fri, 14 Mar 2008 13:21:48 -0400 Message-ID: <21561.1205515308@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/491 X-Sequence-Number: 115292 "Zubkovsky, Sergey" writes: > The previous results were received on PG 8.3 version: > "PostgreSQL 8.3.0, compiled by Visual C++ build 1400" Hmm. I find the whole thing fairly worrisome, because what it suggests is that Windows isn't actually allocating file space during smgrextend, which would mean that we'd be prone to running out of disk space at unfortunate times --- like during a checkpoint, after we've already promised the client the data is committed. Can any Windows hackers look into this and find out what's really happening? regards, tom lane From stark@enterprisedb.com Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E3B7E2E016E for ; Fri, 14 Mar 2008 15:30:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 45217-06 for ; Fri, 14 Mar 2008 15:30:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (unknown [87.127.95.194]) by postgresql.org (Postfix) with ESMTP id 4C76E2E00D5 for ; Fri, 14 Mar 2008 15:30:38 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.69) (envelope-from ) id 1JaEfl-0001ZJ-RS; Fri, 14 Mar 2008 18:30:29 +0000 To: "Tom Lane" Cc: "Zubkovsky\, Sergey" , Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT In-Reply-To: <21561.1205515308@sss.pgh.pa.us> (Tom Lane's message of "Fri\, 14 Mar 2008 13\:21\:48 -0400") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.hackers" 19638) References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> From: Gregory Stark Organization: EnterpriseDB Date: Fri, 14 Mar 2008 18:30:24 +0000 Message-ID: <87myp1f9a7.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/495 X-Sequence-Number: 115296 "Tom Lane" writes: > "Zubkovsky, Sergey" writes: >> The previous results were received on PG 8.3 version: >> "PostgreSQL 8.3.0, compiled by Visual C++ build 1400" > > Hmm. I find the whole thing fairly worrisome, because what it suggests > is that Windows isn't actually allocating file space during smgrextend, > which would mean that we'd be prone to running out of disk space at > unfortunate times --- like during a checkpoint, after we've already > promised the client the data is committed. Surely we can't lose after the fsync? Losing at commit rather than at the time of insert might still be poor, but how could we lose after we've promised the data is committed? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! From tgl@sss.pgh.pa.us Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A39782E1A8C for ; Fri, 14 Mar 2008 17:02:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 79552-04 for ; Fri, 14 Mar 2008 17:02:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C67D42E034A for ; Fri, 14 Mar 2008 17:02:04 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2EK22r8024577; Fri, 14 Mar 2008 16:02:02 -0400 (EDT) To: Gregory Stark cc: "Zubkovsky\, Sergey" , pgsql-hackers@postgresql.org Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT In-reply-to: <87myp1f9a7.fsf@oxford.xeocode.com> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> Comments: In-reply-to Gregory Stark message dated "Fri, 14 Mar 2008 18:30:24 -0000" Date: Fri, 14 Mar 2008 16:02:01 -0400 Message-ID: <24576.1205524921@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/498 X-Sequence-Number: 115299 Gregory Stark writes: > "Tom Lane" writes: >> Hmm. I find the whole thing fairly worrisome, because what it suggests >> is that Windows isn't actually allocating file space during smgrextend, >> which would mean that we'd be prone to running out of disk space at >> unfortunate times --- like during a checkpoint, after we've already >> promised the client the data is committed. > Surely we can't lose after the fsync? Losing at commit rather than at > the time of insert might still be poor, but how could we lose after > we've promised the data is committed? What I'm afraid of is write() returning ENOSPC for a write to a disk block we thought we had allocated previously. If such a situation is persistent we'd be unable to flush dirty data from shared buffers and thus never be able to complete a checkpoint. We'd never *get* to the fsync, so whether the data is safe after fsync is moot. The way it is supposed to work is that ENOSPC ought to happen during smgrextend, that is before we've put any data into a shared buffer corresponding to a new page of the file. With that, we will never be able to commit a transaction that requires disk space we don't have. The real question here is whether Windows' stat() is telling the truth about how much filesystem space has actually been allocated to a file. It seems entirely possible that it's not; but if it is, then I think we have a problem. regards, tom lane From alvherre@commandprompt.com Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6E84A2E0057 for ; Wed, 26 Mar 2008 10:23:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 34588-01 for ; Wed, 26 Mar 2008 10:22:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id AAA742E005E for ; Wed, 26 Mar 2008 10:23:06 -0300 (ADT) Received: from perhan.alvh.no-ip.org (200-126-66-43.bk5-dsl.surnet.cl [200.126.66.43]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m2QDNNnD014444 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Wed, 26 Mar 2008 06:23:25 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 492EF47BFE; Wed, 26 Mar 2008 10:22:50 -0300 (CLST) Date: Wed, 26 Mar 2008 10:22:50 -0300 From: Alvaro Herrera To: Tom Lane Cc: Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT Message-ID: <20080326132250.GK5895@alvh.no-ip.org> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <24576.1205524921@sss.pgh.pa.us> User-Agent: Mutt/1.5.17+20080114 (2008-01-14) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Wed, 26 Mar 2008 06:23:29 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1033 X-Sequence-Number: 115834 Tom Lane wrote: > The real question here is whether Windows' stat() is telling the truth > about how much filesystem space has actually been allocated to a file. > It seems entirely possible that it's not; but if it is, then I think we > have a problem. Has this been examined by a Windows hacker? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. From andrew@dunslane.net Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 872642E0082 for ; Wed, 26 Mar 2008 11:01:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 45064-04 for ; Wed, 26 Mar 2008 11:01:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from alexis.jtlnet.com (alexis.jtlnet.com [69.36.9.81]) by postgresql.org (Postfix) with ESMTP id E5B1E2E005A for ; Wed, 26 Mar 2008 11:01:29 -0300 (ADT) Received: from [192.168.10.103] (cpe-075-177-177-228.nc.res.rr.com [::ffff:75.177.177.228]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by alexis.jtlnet.com with esmtp; Wed, 26 Mar 2008 09:55:40 -0400 id 0007FC5B.47EA55DD.000021ED Message-ID: <47EA5602.6060605@dunslane.net> Date: Wed, 26 Mar 2008 09:56:18 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20071019 Fedora/1.0.9-3.fc6 pango-text SeaMonkey/1.0.9 MIME-Version: 1.0 To: Alvaro Herrera CC: Tom Lane , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> In-Reply-To: <20080326132250.GK5895@alvh.no-ip.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1036 X-Sequence-Number: 115837 Alvaro Herrera wrote: > Tom Lane wrote: > > >> The real question here is whether Windows' stat() is telling the truth >> about how much filesystem space has actually been allocated to a file. >> It seems entirely possible that it's not; but if it is, then I think we >> have a problem. >> > > Has this been examined by a Windows hacker? > > If someone can suggest a test program I'll be happy to run it. cheers andrew From Sergey.Zubkovsky@transas.com Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D5F032E0090 for ; Wed, 26 Mar 2008 11:44:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 46168-03-8 for ; Wed, 26 Mar 2008 11:44:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.transas.com (mail.transas.com [82.140.75.150]) by postgresql.org (Postfix) with ESMTP id 2BEEF2E00A0 for ; Wed, 26 Mar 2008 11:44:26 -0300 (ADT) Received: from sol.transas.com (sol.transas.com [10.10.0.20]) by mail.transas.com (Postfix) with ESMTP id 8CABA507EBE; Wed, 26 Mar 2008 17:44:16 +0300 (MSK) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT Date: Wed, 26 Mar 2008 17:44:16 +0300 Message-ID: <528853D3C5ED2C4AA8990B504BA7FB850106DF29@sol.transas.com> In-Reply-To: <47EA5602.6060605@dunslane.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT Thread-Index: AciPSTv4G1+wnIhIQ8en8AXpo1fFrgABW8dg From: "Zubkovsky, Sergey" To: "Andrew Dunstan" , "Alvaro Herrera" Cc: "Tom Lane" , "Gregory Stark" , , "Magnus Hagander" X-Transas-MailScanner: Found to be clean X-Transas-MailScanner-From: sergey.zubkovsky@transas.com X-Transas-MailScanner-To: alvherre@commandprompt.com, andrew@dunslane.net, magnus@hagander.net, pgsql-hackers@postgresql.org, stark@enterprisedb.com, tgl@sss.pgh.pa.us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1043 X-Sequence-Number: 115844 Can anybody tell me how filesystem space is allocated and point me to the sources if it's possible? I have some experience with programming for Windows and I'll try to investigate this problem. -----Original Message----- From: Andrew Dunstan [mailto:andrew@dunslane.net]=20 Sent: Wednesday, March 26, 2008 4:56 PM To: Alvaro Herrera Cc: Tom Lane; Gregory Stark; Zubkovsky, Sergey; pgsql-hackers@postgresql.org; Magnus Hagander Subject: Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT Alvaro Herrera wrote: > Tom Lane wrote: > > =20 >> The real question here is whether Windows' stat() is telling the truth >> about how much filesystem space has actually been allocated to a file. >> It seems entirely possible that it's not; but if it is, then I think we >> have a problem. >> =20 > > Has this been examined by a Windows hacker? > > =20 If someone can suggest a test program I'll be happy to run it. cheers andrew From tgl@sss.pgh.pa.us Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C5A522E005F for ; Wed, 26 Mar 2008 12:42:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 75467-05 for ; Wed, 26 Mar 2008 12:41:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 30DB92E0057 for ; Wed, 26 Mar 2008 12:41:44 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2QFfWj9021663; Wed, 26 Mar 2008 11:41:32 -0400 (EDT) To: Andrew Dunstan cc: Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT In-reply-to: <47EA5602.6060605@dunslane.net> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> Comments: In-reply-to Andrew Dunstan message dated "Wed, 26 Mar 2008 09:56:18 -0400" Date: Wed, 26 Mar 2008 11:41:32 -0400 Message-ID: <21662.1206546092@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1050 X-Sequence-Number: 115851 Andrew Dunstan writes: > Alvaro Herrera wrote: >> Tom Lane wrote: >>> The real question here is whether Windows' stat() is telling the truth >>> about how much filesystem space has actually been allocated to a file. >>> It seems entirely possible that it's not; but if it is, then I think we >>> have a problem. >> Has this been examined by a Windows hacker? > If someone can suggest a test program I'll be happy to run it. One thing that would be good is just to see who else can reproduce the original observation: http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php It might occur only on some versions of Windows, for instance. regards, tom lane From andrew@dunslane.net Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 81ECB2E0059 for ; Wed, 26 Mar 2008 21:03:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67114-07 for ; Wed, 26 Mar 2008 21:03:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from alexis.jtlnet.com (alexis.jtlnet.com [69.36.9.81]) by postgresql.org (Postfix) with ESMTP id A1C082E0057 for ; Wed, 26 Mar 2008 21:03:51 -0300 (ADT) Received: from [192.168.10.103] (cpe-075-177-177-228.nc.res.rr.com [::ffff:75.177.177.228]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by alexis.jtlnet.com with esmtp; Wed, 26 Mar 2008 20:03:07 -0400 id 00080569.47EAE43B.00001C36 Message-ID: <47EAE45D.8050902@dunslane.net> Date: Wed, 26 Mar 2008 20:03:41 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20071019 Fedora/1.0.9-3.fc6 pango-text SeaMonkey/1.0.9 MIME-Version: 1.0 To: Tom Lane CC: Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> In-Reply-To: <21662.1206546092@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1087 X-Sequence-Number: 115888 Tom Lane wrote: > Andrew Dunstan writes: > >> Alvaro Herrera wrote: >> >>> Tom Lane wrote: >>> >>>> The real question here is whether Windows' stat() is telling the truth >>>> about how much filesystem space has actually been allocated to a file. >>>> It seems entirely possible that it's not; but if it is, then I think we >>>> have a problem. >>>> > > >>> Has this been examined by a Windows hacker? >>> > > >> If someone can suggest a test program I'll be happy to run it. >> > > One thing that would be good is just to see who else can reproduce > the original observation: > http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php > > It might occur only on some versions of Windows, for instance. > > > I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 host. cheers andrew From tgl@sss.pgh.pa.us Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 246B12E006F for ; Wed, 26 Mar 2008 22:09:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 76640-05 for ; Wed, 26 Mar 2008 22:09:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8595D2E0064 for ; Wed, 26 Mar 2008 22:09:18 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2R18vJb021045; Wed, 26 Mar 2008 21:08:57 -0400 (EDT) To: Andrew Dunstan cc: Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT In-reply-to: <47EAE45D.8050902@dunslane.net> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> Comments: In-reply-to Andrew Dunstan message dated "Wed, 26 Mar 2008 20:03:41 -0400" Date: Wed, 26 Mar 2008 21:08:56 -0400 Message-ID: <21044.1206580136@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1088 X-Sequence-Number: 115889 Andrew Dunstan writes: > Tom Lane wrote: >>> The real question here is whether Windows' stat() is telling the truth >>> about how much filesystem space has actually been allocated to a file. >> >> One thing that would be good is just to see who else can reproduce >> the original observation: >> http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php > I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 > host. OK, so the next question is do we really have an issue, or is this just an observational artifact? What I'd try is deliberately running the machine out of disk space with a long series of inserts, and then see whether subsequent checkpoint attempts fail due to ENOSPC errors while trying to write out dirty buffers. To avoid conflating this effect with anything else, it'd be best if you could put the DB on its own small partition, and *not* put pg_xlog there. regards, tom lane From andrew@dunslane.net Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id F2C262E0057 for ; Wed, 26 Mar 2008 23:37:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 22670-04 for ; Wed, 26 Mar 2008 23:37:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from alexis.jtlnet.com (alexis.jtlnet.com [69.36.9.81]) by postgresql.org (Postfix) with ESMTP id E63722E0056 for ; Wed, 26 Mar 2008 23:37:16 -0300 (ADT) Received: from [192.168.10.103] (cpe-075-177-177-228.nc.res.rr.com [::ffff:75.177.177.228]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by alexis.jtlnet.com with esmtp; Wed, 26 Mar 2008 22:36:30 -0400 id 00080574.47EB082E.000038C5 Message-ID: <47EB0850.1050805@dunslane.net> Date: Wed, 26 Mar 2008 22:37:04 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20071019 Fedora/1.0.9-3.fc6 pango-text SeaMonkey/1.0.9 MIME-Version: 1.0 To: Tom Lane CC: Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> <21044.1206580136@sss.pgh.pa.us> In-Reply-To: <21044.1206580136@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1089 X-Sequence-Number: 115890 Tom Lane wrote: > Andrew Dunstan writes: > >> Tom Lane wrote: >> >>>> The real question here is whether Windows' stat() is telling the truth >>>> about how much filesystem space has actually been allocated to a file. >>>> >>> One thing that would be good is just to see who else can reproduce >>> the original observation: >>> http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php >>> > > >> I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 >> host. >> > > OK, so the next question is do we really have an issue, or is this just > an observational artifact? What I'd try is deliberately running the > machine out of disk space with a long series of inserts, and then see > whether subsequent checkpoint attempts fail due to ENOSPC errors while > trying to write out dirty buffers. > > To avoid conflating this effect with anything else, it'd be best if you > could put the DB on its own small partition, and *not* put pg_xlog > there. > > > I'm working on this (thank goodness for junctions). Maybe we shopuld look at providing a config setting for pg_xlog. cheers andrew From andrew@dunslane.net Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B6EF12E004A for ; Thu, 27 Mar 2008 00:25:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50124-07 for ; Thu, 27 Mar 2008 00:25:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from alexis.jtlnet.com (alexis.jtlnet.com [69.36.9.81]) by postgresql.org (Postfix) with ESMTP id A34252E0031 for ; Thu, 27 Mar 2008 00:25:54 -0300 (ADT) Received: from [192.168.10.103] (cpe-075-177-177-228.nc.res.rr.com [::ffff:75.177.177.228]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by alexis.jtlnet.com with esmtp; Wed, 26 Mar 2008 23:25:10 -0400 id 000804D0.47EB1396.0000063B Message-ID: <47EB13BF.8080301@dunslane.net> Date: Wed, 26 Mar 2008 23:25:51 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20071019 Fedora/1.0.9-3.fc6 pango-text SeaMonkey/1.0.9 MIME-Version: 1.0 To: Tom Lane CC: Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> <21044.1206580136@sss.pgh.pa.us> In-Reply-To: <21044.1206580136@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1090 X-Sequence-Number: 115891 Tom Lane wrote: > Andrew Dunstan writes: > >> Tom Lane wrote: >> >>>> The real question here is whether Windows' stat() is telling the truth >>>> about how much filesystem space has actually been allocated to a file. >>>> >>> One thing that would be good is just to see who else can reproduce >>> the original observation: >>> http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php >>> > > >> I have reproduced it in XP-Pro/SP2 running in a VMWare machine on an FC6 >> host. >> > > OK, so the next question is do we really have an issue, or is this just > an observational artifact? What I'd try is deliberately running the > machine out of disk space with a long series of inserts, and then see > whether subsequent checkpoint attempts fail due to ENOSPC errors while > trying to write out dirty buffers. > > To avoid conflating this effect with anything else, it'd be best if you > could put the DB on its own small partition, and *not* put pg_xlog > there. > > > OK, a very large insert failed as expected. Checkpoint succeeded. Then vacuum recovered the space. I suspect that the size reported by stat() is a little delayed here, but the file system is keeping proper track of it, so the lseek that tries to extend the file fails at the right spot. cheers andrew From tgl@sss.pgh.pa.us Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 989952E0096 for ; Thu, 27 Mar 2008 01:13:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 67142-05 for ; Thu, 27 Mar 2008 01:13:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B01992E00A2 for ; Thu, 27 Mar 2008 01:13:55 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2R4DggJ000969; Thu, 27 Mar 2008 00:13:42 -0400 (EDT) To: Andrew Dunstan cc: Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT In-reply-to: <47EB13BF.8080301@dunslane.net> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> <21044.1206580136@sss.pgh.pa.us> <47EB13BF.8080301@dunslane.net> Comments: In-reply-to Andrew Dunstan message dated "Wed, 26 Mar 2008 23:25:51 -0400" Date: Thu, 27 Mar 2008 00:13:42 -0400 Message-ID: <968.1206591222@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1091 X-Sequence-Number: 115892 Andrew Dunstan writes: > I suspect that the size reported by stat() is a little delayed here, but > the file system is keeping proper track of it, so the lseek that tries > to extend the file fails at the right spot. Hmm. If it really works that way, one would hope Microsoft would've documented that someplace. Can anyone find a statement that Windows' stat() is not current? regards, tom lane From magnus@hagander.net Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1F5482E004A for ; Thu, 27 Mar 2008 04:32:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 64079-06 for ; Thu, 27 Mar 2008 04:32:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from svr2.hagander.net (svr2.hagander.net [88.198.128.226]) by postgresql.org (Postfix) with ESMTP id 577D22E0039 for ; Thu, 27 Mar 2008 04:32:26 -0300 (ADT) Received: from dynamic.hagander.net ([127.0.0.1]) (encrypted and authenticated) by svr2.hagander.net (Postfix) with ESMTP id 2CB1DDCC9C8; Thu, 27 Mar 2008 08:32:24 +0100 (CET) Received: from mha-laptop (localhost [127.0.0.1]) by mha-laptop.hagander.net (Postfix) with ESMTP id 4AA27FF09C; Thu, 27 Mar 2008 08:33:42 +0100 (CET) Date: Thu, 27 Mar 2008 08:33:41 +0100 From: Magnus Hagander To: Tom Lane Cc: Andrew Dunstan , Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT Message-ID: <20080327083341.260e222e@mha-laptop> In-Reply-To: <968.1206591222@sss.pgh.pa.us> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> <21044.1206580136@sss.pgh.pa.us> <47EB13BF.8080301@dunslane.net> <968.1206591222@sss.pgh.pa.us> X-Mailer: Claws Mail 3.3.1 (GTK+ 2.12.0; i486-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1093 X-Sequence-Number: 115894 On Thu, 27 Mar 2008 00:13:42 -0400 Tom Lane wrote: > Andrew Dunstan writes: > > I suspect that the size reported by stat() is a little delayed > > here, but the file system is keeping proper track of it, so the > > lseek that tries to extend the file fails at the right spot. > > Hmm. If it really works that way, one would hope Microsoft would've > documented that someplace. Can anyone find a statement that Windows' > stat() is not current? I'm not in a position to test it myself now (doing training, and then I'll be off to pg-east...), but it'd be interesting to see if it acts the same way with GetFileSize(), or if it's just stat()... /Magnus From alvherre@commandprompt.com Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 53B042E0049 for ; Thu, 27 Mar 2008 08:15:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 31373-05 for ; Thu, 27 Mar 2008 08:15:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id A29D42E0060 for ; Thu, 27 Mar 2008 08:15:23 -0300 (ADT) Received: from perhan.alvh.no-ip.org (200-126-66-43.bk5-dsl.surnet.cl [200.126.66.43]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m2RBFV8C020531 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 27 Mar 2008 04:15:33 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 4A38147C11; Thu, 27 Mar 2008 08:14:57 -0300 (CLST) Date: Thu, 27 Mar 2008 08:14:57 -0300 From: Alvaro Herrera To: Andrew Dunstan Cc: Tom Lane , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT Message-ID: <20080327111457.GA4906@alvh.no-ip.org> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> <21044.1206580136@sss.pgh.pa.us> <47EB0850.1050805@dunslane.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <47EB0850.1050805@dunslane.net> User-Agent: Mutt/1.5.17+20080114 (2008-01-14) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Thu, 27 Mar 2008 04:15:36 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1095 X-Sequence-Number: 115896 Andrew Dunstan wrote: > I'm working on this (thank goodness for junctions). Maybe we shopuld > look at providing a config setting for pg_xlog. I hope you mean an initdb switch -- otherwise it is way too easy to misuse. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support From Sergey.Zubkovsky@transas.com Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 53EC42E0039 for ; Thu, 27 Mar 2008 09:31:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73812-07 for ; Thu, 27 Mar 2008 09:31:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.transas.com (mail.transas.com [82.140.75.150]) by postgresql.org (Postfix) with ESMTP id 50D5E2E0031 for ; Thu, 27 Mar 2008 09:31:28 -0300 (ADT) Received: from sol.transas.com (sol.transas.com [10.10.0.20]) by mail.transas.com (Postfix) with ESMTP id D22FF509365; Thu, 27 Mar 2008 15:30:01 +0300 (MSK) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----_=_NextPart_001_01C89006.46FCD90C" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT Date: Thu, 27 Mar 2008 15:30:01 +0300 Message-ID: <528853D3C5ED2C4AA8990B504BA7FB850106DF2A@sol.transas.com> In-Reply-To: <968.1206591222@sss.pgh.pa.us> X-MS-Has-Attach: yes X-MS-TNEF-Correlator: Thread-Topic: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT Thread-Index: AciPwP7PV+PgMF+dS7yGm7HNs7L5PQAQ2M4Q From: "Zubkovsky, Sergey" To: "Tom Lane" , "Andrew Dunstan" Cc: "Alvaro Herrera" , "Gregory Stark" , , "Magnus Hagander" X-Transas-MailScanner: Found to be clean X-Transas-MailScanner-From: sergey.zubkovsky@transas.com X-Transas-MailScanner-To: alvherre@commandprompt.com, andrew@dunslane.net, magnus@hagander.net, pgsql-hackers@postgresql.org, stark@enterprisedb.com, tgl@sss.pgh.pa.us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1099 X-Sequence-Number: 115900 This is a multi-part message in MIME format. ------_=_NextPart_001_01C89006.46FCD90C Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Maybe this helps: "It is not an error to set a file pointer to a position beyond the end of the file. The size of the file does not increase until you call the SetEndOfFile, WriteFile, or WriteFileEx function. A write operation increases the size of the file to the file pointer position plus the size of the buffer written, which results in the intervening bytes uninitialized." http://msdn2.microsoft.com/en-us/library/aa365541(VS.85).aspx According to Windows' lseek implementation (attached) SetEndOfFile() isn't called for this case. Thanks, Sergey Zubkovsky -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Thursday, March 27, 2008 7:14 AM To: Andrew Dunstan Cc: Alvaro Herrera; Gregory Stark; Zubkovsky, Sergey; pgsql-hackers@postgresql.org; Magnus Hagander Subject: Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT Andrew Dunstan writes: > I suspect that the size reported by stat() is a little delayed here, but=20 > the file system is keeping proper track of it, so the lseek that tries > to extend the file fails at the right spot. Hmm. If it really works that way, one would hope Microsoft would've documented that someplace. Can anyone find a statement that Windows' stat() is not current? regards, tom lane ------_=_NextPart_001_01C89006.46FCD90C Content-Type: application/octet-stream; name="lseek.c" Content-Transfer-Encoding: base64 Content-Description: lseek.c Content-Disposition: attachment; filename="lseek.c" LyoqKg0KKmxzZWVrLmMgLSBjaGFuZ2UgZmlsZSBwb3NpdGlvbg0KKg0KKiAgICAgICBDb3B5cmln aHQgKGMpIE1pY3Jvc29mdCBDb3Jwb3JhdGlvbi4gQWxsIHJpZ2h0cyByZXNlcnZlZC4NCioNCipQ dXJwb3NlOg0KKiAgICAgICBkZWZpbmVzIF9sc2VlaygpIC0gbW92ZSB0aGUgZmlsZSBwb2ludGVy DQoqDQoqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq KioqKioqKioqKioqKioqKioqKioqKioqKioqLw0KDQojaW5jbHVkZSA8Y3J1bnRpbWUuaD4NCiNp bmNsdWRlIDxvc2NhbGxzLmg+DQojaW5jbHVkZSA8bXRkbGwuaD4NCiNpbmNsdWRlIDxpby5oPg0K I2luY2x1ZGUgPGludGVybmFsLmg+DQojaW5jbHVkZSA8c3RkbGliLmg+DQojaW5jbHVkZSA8ZXJy bm8uaD4NCiNpbmNsdWRlIDxtc2Rvcy5oPg0KI2luY2x1ZGUgPHN0ZGlvLmg+DQoNCi8qKioNCips b25nIF9sc2VlayhmaCxwb3MsbXRoZCkgLSBtb3ZlIHRoZSBmaWxlIHBvaW50ZXINCioNCipQdXJw b3NlOg0KKiAgICAgICBNb3ZlcyB0aGUgZmlsZSBwb2ludGVyIGFzc29jaWF0ZWQgd2l0aCBmaCB0 byBhIG5ldyBwb3NpdGlvbi4NCiogICAgICAgVGhlIG5ldyBwb3NpdGlvbiBpcyBwb3MgYnl0ZXMg KHBvcyBtYXkgYmUgbmVnYXRpdmUpIGF3YXkNCiogICAgICAgZnJvbSB0aGUgb3JpZ2luIHNwZWNp ZmllZCBieSBtdGhkLg0KKg0KKiAgICAgICBJZiBtdGhkID09IFNFRUtfU0VULCB0aGUgb3JpZ2lu IGluIHRoZSBiZWdpbm5pbmcgb2YgZmlsZQ0KKiAgICAgICBJZiBtdGhkID09IFNFRUtfQ1VSLCB0 aGUgb3JpZ2luIGlzIHRoZSBjdXJyZW50IGZpbGUgcG9pbnRlciBwb3NpdGlvbg0KKiAgICAgICBJ ZiBtdGhkID09IFNFRUtfRU5ELCB0aGUgb3JpZ2luIGlzIHRoZSBlbmQgb2YgdGhlIGZpbGUNCioN CiogICAgICAgTXVsdGktdGhyZWFkOg0KKiAgICAgICBfbHNlZWsoKSAgICA9IGxvY2tzL3VubG9j a3MgdGhlIGZpbGUNCiogICAgICAgX2xzZWVrX25vbG9jaygpID0gZG9lcyBOT1QgbG9jay91bmxv Y2sgdGhlIGZpbGUgKGl0IGlzIGFzc3VtZWQgdGhhdA0KKiAgICAgICAgICAgICAgICAgICAgIHRo ZSBjYWxsZXIgaGFzIHRoZSBhcXVpcmVkIHRoZSBmaWxlIGxvY2ssaWYgbmVlZGVkKS4NCioNCipF bnRyeToNCiogICAgICAgaW50IGZoIC0gZmlsZSBoYW5kbGUgdG8gbW92ZSBmaWxlIHBvaW50ZXIg b24NCiogICAgICAgbG9uZyBwb3MgLSBwb3NpdGlvbiB0byBtb3ZlIHRvLCByZWxhdGl2ZSB0byBv cmlnaW4NCiogICAgICAgaW50IG10aGQgLSBzcGVjaWZpZXMgdGhlIG9yaWdpbiBwb3MgaXMgcmVs YXRpdmUgdG8gKHNlZSBhYm92ZSkNCioNCipFeGl0Og0KKiAgICAgICByZXR1cm5zIHRoZSBvZmZz ZXQsIGluIGJ5dGVzLCBvZiB0aGUgbmV3IHBvc2l0aW9uIGZyb20gdGhlIGJlZ2lubmluZw0KKiAg ICAgICBvZiB0aGUgZmlsZS4NCiogICAgICAgcmV0dXJucyAtMUwgKGFuZCBzZXRzIGVycm5vKSBp ZiBmYWlscy4NCiogICAgICAgTm90ZSB0aGF0IHNlZWtpbmcgYmV5b25kIHRoZSBlbmQgb2YgdGhl IGZpbGUgaXMgbm90IGFuIGVycm9yLg0KKiAgICAgICAoYWx0aG91Z2ggc2Vla2luZyBiZWZvcmUg dGhlIGJlZ2lubmluZyBpcy4pDQoqDQoqRXhjZXB0aW9uczoNCioNCioqKioqKioqKioqKioqKioq KioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq KioqKiovDQoNCg0KLyogZGVmaW5lIGxvY2tpbmcvdmFsaWRhdGluZyBsc2VlayAqLw0KbG9uZyBf X2NkZWNsIF9sc2VlayAoDQogICAgICAgIGludCBmaCwNCiAgICAgICAgbG9uZyBwb3MsDQogICAg ICAgIGludCBtdGhkDQogICAgICAgICkNCnsNCiAgICAgICAgaW50IHI7DQoNCiAgICAgICAgLyog dmFsaWRhdGUgZmggKi8NCiAgICAgICAgX0NIRUNLX0ZIX0NMRUFSX09TU0VSUl9SRVRVUk4oIGZo LCBFQkFERiwgLTEgKTsNCiAgICAgICAgX1ZBTElEQVRFX0NMRUFSX09TU0VSUl9SRVRVUk4oKGZo ID49IDAgJiYgKHVuc2lnbmVkKWZoIDwgKHVuc2lnbmVkKV9uaGFuZGxlKSwgRUJBREYsIC0xKTsN CiAgICAgICAgX1ZBTElEQVRFX0NMRUFSX09TU0VSUl9SRVRVUk4oKF9vc2ZpbGUoZmgpICYgRk9Q RU4pLCBFQkFERiwgLTEpOw0KDQogICAgICAgIF9sb2NrX2ZoKGZoKTsgICAgICAgICAgICAgICAg ICAgLyogbG9jayBmaWxlIGhhbmRsZSAqLw0KDQogICAgICAgIF9fdHJ5IHsNCiAgICAgICAgICAg ICAgICBpZiAoIF9vc2ZpbGUoZmgpICYgRk9QRU4gKQ0KICAgICAgICAgICAgICAgICAgICAgICAg ciA9IF9sc2Vla19ub2xvY2soZmgsIHBvcywgbXRoZCk7ICAgLyogc2VlayAqLw0KICAgICAgICAg ICAgICAgIGVsc2Ugew0KICAgICAgICAgICAgICAgICAgICAgICAgZXJybm8gPSBFQkFERjsNCiAg ICAgICAgICAgICAgICAgICAgICAgIF9kb3NlcnJubyA9IDA7DQogICAgICAgICAgICAgICAgICAg ICAgICByID0gLTE7DQogICAgICAgICAgICAgICAgICAgICAgICBfQVNTRVJURSgoIkludmFsaWQg ZmlsZSBkZXNjcmlwdG9yLiBGaWxlIHBvc3NpYmx5IGNsb3NlZCBieSBhIGRpZmZlcmVudCB0aHJl YWQiLDApKTsNCiAgICAgICAgICAgICAgICB9DQogICAgICAgIH0NCiAgICAgICAgX19maW5hbGx5 IHsNCiAgICAgICAgICAgICAgICBfdW5sb2NrX2ZoKGZoKTsgICAgICAgICAvKiB1bmxvY2sgZmls ZSBoYW5kbGUgKi8NCiAgICAgICAgfQ0KDQogICAgICAgIHJldHVybiByOw0KfQ0KDQovKiBkZWZp bmUgY29yZSBfbHNlZWsgLS0gZG9lc24ndCBsb2NrIG9yIHZhbGlkYXRlIGZoICovDQpsb25nIF9f Y2RlY2wgX2xzZWVrX25vbG9jayAoDQogICAgICAgIGludCBmaCwNCiAgICAgICAgbG9uZyBwb3Ms DQogICAgICAgIGludCBtdGhkDQogICAgICAgICkNCnsNCiAgICAgICAgVUxPTkcgbmV3cG9zOyAg ICAgICAgICAgICAgICAgICAvKiBuZXcgZmlsZSBwb3NpdGlvbiAqLw0KICAgICAgICBVTE9ORyBk b3NyZXR2YWw7ICAgICAgICAgICAgICAgIC8qIG8ucy4gcmV0dXJuIHZhbHVlICovDQogICAgICAg IEhBTkRMRSBvc0hhbmRsZTsgICAgICAgIC8qIG8ucy4gaGFuZGxlIHZhbHVlICovDQoNCg0KICAg ICAgICAvKiB0ZWxsIG8ucy4gdG8gc2VlayAqLw0KDQojaWYgU0VFS19TRVQgIT0gRklMRV9CRUdJ TiB8fCBTRUVLX0NVUiAhPSBGSUxFX0NVUlJFTlQgfHwgU0VFS19FTkQgIT0gRklMRV9FTkQNCiAg ICAjZXJyb3IgWGVuaXggYW5kIFdpbjMyIHNlZWsgY29uc3RhbnRzIG5vdCBjb21wYXRpYmxlDQoj ZW5kaWYgIC8qIFNFRUtfU0VUICE9IEZJTEVfQkVHSU4gfHwgU0VFS19DVVIgIT0gRklMRV9DVVJS RU5UIHx8IFNFRUtfRU5EICE9IEZJTEVfRU5EICovDQogICAgICAgIGlmICgob3NIYW5kbGUgPSAo SEFORExFKV9nZXRfb3NmaGFuZGxlKGZoKSkgPT0gKEhBTkRMRSktMSkNCiAgICAgICAgew0KICAg ICAgICAgICAgZXJybm8gPSBFQkFERjsNCiAgICAgICAgICAgIF9BU1NFUlRFKCgiSW52YWxpZCBm aWxlIGRlc2NyaXB0b3IiLDApKTsNCiAgICAgICAgICAgIHJldHVybiAtMTsNCiAgICAgICAgfQ0K DQogICAgICAgIGlmICgobmV3cG9zID0gU2V0RmlsZVBvaW50ZXIob3NIYW5kbGUsIHBvcywgTlVM TCwgbXRoZCkpID09IC0xKQ0KICAgICAgICAgICAgICAgIGRvc3JldHZhbCA9IEdldExhc3RFcnJv cigpOw0KICAgICAgICBlbHNlDQogICAgICAgICAgICAgICAgZG9zcmV0dmFsID0gMDsNCg0KICAg ICAgICBpZiAoZG9zcmV0dmFsKSB7DQogICAgICAgICAgICAgICAgLyogby5zLiBlcnJvciAqLw0K ICAgICAgICAgICAgICAgIF9kb3NtYXBlcnIoZG9zcmV0dmFsKTsNCiAgICAgICAgICAgICAgICBy ZXR1cm4gLTE7DQogICAgICAgIH0NCg0KICAgICAgICBfb3NmaWxlKGZoKSAmPSB+RkVPRkxBRzsg ICAgICAgIC8qIGNsZWFyIHRoZSBjdHJsLXogZmxhZyBvbiB0aGUgZmlsZSAqLw0KICAgICAgICBy ZXR1cm4gbmV3cG9zOyAgICAgICAgICAgICAgICAgIC8qIHJldHVybiAqLw0KfQ0K ------_=_NextPart_001_01C89006.46FCD90C-- From andrew@dunslane.net Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F11932E0059 for ; Thu, 27 Mar 2008 09:54:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 59436-10 for ; Thu, 27 Mar 2008 09:54:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from alexis.jtlnet.com (alexis.jtlnet.com [69.36.9.81]) by postgresql.org (Postfix) with ESMTP id 57C352E0057 for ; Thu, 27 Mar 2008 09:54:24 -0300 (ADT) Received: from [192.168.10.103] (cpe-075-177-177-228.nc.res.rr.com [::ffff:75.177.177.228]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by alexis.jtlnet.com with esmtp; Thu, 27 Mar 2008 08:54:15 -0400 id 00080627.47EB98F8.00007E52 Message-ID: <47EB98EA.7020402@dunslane.net> Date: Thu, 27 Mar 2008 08:54:02 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20071019 Fedora/1.0.9-3.fc6 pango-text SeaMonkey/1.0.9 MIME-Version: 1.0 To: "Zubkovsky, Sergey" CC: Tom Lane , Alvaro Herrera , Gregory Stark , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT References: <528853D3C5ED2C4AA8990B504BA7FB850106DF2A@sol.transas.com> In-Reply-To: <528853D3C5ED2C4AA8990B504BA7FB850106DF2A@sol.transas.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1100 X-Sequence-Number: 115901 Zubkovsky, Sergey wrote: > Maybe this helps: > > "It is not an error to set a file pointer to a position beyond the end > of the file. The size of the file does not increase until you call the > SetEndOfFile, WriteFile, or WriteFileEx function. A write operation > increases the size of the file to the file pointer position plus the > size of the buffer written, which results in the intervening bytes > uninitialized." > > http://msdn2.microsoft.com/en-us/library/aa365541(VS.85).aspx > > According to Windows' lseek implementation (attached) SetEndOfFile() > isn't called for this case. > > > Yes, but we immediately follow the lseek bye a write(). See src/backend/storage/smgr/md.c:mdextend() . cheers andrew From tgl@sss.pgh.pa.us Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8CE862E006B for ; Thu, 27 Mar 2008 10:54:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 92370-02 for ; Thu, 27 Mar 2008 10:54:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B9C2D2E005E for ; Thu, 27 Mar 2008 10:54:34 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2RDsCe0008081; Thu, 27 Mar 2008 09:54:13 -0400 (EDT) To: Alvaro Herrera cc: Andrew Dunstan , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT In-reply-to: <20080327111457.GA4906@alvh.no-ip.org> References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> <21044.1206580136@sss.pgh.pa.us> <47EB0850.1050805@dunslane.net> <20080327111457.GA4906@alvh.no-ip.org> Comments: In-reply-to Alvaro Herrera message dated "Thu, 27 Mar 2008 08:14:57 -0300" Date: Thu, 27 Mar 2008 09:54:12 -0400 Message-ID: <8080.1206626052@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1102 X-Sequence-Number: 115903 Alvaro Herrera writes: > Andrew Dunstan wrote: >> I'm working on this (thank goodness for junctions). Maybe we shopuld >> look at providing a config setting for pg_xlog. > I hope you mean an initdb switch -- otherwise it is way too easy to > misuse. There's one already .. regards, tom lane From andrew@dunslane.net Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AB2BB2E0059 for ; Thu, 27 Mar 2008 11:30:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30849-02 for ; Thu, 27 Mar 2008 11:30:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from alexis.jtlnet.com (alexis.jtlnet.com [69.36.9.81]) by postgresql.org (Postfix) with ESMTP id 972112E0049 for ; Thu, 27 Mar 2008 11:30:13 -0300 (ADT) Received: from [192.168.10.103] (cpe-075-177-177-228.nc.res.rr.com [::ffff:75.177.177.228]) (TLS: TLSv1/SSLv3,256bits,AES256-SHA) by alexis.jtlnet.com with esmtp; Thu, 27 Mar 2008 10:30:07 -0400 id 000804CF.47EBAF70.00005A8A Message-ID: <47EBAF40.60405@dunslane.net> Date: Thu, 27 Mar 2008 10:29:20 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20071019 Fedora/1.0.9-3.fc6 pango-text SeaMonkey/1.0.9 MIME-Version: 1.0 To: Tom Lane CC: Alvaro Herrera , Gregory Stark , "Zubkovsky, Sergey" , pgsql-hackers@postgresql.org, Magnus Hagander Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT References: <528853D3C5ED2C4AA8990B504BA7FB850106DF14@sol.transas.com> <21561.1205515308@sss.pgh.pa.us> <87myp1f9a7.fsf@oxford.xeocode.com> <24576.1205524921@sss.pgh.pa.us> <20080326132250.GK5895@alvh.no-ip.org> <47EA5602.6060605@dunslane.net> <21662.1206546092@sss.pgh.pa.us> <47EAE45D.8050902@dunslane.net> <21044.1206580136@sss.pgh.pa.us> <47EB0850.1050805@dunslane.net> <20080327111457.GA4906@alvh.no-ip.org> <8080.1206626052@sss.pgh.pa.us> In-Reply-To: <8080.1206626052@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1103 X-Sequence-Number: 115904 Tom Lane wrote: > Alvaro Herrera writes: > >> Andrew Dunstan wrote: >> >>> I'm working on this (thank goodness for junctions). Maybe we shopuld >>> look at providing a config setting for pg_xlog. >>> > > >> I hope you mean an initdb switch -- otherwise it is way too easy to >> misuse. >> > > There's one already .. > > > heh, the things that creep up on you when you're not looking ... cheers andrew From Sergey.Zubkovsky@transas.com Wed Jun 3 19:57:25 2026 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3DC942E0050 for ; Fri, 28 Mar 2008 12:43:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87892-09 for ; Fri, 28 Mar 2008 12:43:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.transas.com (mail.transas.com [82.140.75.150]) by postgresql.org (Postfix) with ESMTP id 186FF2E0038 for ; Fri, 28 Mar 2008 12:43:39 -0300 (ADT) Received: from sol.transas.com (sol.transas.com [10.10.0.20]) by mail.transas.com (Postfix) with ESMTP id A3FBD50A7D8; Fri, 28 Mar 2008 18:43:29 +0300 (MSK) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT Date: Fri, 28 Mar 2008 18:43:29 +0300 Message-ID: <528853D3C5ED2C4AA8990B504BA7FB850106DF2E@sol.transas.com> In-Reply-To: <47EB98EA.7020402@dunslane.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT Thread-Index: AciQCbbzLig5TxtsR4e+tuhZLNe2GgA1vZYw From: "Zubkovsky, Sergey" To: "Andrew Dunstan" Cc: "Tom Lane" , "Alvaro Herrera" , "Gregory Stark" , , "Magnus Hagander" X-Transas-MailScanner: Found to be clean X-Transas-MailScanner-From: sergey.zubkovsky@transas.com X-Transas-MailScanner-To: alvherre@commandprompt.com, andrew@dunslane.net, magnus@hagander.net, pgsql-hackers@postgresql.org, stark@enterprisedb.com, tgl@sss.pgh.pa.us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/1182 X-Sequence-Number: 115983 It seems I've found the cause and the workaround of the problem. MSVC's stat() is implemented by using FindNextFile(). MSDN contains the following suspicious paragraph =C1bout FindNextFile(): "In rare cases, file attribute information on NTFS file systems may not = be current at the time you call this function. To obtain the current = NTFS file system file attributes, call GetFileInformationByHandle." Since we generally cannot open an examined file, we need another way. In the prepared custom build of PG 8.3.1 the native MSVC's stat() was = rewrote by adding GetFileAttributesEx() to correct stat's st_size value. I had seen that a result of MSVC's stat() and a result of = GetFileAttributesEx() may be differ by the file size values at least. The most important thing is the test in the original post ( http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php ) doesn't reproduce any inconsistence now. All work fine. This was tested on my WinXP SP2 platform but I suppose it will work on = any NT-based OS. Thanks, Sergey Zubkovsky -----Original Message----- From: Andrew Dunstan [mailto:andrew@dunslane.net]=20 Sent: Thursday, March 27, 2008 3:54 PM To: Zubkovsky, Sergey Cc: Tom Lane; Alvaro Herrera; Gregory Stark; = pgsql-hackers@postgresql.org; Magnus Hagander Subject: Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT Zubkovsky, Sergey wrote: > Maybe this helps: > > "It is not an error to set a file pointer to a position beyond the end > of the file. The size of the file does not increase until you call the > SetEndOfFile, WriteFile, or WriteFileEx function. A write operation > increases the size of the file to the file pointer position plus the > size of the buffer written, which results in the intervening bytes > uninitialized." > > http://msdn2.microsoft.com/en-us/library/aa365541(VS.85).aspx > > According to Windows' lseek implementation (attached) SetEndOfFile() > isn't called for this case. > > =20 > Yes, but we immediately follow the lseek bye a write(). See=20 src/backend/storage/smgr/md.c:mdextend() . cheers andrew