One or more of my PDTs isn't building, even though the trigger value should have changed.
At a high level, the things to check are:
- The state of that PDT
- The state of the Regenerator overall
For both of these steps, we’ll start in the Persistent Derived Tables section of the Admin panel and then dig deeper as necessary.
Checking the State of the PDT
There’s quite a bit of helpful information under each entry in the PDT panel. Here’s an example of a healthy entry:
Check the following things to make sure the PDT is able to build:
- Is there an error in the PDT panel? A SQL syntax error in either the trigger value or the derived table itself will show up in red here on this entry.
- Is this marked as a
triggertype PDT in the third column? PDTs of type
persistonly rebuild when a user requests the table and are not built by the regenerator. A PDT will only listen to a trigger if it uses a
sql_trigger_valueor a datagroup trigger.
- Has the trigger value actually changed? The most recent trigger value change is shown on the PDT panel. Getting the
sql_trigger_valueSQL from the view and running that in SQL Runner can help confirm whether there is a new trigger value that should have prompted a change.
Checking the State of the Regenerator
The regenerator is a process that both checks PDT trigger values and builds PDTs. The important thing to remember about the regenerator is that it only does one task at a time, per connection. This means that if a trigger value or PDT build takes a long time, other processes for that connection will have to wait. Some things to check are:
- How frequently is the regenerator actually running against the database? The default is every 5 minutes, but this can be changed in the Connections section of the Admin panel, under "PDT Maintenance Schedule".
What is the regenerator doing right now? Searching for currently regenerating tables in the PDT panel can help pinpoint a potential bottleneck in the regenerator.
If there is a table that has been regenerating for awhile, then it will hold up the regenerator and prevent it from checking other trigger values or building other tables. Consider either:
- Checking the SQL of this PDT to make sure it is performant.
- Having this PDT build less frequently or during a time with minimal usage.
- Moving this PDT to a regular ETL process.
- Killing this PDT in the Queries section of the Admin panel to free up the regenerator.
Note: Permissions and Locking
Looker expects that the Looker database user specified in the Connections panel has permissions to create and drop PDTs. If the Looker user is unable to do these, then error messages may manifest in the PDT panel or even the explore page. This can happen in cases where PDTs are used in processes outside of Looker, causing potential locking on the database side. Errors indicating this can include:
Relation already exists
Cannot rename table
For these cases, check against the database itself to make sure that no other processes are using the table and that the Looker database user does have access to drop the table. In some cases, the table may need to be manually dropped from the database in order to get the regenerator back on track.