transaction safety

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