FAQ
Hi,

I am experimenting on postgresql 9.0 windows
I am using CreateNamedPipe in windows

I am using the command (with an corresponding custom win32 console app
that opens and sends data)
copy table from e'\\\\.\\pipe\testpipe' with delimiter e'\t';

Everything is working as expected and documented.

I am hoping to leave this connection (pipe) open for an extended
period of time, and send fresh data as needed (from a source
application)

My problem and question is that  the data is not available in the
table until I end / close the pipe.
I test this using "select count(*) from table;"  in another connection.
The moment the pipe is closed, all the data becomes available

I assume an underlying begin / commit is happening under the hood at
the start / end of the copy from command

Any amount of flushing, or size of data (tested up to 50 mb) does not
make the data available to the table before the command finishes.
I also experimented with the end-of-data "\\.\n" but all this does it
break the pipe (you should not send data after this)

Is it (or would it) be possible to allow some type of equivalent of
BATCHSIZE as used in mssql BULK INSERT command
This would group and commit this number of records during the import,
while it is still running
One could in theory use a value of =1, =1000 or leave the default (whole batch)

This would have multiple advantages
1/ make data available during long imports (because of bandwidth, file
size, or other delays)
2/ not loose all data if you have a problem records
3/ not loose all data if you get a connection (pipe) die
4/ allow for 'open' connections to allow continuous back end import
programs to feed data as it becomes available.
5/ as it is an optional, non default setting, no existing program
would behave different, but new one could take advantage of it.

Trent

Search Discussions

  • Jon Jensen at Mar 27, 2011 at 6:16 pm

    On Mar 26, 2011, at 5:31 PM, Trent George wrote:

    Is it (or would it) be possible to allow some type of equivalent of
    BATCHSIZE as used in mssql BULK INSERT command
    This would group and commit this number of records during the import,
    while it is still running
    One could in theory use a value of =1, =1000 or leave the default (whole batch)
    There is nothing built-in for what you describe, but you could accomplish the desired result on your end. e.g. split the import into batches of 1000 (or whatever) and issue individual COPY commands for each one. This could all be wrapped into a sh/perl/whatever script to make it reusable, configurable, add error handling, etc.

    Jon

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 26, '11 at 11:31p
activeMar 27, '11 at 6:16p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Jon Jensen: 1 post Trent George: 1 post

People

Translate

site design / logo © 2021 Grokbase