is it possible to flush / commit / update underlying table during the COPY table FROM 'pipe'

From: Trent George <trent(dot)george1(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: is it possible to flush / commit / update underlying table during the COPY table FROM 'pipe'
Date: 2011-03-26 23:31:14
Message-ID: AANLkTi=0pJP80m+6gcxvxn6Zfqu+Y=3jZaCufUgme_sd@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jon Jensen 2011-03-27 18:09:12 Re: is it possible to flush / commit / update underlying table during the COPY table FROM 'pipe'
Previous Message Lew 2011-03-26 13:16:19 Re: Re: Not to start a flame war but what does Oracle have that Postgresql does not?