Avoiding Random IDs for SQL Views

When manually creating a SQL view for an app's backend, as is sometimes done for performance reasons, it is important not to create random identifiers for the view rows in the view definition. For example, sometimes when such a view is created, the first field would be defined as:

select public.uuid_generate_v4() as id,

The reason this is problematic is because Helium, when querying the database, starts "paging" when a data set becomes large enough (to strike a performant balance between the number of selects and the size of the returned result), which means multiple selects from the database, with the result then ordered according to the ID. If the IDs are randomly generated with each select, and the result ordered according to this ID (which is default), the expected position of a record would be different between, for example, the first and second selects. The query result batch within which a particular record would be expected to be found would therefor be randomized with each select, and so might not be returned at all, while other records might be returned multiple times.

Here are two examples of how this behaviour may manifest as a bug in a DSL app:

1. When populating a table with data selected from a view, the ordering of the table rows will be inconsistent, and if spread across multiple table pages, some records will be missing while other records will appear more than once. In this case it is possible to spot the problem on the frontend when scrutinizing the data in the table. (It is also possible to "work around" the problem by ordering the table data by a particular column.)
2. When populating a variable collection in a presenter unit with data selected from a view, it will likewise have missing and duplicate records, and if the business logic then requires looping over this dataset to e.g. perform a function for each record, the logic will essentially be broken. This scenario might, however, be especially hard to spot at runtime.

If a developer has written SQL views for Helium apps without knowing about this problem, it is strongly recommended that he or she inspect the project's SQL to make sure it doesn't contain uuid_generate_v4() or similar functions. Because the way this issue manifests as a bug is subtle, a particular app might falsely appear to be functioning correctly, and relying on bug reports will likely be insufficient to spot this.

Solutions

Implement one of the following alternatives for the view in question:

1. If you have a UUID from one of the view's constituent objects that are going to be unique to each record in the view, you can set the ID to that.
2. Similarly, if you have a unique non-UUID field, you could use that to .sortAsc or .sortDesc your collection before processing. If its only purpose is to be displayed in a table widget, explicitly order the table results according to such a column.

3. Use materialized views (if you have no option but to use uuid_generate_v4()).