Re: How does the transaction buffer work?

Veikko Mäkinen wrote:

> Hey,
>
> 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.

John
=:->

>
> -veikko