mfundul
commented
2 years ago Can you post the full query and the hypertable and continuous aggregate definitions please?
Closed brockhaywood closed 2 years ago
mfundul
commented
2 years ago Can you post the full query and the hypertable and continuous aggregate definitions please?
brockhaywood
commented
2 years ago Here's the related query
SELECT "hopthru_routemaster"."route_id", "hopthru_routemaster"."route_long_name", "hopthru_tripreference"."trip_id", "hopthru_tripmaster"."trip_id", "hopthru_tripmetadata"."variant", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."ons"), 0) AS "sum_ons", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."offs"), 0) AS "sum_offs", COUNT(DISTINCT "hopthru_ca_boarding_routes_trips"."service_date") AS "total_days", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."passenger_distance_travelled"), 0) AS "sum_passenger_distance_travelled", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."load"), 0) AS "sum_load", MAX("hopthru_ca_boarding_routes_trips"."maxload") AS "maxload", SUM("hopthru_ca_boarding_routes_trips"."load_count") AS "load_count" FROM "hopthru_ca_boarding_routes_trips" INNER JOIN "hopthru_tripreference" ON ("hopthru_ca_boarding_routes_trips"."trip_reference_id" = "hopthru_tripreference"."id") INNER JOIN "hopthru_routereference" ON ("hopthru_ca_boarding_routes_trips"."route_reference_id" = "hopthru_routereference"."id") INNER JOIN "hopthru_routemaster" ON ("hopthru_routereference"."route_id" = "hopthru_routemaster"."id") INNER JOIN "hopthru_tripmaster" ON ("hopthru_tripreference"."trip_id" = "hopthru_tripmaster"."id") LEFT OUTER JOIN "hopthru_tripmetadata" ON ("hopthru_tripreference"."id" = "hopthru_tripmetadata"."reference_id") WHERE ("hopthru_ca_boarding_routes_trips"."service_date" >= '2020-12-20'::date AND "hopthru_ca_boarding_routes_trips"."service_date" <= '2020-12-24'::date AND "hopthru_ca_boarding_routes_trips"."agency_id" = '13b6264c-bea6-46d2-b59b-cfdd092c254e'::uuid AND EXTRACT('dow' FROM "hopthru_ca_boarding_routes_trips"."service_date") + 1 IN (2, 3, 4, 5, 6) AND "hopthru_ca_boarding_routes_trips"."expanded" = false AND ("hopthru_routereference"."route_id" IS NULL OR "hopthru_routemaster"."hidden" = false) AND "hopthru_routereference"."route_id" IS NOT NULL AND "hopthru_tripreference"."trip_id" IN (SELECT DISTINCT V6."trip_id" FROM "hopthru_tripmetadata" V0 INNER JOIN "hopthru_routemetadata" V3 ON (V0."route_id" = V3."id") INNER JOIN "hopthru_gtfs" V5 ON (V0."gtfs_id" = V5."id") LEFT OUTER JOIN "hopthru_tripreference" V6 ON (V0."reference_id" = V6."id") INNER JOIN "hopthru_routereference" V7 ON (V3."reference_id" = V7."id") WHERE (V0."service_id" IN ('ef949784-5aed-4470-9d74-341fd66ef5ea'::uuid) AND V7."route_id" = '953e34bc-d2b4-476a-a4f3-17da50e7ef1f'::uuid AND ((V5."calculated_start_date" <= '2020-12-24'::date AND V5."calculated_end_date" >= '2020-12-24'::date) OR (V5."calculated_start_date" <= '2020-12-20'::date AND V5."calculated_end_date" >= '2020-12-20'::date) OR (V5."calculated_start_date" >= '2020-12-20'::date AND V5."calculated_end_date" <= '2020-12-24'::date))) GROUP BY V6."trip_id", V0."gtfs_trip_headsign", V0."gtfs_direction_id", (SELECT U0."gtfs_arrival_time" FROM "hopthru_stoptimemetadata" U0 WHERE U0."trip_id" = (V0."id") ORDER BY U0."gtfs_stop_sequence" ASC LIMIT 1), (SELECT U0."gtfs_departure_time" FROM "hopthru_stoptimemetadata" U0 WHERE U0."trip_id" = (V0."id") ORDER BY U0."gtfs_stop_sequence" DESC LIMIT 1), (SELECT U0."gtfs_departure_time_next_day" FROM "hopthru_stoptimemetadata" U0 WHERE U0."trip_id" = (V0."id") ORDER BY U0."gtfs_stop_sequence" ASC LIMIT 1)) AND "hopthru_routereference"."route_id" IN ('953e34bc-d2b4-476a-a4f3-17da50e7ef1f'::uuid)) GROUP BY "hopthru_routemaster"."route_id", "hopthru_routemaster"."route_long_name", "hopthru_tripreference"."trip_id", "hopthru_tripmaster"."trip_id", "hopthru_tripmetadata"."variant"The exception that gets bubbled up in code is
InternalError('invalid memory alloc request size 3299999744\n')The hypertable definition
hopthru=# \d hopthru_boarding
Table "public.hopthru_boarding"
Column | Type | Collation | Nullable | Default
------------------------------+--------------------------+-----------+----------+---------
id | uuid | | not null |
vehicle_num | character varying(50) | | |
ons | numeric(7,2) | | |
offs | numeric(7,2) | | |
load | numeric(7,2) | | |
capacity | integer | | |
dwell_time | integer | | |
lon | double precision | | |
lat | double precision | | |
direction | character varying(50) | | |
agency_id | uuid | | not null |
route_reference_id | uuid | | |
stop_reference_id | uuid | | |
service_date | date | | not null |
trip_reference_id | uuid | | |
stop_date | date | | not null |
stop_time | time without time zone | | |
expanded | boolean | | not null |
apc_file_id | uuid | | |
generated | boolean | | not null |
trip_unexpandable | boolean | | not null |
passenger_distance_travelled | double precision | | |
following_stop_reference_id | uuid | | |
block_id | character varying(150) | | |
created_ts | timestamp with time zone | | |
updated_ts | timestamp with time zone | | |
Indexes:
"hopthru_boa_agency__38985c_idx" btree (agency_id, service_date, stop_time)
"hopthru_boa_agency__eb7f23_idx" btree (agency_id, stop_date, stop_time)
"hopthru_boarding_agency_id_6ff3a38a" btree (agency_id)
"hopthru_boarding_apc_file_id_2bbf8877" btree (apc_file_id)
"hopthru_boarding_following_stop_reference_id_b12a82b6" btree (following_stop_reference_id)
"hopthru_boarding_route_reference_id_45c06f06" btree (route_reference_id)
"hopthru_boarding_service_date_e83d1040" btree (service_date)
"hopthru_boarding_stop_reference_id_2ff3ecff" btree (stop_reference_id)
"hopthru_boarding_trip_reference_id_fd76fa09" btree (trip_reference_id)
Foreign-key constraints:
"hopthru_boarding_agency_id_6ff3a38a_fk_hopthru_agency_id" FOREIGN KEY (agency_id) REFERENCES hopthru_agency(id) DEFERRABLE INITIALLY DEFERRED
"hopthru_boarding_apc_file_id_2bbf8877_fk_hopthru_apc_id" FOREIGN KEY (apc_file_id) REFERENCES hopthru_apc(id) DEFERRABLE INITIALLY DEFERRED
"hopthru_boarding_following_stop_refer_b12a82b6_fk_hopthru_s" FOREIGN KEY (following_stop_reference_id) REFERENCES hopthru_stopreference(id) DEFERRABLE INITIALLY DEFERRED
"hopthru_boarding_route_reference_id_45c06f06_fk_hopthru_r" FOREIGN KEY (route_reference_id) REFERENCES hopthru_routereference(id) DEFERRABLE INITIALLY DEFERRED
"hopthru_boarding_stop_reference_id_2ff3ecff_fk_hopthru_s" FOREIGN KEY (stop_reference_id) REFERENCES hopthru_stopreference(id) DEFERRABLE INITIALLY DEFERRED
"hopthru_boarding_trip_reference_id_fd76fa09_fk_hopthru_t" FOREIGN KEY (trip_reference_id) REFERENCES hopthru_tripreference(id) DEFERRABLE INITIALLY DEFERRED
Triggers:
ts_cagg_invalidation_trigger AFTER INSERT OR DELETE OR UPDATE ON hopthru_boarding FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.continuous_agg_invalidation_trigger('1')
ts_insert_blocker BEFORE INSERT ON hopthru_boarding FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()And the view was created with
CREATE MATERIALIZED VIEW hopthru_ca_boarding_routes_trips
WITH (timescaledb.continuous) AS
SELECT time_bucket('1day', service_date) as bucket,
agency_id,
service_date,
route_reference_id,
trip_reference_id,
expanded,
trip_unexpandable,
date_trunc('hour', stop_time) AS stop_hour,
sum(ons) as ons,
sum (offs) as offs,
max(load) as maxload,
sum(load) as load,
count(load) as load_count,
sum(passenger_distance_travelled) as passenger_distance_travelled
FROM hopthru_boarding
GROUP BY bucket, agency_id, service_date, route_reference_id, trip_reference_id, expanded, trip_unexpandable, stop_hour
WITH NO DATA;
mkindahl
commented
2 years ago @brockhaywood The issue sees to be in aggregating the numeric type, so one possible workaround could be to not use the numeric datatype.
brockhaywood
commented
2 years ago numeric
I can try, for testing, to use an int for that field but our use case requires it to store a floating point number.
brockhaywood
commented
2 years ago numeric
I can try, for testing, to use an int for that field but our use case requires it to store a floating point number.
Should that work, I suppose we could shift the decimal place and store the value as int.
Since this issue is only happening atm in our local docker development environments, I'm hesitant to introduce that source of possible confusion.
mkindahl
commented
2 years ago @brockhaywood Do you have the explain plan for the query?
mkindahl
commented
2 years ago numeric
I can try, for testing, to use an int for that field but our use case requires it to store a floating point number.
Should that work, I suppose we could shift the decimal place and store the value as int.
Since this issue is only happening atm in our local docker development environments, I'm hesitant to introduce that source of possible confusion.
It is clearly a bug, so it needs to be fixed anyway, but in case you were blocked.
brockhaywood
commented
2 years ago @brockhaywood Do you have the explain plan for the query?
test_hopthru=# explain analyze SELECT "hopthru_routemaster"."route_id", "hopthru_routemaster"."route_long_name", "hopthru_tripreference"."trip_id", "hopthru_tripmaster"."trip_id", "hopthru_tripmetadata"."variant", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."ons"), 0) AS "sum_ons", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."offs"), 0) AS "sum_offs", COUNT(DISTINCT "hopthru_ca_boarding_routes_trips"."service_date") AS "total_days", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."passenger_distance_travelled"), 0) AS "sum_passenger_distance_travelled", COALESCE(SUM("hopthru_ca_boarding_routes_trips"."load"), 0) AS "sum_load", MAX("hopthru_ca_boarding_routes_trips"."maxload") AS "maxload", SUM("hopthru_ca_boarding_routes_trips"."load_count") AS "load_count" FROM "hopthru_ca_boarding_routes_trips" INNER JOIN "hopthru_tripreference" ON ("hopthru_ca_boarding_routes_trips"."trip_reference_id" = "hopthru_tripreference"."id") INNER JOIN "hopthru_routereference" ON ("hopthru_ca_boarding_routes_trips"."route_reference_id" = "hopthru_routereference"."id") INNER JOIN "hopthru_routemaster" ON ("hopthru_routereference"."route_id" = "hopthru_routemaster"."id") INNER JOIN "hopthru_tripmaster" ON ("hopthru_tripreference"."trip_id" = "hopthru_tripmaster"."id") LEFT OUTER JOIN "hopthru_tripmetadata" ON ("hopthru_tripreference"."id" = "hopthru_tripmetadata"."reference_id") WHERE ("hopthru_ca_boarding_routes_trips"."service_date" >= '2020-12-20' AND "hopthru_ca_boarding_routes_trips"."service_date" <= '2020-12-24' AND "hopthru_ca_boarding_routes_trips"."agency_id" = 'b5f0ed51-323c-406a-bfee-948f333a9800' AND EXTRACT('dow' FROM "hopthru_ca_boarding_routes_trips"."service_date") + 1 IN (2, 3, 4, 5, 6) AND "hopthru_ca_boarding_routes_trips"."expanded" = False AND ("hopthru_routereference"."route_id" IS NULL OR "hopthru_routemaster"."hidden" = False) AND "hopthru_routereference"."route_id" IS NOT NULL AND "hopthru_tripreference"."trip_id" IN (SELECT DISTINCT V6."trip_id" FROM "hopthru_tripmetadata" V0 INNER JOIN "hopthru_routemetadata" V3 ON (V0."route_id" = V3."id") INNER JOIN "hopthru_gtfs" V5 ON (V0."gtfs_id" = V5."id") LEFT OUTER JOIN "hopthru_tripreference" V6 ON (V0."reference_id" = V6."id") INNER JOIN "hopthru_routereference" V7 ON (V3."reference_id" = V7."id") WHERE (V0."service_id" IN ('0701ee11-7de7-4c25-8b4b-d4e1909ce33d') AND V7."route_id" = '0ac9a630-517f-4325-81da-2c5bb0abc48e' AND ((V5."calculated_start_date" <= '2020-12-24' AND V5."calculated_end_date" >= '2020-12-24') OR (V5."calculated_start_date" <= '2020-12-20' AND V5."calculated_end_date" >= '2020-12-20') OR (V5."calculated_start_date" >= '2020-12-20' AND V5."calculated_end_date" <= '2020-12-24'))) GROUP BY V6."trip_id", V0."gtfs_trip_headsign", V0."gtfs_direction_id", (SELECT U0."gtfs_arrival_time" FROM "hopthru_stoptimemetadata" U0 WHERE U0."trip_id" = (V0."id") ORDER BY U0."gtfs_stop_sequence" ASC LIMIT 1), (SELECT U0."gtfs_departure_time" FROM "hopthru_stoptimemetadata" U0 WHERE U0."trip_id" = (V0."id") ORDER BY U0."gtfs_stop_sequence" DESC LIMIT 1), (SELECT U0."gtfs_departure_time_next_day" FROM "hopthru_stoptimemetadata" U0 WHERE U0."trip_id" = (V0."id") ORDER BY U0."gtfs_stop_sequence" ASC LIMIT 1)) AND "hopthru_routereference"."route_id" IN ('0ac9a630-517f-4325-81da-2c5bb0abc48e')) GROUP BY "hopthru_routemaster"."route_id", "hopthru_routemaster"."route_long_name", "hopthru_tripreference"."trip_id", "hopthru_tripmaster"."trip_id", "hopthru_tripmetadata"."variant";
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=75.71..75.76 rows=1 width=338) (actual time=33.562..34.785 rows=7 loops=1)
Group Key: hopthru_routemaster.route_id, hopthru_routemaster.route_long_name, hopthru_tripreference.trip_id, hopthru_tripmaster.trip_id, hopthru_tripmetadata.variant
-> Sort (cost=75.71..75.71 rows=1 width=310) (actual time=33.488..34.411 rows=22 loops=1)
Sort Key: hopthru_routemaster.route_id, hopthru_routemaster.route_long_name, hopthru_tripreference.trip_id, hopthru_tripmaster.trip_id, hopthru_tripmetadata.variant
Sort Method: quicksort Memory: 30kB
-> Nested Loop Left Join (cost=38.63..75.70 rows=1 width=310) (actual time=25.276..33.967 rows=22 loops=1)
-> Nested Loop (cost=38.49..74.86 rows=1 width=261) (actual time=25.232..32.879 rows=22 loops=1)
-> Nested Loop (cost=38.34..74.03 rows=1 width=245) (actual time=25.186..32.034 rows=22 loops=1)
Join Filter: ((hopthru_routereference.route_id IS NULL) OR (NOT hopthru_routemaster.hidden))
-> Nested Loop (cost=38.19..71.66 rows=1 width=212) (actual time=25.147..31.209 rows=22 loops=1)
Join Filter: ("*SELECT* 1".trip_reference_id = hopthru_tripreference.id)
Rows Removed by Join Filter: 178
-> Nested Loop (cost=36.27..64.77 rows=2 width=48) (actual time=18.062..21.171 rows=8 loops=1)
Join Filter: (hopthru_tripreference.trip_id = v6.trip_id)
Rows Removed by Join Filter: 132
-> Unique (cost=36.27..63.31 rows=1 width=58) (actual time=17.881..18.512 rows=7 loops=1)
-> Group (cost=36.27..63.31 rows=1 width=58) (actual time=17.863..18.370 rows=7 loops=1)
Group Key: v6.trip_id, v0.gtfs_trip_headsign, v0.gtfs_direction_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3))
-> Sort (cost=36.27..36.28 rows=1 width=58) (actual time=17.844..18.227 rows=7 loops=1)
Sort Key: v6.trip_id, v0.gtfs_trip_headsign, v0.gtfs_direction_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3))
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=5.57..36.26 rows=1 width=58) (actual time=3.219..18.066 rows=7 loops=1)
-> Hash Right Join (cost=5.43..6.72 rows=1 width=73) (actual time=0.613..1.161 rows=7 loops=1)
Hash Cond: (v6.id = v0.reference_id)
-> Seq Scan on hopthru_tripreference v6 (cost=0.00..1.20 rows=20 width=32) (actual time=0.018..0.276 rows=20 loops=1)
-> Hash (cost=5.42..5.42 rows=1 width=73) (actual time=0.502..0.584 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=0.42..5.42 rows=1 width=73) (actual time=0.127..0.478 rows=7 loops=1)
-> Nested Loop (cost=0.28..4.72 rows=1 width=16) (actual time=0.077..0.197 rows=1 loops=1)
-> Index Scan using hopthru_routereference_route_id_0ca7dbde on hopthru_routereference v7 (cost=0.14..2.36 rows=1 width=16) (actual time=0.025..0.052 rows=2 loops=1)
Index Cond: (route_id = '0ac9a630-517f-4325-81da-2c5bb0abc48e'::uuid)
-> Index Scan using hopthru_routemetadata_reference_id_8e6b72b4 on hopthru_routemetadata v3 (cost=0.14..2.36 rows=1 width=32) (actual time=0.017..0.025 rows=0 loops=2)
Index Cond: (reference_id = v7.id)
-> Index Scan using hopthru_tripmetadata_route_id_af67a8cb on hopthru_tripmetadata v0 (cost=0.14..0.69 rows=1 width=89) (actual time=0.022..0.102 rows=7 loops=1)
Index Cond: (route_id = v3.id)
Filter: (service_id = '0701ee11-7de7-4c25-8b4b-d4e1909ce33d'::uuid)
Rows Removed by Filter: 7
-> Index Scan using hopthru_gtfs_pkey on hopthru_gtfs v5 (cost=0.14..2.38 rows=1 width=16) (actual time=0.017..0.018 rows=1 loops=7)
Index Cond: (id = v0.gtfs_id)
Filter: (((calculated_start_date <= '2020-12-24'::date) AND (calculated_end_date >= '2020-12-24'::date)) OR ((calculated_start_date <= '2020-12-20'::date) AND (calculated_end_date >= '2020-12-20'::date)) OR ((calculated_start_date >= '2020-12-20'::date) AND (calculated_end_date <= '2020-12-24'::date)))
SubPlan 1
-> Limit (cost=9.00..9.01 rows=1 width=12) (actual time=0.782..0.798 rows=1 loops=7)
-> Sort (cost=9.00..9.08 rows=30 width=12) (actual time=0.756..0.761 rows=1 loops=7)
Sort Key: u0.gtfs_stop_sequence
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on hopthru_stoptimemetadata u0 (cost=1.48..8.85 rows=30 width=12) (actual time=0.051..0.392 rows=33 loops=7)
Recheck Cond: (trip_id = v0.id)
Heap Blocks: exact=12
-> Bitmap Index Scan on hopthru_stoptimemetadata_trip_id_679b5ad0 (cost=0.00..1.47 rows=30 width=0) (actual time=0.016..0.017 rows=33 loops=7)
Index Cond: (trip_id = v0.id)
SubPlan 2
-> Limit (cost=9.00..9.01 rows=1 width=12) (actual time=0.756..0.772 rows=1 loops=7)
-> Sort (cost=9.00..9.08 rows=30 width=12) (actual time=0.729..0.734 rows=1 loops=7)
Sort Key: u0_1.gtfs_stop_sequence DESC
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on hopthru_stoptimemetadata u0_1 (cost=1.48..8.85 rows=30 width=12) (actual time=0.038..0.384 rows=33 loops=7)
Recheck Cond: (trip_id = v0.id)
Heap Blocks: exact=12
-> Bitmap Index Scan on hopthru_stoptimemetadata_trip_id_679b5ad0 (cost=0.00..1.47 rows=30 width=0) (actual time=0.016..0.017 rows=33 loops=7)
Index Cond: (trip_id = v0.id)
SubPlan 3
-> Limit (cost=9.00..9.01 rows=1 width=5) (actual time=0.721..0.737 rows=1 loops=7)
-> Sort (cost=9.00..9.08 rows=30 width=5) (actual time=0.704..0.709 rows=1 loops=7)
Sort Key: u0_2.gtfs_stop_sequence
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on hopthru_stoptimemetadata u0_2 (cost=1.48..8.85 rows=30 width=5) (actual time=0.035..0.345 rows=33 loops=7)
Recheck Cond: (trip_id = v0.id)
Heap Blocks: exact=12
-> Bitmap Index Scan on hopthru_stoptimemetadata_trip_id_679b5ad0 (cost=0.00..1.47 rows=30 width=0) (actual time=0.014..0.015 rows=33 loops=7)
Index Cond: (trip_id = v0.id)
-> Seq Scan on hopthru_tripreference (cost=0.00..1.20 rows=20 width=32) (actual time=0.009..0.168 rows=20 loops=7)
-> Materialize (cost=1.91..6.86 rows=1 width=180) (actual time=0.224..1.010 rows=25 loops=8)
-> Nested Loop (cost=1.91..6.85 rows=1 width=180) (actual time=1.713..6.022 rows=25 loops=1)
Join Filter: ("*SELECT* 1".route_reference_id = hopthru_routereference.id)
Rows Removed by Join Filter: 27
-> Index Scan using hopthru_routereference_route_id_0ca7dbde on hopthru_routereference (cost=0.14..2.36 rows=1 width=32) (actual time=0.031..0.118 rows=2 loops=1)
Index Cond: ((route_id IS NOT NULL) AND (route_id = '0ac9a630-517f-4325-81da-2c5bb0abc48e'::uuid))
-> Append (cost=1.77..4.47 rows=2 width=180) (actual time=0.689..2.563 rows=26 loops=2)
-> Subquery Scan on "*SELECT* 1" (cost=1.77..1.85 rows=1 width=180) (actual time=0.673..1.953 rows=26 loops=2)
-> GroupAggregate (cost=1.77..1.84 rows=1 width=218) (actual time=0.656..1.505 rows=26 loops=2)
Group Key: _materialized_hypertable_11.bucket, _materialized_hypertable_11.agency_id, _materialized_hypertable_11.service_date, _materialized_hypertable_11.route_reference_id, _materialized_hypertable_11.trip_reference_id, _materialized_hypertable_11.expanded, _materialized_hypertable_11.trip_unexpandable, _materialized_hypertable_11.stop_hour
-> Sort (cost=1.77..1.78 rows=1 width=214) (actual time=0.569..0.825 rows=26 loops=2)
Sort Key: _materialized_hypertable_11.bucket, _materialized_hypertable_11.service_date, _materialized_hypertable_11.route_reference_id, _materialized_hypertable_11.trip_reference_id, _materialized_hypertable_11.expanded, _materialized_hypertable_11.trip_unexpandable, _materialized_hypertable_11.stop_hour
Sort Method: quicksort Memory: 31kB
-> Custom Scan (ChunkAppend) on _materialized_hypertable_11 (cost=0.14..1.76 rows=1 width=214) (actual time=0.051..0.793 rows=26 loops=1)
Chunks excluded during startup: 0
-> Index Scan using _hyper_11_9_chunk__materialized_hypertable_11_agency_id_bucket_ on _hyper_11_9_chunk (cost=0.14..1.76 rows=1 width=214) (actual time=0.033..0.318 rows=26 loops=1)
Index Cond: ((agency_id = 'b5f0ed51-323c-406a-bfee-948f333a9800'::uuid) AND (bucket < COALESCE(_timescaledb_internal.to_date(_timescaledb_internal.cagg_watermark(11)), '-infinity'::date)))
Filter: ((NOT expanded) AND (service_date >= '2020-12-20'::date) AND (service_date <= '2020-12-24'::date) AND ((date_part('dow'::text, (service_date)::timestamp without time zone) + '1'::double precision) = ANY ('{2,3,4,5,6}'::double precision[])))
Rows Removed by Filter: 2
-> Subquery Scan on "*SELECT* 2" (cost=2.53..2.61 rows=1 width=180) (actual time=0.066..0.098 rows=0 loops=2)
-> GroupAggregate (cost=2.53..2.60 rows=1 width=218) (actual time=0.051..0.076 rows=0 loops=2)
Group Key: (time_bucket('1 day'::interval, hopthru_boarding.service_date)), hopthru_boarding.agency_id, hopthru_boarding.service_date, hopthru_boarding.route_reference_id, hopthru_boarding.trip_reference_id, hopthru_boarding.expanded, hopthru_boarding.trip_unexpandable, (date_trunc('hour'::text, (hopthru_boarding.stop_time)::interval))
-> Sort (cost=2.53..2.54 rows=1 width=92) (actual time=0.034..0.053 rows=0 loops=2)
Sort Key: (time_bucket('1 day'::interval, hopthru_boarding.service_date)), hopthru_boarding.service_date, hopthru_boarding.route_reference_id, hopthru_boarding.trip_reference_id, hopthru_boarding.expanded, hopthru_boarding.trip_unexpandable, (date_trunc('hour'::text, (hopthru_boarding.stop_time)::interval))
Sort Method: quicksort Memory: 25kB
-> Custom Scan (ChunkAppend) on hopthru_boarding (cost=0.28..2.52 rows=1 width=92) (actual time=0.043..0.065 rows=0 loops=1)
Chunks excluded during startup: 0
-> Index Scan using _hyper_1_159_chunk_hopthru_boa_agency__38985c_idx on _hyper_1_159_chunk (cost=0.28..2.52 rows=1 width=80) (actual time=0.027..0.034 rows=0 loops=1)
Index Cond: ((agency_id = 'b5f0ed51-323c-406a-bfee-948f333a9800'::uuid) AND (service_date >= COALESCE(_timescaledb_internal.to_date(_timescaledb_internal.cagg_watermark(11)), '-infinity'::date)) AND (service_date >= '2020-12-20'::date) AND (service_date <= '2020-12-24'::date))
Filter: ((NOT expanded) AND ((date_part('dow'::text, (service_date)::timestamp without time zone) + '1'::double precision) = ANY ('{2,3,4,5,6}'::double precision[])))
-> Index Scan using hopthru_routemaster_pkey on hopthru_routemaster (cost=0.15..2.37 rows=1 width=66) (actual time=0.010..0.011 rows=1 loops=22)
Index Cond: (id = '0ac9a630-517f-4325-81da-2c5bb0abc48e'::uuid)
-> Index Scan using hopthru_tripmaster_pkey on hopthru_tripmaster (cost=0.15..0.83 rows=1 width=48) (actual time=0.013..0.013 rows=1 loops=22)
Index Cond: (id = hopthru_tripreference.trip_id)
-> Index Scan using hopthru_tripmetadata_reference_id_a0080007 on hopthru_tripmetadata (cost=0.14..0.82 rows=1 width=81) (actual time=0.010..0.017 rows=1 loops=22)
Index Cond: (reference_id = hopthru_tripreference.id)
Planning Time: 3.730 ms
Execution Time: 35.476 ms
(109 rows)numeric
I can try, for testing, to use an int for that field but our use case requires it to store a floating point number.
Should that work, I suppose we could shift the decimal place and store the value as int. Since this issue is only happening atm in our local docker development environments, I'm hesitant to introduce that source of possible confusion.
It is clearly a bug, so it needs to be fixed anyway, but in case you were blocked.
No, i can usually workaround the issue by running the test suite multiple times and it will not exhibit the crash in some runs. It also hasn't exhibited this behavior when running in CI.
mkindahl
commented
2 years ago @brockhaywood Do you have precise instructions (preferably SQL and docker commands) for reproducing the issue, even if it does not happen all the time? I just see the table definition above, but it would be useful to have data that triggers the issue as well since it's hard to reproduce locally.
brockhaywood
commented
2 years ago @brockhaywood Do you have precise instructions (preferably SQL and docker commands) for reproducing the issue, even if it does not happen all the time? I just see the table definition above, but it would be useful to have data that triggers the issue as well since it's hard to reproduce locally.
I'll try to put together a data set that can reproduce this more reliably this week.
mfundul
commented
2 years ago @brockhaywood I have a suspicion that in your case this bug might cause incorrect results when it does not crash. Can you in any way verify the correctness of the results?
brockhaywood
commented
2 years ago @brockhaywood I have a suspicion that in your case this bug might cause incorrect results when it does not crash. Can you in any way verify the correctness of the results?
@brockhaywood Do you have precise instructions (preferably SQL and docker commands) for reproducing the issue, even if it does not happen all the time? I just see the table definition above, but it would be useful to have data that triggers the issue as well since it's hard to reproduce locally.
here's a gist with the data, a subset of the docker compose file, and the query im using. with this setup, i can reproduce it everytime, right now (maybe there is some other environmental factors which have me in this state?).
https://gist.github.com/brockhaywood/8c687210a30e4828d2a85d2ee73265df
brockhaywood
commented
2 years ago @brockhaywood I have a suspicion that in your case this bug might cause incorrect results when it does not crash. Can you in any way verify the correctness of the results?
yeah, the way we build the queries, i can pretty easily run it against the hypertable instead of the continous aggregate. assuming it gives the same results, i think that would be sufficient to verify the results are accurate.
brockhaywood
commented
2 years ago Aggregating against the hypertable and the continous aggregate does appear to produce the same results.
brockhaywood
commented
2 years ago @brockhaywood I have a suspicion that in your case this bug might cause incorrect results when it does not crash. Can you in any way verify the correctness of the results?
@brockhaywood Do you have precise instructions (preferably SQL and docker commands) for reproducing the issue, even if it does not happen all the time? I just see the table definition above, but it would be useful to have data that triggers the issue as well since it's hard to reproduce locally.
here's a gist with the data, a subset of the docker compose file, and the query im using. with this setup, i can reproduce it everytime, right now (maybe there is some other environmental factors which have me in this state?).
https://gist.github.com/brockhaywood/8c687210a30e4828d2a85d2ee73265df
FWIW, now that i've created that test case.. i can't reproduce the error with that pg_dump. seems like there is more to getting into that state than just loading the data and running the query that fails. I've run the query 10k times against the dataset above without error.
mkindahl
commented
2 years ago @brockhaywood I tried running the query using the dataset you provided and it does not crash (but it does not return any rows). Note that the dump file was partial. Not sure if any critical data was missing from the file.
brockhaywood
commented
2 years ago hm, i just re-ran using the provided gist and i get 7 rows back from that query.
but i also can't don't have a reliable way of reproducing the error using just a sql dump and running that query.
mkindahl
commented
2 years ago @brockhaywood The last COPY command in the test_hopthru.sql file does not end with a \., which is what I expected. Are you sure that that the gist is complete? I got zero rows when I truncated the copy command there and added the \. manually.
brockhaywood
commented
2 years ago https://gist.github.com/brockhaywood/8c687210a30e4828d2a85d2ee73265df
OH! yeah, that is cut off. i wonder if there is some upper bound on gist length.
brockhaywood
commented
2 years ago @brockhaywood The last
COPYcommand in thetest_hopthru.sqlfile does not end with a\., which is what I expected. Are you sure that that the gist is complete? I got zero rows when I truncated the copy command there and added the\.manually.
Okay, not sure what i did earlier but that looks better to me.
mkindahl
commented
2 years ago https://gist.github.com/brockhaywood/8c687210a30e4828d2a85d2ee73265df
OH! yeah, that is cut off. i wonder if there is some upper bound on gist length.
Could be. My main worry is that some data is missing that would trigger the error, so do you have the complete file?
brockhaywood
commented
2 years ago Yeah, when i look at it now, it says it's truncated with a link at the top of the file to the full version.
This file has been truncated, but you can [view the full file](https://gist.github.com/brockhaywood/8c687210a30e4828d2a85d2ee73265df/raw/38023b88ec92de60010cb8dd1a8799f4d36f0998/test_hopthru.sql).@brockhaywood Right, missed that. I've managed to download the full file now, but still cannot reproduce the problem.
mkindahl
commented
2 years ago Possible duplicate of #3248.
mkindahl
commented
2 years ago @brockhaywood We have a potential fix in #4219, which is released in 2.6.1, but if would be good to get verification that it fixes the issue. If you can verify that it fixes the issue, it would be great.
brockhaywood
commented
2 years ago @brockhaywood We have a potential fix in #4219, which is released in 2.6.1, but if would be good to get verification that it fixes the issue. If you can verify that it fixes the issue, it would be great.
Sounds good!
I pulled timescale/timescaledb-ha:pg12-latest but still see 2.5.0. Does it take some time before the docker images get updated?
mkindahl
commented
2 years ago @brockhaywood Yes, it usually takes a few days before that is updated, but let me check the status.
mkindahl
commented
2 years ago @brockhaywood Docker images were updated yesterday, so I think this should have what you need: https://hub.docker.com/r/timescale/timescaledb-ha/tags?page=1&name=2.6.1
brockhaywood
commented
2 years ago @brockhaywood Docker images were updated yesterday, so I think this should have what you need: https://hub.docker.com/r/timescale/timescaledb-ha/tags?page=1&name=2.6.1
Is pg12 EOL for docker images? We're running pg12 on Timescale cloud and all my tests previously have been on pg12.
mkindahl
commented
2 years ago We have not EOLed PG12 yet, but the HA images contain all supported versions and you have to set the path for it specifically, for example:
docker run \
-e PATH=/usr/lib/postgresql/12/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin \
-e POSTGRES_HOST_AUTH_METHOD=trust \
timescale/timescaledb-ha:pg14.2-ts2.6.1-latestIt is ugly, I know. We are working on having a more structured suite of docker images, but we're not there yet.
brockhaywood
commented
2 years ago We have not EOLed PG12 yet, but the HA images contain all supported versions and you have to set the path for it specifically, for example:
docker run \ -e PATH=/usr/lib/postgresql/12/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin \ -e POSTGRES_HOST_AUTH_METHOD=trust \ timescale/timescaledb-ha:pg14.2-ts2.6.1-latestIt is ugly, I know. We are working on having a more structured suite of docker images, but we're not there yet.
Ah, I'll give that a shot. It used to be that a pg12 tag was created (or so I think).
brockhaywood
commented
2 years ago The fix looks good to me! I've run through the test suite a dozen times which would have caused the issue to have occurred.
mkindahl
commented
2 years ago @brockhaywood Thanks for verifying!
Ah, I'll give that a shot. It used to be that a pg12 tag was created (or so I think).
Yeah, I think this is what most expect so we should probably make that available.
What type of bug is this?
Crash
What subsystems and features are affected?
Continuous aggregate, Query executor
What happened?
I'm seeing an inconsistent crash when running timescale on my development machine within docker. This happens most commonly when I run my unit test suite.
I'm using the timescale image:
timescale/timescaledb-postgis:latest-pg12When a particular query runs, it causes a postgres segfault which restarts the database and crashes the test suite.
TimescaleDB version affected
2.5.0
PostgreSQL version used
12.9
What operating system did you use?
Debian GNU/Linux 10 (buster)
What installation method did you use?
Docker
What platform did you run on?
Other
Relevant log output and stack trace
How can we reproduce the bug?
No response