From: Craig James <cjames@emolecules.com>
To: Mark Thornton <mthornton@optrak.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: DELETE vs TRUNCATE explanation
Date: Wed, 11 Jul 2012 15:09:56 -0700
Message-ID: <CAFwQ8rdZWMHvmEEPyOKCgC0bMtPhaRGiY1nSVLA_tORAk1SVPg@mail.gmail.com> (raw)
In-Reply-To: <4FFDF0F1.9010005@optrak.com>
References: <4FFCCAC4.4030503@ringerc.id.au>
<CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
<23853.1342015548@sss.pgh.pa.us>
<CAFwQ8rcR5mimJO5FumcYzkgoxkNiP3g3XOaHB1H4oHe-QnLRPQ@mail.gmail.com>
<4FFDF0F1.9010005@optrak.com>
On Wed, Jul 11, 2012 at 2:32 PM, Mark Thornton <mthornton@optrak.com> wrote:
> On 11/07/12 21:18, Craig James wrote:
>
>>
>> It strikes me as a contrived case rather than a use case. What sort of
>> app repeatedly fills and truncates a small table thousands of times ...
>> other than a test app to see whether you can do it or not?
>>
> If I have a lot of data which updates/inserts an existing table but I
> don't know if a given record will be an update or an insert, then I write
> all the 'new' data to a temporary table and then use sql statements to
> achieve the updates and inserts on the existing table.
>
> Is there a better way of doing this in standard SQL?
>
If it's a single session, use a temporary table. It is faster to start
with (temp tables aren't logged), and it's automatically dropped at the end
of the session (or at the end of the transaction if that's what you
specified when you created it). This doesn't work if your insert/update
spans more than one session.
Another trick that works (depending on how big your tables are) is to scan
the primary key before you start, and build a hash table of the keys. That
instantly tells you whether each record should be an insert or update.
Craig
>
> Mark
>
>
>
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: cjames@emolecules.com, mthornton@optrak.com
Subject: Re: DELETE vs TRUNCATE explanation
In-Reply-To: <CAFwQ8rdZWMHvmEEPyOKCgC0bMtPhaRGiY1nSVLA_tORAk1SVPg@mail.gmail.com>
References: <4FFCCAC4.4030503@ringerc.id.au> <CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com> <23853.1342015548@sss.pgh.pa.us> <CAFwQ8rcR5mimJO5FumcYzkgoxkNiP3g3XOaHB1H4oHe-QnLRPQ@mail.gmail.com> <4FFDF0F1.9010005@optrak.com> <CAFwQ8rdZWMHvmEEPyOKCgC0bMtPhaRGiY1nSVLA_tORAk1SVPg@mail.gmail.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