Re: transactional swap of tables

Поиск
Список
Период
Сортировка
От Vincenzo Romano
Тема Re: transactional swap of tables
Дата
Msg-id CAHjZ2x4sWjDwi_vj-d8jb=p1eqSoumHpNGX6XDtHne8k6SskBw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: transactional swap of tables  (Merlin Moncure <mmoncure@gmail.com>)
Список 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?


В списке pgsql-general по дате отправления:

Предыдущее
От: David Welton
Дата:
Сообщение: Re: V8.4 TOAST table problem
Следующее
От: Devrim GÜNDÜZ
Дата:
Сообщение: Re: Build RPM from Postgres Source