remove the single-property index exemption

[jesse_]

I am just getting started with AppEngine and Firestore. I'm using the Google sample code, and getting this error:

The query requires an ASC or DESC index for kind visit and property timestamp. Please use the gcloud CLI to remove the single-property index exemption for this property.

The code is just this:

query = datastore_client.query(kind="visit")

query.order = ["-timestamp"]

When I go to the Query Builder, I can do this query with no problem. But when it's done from the code, I get the error above. (Note that there is no exemption in place.)

What am I missing?

[faizanoor7878_8]

Same issue for me as well. Do we need to explicitly add indexes ?

[ms4446]

The error message you are receiving is telling you that you need to create an index on the timestamp property of the visit kind in order to perform a descending sort on that property.

To do this, you can create an index configuration file and deploy it to Datastore using the following steps:

1. Create an index configuration file

indexes:
  - kind: visit
    properties:
    - name: timestamp
      direction: desc

This configuration specifies a descending index on the timestamp property for the visit kind.

2. Deploy the index configuration file

gcloud datastore indexes create index.yaml

This command will create the necessary index based on the configuration in the index.yaml file.

Note: The index building might take some time. You can check the status of the index using the following command:

gcloud datastore indexes list

Once the index is deployed, you should be able to perform your query without receiving the error message.

Why is an index required for this query?

Datastore uses indexes to efficiently execute queries. Without an index, Datastore would have to scan all of the entities in the visit kind to find the ones that match your query. This could be very slow, especially if you have a large number of entities in that kind. Creating an index on the timestamp property allows Datastore to quickly find and sort the entities that match your query.

Additional information

For more information on indexes in Datastore, please refer to the following documentation:

• Creating and managing indexes: Google Cloud Documentation
• Querying Datastore: Google Cloud Documentation

I hope this revised response is even more helpful and informative. Please let me know if you have any other feedback or suggestions.

[jesse_]

Thanks for the response! It seems that if the query required a missing index, I should get the same error message no matter where I run it -- from my python code or from the QueryBuilder. Why would it work correctly without an index in the QueryBuilder, but throw an exception when run from python?

Also, when I look at the indexes for the collection, I see that automatic indexing for Collection Scope is enabled for both ascending and descending. No exemptions have been created. Doesn't that mean that there already is an index for my sort order?

[ms4446]

You are correct that you should get the same error message whether you run the query from Python code or from the QueryBuilder, if the query requires a missing index. However, there are a few possible explanations for why you might be getting different results in these two cases:

QueryBuilder Behavior:

It is possible that the QueryBuilder is using a different indexing strategy than the Python client library. For example, the QueryBuilder might be able to use a partial index to execute the query, even if a complete index is not available. However, it is not typical for query builders to cache results in a cloud environment where data can change frequently, so caching is unlikely to be the cause of the different results in this case.

Potential Bug in the Python Client Library:

It is also possible that there is a bug in the Python client library that is preventing it from correctly handling the missing index. Ensure that you are using the latest version of the library.

Troubleshooting Steps:

1. Clear any potential cache in the QueryBuilder to force it to re-execute the query and check for the necessary index.
2. Use the gcloud datastore indexes list command to check the status and existence of the necessary index for the timestamp property.
3. If using an older version, consider upgrading the Python client library to the latest version.
4. If you are still having problems, you can report the issue to Google Cloud Support.

Regarding Automatic Indexing:

Automatic indexing for Collection Scope is enabled for both ascending and descending by default. This means that Datastore will automatically create indexes for all properties of all entities in your collection. However, there are a few cases where Datastore will not create an index, even if automatic indexing is enabled:

• The property is not a supported type. Datastore only creates indexes for properties of supported types, such as strings, numbers, and dates.
• The property is too large. Datastore will not create an index for a property that is larger than 100KB.
• In your case, if you find that the necessary index for the timestamp property is not present, it might be due to specific configurations or exemptions that have been set, preventing the automatic creation of the index.

Verifying and Creating Index:

To verify this, you can check the indexes for your collection using the Cloud Console or the gcloud CLI. If there is no index for the timestamp property, you can create an index manually using the steps outlined in the previous response.

I hope this revised information is helpful. Please let me know if you have any other questions or need further assistance.

Additional Notes:

• If you are using a Python framework such as Django or Flask, there may be additional steps required to configure the framework to use the indexes that you create in Datastore. Please consult the documentation for your framework for more information.
• If you are still having problems, you can contact Google Cloud Support for assistance.

[Juan_L]

