Writing and Deploying Custom Projections

Before you write custom projections, be sure to review the topics in Planning Your Design carefully. Failure to follow these considerations can result in non-functional projections.

To manually modify or create a projection:

  1. Write a script to create the projection, using the CREATE PROJECTION statement.
  2. Use the \i meta-command in vsql to run the script.

    Note: You must have a database loaded with a logical schema.

  3. For a K-safe database, use the function SELECT get_projections('table_name') to verify that the projections were properly created. Good projections are noted as being "safe." This means that the projection has enough buddies to be K-safe.
  4. If you added the new projection to a database that already has projections that contain data, you need to update the newly created projection to work with the existing projections. By default, the new projection is out-of-date (not available for query processing) until you refresh it.
  5. Use the MAKE_AHM_NOW function to set the Ancient History Mark (AHM) to the greatest allowable epoch (now).
  6. Use DROP PROJECTION to drop any previous projections that are no longer needed.

    These projections can waste disk space and reduce load speed if they remain in the database.

  7. Run the ANALYZE_STATISTICS function on all projections in the database. This function collects and aggregates data samples and storage information from all nodes on which a projection is stored, and then writes statistics into the catalog. For example:
    =>SELECT ANALYZE_STATISTICS ('');