public inbox for [email protected]  

help / color / mirror / Atom feed

From: Zubkovsky, Sergey <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: pg_total_relation_size() and CHECKPOINT
Date: Fri, 14 Mar 2008 18:23:22 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>

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:[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

view thread (24+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pg_total_relation_size() and CHECKPOINT
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox