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:
So my rum_dba user became a kind of clone of dbadmin user.
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 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...