pg_dbms_stats icon indicating copy to clipboard operation
pg_dbms_stats copied to clipboard

Fix the wrong statistic of timestamp data type after import.

Open higuchi-daisuke opened this issue 4 years ago • 1 comments

Hi,

I found the problem when trying import the statistics of timestamp data type. The values of the statistics have changed before and after the import.

[How to reproduce]

create extension pg_dbms_stats ;
create table timestamp_test(c1 timestamp);
insert into timestamp_test values ('2020-10-01 10:00:0.55555'), ('2020-10-01 10:00:0.55555'), ('2020-10-01 10:00:0.55555');
analyze timestamp_test;
select most_common_vals from dbms_stats.stats where tablename = 'timestamp_test'; -- (1) before import, the result is {"2020-10-01 10:00:00.55555"}
\i /home/postgres/pgsql/share/doc/extension/export_effective_stats-12.sql.sample
select dbms_stats.import_database_stats('/home/postgres/export_stats.dmp');
select most_common_vals from dbms_stats.stats where tablename = 'timestamp_test'; -- (2) after import, the result is {"2020-10-01 10:00:01"}.

As you see, milliseconds of the statistic have been rounded off.

[Investigation] As my investigation, "0" is always passed to the fifth argument ("typmod") of ReadArrayBinary in dbms_stats_array_recv. I think the value which ReceiveFunctionCall() passes should be used.

        ReadArrayBinary(buf, nitems,
                                        &my_extra->proc, typioparam, 0,
                                        typlen, typbyval, typalign,
                                        dataPtr, nullsPtr,
                                        &hasnulls, &nbytes);

This PR is fix this issue and add test for master branch.

Regards, Daisuke Higuchi

higuchi-daisuke avatar Oct 07 '20 12:10 higuchi-daisuke

Hi.

Yeah, I think it's a reasonable fix. The time and interval types are also affected by this bug...

kasaharatt avatar Oct 09 '20 05:10 kasaharatt