Re: transactional swap of tables

From: Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: transactional swap of tables
Date: 2013-07-17 12:12:24
Message-ID: CAHjZ2x4sWjDwi_vj-d8jb=p1eqSoumHpNGX6XDtHne8k6SskBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have done the following test pn v9.2.4 with two concurrent sessions:

-- session no.1
tmp1=# create table t1 ( t text );
CREATE TABLE
Tempo: 37,351 ms
tmp1=# create table t2 ( t text );
CREATE TABLE
Tempo: 33,363 ms
tmp1=# create or replace function f1( out tx text )
tmp1-# language plpgsql
tmp1-# stable as $l0$
tmp1$# begin
tmp1$# select t into tx from t1 limit 1;
tmp1$# end;
tmp1$# $l0$;
CREATE FUNCTION
Tempo: 14,148 ms
tmp1=# create or replace function f2( out tx text )
tmp1-# language plpgsql
tmp1-# volatile as $l0$
tmp1$# begin
tmp1$# select t into tx from t1 limit 1;
tmp1$# end;
tmp1$# $l0$;
CREATE FUNCTION
Tempo: 12,712 ms
tmp1=# insert into t1 values ( 'ciao' );
INSERT 0 1
Tempo: 14,777 ms
tmp1=# insert into t2 values ( 'hello' );
INSERT 0 1
Tempo: 9,032 ms
tmp1=# select * from f1();
tx
------
ciao
(1 riga)

Tempo: 0,600 ms
tmp1=# select * from f2();
tx
------
ciao
(1 riga)

Tempo: 0,549 ms

-- session no.2
tmp1=# begin;
BEGIN
Tempo: 0,287 ms
tmp1=# alter table t1 rename to t3;
ALTER TABLE
Tempo: 1,023 ms
tmp1=# alter table t2 rename to t1;
ALTER TABLE
Tempo: 0,533 ms
tmp1=# alter table t3 rename to t2;
ALTER TABLE
Tempo: 0,449 ms

-- back to session no.1
tmp1=# select * from f1();
-- not ending, possibly due to table lock

-- back to session no.2
tmp1=# commit;
COMMIT
Tempo: 10,986 ms

-- back to session no.1
tx
-------
hello
(1 riga)

Tempo: 39946,137 ms

The result changes slightly if I query the function f1() just after ALTERing t1.
In this case from f1() I get NULL as result after COMMIT on session no.2.
A subsequent query returns 'hello'.
While from f2() I get always the right result.
This makes me think that the volatility specification in the function
declaration obviously changes something in the caching of the catalog
queries.
The NULL remains a mystere for me.
Any hint? Any way to avoid such a behaviour?

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Devrim GÜNDÜZ 2013-07-17 12:28:58 Re: Build RPM from Postgres Source
Previous Message Stephen Brearley 2013-07-17 10:41:45 FW: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure