Thread: transaction safety

transaction safety

From
DaVinci
Date:
 Hi all.

 I want to create a new tuple of main info and detail tuples (in
 different tables) that are joined by a key field. Key field is created by
 generator automatically and I need that number in order to assign to detail
 tuples. How can I to get that number in a safe way?.

 Thanks for ideas.

                                                             David

Re: transaction safety

From
"Oliver Elphick"
Date:
DaVinci wrote:
  > Hi all.
  >
  > I want to create a new tuple of main info and detail tuples (in
  > different tables) that are joined by a key field. Key field is created by
  > generator automatically and I need that number in order to assign to detail
  > tuples. How can I to get that number in a safe way?.

A successful INSERT returns the oid of the row just created; so get the
new value with a query like this:

  SELECT key_field FROM table WHERE oid = new_oid_value;

In libpq programming, use PQoidValue() to get the oid.  Other interfcaes
should have equivalent methods.



--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "When I consider thy heavens, the work of thy fingers,
      the moon and the stars, which thou hast ordained; What
      is man, that thou art mindful of him? and the son of
      man, that thou visitest him? For thou hast made him a
      little lower than the angels, and hast crowned him
      with glory and honour."            Psalms 8:3-5



Re: transaction safety

From
DaVinci
Date:
On Mon, Feb 12, 2001 at 11:08:55AM +0000, Oliver Elphick wrote:
> DaVinci wrote:
>   > Hi all.
>   >
>   > I want to create a new tuple of main info and detail tuples (in
>   > different tables) that are joined by a key field. Key field is created by
>   > generator automatically and I need that number in order to assign to detail
>   > tuples. How can I to get that number in a safe way?.
>
> A successful INSERT returns the oid of the row just created; so get the
> new value with a query like this:
>
>   SELECT key_field FROM table WHERE oid = new_oid_value;

 I have a new question for this idea (thanks). When Database is big, do i
 need index for oid field to speed select?.

 Greets.

                                                             David

RE: transaction safety

From
Michael Ansley
Date:

Hi,

The number returned by the sequence for the serial ID is retained within the session, and so it can be returned by calling currval, e.g.:

CREATE TABLE person (
        id serial primary key,
        name varchar not null,
        dob timestamp not null
);
CREATE TABLE address (
        id serial primary key,
        id_person int not null references person(id),
        address varchar not null
);

INSERT INTO person (name, dob) VALUES ('Peter', '03/09/1945');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '44 Willowdown Road');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '23 Second Ave. (Second Home)');
INSERT INTO person (name, dob) VALUES ('Jane', '06/12/1958');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '16 Parsons Crescent');

Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-)

Cheers...

MikeA


-----Original Message-----
From: DaVinci [mailto:bombadil@wanadoo.es]
Sent: 12 February 2001 11:34
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety

On Mon, Feb 12, 2001 at 11:08:55AM +0000, Oliver Elphick wrote:
> DaVinci wrote:
>   > Hi all.
>   >
>   > I want to create a new tuple of main info and detail tuples (in
>   > different tables) that are joined by a key field. Key field is created by
>   > generator automatically and I need that number in order to assign to detail
>   > tuples. How can I to get that number in a safe way?.
>
> A successful INSERT returns the oid of the row just created; so get the
> new value with a query like this:
>
>   SELECT key_field FROM table WHERE oid = new_oid_value;

 I have a new question for this idea (thanks). When Database is big, do i
 need index for oid field to speed select?.

 Greets.

                                                                                                                        David

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

Re: transaction safety

From
DaVinci
Date:
On Mon, Feb 12, 2001 at 01:08:01PM -0000, Michael Ansley wrote:

> The number returned by the sequence for the serial ID is retained within the
> session, and so it can be returned by calling currval, e.g.:

...[a detailed example]...

