-- Setup the test CREATE language plpython3u; CREATE OR REPLACE FUNCTION create_table() RETURNS VOID AS $$ import plpy import tempfile import os dir = tempfile.mkdtemp() plpy.execute(""" INSERT INTO temp_dir(dirname) VALUES ('%s') """ % str(dir)) plpy.execute(""" CREATE foreign table testmulticorn ( color varchar, size varchar, name varchar, ext varchar, filename varchar, data varchar ) server multicorn_srv options ( filename_column 'filename', content_column 'data', pattern '{color}/{size}/{name}.{ext}', root_dir '%s' ); """ % dir) for color in ('blue', 'red'): for size in ('big', 'small'): dirname = os.path.join(dir, color, size) os.makedirs(dirname) for name, ext in (('square', 'txt'), ('round', 'ini')): with open(os.path.join(dirname, '.'.join([name, ext])), 'a') as fd: fd.write('Im a %s %s %s\n' % (size, color, name)) $$ language plpython3u; CREATE EXTENSION multicorn; CREATE server multicorn_srv foreign data wrapper multicorn options ( wrapper 'multicorn.fsfdw.FilesystemFdw' ); CREATE TABLE temp_dir (dirname varchar); -- Create a table with the filesystem fdw in a temporary directory, -- and store the dirname in the temp_dir table. select create_table(); create_table -------------- (1 row) -- End of Setup \i test-common/multicorn_testfilesystem.include -- Should have 8 lines. SELECT * from testmulticorn ORDER BY filename; color | size | name | ext | filename | data -------+-------+--------+-----+-----------------------+------------------------ blue | big | round | ini | blue/big/round.ini | Im a big blue round + | | | | | blue | big | square | txt | blue/big/square.txt | Im a big blue square + | | | | | blue | small | round | ini | blue/small/round.ini | Im a small blue round + | | | | | blue | small | square | txt | blue/small/square.txt | Im a small blue square+ | | | | | red | big | round | ini | red/big/round.ini | Im a big red round + | | | | | red | big | square | txt | red/big/square.txt | Im a big red square + | | | | | red | small | round | ini | red/small/round.ini | Im a small red round + | | | | | red | small | square | txt | red/small/square.txt | Im a small red square + | | | | | (8 rows) -- Test the cost analysis EXPLAIN select color, size from testmulticorn where color = 'blue' and size = 'big' and name = 'square' and ext = 'txt'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on testmulticorn (cost=20.00..120.00 rows=1 width=120) Filter: (((color)::text = 'blue'::text) AND ((size)::text = 'big'::text) AND ((name)::text = 'square'::text) AND ((ext)::text = 'txt'::text)) (2 rows) EXPLAIN select color, size from testmulticorn where color = 'blue' and size = 'big'; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on testmulticorn (cost=20.00..6000.00 rows=100 width=60) Filter: (((color)::text = 'blue'::text) AND ((size)::text = 'big'::text)) (2 rows) EXPLAIN select color, size from testmulticorn where color = 'blue'; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on testmulticorn (cost=20.00..600000.00 rows=10000 width=60) Filter: ((color)::text = 'blue'::text) (2 rows) EXPLAIN select color, size, data from testmulticorn where color = 'blue' and size = 'big' and name = 'square' and ext = 'txt'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on testmulticorn (cost=20.00..1000150.00 rows=1 width=1000150) Filter: (((color)::text = 'blue'::text) AND ((size)::text = 'big'::text) AND ((name)::text = 'square'::text) AND ((ext)::text = 'txt'::text)) (2 rows) -- Test insertion -- Normal insertion INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('yellow', 'big', 'square', 'text', 'Im a big yellow square') RETURNING filename; filename ------------------------ yellow/big/square.text (1 row) -- Insertion with redundant filename/properties INSERT INTO testmulticorn (color, size, name, ext, data, filename) VALUES ('yellow', 'small', 'square', 'txt', 'Im a small yellow square', 'yellow/small/square.txt'); -- Insertion with just a filename INSERT INTO testmulticorn (data, filename) VALUES ('Im a big blue triangle', 'blue/big/triangle.txt') RETURNING color, size, name, ext; color | size | name | ext -------+------+----------+----- blue | big | triangle | txt (1 row) -- Should have 11 lines by now. SELECT * from testmulticorn ORDER BY filename; color | size | name | ext | filename | data --------+-------+----------+------+-------------------------+-------------------------- blue | big | round | ini | blue/big/round.ini | Im a big blue round + | | | | | blue | big | square | txt | blue/big/square.txt | Im a big blue square + | | | | | blue | big | triangle | txt | blue/big/triangle.txt | Im a big blue triangle blue | small | round | ini | blue/small/round.ini | Im a small blue round + | | | | | blue | small | square | txt | blue/small/square.txt | Im a small blue square + | | | | | red | big | round | ini | red/big/round.ini | Im a big red round + | | | | | red | big | square | txt | red/big/square.txt | Im a big red square + | | | | | red | small | round | ini | red/small/round.ini | Im a small red round + | | | | | red | small | square | txt | red/small/square.txt | Im a small red square + | | | | | yellow | big | square | text | yellow/big/square.text | Im a big yellow square yellow | small | square | txt | yellow/small/square.txt | Im a small yellow square (11 rows) -- Insertion with incoherent filename/properties (should fail) INSERT INTO testmulticorn (color, size, name, ext, data, filename) VALUES ('blue', 'big', 'triangle', 'txt', 'Im a big blue triangle', 'blue/small/triangle.txt'); psql:test-common/multicorn_testfilesystem.include:28: ERROR: The columns inferred from the filename do not match the supplied columns. HINT: Remove either the filename column or the properties column from your statement, or ensure they match -- Insertion with missing keys (should fail) INSERT INTO testmulticorn (color, size, name) VALUES ('blue', 'small', 'triangle'); psql:test-common/multicorn_testfilesystem.include:31: ERROR: The following columns are necessary: {'ext'} HINT: You can also insert an item by providing only the filename and content columns -- Insertion with missing keys and filename (should fail) INSERT INTO testmulticorn (color, size, name, filename) VALUES ('blue', 'small', 'triangle', 'blue/small/triangle.txt'); psql:test-common/multicorn_testfilesystem.include:34: ERROR: The following columns are necessary: {'ext'} HINT: You can also insert an item by providing only the filename and content columns -- Insertion which would overwrite a file. -- Normal insertion INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('yellow', 'big', 'square', 'text', 'Im a duplicate big square'); psql:test-common/multicorn_testfilesystem.include:38: ERROR: Duplicate key value violates filesystem integrity. DETAIL: Key (color, ext, name, size)=(yellow, text, square, big) already exists -- Should still have 11 lines by now. SELECT * from testmulticorn ORDER BY filename; color | size | name | ext | filename | data --------+-------+----------+------+-------------------------+-------------------------- blue | big | round | ini | blue/big/round.ini | Im a big blue round + | | | | | blue | big | square | txt | blue/big/square.txt | Im a big blue square + | | | | | blue | big | triangle | txt | blue/big/triangle.txt | Im a big blue triangle blue | small | round | ini | blue/small/round.ini | Im a small blue round + | | | | | blue | small | square | txt | blue/small/square.txt | Im a small blue square + | | | | | red | big | round | ini | red/big/round.ini | Im a big red round + | | | | | red | big | square | txt | red/big/square.txt | Im a big red square + | | | | | red | small | round | ini | red/small/round.ini | Im a small red round + | | | | | red | small | square | txt | red/small/square.txt | Im a small red square + | | | | | yellow | big | square | text | yellow/big/square.text | Im a big yellow square yellow | small | square | txt | yellow/small/square.txt | Im a small yellow square (11 rows) -- Test insertion in transaction BEGIN; INSERT INTO testmulticorn (data, filename) VALUES ('Im a big red triangle', 'red/big/triangle.txt'); SELECT * from testmulticorn where name = 'triangle' and color = 'red' ORDER BY filename; color | size | name | ext | filename | data -------+------+----------+-----+----------------------+----------------------- red | big | triangle | txt | red/big/triangle.txt | Im a big red triangle (1 row) ROLLBACK; -- The file should not be persisted. SELECT * from testmulticorn where name = 'triangle' and color = 'red' ORDER BY filename; color | size | name | ext | filename | data -------+------+------+-----+----------+------ (0 rows) -- Test Update WITH t as ( UPDATE testmulticorn set name = 'rectangle' where name = 'square' RETURNING filename ) SELECT * from t order by filename; filename ---------------------------- blue/big/rectangle.txt blue/small/rectangle.txt red/big/rectangle.txt red/small/rectangle.txt yellow/big/rectangle.text yellow/small/rectangle.txt (6 rows) -- O lines SELECT count(1) from testmulticorn where name = 'square'; count ------- 0 (1 row) -- 6 lines SELECT count(1) from testmulticorn where name = 'rectangle'; count ------- 6 (1 row) -- Update should not work if it would override an existing file. UPDATE testmulticorn set filename = 'blue/big/triangle.txt' where filename = 'blue/big/rectangle.txt'; psql:test-common/multicorn_testfilesystem.include:65: ERROR: Duplicate key value violates filesystem integrity. DETAIL: Key (color, ext, name, size)=(blue, txt, triangle, big) already exists -- Update should not work when setting filename column to NULL UPDATE testmulticorn set filename = NULL where filename = 'blue/big/rectangle.txt'; psql:test-common/multicorn_testfilesystem.include:68: ERROR: The filename, or all pattern columns are needed. -- Update should not work when setting a property column to NULL WITH t as ( UPDATE testmulticorn set color = NULL where filename = 'blue/big/rectangle.txt' RETURNING color ) SELECT * from t ORDER BY color; psql:test-common/multicorn_testfilesystem.include:73: ERROR: Null value in columns (color) are not allowed DETAIL: Failing row contains (NULL, big, rectangle, txt) -- Content column update. UPDATE testmulticorn set data = 'Im an updated rectangle' where filename = 'blue/big/rectangle.txt' RETURNING data; data ------------------------- Im an updated rectangle (1 row) SELECT * from testmulticorn where filename = 'blue/big/rectangle.txt'; color | size | name | ext | filename | data -------+------+-----------+-----+------------------------+------------------------- blue | big | rectangle | txt | blue/big/rectangle.txt | Im an updated rectangle (1 row) -- Update in transactions BEGIN; UPDATE testmulticorn set name = 'square' where name = 'rectangle'; -- O lines SELECT count(1) from testmulticorn where name = 'rectangle'; count ------- 0 (1 row) -- 6 lines SELECT count(1) from testmulticorn where name = 'square'; count ------- 6 (1 row) ROLLBACK; -- O lines SELECT count(1) from testmulticorn where name = 'square'; count ------- 0 (1 row) -- 6 lines SELECT count(1) from testmulticorn where name = 'rectangle'; count ------- 6 (1 row) BEGIN; UPDATE testmulticorn set data = data || ' UPDATED!'; -- 11 lines SELECT count(1) from testmulticorn where data ilike '% UPDATED!'; count ------- 11 (1 row) SELECT data from testmulticorn where data ilike '% UPDATED!' order by filename limit 1; data ---------------------------------- Im an updated rectangle UPDATED! (1 row) ROLLBACK; -- 0 lines SELECT count(1) from testmulticorn where data ilike '% UPDATED!'; count ------- 0 (1 row) BEGIN; UPDATE testmulticorn set data = data || ' UPDATED!'; UPDATE testmulticorn set data = data || ' TWICE!'; SELECT data from testmulticorn order by filename; data ------------------------------------------ Im an updated rectangle UPDATED! TWICE! Im a big blue round + UPDATED! TWICE! Im a big blue triangle UPDATED! TWICE! Im a small blue square + UPDATED! TWICE! Im a small blue round + UPDATED! TWICE! Im a big red square + UPDATED! TWICE! Im a big red round + UPDATED! TWICE! Im a small red square + UPDATED! TWICE! Im a small red round + UPDATED! TWICE! Im a big yellow square UPDATED! TWICE! Im a small yellow square UPDATED! TWICE! (11 rows) ROLLBACK; -- No 'UPDATED! or 'TWICE!' SELECT data from testmulticorn order by filename; data -------------------------- Im an updated rectangle Im a big blue round + Im a big blue triangle Im a small blue square + Im a small blue round + Im a big red square + Im a big red round + Im a small red square + Im a small red round + Im a big yellow square Im a small yellow square (11 rows) -- Test successive update to the same files. BEGIN; UPDATE testmulticorn set color = 'cyan' where filename = 'blue/big/rectangle.txt'; -- There should be one line with cyan color, 0 with the old filename SELECT filename, data from testmulticorn where color = 'cyan' order by filename; filename | data ------------------------+------------------------- cyan/big/rectangle.txt | Im an updated rectangle (1 row) SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename; filename | data ----------+------ (0 rows) -- There should be one line with magenta, and 0 with cyan and the old -- filename UPDATE testmulticorn set color = 'magenta' where color = 'cyan'; SELECT filename, data from testmulticorn where color = 'magenta' order by filename; filename | data ---------------------------+------------------------- magenta/big/rectangle.txt | Im an updated rectangle (1 row) SELECT filename, data from testmulticorn where color = 'cyan' order by filename; filename | data ----------+------ (0 rows) SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename; filename | data ----------+------ (0 rows) UPDATE testmulticorn set color = 'blue' where color = 'magenta'; -- There should be one line with the old filename, and zero with the rest SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename; filename | data ------------------------+------------------------- blue/big/rectangle.txt | Im an updated rectangle (1 row) SELECT filename, data from testmulticorn where color = 'magenta' order by filename; filename | data ----------+------ (0 rows) SELECT filename, data from testmulticorn where color = 'cyan' order by filename; filename | data ----------+------ (0 rows) COMMIT; -- Result should be the same than pre-commit SELECT filename, data from testmulticorn where filename = 'blue/big/rectangle.txt' order by filename; filename | data ------------------------+------------------------- blue/big/rectangle.txt | Im an updated rectangle (1 row) SELECT filename, data from testmulticorn where color = 'magenta' order by filename; filename | data ----------+------ (0 rows) SELECT filename, data from testmulticorn where color = 'cyan' order by filename; filename | data ----------+------ (0 rows) -- DELETE test WITH t as ( DELETE from testmulticorn where color = 'yellow' returning filename ) SELECT * from t order by filename; filename ---------------------------- yellow/big/rectangle.text yellow/small/rectangle.txt (2 rows) -- Should have no rows select count(1) from testmulticorn where color = 'yellow'; count ------- 0 (1 row) -- DELETE in transaction BEGIN; WITH t as ( DELETE from testmulticorn where color = 'red' returning filename ) SELECT * from t order by filename; filename ------------------------- red/big/rectangle.txt red/big/round.ini red/small/rectangle.txt red/small/round.ini (4 rows) select count(1) from testmulticorn where color = 'red'; count ------- 0 (1 row) ROLLBACK; -- Should have 4 rows select count(1) from testmulticorn where color = 'red'; count ------- 4 (1 row) -- Test various combinations of INSERT/UPDATE/DELETE BEGIN; INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('cyan', 'medium', 'triangle', 'jpg', 'Im a triangle'); INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('cyan', 'large', 'triangle', 'jpg', 'Im a triangle'); -- 2 lines SELECT * from testmulticorn where color = 'cyan' order by filename; color | size | name | ext | filename | data -------+--------+----------+-----+--------------------------+--------------- cyan | large | triangle | jpg | cyan/large/triangle.jpg | Im a triangle cyan | medium | triangle | jpg | cyan/medium/triangle.jpg | Im a triangle (2 rows) UPDATE testmulticorn set color = 'magenta' where size = 'large' and color = 'cyan' returning filename; filename ---------------------------- magenta/large/triangle.jpg (1 row) -- 2 lines, one cyan, one magenta SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename; color | size | name | ext | filename | data ---------+--------+----------+-----+----------------------------+--------------- cyan | medium | triangle | jpg | cyan/medium/triangle.jpg | Im a triangle magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im a triangle (2 rows) UPDATE testmulticorn set data = 'Im magenta' where color = 'magenta'; WITH t as ( DELETE from testmulticorn where color = 'cyan' returning filename ) SELECT * from t order by filename; filename -------------------------- cyan/medium/triangle.jpg (1 row) -- One magenta line SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename; color | size | name | ext | filename | data ---------+-------+----------+-----+----------------------------+------------ magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im magenta (1 row) COMMIT; -- Result should be the same as precommit SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename; color | size | name | ext | filename | data ---------+-------+----------+-----+----------------------------+------------ magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im magenta (1 row) DELETE from testmulticorn where color = 'magenta'; -- Same as before, but rollbacking BEGIN; INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('cyan', 'medium', 'triangle', 'jpg', 'Im a triangle'); INSERT INTO testmulticorn (color, size, name, ext, data) VALUES ('cyan', 'large', 'triangle', 'jpg', 'Im a triangle'); -- 2 lines SELECT * from testmulticorn where color = 'cyan' order by filename; color | size | name | ext | filename | data -------+--------+----------+-----+--------------------------+--------------- cyan | large | triangle | jpg | cyan/large/triangle.jpg | Im a triangle cyan | medium | triangle | jpg | cyan/medium/triangle.jpg | Im a triangle (2 rows) UPDATE testmulticorn set color = 'magenta' where size = 'large' and color = 'cyan' returning filename; filename ---------------------------- magenta/large/triangle.jpg (1 row) -- 2 lines, one cyan, one magenta SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename; color | size | name | ext | filename | data ---------+--------+----------+-----+----------------------------+--------------- cyan | medium | triangle | jpg | cyan/medium/triangle.jpg | Im a triangle magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im a triangle (2 rows) UPDATE testmulticorn set data = 'Im magenta' where color = 'magenta'; DELETE FROM testmulticorn where color = 'cyan' RETURNING filename; filename -------------------------- cyan/medium/triangle.jpg (1 row) -- One magenta line SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename; color | size | name | ext | filename | data ---------+-------+----------+-----+----------------------------+------------ magenta | large | triangle | jpg | magenta/large/triangle.jpg | Im magenta (1 row) ROLLBACK; SELECT * from testmulticorn where color in ('cyan', 'magenta') order by filename; color | size | name | ext | filename | data -------+------+------+-----+----------+------ (0 rows) -- Cleanup everything we've done CREATE OR REPLACE FUNCTION cleanup_dir() RETURNS VOID AS $$ import shutil root_dir = plpy.execute("""SELECT dirname from temp_dir;""")[0]['dirname'] shutil.rmtree(root_dir) $$ language plpython3u; select cleanup_dir(); cleanup_dir ------------- (1 row) DROP FUNCTION cleanup_dir(); DROP TABLE temp_dir; DROP FUNCTION create_table(); DROP EXTENSION multicorn cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to server multicorn_srv drop cascades to foreign table testmulticorn DROP LANGUAGE plpython3u;