Veikko Mäkinen wrote:


How does Postgres (8.0.x) buffer changes to a database within a transaction? I need to insert/update more than a thousand rows (mayde even more than 10000 rows, ~100 bytes/row) in a table but the changes must not be visible to other users/transactions before every row is updated. One way of doing this that I thought of was start a transaction, delete everything and then just dump new data in (copy perhaps). The old data would be usable to other transactions until I commit my insert. This would be the fastest way, but how much memory would this use? Will this cause performance issues on a heavily loaded server with too little memory even to begin with :)

Postgres does indeed keep track of who can see what. Such that changes won't be seen until a final commit.
If you are trying to insert bulk data, definitely consider COPY.

But UPDATE should also be invisible until the commit. So if you are only changing data, there really isn't any reason to do a DELETE and INSERT. Especially since you'll have problems with foreign keys at the DELETE stage.



Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to