> Typically, the insert for a person, and for all the associated addresses
> would be done in the same transaction so that if the insert for one of the
> addresses failed, then the whole lot would role back (perhaps a bit extreme,
> but I think that's what you asked for ;-)

 That sounds good ;)

 I thought it is possible to have different transactions opened and insert
 data in same table from them. It seems my idea was fault, doesn't it?.

 In sumary: locks with inserts are for table and not for tuple. If this is
 not true, tell me details, please :)

 Thanks for all.

                                                                 David

RE: transaction safety

From
Michael Ansley
Date:

There should be no locks on the table for insert.  The sequence number is retained by the session, not locked on the database.  What this means is that somebody else may start their insert after you, and then you roll back, leaving a gap in the sequences.  However, gaps in the sequences are not (or should not be) important.  You can test this out using a few sessions of psql.

You need to view this with about 110 chars across, in fixed-width font (tab-width of 4 chars):

a$ psql test                                            |       b$ psql test
a=> begin;      b=> begin;                                      |      
a=> insert into person (name, dob)                      |      
a-> values ('Peter', '03/09/1945');                     |      
INSERT XXXX                                                     |      
a=> select currval('person_id_seq');            |      
1                                                               |      
                                                                |       b=> insert into person (name, dob)
                                                                |       b-> values ('Marge', '05/03/1967');
                                                                |       INSERT XXXX
                                                                |       b=> select currval('person_id_seq');
                                                                |       2
a=> select currval('person_id_seq');            |      
1                                                               |      
a=> insert into address (id_person, address)    |      
a-> values (currval('person_id_seq'), 'XXXX');  |      
INSERT XXXY                                                     |      
                                                                |       b=> insert into address (id_person, address)
                                                                |       b-> values (currval('person_id_seq'), 'XXXX');
                                                                |       INSERT XXXY
                                                                |       b=> select currval('person_id_seq');
                                                                |       2
a=> rollback;                                           |      
ROLLBACK                                                        |      
                                                                |       b=> select currval('person_id_seq');
                                                                |       2
                                                                |       b=> end;
                                                                |       COMMIT
                                                                v
                                                            time

Now, although the left side has rolled back, the sequence number 1 is lost.  However, this is not a problem, as the primary key requires uniqueness, but not continuity, and it means that your inserts do not block the table.  Inserts can happen as fast as you can push data into the table.

Read up on sequences, because serial fields are based on sequences, thus avoiding the locking issue.

Hope this helps...

MikeA

-----Original Message-----
From: DaVinci [mailto:bombadil@wanadoo.es]
Sent: 12 February 2001 13:43
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety

On Mon, Feb 12, 2001 at 01:08:01PM -0000, Michael Ansley wrote:
 
> The number returned by the sequence for the serial ID is retained within the
> session, and so it can be returned by calling currval, e.g.:

...[a detailed example]...

> Typically, the insert for a person, and for all the associated addresses
> would be done in the same transaction so that if the insert for one of the
> addresses failed, then the whole lot would role back (perhaps a bit extreme,
> but I think that's what you asked for ;-)

 That sounds good ;)

 I thought it is possible to have different transactions opened and insert
 data in same table from them. It seems my idea was fault, doesn't it?.

 In sumary: locks with inserts are for table and not for tuple. If this is
 not true, tell me details, please :)

 Thanks for all.

                                                                                                                                David

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

Re: transaction safety

From
Tom Lane
Date:
DaVinci <bombadil@wanadoo.es> writes:
> On Mon, Feb 12, 2001 at 01:08:01PM -0000, Michael Ansley wrote:
>> Typically, the insert for a person, and for all the associated addresses
>> would be done in the same transaction so that if the insert for one of the
>> addresses failed, then the whole lot would role back (perhaps a bit extreme,
>> but I think that's what you asked for ;-)

>  I thought it is possible to have different transactions opened and insert
>  data in same table from them. It seems my idea was fault, doesn't it?.
>  In sumary: locks with inserts are for table and not for tuple. If this is
>  not true, tell me details, please :)

It's not true.  How did you arrive at that conclusion from what Mike
said?

            regards, tom lane

Re: transaction safety

From
DaVinci
Date:
On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote:
> DaVinci <bombadil@wanadoo.es> writes:
> > On Mon, Feb 12, 2001 at 01:08:01PM -0000, Michael Ansley wrote:
> >> Typically, the insert for a person, and for all the associated addresses
> >> would be done in the same transaction so that if the insert for one of the
> >> addresses failed, then the whole lot would role back (perhaps a bit extreme,
> >> but I think that's what you asked for ;-)
>
> >  I thought it is possible to have different transactions opened and insert
> >  data in same table from them. It seems my idea was fault, doesn't it?.
> >  In sumary: locks with inserts are for table and not for tuple. If this is
> >  not true, tell me details, please :)
>
> It's not true.  How did you arrive at that conclusion from what Mike
> said?

 I'll try to explain. Mike said: "in a transaction make an insert and then a
 read in serial current value".

 If in gap between those operations occurs another insert from different
 transaction, then reading serial is not safe.

 In order to understand this well I have made some basic experiments,
 freezing a transaction with an insert and making other transaction with an
 insert to the same table. Second gets frozen until first commit or cancel.
 That is reason of my last message: "locks with inserts are for table and
 not for tuple". Perhaps I didn't explain myself very well or there is some
 detail about locks that I don't understand at all.

 Thanks all for your time.

                                 David




RE: transaction safety

From
Michael Ansley
Date:

Hi,

Well, the number is 'locked', because once it's given to you, that's it, it's gone from the 'list of available numbers' (i.e.: the sequence).  However, between the insert, and the read of the ID, if another transaction performs an insert, it does NOT affect the ID that the first transaction reads (i.e.: your ID read in the first transaction IS definitely still safe, it will still read the correct one).  AND, the first insert does NOT block the second insert.  The second insert could complete and commit before the first one.

Does this explain better?

MikeA


-----Original Message-----
From: DaVinci [mailto:bombadil@wanadoo.es]
Sent: 13 February 2001 08:08
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety

On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote:
> DaVinci <bombadil@wanadoo.es> writes:
> > On Mon, Feb 12, 2001 at 01:08:01PM -0000, Michael Ansley wrote:
> >> Typically, the insert for a person, and for all the associated addresses
> >> would be done in the same transaction so that if the insert for one of the
> >> addresses failed, then the whole lot would role back (perhaps a bit extreme,
> >> but I think that's what you asked for ;-)
>
> >  I thought it is possible to have different transactions opened and insert
> >  data in same table from them. It seems my idea was fault, doesn't it?.
> >  In sumary: locks with inserts are for table and not for tuple. If this is
> >  not true, tell me details, please :)
>
> It's not true.  How did you arrive at that conclusion from what Mike
> said?

 I'll try to explain. Mike said: "in a transaction make an insert and then a
 read in serial current value".
 
 If in gap between those operations occurs another insert from different
 transaction, then reading serial is not safe.

 In order to understand this well I have made some basic experiments,
 freezing a transaction with an insert and making other transaction with an
 insert to the same table. Second gets frozen until first commit or cancel.
 That is reason of my last message: "locks with inserts are for table and
 not for tuple". Perhaps I didn't explain myself very well or there is some
 detail about locks that I don't understand at all.

 Thanks all for your time.

                                                                David


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

Re: transaction safety

From
DaVinci
Date:
On Tue, Feb 13, 2001 at 09:56:18AM -0000, Michael Ansley wrote:
> Hi,
>
> Well, the number is 'locked', because once it's given to you, that's it,
> it's gone from the 'list of available numbers' (i.e.: the sequence).
> However, between the insert, and the read of the ID, if another transaction
> performs an insert, it does NOT affect the ID that the first transaction
> reads (i.e.: your ID read in the first transaction IS definitely still safe,
> it will still read the correct one).

 I understand this.

> AND, the first insert does NOT block
> the second insert.  The second insert could complete and commit before the
> first one.

 But I don't know how to reproduce this part.

 If I have two different sessions of psql connected to same database:

     psql-1# begin;
    psql-2# begin;
    psql-1# insert into foo ...;
    psql-2# insert into foo ...;    <- ���Frozen!!!
    psql-1# commit;                    <- psql-2 unfrozen

 Why I get this?.

