Re: pg_total_relation_size() and CHECKPOINT

["Zubkovsky, Sergey" <Sergey(dot)Zubkovsky(at)transas(dot)com>]

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, March 13, 2008 11:21 PM
To: Zubkovsky, Sergey
Cc: pgsql-docs(at)postgresql(dot)org
Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT

 

"Zubkovsky, Sergey" <Sergey(dot)Zubkovsky(at)transas(dot)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