search

zubkov-andrei / pg_profile

Postgres historic workload reports
Other
236 stars 33 forks source link

remote query result rowtype does not match the specified FROM clause rowtype #112

Open VladMak opened 3 weeks ago

VladMak commented 3 weeks ago

When I try to take a sample, I get the following error:

remote query result rowtype does not match the specified FROM clause rowtype
...
PL/pgSQL function collect_pg_stat_statements_stats(jsonb,integer,integer,integer) line 340 at SQL statement
SQL statement ""SELECT collect_pg_stat_statements_stats(server_properties, sserver_id, s_id, topn)""
PL/pgSQL function take_sample(integer,boolean) line 624 at PERFORM                                                      
PL/pgSQL assignment ""server_sampleres := take_sample(qres.server_id, NULL)""                                           
PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment                                             
SQL function ""take_sample"" statement 1                                                                                
SQL function ""snapshot"" statement 1                                                                                   
",00:00:02.48)

I considered the collect_pg_stat_statements_stats function. During debugging, I saw that the following fields are used in the SELECT request on the server with pg_profile: 

1 sserver_id,
1 s_id,
dbl.userid,
dbl.username,
dbl.datid,
dbl.queryid,
dbl.plans,
dbl.total_plan_time,
dbl.min_plan_time,
dbl.max_plan_time,
dbl.mean_plan_time,
dbl.stddev_plan_time,
dbl.calls,
dbl.total_exec_time,
dbl.min_exec_time,
dbl.max_exec_time,
dbl.mean_exec_time,
dbl.stddev_exec_time,
dbl.rows,
dbl.shared_blks_hit,
dbl.shared_blks_read,
dbl.shared_blks_dirtied,
dbl.shared_blks_written,
dbl.local_blks_hit,
dbl.local_blks_read,
dbl.local_blks_dirtied,
dbl.local_blks_written,
dbl.temp_blks_read,
dbl.temp_blks_written,
dbl.shared_blk_read_time,
dbl.shared_blk_write_time,
dbl.wal_records,
dbl.wal_fpi,
dbl.wal_bytes,
dbl.toplevel,
false,
dbl.jit_functions,
dbl.jit_generation_time,
dbl.jit_inlining_count,
dbl.jit_inlining_time,
dbl.jit_optimization_count,
dbl.jit_optimization_time,
dbl.jit_emission_count,
dbl.jit_emission_time,
dbl.temp_blk_read_time,
dbl.temp_blk_write_time,
dbl.local_blk_read_time,
dbl.local_blk_write_time,
dbl.jit_deform_count,
dbl.jit_deform_time,
dbl.stats_since,
dbl.minmax_stats_since

But the following fields are added to the request via dblink to those listed above:

temp_blk_write_time
local_blk_read_time
local_blk_write_time
jit_deform_count
jit_deform_time

All of them have a NULL value. But then comes the type conversion:

