环境描述：Oracle业务迁移至PG出现问题
原理：
The AccessShareLock lock is a Table lock. This list shows the commands that acquire AccessShareLock and what locks conflicts with it.
问题原因：
存储过程中使用了ddl，机制不同：Oracle对于DDL是默认会触发commit。而PG不会，索引放在存储过程中的DDL会一直持有对象锁，知道出现显示commit或者存储过程执行完成。
锁冲突列表：
Commit机制
Oracle机制：
PostgreSQL
$ psql mydb
mydb=# DROP TABLE IF EXISTS foo;
NOTICE: table "foo" does not exist
DROP TABLE
mydb=# BEGIN;
BEGIN
mydb=# CREATE TABLE foo (bar int);
CREATE TABLE
mydb=# INSERT INTO foo VALUES (1);
INSERT 0 1
mydb=# ROLLBACK;
ROLLBACK
mydb=# SELECT * FROM foo;
ERROR: relation "foo" does not exist
mydb=# SELECT version();
version
If you're using MySQL instead, DDL and some similar changes cannot be reversed in such a fashion. If you're using MyISAM, there's no rollback available at all. With InnoDB, the server has an implicit commit that occurs even if the normal auto-commit behavior is turned off. It's notable that the scope of when you can encounter the implicit commit behavior has even been expanding during the 5.0 releases; you can draw your own conclusions about what that says about the commit reliability of the earlier versions. Here is how that same procedure plays out with a recent MySQL version:
mysql> drop table if exists foo;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> create table foo (bar int) type=InnoDB;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> insert into foo values (1);
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from foo;
+------+
| bar |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select version();
+--------------------------+
| version() |
+--------------------------+
| 5.0.32-Debian_7etch1-log |
+--------------------------+
1 row in set (0.00 sec)
You should also be aware that because of the way they've been implemented, rollbacks in MySQL execute very slowly compared to the original insertion.
With 5.5 (and autocommit turned off) this is no longer the case:
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table foo;
Query OK, 0 rows affected (0.02 sec)
mysql> create table foo (bar int) engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into foo values (1);
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from foo;
Empty set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.8 |
+-----------+
1 row in set (0.05 sec)
SQL Server supports transactional DDL in some cases. If you are running SQL Server in read-committed isolation mode (pessimistic), which is the default mode, you are able to use transactional DDL SQL Server supports transactional DDL. If you're running SQL Server 2005 or greater and running in a snapshot isolation mode (optimistic) there're restrictions to what DDL is supported in an explicit transaction SQL Server snapshot isolation transactional DDL constraints.
"SQL Server does not support versioning of metadata. For this reason, there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation. The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement. These statements are permitted when you are using snapshot isolation within implicit transactions. An implicit transaction, by definition, is a single statement that makes it possible to enforce the semantics of snapshot isolation, even with DDL statements. Violations of this principle can cause error 3961: "Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.""
出处：Transactional DDL in PostgreSQL: A Competitive Analysis - PostgreSQL wiki