CloudSQL index advisor flag can't be enabled

smedina-btf

Hi,

I have a problem activating the "cloudsql.enable_index_advisor" flag in a PostgreSQL 15 database in Cloudsql. I activate it and when the update is finished, it appears as disabled anyway. I have Gemini activated at the organization level.

Anyone with experience activating that? Thanks!

ms4446

To address the issue with activating the "cloudsql.enable_index_advisor" flag on your PostgreSQL 15 database in Google Cloud SQL, I recommend restarting your Cloud SQL instance.

For more detailed guidance on managing and configuring your instance, you can refer to the official documentation: Google Cloud SQL for PostgreSQL - Index Advisor Overview.

alejandrombc

Even with a restart the flag does not appear on the flags part inside the instance configuration, is this a normal behavior?. Maybe its just too new appear there?.

smedina-btf

I tried with an instance restart, but the problem persists. Additional with this, i also tried with gcloud command gcloud sql instances patch DB_NAME --database-flags=cloudsql.enable_index_advisor=on but we've got the same result. Is there any other option that we can try ? thanks again!

ms4446

If you've already tried restarting your instance and using the gcloud command to set the cloudsql.enable_index_advisor flag, but are still facing issues with it being disabled, here are a few more steps youcan take to troubleshoot and resolve this:

Confirm the Current Flag Settings:

Command Verification: Run the following command to list all current flags for your instance, which will confirm if the Index Advisor setting is active:

gcloud sql instances describe [INSTANCE_ID] | grep -A 2 databaseFlags

Replace [INSTANCE_ID] with the identifier for your instance.

Check for Error Messages:

Log Analysis: Carefully review the responses when setting the flag and examine the Google Cloud SQL logs for specific error messages that could indicate the root cause, such as permission issues or conflicts.

Verify Permissions:

Granular Permissions: Ensure your account has the necessary permissions, beyond the Cloud SQL Admin role, to modify database flags. Verify this in the Cloud IAM settings.

Contact Google Cloud Support :

Provide comprehensive information when contacting support, including:
- PostgreSQL version
- All troubleshooting steps taken
- Any error messages from both command line and logs
- Instance region
- Mention that the feature is in Public Preview

smedina-btf

After a few tries with the gcloud command, I was able to activate the index.

First I had to restart the SQL instance, and then launch the command with all the flags including the one already mentioned (otherwise the command deletes all the flags in the instance 😮).

Thanks for your support!

baetensmatthias

This is actually just very poorly documented in general. Error messages are not helpful either.

An end-to-end reproduction led me to the following conclusion:
1. You need to set-up Gemini in Databases which is done through Database Centre first, following this documentation (can take up to 48 hours...).
2. You need to enable Query Insights following this documentation.
3. Then running the specified command to add the flag should work:

gcloud sql instances patch ticket-193833 --database-flags=cloudsql.enable_index_advisor=on -q

Apart from the solution, there were issues with the error messages:
1. Duet AI is not a thing anymore to the best of my understanding. This error message is thus very confusing and should be updated to Gemini in Databases.

ERROR: (gcloud.sql.instances.patch) HTTPError 400: Invalid request: Invalid flag for instance role: Setting flag cloudsql.enable_index_advisor to on requires a Duet AI subscription.

2. I am struggling to find references to DB insights. I think this needs to be updated to Query Insights.

ERROR: (gcloud.sql.instances.patch) HTTPError 400: Invalid request: Invalid flag for instance role: Please enable DB Insights before setting cloudsql.enable_index_advisor to on.

Overall it seems that Google is struggling to keep up their documentation with the pace of the AIs coming up with new product names 🙂

shelley-atl

I ran into a similar issue while trying to enable cloudsql.enable_index_advisor=on on our CloudSQL postgres instance. In order to avoid the patch overwriting all database flags, patch thru. GCP Console will be a good option, it will append the new flag. For this flag, I ran into a similar error during the patch, after opened a Google support case, was told they are able to duplicate the error, so the issue is circled back to Google internal product team. I am waiting for their response right now.

Here is my error message during the Console patch:

Operation failed
Invalid request: Invalid flag for instance role: Setting flag cloudsql.enable_index_advisor to on requires a Duet AI subscription.

shelley-atl

Just to follow up this post, I got Google support case feedback as the below. Due to this Google preview feature, the Duet IA service seems enabled in my project, but actually not. I have to go through my company's approval process, so I can't follow through the instructions in the below right now, but to share if anyone else can.

==> Messages from Google support case:

The product team checked the log and internal stats of the instance. It looks like it does not have any operation to subscribe to the duet service.

In other words, the error message would be correct, and you need to have this subscription enabled first, and only after that you will be able to enable the flag.

In the Google Cloud console, go to the Gemini for Google Cloud page.
Click Gemini in Databases.
The Explore Gemini pane is displayed.
Click Get Gemini in Databases.
The Admin for Gemini page is displayed.
Choose the billing for which you want to enable Gemini in Databases and click Continue to Admin for Gemini page.
The Gemini in Databases page is displayed.
Click Review terms and turn on.
The Turn on Gemini in Databases page is displayed.
Click Purchase to enable Gemini in Databases.

Once you do this process please retry to enable the flag.