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?
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 |