Thread: transaction safety
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
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
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
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
**********************************************************************
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
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
**********************************************************************
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
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
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
**********************************************************************
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
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
**********************************************************************
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
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
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.
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
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
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
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
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
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