Thread: Strange situation with two tables.
/*
I have a bit theoretical and/or practical problem that I am trying (and I would like) to resolve with PL/PGSQL
language.
In summary:
===========
Sum(Table A) generates Table B.
Primary key on Table B must be saved to Table A.
A lot of different people (client connections) is working and doing entries against Table A.
*/
CREATE TABLE debits
( sequen numeric(4,0), -- really it is a serial type
customer_number numeric(4,0),
money numeric(4,2),
bill_number numeric(4,0),
PRIMARY KEY (sequen, customer_number)
) WITHOUT OIDS;
-- Debits for customer 1
INSERT INTO debits VALUES (1, 1, 12, 1); -- already billed . Bill Number = 1
INSERT INTO debits VALUES (2, 1, -2, 1); -- already billed. Bill Number = 1
INSERT INTO debits VALUES (3, 1, 22, null);
INSERT INTO debits VALUES (4, 1, 8, null);
INSERT INTO debits VALUES (5, 1, 1, null);
INSERT INTO debits VALUES (6, 1, 2, null);
-- Debits for customer 2
INSERT INTO debits VALUES (7, 2, 8, null);
INSERT INTO debits VALUES (8, 2, 2, null);
CREATE TABLE bills
(bill_number numeric(4,0),
customer_name varchar(40),
money numeric(6,2),
PRIMARY KEY (bill_number)
) WITHOUT OIDS;
INSERT INTO bills VALUES (1, 'John Doe', 10); -- Here are debits: 1,1 and 2,1
/*
I need to create an AGGREGATE SELECT <SUM(debits.money)> to insert only ONE bill by all debits rows. Only a bill by
customer.Ok, that's already done. After (or at the same time), I need to UPDATE debits.bill_number COLUMN to reflect
thebills.bill_number on debits.bill_number.
And here is the problem because more client connections are inserting debits (and perhaps to the same customer_number I
amworking with), but I _ONLY_ MUST to update the debits.bill_number to that debits.ROWS I have treated in the bill.ROW
creationstep.
I have tried with:
TEMPORARY TABLES.
But perhaps same client connection runs the procedure twice.
In this situation I have an error:
'ERROR: Relation 'xxxxxxxxxxxx' already exists'.
If I DROP temporary table previous to the CREATE TEMPORARY TABLE sentence into the pl/pgsql function,
then I obtain an error:
'ERROR: pg_aclcheck: class "pg_temp_5470_7" not found'
SELECT xx GROUP BY xx FOR UPDATE OF debits.bill_number
This does not work at all.....
I am off ideas now. I don't know how to continue without a really "row by row work and check". :-\
Of course, previous is a simple example with less tables and columns than the real situation...
Somebody have any idea how to solve this situation ?
Thanks in advance...
*/
On Wed, 2002-09-11 at 14:34, Terry Yapt wrote: ... > And here is the problem because more client connections are inserting debits (and perhaps to the same customer_number I am working with), but I _ONLY_ MUST to update the debits.bill_number to that debits.ROWS I have treated in the bill.ROW creation step. I think you are worrying about a non-problem.. If you are working in a PL/PGSQL function. anything you do will automatically happen inside one transaction. Therefore, nothing that happens to the database after that transaction starts will be visible to your function. So you don't have to worry about subsequent changes. If you are doing several inserts before doing your bill update, and don't want a later bill to include the ones you are dealing with, enclose all your SQL commands in BEGIN ... END to turn them all into one transaction. (Don't confuse PL/PGSQL's BEGIN...END with SQL's BEGIN...END) None of the results of the transaction will be visible to other connections until the transaction is committed. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me." Galatians 2:20
If I understand you and
USER1 starts this pl/pgsql body function
BEGIN
SELECT count(*) AS c1 FROM debits;
........
........ -- Here USER2 INSERTS a new row in debits table. --
........
SELECT count(*) AS c2 FROM debits
IF c1 = c2 THEN
RETURN true
ELSE
RETURN false
END IF;
END;
This functions returns TRUE ? It is right ?
Thanks...
Oliver Elphick wrote:
>
> On Wed, 2002-09-11 at 14:34, Terry Yapt wrote:
> ...
> > And here is the problem because more client connections are inserting
> debits (and perhaps to the same customer_number I am working with), but
> I _ONLY_ MUST to update the debits.bill_number to that debits.ROWS I
> have treated in the bill.ROW creation step.
>
> I think you are worrying about a non-problem.. If you are working in a
> PL/PGSQL function. anything you do will automatically happen inside one
> transaction. Therefore, nothing that happens to the database after that
> transaction starts will be visible to your function. So you don't have
> to worry about subsequent changes.
>
> If you are doing several inserts before doing your bill update, and
> don't want a later bill to include the ones you are dealing with,
> enclose all your SQL commands in BEGIN ... END to turn them all into one
> transaction. (Don't confuse PL/PGSQL's BEGIN...END with SQL's
> BEGIN...END) None of the results of the transaction will be visible to
> other connections until the transaction is committed.
>
> --
> Oliver Elphick Oliver.Elphick@lfix.co.uk
> Isle of Wight, UK
> http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
> ========================================
> "I am crucified with Christ; nevertheless I live; yet
> not I, but Christ liveth in me; and the life which I
> now live in the flesh I live by the faith of the Son
> of God, who loved me, and gave himself for me."
> Galatians 2:20
On Wed, 2002-09-11 at 15:20, Henshall, Stuart - WCP wrote: ... > > BEGIN...END) None of the results of the transaction will be > > visible to > > other connections until the transaction is committed. > > > > > I thought that was only the case when the transaction level was SERIALIZABLE > I believe pg starts in READ COMMITED transaction level. > http://www.postgresql.org/idocs/index.php?transaction-iso.html To be sure, I just tested by explicitly setting the isolation level to read committed and to serializable. As I expected, an uncompleted transaction is indeed invisible to other connections, whichever isolation level is chosen. In terms of the documentation page you referred to, reading uncommitted rows is a "dirty read". This is only possible at isolation level 'read uncommitted', which PostgreSQL does not support. You may be referring to phantom reads; these could be avoided, if they are a problem, by explicitly setting the transaction isolation level to serializable. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me." Galatians 2:20
On Wed, 2002-09-11 at 15:41, Terry Yapt wrote: > If I understand you and > > USER1 starts this pl/pgsql body function > BEGIN > SELECT count(*) AS c1 FROM debits; > ........ > ........ -- Here USER2 INSERTS a new row in debits table. -- > ........ > SELECT count(*) AS c2 FROM debits > IF c1 = c2 THEN > RETURN true > ELSE > RETURN false > END IF; > END; > > This functions returns TRUE ? It is right ? If USER2's transaction has been committed, if the isolation level is 'read committed' the function will return false but if the isolation level is 'serializable' it will return true. If USER2's transaction is uncommitted, the function will return true whatever the isolation level. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me." Galatians 2:20
Ok, I must to set isolation level to 'serializable' at the function begin, and set isolation level to 'normal mode' at theend. How can I do it ? Thanks.. -----Original Message----- From: Oliver Elphick <olly@lfix.co.uk> To: Terry Yapt <yapt@technovell.com> Date: 11 Sep 2002 16:09:07 +0100 Subject: Re: [NOVICE] Strange situation with two tables. On Wed, 2002-09-11 at 15:41, Terry Yapt wrote: > If I understand you and > > USER1 starts this pl/pgsql body function > BEGIN > SELECT count(*) AS c1 FROM debits; > ........ > ........ -- Here USER2 INSERTS a new row in debits table. -- > ........ > SELECT count(*) AS c2 FROM debits > IF c1 = c2 THEN > RETURN true > ELSE > RETURN false > END IF; > END; > > This functions returns TRUE ? It is right ? If USER2's transaction has been committed, if the isolation level is 'read committed' the function will return false but if the isolation level is 'serializable' it will return true. If USER2's transaction is uncommitted, the function will return true whatever the isolation level. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me." Galatians 2:20
On Wed, 2002-09-11 at 18:20, Terry Yapt wrote:
> Ok, I must to set isolation level to 'serializable' at the function begin, and set isolation level to 'normal mode'
atthe end.
>
> How can I do it ?
SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20