Thread: Strange situation with two tables.

Strange situation with two tables.

From
Terry Yapt
Date:
/*
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...

*/

Re: Strange situation with two tables.

From
Oliver Elphick
Date:
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


Re: Strange situation with two tables.

From
Terry Yapt
Date:
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

Re: Strange situation with two tables.

From
Oliver Elphick
Date:
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


Re: Strange situation with two tables.

From
Oliver Elphick
Date:
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


Re: Strange situation with two tables.

From
Terry Yapt
Date:
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





Re: Strange situation with two tables.

From
Oliver Elphick
Date:
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