no postgresql support

[yoshi314]

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "WITH"
LINE 4: ...t_id GROUP BY c_os.varvalue) AS sub GROUP BY (os) WITH ROLLU...
^, query was: SELECT rollup.os, rollup.hosts_cnt, rollup.hosts_nok, rollup.hosts_unhandled_nok FROM (SELECT sub.os, SUM(hosts_cnt) AS hosts_cnt, SUM(hosts_nok) AS hosts_nok, SUM(hosts_unhandled_nok) AS hosts_unhandled_nok FROM (SELECT c_os.varvalue AS os, COUNT(*) AS hosts_cnt, SUM(CASE WHEN hs.current_state = 0 THEN 0 ELSE 1 END) AS hosts_nok, SUM(CASE WHEN hs.current_state != 0 AND hs.problem_has_been_acknowledged = 0 AND hs.scheduled_downtime_depth = 0 THEN 1 ELSE 0 END) AS hosts_unhandled_nok FROM icinga_objects AS o
INNER JOIN icinga_hosts AS h ON o.object_id = h.host_object_id AND o.is_active = 1
LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = h.host_object_id
LEFT JOIN icinga_customvariablestatus AS c_os ON c_os.varname = 'os' AND c_os.object_id = o.object_id GROUP BY c_os.varvalue) AS sub GROUP BY (os) WITH ROLLUP) AS rollup ORDER BY (rollup.os IS NOT NULL) ASC, rollup.os ASC, (rollup.hosts_cnt IS NOT NULL) ASC, rollup.hosts_cnt ASC, (rollup.hosts_nok IS NOT NULL) ASC, rollup.hosts_nok ASC, (rollup.hosts_unhandled_nok IS NOT NULL) ASC, rollup.hosts_unhandled_nok ASC

I might give it a try, once i figure out all the queries the module does.

[Thomas-Gelf]

Hi @yoshi314!

Honestly I didn't even try it on PostgreSQL, just had a quick look at the documentation and seemed that it would work. Could you please just remove the "WITH" and try again? Just the word WITH, nothing else. And what PostgreSQL version are you running?

[das-chick]

removed "WITH" in DbCube.php in function prepareRollupQuery. does not work either:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "ROLLUP"
ZEILE 4: ...t_id GROUP BY c_os.varvalue) AS sub GROUP BY (os) ROLLUP) AS...
^, query was: SELECT rollup.os, rollup.hosts_cnt, rollup.hosts_nok, rollup.hosts_unhandled_nok FROM (SELECT sub.os, SUM(hosts_cnt) AS hosts_cnt, SUM(hosts_nok) AS hosts_nok, SUM(hosts_unhandled_nok) AS hosts_unhandled_nok FROM (SELECT c_os.varvalue AS os, COUNT(*) AS hosts_cnt, SUM(CASE WHEN hs.current_state = 0 THEN 0 ELSE 1 END) AS hosts_nok, SUM(CASE WHEN hs.current_state != 0 AND hs.problem_has_been_acknowledged = 0 AND hs.scheduled_downtime_depth = 0 THEN 1 ELSE 0 END) AS hosts_unhandled_nok FROM icinga_objects AS o
INNER JOIN icinga_hosts AS h ON o.object_id = h.host_object_id AND o.is_active = 1
LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = h.host_object_id
LEFT JOIN icinga_customvariablestatus AS c_os ON c_os.varname = 'os' AND c_os.object_id = o.object_id GROUP BY c_os.varvalue) AS sub GROUP BY (os) ROLLUP) AS rollup ORDER BY (rollup.os IS NOT NULL) ASC, rollup.os ASC, (rollup.hosts_cnt IS NOT NULL) ASC, rollup.hosts_cnt ASC, (rollup.hosts_nok IS NOT NULL) ASC, rollup.hosts_nok ASC, (rollup.hosts_unhandled_nok IS NOT NULL) ASC, rollup.hosts_unhandled_nok ASC

running postgresql-server-9.2.15-1.el7_2.x86_64

[Thomas-Gelf]

postgresql-server-9.2 -> guess that's the problem. I have to double-check this, but it seems that they introduced rollups later on. This was my first hit on that friendly search engine: https://www.compose.com/articles/deeper-into-postgres-9-5-new-group-by-options-for-aggregation/

Also had a look at the documentation, it exists on the relate page for 9.5, but is missing on the one for 9.4.

I'll try to find some time to test it on 9.5 and mention it as a requirement in case it works. There is not much more I can do about this right now, sorry for that.

Cheers,
Thomas

[yoshi314]

i think i am on 9.6 atm. i'll see if i can alter the resulting query somehow.

