Remove backward code in AsWithMaterialized
!56976 (merged) introduces backward compatible SQL language syntax fix that works for both PG11 and PG12 by inspecting the DB version.
After upgrading to PG12 and the required minimum PG version is set to 12 this code can be removed. Ideally in 14.1 or 14.2
Related issue: #245323 (closed) Relevant thread: !56976 (comment 535275269)
How
In the Gitlab::Database::AsWithMaterialized
class, remove the materialized_supported?
and materialized_if_supported
methods and fix the test cases.
Search for Gitlab::Database::AsWithMaterialized.materialized_if_supported
in the application code and replace the text with a simple MATERIALIZED
keyword in the raw SQL queries.
Example:
def bucketed_data_sql
<<~SQL
WITH hashed_attributes AS #{Gitlab::Database::AsWithMaterialized.materialized_if_supported} (%{source_query})
SELECT (attr_hash_32_bits & #{BIT_32_NORMALIZED_BUCKET_ID_MASK})::int AS bucket_num,
(31 - floor(log(2, min((attr_hash_32_bits & #{BIT_31_MASK})::int))))::int as bucket_hash
FROM hashed_attributes
GROUP BY 1
SQL
end
Becomes:
def bucketed_data_sql
<<~SQL
WITH hashed_attributes AS MATERIALIZED (%{source_query})
SELECT (attr_hash_32_bits & #{BIT_32_NORMALIZED_BUCKET_ID_MASK})::int AS bucket_num,
(31 - floor(log(2, min((attr_hash_32_bits & #{BIT_31_MASK})::int))))::int as bucket_hash
FROM hashed_attributes
GROUP BY 1
SQL
end
Technical notes
After this one is done please consider reverting !99197 (merged)
Edited by Pavel Shutsin