Projection refresh doesnt work as/when expected

Moderator: NorbertKrupa

Post Reply
Posts: 2
Joined: Thu Oct 31, 2013 12:27 pm

Projection refresh doesnt work as/when expected

Post by nkomar » Thu Oct 31, 2013 2:23 pm

I'm trying to optimize some queries/check execution plans having differenet set of projections for a table.
So I have a schema, separate user (rum_dba) which is the schema owner and the table inside schema with some set of initially created projections. "Initially created projections" were created by rum_dba user while source table had no data and "select start_refresh();" was done on empty table.
After that I loaded table with some data.
After that I tried to add a column to table with data and failed.
I added "pseudosuperuser" role to "rum_dba" and set it to active and after that I successfully added new column to my table.
So I assume that if table has any data inside then only user which has "pseudosuperuser" role assigned and active can do such ALTER TABLE statement. I assume that this is related to filesystem access - only "pseudosuperuser" role is mapped to real linux system user so can do changes in filesystem. Is it correct?
That was a first question.

The second question is related to new projections I created for the same table after adding new column.
So after I added new column it was automatically added to its superprojection and superprojection is up to date after that.
I added additional projection which contains new column and ran "select start_refresh();" and didnt get any error message. After ~10 minutes I checked PROJETIONS dictionary and realised that new projection is not up to date. I checked PROJECTION_REFRESHES table and didnt find anything related to my new pjection there. I ran "select refresh(<my new projections anchor table name>);" and didnt get any error but projection was not updated again (PROJECTION_REFRESHES table didnt hold anything related to my new projection again).
After having the same issue with different projections I altered ny user to give it more roles.
I granted dbduser role (grant dbduser to rum_dba;) and added it to default roles list but it didnt help.
In addition I added dbadmin role and added it to default roles list and after that "select start_refresh();" finished with no errors and my projections became up to date.
So finally I had to alter my user this way:

Code: Select all

grant pseudosuperuser to rum_dba
grant dbduser to rum_dba;
grant dbadmin to rum_dba;
ALTER USER rum_dba DEFAULT ROLE dbduser, dbadmin, pseudosuperuser;
So my rum_dba user became a kind of clone of dbadmin user.
SO the question is : Is it really necessary to grant all of those roles to my user to be able to refresh my projections? I expected pseudosuperuser role would be enough...

Post Reply

Return to “Vertica Performance Tuning”