pg_total_relation_size incorrect when inserting result into a table

[Alric Kriel]

Good Day,

Hope someone can assist. We have a process where we have a table that we populate the specific table and its size on a daily basis containing the output of pg_total_relation_size in order to allow us to report on table and partition growth over time.

The Table we populate is as follows:

create table monitor.partition_space_usage
(
run_date date,
schema_name varchar(50),
table_name varchar(255),
partition_name varchar(255),
size varchar(255)
)
distributed by (partition_name);

The insert statement is as follows:

insert into monitor.partition_space_usage(run_date, schema_name, table_name, partition_name, size)
select date(now()), b. schemaname, b.tablename, b.partitiontablename, pg_total_relation_size(schemaname||'.'||partitiontablename)
from pg_class a , pg_partitions b
where a.relname = b.partitiontablename;

What is extremely odd is that when doing a normal select if the select statement for example for one specific partition the value is the actual size of the partition in bytes, however after doing the insert with the select statement and then selecting from the table just inserted into the values are completely different.

For example. The select without the insert gives a values of x. Selecting from the tables the values was inserted into gives a much smaller value than the original select statement returned.

We are running Greenplum version 6.11.0. 

Is there maybe a reason why this is happening and also a way we can get correct sizing of tables and partitions when inserting it into another table as to allow us to track table sizes over time.

Kind Regards

Alric Kriel

[Danilo Fortunato]

Alric,

pg_total_relation_size returs a bigint, and you are inserting this value into a varchar(255) column, without an explicit cast.

What about changing the size column type to bigint, or insering the value using an explicit cast ?

pg_total_relation_size(schemaname||'.'||partitiontablename)::varchar

Regards,

Danilo Fortunato

---

Da: Alric Kriel <alric...@gmail.com> (alric...@gmail.com)
Inviato: Giovedì 22 Ottobre 2020 12:45
A: Greenplum Users (gpdb-...@greenplum.org)
Oggetto: [gpdb-users] pg_total_relation_size incorrect when inserting result into a table

 

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To view this discussion on the web visit https://groups.google.com/a/greenplum.org/d/msgid/gpdb-users/454b0f45-05cd-494b-9fc8-1054066bafd4n%40greenplum.org.

[Alric Kriel]

Good Day all,

Thank you for the responses. To answer your questions. I have tried casting and also different data types to no avail. I however did find a workaround that is working. When I explicitly put the schema and tablename/partitioname as parameters it works. So I was forced to do a loop in a function that retrieves the schema and partition names and then loop through them one by one to retrieve the size and then insert it into the table.

By joining to the pg_partition table and doing an insert together seems to be causing the issue. 

Very strange. Maybe something to do with segments? No idea.

Kind Regards

Alric Kriel

[Alric Kriel]

Hi Luis,

I have tried there tables and for some strange reason when I try and insert into a table from these views it does not allow me to do so. I get an error when attempting to do so.

Kind Regards

Alric Kriel

On Sun, Oct 25, 2020 at 7:13 PM Luis Filipe de Macedo <mac...@vmware.com> wrote:

Alric,

There are views on the gp_tookit schema that have the size of tables. Did you check those?

 

https://gpdb.docs.pivotal.io/6-11/ref_guide/gp_toolkit.html#topic38

 

Rgds,

 

Luis F R Macedo

Advisory Data Engineer & Business Development for Latam

VMware Tanzu Data

Call Me @ +55 11 97616-6438

Take care of the customers and the rest takes care of itself

To view this discussion on the web visit https://groups.google.com/a/greenplum.org/d/msgid/gpdb-users/CAAxtsZufcJ6cDoiaU%2BdaFKM1swNv2G99mv7HvgjYKTm0r5HgwA%40mail.gmail.com.

[Alric Kriel]

I will attempt it again.

On Mon, Nov 2, 2020 at 5:35 PM Luis Filipe de Macedo <mac...@vmware.com> wrote:

Really? Even if you match the datatypes? I am quite sure its some casting issue.