This shows a database in Firestore in Native mode. The sample code you referenced earlier uses the Datastore client library. The code is meant to be used alongside a Firestore in Datastore mode database.

[faizanoor7878_8]

return func(*args, **kwargs) File "/layers/google.python.pip/pip/lib/python3.9/site-packages/google/api_core/grpc_helpers.py", line 77, in error_remapped_callable raise exceptions.from_grpc_error(exc) from exc google.api_core.exceptions.FailedPrecondition: 400 The query requires an ASC or DESC index for kind visit and property timestamp. Please use the gcloud CLI to remove the single-property index exemption for this property.


@ms4446 even after adding the index, it fails with the above error, I have added the index but still the issue persists. And I have same questions as Jesse has, why we need to add indexes explicitly.

[ms4446]

The error message you are receiving indicates that Datastore still does not have the necessary index to execute your query. There are a few possible reasons for this:

• The index may not have finished building yet. It can take some time for Datastore to build a new index, especially if you have a large number of entities in your collection.
• The index may have been built incorrectly. You can check the status of the index using the command gcloud datastore indexes list in the command-line interface. If the index is listed as ACTIVE, then it has been built successfully. If it is listed as ERROR, there was a problem building the index.
• There may be an exemption that is preventing the index from being created. You can check for exemptions using the gcloud datastore indexes list command. If there is an exemption for the index, you need to remove it before the index can be created.
• There may be a bug in the Datastore API. If you have checked the above and you are still having problems, you can report the issue to Google Cloud Support.

Why do we need to add indexes explicitly?

Datastore is a NoSQL database, which means that it does not have a fixed schema. This gives you a lot of flexibility in how you store and retrieve your data. However, it also means that Datastore needs to know how to index your data in order to execute queries efficiently.

By default, Datastore will create a single-property index for each property of each entity kind. This is usually sufficient for simple queries. However, for more complex queries, such as queries that filter or sort on multiple properties or queries that use inequality operators, you may need to create additional indexes.

If you try to execute a query that requires an index that does not exist, Datastore will throw an error. To avoid this, you can explicitly create the necessary indexes using the Cloud Console or the gcloud CLI.

Troubleshooting steps:

1. Run gcloud datastore indexes list to check the status of the index.
2. If the index is not listed, or if it is listed as BUILDING, wait until the index has finished building.
3. If the index is listed as ERROR, check the error message for more information about the problem.
4. If the index is listed as ACTIVE, check for any exemptions that might have been set. If there is an exemption, remove it using the gcloud datastore indexes remove-exemption command.
5. If you are still having problems, report the issue to Google Cloud Support.

[Juan_L]

This might happen if you try to use a Datastore client library with a Firestore in Native mode database. Can you check which mode your database uses? It should correspond to the client library you use:

• Firestore in Datastore mode -> Datastore client library
• Firestore in Native mode -> Firestore client library

[radzisz]

same issue
I'm filtering by a single boolean property with is not on the exemption list.

Exception from a finished function: Error: 9 FAILED_PRECONDITION: The query requires an ASC or DESC index for kind XXXX and property isDeleted. Please use the gcloud CLI to remove the single-property index exemption for this property.

However, it is not possible to create an index on a single property as far as to remove an exemption (as there is none)
It may be caused by using a multiple database feature - I'm accessing database with a name other than  "(default)".

Any suggestion?

[ms4446]

The error message you are receiving indicates that Datastore does not have an index on the isDeleted property for the XXXX kind, which is required to execute your query.

To create the necessary index:

1. Edit your index.yaml file and ensure it contains the following:

indexes:
- kind: XXXX
  properties:
  - name: isDeleted

2. Deploy the index using the gcloud CLI:

gcloud datastore indexes create index.yaml

After deploying the index, there might be a delay before it becomes active, especially if there is a large amount of data. You can check the status of your indexes in the Google Cloud Console.

If you continue to face issues, consider reaching out to Google Cloud Support for further assistance.

Additional information:

• Creating and managing indexes in Datastore
• Querying Datastore

[Juan_L]

Does the client library corresponds to the database type?

• Firestore in Datastore mode -> Datastore client library
• Firestore in Native mode -> Firestore client library

[llay]

I had the same issue. It was caused by the client library - firestore mode mismatch Juan_L referred to
I had two firestores, one in each mode (Native, Datastore). The FAILED_PRECONDITION error was due to my datastore client trying to talk to the Native firestore, which was the default. I fixed this by specifying the database in the client init:

datastore.Client(google_cloud_project_name, database=datastore_name)