Problems with group by queries and partitioned tables

[jmfailde]

Hello,
I have encountered two bugs when using this module.
If the underlying tables are partitioned, or the MV is a group by query, the proccess return an error and VM will be no create

Thanks&Regards

[dday01]

Thanks for your comments - it's great to get feedback. This module does not currently support GROUP BY functions - you'd need to use the complete refresh option provided by Postgres to support this. And, partitioning hasn't been tested so at the moment this isn't supported but any ideas and contribution would be most appreciated.

We're reviewing our latest internally code set currently and we hope to get this into the base line master branch in the next few weeks with all the latest changes and bug fixes we've done.

Thanks again for your feedback.

[aleksanderhan]

Is this issue solved? When will master be updated with the latest changes?

[jaflolo]

Hello,
We were using this module for a while and it worked really well at the beginning, after some time, the database increased in size and some tables reached 100GB of size and the fast refresh function started to take so much time to process the fast refresh.

What we have been doing is to remove the materialized views and re-create them with the provided application functions and prune the mv logs involved with a simple truncate (yes, the mv logs were not removed at all for some tables).

We noticed that the cursor which obtains the pending rows to be processed into the materialized view in the following line

pg-mv-fast-refresh/BuildScripts/mvComplexFunctions.sql

Line 889 in b872102

-- SELECT m_row$,sequence$,dmltype$ FROM cdl_data.log$_t1 WHERE bitmap$ & POWER( 2, $1)::BIGINT = POWER( 2, $2)::BIGINT ORDER BY sequence$

was taking more time on each iteration even with one single change to be processed.

Any hints on this or suggestions on what we are missing?

Thanks

[dday01]

Is this issue solved? When will master be updated with the latest changes?

Master has been updated.

[dday01]

Hello, We were using this module for a while and it worked really well at the beginning, after some time, the database increased in size and some tables reached 100GB of size and the fast refresh function started to take so much time to process the fast refresh.

What we have been doing is to remove the materialized views and re-create them with the provided application functions and prune the mv logs involved with a simple truncate (yes, the mv logs were not removed at all for some tables).

We noticed that the cursor which obtains the pending rows to be processed into the materialized view in the following line

pg-mv-fast-refresh/BuildScripts/mvComplexFunctions.sql

Line 889 in b872102

-- SELECT m_row$,sequence$,dmltype$ FROM cdl_data.log$_t1 WHERE bitmap$ & POWER( 2, $1)::BIGINT = POWER( 2, $2)::BIGINT ORDER BY sequence$

was taking more time on each iteration even with one single change to be processed.

Any hints on this or suggestions on what we are missing?

Thanks

I pushed our latest code set up in the past couple of months - there have been a number of changes and improvements. Probably worth testing this can see if it works any better from a performance point of view.

[dday01]

Hello, We were using this module for a while and it worked really well at the beginning, after some time, the database increased in size and some tables reached 100GB of size and the fast refresh function started to take so much time to process the fast refresh.
What we have been doing is to remove the materialized views and re-create them with the provided application functions and prune the mv logs involved with a simple truncate (yes, the mv logs were not removed at all for some tables).
We noticed that the cursor which obtains the pending rows to be processed into the materialized view in the following line

pg-mv-fast-refresh/BuildScripts/mvComplexFunctions.sql

Line 889 in b872102

-- SELECT m_row$,sequence$,dmltype$ FROM cdl_data.log$_t1 WHERE bitmap$ & POWER( 2, $1)::BIGINT = POWER( 2, $2)::BIGINT ORDER BY sequence$

was taking more time on each iteration even with one single change to be processed.
Any hints on this or suggestions on what we are missing?
Thanks

I pushed our latest code set up in the past couple of months - there have been a number of changes and improvements. Probably worth testing this can see if it works any better from a performance point of view.

Be good to get some feedback if you've tested the latest code setup? Thanks

[wzcolon]

Is this issue solved? When will master be updated with the latest changes?

Master has been updated.

@dday01 Does this mean GROUP BY queries are working now?

From what I can see, that functionality is not present in master.

[scbergeron]

Is this issue solved? When will master be updated with the latest changes?

Master has been updated.

@dday01 Does this mean GROUP BY queries are working now?

From what I can see, that functionality is not present in master.

Indeed it is still not working with group by. Is there an update on this?