Materialize/test/sqllogictest/retain_history.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_index_options = on;
----
COMPLETE 0

statement ok
CREATE TABLE tab_a (a INT)

statement ok
CREATE VIEW view_a AS SELECT a FROM tab_a

statement ok
CREATE INDEX idx_a ON view_a (a) WITH (RETAIN HISTORY FOR '1m')

statement ok
CREATE VIEW view_b AS SELECT a AS b FROM view_a

statement ok
CREATE INDEX idx_b ON view_b (b)

statement ok
DROP INDEX idx_b

statement ok
DROP INDEX idx_a

# Default retain history.
statement ok
CREATE INDEX idx_a ON view_a (a)

statement ok
CREATE INDEX idx_b ON view_b (b) WITH (RETAIN HISTORY FOR '1001ms')

statement ok
ALTER INDEX idx_a SET (RETAIN HISTORY FOR '5m')

statement ok
ALTER INDEX idx_b SET (RETAIN HISTORY FOR '3m')

statement ok
CREATE VIEW view_c AS SELECT * FROM view_b

statement ok
CREATE INDEX idx_c ON view_c (b)

statement ok
ALTER INDEX idx_c SET (RETAIN HISTORY FOR '1m')

query TTT
SELECT o.name, h.strategy, h.value FROM mz_internal.mz_history_retention_strategies h
JOIN mz_objects o ON o.id = h.id
WHERE o.id LIKE 'u%' AND o.name LIKE 'idx_%'
ORDER BY o.name
----
idx_a  FOR  300000
idx_b  FOR  180000
idx_c  FOR  60000

statement ok
ALTER INDEX idx_a SET (RETAIN HISTORY FOR '7m')

query TTT
SELECT o.name, h.strategy, h.value FROM mz_internal.mz_history_retention_strategies h
JOIN mz_objects o ON o.id = h.id
WHERE o.id LIKE 'u%' AND o.name LIKE 'idx_%'
ORDER BY o.name
----
idx_a  FOR  420000
idx_b  FOR  180000
idx_c  FOR  60000

statement ok
ALTER INDEX idx_b SET (RETAIN HISTORY FOR '6m')

statement ok
ALTER INDEX idx_c SET (RETAIN HISTORY FOR '4m')

query TTT
SELECT o.name, h.strategy, h.value FROM mz_internal.mz_history_retention_strategies h
JOIN mz_objects o ON o.id = h.id
WHERE o.id LIKE 'u%' AND o.name LIKE 'idx_%'
ORDER BY o.name
----
idx_a  FOR  420000
idx_b  FOR  360000
idx_c  FOR  240000

# Test subsource propagation. Test sources with and without subsources and view dependencies to
# ensure the alter code correctly ignores the views.
statement ok
CREATE SOURCE auction_house FROM LOAD GENERATOR AUCTION
----

statement ok
CREATE TABLE accounts FROM SOURCE auction_house (REFERENCE accounts);

statement ok
CREATE TABLE auctions FROM SOURCE auction_house (REFERENCE auctions);

statement ok
CREATE TABLE bids FROM SOURCE auction_house (REFERENCE bids);

statement ok
CREATE TABLE organizations FROM SOURCE auction_house (REFERENCE organizations);

statement ok
CREATE TABLE users FROM SOURCE auction_house (REFERENCE users);

statement ok
CREATE VIEW auction_house_view AS SELECT * FROM users
----

statement ok
CREATE SOURCE counter FROM LOAD GENERATOR COUNTER
----

statement ok
CREATE VIEW counter_view AS SELECT * FROM counter
----

query TTT
SELECT o.name, h.strategy, h.value FROM mz_internal.mz_history_retention_strategies h
JOIN mz_objects o ON o.id = h.id
WHERE o.id LIKE 'u%'
ORDER BY o.name
----
accounts  FOR  1000
auction_house  FOR  1000
auction_house_progress  FOR  1000
auctions  FOR  1000
bids  FOR  1000
counter  FOR  1000
counter_progress  FOR  1000
idx_a  FOR  420000
idx_b  FOR  360000
idx_c  FOR  240000
organizations  FOR  1000
tab_a  FOR  1000
users  FOR  1000

statement ok
ALTER SOURCE auction_house SET (RETAIN HISTORY FOR '1m')
----

statement ok
ALTER SOURCE counter SET (RETAIN HISTORY FOR '1m')
----

query TTT
SELECT o.name, h.strategy, h.value FROM mz_internal.mz_history_retention_strategies h
JOIN mz_objects o ON o.id = h.id
WHERE o.id LIKE 'u%'
ORDER BY o.name
----
accounts  FOR  60000
auction_house  FOR  60000
auction_house_progress  FOR  1000
auctions  FOR  60000
bids  FOR  60000
counter  FOR  60000
counter_progress  FOR  1000
idx_a  FOR  420000
idx_b  FOR  360000
idx_c  FOR  240000
organizations  FOR  60000
tab_a  FOR  1000
users  FOR  60000

statement ok
ALTER SOURCE counter RESET (RETAIN HISTORY)

statement ok
ALTER SOURCE auction_house RESET (RETAIN HISTORY)

statement ok
ALTER INDEX idx_c RESET (RETAIN HISTORY)

statement ok
ALTER INDEX idx_b RESET (RETAIN HISTORY)

query TTT
SELECT o.name, h.strategy, h.value FROM mz_internal.mz_history_retention_strategies h
JOIN mz_objects o ON o.id = h.id
WHERE o.id LIKE 'u%'
ORDER BY o.name
----
accounts  FOR  1000
auction_house  FOR  1000
auction_house_progress  FOR  1000
auctions  FOR  1000
bids  FOR  1000
counter  FOR  1000
counter_progress  FOR  1000
idx_a  FOR  420000
idx_b  FOR  1000
idx_c  FOR  1000
organizations  FOR  1000
tab_a  FOR  1000
users  FOR  1000

# Check retain history lower bounds.
statement error db error: ERROR: RETAIN HISTORY cannot be set lower than 1000ms
ALTER SOURCE counter SET (RETAIN HISTORY FOR '1ms')

statement error db error: ERROR: RETAIN HISTORY cannot be disabled or set to 0
ALTER SOURCE counter SET (RETAIN HISTORY FOR '0')

statement error db error: ERROR: RETAIN HISTORY cannot be set lower than 1000ms
CREATE SOURCE low_rh FROM LOAD GENERATOR COUNTER WITH (RETAIN HISTORY FOR '1ms')

statement error db error: ERROR: RETAIN HISTORY cannot be disabled or set to 0
CREATE SOURCE low_rh FROM LOAD GENERATOR COUNTER WITH (RETAIN HISTORY FOR '0')