/* * Install the extension and define the tables. * All the foreign tables defined refer to the same Oracle table. */ SET client_min_messages = WARNING; CREATE EXTENSION oracle_fdw; -- TWO_TASK or ORACLE_HOME and ORACLE_SID must be set in the server's environment for this to work CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '', isolation_level 'read_committed'); CREATE USER MAPPING FOR PUBLIC SERVER oracle OPTIONS (user 'SCOTT', password 'tiger'); -- drop the Oracle tables if they exist DO $$BEGIN SELECT oracle_execute('oracle', 'DROP TABLE scott.typetest1 PURGE'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; DO $$BEGIN SELECT oracle_execute('oracle', 'DROP TABLE scott.gis PURGE'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; SELECT oracle_execute( 'oracle', E'CREATE TABLE scott.typetest1 (\n' ' id NUMBER(5)\n' ' CONSTRAINT typetest1_pkey PRIMARY KEY,\n' ' c CHAR(10 CHAR),\n' ' nc NCHAR(10),\n' ' vc VARCHAR2(10 CHAR),\n' ' nvc NVARCHAR2(10),\n' ' lc CLOB,\n' ' r RAW(10),\n' ' u RAW(16),\n' ' lb BLOB,\n' ' lr LONG RAW,\n' ' b NUMBER(1),\n' ' num NUMBER(7,5),\n' ' fl BINARY_FLOAT,\n' ' db BINARY_DOUBLE,\n' ' d DATE,\n' ' ts TIMESTAMP WITH TIME ZONE,\n' ' ids INTERVAL DAY TO SECOND,\n' ' iym INTERVAL YEAR TO MONTH\n' ') SEGMENT CREATION IMMEDIATE' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'CREATE TABLE scott.gis (\n' ' id NUMBER(5) PRIMARY KEY,\n' ' g MDSYS.SDO_GEOMETRY\n' ') SEGMENT CREATION IMMEDIATE' ); oracle_execute ---------------- (1 row) -- gather statistics SELECT oracle_execute( 'oracle', E'BEGIN\n' ' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''TYPETEST1'', NULL, 100);\n' 'END;' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'BEGIN\n' ' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''GIS'', NULL, 100);\n' 'END;' ); oracle_execute ---------------- (1 row) -- create the foreign tables CREATE FOREIGN TABLE typetest1 ( id integer OPTIONS (key 'yes') NOT NULL, q double precision, c character(10), nc character(10), vc character varying(10), nvc character varying(10), lc text, r bytea, u uuid, lb bytea, lr bytea, b boolean, num numeric(7,5), fl float, db double precision, d date, ts timestamp with time zone, ids interval, iym interval ) SERVER oracle OPTIONS (table 'TYPETEST1'); ALTER FOREIGN TABLE typetest1 DROP q; -- a table that is missing some fields CREATE FOREIGN TABLE shorty ( id integer OPTIONS (key 'yes') NOT NULL, c character(10) ) SERVER oracle OPTIONS (table 'TYPETEST1'); -- a table that has some extra fields CREATE FOREIGN TABLE longy ( id integer OPTIONS (key 'yes') NOT NULL, c character(10), nc character(10), vc character varying(10), nvc character varying(10), lc text, r bytea, u uuid, lb bytea, lr bytea, b boolean, num numeric(7,5), fl float, db double precision, d date, ts timestamp with time zone, ids interval, iym interval, x integer ) SERVER oracle OPTIONS (table 'TYPETEST1'); /* * Empty the table and INSERT some samples. */ -- will fail with a read-only transaction ALTER SERVER oracle OPTIONS (SET isolation_level 'read_only'); SELECT oracle_close_connections(); oracle_close_connections -------------------------- (1 row) DELETE FROM typetest1; ERROR: error executing query: OCIStmtExecute failed to execute remote query DETAIL: ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction -- use the default SERIALIZABLE isolation level from now on ALTER SERVER oracle OPTIONS (DROP isolation_level); SELECT oracle_close_connections(); oracle_close_connections -------------------------- (1 row) DELETE FROM typetest1; INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 1, 'fixed char', 'nat''l char', 'varlena', 'nat''l var', 'character large object', bytea('\xDEADBEEF'), uuid('055e26fa-f1d8-771f-e053-1645990add93'), bytea('\xDEADBEEF'), bytea('\xDEADBEEF'), TRUE, 3.14159, 3.14159, 3.14159, '1968-10-20', '2009-01-26 15:02:54.893532 PST', '1 day 2 hours 30 seconds 1 microsecond', '-6 months' ); INSERT INTO shorty (id, c) VALUES (2, NULL); INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 3, E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb ABC' || repeat('X', 9000), bytea('\xDEADF00D'), uuid('055f3b32-a02c-4532-e053-1645990a6db2'), bytea('\xDEADF00DDEADF00DDEADF00D'), bytea('\xDEADF00DDEADF00DDEADF00D'), FALSE, -2.71828, -2.71828, -2.71828, '0044-03-15 BC', '0044-03-15 12:00:00 BC', '-2 days -12 hours -30 minutes', '-2 years -6 months' ); INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 4, 'short', 'short', 'short', 'short', 'short', bytea('\xDEADF00D'), uuid('0560ee34-2ef9-1137-e053-1645990ac874'), bytea('\xDEADF00D'), bytea('\xDEADF00D'), NULL, 0, 0, 0, NULL, NULL, '23:59:59.999999', '3 years' ); /* * Test SELECT, UPDATE ... RETURNING, DELETE and transactions. */ -- simple SELECT SELECT id, c, nc, vc, nvc, length(lc), r, u, length(lb), length(lr), b, num, fl, db, d, ts, ids, iym, x FROM longy ORDER BY id; WARNING: column number 19 of foreign table "longy" does not exist in foreign Oracle table, will be replaced by NULL id | c | nc | vc | nvc | length | r | u | length | length | b | num | fl | db | d | ts | ids | iym | x ----+----------------------+----------------------+------------------+------------------+--------+------------+--------------------------------------+--------+--------+---+----------+-------------+----------+---------------+-------------------------------------+-----------------------------------+----------------------+--- 1 | fixed char | nat'l char | varlena | nat'l var | 22 | \xdeadbeef | 055e26fa-f1d8-771f-e053-1645990add93 | 4 | 4 | t | 3.14159 | 3.14159012 | 3.14159 | 10-20-1968 | Mon Jan 26 15:02:54.893532 2009 PST | @ 1 day 2 hours 30.000001 secs | @ 6 mons ago | 2 | | | | | | | | | | | | | | | | | | 3 | a\x1B\x07\r\x7Fb | a\x1B\x07\r\x7Fb | a\x1B\x07\r\x7Fb | a\x1B\x07\r\x7Fb | 9010 | \xdeadf00d | 055f3b32-a02c-4532-e053-1645990a6db2 | 12 | 12 | f | -2.71828 | -2.71828008 | -2.71828 | 03-15-0044 BC | Fri Mar 15 12:00:00 0044 PST BC | @ 2 days 12 hours 30 mins ago | @ 2 years 6 mons ago | 4 | short | short | short | short | 5 | \xdeadf00d | 0560ee34-2ef9-1137-e053-1645990ac874 | 4 | 4 | | 0.00000 | 0 | 0 | | | @ 23 hours 59 mins 59.999999 secs | @ 3 years | (4 rows) -- mass UPDATE WITH upd (id, c, lb, d, ts) AS (UPDATE longy SET c = substr(c, 1, 9) || 'u', lb = lb || bytea('\x00'), lr = lr || bytea('\x00'), d = d + 1, ts = ts + '1 day' WHERE id < 3 RETURNING id + 1, c, lb, d, ts) SELECT * FROM upd ORDER BY id; WARNING: column number 19 of foreign table "longy" does not exist in foreign Oracle table, will be replaced by NULL id | c | lb | d | ts ----+------------+--------------+------------+------------------------------------- 2 | fixed chau | \xdeadbeef00 | 10-21-1968 | Tue Jan 27 15:02:54.893532 2009 PST 3 | | | | (2 rows) -- transactions BEGIN; DELETE FROM shorty WHERE id = 2; SAVEPOINT one; -- will cause an error INSERT INTO shorty (id, c) VALUES (1, 'c'); ERROR: error executing query: OCIStmtExecute failed to execute remote query DETAIL: ORA-00001: unique constraint (SCOTT.TYPETEST1_PKEY) violated ROLLBACK TO one; INSERT INTO shorty (id, c) VALUES (2, 'c'); ROLLBACK TO one; COMMIT; -- see if the correct data are in the table SELECT id, c FROM typetest1 ORDER BY id; id | c ----+---------------------- 1 | fixed chau 3 | a\x1B\x07\r\x7Fb 4 | short (3 rows) -- try to update the nonexistant column (should cause an error) UPDATE longy SET x = NULL WHERE id = 1; ERROR: no Oracle column modified by UPDATE DETAIL: The UPDATE statement only changes colums that do not exist in the Oracle table. -- check that UPDATES work with "date" in Oracle and "timestamp" in PostgreSQL BEGIN; ALTER FOREIGN TABLE typetest1 ALTER COLUMN d TYPE timestamp(0) without time zone; UPDATE typetest1 SET d = '1968-10-10 12:00:00' WHERE id = 1 RETURNING d; d -------------------------- Thu Oct 10 12:00:00 1968 (1 row) ROLLBACK; -- test if "IN" or "= ANY" expressions are pushed down correctly SELECT id FROM typetest1 WHERE vc = ANY (ARRAY['short', (SELECT 'varlena'::varchar)]) ORDER BY id; id ---- 1 4 (2 rows) EXPLAIN (COSTS off) SELECT id FROM typetest1 WHERE vc = ANY (ARRAY['short', (SELECT 'varlena'::varchar)]) ORDER BY id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*8c6beaafc8da5092be8f9e5228293b79*/ r1."ID", r1."VC" FROM "TYPETEST1" r1 WHERE (r1."VC" IN ('short', :p1)) ORDER BY r1."ID" ASC NULLS LAST InitPlan 1 (returns $0) -> Result (4 rows) -- test modifications that need no foreign scan scan (bug #295) DELETE FROM typetest1 WHERE FALSE; UPDATE shorty SET c = NULL WHERE FALSE RETURNING *; id | c ----+--- (0 rows) -- test deparsing of ScalarArrayOpExpr where the RHS has different element type than the LHS SELECT id FROM typetest1 WHERE vc = ANY ('{zzzzz}'::name[]); id ---- (0 rows) /* * Test "strip_zeros" column option. */ SELECT oracle_execute( 'oracle', 'INSERT INTO typetest1 (id, vc) VALUES (5, ''has'' || chr(0) || ''zeros'')' ); oracle_execute ---------------- (1 row) SELECT vc FROM typetest1 WHERE id = 5; -- should fail ERROR: invalid byte sequence for encoding "UTF8": 0x00 CONTEXT: converting column "vc" for foreign table scan of "typetest1", row 1 ALTER FOREIGN TABLE typetest1 ALTER vc OPTIONS (ADD strip_zeros 'yes'); SELECT vc FROM typetest1 WHERE id = 5; -- should work vc ---------- haszeros (1 row) ALTER FOREIGN TABLE typetest1 ALTER vc OPTIONS (DROP strip_zeros); DELETE FROM typetest1 WHERE id = 5; /* * Test EXPLAIN support. */ EXPLAIN (COSTS off) UPDATE typetest1 SET lc = current_timestamp WHERE id < 4 RETURNING id + 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on typetest1 Oracle statement: UPDATE "TYPETEST1" SET "LC" = :p7 WHERE "ID" = :k1 RETURNING "ID" INTO :r1 -> Foreign Scan on typetest1 Oracle query: SELECT /*3f20c4dd8d9918f2fe414510067e0a60*/ r1."ID", r1."C", r1."NC", r1."VC", r1."NVC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 WHERE (r1."ID" < 4) FOR UPDATE (4 rows) EXPLAIN (VERBOSE on, COSTS off) SELECT * FROM shorty; QUERY PLAN ------------------------------------------------------------------------------------------------- Foreign Scan on public.shorty Output: id, c Oracle query: SELECT /*91f1b22c0e234d8c0794325f39acdac8*/ r1."ID", r1."C" FROM "TYPETEST1" r1 Oracle plan: SELECT STATEMENT Oracle plan: TABLE ACCESS FULL TYPETEST1 (5 rows) -- this should fetch all columns from the foreign table EXPLAIN (COSTS off) SELECT typetest1 FROM typetest1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*4737e8f4f6dd5315e1ad7dcd923945e3*/ r1."ID", r1."C", r1."NC", r1."VC", r1."NVC", r1."LC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 (2 rows) /* * Test parameters. */ PREPARE stmt(integer, date, timestamp) AS SELECT d FROM typetest1 WHERE id = $1 AND d < $2 AND ts < $3; -- six executions to switch to generic plan EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00'); d ------------ 10-21-1968 (1 row) EXPLAIN (COSTS off) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*af20b28e0a7b0cb45f42d27a533f758e*/ r1."ID", r1."D", r1."TS" FROM "TYPETEST1" r1 WHERE (r1."D" < CAST (:p1 AS DATE)) AND (r1."TS" < CAST (:p2 AS TIMESTAMP)) AND (r1."ID" = :p3) (2 rows) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00'); d ------------ 10-21-1968 (1 row) DEALLOCATE stmt; -- test NULL parameters SELECT id FROM typetest1 WHERE vc = (SELECT NULL::text); id ---- (0 rows) /* * Test current_timestamp. */ SELECT id FROM typetest1 WHERE d < current_date AND ts < now() AND ts < current_timestamp AND ts < 'now'::timestamp ORDER BY id; id ---- 1 3 (2 rows) /* * Test foreign table based on SELECT statement. */ CREATE FOREIGN TABLE qtest ( id integer OPTIONS (key 'yes') NOT NULL, vc character varying(10), num numeric(7,5) ) SERVER oracle OPTIONS (table '(SELECT id, vc, num FROM typetest1)'); -- INSERT works with simple "view" INSERT INTO qtest (id, vc, num) VALUES (5, 'via query', -12.5); ALTER FOREIGN TABLE qtest OPTIONS (SET table '(SELECT id, SUBSTR(vc, 1, 3), num FROM typetest1)'); -- SELECT and DELETE should also work with derived columns SELECT * FROM qtest ORDER BY id; id | vc | num ----+-----------+----------- 1 | var | 3.14159 3 | a\x1B\x07 | -2.71828 4 | sho | 0.00000 5 | via | -12.50000 (4 rows) DELETE FROM qtest WHERE id = 5; /* * Test COPY */ BEGIN; COPY typetest1 FROM STDIN; ROLLBACK; /* * Test foreign table as a partition. */ CREATE TABLE party (LIKE typetest1) PARTITION BY RANGE (id); CREATE TABLE defpart PARTITION OF party DEFAULT; ALTER TABLE party ATTACH PARTITION typetest1 FOR VALUES FROM (1) TO (MAXVALUE); BEGIN; COPY party FROM STDIN; INSERT INTO party (id, lc, lr, lb) VALUES (12, 'very long character', '\x0001020304', '\xFFFEFDFC'); SELECT id, lr, lb, c FROM typetest1 ORDER BY id; id | lr | lb | c -----+----------------------------+----------------------------+---------------------- 1 | \xdeadbeef00 | \xdeadbeef00 | fixed chau 3 | \xdeadf00ddeadf00ddeadf00d | \xdeadf00ddeadf00ddeadf00d | a\x1B\x07\r\x7Fb 4 | \xdeadf00d | \xdeadf00d | short 12 | \x0001020304 | \xfffefdfc | 666 | \xffff | \x01020304 | cöpy 777 | \x00 | \x00 | fdjkl (6 rows) ROLLBACK; BEGIN; CREATE TABLE shortpart ( id integer NOT NULL, c character(10) ) PARTITION BY LIST (id); ALTER TABLE shortpart ATTACH PARTITION shorty FOR VALUES IN (1, 2, 3, 4, 5, 6, 7, 8, 9); INSERT INTO shortpart (id, c) VALUES (6, 'returnme') RETURNING *; id | c ----+------------ 6 | returnme (1 row) ROLLBACK; /* * Test triggers on foreign tables. */ -- trigger function CREATE FUNCTION shorttrig() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF TG_OP IN ('UPDATE', 'DELETE') THEN RAISE WARNING 'trigger % % OLD row: id = %, c = %', TG_WHEN, TG_OP, OLD.id, OLD.c; END IF; IF TG_OP IN ('INSERT', 'UPDATE') THEN RAISE WARNING 'trigger % % NEW row: id = %, c = %', TG_WHEN, TG_OP, NEW.id, NEW.c; END IF; RETURN NEW; END;$$; -- test BEFORE trigger CREATE TRIGGER shorttrig BEFORE UPDATE ON shorty FOR EACH ROW EXECUTE PROCEDURE shorttrig(); BEGIN; UPDATE shorty SET id = id + 1 WHERE id = 4; WARNING: trigger BEFORE UPDATE OLD row: id = 4, c = short WARNING: trigger BEFORE UPDATE NEW row: id = 5, c = short ROLLBACK; -- test AFTER trigger DROP TRIGGER shorttrig ON shorty; CREATE TRIGGER shorttrig AFTER UPDATE ON shorty FOR EACH ROW EXECUTE PROCEDURE shorttrig(); BEGIN; UPDATE shorty SET id = id + 1 WHERE id = 4; WARNING: trigger AFTER UPDATE OLD row: id = 4, c = short WARNING: trigger AFTER UPDATE NEW row: id = 5, c = short ROLLBACK; -- test AFTER INSERT trigger with COPY DROP TRIGGER shorttrig ON shorty; CREATE TRIGGER shorttrig AFTER INSERT ON shorty FOR EACH ROW EXECUTE PROCEDURE shorttrig(); BEGIN; COPY shorty FROM STDIN; WARNING: trigger AFTER INSERT NEW row: id = 42, c = hammer WARNING: trigger AFTER INSERT NEW row: id = 753, c = rom WARNING: trigger AFTER INSERT NEW row: id = 0, c = ROLLBACK; /* * Test ORDER BY pushdown. */ -- don't push down string data types EXPLAIN (COSTS off) SELECT id FROM typetest1 ORDER BY id, vc; QUERY PLAN -------------------------------------------------------------------------------------------------------- Sort Sort Key: id, vc -> Foreign Scan on typetest1 Oracle query: SELECT /*072b982b9aa0c38d3ad556626000d2d7*/ r1."ID", r1."VC" FROM "TYPETEST1" r1 (4 rows) -- push down complicated expressions EXPLAIN (COSTS off) SELECT id FROM typetest1 ORDER BY length(vc), CASE WHEN vc IS NULL THEN 0 ELSE 1 END, ts DESC NULLS FIRST FOR UPDATE; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- LockRows -> Foreign Scan on typetest1 Oracle query: SELECT /*7dc72f311b677cb569d2f9187585c5f0*/ r1."ID", r1."C", r1."NC", r1."VC", r1."NVC", r1."LC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 ORDER BY length(r1."VC") ASC NULLS LAST, CASE WHEN (r1."VC" IS NULL) THEN 0 ELSE 1 END ASC NULLS LAST, r1."TS" DESC NULLS FIRST FOR UPDATE (3 rows) SELECT id FROM typetest1 ORDER BY length(vc), CASE WHEN vc IS NULL THEN 0 ELSE 1 END, ts DESC NULLS FIRST FOR UPDATE; id ---- 4 3 1 (3 rows) /* * Test that incorrect type mapping throws an error. */ -- create table with bad type matches CREATE FOREIGN TABLE badtypes ( id integer OPTIONS (key 'yes') NOT NULL, c xml, nc xml ) SERVER oracle OPTIONS (table 'TYPETEST1'); -- should fail for column "nc", as "c" is not used SELECT id, nc FROM badtypes WHERE id = 1; ERROR: column "nc" of foreign table "badtypes" cannot be converted to or from Oracle data type -- this will fail for inserting a NULL in column "c" INSERT INTO badtypes (id, nc) VALUES (42, XML ''); ERROR: column "c" of foreign table "badtypes" cannot be converted to or from Oracle data type -- remove foreign table DROP FOREIGN TABLE badtypes; /* * Test subplans (initplans) */ -- testcase for bug #364 SELECT id FROM typetest1 WHERE vc NOT IN (SELECT * FROM (VALUES ('short'), ('other')) AS q) ORDER BY id; id ---- 1 3 (2 rows)