Computeds in SQL adapter

[aljazerzen]

Computeds in SQL adapter

[jnak]

Thanks for continuing the discussion!

@jnak Can I ask, which tool are you using for reverse-ETL? Is there an option to include auto-generated columns? And views?

We're not yet using a proper reverse-ETL tool, as we've purposefully pushed back on investing too much into our warehouse for now. However, we're currently using services such as Metabase and Customer IO that query EdgeDB via the SQL interface. We'll be looking at tools such as FiveTran or Airbyte when we're ready to invest to our warehouse.

Current plan is to expose single computeds as GENERATED columns and multi computeds as CREATE VIEW X (source uuid, target computed_type). This is the plan because pg_dump does not dump GENERATED columns and views, but it would be useless if they were unavailable to ETL tools.

This is super exciting! I can't wait to use it. What's the timeline there?

Tangantially-related, are you planning to allow computed fields to be materialized/generated in EdgeDB? That would be huge! We don't need it yet, but it would be amazing to know that, at some point, we could speed up slow computed values by materializing them :)

[aljazerzen]

@jnak Can I ask, which tool are you using for reverse-ETL? Is there an option to include auto-generated columns? And views?

Current plan is to expose single computeds as GENERATED columns and multi computeds as CREATE VIEW X (source uuid, target computed_type). This is the plan because pg_dump does not dump GENERATED columns and views, but it would be useless if they were unavailable to ETL tools.

An alternative approach would be to have a SET variable that enables the computed columns as regular columns and also the table for multi computeds.

[aljazerzen]

What's the timeline there?

This is hard to say, as I have a different task before this one which has a lot of unknowns with it. The worst-worst scenario would be 6.0, which will release end of June.

Are you planning to allow computed fields to be materialized/generated in EdgeDB?

They will definitely be computed during query-time. We don't want to unnecessary space. If you'd want things materialized, you could use regular pointers with mutation rewrites to update the value on inserts and updates.

[jnak]

This is hard to say, as I have a different task before this one which has a lot of unknowns with it. The worst-worst scenario would be 6.0, which will release end of June.

Exciting!

They will definitely be computed during query-time. We don't want to unnecessary space. If you'd want things materialized, you could use regular pointers with mutation rewrites to update the value on inserts and updates.

Yeah, I know it's possible with triggers, but then it can get tricky to write it in the first place if the computed field queries multiple types. Also, the logic is no longer encapsulated in one place, which increases the likelihood that some refactoring will introduce an unwanted consistency bug. It would be nice if the compiler could take care of that automatically for us. But that's a non-trivial project to handle all the edge cases.