On Sat, 2005-10-29 at 09:10 -0600, Michael Fuhr wrote:
> On Fri, Oct 28, 2005 at 06:22:43PM -0000, davidgn@servidor.unam.mx wrote:
> > This is postgresql 7.4
> > I am trying to check that postgres is updating a table.
> > I have a pretty large ascii table file (+- 210 Mb) which I am copying into a
> > table with pgsql, but it is taking a long time, and any select query I do to the
> > table returns me 0 rows
>
> Presumably you're using COPY or doing multiple inserts inside a
> transaction (hopefully the former for performance reasons). No
> other transaction will be able to see any of the data until the
> inserting transaction commits it (a single statement like COPY is
> wrapped in its own transaction even if it occurs outside an explicit
> transaction block). You could use contrib/pgstattuple to check on
> the copy/insert's progress, but that won't allow you to query the
> data itself.
>
Yep. The script uses COPY to populate the database right after erasing
all its contents.
I was wondering about querying just as a mean of feedback, as you
guessed, but anything else would be right.
I initially though that read access was available as each row was
copied.
I have been playing with this script, which was handed to me, and before
DELETE and COPY they run a vacuumdb on the whole database.
I commented that line out and the COPY ran in about 5.5 mins (under
postgres 7.3 the script runs all right)
I am wondering if that was what was somehow causing the problem, as I
did some lot of stuff with the database and couldn't be sure.
I found some discussions about ANALYZE, but such discussions were under
Postgres 8, so I don't know if this is related.
> > I am a bit clueless as to what can I do to the configuration files to optimize
> > this copy.
>
> See "Populating a Database" in the "Performance Tips" chapter of
> the documentation for some ideas.
>
> http://www.postgresql.org/docs/8.0/interactive/populate.html
>
Thank you.
A lot of stuff I can't grab yet. Guess I need some more experience
administering databases.
Sorry about the double mail, I didn't realize I hadn't replied to the
list. Lest this be archived.
--
David Eduardo Gómez Noguera <davidgn@servidor.unam.mx>
