Add decompressor tests to file_fdw suite

Duplicates the agg.csv-based tests, but using a decompressor. Includes a Perl-based decompressor since the codebase already depends on Perl and I didn't want to hardcode a path to the gunzip executable.
postgres · jasonmp85 · Dec 30, 2013 · Dec 30, 2013 · Dec 30, 2013 · Dec 31, 2013
commit 32ab3e2388a5ca69a4a57f2e0217557bcc2311d8
diff --git a/contrib/file_fdw/data/agg.csv.gz b/contrib/file_fdw/data/agg.csv.gz
diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source
@@ -72,6 +72,11 @@ CREATE FOREIGN TABLE agg_csv (
 	b	float4
 ) SERVER file_server
 OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
+CREATE FOREIGN TABLE agg_csv_gz (
+	a	int2,
+	b	float4
+) SERVER file_server
+OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv.gz', header 'true', delimiter ';', quote '@', escape '"', null '', decompressor '@abs_srcdir@/scripts/gunzip.pl');
 CREATE FOREIGN TABLE agg_bad (
 	a	int2,
 	b	float4
@@ -97,7 +102,9 @@ CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); --
 -- basic query tests
 SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
 SELECT * FROM agg_csv ORDER BY a;
+SELECT * FROM agg_csv_gz ORDER BY a;
 SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
+SELECT * FROM agg_csv_gz c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
 
 -- error context report tests
 SELECT * FROM agg_bad;               -- ERROR
@@ -111,6 +118,14 @@ EXECUTE st(100);
 EXECUTE st(100);
 DEALLOCATE st;
 
+\t on
+EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv_gz;
+\t off
+PREPARE st(int) AS SELECT * FROM agg_csv_gz WHERE a = $1;
+EXECUTE st(100);
+EXECUTE st(100);
+DEALLOCATE st;
+
 -- tableoid
 SELECT tableoid::regclass, b FROM agg_csv;
 

diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source
@@ -88,6 +88,11 @@ CREATE FOREIGN TABLE agg_csv (
 	b	float4
 ) SERVER file_server
 OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
+CREATE FOREIGN TABLE agg_csv_gz (
+	a	int2,
+	b	float4
+) SERVER file_server
+OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv.gz', header 'true', delimiter ';', quote '@', escape '"', null '', decompressor '@abs_srcdir@/scripts/gunzip.pl');
 CREATE FOREIGN TABLE agg_bad (
 	a	int2,
 	b	float4
@@ -140,6 +145,14 @@ SELECT * FROM agg_csv ORDER BY a;
  100 |  99.097
 (3 rows)
 
+SELECT * FROM agg_csv_gz ORDER BY a;
+  a  |    b    
+-----+---------
+   0 | 0.09561
+  42 |  324.78
+ 100 |  99.097
+(3 rows)
+
 SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
   a  |    b    |  a  |    b    
 -----+---------+-----+---------
@@ -148,6 +161,14 @@ SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
  100 |  99.097 | 100 |  99.097
 (3 rows)
 
+SELECT * FROM agg_csv_gz c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
+  a  |    b    |  a  |    b    
+-----+---------+-----+---------
+   0 | 0.09561 |   0 | 0.09561
+  42 |  324.78 |  42 |  324.78
+ 100 |  99.097 | 100 |  99.097
+(3 rows)
+
 -- error context report tests
 SELECT * FROM agg_bad;               -- ERROR
 ERROR:  invalid input syntax for type real: "aaa"
@@ -173,6 +194,29 @@ EXECUTE st(100);
  100 | 99.097
 (1 row)
 
+DEALLOCATE st;
+\t on
+EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv_gz;
+ Foreign Scan on public.agg_csv_gz
+   Output: a, b
+   Foreign File: @abs_srcdir@/data/agg.csv.gz
+   Foreign Program: @abs_srcdir@/scripts/gunzip.pl '@abs_srcdir@/data/agg.csv.gz'
+   Foreign Program Compression Est.: 2.7709
+
+\t off
+PREPARE st(int) AS SELECT * FROM agg_csv_gz WHERE a = $1;
+EXECUTE st(100);
+  a  |   b    
+-----+--------
+ 100 | 99.097
+(1 row)
+
+EXECUTE st(100);
+  a  |   b    
+-----+--------
+ 100 | 99.097
+(1 row)
+
 DEALLOCATE st;
 -- tableoid
 SELECT tableoid::regclass, b FROM agg_csv;
@@ -243,13 +287,14 @@ SET ROLE file_fdw_superuser;
 -- cleanup
 RESET ROLE;
 DROP EXTENSION file_fdw CASCADE;
-NOTICE:  drop cascades to 8 other objects
+NOTICE:  drop cascades to 9 other objects
 DETAIL:  drop cascades to server file_server
 drop cascades to user mapping for file_fdw_user
 drop cascades to user mapping for file_fdw_superuser
 drop cascades to user mapping for no_priv_user
 drop cascades to foreign table agg_text
 drop cascades to foreign table agg_csv
+drop cascades to foreign table agg_csv_gz
 drop cascades to foreign table agg_bad
 drop cascades to foreign table text_csv
 DROP ROLE file_fdw_superuser, file_fdw_user, no_priv_user;
diff --git a/contrib/file_fdw/scripts/gunzip.pl b/contrib/file_fdw/scripts/gunzip.pl
@@ -0,0 +1,10 @@
+#!/usr/bin/perl
+
+# Decompress the gzipped file at the path specified by the ARGV[0]
+# Usage: gunzip.pl /path/to/compressed/file.gz
+
+use strict;
+
+use IO::Uncompress::Gunzip qw(gunzip $GunzipError) ;
+
+gunzip $ARGV[0] => '-' or die "could not decompress: GunzipError\n";
diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml
@@ -32,6 +32,18 @@
    </listitem>
   </varlistentry>
 
+  <varlistentry>
+   <term><literal>decompressor</literal></term>
+
+   <listitem>
+    <para>
+     Specifies an external program to be used to decompress the file. Such
+     programs should accept the filename as an argument and decompress data to
+     stdout. The program must be readable and executable by the server process.
+    </para>
+   </listitem>
+  </varlistentry>
+
   <varlistentry>
    <term><literal>format</literal></term>