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. > Is there any way to ask postgres to update the data more frequently or some way > to make it end sooner? By "update the data more frequently" I assume you mean "make the data visible to other transactions more frequently." To do that you could use multiple COPY statements and make sure each is committed before beginning the next. One way to do that would be to write a simple client that reads the file and issues a new "COPY tablename FROM stdin" for each X number of lines. But are you sure you want other transactions querying the table before it's completely loaded? Are queries based on incomplete data acceptable for whatever you're doing? > 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 -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
