COPY for Npgsql, PostgreSQL 8.x .Net Driver

Copy is an efficient (typically tens of times faster than INSERT) bulk data exchange method for PostgreSQL databases. This is a driver extension to support COPY FROM STDIN and COPY TO STDOUT data exchange with the client for PostgresSQL dot-Net driver Npgsql. Derived from my earlier implementation for JDBC, it provides a simple Stream based API while keeping low level protocol handling in the internal class meant for that purpose.

Roadmap

• Encoder and decoder to hide conversion to server character set
• Serializer and deserializer to present the data as objects rather than byte stream
• Binary copy format support with above serializers
• Caching (row based, to work around server glitches with abrupt packet ending)

History

• 2007-10-12 Documentation completed, minor fixes, preliminary Serializer.
• 2007-10-05 Fixed overflow bug, error handling, added unit tests.
• 2007-09-28 First version with acceptablish API, for raw streams only.

Sources and Binaries

The original patch was made against latest published beta and is already included in Npgsql2 CVS. Fixes in the other patch are not yet; it's against CVS HEAD for now. So please use the DLL with latest time stamp, apply both patches against latest beta source or apply latest patch against source from CVS HEAD.

Full original source without the patches is available at the official site: http://pgfoundry.org/projects/npgsql

Readily usable DLL compiled with nant on OSX is also available.

• Npgsql-copy-20071012.diff.gz (12791 bytes); Npgsql-copy-20071012.dll (233984 bytes)
• Npgsql-cvs-copyfixes-20071005.diff.gz (4981 bytes); Npgsql-cvs-copyfixes-20071005.dll (226816 bytes)
• Npgsql-copy-20070927-2.diff.gz (5577 bytes); Npgsql-copy-20070927-2.dll (226304 bytes)

Installation

gacutil -i Npgsql.dll

Patch contents

NpgsqlCommand

Added COPY to operations that may report number of updated rows.

NpgsqlCopyIn

API for feeding data into database:

Constructor takes a SQL command, database connection and optional readable stream. Given command is executed upon Start() and all data from fromStream is passed to it as copy data. If fromStream is not provided, CopyStream is set to provide a writable stream. Data for the requested COPY IN operation can then be written to CopyData stream followed by a call to End() (also issued if the writable stream is closed) to complete or Cancel() to withdraw the operation.

NpgsqlCopyIn(NpgsqlCommand cmd, NpgsqlConnection conn, Stream fromStream)
NpgsqlCopyIn(NpgsqlCommand cmd, NpgsqlConnection conn)
NpgsqlCopyIn(string copyInQuery, NpgsqlConnection conn)

Constructors

Start()

Executes the operation

End()

Completes the operation, if active

Cancel()

Withdraws the operation, if active

CopyStream

Stream associated with this operation

NpgsqlCommand

Command associated with this operation

CopyBufferSize

Size of data chunks read from fromStream and passed to server, default is 8192 bytes.

NpgsqlCopyInState

Internal state between CopyInResponse and ReadyForQuery, ie. when server is accepting CopyData, CopyDone or CopyFail messages. Provides methods for sending such.

NpgsqlCopyInStream

Writable stream provided for writing to COPY IN. Provides methods for ending succesfully (Close()) or failing (Cancel()). TODO: Remove Fail()

NpgsqlCopyOut

API for reading data from database:

Constructor takes a SQL command, database connection and optional writable stream. Given command is executed upon Start() and all data from it is passed to toStream. If toStream is not provided, CopyStream is set to provide a readable stream. Data from the requested COPY OUT operation can then be read from CopyData stream until a null record (from byte[] Read()) or zero bytes (from int Read(byte[],int,int). End() should be called to clean up afterwards. Closing provided readable CopyStream discards rest of copy data from server.

NpgsqlCopyOut(NpgsqlCommand cmd, NpgsqlConnection conn, Stream toStream)
NpgsqlCopyOut(NpgsqlCommand cmd, NpgsqlConnection conn)
NpgsqlCopyOut(string copyOutQuery, NpgsqlConnection conn)

Constructors

Start()

Executes the operation

End()

Cleans up after operation completed

Read()

Withdraws the operation, if active

CopyStream

Stream associated with this operation

NpgsqlCommand

Command associated with this operation

NpgsqlCopyOutState

Internal state between CopyOutResponse and ReadyForQuery, ie. when server is sending CopyData messages followed with a CopyDone. Provides methods for reading the data.

NpgsqlCopyOutStream

Readable stream provided for reading from COPY OUT. Provides methods for reading the data (notably a non-standard but convenient byte[] Read()) or discarding the rest of it (Close()).

NpgsqlMediator

Container for variables CopyStream, CopyBufferSize and ReceivedCopyData, required for copy operations.

NpgsqlMessageTypes

CopyData, CopyDone and CopyFail messages for PosgreSQL protocol version 3.

NpgsqlState

Declares copy state methods and handles copy messages. Also copy header data (should later be provided for user, at least overall format and number of fields.)

Examples

pgcopy.cs


pgcopy "CREATE TEMP TABLE t(t text)" "COPY t FROM STDIN" -o out "COPY t TO STDOUT"
Copy rows from stdin into temporary table, then contents of that table to local file "out".