> Does this explain better?

 Very well, thanks ;)

                                                     David

RE: transaction safety

From
Michael Ansley
Date:

OK, someone want to answer this?  I have always been under the impression that Postgres would not block under these circumstances, however, this is clearly blocking, for no apparently good reason.

I have just run a test on my own server, and this blocking does not happen.  Both sessions run independently until each has committed, then displaying information from the other insert, but definitely not blocking.  It works exactly as I would have expected.

Anybody???

MikeA

-----Original Message-----
From: DaVinci [mailto:bombadil@wanadoo.es]
Sent: 13 February 2001 10:42
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety

On Tue, Feb 13, 2001 at 09:56:18AM -0000, Michael Ansley wrote:
> Hi,
>
> Well, the number is 'locked', because once it's given to you, that's it,
> it's gone from the 'list of available numbers' (i.e.: the sequence).
> However, between the insert, and the read of the ID, if another transaction
> performs an insert, it does NOT affect the ID that the first transaction
> reads (i.e.: your ID read in the first transaction IS definitely still safe,
> it will still read the correct one).

 I understand this.

> AND, the first insert does NOT block
> the second insert.  The second insert could complete and commit before the
> first one.

 But I don't know how to reproduce this part.

 If I have two different sessions of psql connected to same database:

        psql-1# begin;
        psql-2# begin;
        psql-1# insert into foo ...;
        psql-2# insert into foo ...;    <- ¡¡¡Frozen!!!
        psql-1# commit;                                 <- psql-2 unfrozen

 Why I get this?.

> Does this explain better?

 Very well, thanks ;)

                                                                                                        David

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

RE: transaction safety

From
fabrizio.ermini@sysdat.it
Date:
On 13 Feb 2001, at 10:58, Michael Ansley wrote:

> OK, someone want to answer this?  I have always been under the impression
> that Postgres would not block under these circumstances, however, this is
> clearly blocking, for no apparently good reason.
>
> I have just run a test on my own server, and this blocking does not happen.
> Both sessions run independently until each has committed, then displaying
> information from the other insert, but definitely not blocking.  It works
> exactly as I would have expected.
>

This thing has ignited my curiosity, too. I've tested it on a server
and I've obtained your same results, no blocking, as should be.

Don't understand why David experiences a lock. Maybe it has
"SET TRANSACTION SERIALIZABLE" on? Could that be of some
influence? Or maybe it's something that's in those "..." in his
examples, but it seems strange.

just my 0.02 Euro

Ciao!


/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Fabrizio Ermini               Alternate E-mail:
C.so Umberto, 7               faermini@tin.it
loc. Meleto Valdarno          Mail on GSM: (keep it short!)
52020 Cavriglia (AR)          faermini@sms.tin.it

Re: transaction safety

From
DaVinci
Date:
On Tue, Feb 13, 2001 at 12:25:59PM +0100, fabrizio.ermini@sysdat.it wrote:
> On 13 Feb 2001, at 10:58, Michael Ansley wrote:
>
> > OK, someone want to answer this?  I have always been under the impression
> > that Postgres would not block under these circumstances, however, this is
> > clearly blocking, for no apparently good reason.
> >
> > I have just run a test on my own server, and this blocking does not happen.
> > Both sessions run independently until each has committed, then displaying
> > information from the other insert, but definitely not blocking.  It works
> > exactly as I would have expected.
> >
>
> This thing has ignited my curiosity, too. I've tested it on a server
> and I've obtained your same results, no blocking, as should be.

 8| That sounds terrible for me. I don know what can be the problem. Any
 volunteer for using my script to create the Database and experimenting with
 insert?... :)

 Here is (as preambule) the table in which I make insertions:

