How to lock and unlock table in postgresql
Hi ya,
I've 2 tables: One being the master (table1) and another being the slave (table2). I want to lock table1 until the below function completes, and it should unlock the same at last.
Below is my function. Pls guide me on how to apply locking table1 and unlocking the same finally. The scenario why I require this is: I want to add 70000 records in these tables. As I want to retrieve the last generated Id of table1 which is needed for a foreign column in table2, I used MAX operation. If anyone tries to add a record manually, I suspect it might get disturbed. So, I wish to go for locking and unlocking the table for every record insertion such that other process waits till this function completes its tasks. If there is a better solution, kindly let me know.
CREATE OR REPLACE FUNCTION Fun()
RETURNS VOID AS '
DECLARE
Id INTEGER;
BEGIN
INSERT INTO table1 VALUES (DEFAULT, ''Sample'', DEFAULT);
SELECT MAX(id) INTO Id FROM table1;
INSERT INTO table2 VALUES(DEFAULT,Id,''sampledata'');
END;
' LANGUAGE 'plpgsql';
Regards,
Siva.
________________________________
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.
-----------------------------------------------------------------------------------------------------------------------
On 11/16/11 10:10 PM, Siva Palanisamy wrote:
If there is a better solution, kindly let me know.
use nextval('seqname') ... full transactional integrity without any
blocking or locking.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On 11/16/11 10:23 PM, John R Pierce wrote:
use nextval('seqname')
sorry, I meant, currval('seqname') oooops.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
Hi John,
Thanks for the solution. If I use currval('sqlname') in a loop of 70000 records, what will happen if a record is inserted manually? I guess it will alter the sequences, and wrong values/chain might be introduced in foreign tables in the below function. Could you please clarify me on this?
Thanks and Regards,
Siva.
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2011 12:05 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to lock and unlock table in postgresql
On 11/16/11 10:23 PM, John R Pierce wrote:
use nextval('seqname')
sorry, I meant, currval('seqname') oooops.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.
-----------------------------------------------------------------------------------------------------------------------
On 11/16/11 11:16 PM, Siva Palanisamy wrote:
Thanks for the solution. If I use currval('sqlname') in a loop of 70000 records, what will happen if a record is inserted manually? I guess it will alter the sequences, and wrong values/chain might be introduced in foreign tables in the below function. Could you please clarify me on this?
currval is consistent within the current transaction. another
transaction will see the different currval that its generated.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On 17 Nov 2011, at 7:10, Siva Palanisamy wrote:
If there is a better solution, kindly let me know.
CREATE OR REPLACE FUNCTION Fun()
RETURNS VOID AS '
DECLARE
Id INTEGER;
BEGIN
INSERT INTO table1 VALUES (DEFAULT, ''Sample'', DEFAULT);
SELECT MAX(id) INTO Id FROM table1;
INSERT INTO table2 VALUES(DEFAULT,Id,''sampledata'');
END;
' LANGUAGE 'plpgsql';Regards,
Siva.
As John says, you're re-inventing the wheel that sequences solve. You could also get the id using INSERT .. RETURNING.
You have another problem on your hands though. You have a naming conflict between your variable name and a column name in that second query: id and Id are the same.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
Hi Alban,
Thanks for the reply.
1) I'm using PostgreSQL 8.1; So, I can't use RETURNING clause!
2) The function I gave is just to put my understanding! Thanks for spotting the error though.
Regards,
Siva.
-----Original Message-----
From: Alban Hertroys [mailto:haramrae@gmail.com]
Sent: Thursday, November 17, 2011 1:20 PM
To: Siva Palanisamy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to lock and unlock table in postgresql
On 17 Nov 2011, at 7:10, Siva Palanisamy wrote:
If there is a better solution, kindly let me know.
CREATE OR REPLACE FUNCTION Fun()
RETURNS VOID AS '
DECLARE
Id INTEGER;
BEGIN
INSERT INTO table1 VALUES (DEFAULT, ''Sample'', DEFAULT);
SELECT MAX(id) INTO Id FROM table1;
INSERT INTO table2 VALUES(DEFAULT,Id,''sampledata'');
END;
' LANGUAGE 'plpgsql';Regards,
Siva.
As John says, you're re-inventing the wheel that sequences solve. You could also get the id using INSERT .. RETURNING.
You have another problem on your hands though. You have a naming conflict between your variable name and a column name in that second query: id and Id are the same.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.
-----------------------------------------------------------------------------------------------------------------------
