[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Parallel Insert and Delete operation
From: Moshe Jacobson <moshe () neadwerx ! com>
Date: 2012-10-31 15:31:06
Message-ID: CAJ4CxL=ejPBTwZDSRhX9dQNSZRup5n56JvYh9z0aLzD=N+rCGg () mail ! gmail ! com
[Download RAW message or body]
It is also possible that you will get a foreign key violation exception on
the process inserting into table 2, but you will not get database
inconsistency.
On Wed, Oct 31, 2012 at 9:33 AM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
> Yelai, Ramkumar IN BLR STS worte:
> > Sent: Wednesday, October 31, 2012 12:40 PM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] Parallel Insert and Delete operation
> >
> > Hi All,
> >
> > Please clarify me the following example.
> >
> > I have 2 tables
> >
> > Table1 - ( it has one primary key and few columns )
> > Table2 - ( it has one primary key and few columns. It has one
> foreign key, which refers table1
> > primary key ).
> >
> > I have 2 operations, which are written in pl/pgsql procedure.
> >
> > Operation1() - Inserts the records to table2 at every hour basis.
> > Operation2() - Delete the records from Table 1 and Table2 based on the
> primary key.
> >
> > What if both operations are running at the time for the same primary
> key.
> >
> > what I have to take care to run these two operations perfectly without
> creating inconsistency in
> > database.
>
> With the foreign key in place there can be no entry in table2
> that does not have a corresponding entry in table1.
>
> Concurrency is solved with locks, so one of the concurrent
> operations might have to wait until the other one is done.
>
> That is handled by the database system automatically.
>
> Yours,
> Laurenz Albe
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com
[Attachment #3 (text/html)]
It is also possible that you will get a foreign key violation exception on the \
process inserting into table 2, but you will not get database \
inconsistency.<br><br><div class="gmail_quote">On Wed, Oct 31, 2012 at 9:33 AM, Albe \
Laurenz <span dir="ltr"><<a href="mailto:laurenz.albe@wien.gv.at" \
target="_blank">laurenz.albe@wien.gv.at</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">Yelai, Ramkumar IN BLR STS worte:<br> > Sent: Wednesday, \
October 31, 2012 12:40 PM<br> > To: <a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a><br> > \
Subject: [GENERAL] Parallel Insert and Delete operation<br> <div><div \
class="h5">><br> > Hi All,<br>
><br>
> Please clarify me the following example.<br>
><br>
> I have 2 tables<br>
><br>
> Table1 - ( it has one primary key and few columns )<br>
> Table2 - ( it has one primary key and few columns. It has one<br>
foreign key, which refers table1<br>
> primary key ).<br>
><br>
> I have 2 operations, which are written in pl/pgsql procedure.<br>
><br>
> Operation1() - Inserts the records to table2 at every hour basis.<br>
> Operation2() - Delete the records from Table 1 and Table2 based on the<br>
primary key.<br>
><br>
> What if both operations are running at the time for the same primary<br>
key.<br>
><br>
> what I have to take care to run these two operations perfectly without<br>
creating inconsistency in<br>
> database.<br>
<br>
</div></div>With the foreign key in place there can be no entry in table2<br>
that does not have a corresponding entry in table1.<br>
<br>
Concurrency is solved with locks, so one of the concurrent<br>
operations might have to wait until the other one is done.<br>
<br>
That is handled by the database system automatically.<br>
<br>
Yours,<br>
Laurenz Albe<br>
<span class="HOEnZb"><font color="#888888"><br>
<br>
--<br>
Sent via pgsql-general mailing list (<a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>)<br> To \
make changes to your subscription:<br> <a \
href="http://www.postgresql.org/mailpref/pgsql-general" \
target="_blank">http://www.postgresql.org/mailpref/pgsql-general</a><br> \
</font></span></blockquote></div><br><br clear="all"><div><br></div>-- <br><div><span \
style="border-collapse:collapse"><font face="'times new roman', \
serif"><div><span style="border-collapse:collapse"><font><font>Moshe Jacobson<br>
</font></font>Nead Werx, Inc. <font>| Senior Systems \
Engineer</font></span></div><div><span style="border-collapse:collapse"><font>2323 \
Cumberland Parkway, Suite 201 | Atlanta, GA 30339<br></font><font><a \
href="mailto:moshe@neadwerx.com" target="_blank">moshe@neadwerx.com</a> | \
</font></span><font><a href="http://www.neadwerx.com/" \
target="_blank">www.neadwerx.com</a></font></div>
</font></span></div><br>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic