Re: PostgreSQL under BSD/OS

> > I do this with C programs which output two principal files: the first is
> > an input file for psql which contains various commands to create tables
> > and indexes, etc.; the second, much larger, file contains the actual
> > data in a suitable format.
> >
> > The first file is input to psql by the \i command. The last thing in
> > that first file is a SQL COPY command which copies the data from the big
> > data file into the appropriate table. After an hour or so, the data
> > input completes and I can issue psql commands to play with the data to
> > see if it's the way I expect. At this point, psql ought to close the
> > data file that it copied the data from so that I can delete it. As
> > things stand, I have three copies of all the data -- the original
> > database (which I can't remove until this process is completed in a few
> > weeks); the temporary data file, used as input to psql (which I want to
> > delete since it can be recreated if needed); and the PostgreSQL database
> > which I have just created.
> >
> > But if I remove the temporary file, I don't get any disk space back
> > because psql still has it open. If I \connect to the same database,
> > then psql closes the input file, but my contention is that I should not
> > have to do that. I hope this explanation is clear.
>
> 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.

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>