postgres/timescaledb
1
0
Fork 0
mirror of https://github.com/timescale/timescaledb.git synced 2025-04-20 18:11:02 +08:00
Code Issues Packages Projects Releases Wiki Activity
timescaledb/test/expected/insert-17.out
Fabrízio de Royes Mello a627861e8a PG17: Regression Tests 
Added missing template output tests and also converted others to
template due to output changes.

Also fixed some small pieces of code to properly run in all current
supported Postgres versions (14, 15, 16 and 17)
2024-09-05 13:20:24 -03:00

677 lines
40 KiB
Plaintext
Raw Permalink Blame History

-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
SET enable_seqscan TO off;
\ir include/insert_two_partitions.sql
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
CREATE TABLE PUBLIC."two_Partitions" (
"timeCustom" BIGINT NOT NULL,
device_id TEXT NOT NULL,
series_0 DOUBLE PRECISION NULL,
series_1 DOUBLE PRECISION NULL,
series_2 DOUBLE PRECISION NULL,
series_bool BOOLEAN NULL
);
CREATE INDEX ON PUBLIC."two_Partitions" (device_id, "timeCustom" DESC NULLS LAST) WHERE device_id IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_0) WHERE series_0 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_1) WHERE series_1 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_2) WHERE series_2 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_bool) WHERE series_bool IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, device_id);
SELECT * FROM create_hypertable('"public"."two_Partitions"'::regclass, 'timeCustom'::name, 'device_id'::name, associated_schema_name=>'_timescaledb_internal'::text, number_partitions => 2, chunk_time_interval=>_timescaledb_functions.interval_to_usec('1 month'));
hypertable_id | schema_name | table_name | created
---------------+-------------+----------------+---------
1 | public | two_Partitions | t
(1 row)
\set QUIET off
BEGIN;
BEGIN
\COPY public."two_Partitions" FROM 'data/ds1_dev1_1.tsv' NULL AS '';
COPY 7
COMMIT;
COMMIT
INSERT INTO public."two_Partitions"("timeCustom", device_id, series_0, series_1) VALUES
(1257987600000000000, 'dev1', 1.5, 1),
(1257987600000000000, 'dev1', 1.5, 2),
(1257894000000000000, 'dev2', 1.5, 1),
(1257894002000000000, 'dev1', 2.5, 3);
INSERT 0 4
INSERT INTO "two_Partitions"("timeCustom", device_id, series_0, series_1) VALUES
(1257894000000000000, 'dev2', 1.5, 2);
INSERT 0 1
\set QUIET on
SELECT * FROM test.show_columnsp('_timescaledb_internal.%_hyper%');
Relation | Kind | Column | Column type | NotNull
------------------------------------------------------------------------------------+------+-------------+------------------+---------
_timescaledb_internal._hyper_1_1_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_1_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_1_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_1_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_1_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_1_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
_timescaledb_internal._hyper_1_2_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_2_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_2_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_2_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_2_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_2_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
_timescaledb_internal._hyper_1_3_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_3_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_3_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_3_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_3_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_3_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
_timescaledb_internal._hyper_1_4_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_4_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_4_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_4_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_4_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_4_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
(76 rows)
SELECT * FROM test.show_indexesp('_timescaledb_internal._hyper%');
Table | Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace
----------------------------------------+------------------------------------------------------------------------------------+--------------------------+------+--------+---------+-----------+------------
_timescaledb_internal._hyper_1_1_chunk | _timescaledb_internal."_hyper_1_1_chunk_two_Partitions_device_id_timeCustom_idx" | {device_id,timeCustom} | | f | f | f |
_timescaledb_internal._hyper_1_1_chunk | _timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_0_idx" | {timeCustom,series_0} | | f | f | f |
_timescaledb_internal._hyper_1_1_chunk | _timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_1_idx" | {timeCustom,series_1} | | f | f | f |
_timescaledb_internal._hyper_1_1_chunk | _timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_2_idx" | {timeCustom,series_2} | | f | f | f |
_timescaledb_internal._hyper_1_1_chunk | _timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_bool_idx" | {timeCustom,series_bool} | | f | f | f |
_timescaledb_internal._hyper_1_1_chunk | _timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_device_id_idx" | {timeCustom,device_id} | | f | f | f |
_timescaledb_internal._hyper_1_1_chunk | _timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_idx" | {timeCustom} | | f | f | f |
_timescaledb_internal._hyper_1_2_chunk | _timescaledb_internal."_hyper_1_2_chunk_two_Partitions_device_id_timeCustom_idx" | {device_id,timeCustom} | | f | f | f |
_timescaledb_internal._hyper_1_2_chunk | _timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_0_idx" | {timeCustom,series_0} | | f | f | f |
_timescaledb_internal._hyper_1_2_chunk | _timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_1_idx" | {timeCustom,series_1} | | f | f | f |
_timescaledb_internal._hyper_1_2_chunk | _timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_2_idx" | {timeCustom,series_2} | | f | f | f |
_timescaledb_internal._hyper_1_2_chunk | _timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_bool_idx" | {timeCustom,series_bool} | | f | f | f |
_timescaledb_internal._hyper_1_2_chunk | _timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_device_id_idx" | {timeCustom,device_id} | | f | f | f |
_timescaledb_internal._hyper_1_2_chunk | _timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_idx" | {timeCustom} | | f | f | f |
_timescaledb_internal._hyper_1_3_chunk | _timescaledb_internal."_hyper_1_3_chunk_two_Partitions_device_id_timeCustom_idx" | {device_id,timeCustom} | | f | f | f |
_timescaledb_internal._hyper_1_3_chunk | _timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_0_idx" | {timeCustom,series_0} | | f | f | f |
_timescaledb_internal._hyper_1_3_chunk | _timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_1_idx" | {timeCustom,series_1} | | f | f | f |
_timescaledb_internal._hyper_1_3_chunk | _timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_2_idx" | {timeCustom,series_2} | | f | f | f |
_timescaledb_internal._hyper_1_3_chunk | _timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_bool_idx" | {timeCustom,series_bool} | | f | f | f |
_timescaledb_internal._hyper_1_3_chunk | _timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_device_id_idx" | {timeCustom,device_id} | | f | f | f |
_timescaledb_internal._hyper_1_3_chunk | _timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_idx" | {timeCustom} | | f | f | f |
_timescaledb_internal._hyper_1_4_chunk | _timescaledb_internal."_hyper_1_4_chunk_two_Partitions_device_id_timeCustom_idx" | {device_id,timeCustom} | | f | f | f |
_timescaledb_internal._hyper_1_4_chunk | _timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_0_idx" | {timeCustom,series_0} | | f | f | f |
_timescaledb_internal._hyper_1_4_chunk | _timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_1_idx" | {timeCustom,series_1} | | f | f | f |
_timescaledb_internal._hyper_1_4_chunk | _timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_2_idx" | {timeCustom,series_2} | | f | f | f |
_timescaledb_internal._hyper_1_4_chunk | _timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_bool_idx" | {timeCustom,series_bool} | | f | f | f |
_timescaledb_internal._hyper_1_4_chunk | _timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_device_id_idx" | {timeCustom,device_id} | | f | f | f |
_timescaledb_internal._hyper_1_4_chunk | _timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_idx" | {timeCustom} | | f | f | f |
(28 rows)
SELECT id, hypertable_id, schema_name, table_name, compressed_chunk_id, dropped, status, osm_chunk FROM _timescaledb_catalog.chunk;
id | hypertable_id | schema_name | table_name | compressed_chunk_id | dropped | status | osm_chunk
----+---------------+-----------------------+------------------+---------------------+---------+--------+-----------
1 | 1 | _timescaledb_internal | _hyper_1_1_chunk | | f | 0 | f
2 | 1 | _timescaledb_internal | _hyper_1_2_chunk | | f | 0 | f
3 | 1 | _timescaledb_internal | _hyper_1_3_chunk | | f | 0 | f
4 | 1 | _timescaledb_internal | _hyper_1_4_chunk | | f | 0 | f
(4 rows)
SELECT * FROM "two_Partitions" ORDER BY "timeCustom", device_id, series_0, series_1;
timeCustom | device_id | series_0 | series_1 | series_2 | series_bool
---------------------+-----------+----------+----------+----------+-------------
1257894000000000000 | dev1 | 1.5 | 1 | 2 | t
1257894000000000000 | dev1 | 1.5 | 2 | |
1257894000000000000 | dev2 | 1.5 | 1 | |
1257894000000000000 | dev2 | 1.5 | 2 | |
1257894000000001000 | dev1 | 2.5 | 3 | |
1257894001000000000 | dev1 | 3.5 | 4 | |
1257894002000000000 | dev1 | 2.5 | 3 | |
1257894002000000000 | dev1 | 5.5 | 6 | | t
1257894002000000000 | dev1 | 5.5 | 7 | | f
1257897600000000000 | dev1 | 4.5 | 5 | | f
1257987600000000000 | dev1 | 1.5 | 1 | |
1257987600000000000 | dev1 | 1.5 | 2 | |
(12 rows)
SELECT * FROM ONLY "two_Partitions";
timeCustom | device_id | series_0 | series_1 | series_2 | series_bool
------------+-----------+----------+----------+----------+-------------
(0 rows)
CREATE TABLE error_test(time timestamp, temp float8, device text NOT NULL);
SELECT create_hypertable('error_test', 'time', 'device', 2);
WARNING: column type "timestamp without time zone" used for "time" does not follow best practices
NOTICE: adding not-null constraint to column "time"
create_hypertable
-------------------------
(2,public,error_test,t)
(1 row)
\set QUIET off
INSERT INTO error_test VALUES ('Mon Mar 20 09:18:20.1 2017', 21.3, 'dev1');
INSERT 0 1
\set ON_ERROR_STOP 0
-- generate insert error
INSERT INTO error_test VALUES ('Mon Mar 20 09:18:22.3 2017', 21.1, NULL);
ERROR: null value in column "device" of relation "_hyper_2_6_chunk" violates not-null constraint
\set ON_ERROR_STOP 1
INSERT INTO error_test VALUES ('Mon Mar 20 09:18:25.7 2017', 22.4, 'dev2');
INSERT 0 1
\set QUIET on
SELECT * FROM error_test;
time | temp | device
----------------------------+------+--------
Mon Mar 20 09:18:20.1 2017 | 21.3 | dev1
Mon Mar 20 09:18:25.7 2017 | 22.4 | dev2
(2 rows)
--test character(9) partition keys since there were issues with padding causing partitioning errors
CREATE TABLE tick_character (
symbol character(9) NOT NULL,
mid REAL NOT NULL,
spread REAL NOT NULL,
time TIMESTAMPTZ NOT NULL
);
SELECT create_hypertable ('tick_character', 'time', 'symbol', 2);
create_hypertable
-----------------------------
(3,public,tick_character,t)
(1 row)
INSERT INTO tick_character ( symbol, mid, spread, time ) VALUES ( 'GBPJPY', 142.639000, 5.80, 'Mon Mar 20 09:18:22.3 2017') RETURNING time, symbol, mid;
time | symbol | mid
--------------------------------+-----------+---------
Mon Mar 20 09:18:22.3 2017 PDT | GBPJPY | 142.639
(1 row)
SELECT * FROM tick_character;
symbol | mid | spread | time
-----------+---------+--------+--------------------------------
GBPJPY | 142.639 | 5.8 | Mon Mar 20 09:18:22.3 2017 PDT
(1 row)
CREATE TABLE date_col_test(time date, temp float8, device text NOT NULL);
SELECT create_hypertable('date_col_test', 'time', 'device', 1000, chunk_time_interval => INTERVAL '1 Day');
NOTICE: adding not-null constraint to column "time"
create_hypertable
----------------------------
(4,public,date_col_test,t)
(1 row)
INSERT INTO date_col_test
VALUES ('2001-02-01', 98, 'dev1'),
('2001-03-02', 98, 'dev1');
SELECT * FROM date_col_test WHERE time > '2001-01-01';
time | temp | device
------------+------+--------
02-01-2001 | 98 | dev1
03-02-2001 | 98 | dev1
(2 rows)
-- Out-of-order insertion regression test.
-- this used to trip an assert in subspace_store.c checking that
-- max_open_chunks_per_insert was obeyed
set timescaledb.max_open_chunks_per_insert=1;
CREATE TABLE chunk_assert_fail(i bigint, j bigint);
SELECT create_hypertable('chunk_assert_fail', 'i', 'j', 1000, chunk_time_interval=>1);
NOTICE: adding not-null constraint to column "i"
create_hypertable
--------------------------------
(5,public,chunk_assert_fail,t)
(1 row)
insert into chunk_assert_fail values (1, 1), (1, 2), (2,1);
select * from chunk_assert_fail;
i | j
---+---
1 | 1
1 | 2
2 | 1
(3 rows)
CREATE TABLE one_space_test(time timestamp, temp float8, device text NOT NULL);
SELECT create_hypertable('one_space_test', 'time', 'device', 1);
WARNING: column type "timestamp without time zone" used for "time" does not follow best practices
NOTICE: adding not-null constraint to column "time"
create_hypertable
-----------------------------
(6,public,one_space_test,t)
(1 row)
INSERT INTO one_space_test VALUES
('2001-01-01 01:01:01', 1.0, 'device'),
('2002-01-01 01:02:01', 1.0, 'device');
SELECT * FROM one_space_test;
time | temp | device
--------------------------+------+--------
Mon Jan 01 01:01:01 2001 | 1 | device
Tue Jan 01 01:02:01 2002 | 1 | device
(2 rows)
--CTE & EXPLAIN ANALYZE TESTS
WITH insert_cte as (
INSERT INTO one_space_test VALUES
('2001-01-01 01:02:01', 1.0, 'device')
RETURNING *)
SELECT * FROM insert_cte;
time | temp | device
--------------------------+------+--------
Mon Jan 01 01:02:01 2001 | 1 | device
(1 row)
EXPLAIN (analyze, costs off, timing off) --can't turn summary off in 9.6 so instead grep it away at end.
WITH insert_cte as (
INSERT INTO one_space_test VALUES
('2001-01-01 01:03:01', 1.0, 'device')
)
SELECT 1 \g | grep -v "Planning" | grep -v "Execution"
QUERY PLAN
-------------------------------------------------------------------------
Result (actual rows=1 loops=1)
CTE insert_cte
-> Custom Scan (HypertableModify) (actual rows=0 loops=1)
-> Insert on one_space_test (actual rows=0 loops=1)
-> Custom Scan (ChunkDispatch) (actual rows=1 loops=1)
-> Result (actual rows=1 loops=1)
(8 rows)
-- INSERTs can exclude chunks based on constraints
EXPLAIN (costs off) INSERT INTO chunk_assert_fail SELECT i, j FROM chunk_assert_fail;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on chunk_assert_fail
-> Custom Scan (ChunkDispatch)
-> Append
-> Index Only Scan using _hyper_5_11_chunk_chunk_assert_fail_j_i_idx on _hyper_5_11_chunk
-> Index Only Scan using _hyper_5_12_chunk_chunk_assert_fail_j_i_idx on _hyper_5_12_chunk
-> Index Only Scan using _hyper_5_13_chunk_chunk_assert_fail_j_i_idx on _hyper_5_13_chunk
(7 rows)
EXPLAIN (costs off) INSERT INTO chunk_assert_fail SELECT i, j FROM chunk_assert_fail WHERE i < 1;
QUERY PLAN
--------------------------------------------
Custom Scan (HypertableModify)
-> Insert on chunk_assert_fail
-> Custom Scan (ChunkDispatch)
-> Result
One-Time Filter: false
(5 rows)
EXPLAIN (costs off) INSERT INTO chunk_assert_fail SELECT i, j FROM chunk_assert_fail WHERE i = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on chunk_assert_fail
-> Custom Scan (ChunkDispatch)
-> Append
-> Index Scan using _hyper_5_11_chunk_chunk_assert_fail_i_idx on _hyper_5_11_chunk
Index Cond: (i = 1)
-> Index Scan using _hyper_5_12_chunk_chunk_assert_fail_i_idx on _hyper_5_12_chunk
Index Cond: (i = 1)
(8 rows)
EXPLAIN (costs off) INSERT INTO chunk_assert_fail SELECT i, j FROM chunk_assert_fail WHERE i > 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on chunk_assert_fail
-> Custom Scan (ChunkDispatch)
-> Index Scan using _hyper_5_13_chunk_chunk_assert_fail_i_idx on _hyper_5_13_chunk
Index Cond: (i > 1)
(5 rows)
INSERT INTO chunk_assert_fail SELECT i, j FROM chunk_assert_fail WHERE i > 1;
EXPLAIN (costs off) INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time < 'infinity' LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on one_space_test
-> Custom Scan (ChunkDispatch)
-> Limit
-> Append
-> Index Scan using _hyper_6_14_chunk_one_space_test_time_idx on _hyper_6_14_chunk
Index Cond: ("time" < 'infinity'::timestamp without time zone)
-> Index Scan using _hyper_6_15_chunk_one_space_test_time_idx on _hyper_6_15_chunk
Index Cond: ("time" < 'infinity'::timestamp without time zone)
(9 rows)
EXPLAIN (costs off) INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time >= 'infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on one_space_test
-> Custom Scan (ChunkDispatch)
-> Limit
-> Result
One-Time Filter: false
(6 rows)
EXPLAIN (costs off) INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time <= '-infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on one_space_test
-> Custom Scan (ChunkDispatch)
-> Limit
-> Result
One-Time Filter: false
(6 rows)
EXPLAIN (costs off) INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time > '-infinity' LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on one_space_test
-> Custom Scan (ChunkDispatch)
-> Limit
-> Append
-> Index Scan using _hyper_6_14_chunk_one_space_test_time_idx on _hyper_6_14_chunk
Index Cond: ("time" > '-infinity'::timestamp without time zone)
-> Index Scan using _hyper_6_15_chunk_one_space_test_time_idx on _hyper_6_15_chunk
Index Cond: ("time" > '-infinity'::timestamp without time zone)
(9 rows)
INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time < 'infinity' LIMIT 1;
INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time >= 'infinity' LIMIT 1;
INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time <= '-infinity' LIMIT 1;
INSERT INTO one_space_test SELECT * FROM one_space_test WHERE time > '-infinity' LIMIT 1;
CREATE TABLE timestamp_inf(time TIMESTAMP);
SELECT create_hypertable('timestamp_inf', 'time');
WARNING: column type "timestamp without time zone" used for "time" does not follow best practices
NOTICE: adding not-null constraint to column "time"
create_hypertable
----------------------------
(7,public,timestamp_inf,t)
(1 row)
INSERT INTO timestamp_inf VALUES ('2018/01/02'), ('2019/01/02');
EXPLAIN (costs off) INSERT INTO timestamp_inf SELECT * FROM timestamp_inf
WHERE time < 'infinity' LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on timestamp_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Append
-> Index Only Scan using _hyper_7_16_chunk_timestamp_inf_time_idx on _hyper_7_16_chunk
Index Cond: ("time" < 'infinity'::timestamp without time zone)
-> Index Only Scan using _hyper_7_17_chunk_timestamp_inf_time_idx on _hyper_7_17_chunk
Index Cond: ("time" < 'infinity'::timestamp without time zone)
(9 rows)
EXPLAIN (costs off) INSERT INTO timestamp_inf SELECT * FROM timestamp_inf
WHERE time >= 'infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on timestamp_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Result
One-Time Filter: false
(6 rows)
EXPLAIN (costs off) INSERT INTO timestamp_inf SELECT * FROM timestamp_inf
WHERE time <= '-infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on timestamp_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Result
One-Time Filter: false
(6 rows)
EXPLAIN (costs off) INSERT INTO timestamp_inf SELECT * FROM timestamp_inf
WHERE time > '-infinity' LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on timestamp_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Append
-> Index Only Scan using _hyper_7_16_chunk_timestamp_inf_time_idx on _hyper_7_16_chunk
Index Cond: ("time" > '-infinity'::timestamp without time zone)
-> Index Only Scan using _hyper_7_17_chunk_timestamp_inf_time_idx on _hyper_7_17_chunk
Index Cond: ("time" > '-infinity'::timestamp without time zone)
(9 rows)
CREATE TABLE date_inf(time DATE);
SELECT create_hypertable('date_inf', 'time');
NOTICE: adding not-null constraint to column "time"
create_hypertable
-----------------------
(8,public,date_inf,t)
(1 row)
INSERT INTO date_inf VALUES ('2018/01/02'), ('2019/01/02');
EXPLAIN (costs off) INSERT INTO date_inf SELECT * FROM date_inf
WHERE time < 'infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on date_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Append
-> Index Only Scan using _hyper_8_18_chunk_date_inf_time_idx on _hyper_8_18_chunk
Index Cond: ("time" < 'infinity'::date)
-> Index Only Scan using _hyper_8_19_chunk_date_inf_time_idx on _hyper_8_19_chunk
Index Cond: ("time" < 'infinity'::date)
(9 rows)
EXPLAIN (costs off) INSERT INTO date_inf SELECT * FROM date_inf
WHERE time >= 'infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on date_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Result
One-Time Filter: false
(6 rows)
EXPLAIN (costs off) INSERT INTO date_inf SELECT * FROM date_inf
WHERE time <= '-infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on date_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Result
One-Time Filter: false
(6 rows)
EXPLAIN (costs off) INSERT INTO date_inf SELECT * FROM date_inf
WHERE time > '-infinity' LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Custom Scan (HypertableModify)
-> Insert on date_inf
-> Custom Scan (ChunkDispatch)
-> Limit
-> Append
-> Index Only Scan using _hyper_8_18_chunk_date_inf_time_idx on _hyper_8_18_chunk
Index Cond: ("time" > '-infinity'::date)
-> Index Only Scan using _hyper_8_19_chunk_date_inf_time_idx on _hyper_8_19_chunk
Index Cond: ("time" > '-infinity'::date)
(9 rows)
-- test INSERT with cached plans / plpgsql functions
-- https://github.com/timescale/timescaledb/issues/1809
CREATE TABLE status_table(a int, b int, last_ts timestamptz, UNIQUE(a,b));
CREATE TABLE metrics(time timestamptz NOT NULL, value float);
CREATE TABLE metrics2(time timestamptz NOT NULL, value float);
SELECT (create_hypertable(t,'time')).table_name FROM (VALUES ('metrics'),('metrics2')) v(t);
table_name
------------
metrics
metrics2
(2 rows)
INSERT INTO metrics VALUES ('2000-01-01',random()), ('2000-02-01',random()), ('2000-03-01',random());
CREATE OR REPLACE FUNCTION insert_test() RETURNS VOID LANGUAGE plpgsql AS
$$
DECLARE
r RECORD;
BEGIN
FOR r IN
SELECT * FROM metrics
LOOP
WITH foo AS (
INSERT INTO metrics2 SELECT * FROM metrics RETURNING *
)
INSERT INTO status_table (a,b, last_ts)
VALUES (1,1, now())
ON CONFLICT (a,b) DO UPDATE SET last_ts=(SELECT max(time) FROM metrics);
END LOOP;
END;
$$;
SELECT insert_test(), insert_test(), insert_test();
insert_test | insert_test | insert_test
-------------+-------------+-------------
| |
(1 row)
-- test Postgres crashes on INSERT ... SELECT ... WHERE NOT EXISTS with empty table
-- https://github.com/timescale/timescaledb/issues/1883
CREATE TABLE readings (
toe TIMESTAMPTZ NOT NULL,
sensor_id INT NOT NULL,
value INT NOT NULL
);
SELECT create_hypertable(
'readings',
'toe',
chunk_time_interval => interval '1 day',
if_not_exists => TRUE,
migrate_data => TRUE
);
create_hypertable
------------------------
(11,public,readings,t)
(1 row)
EXPLAIN (costs off)
INSERT INTO readings
SELECT '2020-05-09 10:34:35.296288+00', 1, 0
WHERE NOT EXISTS (
SELECT 1
FROM readings
WHERE sensor_id = 1
AND toe = '2020-05-09 10:34:35.296288+00'
);
QUERY PLAN
--------------------------------------------------------------
Custom Scan (HypertableModify)
InitPlan 1
-> Result
One-Time Filter: false
-> Insert on readings
-> Custom Scan (ChunkDispatch)
-> Result
One-Time Filter: (NOT (InitPlan 1).col1)
(8 rows)
INSERT INTO readings
SELECT '2020-05-09 10:34:35.296288+00', 1, 0
WHERE NOT EXISTS (
SELECT 1
FROM readings
WHERE sensor_id = 1
AND toe = '2020-05-09 10:34:35.296288+00'
);
DROP TABLE readings;
CREATE TABLE sample_table (
sequence INTEGER NOT NULL,
time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
value NUMERIC NOT NULL,
UNIQUE (sequence, time)
);
SELECT * FROM create_hypertable('sample_table', 'time',
chunk_time_interval => INTERVAL '1 day');
WARNING: column type "timestamp without time zone" used for "time" does not follow best practices
hypertable_id | schema_name | table_name | created
---------------+-------------+--------------+---------
12 | public | sample_table | t
(1 row)
INSERT INTO sample_table (sequence,time,value) VALUES
(7, generate_series(TIMESTAMP '2019-08-01', TIMESTAMP '2019-08-10', INTERVAL '10 minutes'), ROUND(RANDOM()*10)::int);
\set ON_ERROR_STOP 0
INSERT INTO sample_table (sequence,time,value) VALUES
(7, generate_series(TIMESTAMP '2019-07-21', TIMESTAMP '2019-08-01', INTERVAL '10 minutes'), ROUND(RANDOM()*10)::int);
ERROR: duplicate key value violates unique constraint "27_1_sample_table_sequence_time_key"
\set ON_ERROR_STOP 1
INSERT INTO sample_table (sequence,time,value) VALUES
(7,generate_series(TIMESTAMP '2019-01-01', TIMESTAMP '2019-07-01', '10 minutes'), ROUND(RANDOM()*10)::int);
DROP TABLE sample_table;
-- test on conflict clause on columns with default value
-- issue #3037
CREATE TABLE i3037(time timestamptz PRIMARY KEY);
SELECT create_hypertable('i3037','time');
create_hypertable
---------------------
(13,public,i3037,t)
(1 row)
ALTER TABLE i3037 ADD COLUMN value float DEFAULT 0;
INSERT INTO i3037 VALUES ('2000-01-01');
INSERT INTO i3037 VALUES ('2000-01-01') ON CONFLICT(time) DO UPDATE SET value = EXCLUDED.value;
Reference in New Issue View Git Blame Copy Permalink