Обсуждение: pg_total_relation_size() and CHECKPOINT

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

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 := 512*1024;

    off bigint := 0;

    rdBytes bigint;

    buf text;

BEGIN

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

    fn := 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 := pg_file_read( fn, off, chunk );

        rdBytes := length( buf );

        IF ( rdBytes > 0 ) THEN

            PERFORM "__InsertChunk__sp"( buf );

            off := 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