Message-ID: <42B1D4A7.60407@arbash-meinel.com>
Date: Thu, 16 Jun 2005 14:36:07 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Veikko_M=E4kinen?= <veikko.makinen@ecom.fi>
Cc: pgsql-performance@postgresql.org
Subject: Re: How does the transaction buffer work?
References: <42B1D2DE.4020709@ecom.fi>
In-Reply-To: <42B1D2DE.4020709@ecom.fi>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig43DEAECC775450173E44EF25"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig43DEAECC775450173E44EF25
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Veikko M=E4kinen wrote:

> Hey,
>
> How does Postgres (8.0.x) buffer changes to a database within a=20
> transaction? I need to insert/update more than a thousand rows (mayde=20
> even more than 10000 rows, ~100 bytes/row) in a table but the changes=20
> must not be visible to other users/transactions before every row is=20
> updated. One way of doing this that I thought of was start a=20
> transaction, delete everything and then just dump new data in (copy=20
> perhaps). The old data would be usable to other transactions until I=20
> commit my insert. This would be the fastest way, but how much memory=20
> 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=20
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.=20
Especially since you'll have problems with foreign keys at the DELETE sta=
ge.

John
=3D:->

>
> -veikko


--------------enig43DEAECC775450173E44EF25
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCsdSnJdeBCYSNAAMRAlEtAJ9SjVXnBlARTRTCF8QsEhlqFsXrcACfdCoW
tczQPVs2jxvqEGY0tM6PdlU=
=Bhn2
-----END PGP SIGNATURE-----

--------------enig43DEAECC775450173E44EF25--