From: Carlos Moreno <moreno_pg@mochima.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Insert performance
Date: Tue, 06 Mar 2007 10:55:41 -0500
Message-ID: <45ED8EFD.5030908@mochima.com> (raw)
In-Reply-To: <45ED37D1.3000503@archonet.com>
References: <1172596221.393824.41130@j27g2000cwj.googlegroups.com>
<45ED1996.7050008@archonet.com>
<1173167607.4917.8.camel@hatman>
<45ED217D.30404@archonet.com>
<1173172748.4917.23.camel@hatman>
<45ED37D1.3000503@archonet.com>
>>> 1. If you're running 8.2 you can have multiple sets of values in an
>>> INSERT
>>> http://www.postgresql.org/docs/8.2/static/sql-insert.html
>>
>>
>> Yeah, i'm running the 8.2.3 version ! i didn't know about multiple
>> inserts sets ! Thanks for the tip ;-)
>
No kidding --- thanks for the tip from me as well !!!
I didn't know this was possible (though I read in the docs that it is ANSI
SQL standard), and I'm also having a similar situation.
Two related questions:
1) What about atomicity? Is it strictly equivalent to having multiple
insert
statements inside a transaction? (I assume it should be)
2) What about the issue with excessive locking for foreign keys when
inside a transaction? Has that issue disappeared in 8.2? And if not,
would it affect similarly in the case of multiple-row inserts?
In case you have no clue what I'm referring to:
Say that we have a table A, with one foreign key constraint to table
B --- last time I checked, there was an issue that whenever inserting
or updating table A (inside a transacion), postgres sets an exclusive
access lock on the referenced row on table B --- this is overkill, and
the correct thing to do would be to set a read-only lock (so that
no-one else can *modify or remove* the referenced row while the
transaction has not been finished).
This caused unnecessary deadlock situations --- even though no-one
is modifying table B (which is enough to guarantee that concurrent
transactions would be ok), a second transacion would fail to set the
exclusive access lock, since someone already locked it.
My solution was to sort the insert statements by the referenced value
on table B.
(I hope the above explanation clarifies what I'm trying to say)
I wonder if I should still do the same if I go with a multiple-row
insert instead of multiple insert statements inside a transaction.
Thanks,
Carlos
--
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: moreno_pg@mochima.com
Subject: Re: Insert performance
In-Reply-To: <45ED8EFD.5030908@mochima.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