From: John A Meinel <john@arbash-meinel.com>
To: Veikko Mäkinen <veikko.makinen@ecom.fi>
Cc: pgsql-performance@postgresql.org
Subject: Re: How does the transaction buffer work?
Date: Thu, 16 Jun 2005 14:36:07 -0500
Message-ID: <42B1D4A7.60407@arbash-meinel.com> (raw)
In-Reply-To: <42B1D2DE.4020709@ecom.fi>
References: <42B1D2DE.4020709@ecom.fi>
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
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, veikko.makinen@ecom.fi
Subject: Re: How does the transaction buffer work?
In-Reply-To: <42B1D4A7.60407@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox