jasonmp85/postgres — pull request #1 by jasonmp85 (2013-10-06 23:06:59+00:00)
Title: Enable use of compressed files with `file_fdw`

# Summary

This request is mostly for perusal because I haven't fleshed out all aspects to make this feature work optimally; however, it does work fully.

## Installation

To install the extension:

1. Build and install the extension: `cd contrib/file_fdw; make clean all install`
2. Remove any existing `file_fdw` extension in the schema you wish to use
3. Load the new version of the extension: `psql -c'CREATE EXTENSION file_fdw;'

## Configuration

Now you can use the extension. It augments existing `file_fdw` options with a `gzip` flag (default `false`), allowing users to declare `FOREIGN TABLE`s using compressed files. For instance, I have downloaded [this](http://introcs.cs.princeton.edu/java/data/fortune500.csv) data set and have it available in my home folder in plain and compressed form:

```sql
CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE fortune_500 (
year smallint NOT NULL,
rank smallint NOT NULL,
name varchar(255) NOT NULL,
revenue decimal,
profit decimal
) SERVER file_fdw_server OPTIONS (
filename '/Users/jpetersen/Downloads/fortune500.csv',
format 'csv',
header 'true',
gzip 'false',
delimiter ',',
quote '"',
escape '"',
null 'N.A.',
encoding 'UTF8'
);

CREATE FOREIGN TABLE gzfortune_500 (
year smallint NOT NULL,
rank smallint NOT NULL,
name varchar(255) NOT NULL,
revenue decimal,
profit decimal
) SERVER file_fdw_server OPTIONS (
filename '/Users/jpetersen/Downloads/fortune500.csv.gz',
format 'csv',
header 'true',
gzip 'true',
delimiter ',',
quote '"',
escape '"',
null 'N.A.',
encoding 'UTF8'
);
```

## Use

Now I can use either table:

```sql
SELECT name, year FROM fortune_500 WHERE profit > 1000 LIMIT 1;
-- name | year
-- ----------------+------
-- General Motors | 1956
-- General Motors | 1963
-- General Motors | 1964
-- Exxon Mobil | 1964
-- General Motors | 1965
-- Exxon Mobil | 1965
-- General Motors | 1966
-- Exxon Mobil | 1966
-- General Motors | 1967
-- Exxon Mobil | 1967

SELECT name, year FROM gzfortune_500 WHERE profit < 0 LIMIT 10;
-- name | year
-- ----------------------------------+------
-- Cudahy | 1955
-- American Motors | 1955
-- Studebaker | 1955
-- Kaiser Industries | 1955
-- Hearst Consolidated Publications | 1955
-- Case | 1955
-- Servel | 1955
-- Motor Products | 1955
-- Crowell-Collier Publishing | 1955
-- National Automotive Fibers | 1955
```

# TODO

So if this works, why am I saying it's suboptimal? I'd like to (in order of desire):

1. Verify that my EOF handling is correct for compressed files
2. Modify the cost calculation heuristic to consider compression. To wit, I need to…
1. guess the size of the uncompressed data. This is easy to do for files under 4 GB, so I'd at least like to add that
2. modify the startup cost calculation based on the extra allocation and processing needed by the zlib routines
3. modify the ongoing cost calculation based on the extra processing required for decompression (or conversely, the reduced I/O cost of reading fewer bytes from disk)
3. Do some better testing of error conditions (transaction rollback, file errors, bad gzip data, etc.)
4. Add tests for compressed files in the `contrib/file_fdw` subdirectory
5. Refactor some of my repeated structures into methods or macros
6. Update the generated documentation to reflect the new option
7. Finish modifying `copy.c` to allow compressed output (from `COPY TO`), for completeness

Just wanted to get this PR out there in case I don't have time to do all of the above.