Total Blocks dirtied does not exist

[mageru]

When I try to run the web UI it keeps throwing errors that lead me to this query..

I tried the query in a SQL editor and get the same error

[Tue Oct 14 15:17:46 2014] [debug] Routing to controller "Powa::Statement" and action "listdbdata". DBD::Pg::st execute failed: ERROR: column "total_blks_dirtied" does not exist LINE 6: sum(total_blks_dirtied) * b.blocksize AS total_b... ^ at script/../lib/Powa/Statement.pm line 103. DBD::Pg::st fetchrow_hashref failed: no statement executing at script/../lib/Powa/Statement.pm line 106.

ERROR: column "total_blks_dirtied" does not exist

SELECT datname, sum(total_calls) AS total_calls, sum(total_runtime) AS total_runtime, round(sum(total_runtime)/sum(total_calls),2) AS avg_runtime, sum(total_blks_read) * b.blocksize AS total_blks_read, sum(total_blks_hit) * b.blocksize AS total_blks_hit, sum(total_blks_dirtied) * b.blocksize AS total_blks_dirtied, sum(total_blks_written) * b.blocksize AS total_blks_written, sum(total_temp_blks_written) * b.blocksize AS total_temp_blks_written, round(sum(total_blk_read_time+total_blk_write_time)::numeric,2) AS io_time FROM ( SELECT datname, (powa_getstatdata_db(to_timestamp(?), to_timestamp(?), datname)).* FROM pg_database ) s JOIN (SELECT current_setting('block_size')::int AS blocksize) b ON true GROUP BY datname, b.blocksize ORDER BY sum(total_calls) DESC;

[2014-10-14 15:23:59] [42703] ERROR: column "total_blks_dirtied" does not exist Position: 320 sql> SELECT datname, sum(total_calls) AS total_calls, sum(total_runtime) AS total_runtime, round(sum(total_runtime)/sum(total_calls),2) AS avg_runtime, sum(total_blks_read) * b.blocksize AS total_blks_read, sum(total_blks_hit) * b.blocksize AS total_blks_hit, sum(total_blks_dirtied) * b.blocksize AS total_blks_dirtied, sum(total_blks_written) * b.blocksize AS total_blks_written, sum(total_temp_blks_written) * b.blocksize AS total_temp_blks_written, round(sum(total_blk_read_time+total_blk_write_time)::numeric,2) AS io_time FROM ( SELECT datname, (powa_getstatdata_db(to_timestamp(?), to_timestamp(?), datname)).* FROM pg_database ) s JOIN (SELECT current_setting('block_size')::int AS blocksize) b ON true GROUP BY datname, b.blocksize ORDER BY sum(total_calls) DESC (?=1413311439, ?=1413314439) [2014-10-14 15:24:11] [42703] ERROR: column "total_blks_dirtied" does not exist Position: 320

[mageru]

Add settings for extensions here

powa.database = 'powa' powa.frequency = 5min powa.retention = 1d powa.coalesce = 100

[mageru]

powa=# SELECT powa_getstatdata_db(to_timestamp(1413311439),to_timestamp(1413314439),'powa');

powa_getstatdata_db

(0 rows)

powa=# SELECT powa_getstatdata_db(to_timestamp(1413311439),to_timestamp(1413314439),'postgres');

powa_getstatdata_db

(0 rows)

powa=# SELECT powa_getstatdata_db(to_timestamp(1413311439),to_timestamp(1413314439),'powa');

powa_getstatdata_db

(0 rows)

powa=# \q

[mageru]

\df powa_getstatdata powa_getstatdata_db powa_getstatdata_sample powa_getstatdata_sample_db powa_statements_aggregate powa_statements_purge powa_stats_reset powa_take_snapshot powa_take_statements_snapshot

[rjuju]

Hi,

This query comes from UI verions 1.2 (still in developpment), and as far as I can tell your postgres powa extension is version 1.1 (otherwise you should have more functions like powa_getstatdata_detailed_db). \dx will tell you the exact version.

Did you upgrade from an existing 1.1 PoWA installation ? If yes, you should recompile the extension (sudo make install in main directory for instance), and update the extensions (ALTER EXTENSION powa UPDATE TO '1.2').

Also, as the 1.2 isn't release yet, you may have to drop and recreate the extension when it'll be out, depending on changes on the extension part.

Regards

[mageru]

Not sure how I did this, thanks for the update I will follow your instructions. Excellent support.