Re: PostgreSQL under BSD/OS

> > OK, the file you are using for COPY is still open. Let me try and find
> > the cause, and I can fix it.
> >
> > Are you using the COPY command, or psql's \copy command?
>
> I've tried to be clear above, showing `\i' and `SQL COPY command'. No,
> I did not use psql's `\copy' command.
>
> > After the
> > copy, if you do an 'ls -i data_file', you get the inode number. If you
> > grep 'fstat' what process is holding the file as open? Is it psql or
> > the postgres backend process?
>
> The command is `postgres'. It has two FDs for the file that was read in
> as the target of the COPY command. If you do `\connect dbname' in psql,
> the open files are closed.

Two file descriptors. That seems strange to me.

I just tried it on a file with 200000 integers:

#$ fstat |grep 'tmp'
postgres postmaster 29288 27 /tmp 5 -rw-r--r-- 1288895 r
maillist ema 29265 3 /tmp 6 -rwx------ 622165 r
maillist sh 29264 3 /tmp 6 -rwx------ 622165 r
maillist sh 29263 3 /tmp 6 -rwx------ 622165 r
maillist elm 26332 3 /tmp 6 -rwx------ 622165 r
#$ fstat |grep 'tmp'
maillist ema 29265 3 /tmp 6 -rwx------ 622165 r
maillist sh 29264 3 /tmp 6 -rwx------ 622165 r
maillist sh 29263 3 /tmp 6 -rwx------ 622165 r
maillist elm 26332 3 /tmp 6 -rwx------ 622165 r

As you can see, the file with inode 5 in /tmp was open during the copy,
but closed after the copy completed. I tried it several times, and it
worked every time. Now, if I got an error in the COPY, it did not close
the file descriptor, as it should. Not quite sure how to fix that, but
it should be fixed.

>
> BTW, because I didn't have an hour to wait while I did this with the
> real data, I tried it with a test file with only four rows of data. The
> first time, it happened as described. However, after doing the \connect,
> the problem did not repeat on the next couple of tries. It was
> consistent when I was working with a table with 50,000 rows, however.
>
> > During the copy, is it failing or
> > succeeding? I can see a case were a copy failure will not properly
> > close the file.
>
> The copy completes successfully.
>
> --
> Greg Black <gjb(at)acm(dot)org>
>
>
>

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)