is it possible to flush / commit / update underlying table during the COPY table FROM 'pipe'
От | Trent George |
---|---|
Тема | is it possible to flush / commit / update underlying table during the COPY table FROM 'pipe' |
Дата | |
Msg-id | AANLkTi=0pJP80m+6gcxvxn6Zfqu+Y=3jZaCufUgme_sd@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: is it possible to flush / commit / update underlying table during the COPY table FROM 'pipe'
|
Список | 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
В списке pgsql-novice по дате отправления: