problem postgresql-8 select joins

[heiklehm]

Hallo,

I try to install cacti with postgres-8.
Following query works on pg-7:
cacti=> select data_template_data.rrd_step, rra.x_files_factor, rra.steps, rra.rows, rra_cf.consolidation_function_id, (rra.rows*rra.steps) as rra_order from data_template_data left join data_template_data_rra on (data_template_data.id=data_template_data_rra.data_template_data_id) left join rra on (data_template_data_rra.rra_id=rra.id) left join rra_cf on (rra.id=rra_cf.rra_id) where data_template_data.local_data_id=11 and (rra.steps is not null or rra.rows is not null) order by rra_cf.consolidation_function_id,rra_order;
....
(8 rows)


On pg-8 this query result nothing. (on same tables)
cacti=> select data_template_data.rrd_step, rra.x_files_factor, rra.steps, rra.rows, rra_cf.consolidation_function_id, (rra.rows*rra.steps) as rra_order from data_template_data left join data_template_data_rra on (data_template_data.id=data_template_data_rra.data_template_data_id) left join rra on (data_template_data_rra.rra_id=rra.id) left join rra_cf on (rra.id=rra_cf.rra_id) where data_template_data.local_data_id=11 and (rra.steps is not null or rra.rows is not null) order by rra_cf.consolidation_function_id,rra_order;
rrd_step | x_files_factor | steps | rows | consolidation_function_id | rra_order
----------+----------------+-------+------+---------------------------+-----------
(0 rows)


Whats going wrong?
pg-8 seen to work with stronger rules.

How to changes this query to work on pg-8?

regards Heiko

[gandalf]

When running that query on PG cli, does it throw an error?
R.

[heiklehm]

no error, no warning

Heiko

[heiklehm]

prob: empty table if "select ...join..." seen to be solved.

cause:
- postgres (7,8) seen to required col "oid" for calculate hashes / join

After convert cacti-0.8.7-pgsql/postgres.sql to postges-8.sql
(add "WITH OIDS" by script pgconvsql)

-> cacti works on sol10+pg8 now.

Docu "HowTo install cacti+postgresql on Sol10" is in progress.

Heiko

[code]
----100531.cacti.conv.sql----
url=http://www.fh-lausitz.de/launic/comp/so ... gsql.sol10
wget $url/patches/pgconvsql-100531
cat $f | pgconvsql --file - --strcreatetab '^CREATETABLE.*"data_template_data".*' \
> $p8
-----------------------------------
[/code]