Parallel Insert and Delete operation

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

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

Thanks Albe Laurenz, Moshe Jacobson

@Albe - I got you first point. The second point is little skeptical because postgres could have been avoided this lock by using MVCC. Please correct me if I am wrong?
@ Jacobson - it could be possible that foreign key violation may arise but when it arise the procedure could have been stopped and it could be rollback the transaction. Please correct me if I am wrong?

Here, I want both operation should successfully run. So what I have to do. I would like to do some prototype test on this, hence please tell me is that possible to do it from pgadmin or I have write some example programming code on this.

Thanks & Regards,
Ramkumar

From: Moshe Jacobson [mailto:moshe@neadwerx.com]
Sent: Wednesday, October 31, 2012 9:01 PM
To: Albe Laurenz
Cc: Yelai, Ramkumar IN BLR STS; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Parallel Insert and Delete operation

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<mailto: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<mailto: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<mailto: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<mailto:moshe@neadwerx.com> | www.neadwerx.com<http://www.neadwerx.com/&gt;

Ramkumar Yelai wrote:
[is worried that a database might become inconsistent if conflicting
INSERTs and DELETEs occur]

@Albe - I got you first point. The second point is little skeptical

because postgres could have been

avoided this lock by using MVCC. Please correct me if I am wrong?

Which lock could have been avoided?

PostgreSQL locks rows when the data change.
That has little to do with MVCC.

If you INSERT into a table that has a foreign key, the
referenced row in the referenced table gets a SHARE lock
that conflicts with the EXCLUSIVE lock required for
a DELETE.
So they cannot execute concurrently.

Yours,
Laurenz Albe

Ramkumar Yelai wrote:
[is worried that a database might become inconsistent if conflicting INSERTs and DELETEs occur]

@Albe - I got you first point. The second point is little skeptical

because postgres could have been

avoided this lock by using MVCC. Please correct me if I am wrong?

Which lock could have been avoided?

PostgreSQL locks rows when the data change.
That has little to do with MVCC.

If you INSERT into a table that has a foreign key, the referenced row in the referenced table gets a SHARE lock that conflicts with the EXCLUSIVE lock required for a DELETE.
So they cannot execute concurrently.

Yours,
Laurenz Albe

Thanks very much Albe.

I am not aware of that, delete will lock the table.