-------------------------------------------------------
create table aviso (
    n�mero        serial primary key,
    fecha        timestamp default now(),
    procedencia    int2 references procedencia,
    l�nea        int2 references l�nea,
    empresa        int2 references empresa,
    urgente        bool    default 'f',
    externo        bool    default 'f',
    aceptado    bool,
    tmr            bool    default 'f',
    detalle        text
);
create index avi_fecha_ndx on aviso (fecha);
create index avi_procedencia_ndx on aviso (procedencia);
create index avi_linea_ndx on aviso (l�nea);
create index avi_empresa_ndx on aviso (empresa);
create index avi_urgente_ndx on aviso (urgente);
create index avi_aceptado_ndx on aviso (aceptado);
create index avi_tmr_ndx on aviso (tmr);
create index avi_externo_ndx on aviso (externo);
----------------------------------------------------------

 I make following insert from psql:

 # insert into aviso(procedencia,l�nea,empresa,detalle) values
    (1,1,1,'Example');

 referencial integrity to "procedencia", "l�nea" and "empresa" works fine.
 Those tables have convenient values.

 If you have any idea about this problem I'll thank your comments.

 Greets.

                                                         David

How to limit the size of pg_log ??

From
Jean-Arthur Silve
Date:
Hello !

Well, I have a process which continuously access to the DB (with SELECT,
DELETE and UPDATE)

My problem is that in one or two months pg_log file size is 1Go !

is there a solution ???

thank you


At 13:47 13/02/01 +0100, DaVinci wrote:
>On Tue, Feb 13, 2001 at 12:25:59PM +0100, fabrizio.ermini@sysdat.it wrote:
> > On 13 Feb 2001, at 10:58, Michael Ansley wrote:
> >
> > > OK, someone want to answer this?  I have always been under the impression
> > > that Postgres would not block under these circumstances, however, this is
> > > clearly blocking, for no apparently good reason.
> > >
> > > I have just run a test on my own server, and this blocking does not
> happen.
> > > Both sessions run independently until each has committed, then displaying
> > > information from the other insert, but definitely not blocking.  It works
> > > exactly as I would have expected.
> > >
> >
> > This thing has ignited my curiosity, too. I've tested it on a server
> > and I've obtained your same results, no blocking, as should be.
>
>  8| That sounds terrible for me. I don know what can be the problem. Any
>  volunteer for using my script to create the Database and experimenting with
>  insert?... :)
>
>  Here is (as preambule) the table in which I make insertions:
>
>-------------------------------------------------------
>create table aviso (
>         número          serial primary key,
>         fecha           timestamp default now(),
>         procedencia     int2 references procedencia,
>         línea           int2 references línea,
>         empresa         int2 references empresa,
>         urgente         bool    default 'f',
>         externo         bool    default 'f',
>         aceptado        bool,
>         tmr                     bool    default 'f',
>         detalle         text
>);
>create index avi_fecha_ndx on aviso (fecha);
>create index avi_procedencia_ndx on aviso (procedencia);
>create index avi_linea_ndx on aviso (línea);
>create index avi_empresa_ndx on aviso (empresa);
>create index avi_urgente_ndx on aviso (urgente);
>create index avi_aceptado_ndx on aviso (aceptado);
>create index avi_tmr_ndx on aviso (tmr);
>create index avi_externo_ndx on aviso (externo);
>----------------------------------------------------------
>
>  I make following insert from psql:
>
>  # insert into aviso(procedencia,línea,empresa,detalle) values
>         (1,1,1,'Example');
>
>  referencial integrity to "procedencia", "línea" and "empresa" works fine.
>  Those tables have convenient values.
>
>  If you have any idea about this problem I'll thank your comments.
>
>  Greets.
>
>
>David

----------------------------------------------------------------
Le simple fait de passer par la fenetre ne suffit pas a la transformer en porte.


Re: transaction safety

From
Tom Lane
Date:
DaVinci <bombadil@wanadoo.es> writes:
>     procedencia    int2 references procedencia,
>     l�nea        int2 references l�nea,
>     empresa        int2 references empresa,

