On Sat, 2005-10-29 at 09:10 -0600, Michael Fuhr wrote: > On Fri, Oct 28, 2005 at 06:22:43PM -0000, davidgn@xxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxx> ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