AS dbl (
      -- pg_stat_statements fields
        userid              oid,
        username            name,
        datid               oid,

Where the data of 5 fields are not listed, which leads to an error. How can I avoid this error? Maybe I'm doing something wrong?

PostgreSQL and pg_profile version:

select * from pg_available_extensions where name = 'pg_profile';
-[ RECORD 1 ]-----+------------------------------------------------------
name              | pg_profile
default_version   | 4.7
installed_version | 4.7
comment           | PostgreSQL load profile repository and report builder

select version();
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------
version | PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit

I also attach the full code of the request that I received, on which I receive this error (the request inside dblink led to a readable form, and put down some fields - sserver_id, s_id):

SELECT
      1 sserver_id,
      1 s_id,
      dbl.userid,
      dbl.username,
      dbl.datid,
      dbl.queryid,
      dbl.plans,
      dbl.total_plan_time,
      dbl.min_plan_time,
      dbl.max_plan_time,
      dbl.mean_plan_time,
      dbl.stddev_plan_time,
      dbl.calls,
      dbl.total_exec_time,
      dbl.min_exec_time,
      dbl.max_exec_time,
      dbl.mean_exec_time,
      dbl.stddev_exec_time,
      dbl.rows,
      dbl.shared_blks_hit,
      dbl.shared_blks_read,
      dbl.shared_blks_dirtied,
      dbl.shared_blks_written,
      dbl.local_blks_hit,
      dbl.local_blks_read,
      dbl.local_blks_dirtied,
      dbl.local_blks_written,
      dbl.temp_blks_read,
      dbl.temp_blks_written,
      dbl.shared_blk_read_time,
      dbl.shared_blk_write_time,
      dbl.wal_records,
      dbl.wal_fpi,
      dbl.wal_bytes,
      dbl.toplevel,
      false,
      dbl.jit_functions,
      dbl.jit_generation_time,
      dbl.jit_inlining_count,
      dbl.jit_inlining_time,
      dbl.jit_optimization_count,
      dbl.jit_optimization_time,
      dbl.jit_emission_count,
      dbl.jit_emission_time,
      dbl.temp_blk_read_time,
      dbl.temp_blk_write_time,
      dbl.local_blk_read_time,
      dbl.local_blk_write_time,
      dbl.jit_deform_count,
      dbl.jit_deform_time,
      dbl.stats_since,
      dbl.minmax_stats_since
    FROM profile.dblink('connection_string','SELECT
        st.userid
,       st.userid::regrole username
,       st.dbid
,       st.queryid
,       TRUE toplevel
,       st.plans
,       st.total_plan_time
,       st.min_plan_time
,       st.max_plan_time
,       st.mean_plan_time
,       st.stddev_plan_time
,       st.calls
,       st.total_exec_time
,       st.min_exec_time
,       st.max_exec_time
,       st.mean_exec_time
,       st.stddev_exec_time
,       st.rows
,       st.shared_blks_hit
,       st.shared_blks_read
,       st.shared_blks_dirtied
,       st.shared_blks_written
,       st.local_blks_hit
,       st.local_blks_read
,       st.local_blks_dirtied
,       st.local_blks_written
,       st.temp_blks_read
,       st.temp_blks_written
,       st.blk_read_time shared_blk_read_time
,       st.blk_write_time shared_blk_write_time
,       st.wal_records
,       st.wal_fpi
,       st.wal_bytes
,       NULL jit_functions
,       NULL jit_generation_time
,       NULL jit_inlining_count
,       NULL jit_inlining_time
,       NULL jit_optimization_count
,       NULL jit_optimization_time
,       NULL jit_emission_count
,       NULL jit_emission_time
,       NULL temp_blk_read_time
,       NULL temp_blk_write_time
,       NULL local_blk_read_time
,       NULL local_blk_write_time
,       NULL jit_deform_count
,       NULL jit_deform_time
,       NULL temp_blk_read_time
,       NULL temp_blk_write_time
,       NULL local_blk_read_time
,       NULL local_blk_write_time
,       NULL jit_deform_count
,       NULL jit_deform_time
,       NULL stats_since
,       NULL minmax_stats_since
FROM
        public.pg_stat_statements(FALSE) st')
    AS dbl (
      -- pg_stat_statements fields
        userid              oid,
        username            name,
        datid               oid,
        queryid             bigint,
        toplevel            boolean,
        plans               bigint,
        total_plan_time     double precision,
        min_plan_time       double precision,
        max_plan_time       double precision,
        mean_plan_time      double precision,
        stddev_plan_time    double precision,
        calls               bigint,
        total_exec_time     double precision,
        min_exec_time       double precision,
        max_exec_time       double precision,
        mean_exec_time      double precision,
        stddev_exec_time    double precision,
        rows                bigint,
        shared_blks_hit     bigint,
        shared_blks_read    bigint,
        shared_blks_dirtied bigint,
        shared_blks_written bigint,
        local_blks_hit      bigint,
        local_blks_read     bigint,
        local_blks_dirtied  bigint,
        local_blks_written  bigint,
        temp_blks_read      bigint,
        temp_blks_written   bigint,
        shared_blk_read_time  double precision,
        shared_blk_write_time double precision,
        wal_records         bigint,
        wal_fpi             bigint,
        wal_bytes           numeric,
        jit_functions       bigint,
        jit_generation_time double precision,
        jit_inlining_count  bigint,
        jit_inlining_time   double precision,
        jit_optimization_count  bigint,
        jit_optimization_time   double precision,
        jit_emission_count  bigint,
        jit_emission_time   double precision,
        temp_blk_read_time  double precision,
        temp_blk_write_time double precision,
        local_blk_read_time double precision,
        local_blk_write_time  double precision,
        jit_deform_count    bigint,
        jit_deform_time     double precision,
        stats_since         timestamp with time zone,
        minmax_stats_since  timestamp with time zone
      );