Depending on the data values you are working with, it could be that the
foreign key references cause interlocks --- ie, one transaction has to
wait to see if the other commits to know if it has a valid foreign key.

However, you still have not shown us a complete example that would allow
anyone else to reproduce your results.

            regards, tom lane

Re: transaction safety

From
DaVinci
Date:
On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> DaVinci <bombadil@wanadoo.es> writes:
> >     procedencia    int2 references procedencia,
> >     l�nea        int2 references l�nea,
> >     empresa        int2 references empresa,
>
> Depending on the data values you are working with, it could be that the
> foreign key references cause interlocks --- ie, one transaction has to
> wait to see if the other commits to know if it has a valid foreign key.
>
> However, you still have not shown us a complete example that would allow
> anyone else to reproduce your results.

 Good. Here is a script for psql that creates database:

---------------------------------------------------------------

------------------------------------------------------------------
-- David Espada 2000.
--
------------------------------------------------------------------

create database example;

\connect example

---------------------------
create table empresa (
    cod        serial primary key,
    descripcion    text
);

---------------------------
create table procedencia (
    cod        serial primary key,
    descripcion    text
);

---------------------------
create table calle (
    cod        serial primary key,
    nombre        text,
    v�a            text
);

---------------------------
create table provincia (
    cod        serial primary key,
    nombre        text
);

---------------------------
create table localidad (
    cod        serial primary key,
    nombre        text
);

---------------------------
create table l�nea (
    cod        serial primary key,
    nombre        text
);

---------------------------
create table forma_pago (
    cod        serial primary key,
    descripcion    text
);


---------------------------------------------------------------------------------

-----------------------------
create table aviso (
    n�mero        serial primary key,
    fecha        timestamp default now(),
    procedencia    int2 references procedencia,
    l�nea        int2 references l�nea,
    empresa        int2 references empresa,
    urgente        bool    default 'f',
    externo        bool    default 'f',
    aceptado    bool,            -- El valor nulo implica 'pendiente'
    tmr        bool    default 'f',    -- Trabajo Mal Realizado
    detalle        text
);
create index avi_fecha_ndx on aviso (fecha);
create index avi_procedencia_ndx on aviso (procedencia);
create index avi_linea_ndx on aviso (l�nea);
create index avi_empresa_ndx on aviso (empresa);
create index avi_urgente_ndx on aviso (urgente);
create index avi_aceptado_ndx on aviso (aceptado);
create index avi_tmr_ndx on aviso (tmr);
create index avi_externo_ndx on aviso (externo);

--------------------------------------------------------------------------

 With this, you only have to insert values in "procedencia", "l�nea" and
 "empresa" to satisfy referencial integrity and make experiment proposed in
 the other message with two psql:

 # insert into aviso(procedencia,l�nea,empresa,detalle) values
   (1,1,1,'Example');

 Greets.

                                                         David

Re: transaction safety

From
DaVinci
Date:
On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> DaVinci <bombadil@wanadoo.es> writes:
> >     procedencia    int2 references procedencia,
> >     l�nea        int2 references l�nea,
> >     empresa        int2 references empresa,
>
> Depending on the data values you are working with, it could be that the
> foreign key references cause interlocks --- ie, one transaction has to
> wait to see if the other commits to know if it has a valid foreign key.

 Problem is with referential integrity like you say. But I don't understand
 well reason. Interlocks should appear only when values of referenced tables
 are changed, added or removed, shouldn't they?. But that is not the case. I
 only insert in table "aviso".

 Is this normal?

 Greets.

                                                             David


Re: transaction safety

