src/test/isolation2/input/external_table.source

CREATE EXTENSION IF NOT EXISTS gp_inject_fault;

-- start_ignore
DROP EXTERNAL TABLE IF EXISTS exttab_cursor_1;
DROP EXTERNAL TABLE IF EXISTS exttab_cursor_2;
-- end_ignore

-- Define a cursor on an external table scan query with segment reject limit reached
-- does not reach reject limit
CREATE EXTERNAL TABLE exttab_cursor_1( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS SEGMENT REJECT LIMIT 10;
-- reaches reject limit, use the same err table
CREATE EXTERNAL TABLE exttab_cursor_2( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_more_errors.data') FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS SEGMENT REJECT LIMIT 2;
-- Test to make sure error logs are populated when cursors are used
-- The total number of formatting errors reported by the query is dependant on the number of rows parsed on
-- the segments before COMMIT finishes. So mask the NOTICE by setting the client_min_messages guc to WARNING.
10: set CLIENT_MIN_MESSAGES=WARNING;
10: BEGIN;
10: DECLARE exttab_cur1 no scroll cursor FOR
    SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i
    UNION ALL
    SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i
    UNION ALL
    SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i;
10: COMMIT;
10: reset CLIENT_MIN_MESSAGES;

-- Fetch on external table scans with segment reject limit reached
10: SELECT gp_truncate_error_log('exttab_cursor_1');
10: SELECT gp_truncate_error_log('exttab_cursor_2');
-- DECLARE CURSOR implicitly initiates a FETCH on the segments. If one of
-- the segments reaches the reject limit and reports the error back to the QD
-- before the others, the transaction gets aborted. The timing of fetching the
-- results will  influence the order of the ERROR in the output.
--
-- For gp_interconnect_type=tcp:
-- If the segment errors out before QD call 'SetupInterconnect', QD will report
-- this error in 'SetupInterconnect', or else, QD will report the error in the
-- following FETCH command.
--
-- To make this test deterministic, we use gp_inject_fault to let QD suspend
-- before 'SetupInterconnect' to make sure QD calls 'SetupInterconnect' after
-- segment errors out.

-- Suspend QD when it is setting up interconnect.
SELECT gp_inject_fault('interconnect_setup_palloc', 'suspend', dbid)
  FROM gp_segment_configuration
 WHERE content=-1 AND role='p';

-- Inject fault on QE to determine when it aborts.
SELECT gp_inject_fault('transaction_abort_failure', 'skip', dbid)
  FROM gp_segment_configuration
 WHERE content=0 AND role='p';

-- Start a transaction with cursor that triggers error on QE
10: BEGIN;
10&: DECLARE exttab_cur1 no scroll cursor FOR
     SELECT e1.i, e2.j from exttab_cursor_2 e1 INNER JOIN exttab_cursor_2 e2 ON e1.i = e2.i
     UNION ALL
     SELECT e1.i, e2.j from exttab_cursor_2 e1 INNER JOIN exttab_cursor_2 e2 ON e1.i = e2.i
     UNION ALL
     SELECT e1.i, e2.j from exttab_cursor_2 e1 INNER JOIN exttab_cursor_2 e2 ON e1.i = e2.i;

-- Ensure that QE has hit an error
SELECT gp_wait_until_triggered_fault('transaction_abort_failure', 1, dbid)
  FROM gp_segment_configuration
 WHERE content=0 AND role='p';

SELECT gp_wait_until_triggered_fault('interconnect_setup_palloc', 1, dbid)
  FROM gp_segment_configuration
 WHERE content=-1 AND role='p';

-- Resume QD.
-- Error from QE should be reported after QD resumes
SELECT gp_inject_fault('interconnect_setup_palloc', 'resume', dbid)
  FROM gp_segment_configuration
 WHERE content=-1 AND role='p';

-- Check the result
10<:
10: COMMIT;

-- Reset injected faults
SELECT gp_inject_fault('transaction_abort_failure', 'reset', dbid)
  FROM gp_segment_configuration
 WHERE content=0 AND role='p';
SELECT gp_inject_fault('interconnect_setup_palloc', 'reset', dbid)
  FROM gp_segment_configuration
 WHERE content=-1 AND role='p';

-- This should have errors populated already
10: SELECT count(*) > 0 FROM gp_read_error_log('exttab_cursor_2');

-- Fetch on external table scans without reaching segment reject limit
10: SELECT gp_truncate_error_log('exttab_cursor_1');
10: SELECT gp_truncate_error_log('exttab_cursor_2');
10: BEGIN;
10: DECLARE exttab_cur1 no scroll cursor FOR
    (SELECT e1.i, e2.j from exttab_cursor_1 e1 INNER JOIN exttab_cursor_1 e2 ON e1.i = e2.i
    ORDER BY e1.i);
-- Should not fail
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: FETCH exttab_cur1;
10: COMMIT;


-- start_ignore
DROP EXTERNAL WEB TABLE IF EXISTS ext_delim_off;
-- end_ignore

-- Create external table with delimiter off
CREATE EXTERNAL WEB TABLE ext_delim_off ( junk text) execute 'echo hi' on coordinator FORMAT 'text' (delimiter 'OFF' null E'\\N' escape E'\\');

-- Query the ext_delim_off table
SELECT * FROM ext_delim_off;

-- start_ignore
DROP EXTERNAL TABLE IF EXISTS ext_delimiter_off_text;
-- end_ignore

-- Create external table(format text) with delimiter off, and a row with 'O'
CREATE EXTERNAL WEB TABLE ext_delimiter_off_text
(a text) EXECUTE E'echo O' ON COORDINATOR FORMAT 'text' (delimiter 'OFF') ENCODING 'UTF8';

SELECT * FROM ext_delimiter_off_text;

-- start_ignore
DROP EXTERNAL TABLE IF EXISTS ext_delimiter_off_csv;
-- end_ignore

-- Create external table(format csv) with delimiter off, and a row with 'O'
CREATE EXTERNAL WEB TABLE ext_delimiter_off_csv
(a text) EXECUTE E'echo O' ON COORDINATOR FORMAT 'csv' (delimiter 'OFF') ENCODING 'UTF8';

SELECT * FROM ext_delimiter_off_csv;

-- start_ignore
DROP EXTERNAL TABLE IF EXISTS exttab_delimiter_escape_off;
-- end_ignore

-- Create external table(text format) with both delimiter and escape off
CREATE EXTERNAL WEB TABLE exttab_delimiter_escape_off(a text)
EXECUTE E'cat @abs_srcdir@/data/exttab_escape_off.data' ON SEGMENT 0
format 'TEXT' (delimiter 'OFF' escape 'OFF') encoding 'UTF8'
LOG ERRORS  SEGMENT REJECT LIMIT 10;

SELECT * FROM exttab_delimiter_escape_off ORDER BY a;