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, 10 ), generate_series(1,
10 );
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:[EMAIL PROTECTED]
Sent: Thursday, March 13, 2008 11:21 PM
To: Zubkovsky, Sergey
Cc: [email protected]
Subject: Re: [DOCS] pg_total_relation_size() and CHECKPOINT
"Zubkovsky, Sergey" <[EMAIL PROTECTED]> 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
