Message-ID: <45ED8EFD.5030908@mochima.com>
Date: Tue, 06 Mar 2007 10:55:41 -0500
From: Carlos Moreno <moreno_pg@mochima.com>
User-Agent: Mozilla Thunderbird 1.0.8-1.1.fc4 (X11/20060501)
MIME-Version: 1.0
CC: pgsql-performance@postgresql.org
Subject: Re: Insert performance
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>
In-Reply-To: <45ED37D1.3000503@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>>> 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
--