using DROP in a transaction
Hi,
We have a large set of simple queries, that can run in both PostgreSQL and
BDE (Corel Paradox backend).
We want to be able to re-create some half-product tables (cache, sortof),
while they are (possibly) being accessed by other users. Otherwise there
would be a severe performance penalty. We were thinking about re-creating
them in a transaction. Concurrent transactions may occur.
But it doesn´t seem to work out.
I tested it this way (in PostgreSQL 8.2.6 and in 8.1.10):
in TTY1, run:
create table test (id int4 primary key);
insert into test (id) values (1);
then in TTY2, run
begin;
drop table test;
create table test (id int4 primary key);
insert into test (id) values (2);
then, in TTY1:
select * from test;
Actually, i thought of a longer test, but it stops here - there is no
response, no prompt anaymore, until i cancel the query.
Is this correct behaviour of PostgreSQL? (i think not..)
The easiest solution would be to just empty the tables instead of dropping
them i guess, but this made me wonder about transactions. I've read the
chapter on Transaction
Isolation<http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html>,
but it doesn't explain the behaviour of DROP. Anything else i need to know?
(OMG transactions are not pure magic ;P )
WBL
er.. never mind the BDE part, it has nothing to do with this.
On Feb 8, 2008 12:53 AM, Willy-Bas Loos <willybas@gmail.com> wrote:
Show quoted text
Hi,
We have a large set of simple queries, that can run in both PostgreSQL and
BDE (Corel Paradox backend).
We want to be able to re-create some half-product tables (cache, sortof),
while they are (possibly) being accessed by other users. Otherwise there
would be a severe performance penalty. We were thinking about re-creating
them in a transaction. Concurrent transactions may occur.
But it doesn´t seem to work out.I tested it this way (in PostgreSQL 8.2.6 and in 8.1.10):
in TTY1, run:
create table test (id int4 primary key);
insert into test (id) values (1);then in TTY2, run
begin;
drop table test;
create table test (id int4 primary key);
insert into test (id) values (2);then, in TTY1:
select * from test;Actually, i thought of a longer test, but it stops here - there is no
response, no prompt anaymore, until i cancel the query.
Is this correct behaviour of PostgreSQL? (i think not..)The easiest solution would be to just empty the tables instead of dropping
them i guess, but this made me wonder about transactions. I've read the
chapter on Transaction Isolation<http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html>,
but it doesn't explain the behaviour of DROP. Anything else i need to know?
(OMG transactions are not pure magic ;P )WBL
"Willy-Bas Loos" <willybas@gmail.com> writes:
then in TTY2, run
begin;
drop table test;
create table test (id int4 primary key);
insert into test (id) values (2);
then, in TTY1:
select * from test;
Actually, i thought of a longer test, but it stops here - there is no
response, no prompt anaymore, until i cancel the query.
DROP TABLE takes an exclusive lock. The SELECT is waiting for that
lock to be released.
You could shorten the length of the exclusive lock by creating and
filling the replacement table under a new name, then DROP, then
rename the new table into place and immediately commit. But you
aren't going to be able to eliminate it entirely.
regards, tom lane
ok, that explains.
so i go on with my test, which still doesn't turn out as i expected
(PostgreSQL 8.1.10).
why??
==in TTY1== (start.sql)
create table test (id int4 primary key); --expect sucess
insert into test (id) values (1); --expect success
==in TTY2== (tr1a.sql)
begin; --expect success
drop table test; --expect success
create table test (id int4 primary key); --expect success
insert into test (id) values (2); --expect success
==in TTY1==
SELECT * FROM test; --1. expect no answer now, there's an exclusive lock on
"test" from TTY2.
==in TTY2==
SELECT * FROM test; --2. expect 1 record, value 2.
==in TTY3== (tr2a.sql)
begin; --3. expect success
drop table test; --4. expect no answer now, there's an exclusive lock on
"test" from TTY2.
create table test (id int4 primary key); --5.
insert into test (id) values (3); --6.
==in TTY1==
--7. expect no answer still, there's an exclusive lock on "test" from TTY2
==in TTY2==
SELECT * FROM test; --8. expect 1 record, value 2 (the exclusive lock is
from here)
==in TTY2==(tr1b.sql)
insert into test (id) values (4); --9. expect success
commit;--10. expect success. transaction 1 (here in TTY2) will be committed,
the SELECT (in TTY1) and transaction 2 (in TTY3) can continue.
==in TTY1==
--11. expect result at last, value 2 only. (concurrent transaction 2 (in
TTY3) completes after this, and will delete values 2 and 4 (added after
select was issued) upon commit)
--11. true result: ERROR: relation <large nr> deleted while still in use
-- remark: I guess transaction2 was faster? This isn't right. the select
statement should only see transactions that were committed before it was
issued.
-- wait, that can't be true, transaction 2 (in TTY3) was rolled back!
==in TTY2==
SELECT * FROM test; --12. expect no answer now, there's an exlusive lock on
"test" from TTY3, so let it wait
--12. true result: 2 records, values 2 and 4.
-- remark: transaction 2 was rolled back, so there is now only the result of
transaction1 (in TTY2), which is, in itself, correct.
==in TTY3==
--message: ERROR: tuple concurrently updated
-- remark: ?? Huh?
-- ==END OF MY EXERCISE DUE TO ERROR CONDITION== --
--actions as planned below--
SELECT * FROM test; --13. expect 1 record, value 3 (the exclusive lock is
from here)
==in TTY3==(tr2b.sql)
insert into test (id) values (5); --14. expect success
commit;--15. expect success
--transaction 2 has been committed,there are no more locks, 2 values remain:
3 and 5.
==TTY1==
SELECT * FROM test; --16. expect 3 and 5
==TTY2==
SELECT * FROM test; --17. expect 3 and 5
==TTY3==
SELECT * FROM test; --18. expect 3 and 5
this is really bugging me.
am i doing something stupid?
On Fri, Feb 8, 2008 at 2:18 PM, Willy-Bas Loos <willybas@gmail.com> wrote:
Show quoted text
ok, that explains.
so i go on with my test, which still doesn't turn out as i expected
(PostgreSQL 8.1.10).
why??==in TTY1== (start.sql)
create table test (id int4 primary key); --expect sucess
insert into test (id) values (1); --expect success==in TTY2== (tr1a.sql)
begin; --expect success
drop table test; --expect success
create table test (id int4 primary key); --expect success
insert into test (id) values (2); --expect success==in TTY1==
SELECT * FROM test; --1. expect no answer now, there's an exclusive lock
on "test" from TTY2.==in TTY2==
SELECT * FROM test; --2. expect 1 record, value 2.==in TTY3== (tr2a.sql)
begin; --3. expect success
drop table test; --4. expect no answer now, there's an exclusive lock on
"test" from TTY2.
create table test (id int4 primary key); --5.
insert into test (id) values (3); --6.==in TTY1==
--7. expect no answer still, there's an exclusive lock on "test" from TTY2==in TTY2==
SELECT * FROM test; --8. expect 1 record, value 2 (the exclusive lock is
from here)==in TTY2==(tr1b.sql)
insert into test (id) values (4); --9. expect success
commit;--10. expect success. transaction 1 (here in TTY2) will be
committed, the SELECT (in TTY1) and transaction 2 (in TTY3) can continue.==in TTY1==
--11. expect result at last, value 2 only. (concurrent transaction 2 (in
TTY3) completes after this, and will delete values 2 and 4 (added after
select was issued) upon commit)
--11. true result: ERROR: relation <large nr> deleted while still in use
-- remark: I guess transaction2 was faster? This isn't right. the select
statement should only see transactions that were committed before it was
issued.
-- wait, that can't be true, transaction 2 (in TTY3) was rolled back!==in TTY2==
SELECT * FROM test; --12. expect no answer now, there's an exlusive lock
on "test" from TTY3, so let it wait
--12. true result: 2 records, values 2 and 4.
-- remark: transaction 2 was rolled back, so there is now only the result
of transaction1 (in TTY2), which is, in itself, correct.==in TTY3==
--message: ERROR: tuple concurrently updated
-- remark: ?? Huh?-- ==END OF MY EXERCISE DUE TO ERROR CONDITION== --
--actions as planned below--SELECT * FROM test; --13. expect 1 record, value 3 (the exclusive lock is
from here)==in TTY3==(tr2b.sql)
insert into test (id) values (5); --14. expect success
commit;--15. expect success--transaction 2 has been committed,there are no more locks, 2 values
remain: 3 and 5.
==TTY1==
SELECT * FROM test; --16. expect 3 and 5
==TTY2==
SELECT * FROM test; --17. expect 3 and 5
==TTY3==
SELECT * FROM test; --18. expect 3 and 5
==in TTY1==
--11. expect result at last, value 2 only. (concurrent transaction
2 (in TTY3) completes after this, and will delete values 2 and 4
(added after select was issued) upon commit)
--11. true result: ERROR: relation <large nr> deleted while still in
use
The table 'test' which tty1 was looking at (which was dropped in tty2)
doesn't exist any more.
Postgres doesn't look at the name, it looks at the id that is created
behind the scenes.
So in tty1, the id is 'x'.
Then you recreate the table in tty2 which gives it id 'y'.
So tty1 looking at id 'x' doesn't exist any more.
--
Postgresql & php tutorials
http://www.designmagick.com/
ah, of course.
the exclusive lock was preventing tty1 to read "test", and when the lock was
gone, so was the table.
I get it. Thanks a lot.
But, what about the "ERROR: tuple concurrently updated" ? (in TTY3)
What should have happened, i guess, is "ERROR: table "test" does not exist,
upon " drop table test; --4. ..."
Which tuple was concurrently updated? A pg_catalog entry that administers
the table?
WBL
On Fri, Feb 15, 2008 at 5:10 AM, Chris <dmagick@gmail.com> wrote:
Show quoted text
==in TTY1==
--11. expect result at last, value 2 only. (concurrent transaction
2 (in TTY3) completes after this, and will delete values 2 and 4
(added after select was issued) upon commit)
--11. true result: ERROR: relation <large nr> deleted while still in
useThe table 'test' which tty1 was looking at (which was dropped in tty2)
doesn't exist any more.Postgres doesn't look at the name, it looks at the id that is created
behind the scenes.So in tty1, the id is 'x'.
Then you recreate the table in tty2 which gives it id 'y'.So tty1 looking at id 'x' doesn't exist any more.
--
Postgresql & php tutorials
http://www.designmagick.com/
Willy-Bas Loos wrote:
ah, of course.
the exclusive lock was preventing tty1 to read "test", and when the lock
was gone, so was the table.
I get it. Thanks a lot.But, what about the "ERROR: tuple concurrently updated" ? (in TTY3)
Same thing - tty1 was locking that entry and when it was released, tty3
tried to update it. The error message isn't great but both tty1 (which
did drop the table) and tty3 which tried to drop the table are doing the
same thing.
What should have happened, i guess, is "ERROR: table "test" does not
exist, upon " drop table test; --4. ..."
Which tuple was concurrently updated? A pg_catalog entry that
administers the table?
No idea - I guess something like that :)
--
Postgresql & php tutorials
http://www.designmagick.com/