Re: transaction safety

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(at)wanadoo(dot)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(at)tin(dot)it
loc. Meleto Valdarno Mail on GSM: (keep it short!)
52020 Cavriglia (AR) faermini(at)sms(dot)tin(dot)it

On Tue, Feb 13, 2001 at 12:25:59PM +0100, fabrizio(dot)ermini(at)sysdat(dot)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 (
nmero serial primary key,
fecha timestamp default now(),
procedencia int2 references procedencia,
lnea int2 references lnea,
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 (lnea);
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,lnea,empresa,detalle) values
(1,1,1,'Example');

referencial integrity to "procedencia", "lnea" 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(dot)ermini(at)sysdat(dot)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(at)wanadoo(dot)es> writes:
> procedencia int2 references procedencia,
> lnea int2 references lnea,
> 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(at)wanadoo(dot)es> writes:
> > procedencia int2 references procedencia,
> > lnea int2 references lnea,
> > 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,
va text
);

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

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

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

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

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

-----------------------------
create table aviso (
nmero serial primary key,
fecha timestamp default now(),
procedencia int2 references procedencia,
lnea int2 references lnea,
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 (lnea);
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", "lnea" and
"empresa" to satisfy referencial integrity and make experiment proposed in
the other message with two psql:

# insert into aviso(procedencia,lnea,empresa,detalle) values
(1,1,1,'Example');

Greets.

David

On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> DaVinci <bombadil(at)wanadoo(dot)es> writes:
> > procedencia int2 references procedencia,
> > lnea int2 references lnea,
> > 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(at)wanadoo(dot)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(at)Yahoo(dot)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(at)Yahoo(dot)com #

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