andr-sokolov commented on code in PR #1842:
URL: https://github.com/apache/cloudberry/pull/1842#discussion_r3620431169
##########
src/test/regress/sql/lake_table.sql:
##########
@@ -0,0 +1,154 @@
+--
+-- Test lake table DDL: FOREIGN CATALOG, FOREIGN VOLUME, LAKE TABLE
+--
+
+-- Display the lake table catalogs
+\d+ pg_foreign_catalog
+\d+ pg_foreign_volume
+\d+ pg_lake_table
+
+-- Setup: foreign servers for the catalogs and volumes to hang off
+CREATE FOREIGN DATA WRAPPER lake_test_fdw;
+CREATE SERVER lake_test_srv FOREIGN DATA WRAPPER lake_test_fdw;
+CREATE SERVER lake_test_srv2 FOREIGN DATA WRAPPER lake_test_fdw;
+
+-- CREATE FOREIGN CATALOG: TYPE is a required first-class property
+CREATE FOREIGN CATALOG lake_test_cat SERVER lake_test_srv TYPE 'hive' OPTIONS
(uri 'thrift://localhost:9083');
+CREATE FOREIGN CATALOG lake_test_notype SERVER lake_test_srv; --
fail, TYPE is required
+CREATE FOREIGN CATALOG lake_test_cat SERVER lake_test_srv TYPE 'hive';
-- fail, duplicate
+CREATE FOREIGN CATALOG IF NOT EXISTS lake_test_cat SERVER lake_test_srv TYPE
'hive'; -- skip with notice
+-- catalog names are global: the same name on another server is still a
duplicate
+CREATE FOREIGN CATALOG lake_test_cat SERVER lake_test_srv2 TYPE 'hive';
-- fail, duplicate
+CREATE FOREIGN CATALOG IF NOT EXISTS lake_test_cat SERVER lake_test_srv2 TYPE
'hive'; -- skip with notice
+CREATE FOREIGN CATALOG lake_test_bad SERVER no_such_server TYPE 'hive';
-- fail, no server
+SELECT fcname, fctype, fcoptions FROM pg_foreign_catalog WHERE fcname LIKE
'lake\_test%';
+
+-- CREATE FOREIGN VOLUME
+CREATE FOREIGN VOLUME lake_test_vol SERVER lake_test_srv OPTIONS (base_path
's3://bucket/prefix');
+CREATE FOREIGN VOLUME lake_test_vol SERVER lake_test_srv;
-- fail, duplicate
+CREATE FOREIGN VOLUME IF NOT EXISTS lake_test_vol SERVER lake_test_srv;
-- skip with notice
+-- volume names are global: the same name on another server is still a
duplicate
+CREATE FOREIGN VOLUME lake_test_vol SERVER lake_test_srv2;
-- fail, duplicate
+CREATE FOREIGN VOLUME IF NOT EXISTS lake_test_vol SERVER lake_test_srv2;
-- skip with notice
+CREATE FOREIGN VOLUME lake_test_bad SERVER no_such_server;
-- fail, no server
+SELECT fvname, fvoptions FROM pg_foreign_volume WHERE fvname LIKE
'lake\_test%';
+
+-- Object descriptions
+SELECT pg_catalog.pg_describe_object('pg_foreign_catalog'::regclass, oid, 0)
+ FROM pg_foreign_catalog WHERE fcname = 'lake_test_cat';
+SELECT pg_catalog.pg_describe_object('pg_foreign_volume'::regclass, oid, 0)
+ FROM pg_foreign_volume WHERE fvname = 'lake_test_vol';
+
+-- Catalog and volume rows are dispatched to all segments
+SELECT count(DISTINCT gp_segment_id) > 1 AS on_all_segments
+ FROM gp_dist_random('pg_foreign_catalog') WHERE fcname = 'lake_test_cat';
+SELECT count(DISTINCT gp_segment_id) > 1 AS on_all_segments
+ FROM gp_dist_random('pg_foreign_volume') WHERE fvname = 'lake_test_vol';
+
+-- Without a provider extension there is no iceberg table AM
+CREATE LAKE TABLE lake_test_t0 (a int) USING ICEBERG CATALOG lake_test_cat
VOLUME lake_test_vol; -- fail with hint
+
+-- The default catalog/volume GUCs verify that the object exists
+SET iceberg_default_catalog = 'no_such_catalog'; -- fail
+SET iceberg_default_volume = 'no_such_volume'; -- fail
+
+-- Simulate a datalake provider with a heap-backed iceberg AM
+CREATE ACCESS METHOD iceberg TYPE TABLE HANDLER heap_tableam_handler;
+
+-- CREATE LAKE TABLE with explicit catalog and volume
+CREATE LAKE TABLE lake_test_t1 (a int, b text) USING ICEBERG CATALOG
lake_test_cat VOLUME lake_test_vol OPTIONS (fileformat 'parquet');
+SELECT c.relname, lt.lttable_type, lt.ltoptions, fc.fcname, fv.fvname
+ FROM pg_lake_table lt
+ JOIN pg_class c ON c.oid = lt.ltrelid
+ JOIN pg_foreign_catalog fc ON fc.oid = lt.ltforeign_catalog
+ JOIN pg_foreign_volume fv ON fv.oid = lt.ltforeign_volume;
+SELECT a.amname FROM pg_am a JOIN pg_class c ON c.relam = a.oid WHERE
c.relname = 'lake_test_t1';
+-- Lake tables are always DISTRIBUTED RANDOMLY (policytype 'p', no distkey)
+SELECT policytype, distkey FROM gp_distribution_policy WHERE localoid =
'lake_test_t1'::regclass;
+
+-- The (heap-backed) table is usable
+INSERT INTO lake_test_t1 VALUES (1, 'x'), (2, 'y');
+SELECT count(*) FROM lake_test_t1;
+
+-- Lake tables get a TOAST table like plain tables, so wide values work
+SELECT reltoastrelid <> 0 AS has_toast FROM pg_class WHERE relname =
'lake_test_t1';
+INSERT INTO lake_test_t1 VALUES (3, repeat('x', 500000));
+SELECT a, length(b) FROM lake_test_t1 WHERE a = 3;
+
+-- Catalog and volume are both required
+CREATE LAKE TABLE lake_test_t2 (a int) USING ICEBERG VOLUME lake_test_vol;
-- fail, no catalog
+CREATE LAKE TABLE lake_test_t2 (a int) USING ICEBERG CATALOG lake_test_cat;
-- fail, no volume
+
+-- ... unless the GUCs provide defaults
+SET iceberg_default_catalog = 'lake_test_cat';
+SET iceberg_default_volume = 'lake_test_vol';
+CREATE LAKE TABLE lake_test_t2 (a int) USING ICEBERG;
+RESET iceberg_default_catalog;
+RESET iceberg_default_volume;
+
+-- A DISTRIBUTED clause is ignored with a warning
+CREATE LAKE TABLE lake_test_t3 (a int) USING ICEBERG CATALOG lake_test_cat
VOLUME lake_test_vol DISTRIBUTED BY (a);
Review Comment:
I suggest to show an error message instead of warning, because `DISTRIBUTED
BY` is not supported for the `CREATE LAKE TABLE` query
```
postgres=# \h CREATE LAKE TABLE
Command: CREATE LAKE TABLE
Description: define a new lake table
Syntax:
CREATE LAKE TABLE [ IF NOT EXISTS ] table_name ( [
column_name data_type [, ... ]
] )
USING format
[ CATALOG catalog_name ]
[ VOLUME volume_name ]
[ OPTIONS ( option 'value' [, ...] ) ]
URL: https://www.postgresql.org/docs/16/sql-createlaketable.html
postgres=#
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
