Re: pg_total_relation_size() and CHECKPOINT

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