How do I lock a Table in POSTGRESQL?

[gezeala]

I want to LOCK a TABLE so that users can't use the table when there is another currently accessing it...

How do I do it? 😕

Please help...

TIA!!

[ultraslacker]

<Shamelessly copied from the manual>

BEGIN WORK;
LOCK TABLE films IN ACCESS EXCLUSIVE MODE;
SELECT id FROM films
WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES
(id, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;

[gezeala]

LOCK TABLE films IN ACCESS EXCLUSIVE MODE;
--- I'll try this one out... ---

[mattr]

Yikes! Locking the whole table in that instance is unnecessary and what looks like a MySQL-centric way of doing things! 🙁🙁🙁

[gezeala]

The problem is I have to lock the table within a plpgsql function, 😕

---

create function fn_test() returns bool as'

declare
-- variable declaration --

begin

-- some queries here --

BEGIN transaction;

Lock table...;

END transaction;

-- some queries here --

end;

' language 'plpgsql';

---

is not working. :mad:

If I omit the BEGIN transaction and END transaction
and leave the Lock table... it doesn't give any error but I don't have any way of knowing if I've really lock my table...

[mattr]

Why do you need to lock the ENTIRE TABLE?

[gezeala]

Inside the function I'm doing updates, inserting and deleting which are crucial to the system I'm making...It would ruin the function if users are using it simultaneously...

[mattr]

Then it sounds like you are doing something wrong. Care to elaborate?

[gezeala]

here's my function..

CREATE OR REPLACE FUNCTION fn_pi_gen(int4) RETURNS BOOLEAN AS '
DECLARE
userid alias for $1;
piid int4;
remainingqty int4 :=0;
invoiceqty int4 :=0;
rPid record;
rPo record;

BEGIN
-- create new invoice
INSERT INTO purchase_invoice (
pi_num,
pi_supplier_id,
pi_status
)
SELECT
pi_num,
pi_supplier_id,
''OPEN''
FROM temp_pi WHERE temp_pi.user_id=userid;

-- select last saved invoice id --
SELECT MAX(pi_id)  INTO piid FROM purchase_invoice;

--  insert open/new POs  to temporary table	 --
INSERT INTO temp_po_balance (
	po_id,
	po_issue_date,
	part_supp_id,
	po_qty,
	bal_qty,
	status
)
SELECT 
	po_id,
	po_issue_date,
	pod_part_supp_id,
	pod_qty,
	pod_qty,
	''OPEN'' 
FROM purchase_order,purchase_order_detail WHERE po_id=pod_po_id and po_status=''OPEN'' AND po_id NOT IN (SELECT DISTINCT (po_id) FROM temp_po_balance);

-- loop through invoice details --  -- update po_status --
FOR rPid IN 
	SELECT * FROM temp_pid WHERE temp_pid.user_id=userid

	LOOP
		INSERT INTO purchase_invoice_detail (
			pid_pi_id,
			pid_part_supp_id,
			pid_packing_id,
			pid_packing_qty,
			pid_unitprice,
			pid_qty
		) VALUES (
			piid,
			rPid.pid_part_supp_id,
			rPid.pid_packing_id,
			rPid.pid_packing_qty,
			rPid.pid_unitprice,
			rPid.pid_qty			                
		);

		invoiceqty := rPid.pid_qty;

/**************************************
--begin TRANS;
--lock table temp_po_balance in access exclusive mode;

                                             -- check PO balance quantity --
		<<POloop>>
		FOR rPo IN
			SELECT * FROM temp_po_balance WHERE part_supp_id=rPid.pid_part_supp_id AND status!=''CLOSE'' ORDER BY po_issue_date
		LOOP
			if (rPo.po_qty <= invoiceqty) then
				update temp_po_balance set bal_qty = 0, status = ''CLOSE'' WHERE po_id=rPo.po_id AND part_supp_id=rPo.part_supp_id;
				invoiceqty := invoiceqty - rPo.po_qty;
				exit POloop when invoiceqty=0;					
			else 
				remainingqty := rPo.po_qty - invoiceqty;
				UPDATE temp_po_balance SET bal_qty = remainingqty, status=''ACTIVE'' WHERE po_id=rPo.po_id AND part_supp_id=rPo.part_supp_id;
				exit POloop;
			end if;
       	END LOOP;
		--end TRANS;

**************************************************/
---change PO status
UPDATE purchase_order SET po_status=''ACTIVE'' WHERE po_id IN (SELECT po_id FROM temp_po_balance WHERE status!=''OPEN'');
UPDATE purchase_order SET po_status=''CLOSE'' WHERE po_id NOT IN (SELECT po_id FROM temp_po_balance WHERE status=''ACTIVE'' OR status=''OPEN'');

		DELETE FROM temp_po_balance WHERE po_id NOT IN (SELECT po_id FROM temp_po_balance WHERE status=''ACTIVE'' OR status=''OPEN'');

       END LOOP;

RETURN true;

END;'
LANGUAGE 'plpgsql';

the part enclosed in a block comment is where I'm planning to lock the table temp_po_balance...

as you can see I'm changing the status of the Po and quantity as well..