From
Jan Wieck
Date:
DaVinci wrote:
> On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> > DaVinci <bombadil@wanadoo.es> writes:
> > >     procedencia    int2 references procedencia,
> > >     línea          int2 references línea,
> > >     empresa        int2 references empresa,
> >
> > Depending on the data values you are working with, it could be that the
> > foreign key references cause interlocks --- ie, one transaction has to
> > wait to see if the other commits to know if it has a valid foreign key.
>
>  Problem is with referential integrity like you say. But I don't understand
>  well reason. Interlocks should appear only when values of referenced tables
>  are changed, added or removed, shouldn't they?. But that is not the case. I
>  only insert in table "aviso".
>
>  Is this normal?

    Maybe.

    The problem is, that a referential integrity constraint needs
    to ensure that from the moment  the  constraint  got  checked
    until  your  INSERT got made persistent (COMMIT), nobody else
    has a chance to kick out the referenced key.

    Due to the lack of the ability to put a shared row lock  with
    a  SELECT,  we currently use FOR UPDATE, placing an exclusive
    lock onto the referenced key.

    In your case it might help to make the constraints  INITIALLY
    DEFERRED.  That  causes  that  the  checks  are delayed until
    COMMIT, so it shortens the time the lock is present.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Re: transaction safety

From
DaVinci
Date:
On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote:
> DaVinci wrote:
> >  Problem is with referential integrity like you say. But I don't understand
> >  well reason. Interlocks should appear only when values of referenced tables
> >  are changed, added or removed, shouldn't they?. But that is not the case. I
> >  only insert in table "aviso".
> >
> >  Is this normal?
>
>     Maybe.
>
>     The problem is, that a referential integrity constraint needs
>     to ensure that from the moment  the  constraint  got  checked
>     until  your  INSERT got made persistent (COMMIT), nobody else
>     has a chance to kick out the referenced key.
>
>     Due to the lack of the ability to put a shared row lock  with
>     a  SELECT,  we currently use FOR UPDATE, placing an exclusive
>     lock onto the referenced key.

 I understand this but, why then other changes on "aviso" get locked?. My
 first impression is that only referenced keys should be, but not a table
 that references them.

 If this works so for now, is any plan to change in future?.

>     In your case it might help to make the constraints  INITIALLY
>     DEFERRED.  That  causes  that  the  checks  are delayed until
>     COMMIT, so it shortens the time the lock is present.

 Thanks!. That works fine.

                                            David

Re: transaction safety

From
Jan Wieck
Date:
DaVinci wrote:
> On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote:
> >     The problem is, that a referential integrity constraint needs
> >     to ensure that from the moment  the  constraint  got  checked
> >     until  your  INSERT got made persistent (COMMIT), nobody else
> >     has a chance to kick out the referenced key.
> >
> >     Due to the lack of the ability to put a shared row lock  with
> >     a  SELECT,  we currently use FOR UPDATE, placing an exclusive
> >     lock onto the referenced key.
>
>  I understand this but, why then other changes on "aviso" get locked?. My
>  first impression is that only referenced keys should be, but not a table
>  that references them.

    You're right, at the time two FK rows referencing the same PK
    get inserted, there is no need to block one  of  them.  Thus,
    PostgreSQL *shouldn't* block.

    But at the time beeing, the foreign key triggers issue a

        SELECT oid FROM <pktable> WHERE <key> = <referenced-value>
        FOR UPDATE;

    Since  both  INSERT  operations  on  the  FK table do it, the
    second one is blocked until COMMIT of  the  first,  releasing
    the lock.

    What we need is something like

        SELECT ...
        WITH LOCK;

    applying  a  shared (read) lock of the PK row. In this state,
    UPDATE/DELETE to the PK row in question  or  SELECT  ...  FOR
    UPDATE  of  that  will  block,  but more SELECT ... WITH LOCK
    would be allowed and pass through without blocking.

    I don't really want that language construct, since  it's  not
    SQL  standard.   Maybe  it's  possible  to  achieve  the same
    behaviour with dirty reads or the  like.  However  it's  done
    finally, it should behave like the above.

>
>  If this works so for now, is any plan to change in future?.
>
> >     In your case it might help to make the constraints  INITIALLY
> >     DEFERRED.  That  causes  that  the  checks  are delayed until
> >     COMMIT, so it shortens the time the lock is present.
>
>  Thanks!. That works fine.

    You're welcome.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com