英文:
Is there anything one cannot do (and then rollback safely) in transaction?
问题
以下是要翻译的内容:
在事务中有什么事情是不能做的(然后可以安全地回滚)?
我的事务是对生产数据库进行重大破坏性更改的简单测试。包含在事务中的SQL代码包括以下语句。在测试数据库中,这些SQL代码可以正常运行并能够回滚,处理少量数据,每个事务耗时0.5秒。我想要在生产环境中测试代码的性能,删除并写入事务中的数GB数据,估计需要30分钟。
ALTER TABLE
UPDATE ...
DROP VIEW ... CASCADE
CREATE OR REPLACE VIEW ...
CREATE MATERIALIZED VIEW ...
CREATE UNIQUE INDEX ... ON <the materialized view just created above> ...
-- 如果需要,我可以省略“CONCURRENTLY”:
REFRESH MATERIALIZED VIEW CONCURRENTLY <the materialized view just created above>
CREATE OR REPLACE VIEW ...
相关链接:
- https://stackoverflow.com/q/13979456/967621
- https://stackoverflow.com/q/1043598/967621
- https://stackoverflow.com/q/6274925/967621
英文:
Is there anything one cannot do (and then rollback safely) in transaction?
My transaction is a simple test of a major, disruptive change in a production database. The SQL code wrapped in a transaction consists of statements such as below. The SQL code works in the test database, with a tiny amount of data, where it runs and can be rolled back fine, taking up 0.5 seconds per transaction. I want to measure the performance of the code in production, with several GB of data deleted and then written in the transaction, estimated to take up to 30 min.
ALTER TABLE
UPDATE ...
DROP VIEW ... CASCADE
CREATE OR REPLACE VIEW ...
CREATE MATERIALIZED VIEW ...
CREATE UNIQUE INDEX ... ON <the materialized view just created above> ...
-- I can omit "CONCURRENTLY" if needed:
REFRESH MATERIALIZED VIEW CONCURRENTLY <the materialized view just created above>
CREATE OR REPLACE VIEW ...
Related:
答案1
得分: 3
再三考虑
如果有可能,应该在生产数据库的副本上运行侵入性测试,不要冒着破坏生产数据库的风险。即使一切顺利,您的操作也不会被忽视。您可能会减慢或阻止并发活动。
在没有并发会话的情况下,在同一数据库集群内创建副本的最快方法是:
CREATE DATABASE mydb_test TEMPLATE mydb;
参考:
这样您就可以避免直接摩擦、阻塞和破坏。但是,即使在同一服务器上进行测试,您仍然会竞争资源。在不同的服务器上进行测试也可以避免这种情况...
关于 ROLLBACK
在Postgres中,绝大多数的DML和DDL命令都是完全事务性的(可以在事务块内运行),它们的效果可以被回滚。只要确保不要意外地 COMMIT
。
值得注意的例外情况包括:(这些列表都不是详尽无遗的)
不在事务中运行的事务
有一些命令本身就不能在事务上下文中运行。因此,它们也无法被回滚。如果您试图在事务中包含其中任何一个,Postgres会在它们运行之前引发异常。因此,除了已经完成的工作之外,什么都不会丢失 - 并且会被回滚。
-
CREATE INDEX CONCURRENTLY
,DROP INDEX CONCURRENTLY
,REINDEX CONCURRENTLY
-
REINDEX
- 包括分区索引或表时 -
BEGIN
,START TRANSACTION
,COMMIT
,ROLLBACK
- 显然。这些命令启动和结束事务,不能在其中运行。如果在错误的地方调用,只会引发一个WARNING
。 -
CALL
- 如果被调用的过程包括事务控制命令(参见上文)。 -
DO
- 如果代码块包括事务控制命令(参见上文)。
无法回滚的事务
这些才是您真正需要注意的事项,例如:
-
序列操作函数
setval()
和nextval()
的效果,隐式地被serial
和IDENTITY
列使用。因此,请预期序列号中的间隙。参见: -
写入日志文件的任何内容
-
返回给客户端的任何内容
-
dblink 调用的效果(或类似的)。参见:
特殊情况
-
TRUNCATE
- 手册:TRUNCATE
不是MVCC安全的。在截断后,如果并发事务使用截断之前的快照,表将会为空。参见 Section 13.6 以获取更多详情。TRUNCATE
在表中的数据方面是事务安全的:如果周围的事务不提交,截断将会被安全地回滚。
相关链接:
英文:
Think twice
If at all possible, run invasive tests on a copy of the productive database, don't take the risk of breaking your productive DB. Even if all goes well, your presence won't go unnoticed. You may slow down or block concurrent activity.
The fastest way to create a copy within the same DB cluster - while there are no concurrent sessions:
CREATE DATABASE mydb_test TEMPLATE mydb;
See:
You avoid direct friction, blockage and breakage. But you still compete for resources when testing on the same server. Testing on a different server would avoid that, too ...
About ROLLBACK
In Postgres, the vast majority of all DML and DDL commands are fully transactional (can run inside a transaction block) and their effects can be rolled back. Just make sure you don't COMMIT
by accident.
Notable exceptions include: (none of these lists are comprehensive)
Things that don't even run in a transaction
There are some commands that cannot run in a transaction context to begin with. So they also cannot be rolled back. If you tried to include any of those in your transaction, Postgres would raise an exception before they even run. So nothing lost except the work that had been done already - and is rolled back.
-
CREATE INDEX CONCURRENTLY
,DROP INDEX CONCURRENTLY
,REINDEX CONCURRENTLY
-
REINDEX
- when including a partitioned index or table -
BEGIN
,START TRANSACTION
,COMMIT
,ROLLBACK
- obviously. Those start & end transactions and cannot run inside one. Only raise aWARNING
if called in the wrong place. -
CALL
- if the called procedure includes transaction control commands (see above). -
DO
- if the code block includes transaction control commands (see above).
Things that are not (or cannot be) rolled back
Those are the ones you really need to be aware of. Like:
-
Effects of the sequence manipulation functions
setval()
andnextval()
, implicitly used byserial
andIDENTITY
columns. So expect gaps in serial numbers. See: -
Anything written to log files
-
Anything returned to clients
-
Effects of dblink calls (or similar). See:
Corner cases
-
LISTEN
, UNLISTEN, andNOTIFY
are transactional, but there are some edge cases. -
TRUNCATE
- the manual:
> TRUNCATE
is not MVCC-safe. After truncation, the table will appear
> empty to concurrent transactions, if they are using a snapshot taken
> before the truncation occurred. See Section 13.6 for more
> details.
>
> TRUNCATE
is transaction-safe with respect to the data in the tables:
> the truncation will be safely rolled back if the surrounding
> transaction does not commit.
Related:
答案2
得分: 0
以下是翻译好的部分:
有一些异常的命令(例如评论中提到的“create database”),但在正常运行中没有什么。
然而,您可能要小心锁定。如果您要删除大量行或更改外键等操作,那么其他事务可能会在您的测试之后受阻,等待看它是否提交或回滚。
英文:
There are a few outlying commands (e.g. "create database" as F.H. says in the comments) but nothing in the "normal run" of things.
However - you probably want to be a little careful of locks. If you are deleting a lot of rows or altering foreign-keys and the like then other transactions can get held up behind your test, waiting to see if it commits or rolls back.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论