[Thomas-Gelf]

Thanks @yoshi314! I also gave it a try with 9.5, using GROUP BY ROLLUP (os) instead of GROUP BY (os) WITH ROLLUP should work fine. And makes more fun with more dimensions of course :p

[Thomas-Gelf]

@yoshi314: could you please try the current master? I tried to push a fix for this. In case it works I'll add 9.5 as a requirement when running on PostgreSQL and publish a new release on Thursday.

@das-chick: I'm sorry for you, but while all of this could also be accomplished in userspace the intention of the cube was to let the database do most of the work. So there will we no support for PostgreSQL < 9.5 right now.

[yoshi314]

No, same thing happens. I'll have a look at the query in a few hours in my hopefully more idle work hours.

[yoshi314]

No, same thing happens. I'll have a look at the query in a few hours in my hopefully more idle work hours.

replacing the rollup bit with

GROUP BY grouping sets (os)

seems to fix the query.

e.g. by doing a rollup on vars.os from hosts i get this :

"";267;22;15
"Cisco";9;1;0
"Linux";357;2;1
"Windows";375;2;2

Seems okay to me.

[yoshi314]

SELECT 
	rollup.os, rollup.hosts_cnt, rollup.hosts_nok, rollup.hosts_unhandled_nok 
FROM 
	(SELECT sub.os, 
		SUM(hosts_cnt) AS hosts_cnt, 
		SUM(hosts_nok) AS hosts_nok, 
		SUM(hosts_unhandled_nok) AS hosts_unhandled_nok 
			FROM (SELECT c_os.varvalue AS os, COUNT(*) AS hosts_cnt, 
				SUM(CASE WHEN hs.current_state = 0 THEN 0 ELSE 1 END) AS hosts_nok, 
				SUM(CASE WHEN hs.current_state != 0 AND hs.problem_has_been_acknowledged = 0 AND hs.scheduled_downtime_depth = 0 THEN 1 ELSE 0 END)
					AS hosts_unhandled_nok FROM icinga_objects AS o
				INNER JOIN icinga_hosts AS h ON o.object_id = h.host_object_id AND o.is_active = 1
				LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = h.host_object_id
				LEFT JOIN icinga_customvariablestatus AS c_os ON c_os.varname = 'os' 
				AND c_os.object_id = o.object_id GROUP BY c_os.varvalue) AS sub 
		GROUP BY grouping sets (os) ) AS rollup 

ORDER BY (rollup.os IS NOT NULL) ASC, 
	rollup.os ASC, 
	(rollup.hosts_cnt IS NOT NULL) ASC, 
	rollup.hosts_cnt ASC, 
	(rollup.hosts_nok IS NOT NULL) ASC, 
	rollup.hosts_nok ASC, 
	(rollup.hosts_unhandled_nok IS NOT NULL) ASC, 
	rollup.hosts_unhandled_nok ASC

the entire query in question, works on 9.6 for me.

[yoshi314]

https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#GROUPING_SETS.2C_CUBE_and_ROLLUP

documentation for the feature.

[yoshi314]

Small mistake on my end, grouping sets is not a rollup..

group by rollup (os) should be used in the aforementioned example and it also works fine.

[Thomas-Gelf]

group by rollup (os) should be used in the aforementioned example and it also works fine

That's what I wanted to achieve with the patch I pushed. Could you please show me the query it rendered?

[yoshi314]

i still have the error. for some reason it doesn't detect the database as pgsql. weird.

[Thomas-Gelf]

That's very strange. Could you please add something like...

var_dump($this->connection->getDbType());
exit;

...at the beginning of the isPgsql() function or so? That's how professional debugging works ;-)

[yoshi314]

Ok, where will i see the debug message? I am not very familiar with php.

[Thomas-Gelf]

In the web frontend :-) As of the "exit" it will destroy your layout as soon as you choose a dimension, but at least we'll get some more information

[yoshi314]

I have a suspicion that this function does not start at all. Layout is not messed up and i still have the old error message.

[yoshi314]

http://imgur.com/a/wzjGJ screenshot is here

[Thomas-Gelf]

And you are sure that you pulled the latest master? What does...

git rev-parse HEAD

...tell when being in your cube directory?

[yoshi314]

it says 3107867

[yoshi314]

Okay, i think openvz mounts are messing with me. I'll try now.

[yoshi314]

...aaand it works fine.

[Thomas-Gelf]

Great to hear that! Guess I'll tag a new version with official PostgreSQL support tomorrow :D

[Thomas-Gelf]

Still no tag, we should schedule one ;-)