OSDN > 開發者 > fujii_masao > 工作室 > pg_dbms_stats > 提交

pg_dbms_stats
Fork

Fork origin: pgdbmsstats / pg_dbms_stats

R/O
HTTP
SSH
HTTPS

提交

Commit MetaInfo

修訂	0fea6a73a629a56fbef2e3fbf8950d6aedd544e5 (tree)
時間	2014-08-08 09:48:52
作者	Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
Commiter	Kyotaro Horiguchi

Log Message

Raise granularity of locks.

Almost all sql functions of pg_dbms_stats takes "share update
exclusive" locks on internal tables before they does their work. As
the result, it serializes dbms_stats operations even if they're on
different tables. This commit replaces such giant locks with the more
narrowed locks so that operations on different tables basically can
run in parallel. As the exchange for the concurrency, multiple (first
time) statistics lock operations on the same table at the same time
may result in failure of either of them. But it does no matter under
usual usage.

Change Summary

modified: expected/ut-9.1.out (diff)
modified: expected/ut-9.2.out (diff)
modified: expected/ut-9.3.out (diff)
modified: expected/ut-common.out (diff)
modified: ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.1.sql (diff)
modified: ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.2.sql (diff)
modified: ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.3.sql (diff)
modified: ext_scripts/pg_dbms_stats--1.3.3-9.1.sql (diff)
modified: ext_scripts/pg_dbms_stats--1.3.3-9.2.sql (diff)
modified: ext_scripts/pg_dbms_stats--1.3.3-9.3.sql (diff)

差異

--- a/expected/ut-9.1.out

+++ b/expected/ut-9.1.out

		@@ -1245,7 +1245,7 @@ SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
1245	1245	DELETE FROM dbms_stats.relation_stats_backup;
1246	1246	SELECT dbms_stats.backup(NULL, 's0.st0'::regclass, NULL);
1247	1247	ERROR: null value in column "id" violates not-null constraint
1248		-CONTEXT: SQL function "backup" statement 1
	1248	+CONTEXT: SQL function "backup" statement 2
1249	1249	SELECT count(*) FROM dbms_stats.relation_stats_backup;
1250	1250	count
1251	1251	-------

		@@ -1530,7 +1530,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1530	1530	AND dbms_stats.is_target_relkind(c.relkind)
1531	1531	ORDER BY c.oid
1532	1532	) cn"
1533		-PL/pgSQL function "backup_schema_stats" line 16 at PERFORM
	1533	+PL/pgSQL function "backup_schema_stats" line 17 at PERFORM
1534	1534	NOTICE: arguments are 9, s0.st0_idx, <NULL>
1535	1535	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1536	1536	FROM (SELECT c.oid

		@@ -1541,7 +1541,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1541	1541	AND dbms_stats.is_target_relkind(c.relkind)
1542	1542	ORDER BY c.oid
1543	1543	) cn"
1544		-PL/pgSQL function "backup_schema_stats" line 16 at PERFORM
	1544	+PL/pgSQL function "backup_schema_stats" line 17 at PERFORM
1545	1545	NOTICE: arguments are 9, s0.st1, <NULL>
1546	1546	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1547	1547	FROM (SELECT c.oid

		@@ -1552,7 +1552,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1552	1552	AND dbms_stats.is_target_relkind(c.relkind)
1553	1553	ORDER BY c.oid
1554	1554	) cn"
1555		-PL/pgSQL function "backup_schema_stats" line 16 at PERFORM
	1555	+PL/pgSQL function "backup_schema_stats" line 17 at PERFORM
1556	1556	NOTICE: arguments are 9, s0.st1_idx, <NULL>
1557	1557	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1558	1558	FROM (SELECT c.oid

		@@ -1563,7 +1563,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1563	1563	AND dbms_stats.is_target_relkind(c.relkind)
1564	1564	ORDER BY c.oid
1565	1565	) cn"
1566		-PL/pgSQL function "backup_schema_stats" line 16 at PERFORM
	1566	+PL/pgSQL function "backup_schema_stats" line 17 at PERFORM
1567	1567	NOTICE: arguments are 9, s0.st2, <NULL>
1568	1568	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1569	1569	FROM (SELECT c.oid

		@@ -1574,7 +1574,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1574	1574	AND dbms_stats.is_target_relkind(c.relkind)
1575	1575	ORDER BY c.oid
1576	1576	) cn"
1577		-PL/pgSQL function "backup_schema_stats" line 16 at PERFORM
	1577	+PL/pgSQL function "backup_schema_stats" line 17 at PERFORM
1578	1578	NOTICE: arguments are 9, s0.st2_idx, <NULL>
1579	1579	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1580	1580	FROM (SELECT c.oid

		@@ -1585,7 +1585,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1585	1585	AND dbms_stats.is_target_relkind(c.relkind)
1586	1586	ORDER BY c.oid
1587	1587	) cn"
1588		-PL/pgSQL function "backup_schema_stats" line 16 at PERFORM
	1588	+PL/pgSQL function "backup_schema_stats" line 17 at PERFORM
1589	1589	backup_schema_stats
1590	1590	---------------------
1591	1591	9

		@@ -1832,17 +1832,16 @@ SELECT dbms_stats.restore(2, 's0.st0', NULL);
1832	1832	(1 row)
1833	1833
1834	1834	SELECT * FROM internal_locks;
1835		- relation \| mode
1836		------------------------------------+--------------------------
1837		- dbms_stats.backup_history \| AccessShareLock
	1835	+ relation \| mode
	1836	+-----------------------------------+------------------
	1837	+ dbms_stats.backup_history \| RowShareLock
1838	1838	dbms_stats.column_stats_backup \| AccessShareLock
1839	1839	dbms_stats._column_stats_locked \| RowExclusiveLock
1840		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
1841	1840	dbms_stats.relation_stats_backup \| AccessShareLock
	1841	+ dbms_stats.relation_stats_backup \| RowShareLock
1842	1842	dbms_stats._relation_stats_locked \| RowExclusiveLock
1843	1843	dbms_stats._relation_stats_locked \| RowShareLock
1844		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
1845		-(8 rows)
	1844	+(7 rows)
1846	1845
1847	1846	COMMIT;
1848	1847	SELECT relid::regclass FROM dbms_stats.relation_stats_locked

		@@ -2812,16 +2811,16 @@ SELECT dbms_stats.restore_stats(2);
2812	2811	(13 rows)
2813	2812
2814	2813	SELECT * FROM internal_locks;
2815		- relation \| mode
2816		------------------------------------+--------------------------
	2814	+ relation \| mode
	2815	+-----------------------------------+------------------
2817	2816	dbms_stats.backup_history \| AccessShareLock
2818	2817	dbms_stats.column_stats_backup \| AccessShareLock
2819	2818	dbms_stats._column_stats_locked \| RowExclusiveLock
2820		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
2821	2819	dbms_stats.relation_stats_backup \| AccessShareLock
	2820	+ dbms_stats.relation_stats_backup \| RowShareLock
	2821	+ dbms_stats._relation_stats_locked \| ExclusiveLock
2822	2822	dbms_stats._relation_stats_locked \| RowExclusiveLock
2823	2823	dbms_stats._relation_stats_locked \| RowShareLock
2824		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
2825	2824	(8 rows)
2826	2825
2827	2826	COMMIT;

		@@ -3265,16 +3264,14 @@ SELECT * FROM columns_locked_v c;
3265	3264	(4 rows)
3266	3265
3267	3266	SELECT * FROM internal_locks;
3268		- relation \| mode
3269		------------------------------------+--------------------------
	3267	+ relation \| mode
	3268	+-----------------------------------+------------------
3270	3269	dbms_stats._column_stats_locked \| AccessShareLock
3271	3270	dbms_stats._column_stats_locked \| RowExclusiveLock
3272		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
3273	3271	dbms_stats._relation_stats_locked \| AccessShareLock
3274	3272	dbms_stats._relation_stats_locked \| RowExclusiveLock
3275	3273	dbms_stats._relation_stats_locked \| RowShareLock
3276		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
3277		-(7 rows)
	3274	+(5 rows)
3278	3275
3279	3276	COMMIT;
3280	3277	-- No.11-2-2

		@@ -4651,14 +4648,13 @@ SELECT dbms_stats.unlock();
4651	4648	(14 rows)
4652	4649
4653	4650	SELECT * FROM internal_locks;
4654		- relation \| mode
4655		------------------------------------+--------------------------
	4651	+ relation \| mode
	4652	+-----------------------------------+------------------
4656	4653	dbms_stats._column_stats_locked \| RowExclusiveLock
4657		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
4658	4654	dbms_stats._relation_stats_locked \| AccessShareLock
4659	4655	dbms_stats._relation_stats_locked \| RowExclusiveLock
4660		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
4661		-(5 rows)
	4656	+ dbms_stats._relation_stats_locked \| RowShareLock
	4657	+(4 rows)
4662	4658
4663	4659	COMMIT;
4664	4660	SELECT count(*) FROM dbms_stats._relation_stats_locked;

		@@ -4930,14 +4926,13 @@ SELECT dbms_stats.unlock_database_stats();
4930	4926	(14 rows)
4931	4927
4932	4928	SELECT * FROM internal_locks;
4933		- relation \| mode
4934		------------------------------------+--------------------------
	4929	+ relation \| mode
	4930	+-----------------------------------+------------------
4935	4931	dbms_stats._column_stats_locked \| RowExclusiveLock
4936		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
4937	4932	dbms_stats._relation_stats_locked \| AccessShareLock
	4933	+ dbms_stats._relation_stats_locked \| ExclusiveLock
4938	4934	dbms_stats._relation_stats_locked \| RowExclusiveLock
4939		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
4940		-(5 rows)
	4935	+(4 rows)
4941	4936
4942	4937	COMMIT;
4943	4938	SELECT count(*) FROM dbms_stats._relation_stats_locked;

		@@ -5668,14 +5663,12 @@ SELECT dbms_stats.unlock_schema_stats('s0');
5668	5663	(6 rows)
5669	5664
5670	5665	SELECT * FROM internal_locks;
5671		- relation \| mode
5672		------------------------------------+--------------------------
	5666	+ relation \| mode
	5667	+-----------------------------------+------------------
5673	5668	dbms_stats._column_stats_locked \| RowExclusiveLock
5674		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
5675		- dbms_stats._relation_stats_locked \| AccessShareLock
5676	5669	dbms_stats._relation_stats_locked \| RowExclusiveLock
5677		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
5678		-(5 rows)
	5670	+ dbms_stats._relation_stats_locked \| RowShareLock
	5671	+(3 rows)
5679	5672
5680	5673	COMMIT;
5681	5674	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -6423,13 +6416,11 @@ SELECT dbms_stats.unlock_table_stats('s0.st0');
6423	6416	(1 row)
6424	6417
6425	6418	SELECT * FROM internal_locks;
6426		- relation \| mode
6427		------------------------------------+--------------------------
	6419	+ relation \| mode
	6420	+-----------------------------------+------------------
6428	6421	dbms_stats._column_stats_locked \| RowExclusiveLock
6429		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
6430	6422	dbms_stats._relation_stats_locked \| RowExclusiveLock
6431		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
6432		-(4 rows)
	6423	+(2 rows)
6433	6424
6434	6425	COMMIT;
6435	6426	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -6886,7 +6877,7 @@ SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
6886	6877
6887	6878	SELECT dbms_stats.unlock_table_stats('s00', 's0');
6888	6879	ERROR: schema "s00" does not exist
6889		-CONTEXT: SQL function "unlock_table_stats" statement 3
	6880	+CONTEXT: SQL function "unlock_table_stats" statement 1
6890	6881	SELECT relid::regclass FROM dbms_stats._relation_stats_locked
6891	6882	GROUP BY relid
6892	6883	ORDER BY relid;

		@@ -7184,13 +7175,11 @@ SELECT dbms_stats.unlock_table_stats('s0', 'st0');
7184	7175	(1 row)
7185	7176
7186	7177	SELECT * FROM internal_locks;
7187		- relation \| mode
7188		------------------------------------+--------------------------
	7178	+ relation \| mode
	7179	+-----------------------------------+------------------
7189	7180	dbms_stats._column_stats_locked \| RowExclusiveLock
7190		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
7191	7181	dbms_stats._relation_stats_locked \| RowExclusiveLock
7192		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
7193		-(4 rows)
	7182	+(2 rows)
7194	7183
7195	7184	COMMIT;
7196	7185	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -8183,12 +8172,11 @@ SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
8183	8172	(1 row)
8184	8173
8185	8174	SELECT * FROM internal_locks;
8186		- relation \| mode
8187		------------------------------------+--------------------------
	8175	+ relation \| mode
	8176	+-----------------------------------+------------------
8188	8177	dbms_stats._column_stats_locked \| RowExclusiveLock
8189		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
8190		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
8191		-(3 rows)
	8178	+ dbms_stats._relation_stats_locked \| RowShareLock
	8179	+(2 rows)
8192	8180
8193	8181	COMMIT;
8194	8182	SELECT starelid, attname, stainherit FROM columns_locked_v c;

		@@ -9067,12 +9055,11 @@ SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
9067	9055	(1 row)
9068	9056
9069	9057	SELECT * FROM internal_locks;
9070		- relation \| mode
9071		------------------------------------+--------------------------
	9058	+ relation \| mode
	9059	+-----------------------------------+------------------
9072	9060	dbms_stats._column_stats_locked \| RowExclusiveLock
9073		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
9074		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
9075		-(3 rows)
	9061	+ dbms_stats._relation_stats_locked \| RowShareLock
	9062	+(2 rows)
9076	9063
9077	9064	COMMIT;
9078	9065	SELECT starelid, attname, stainherit FROM columns_locked_v c;

--- a/expected/ut-9.2.out

+++ b/expected/ut-9.2.out

		@@ -1790,7 +1790,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1790	1790	AND dbms_stats.is_target_relkind(c.relkind)
1791	1791	ORDER BY c.oid
1792	1792	) cn"
1793		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1793	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1794	1794	NOTICE: arguments are 9, s0.st0_idx, <NULL>
1795	1795	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1796	1796	FROM (SELECT c.oid

		@@ -1801,7 +1801,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1801	1801	AND dbms_stats.is_target_relkind(c.relkind)
1802	1802	ORDER BY c.oid
1803	1803	) cn"
1804		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1804	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1805	1805	NOTICE: arguments are 9, s0.st1, <NULL>
1806	1806	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1807	1807	FROM (SELECT c.oid

		@@ -1812,7 +1812,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1812	1812	AND dbms_stats.is_target_relkind(c.relkind)
1813	1813	ORDER BY c.oid
1814	1814	) cn"
1815		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1815	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1816	1816	NOTICE: arguments are 9, s0.st1_idx, <NULL>
1817	1817	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1818	1818	FROM (SELECT c.oid

		@@ -1823,7 +1823,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1823	1823	AND dbms_stats.is_target_relkind(c.relkind)
1824	1824	ORDER BY c.oid
1825	1825	) cn"
1826		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1826	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1827	1827	NOTICE: arguments are 9, s0.st2, <NULL>
1828	1828	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1829	1829	FROM (SELECT c.oid

		@@ -1834,7 +1834,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1834	1834	AND dbms_stats.is_target_relkind(c.relkind)
1835	1835	ORDER BY c.oid
1836	1836	) cn"
1837		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1837	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1838	1838	NOTICE: arguments are 9, s0.st2_idx, <NULL>
1839	1839	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1840	1840	FROM (SELECT c.oid

		@@ -1845,7 +1845,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1845	1845	AND dbms_stats.is_target_relkind(c.relkind)
1846	1846	ORDER BY c.oid
1847	1847	) cn"
1848		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1848	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1849	1849	NOTICE: arguments are 9, s0.sft0, <NULL>
1850	1850	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1851	1851	FROM (SELECT c.oid

		@@ -1856,7 +1856,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1856	1856	AND dbms_stats.is_target_relkind(c.relkind)
1857	1857	ORDER BY c.oid
1858	1858	) cn"
1859		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1859	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1860	1860	backup_schema_stats
1861	1861	---------------------
1862	1862	9

		@@ -2105,17 +2105,16 @@ SELECT dbms_stats.restore(2, 's0.st0', NULL);
2105	2105	(1 row)
2106	2106
2107	2107	SELECT * FROM internal_locks;
2108		- relation \| mode
2109		------------------------------------+--------------------------
2110		- dbms_stats.backup_history \| AccessShareLock
	2108	+ relation \| mode
	2109	+-----------------------------------+------------------
	2110	+ dbms_stats.backup_history \| RowShareLock
2111	2111	dbms_stats.column_stats_backup \| AccessShareLock
2112	2112	dbms_stats._column_stats_locked \| RowExclusiveLock
2113		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
2114	2113	dbms_stats.relation_stats_backup \| AccessShareLock
	2114	+ dbms_stats.relation_stats_backup \| RowShareLock
2115	2115	dbms_stats._relation_stats_locked \| RowExclusiveLock
2116	2116	dbms_stats._relation_stats_locked \| RowShareLock
2117		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
2118		-(8 rows)
	2117	+(7 rows)
2119	2118
2120	2119	COMMIT;
2121	2120	SELECT relid::regclass FROM dbms_stats.relation_stats_locked

		@@ -3114,16 +3113,16 @@ SELECT dbms_stats.restore_stats(2);
3114	3113	(14 rows)
3115	3114
3116	3115	SELECT * FROM internal_locks;
3117		- relation \| mode
3118		------------------------------------+--------------------------
	3116	+ relation \| mode
	3117	+-----------------------------------+------------------
3119	3118	dbms_stats.backup_history \| AccessShareLock
3120	3119	dbms_stats.column_stats_backup \| AccessShareLock
3121	3120	dbms_stats._column_stats_locked \| RowExclusiveLock
3122		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
3123	3121	dbms_stats.relation_stats_backup \| AccessShareLock
	3122	+ dbms_stats.relation_stats_backup \| RowShareLock
	3123	+ dbms_stats._relation_stats_locked \| ExclusiveLock
3124	3124	dbms_stats._relation_stats_locked \| RowExclusiveLock
3125	3125	dbms_stats._relation_stats_locked \| RowShareLock
3126		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
3127	3126	(8 rows)
3128	3127
3129	3128	COMMIT;

		@@ -3586,16 +3585,14 @@ SELECT * FROM columns_locked_v c;
3586	3585	(4 rows)
3587	3586
3588	3587	SELECT * FROM internal_locks;
3589		- relation \| mode
3590		------------------------------------+--------------------------
	3588	+ relation \| mode
	3589	+-----------------------------------+------------------
3591	3590	dbms_stats._column_stats_locked \| AccessShareLock
3592	3591	dbms_stats._column_stats_locked \| RowExclusiveLock
3593		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
3594	3592	dbms_stats._relation_stats_locked \| AccessShareLock
3595	3593	dbms_stats._relation_stats_locked \| RowExclusiveLock
3596	3594	dbms_stats._relation_stats_locked \| RowShareLock
3597		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
3598		-(7 rows)
	3595	+(5 rows)
3599	3596
3600	3597	COMMIT;
3601	3598	-- No.11-2-2

		@@ -5027,14 +5024,13 @@ SELECT dbms_stats.unlock();
5027	5024	(15 rows)
5028	5025
5029	5026	SELECT * FROM internal_locks;
5030		- relation \| mode
5031		------------------------------------+--------------------------
	5027	+ relation \| mode
	5028	+-----------------------------------+------------------
5032	5029	dbms_stats._column_stats_locked \| RowExclusiveLock
5033		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
5034	5030	dbms_stats._relation_stats_locked \| AccessShareLock
5035	5031	dbms_stats._relation_stats_locked \| RowExclusiveLock
5036		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
5037		-(5 rows)
	5032	+ dbms_stats._relation_stats_locked \| RowShareLock
	5033	+(4 rows)
5038	5034
5039	5035	COMMIT;
5040	5036	SELECT count(*) FROM dbms_stats._relation_stats_locked;

		@@ -5312,14 +5308,13 @@ SELECT dbms_stats.unlock_database_stats();
5312	5308	(15 rows)
5313	5309
5314	5310	SELECT * FROM internal_locks;
5315		- relation \| mode
5316		------------------------------------+--------------------------
	5311	+ relation \| mode
	5312	+-----------------------------------+------------------
5317	5313	dbms_stats._column_stats_locked \| RowExclusiveLock
5318		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
5319	5314	dbms_stats._relation_stats_locked \| AccessShareLock
	5315	+ dbms_stats._relation_stats_locked \| ExclusiveLock
5320	5316	dbms_stats._relation_stats_locked \| RowExclusiveLock
5321		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
5322		-(5 rows)
	5317	+(4 rows)
5323	5318
5324	5319	COMMIT;
5325	5320	SELECT count(*) FROM dbms_stats._relation_stats_locked;

		@@ -6080,14 +6075,12 @@ SELECT dbms_stats.unlock_schema_stats('s0');
6080	6075	(7 rows)
6081	6076
6082	6077	SELECT * FROM internal_locks;
6083		- relation \| mode
6084		------------------------------------+--------------------------
	6078	+ relation \| mode
	6079	+-----------------------------------+------------------
6085	6080	dbms_stats._column_stats_locked \| RowExclusiveLock
6086		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
6087		- dbms_stats._relation_stats_locked \| AccessShareLock
6088	6081	dbms_stats._relation_stats_locked \| RowExclusiveLock
6089		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
6090		-(5 rows)
	6082	+ dbms_stats._relation_stats_locked \| RowShareLock
	6083	+(3 rows)
6091	6084
6092	6085	COMMIT;
6093	6086	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -6866,13 +6859,11 @@ SELECT dbms_stats.unlock_table_stats('s0.st0');
6866	6859	(1 row)
6867	6860
6868	6861	SELECT * FROM internal_locks;
6869		- relation \| mode
6870		------------------------------------+--------------------------
	6862	+ relation \| mode
	6863	+-----------------------------------+------------------
6871	6864	dbms_stats._column_stats_locked \| RowExclusiveLock
6872		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
6873	6865	dbms_stats._relation_stats_locked \| RowExclusiveLock
6874		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
6875		-(4 rows)
	6866	+(2 rows)
6876	6867
6877	6868	COMMIT;
6878	6869	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -7347,7 +7338,7 @@ SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
7347	7338
7348	7339	SELECT dbms_stats.unlock_table_stats('s00', 's0');
7349	7340	ERROR: schema "s00" does not exist
7350		-CONTEXT: SQL function "unlock_table_stats" statement 3
	7341	+CONTEXT: SQL function "unlock_table_stats" statement 1
7351	7342	SELECT relid::regclass FROM dbms_stats._relation_stats_locked
7352	7343	GROUP BY relid
7353	7344	ORDER BY relid;

		@@ -7660,13 +7651,11 @@ SELECT dbms_stats.unlock_table_stats('s0', 'st0');
7660	7651	(1 row)
7661	7652
7662	7653	SELECT * FROM internal_locks;
7663		- relation \| mode
7664		------------------------------------+--------------------------
	7654	+ relation \| mode
	7655	+-----------------------------------+------------------
7665	7656	dbms_stats._column_stats_locked \| RowExclusiveLock
7666		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
7667	7657	dbms_stats._relation_stats_locked \| RowExclusiveLock
7668		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
7669		-(4 rows)
	7658	+(2 rows)
7670	7659
7671	7660	COMMIT;
7672	7661	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -8702,12 +8691,11 @@ SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
8702	8691	(1 row)
8703	8692
8704	8693	SELECT * FROM internal_locks;
8705		- relation \| mode
8706		------------------------------------+--------------------------
	8694	+ relation \| mode
	8695	+-----------------------------------+------------------
8707	8696	dbms_stats._column_stats_locked \| RowExclusiveLock
8708		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
8709		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
8710		-(3 rows)
	8697	+ dbms_stats._relation_stats_locked \| RowShareLock
	8698	+(2 rows)
8711	8699
8712	8700	COMMIT;
8713	8701	SELECT starelid, attname, stainherit FROM columns_locked_v c;

		@@ -9624,12 +9612,11 @@ SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
9624	9612	(1 row)
9625	9613
9626	9614	SELECT * FROM internal_locks;
9627		- relation \| mode
9628		------------------------------------+--------------------------
	9615	+ relation \| mode
	9616	+-----------------------------------+------------------
9629	9617	dbms_stats._column_stats_locked \| RowExclusiveLock
9630		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
9631		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
9632		-(3 rows)
	9618	+ dbms_stats._relation_stats_locked \| RowShareLock
	9619	+(2 rows)
9633	9620
9634	9621	COMMIT;
9635	9622	SELECT starelid, attname, stainherit FROM columns_locked_v c;

--- a/expected/ut-9.3.out

+++ b/expected/ut-9.3.out

		@@ -1865,7 +1865,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1865	1865	AND dbms_stats.is_target_relkind(c.relkind)
1866	1866	ORDER BY c.oid
1867	1867	) cn"
1868		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1868	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1869	1869	NOTICE: arguments are 9, s0.st0_idx, <NULL>
1870	1870	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1871	1871	FROM (SELECT c.oid

		@@ -1876,7 +1876,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1876	1876	AND dbms_stats.is_target_relkind(c.relkind)
1877	1877	ORDER BY c.oid
1878	1878	) cn"
1879		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1879	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1880	1880	NOTICE: arguments are 9, s0.st1, <NULL>
1881	1881	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1882	1882	FROM (SELECT c.oid

		@@ -1887,7 +1887,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1887	1887	AND dbms_stats.is_target_relkind(c.relkind)
1888	1888	ORDER BY c.oid
1889	1889	) cn"
1890		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1890	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1891	1891	NOTICE: arguments are 9, s0.st1_idx, <NULL>
1892	1892	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1893	1893	FROM (SELECT c.oid

		@@ -1898,7 +1898,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1898	1898	AND dbms_stats.is_target_relkind(c.relkind)
1899	1899	ORDER BY c.oid
1900	1900	) cn"
1901		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1901	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1902	1902	NOTICE: arguments are 9, s0.st2, <NULL>
1903	1903	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1904	1904	FROM (SELECT c.oid

		@@ -1909,7 +1909,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1909	1909	AND dbms_stats.is_target_relkind(c.relkind)
1910	1910	ORDER BY c.oid
1911	1911	) cn"
1912		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1912	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1913	1913	NOTICE: arguments are 9, s0.st2_idx, <NULL>
1914	1914	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1915	1915	FROM (SELECT c.oid

		@@ -1920,7 +1920,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1920	1920	AND dbms_stats.is_target_relkind(c.relkind)
1921	1921	ORDER BY c.oid
1922	1922	) cn"
1923		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1923	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1924	1924	NOTICE: arguments are 9, s0.sft0, <NULL>
1925	1925	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1926	1926	FROM (SELECT c.oid

		@@ -1931,7 +1931,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1931	1931	AND dbms_stats.is_target_relkind(c.relkind)
1932	1932	ORDER BY c.oid
1933	1933	) cn"
1934		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1934	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1935	1935	NOTICE: arguments are 9, s0.smv0, <NULL>
1936	1936	CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1937	1937	FROM (SELECT c.oid

		@@ -1942,7 +1942,7 @@ CONTEXT: SQL statement "SELECT dbms_stats.backup(backup_id, cn.oid, NULL)
1942	1942	AND dbms_stats.is_target_relkind(c.relkind)
1943	1943	ORDER BY c.oid
1944	1944	) cn"
1945		-PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 16 at PERFORM
	1945	+PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 17 at PERFORM
1946	1946	backup_schema_stats
1947	1947	---------------------
1948	1948	9

		@@ -2195,17 +2195,16 @@ SELECT dbms_stats.restore(2, 's0.st0', NULL);
2195	2195	(1 row)
2196	2196
2197	2197	SELECT * FROM internal_locks;
2198		- relation \| mode
2199		------------------------------------+--------------------------
2200		- dbms_stats.backup_history \| AccessShareLock
	2198	+ relation \| mode
	2199	+-----------------------------------+------------------
	2200	+ dbms_stats.backup_history \| RowShareLock
2201	2201	dbms_stats.column_stats_backup \| AccessShareLock
2202	2202	dbms_stats._column_stats_locked \| RowExclusiveLock
2203		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
2204	2203	dbms_stats.relation_stats_backup \| AccessShareLock
	2204	+ dbms_stats.relation_stats_backup \| RowShareLock
2205	2205	dbms_stats._relation_stats_locked \| RowExclusiveLock
2206	2206	dbms_stats._relation_stats_locked \| RowShareLock
2207		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
2208		-(8 rows)
	2207	+(7 rows)
2209	2208
2210	2209	COMMIT;
2211	2210	SELECT relid::regclass FROM dbms_stats.relation_stats_locked

		@@ -3243,16 +3242,16 @@ SELECT dbms_stats.restore_stats(2);
3243	3242	(15 rows)
3244	3243
3245	3244	SELECT * FROM internal_locks;
3246		- relation \| mode
3247		------------------------------------+--------------------------
	3245	+ relation \| mode
	3246	+-----------------------------------+------------------
3248	3247	dbms_stats.backup_history \| AccessShareLock
3249	3248	dbms_stats.column_stats_backup \| AccessShareLock
3250	3249	dbms_stats._column_stats_locked \| RowExclusiveLock
3251		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
3252	3250	dbms_stats.relation_stats_backup \| AccessShareLock
	3251	+ dbms_stats.relation_stats_backup \| RowShareLock
	3252	+ dbms_stats._relation_stats_locked \| ExclusiveLock
3253	3253	dbms_stats._relation_stats_locked \| RowExclusiveLock
3254	3254	dbms_stats._relation_stats_locked \| RowShareLock
3255		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
3256	3255	(8 rows)
3257	3256
3258	3257	COMMIT;

		@@ -3741,16 +3740,14 @@ SELECT * FROM columns_locked_v c;
3741	3740	(4 rows)
3742	3741
3743	3742	SELECT * FROM internal_locks;
3744		- relation \| mode
3745		------------------------------------+--------------------------
	3743	+ relation \| mode
	3744	+-----------------------------------+------------------
3746	3745	dbms_stats._column_stats_locked \| AccessShareLock
3747	3746	dbms_stats._column_stats_locked \| RowExclusiveLock
3748		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
3749	3747	dbms_stats._relation_stats_locked \| AccessShareLock
3750	3748	dbms_stats._relation_stats_locked \| RowExclusiveLock
3751	3749	dbms_stats._relation_stats_locked \| RowShareLock
3752		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
3753		-(7 rows)
	3750	+(5 rows)
3754	3751
3755	3752	COMMIT;
3756	3753	-- No.11-2-2

		@@ -5262,14 +5259,13 @@ SELECT dbms_stats.unlock();
5262	5259	(16 rows)
5263	5260
5264	5261	SELECT * FROM internal_locks;
5265		- relation \| mode
5266		------------------------------------+--------------------------
	5262	+ relation \| mode
	5263	+-----------------------------------+------------------
5267	5264	dbms_stats._column_stats_locked \| RowExclusiveLock
5268		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
5269	5265	dbms_stats._relation_stats_locked \| AccessShareLock
5270	5266	dbms_stats._relation_stats_locked \| RowExclusiveLock
5271		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
5272		-(5 rows)
	5267	+ dbms_stats._relation_stats_locked \| RowShareLock
	5268	+(4 rows)
5273	5269
5274	5270	COMMIT;
5275	5271	SELECT count(*) FROM dbms_stats._relation_stats_locked;

		@@ -5553,14 +5549,13 @@ SELECT dbms_stats.unlock_database_stats();
5553	5549	(16 rows)
5554	5550
5555	5551	SELECT * FROM internal_locks;
5556		- relation \| mode
5557		------------------------------------+--------------------------
	5552	+ relation \| mode
	5553	+-----------------------------------+------------------
5558	5554	dbms_stats._column_stats_locked \| RowExclusiveLock
5559		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
5560	5555	dbms_stats._relation_stats_locked \| AccessShareLock
	5556	+ dbms_stats._relation_stats_locked \| ExclusiveLock
5561	5557	dbms_stats._relation_stats_locked \| RowExclusiveLock
5562		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
5563		-(5 rows)
	5558	+(4 rows)
5564	5559
5565	5560	COMMIT;
5566	5561	SELECT count(*) FROM dbms_stats._relation_stats_locked;

		@@ -6351,14 +6346,12 @@ SELECT dbms_stats.unlock_schema_stats('s0');
6351	6346	(8 rows)
6352	6347
6353	6348	SELECT * FROM internal_locks;
6354		- relation \| mode
6355		------------------------------------+--------------------------
	6349	+ relation \| mode
	6350	+-----------------------------------+------------------
6356	6351	dbms_stats._column_stats_locked \| RowExclusiveLock
6357		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
6358		- dbms_stats._relation_stats_locked \| AccessShareLock
6359	6352	dbms_stats._relation_stats_locked \| RowExclusiveLock
6360		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
6361		-(5 rows)
	6353	+ dbms_stats._relation_stats_locked \| RowShareLock
	6354	+(3 rows)
6362	6355
6363	6356	COMMIT;
6364	6357	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -7168,13 +7161,11 @@ SELECT dbms_stats.unlock_table_stats('s0.st0');
7168	7161	(1 row)
7169	7162
7170	7163	SELECT * FROM internal_locks;
7171		- relation \| mode
7172		------------------------------------+--------------------------
	7164	+ relation \| mode
	7165	+-----------------------------------+------------------
7173	7166	dbms_stats._column_stats_locked \| RowExclusiveLock
7174		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
7175	7167	dbms_stats._relation_stats_locked \| RowExclusiveLock
7176		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
7177		-(4 rows)
	7168	+(2 rows)
7178	7169
7179	7170	COMMIT;
7180	7171	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -7667,7 +7658,7 @@ SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
7667	7658
7668	7659	SELECT dbms_stats.unlock_table_stats('s00', 's0');
7669	7660	ERROR: schema "s00" does not exist
7670		-CONTEXT: SQL function "unlock_table_stats" statement 3
	7661	+CONTEXT: SQL function "unlock_table_stats" statement 1
7671	7662	SELECT relid::regclass FROM dbms_stats._relation_stats_locked
7672	7663	GROUP BY relid
7673	7664	ORDER BY relid;

		@@ -7995,13 +7986,11 @@ SELECT dbms_stats.unlock_table_stats('s0', 'st0');
7995	7986	(1 row)
7996	7987
7997	7988	SELECT * FROM internal_locks;
7998		- relation \| mode
7999		------------------------------------+--------------------------
	7989	+ relation \| mode
	7990	+-----------------------------------+------------------
8000	7991	dbms_stats._column_stats_locked \| RowExclusiveLock
8001		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
8002	7992	dbms_stats._relation_stats_locked \| RowExclusiveLock
8003		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
8004		-(4 rows)
	7993	+(2 rows)
8005	7994
8006	7995	COMMIT;
8007	7996	SELECT relid::regclass FROM dbms_stats._relation_stats_locked

		@@ -9110,12 +9099,11 @@ SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
9110	9099	(1 row)
9111	9100
9112	9101	SELECT * FROM internal_locks;
9113		- relation \| mode
9114		------------------------------------+--------------------------
	9102	+ relation \| mode
	9103	+-----------------------------------+------------------
9115	9104	dbms_stats._column_stats_locked \| RowExclusiveLock
9116		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
9117		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
9118		-(3 rows)
	9105	+ dbms_stats._relation_stats_locked \| RowShareLock
	9106	+(2 rows)
9119	9107
9120	9108	COMMIT;
9121	9109	SELECT starelid, attname, stainherit FROM columns_locked_v c;

		@@ -10098,12 +10086,11 @@ SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
10098	10086	(1 row)
10099	10087
10100	10088	SELECT * FROM internal_locks;
10101		- relation \| mode
10102		------------------------------------+--------------------------
	10089	+ relation \| mode
	10090	+-----------------------------------+------------------
10103	10091	dbms_stats._column_stats_locked \| RowExclusiveLock
10104		- dbms_stats._column_stats_locked \| ShareUpdateExclusiveLock
10105		- dbms_stats._relation_stats_locked \| ShareUpdateExclusiveLock
10106		-(3 rows)
	10092	+ dbms_stats._relation_stats_locked \| RowShareLock
	10093	+(2 rows)
10107	10094
10108	10095	COMMIT;
10109	10096	SELECT starelid, attname, stainherit FROM columns_locked_v c;

--- a/expected/ut-common.out

+++ b/expected/ut-common.out

		@@ -2077,16 +2077,14 @@ SELECT relation::regclass, mode
2077	2077	OR relation::regclass::text LIKE 'dbms_stats.backup_history'
2078	2078	OR relation::regclass::text LIKE 'dbms_stats.%\_backup'
2079	2079	ORDER BY relation::regclass::text, mode;
2080		- relation \| mode
2081		-----------------------------------+--------------------------
	2080	+ relation \| mode
	2081	+----------------------------------+------------------
2082	2082	dbms_stats.backup_history \| AccessShareLock
2083	2083	dbms_stats.backup_history \| RowExclusiveLock
2084		- dbms_stats.backup_history \| ShareUpdateExclusiveLock
	2084	+ dbms_stats.backup_history \| RowShareLock
2085	2085	dbms_stats.column_stats_backup \| RowExclusiveLock
2086		- dbms_stats.column_stats_backup \| ShareUpdateExclusiveLock
2087	2086	dbms_stats.relation_stats_backup \| RowExclusiveLock
2088		- dbms_stats.relation_stats_backup \| ShareUpdateExclusiveLock
2089		-(7 rows)
	2087	+(5 rows)
2090	2088
2091	2089	COMMIT;
2092	2090	SELECT * FROM dbms_stats.backup_history;

--- a/ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.1.sql

+++ b/ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.1.sql

		@@ -4,6 +4,43 @@
4	4	\echo Use "ALTER EXTENSION pg_dbms_stats UPDATE TO '1.3.3'" to load this file. \quit
5	5
6	6	CREATE OR REPLACE FUNCTION dbms_stats.backup(
	7	+ backup_id int8,
	8	+ relid regclass,
	9	+ attnum int2
	10	+) RETURNS int8 AS
	11	+$$
	12	+/* Lock the backup id */
	13	+SELECT * from dbms_stats.backup_history
	14	+ WHERE id = $1 FOR UPDATE;
	15	+
	16	+INSERT INTO dbms_stats.relation_stats_backup
	17	+ SELECT $1, v.relid, v.relname, v.relpages, v.reltuples,
	18	+ v.curpages, v.last_analyze, v.last_autoanalyze
	19	+ FROM pg_catalog.pg_class c,
	20	+ dbms_stats.relation_stats_effective v
	21	+ WHERE c.oid = v.relid
	22	+ AND dbms_stats.is_target_relkind(relkind)
	23	+ AND NOT dbms_stats.is_system_catalog(v.relid)
	24	+ AND (v.relid = $2 OR $2 IS NULL);
	25	+
	26	+INSERT INTO dbms_stats.column_stats_backup
	27	+ SELECT $1, atttypid, s.*
	28	+ FROM pg_catalog.pg_class c,
	29	+ dbms_stats.column_stats_effective s,
	30	+ pg_catalog.pg_attribute a
	31	+ WHERE c.oid = starelid
	32	+ AND starelid = attrelid
	33	+ AND staattnum = attnum
	34	+ AND dbms_stats.is_target_relkind(relkind)
	35	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	36	+ AND ($2 IS NULL OR starelid = $2)
	37	+ AND ($3 IS NULL OR staattnum = $3);
	38	+
	39	+SELECT $1;
	40	+$$
	41	+LANGUAGE sql;
	42	+
	43	+CREATE OR REPLACE FUNCTION dbms_stats.backup(
7	44	relid regclass DEFAULT NULL,
8	45	attname text DEFAULT NULL,
9	46	comment text DEFAULT NULL

		@@ -20,7 +57,7 @@ BEGIN
20	57	END IF;
21	58	IF $1 IS NOT NULL THEN
22	59	SELECT relkind INTO backup_relkind
23		- FROM pg_catalog.pg_class WHERE oid = $1;
	60	+ FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
24	61	IF NOT FOUND THEN
25	62	RAISE EXCEPTION 'relation "%" not found', $1;
26	63	END IF;

		@@ -34,7 +71,7 @@ BEGIN
34	71	END IF;
35	72	IF $2 IS NOT NULL THEN
36	73	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
37		- WHERE a.attrelid = $1 AND a.attname = $2;
	74	+ WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
38	75	IF set_attnum IS NULL THEN
39	76	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
40	77	END IF;

		@@ -65,7 +102,8 @@ $$
65	102	DECLARE
66	103	backup_id int8;
67	104	BEGIN
68		- IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	105	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
	106	+ THEN
69	107	RAISE EXCEPTION 'schema "%" not found', $1;
70	108	END IF;
71	109	IF dbms_stats.is_system_schema($1) THEN

		@@ -112,15 +150,18 @@ BEGIN
112	150	IF $2 IS NULL AND $3 IS NOT NULL THEN
113	151	RAISE EXCEPTION 'relation required';
114	152	END IF;
115		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id <= $1) THEN
	153	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	154	+ WHERE id <= $1 FOR SHARE) THEN
116	155	RAISE EXCEPTION 'backup id % not found', $1;
117	156	END IF;
118	157	IF $2 IS NOT NULL THEN
119		- IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class WHERE oid = $2) THEN
	158	+ IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
	159	+ WHERE oid = $2 FOR SHARE) THEN
120	160	RAISE EXCEPTION 'relation "%" not found', $2;
121	161	END IF;
	162	+ -- Grabbing all backups for the relation which is not used in restore.
122	163	IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
123		- WHERE b.id <= $1 AND b.relid = $2) THEN
	164	+ WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
124	165	RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
125	166	END IF;
126	167	IF $3 IS NOT NULL THEN

		@@ -133,21 +174,25 @@ BEGIN
133	174	RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before',$3, $2, $1;
134	175	END IF;
135	176	END IF;
	177	+ PERFORM * FROM dbms_stats._relation_stats_locked r
	178	+ WHERE r.relid = $2 FOR UPDATE;
	179	+ ELSE
	180	+ /* Lock the whole relation stats if relation is not specified.*/
	181	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
136	182	END IF;
137	183
138		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
139		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
140		-
141	184	FOR restore_id, restore_relid IN
142		- SELECT max(b.id), c.oid
143		- FROM pg_class c, dbms_stats.relation_stats_backup b
144		- WHERE (c.oid = $2 OR $2 IS NULL)
145		- AND c.oid = b.relid
146		- AND dbms_stats.is_target_relkind(c.relkind)
147		- AND NOT dbms_stats.is_system_catalog(c.oid)
148		- AND b.id <= $1
149		- GROUP BY c.oid
150		- ORDER BY c.oid::regclass::text
	185	+ SELECT max(id), coid FROM
	186	+ (SELECT b.id as id, c.oid as coid
	187	+ FROM pg_class c, dbms_stats.relation_stats_backup b
	188	+ WHERE (c.oid = $2 OR $2 IS NULL)
	189	+ AND c.oid = b.relid
	190	+ AND dbms_stats.is_target_relkind(c.relkind)
	191	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	192	+ AND b.id <= $1
	193	+ FOR SHARE) t
	194	+ GROUP BY coid
	195	+ ORDER BY coid::regclass::text
151	196	LOOP
152	197	UPDATE dbms_stats._relation_stats_locked r
153	198	SET relid = b.relid,

		@@ -222,10 +267,31 @@ BEGIN
222	267	AND staattnum = restore_attnum;
223	268	END IF;
224	269	END LOOP;
	270	+EXCEPTION
	271	+ WHEN unique_violation THEN
	272	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
225	273	END;
226	274	$$
227	275	LANGUAGE plpgsql;
228	276
	277	+CREATE OR REPLACE FUNCTION dbms_stats.restore_database_stats(
	278	+ as_of_timestamp timestamp with time zone
	279	+) RETURNS SETOF regclass AS
	280	+$$
	281	+SELECT dbms_stats.restore(m.id, m.relid)
	282	+ FROM (SELECT max(id) AS id, relid
	283	+ FROM (SELECT r.id, r.relid
	284	+ FROM pg_class c, dbms_stats.relation_stats_backup r,
	285	+ dbms_stats.backup_history b
	286	+ WHERE c.oid = r.relid
	287	+ AND r.id = b.id
	288	+ AND b.time <= $1
	289	+ FOR SHARE) t1
	290	+ GROUP BY t1.relid
	291	+ ORDER BY t1.relid) m;
	292	+$$
	293	+LANGUAGE sql STRICT;
	294	+
229	295	CREATE OR REPLACE FUNCTION dbms_stats.restore_schema_stats(
230	296	schemaname text,
231	297	as_of_timestamp timestamp with time zone

		@@ -241,18 +307,19 @@ BEGIN
241	307
242	308	RETURN QUERY
243	309	SELECT dbms_stats.restore(m.id, m.relid)
244		- FROM (SELECT max(r.id) AS id, r.relid
245		- FROM pg_class c, pg_namespace n,
246		- dbms_stats.relation_stats_backup r,
247		- dbms_stats.backup_history b
248		- WHERE c.oid = r.relid
249		- AND c.relnamespace = n.oid
250		- AND n.nspname = $1
251		- AND r.id = b.id
252		- AND b.time <= $2
253		- GROUP BY r.relid
254		- ORDER BY r.relid
255		- ) m;
	310	+ FROM (SELECT max(id) AS id, relid
	311	+ FROM (SELECT r.id, r.relid
	312	+ FROM pg_class c, pg_namespace n,
	313	+ dbms_stats.relation_stats_backup r,
	314	+ dbms_stats.backup_history b
	315	+ WHERE c.oid = r.relid
	316	+ AND c.relnamespace = n.oid
	317	+ AND n.nspname = $1
	318	+ AND r.id = b.id
	319	+ AND b.time <= $2
	320	+ FOR SHARE) t1
	321	+ GROUP BY t1.relid
	322	+ ORDER BY t1.relid) m;
256	323	END;
257	324	$$
258	325	LANGUAGE plpgsql STRICT;

		@@ -272,8 +339,12 @@ BEGIN
272	339	RAISE EXCEPTION 'backup id % not found', $1;
273	340	END IF;
274	341
275		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
276		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	342	+ /* Lock the backup */
	343	+ PERFORM * from dbms_stats.relation_stats_backup b
	344	+ WHERE id = $1 FOR SHARE;
	345	+
	346	+ /* Locking only _relation_stats_locked is sufficient */
	347	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
277	348
278	349	FOR restore_relid IN
279	350	SELECT b.relid

		@@ -382,15 +453,12 @@ BEGIN
382	453	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
383	454	END IF;
384	455
385		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
386		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
387		-
388	456	/*
389	457	* If we don't have per-table statistics, create new one which has NULL for
390		- * every statistic column_stats_effective.
	458	+ * every statistic value for column_stats_effective.
391	459	*/
392	460	IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
393		- WHERE ru.relid = $1) THEN
	461	+ WHERE ru.relid = $1 FOR SHARE) THEN
394	462	INSERT INTO dbms_stats._relation_stats_locked
395	463	SELECT $1, dbms_stats.relname(nspname, relname),
396	464	NULL, NULL, NULL, NULL, NULL

		@@ -463,12 +531,15 @@ BEGIN
463	531	END IF;
464	532	END LOOP;
465	533
466		- /* If we don't have statistic at all, raise error. */
	534	+ /* If we don't have statistics at all, raise error. */
467	535	IF NOT FOUND THEN
468	536	RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
469	537	END IF;
470	538
471	539	RETURN $1;
	540	+EXCEPTION
	541	+ WHEN unique_violation THEN
	542	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
472	543	END;
473	544	$$
474	545	LANGUAGE plpgsql;

		@@ -495,9 +566,6 @@ BEGIN
495	566	RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
496	567	END IF;
497	568
498		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
499		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
500		-
501	569	UPDATE dbms_stats._relation_stats_locked r
502	570	SET relname = dbms_stats.relname(nspname, c.relname),
503	571	relpages = v.relpages,

		@@ -596,6 +664,9 @@ BEGIN
596	664	END LOOP;
597	665
598	666	RETURN $1;
	667	+EXCEPTION
	668	+ WHEN unique_violation THEN
	669	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
599	670	END;
600	671	$$
601	672	LANGUAGE plpgsql;

		@@ -637,15 +708,19 @@ BEGIN
637	708	IF $1 IS NULL AND $2 IS NOT NULL THEN
638	709	RAISE EXCEPTION 'relation required';
639	710	END IF;
	711	+
	712	+ /*
	713	+ * Lock the target relation to prevent conflicting with stats lock/restore
	714	+ */
	715	+ PERFORM * FROM dbms_stats._relation_stats_locked ru
	716	+ WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
	717	+
640	718	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
641	719	WHERE a.attrelid = $1 AND a.attname = $2;
642	720	IF $2 IS NOT NULL AND set_attnum IS NULL THEN
643	721	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
644	722	END IF;
645	723
646		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
647		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
648		-
649	724	DELETE FROM dbms_stats._column_stats_locked
650	725	WHERE (starelid = $1 OR $1 IS NULL)
651	726	AND (staattnum = set_attnum OR $2 IS NULL);

		@@ -668,6 +743,27 @@ END;
668	743	$$
669	744	LANGUAGE plpgsql;
670	745
	746	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_database_stats()
	747	+ RETURNS SETOF regclass AS
	748	+$$
	749	+DECLARE
	750	+ unlock_id int8;
	751	+BEGIN
	752	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
	753	+
	754	+ FOR unlock_id IN
	755	+ SELECT relid
	756	+ FROM dbms_stats._relation_stats_locked
	757	+ ORDER BY relid
	758	+ LOOP
	759	+ DELETE FROM dbms_stats._relation_stats_locked
	760	+ WHERE relid = unlock_id;
	761	+ RETURN NEXT unlock_id;
	762	+ END LOOP;
	763	+END;
	764	+$$
	765	+LANGUAGE plpgsql STRICT;
	766	+
671	767	CREATE OR REPLACE FUNCTION dbms_stats.unlock_schema_stats(
672	768	schemaname text
673	769	) RETURNS SETOF regclass AS

		@@ -682,16 +778,14 @@ BEGIN
682	778	RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
683	779	END IF;
684	780
685		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
686		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
687		-
688	781	FOR unlock_id IN
689		- SELECT relid
690		- FROM dbms_stats._relation_stats_locked, pg_class c, pg_namespace n
	782	+ SELECT r.relid
	783	+ FROM dbms_stats._relation_stats_locked r, pg_class c, pg_namespace n
691	784	WHERE relid = c.oid
692	785	AND c.relnamespace = n.oid
693	786	AND n.nspname = $1
694	787	ORDER BY relid
	788	+ FOR UPDATE
695	789	LOOP
696	790	DELETE FROM dbms_stats._relation_stats_locked
697	791	WHERE relid = unlock_id;

		@@ -701,6 +795,26 @@ END;
701	795	$$
702	796	LANGUAGE plpgsql STRICT;
703	797
	798	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
	799	+ RETURNS SETOF regclass AS
	800	+$$
	801	+DELETE FROM dbms_stats._relation_stats_locked
	802	+ WHERE relid = $1
	803	+ RETURNING relid::regclass
	804	+$$
	805	+LANGUAGE sql STRICT;
	806	+
	807	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_table_stats(
	808	+ schemaname text,
	809	+ tablename text
	810	+) RETURNS SETOF regclass AS
	811	+$$
	812	+DELETE FROM dbms_stats._relation_stats_locked
	813	+ WHERE relid = dbms_stats.relname($1, $2)::regclass
	814	+ RETURNING relid::regclass
	815	+$$
	816	+LANGUAGE sql STRICT;
	817	+
704	818	CREATE OR REPLACE FUNCTION dbms_stats.unlock_column_stats(
705	819	relid regclass,
706	820	attname text

		@@ -715,12 +829,14 @@ BEGIN
715	829	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
716	830	END IF;
717	831
718		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
719		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	832	+ /* Lock the locked table stats */
	833	+ PERFORM * from dbms_stats.relation_stats_locked r
	834	+ WHERE r.relid = $1 FOR SHARE;
	835	+
	836	+ DELETE FROM dbms_stats._column_stats_locked
	837	+ WHERE starelid = $1
	838	+ AND staattnum = set_attnum;
720	839
721		- DELETE FROM dbms_stats._column_stats_locked
722		- WHERE starelid = $1
723		- AND staattnum = set_attnum;
724	840	RETURN QUERY
725	841	SELECT $1;
726	842	END;

		@@ -743,12 +859,14 @@ BEGIN
743	859	RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
744	860	END IF;
745	861
746		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
747		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	862	+ /* Lock the locked table stats */
	863	+ PERFORM * from dbms_stats.relation_stats_locked r
	864	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	865	+
	866	+ DELETE FROM dbms_stats._column_stats_locked
	867	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	868	+ AND staattnum = set_attnum;
748	869
749		- DELETE FROM dbms_stats._column_stats_locked
750		- WHERE starelid = dbms_stats.relname($1, $2)::regclass
751		- AND staattnum = set_attnum;
752	870	RETURN QUERY
753	871	SELECT dbms_stats.relname($1, $2)::regclass;
754	872	END;

		@@ -771,11 +889,8 @@ BEGIN
771	889	RAISE EXCEPTION 'NULL is not allowed as the second parameter';
772	890	END IF;
773	891
774		- LOCK dbms_stats.backup_history IN SHARE UPDATE EXCLUSIVE MODE;
775		- LOCK dbms_stats.relation_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
776		- LOCK dbms_stats.column_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
777		-
778		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
	892	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	893	+ WHERE id = $1 FOR UPDATE) THEN
779	894	RAISE EXCEPTION 'backup id % not found', $1;
780	895	END IF;
781	896	IF NOT $2 AND NOT EXISTS(SELECT *

		@@ -790,7 +905,7 @@ BEGIN
790	905	FOR todelete IN
791	906	SELECT * FROM dbms_stats.backup_history
792	907	WHERE id <= $1
793		- ORDER BY id
	908	+ ORDER BY id FOR UPDATE
794	909	LOOP
795	910	DELETE FROM dbms_stats.backup_history
796	911	WHERE id = todelete.id;

		@@ -799,3 +914,47 @@ BEGIN
799	914	END;
800	915	$$
801	916	LANGUAGE plpgsql;
	917	+
	918	+CREATE OR REPLACE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
	919	+$$
	920	+DECLARE
	921	+ clean_relid Oid;
	922	+ clean_attnum int2;
	923	+ clean_inherit bool;
	924	+ clean_rel_col text;
	925	+BEGIN
	926	+ -- We don't have to check that table-level dummy statistics of the table
	927	+ -- exists here, because the foreign key constraints defined on column-level
	928	+ -- dummy static table ensures that.
	929	+ FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
	930	+ SELECT r.relname \|\| ', ' \|\| v.staattnum::text,
	931	+ v.starelid, v.staattnum, v.stainherit
	932	+ FROM dbms_stats._column_stats_locked v
	933	+ JOIN dbms_stats._relation_stats_locked r ON (v.starelid = r.relid)
	934	+ WHERE NOT EXISTS (
	935	+ SELECT NULL
	936	+ FROM pg_attribute a
	937	+ WHERE a.attrelid = v.starelid
	938	+ AND a.attnum = v.staattnum
	939	+ AND a.attisdropped = false
	940	+ FOR UPDATE
	941	+ )
	942	+ LOOP
	943	+ DELETE FROM dbms_stats._column_stats_locked
	944	+ WHERE starelid = clean_relid
	945	+ AND staattnum = clean_attnum
	946	+ AND stainherit = clean_inherit;
	947	+ RETURN NEXT clean_rel_col;
	948	+ END LOOP;
	949	+
	950	+ RETURN QUERY
	951	+ DELETE FROM dbms_stats._relation_stats_locked r
	952	+ WHERE NOT EXISTS (
	953	+ SELECT NULL
	954	+ FROM pg_class c
	955	+ WHERE c.oid = r.relid)
	956	+ RETURNING relname \|\| ',';
	957	+ RETURN;
	958	+END
	959	+$$
	960	+LANGUAGE plpgsql;

--- a/ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.2.sql

+++ b/ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.2.sql

		@@ -4,6 +4,43 @@
4	4	\echo Use "ALTER EXTENSION pg_dbms_stats UPDATE TO '1.3.3'" to load this file. \quit
5	5
6	6	CREATE OR REPLACE FUNCTION dbms_stats.backup(
	7	+ backup_id int8,
	8	+ relid regclass,
	9	+ attnum int2
	10	+) RETURNS int8 AS
	11	+$$
	12	+/* Lock the backup id */
	13	+SELECT * from dbms_stats.backup_history
	14	+ WHERE id = $1 FOR UPDATE;
	15	+
	16	+INSERT INTO dbms_stats.relation_stats_backup
	17	+ SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
	18	+ v.curpages, v.last_analyze, v.last_autoanalyze
	19	+ FROM pg_catalog.pg_class c,
	20	+ dbms_stats.relation_stats_effective v
	21	+ WHERE c.oid = v.relid
	22	+ AND dbms_stats.is_target_relkind(relkind)
	23	+ AND NOT dbms_stats.is_system_catalog(v.relid)
	24	+ AND (v.relid = $2 OR $2 IS NULL);
	25	+
	26	+INSERT INTO dbms_stats.column_stats_backup
	27	+ SELECT $1, atttypid, s.*
	28	+ FROM pg_catalog.pg_class c,
	29	+ dbms_stats.column_stats_effective s,
	30	+ pg_catalog.pg_attribute a
	31	+ WHERE c.oid = starelid
	32	+ AND starelid = attrelid
	33	+ AND staattnum = attnum
	34	+ AND dbms_stats.is_target_relkind(relkind)
	35	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	36	+ AND ($2 IS NULL OR starelid = $2)
	37	+ AND ($3 IS NULL OR staattnum = $3);
	38	+
	39	+SELECT $1;
	40	+$$
	41	+LANGUAGE sql;
	42	+
	43	+CREATE OR REPLACE FUNCTION dbms_stats.backup(
7	44	relid regclass DEFAULT NULL,
8	45	attname text DEFAULT NULL,
9	46	comment text DEFAULT NULL

		@@ -20,7 +57,7 @@ BEGIN
20	57	END IF;
21	58	IF $1 IS NOT NULL THEN
22	59	SELECT relkind INTO backup_relkind
23		- FROM pg_catalog.pg_class WHERE oid = $1;
	60	+ FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
24	61	IF NOT FOUND THEN
25	62	RAISE EXCEPTION 'relation "%" not found', $1;
26	63	END IF;

		@@ -34,7 +71,7 @@ BEGIN
34	71	END IF;
35	72	IF $2 IS NOT NULL THEN
36	73	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
37		- WHERE a.attrelid = $1 AND a.attname = $2;
	74	+ WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
38	75	IF set_attnum IS NULL THEN
39	76	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
40	77	END IF;

		@@ -65,7 +102,8 @@ $$
65	102	DECLARE
66	103	backup_id int8;
67	104	BEGIN
68		- IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	105	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
	106	+ THEN
69	107	RAISE EXCEPTION 'schema "%" not found', $1;
70	108	END IF;
71	109	IF dbms_stats.is_system_schema($1) THEN

		@@ -112,15 +150,18 @@ BEGIN
112	150	IF $2 IS NULL AND $3 IS NOT NULL THEN
113	151	RAISE EXCEPTION 'relation required';
114	152	END IF;
115		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id <= $1) THEN
	153	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	154	+ WHERE id <= $1 FOR SHARE) THEN
116	155	RAISE EXCEPTION 'backup id % not found', $1;
117	156	END IF;
118	157	IF $2 IS NOT NULL THEN
119		- IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class WHERE oid = $2) THEN
	158	+ IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
	159	+ WHERE oid = $2 FOR SHARE) THEN
120	160	RAISE EXCEPTION 'relation "%" not found', $2;
121	161	END IF;
	162	+ -- Grabbing all backups for the relation which is not used in restore.
122	163	IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
123		- WHERE b.id <= $1 AND b.relid = $2) THEN
	164	+ WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
124	165	RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
125	166	END IF;
126	167	IF $3 IS NOT NULL THEN

		@@ -133,21 +174,25 @@ BEGIN
133	174	RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before',$3, $2, $1;
134	175	END IF;
135	176	END IF;
	177	+ PERFORM * FROM dbms_stats._relation_stats_locked r
	178	+ WHERE r.relid = $2 FOR UPDATE;
	179	+ ELSE
	180	+ /* Lock the whole relation stats if relation is not specified.*/
	181	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
136	182	END IF;
137	183
138		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
139		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
140		-
141	184	FOR restore_id, restore_relid IN
142		- SELECT max(b.id), c.oid
143		- FROM pg_class c, dbms_stats.relation_stats_backup b
144		- WHERE (c.oid = $2 OR $2 IS NULL)
145		- AND c.oid = b.relid
146		- AND dbms_stats.is_target_relkind(c.relkind)
147		- AND NOT dbms_stats.is_system_catalog(c.oid)
148		- AND b.id <= $1
149		- GROUP BY c.oid
150		- ORDER BY c.oid::regclass::text
	185	+ SELECT max(id), coid FROM
	186	+ (SELECT b.id as id, c.oid as coid
	187	+ FROM pg_class c, dbms_stats.relation_stats_backup b
	188	+ WHERE (c.oid = $2 OR $2 IS NULL)
	189	+ AND c.oid = b.relid
	190	+ AND dbms_stats.is_target_relkind(c.relkind)
	191	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	192	+ AND b.id <= $1
	193	+ FOR SHARE) t
	194	+ GROUP BY coid
	195	+ ORDER BY coid::regclass::text
151	196	LOOP
152	197	UPDATE dbms_stats._relation_stats_locked r
153	198	SET relid = b.relid,

		@@ -224,10 +269,31 @@ BEGIN
224	269	AND staattnum = restore_attnum;
225	270	END IF;
226	271	END LOOP;
	272	+EXCEPTION
	273	+ WHEN unique_violation THEN
	274	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
227	275	END;
228	276	$$
229	277	LANGUAGE plpgsql;
230	278
	279	+CREATE OR REPLACE FUNCTION dbms_stats.restore_database_stats(
	280	+ as_of_timestamp timestamp with time zone
	281	+) RETURNS SETOF regclass AS
	282	+$$
	283	+SELECT dbms_stats.restore(m.id, m.relid)
	284	+ FROM (SELECT max(id) AS id, relid
	285	+ FROM (SELECT r.id, r.relid
	286	+ FROM pg_class c, dbms_stats.relation_stats_backup r,
	287	+ dbms_stats.backup_history b
	288	+ WHERE c.oid = r.relid
	289	+ AND r.id = b.id
	290	+ AND b.time <= $1
	291	+ FOR SHARE) t1
	292	+ GROUP BY t1.relid
	293	+ ORDER BY t1.relid) m;
	294	+$$
	295	+LANGUAGE sql STRICT;
	296	+
231	297	CREATE OR REPLACE FUNCTION dbms_stats.restore_schema_stats(
232	298	schemaname text,
233	299	as_of_timestamp timestamp with time zone

		@@ -243,18 +309,19 @@ BEGIN
243	309
244	310	RETURN QUERY
245	311	SELECT dbms_stats.restore(m.id, m.relid)
246		- FROM (SELECT max(r.id) AS id, r.relid
247		- FROM pg_class c, pg_namespace n,
248		- dbms_stats.relation_stats_backup r,
249		- dbms_stats.backup_history b
250		- WHERE c.oid = r.relid
251		- AND c.relnamespace = n.oid
252		- AND n.nspname = $1
253		- AND r.id = b.id
254		- AND b.time <= $2
255		- GROUP BY r.relid
256		- ORDER BY r.relid
257		- ) m;
	312	+ FROM (SELECT max(id) AS id, relid
	313	+ FROM (SELECT r.id, r.relid
	314	+ FROM pg_class c, pg_namespace n,
	315	+ dbms_stats.relation_stats_backup r,
	316	+ dbms_stats.backup_history b
	317	+ WHERE c.oid = r.relid
	318	+ AND c.relnamespace = n.oid
	319	+ AND n.nspname = $1
	320	+ AND r.id = b.id
	321	+ AND b.time <= $2
	322	+ FOR SHARE) t1
	323	+ GROUP BY t1.relid
	324	+ ORDER BY t1.relid) m;
258	325	END;
259	326	$$
260	327	LANGUAGE plpgsql STRICT;

		@@ -274,8 +341,12 @@ BEGIN
274	341	RAISE EXCEPTION 'backup id % not found', $1;
275	342	END IF;
276	343
277		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
278		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	344	+ /* Lock the backup */
	345	+ PERFORM * from dbms_stats.relation_stats_backup b
	346	+ WHERE id = $1 FOR SHARE;
	347	+
	348	+ /* Locking only _relation_stats_locked is sufficient */
	349	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
279	350
280	351	FOR restore_relid IN
281	352	SELECT b.relid

		@@ -386,15 +457,12 @@ BEGIN
386	457	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
387	458	END IF;
388	459
389		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
390		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
391		-
392	460	/*
393	461	* If we don't have per-table statistics, create new one which has NULL for
394		- * every statistic column_stats_effective.
	462	+ * every statistic value for column_stats_effective.
395	463	*/
396	464	IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
397		- WHERE ru.relid = $1) THEN
	465	+ WHERE ru.relid = $1 FOR SHARE) THEN
398	466	INSERT INTO dbms_stats._relation_stats_locked
399	467	SELECT $1, dbms_stats.relname(nspname, relname),
400	468	NULL, NULL, NULL, NULL, NULL

		@@ -475,12 +543,15 @@ BEGIN
475	543	END IF;
476	544	END LOOP;
477	545
478		- /* If we don't have statistic at all, raise error. */
	546	+ /* If we don't have statistics at all, raise error. */
479	547	IF NOT FOUND THEN
480	548	RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
481	549	END IF;
482	550
483	551	RETURN $1;
	552	+EXCEPTION
	553	+ WHEN unique_violation THEN
	554	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
484	555	END;
485	556	$$
486	557	LANGUAGE plpgsql;

		@@ -507,9 +578,6 @@ BEGIN
507	578	RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
508	579	END IF;
509	580
510		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
511		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
512		-
513	581	UPDATE dbms_stats._relation_stats_locked r
514	582	SET relname = dbms_stats.relname(nspname, c.relname),
515	583	relpages = v.relpages,

		@@ -617,6 +685,9 @@ BEGIN
617	685	END LOOP;
618	686
619	687	RETURN $1;
	688	+EXCEPTION
	689	+ WHEN unique_violation THEN
	690	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
620	691	END;
621	692	$$
622	693	LANGUAGE plpgsql;

		@@ -658,15 +729,19 @@ BEGIN
658	729	IF $1 IS NULL AND $2 IS NOT NULL THEN
659	730	RAISE EXCEPTION 'relation required';
660	731	END IF;
	732	+
	733	+ /*
	734	+ * Lock the target relation to prevent conflicting with stats lock/restore
	735	+ */
	736	+ PERFORM * FROM dbms_stats._relation_stats_locked ru
	737	+ WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
	738	+
661	739	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
662	740	WHERE a.attrelid = $1 AND a.attname = $2;
663	741	IF $2 IS NOT NULL AND set_attnum IS NULL THEN
664	742	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
665	743	END IF;
666	744
667		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
668		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
669		-
670	745	DELETE FROM dbms_stats._column_stats_locked
671	746	WHERE (starelid = $1 OR $1 IS NULL)
672	747	AND (staattnum = set_attnum OR $2 IS NULL);

		@@ -689,6 +764,27 @@ END;
689	764	$$
690	765	LANGUAGE plpgsql;
691	766
	767	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_database_stats()
	768	+ RETURNS SETOF regclass AS
	769	+$$
	770	+DECLARE
	771	+ unlock_id int8;
	772	+BEGIN
	773	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
	774	+
	775	+ FOR unlock_id IN
	776	+ SELECT relid
	777	+ FROM dbms_stats._relation_stats_locked
	778	+ ORDER BY relid
	779	+ LOOP
	780	+ DELETE FROM dbms_stats._relation_stats_locked
	781	+ WHERE relid = unlock_id;
	782	+ RETURN NEXT unlock_id;
	783	+ END LOOP;
	784	+END;
	785	+$$
	786	+LANGUAGE plpgsql STRICT;
	787	+
692	788	CREATE OR REPLACE FUNCTION dbms_stats.unlock_schema_stats(
693	789	schemaname text
694	790	) RETURNS SETOF regclass AS

		@@ -703,16 +799,14 @@ BEGIN
703	799	RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
704	800	END IF;
705	801
706		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
707		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
708		-
709	802	FOR unlock_id IN
710		- SELECT relid
711		- FROM dbms_stats._relation_stats_locked, pg_class c, pg_namespace n
	803	+ SELECT r.relid
	804	+ FROM dbms_stats._relation_stats_locked r, pg_class c, pg_namespace n
712	805	WHERE relid = c.oid
713	806	AND c.relnamespace = n.oid
714	807	AND n.nspname = $1
715	808	ORDER BY relid
	809	+ FOR UPDATE
716	810	LOOP
717	811	DELETE FROM dbms_stats._relation_stats_locked
718	812	WHERE relid = unlock_id;

		@@ -722,6 +816,26 @@ END;
722	816	$$
723	817	LANGUAGE plpgsql STRICT;
724	818
	819	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
	820	+ RETURNS SETOF regclass AS
	821	+$$
	822	+DELETE FROM dbms_stats._relation_stats_locked
	823	+ WHERE relid = $1
	824	+ RETURNING relid::regclass
	825	+$$
	826	+LANGUAGE sql STRICT;
	827	+
	828	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_table_stats(
	829	+ schemaname text,
	830	+ tablename text
	831	+) RETURNS SETOF regclass AS
	832	+$$
	833	+DELETE FROM dbms_stats._relation_stats_locked
	834	+ WHERE relid = dbms_stats.relname($1, $2)::regclass
	835	+ RETURNING relid::regclass
	836	+$$
	837	+LANGUAGE sql STRICT;
	838	+
725	839	CREATE OR REPLACE FUNCTION dbms_stats.unlock_column_stats(
726	840	relid regclass,
727	841	attname text

		@@ -736,12 +850,14 @@ BEGIN
736	850	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
737	851	END IF;
738	852
739		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
740		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	853	+ /* Lock the locked table stats */
	854	+ PERFORM * from dbms_stats.relation_stats_locked r
	855	+ WHERE r.relid = $1 FOR SHARE;
	856	+
	857	+ DELETE FROM dbms_stats._column_stats_locked
	858	+ WHERE starelid = $1
	859	+ AND staattnum = set_attnum;
741	860
742		- DELETE FROM dbms_stats._column_stats_locked
743		- WHERE starelid = $1
744		- AND staattnum = set_attnum;
745	861	RETURN QUERY
746	862	SELECT $1;
747	863	END;

		@@ -764,12 +880,14 @@ BEGIN
764	880	RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
765	881	END IF;
766	882
767		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
768		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	883	+ /* Lock the locked table stats */
	884	+ PERFORM * from dbms_stats.relation_stats_locked r
	885	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	886	+
	887	+ DELETE FROM dbms_stats._column_stats_locked
	888	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	889	+ AND staattnum = set_attnum;
769	890
770		- DELETE FROM dbms_stats._column_stats_locked
771		- WHERE starelid = dbms_stats.relname($1, $2)::regclass
772		- AND staattnum = set_attnum;
773	891	RETURN QUERY
774	892	SELECT dbms_stats.relname($1, $2)::regclass;
775	893	END;

		@@ -792,11 +910,8 @@ BEGIN
792	910	RAISE EXCEPTION 'NULL is not allowed as the second parameter';
793	911	END IF;
794	912
795		- LOCK dbms_stats.backup_history IN SHARE UPDATE EXCLUSIVE MODE;
796		- LOCK dbms_stats.relation_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
797		- LOCK dbms_stats.column_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
798		-
799		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
	913	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	914	+ WHERE id = $1 FOR UPDATE) THEN
800	915	RAISE EXCEPTION 'backup id % not found', $1;
801	916	END IF;
802	917	IF NOT $2 AND NOT EXISTS(SELECT *

		@@ -811,7 +926,7 @@ BEGIN
811	926	FOR todelete IN
812	927	SELECT * FROM dbms_stats.backup_history
813	928	WHERE id <= $1
814		- ORDER BY id
	929	+ ORDER BY id FOR UPDATE
815	930	LOOP
816	931	DELETE FROM dbms_stats.backup_history
817	932	WHERE id = todelete.id;

		@@ -820,3 +935,47 @@ BEGIN
820	935	END;
821	936	$$
822	937	LANGUAGE plpgsql;
	938	+
	939	+CREATE OR REPLACE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
	940	+$$
	941	+DECLARE
	942	+ clean_relid Oid;
	943	+ clean_attnum int2;
	944	+ clean_inherit bool;
	945	+ clean_rel_col text;
	946	+BEGIN
	947	+ -- We don't have to check that table-level dummy statistics of the table
	948	+ -- exists here, because the foreign key constraints defined on column-level
	949	+ -- dummy static table ensures that.
	950	+ FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
	951	+ SELECT r.relname \|\| ', ' \|\| v.staattnum::text,
	952	+ v.starelid, v.staattnum, v.stainherit
	953	+ FROM dbms_stats._column_stats_locked v
	954	+ JOIN dbms_stats._relation_stats_locked r ON (v.starelid = r.relid)
	955	+ WHERE NOT EXISTS (
	956	+ SELECT NULL
	957	+ FROM pg_attribute a
	958	+ WHERE a.attrelid = v.starelid
	959	+ AND a.attnum = v.staattnum
	960	+ AND a.attisdropped = false
	961	+ FOR UPDATE
	962	+ )
	963	+ LOOP
	964	+ DELETE FROM dbms_stats._column_stats_locked
	965	+ WHERE starelid = clean_relid
	966	+ AND staattnum = clean_attnum
	967	+ AND stainherit = clean_inherit;
	968	+ RETURN NEXT clean_rel_col;
	969	+ END LOOP;
	970	+
	971	+ RETURN QUERY
	972	+ DELETE FROM dbms_stats._relation_stats_locked r
	973	+ WHERE NOT EXISTS (
	974	+ SELECT NULL
	975	+ FROM pg_class c
	976	+ WHERE c.oid = r.relid)
	977	+ RETURNING relname \|\| ',';
	978	+ RETURN;
	979	+END
	980	+$$
	981	+LANGUAGE plpgsql;

--- a/ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.3.sql

+++ b/ext_scripts/pg_dbms_stats--1.3.2--1.3.3-9.3.sql

		@@ -4,6 +4,43 @@
4	4	\echo Use "ALTER EXTENSION pg_dbms_stats UPDATE TO '1.3.3'" to load this file. \quit
5	5
6	6	CREATE OR REPLACE FUNCTION dbms_stats.backup(
	7	+ backup_id int8,
	8	+ relid regclass,
	9	+ attnum int2
	10	+) RETURNS int8 AS
	11	+$$
	12	+/* Lock the backup id */
	13	+SELECT * from dbms_stats.backup_history
	14	+ WHERE id = $1 FOR UPDATE;
	15	+
	16	+INSERT INTO dbms_stats.relation_stats_backup
	17	+ SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
	18	+ v.curpages, v.last_analyze, v.last_autoanalyze
	19	+ FROM pg_catalog.pg_class c,
	20	+ dbms_stats.relation_stats_effective v
	21	+ WHERE c.oid = v.relid
	22	+ AND dbms_stats.is_target_relkind(relkind)
	23	+ AND NOT dbms_stats.is_system_catalog(v.relid)
	24	+ AND (v.relid = $2 OR $2 IS NULL);
	25	+
	26	+INSERT INTO dbms_stats.column_stats_backup
	27	+ SELECT $1, atttypid, s.*
	28	+ FROM pg_catalog.pg_class c,
	29	+ dbms_stats.column_stats_effective s,
	30	+ pg_catalog.pg_attribute a
	31	+ WHERE c.oid = starelid
	32	+ AND starelid = attrelid
	33	+ AND staattnum = attnum
	34	+ AND dbms_stats.is_target_relkind(relkind)
	35	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	36	+ AND ($2 IS NULL OR starelid = $2)
	37	+ AND ($3 IS NULL OR staattnum = $3);
	38	+
	39	+SELECT $1;
	40	+$$
	41	+LANGUAGE sql;
	42	+
	43	+CREATE OR REPLACE FUNCTION dbms_stats.backup(
7	44	relid regclass DEFAULT NULL,
8	45	attname text DEFAULT NULL,
9	46	comment text DEFAULT NULL

		@@ -20,7 +57,7 @@ BEGIN
20	57	END IF;
21	58	IF $1 IS NOT NULL THEN
22	59	SELECT relkind INTO backup_relkind
23		- FROM pg_catalog.pg_class WHERE oid = $1;
	60	+ FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
24	61	IF NOT FOUND THEN
25	62	RAISE EXCEPTION 'relation "%" not found', $1;
26	63	END IF;

		@@ -34,7 +71,7 @@ BEGIN
34	71	END IF;
35	72	IF $2 IS NOT NULL THEN
36	73	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
37		- WHERE a.attrelid = $1 AND a.attname = $2;
	74	+ WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
38	75	IF set_attnum IS NULL THEN
39	76	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
40	77	END IF;

		@@ -65,7 +102,8 @@ $$
65	102	DECLARE
66	103	backup_id int8;
67	104	BEGIN
68		- IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	105	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
	106	+ THEN
69	107	RAISE EXCEPTION 'schema "%" not found', $1;
70	108	END IF;
71	109	IF dbms_stats.is_system_schema($1) THEN

		@@ -112,15 +150,18 @@ BEGIN
112	150	IF $2 IS NULL AND $3 IS NOT NULL THEN
113	151	RAISE EXCEPTION 'relation required';
114	152	END IF;
115		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id <= $1) THEN
	153	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	154	+ WHERE id <= $1 FOR SHARE) THEN
116	155	RAISE EXCEPTION 'backup id % not found', $1;
117	156	END IF;
118	157	IF $2 IS NOT NULL THEN
119		- IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class WHERE oid = $2) THEN
	158	+ IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
	159	+ WHERE oid = $2 FOR SHARE) THEN
120	160	RAISE EXCEPTION 'relation "%" not found', $2;
121	161	END IF;
	162	+ -- Grabbing all backups for the relation which is not used in restore.
122	163	IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
123		- WHERE b.id <= $1 AND b.relid = $2) THEN
	164	+ WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
124	165	RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
125	166	END IF;
126	167	IF $3 IS NOT NULL THEN

		@@ -133,21 +174,25 @@ BEGIN
133	174	RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before',$3, $2, $1;
134	175	END IF;
135	176	END IF;
	177	+ PERFORM * FROM dbms_stats._relation_stats_locked r
	178	+ WHERE r.relid = $2 FOR UPDATE;
	179	+ ELSE
	180	+ /* Lock the whole relation stats if relation is not specified.*/
	181	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
136	182	END IF;
137	183
138		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
139		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
140		-
141	184	FOR restore_id, restore_relid IN
142		- SELECT max(b.id), c.oid
143		- FROM pg_class c, dbms_stats.relation_stats_backup b
144		- WHERE (c.oid = $2 OR $2 IS NULL)
145		- AND c.oid = b.relid
146		- AND dbms_stats.is_target_relkind(c.relkind)
147		- AND NOT dbms_stats.is_system_catalog(c.oid)
148		- AND b.id <= $1
149		- GROUP BY c.oid
150		- ORDER BY c.oid::regclass::text
	185	+ SELECT max(id), coid FROM
	186	+ (SELECT b.id as id, c.oid as coid
	187	+ FROM pg_class c, dbms_stats.relation_stats_backup b
	188	+ WHERE (c.oid = $2 OR $2 IS NULL)
	189	+ AND c.oid = b.relid
	190	+ AND dbms_stats.is_target_relkind(c.relkind)
	191	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	192	+ AND b.id <= $1
	193	+ FOR SHARE) t
	194	+ GROUP BY coid
	195	+ ORDER BY coid::regclass::text
151	196	LOOP
152	197	UPDATE dbms_stats._relation_stats_locked r
153	198	SET relid = b.relid,

		@@ -224,10 +269,31 @@ BEGIN
224	269	AND staattnum = restore_attnum;
225	270	END IF;
226	271	END LOOP;
	272	+EXCEPTION
	273	+ WHEN unique_violation THEN
	274	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
227	275	END;
228	276	$$
229	277	LANGUAGE plpgsql;
230	278
	279	+CREATE OR REPLACE FUNCTION dbms_stats.restore_database_stats(
	280	+ as_of_timestamp timestamp with time zone
	281	+) RETURNS SETOF regclass AS
	282	+$$
	283	+SELECT dbms_stats.restore(m.id, m.relid)
	284	+ FROM (SELECT max(id) AS id, relid
	285	+ FROM (SELECT r.id, r.relid
	286	+ FROM pg_class c, dbms_stats.relation_stats_backup r,
	287	+ dbms_stats.backup_history b
	288	+ WHERE c.oid = r.relid
	289	+ AND r.id = b.id
	290	+ AND b.time <= $1
	291	+ FOR SHARE) t1
	292	+ GROUP BY t1.relid
	293	+ ORDER BY t1.relid) m;
	294	+$$
	295	+LANGUAGE sql STRICT;
	296	+
231	297	CREATE OR REPLACE FUNCTION dbms_stats.restore_schema_stats(
232	298	schemaname text,
233	299	as_of_timestamp timestamp with time zone

		@@ -243,18 +309,19 @@ BEGIN
243	309
244	310	RETURN QUERY
245	311	SELECT dbms_stats.restore(m.id, m.relid)
246		- FROM (SELECT max(r.id) AS id, r.relid
247		- FROM pg_class c, pg_namespace n,
248		- dbms_stats.relation_stats_backup r,
249		- dbms_stats.backup_history b
250		- WHERE c.oid = r.relid
251		- AND c.relnamespace = n.oid
252		- AND n.nspname = $1
253		- AND r.id = b.id
254		- AND b.time <= $2
255		- GROUP BY r.relid
256		- ORDER BY r.relid
257		- ) m;
	312	+ FROM (SELECT max(id) AS id, relid
	313	+ FROM (SELECT r.id, r.relid
	314	+ FROM pg_class c, pg_namespace n,
	315	+ dbms_stats.relation_stats_backup r,
	316	+ dbms_stats.backup_history b
	317	+ WHERE c.oid = r.relid
	318	+ AND c.relnamespace = n.oid
	319	+ AND n.nspname = $1
	320	+ AND r.id = b.id
	321	+ AND b.time <= $2
	322	+ FOR SHARE) t1
	323	+ GROUP BY t1.relid
	324	+ ORDER BY t1.relid) m;
258	325	END;
259	326	$$
260	327	LANGUAGE plpgsql STRICT;

		@@ -274,8 +341,12 @@ BEGIN
274	341	RAISE EXCEPTION 'backup id % not found', $1;
275	342	END IF;
276	343
277		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
278		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	344	+ /* Lock the backup */
	345	+ PERFORM * from dbms_stats.relation_stats_backup b
	346	+ WHERE id = $1 FOR SHARE;
	347	+
	348	+ /* Locking only _relation_stats_locked is sufficient */
	349	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
279	350
280	351	FOR restore_relid IN
281	352	SELECT b.relid

		@@ -386,15 +457,12 @@ BEGIN
386	457	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
387	458	END IF;
388	459
389		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
390		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
391		-
392	460	/*
393	461	* If we don't have per-table statistics, create new one which has NULL for
394		- * every statistic column_stats_effective.
	462	+ * every statistic value for column_stats_effective.
395	463	*/
396	464	IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
397		- WHERE ru.relid = $1) THEN
	465	+ WHERE ru.relid = $1 FOR SHARE) THEN
398	466	INSERT INTO dbms_stats._relation_stats_locked
399	467	SELECT $1, dbms_stats.relname(nspname, relname),
400	468	NULL, NULL, NULL, NULL, NULL

		@@ -475,12 +543,15 @@ BEGIN
475	543	END IF;
476	544	END LOOP;
477	545
478		- /* If we don't have statistic at all, raise error. */
	546	+ /* If we don't have statistics at all, raise error. */
479	547	IF NOT FOUND THEN
480	548	RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
481	549	END IF;
482	550
483	551	RETURN $1;
	552	+EXCEPTION
	553	+ WHEN unique_violation THEN
	554	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
484	555	END;
485	556	$$
486	557	LANGUAGE plpgsql;

		@@ -507,9 +578,6 @@ BEGIN
507	578	RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
508	579	END IF;
509	580
510		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
511		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
512		-
513	581	UPDATE dbms_stats._relation_stats_locked r
514	582	SET relname = dbms_stats.relname(nspname, c.relname),
515	583	relpages = v.relpages,

		@@ -617,6 +685,9 @@ BEGIN
617	685	END LOOP;
618	686
619	687	RETURN $1;
	688	+EXCEPTION
	689	+ WHEN unique_violation THEN
	690	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
620	691	END;
621	692	$$
622	693	LANGUAGE plpgsql;

		@@ -658,15 +729,19 @@ BEGIN
658	729	IF $1 IS NULL AND $2 IS NOT NULL THEN
659	730	RAISE EXCEPTION 'relation required';
660	731	END IF;
	732	+
	733	+ /*
	734	+ * Lock the target relation to prevent conflicting with stats lock/restore
	735	+ */
	736	+ PERFORM * FROM dbms_stats._relation_stats_locked ru
	737	+ WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
	738	+
661	739	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
662	740	WHERE a.attrelid = $1 AND a.attname = $2;
663	741	IF $2 IS NOT NULL AND set_attnum IS NULL THEN
664	742	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
665	743	END IF;
666	744
667		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
668		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
669		-
670	745	DELETE FROM dbms_stats._column_stats_locked
671	746	WHERE (starelid = $1 OR $1 IS NULL)
672	747	AND (staattnum = set_attnum OR $2 IS NULL);

		@@ -689,6 +764,105 @@ END;
689	764	$$
690	765	LANGUAGE plpgsql;
691	766
	767	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_database_stats()
	768	+ RETURNS SETOF regclass AS
	769	+$$
	770	+DECLARE
	771	+ unlock_id int8;
	772	+BEGIN
	773	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
	774	+
	775	+ FOR unlock_id IN
	776	+ SELECT relid
	777	+ FROM dbms_stats._relation_stats_locked
	778	+ ORDER BY relid
	779	+ LOOP
	780	+ DELETE FROM dbms_stats._relation_stats_locked
	781	+ WHERE relid = unlock_id;
	782	+ RETURN NEXT unlock_id;
	783	+ END LOOP;
	784	+END;
	785	+$$
	786	+LANGUAGE plpgsql STRICT;
	787	+
	788	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
	789	+ RETURNS SETOF regclass AS
	790	+$$
	791	+DELETE FROM dbms_stats._relation_stats_locked
	792	+ WHERE relid = $1
	793	+ RETURNING relid::regclass
	794	+$$
	795	+LANGUAGE sql STRICT;
	796	+
	797	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_table_stats(
	798	+ schemaname text,
	799	+ tablename text
	800	+) RETURNS SETOF regclass AS
	801	+$$
	802	+DELETE FROM dbms_stats._relation_stats_locked
	803	+ WHERE relid = dbms_stats.relname($1, $2)::regclass
	804	+ RETURNING relid::regclass
	805	+$$
	806	+LANGUAGE sql STRICT;
	807	+
	808	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_column_stats(
	809	+ relid regclass,
	810	+ attname text
	811	+) RETURNS SETOF regclass AS
	812	+$$
	813	+DECLARE
	814	+ set_attnum int2;
	815	+BEGIN
	816	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	817	+ WHERE a.attrelid = $1 AND a.attname = $2;
	818	+ IF $2 IS NOT NULL AND set_attnum IS NULL THEN
	819	+ RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
	820	+ END IF;
	821	+
	822	+ /* Lock the locked table stats */
	823	+ PERFORM * from dbms_stats.relation_stats_locked r
	824	+ WHERE r.relid = $1 FOR SHARE;
	825	+
	826	+ DELETE FROM dbms_stats._column_stats_locked
	827	+ WHERE starelid = $1
	828	+ AND staattnum = set_attnum;
	829	+
	830	+ RETURN QUERY
	831	+ SELECT $1;
	832	+END;
	833	+$$
	834	+LANGUAGE plpgsql STRICT;
	835	+
	836	+CREATE OR REPLACE FUNCTION dbms_stats.unlock_column_stats(
	837	+ schemaname text,
	838	+ tablename text,
	839	+ attname text
	840	+) RETURNS SETOF regclass AS
	841	+$$
	842	+DECLARE
	843	+ set_attnum int2;
	844	+BEGIN
	845	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	846	+ WHERE a.attrelid = dbms_stats.relname($1, $2)::regclass
	847	+ AND a.attname = $3;
	848	+ IF $3 IS NOT NULL AND set_attnum IS NULL THEN
	849	+ RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
	850	+ END IF;
	851	+
	852	+ /* Lock the locked table stats */
	853	+ PERFORM * from dbms_stats.relation_stats_locked r
	854	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	855	+
	856	+ DELETE FROM dbms_stats._column_stats_locked
	857	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	858	+ AND staattnum = set_attnum;
	859	+
	860	+ RETURN QUERY
	861	+ SELECT dbms_stats.relname($1, $2)::regclass;
	862	+END;
	863	+$$
	864	+LANGUAGE plpgsql STRICT;
	865	+
692	866	CREATE OR REPLACE FUNCTION dbms_stats.unlock_schema_stats(
693	867	schemaname text
694	868	) RETURNS SETOF regclass AS

		@@ -703,16 +877,14 @@ BEGIN
703	877	RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
704	878	END IF;
705	879
706		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
707		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
708		-
709	880	FOR unlock_id IN
710		- SELECT relid
711		- FROM dbms_stats._relation_stats_locked, pg_class c, pg_namespace n
	881	+ SELECT r.relid
	882	+ FROM dbms_stats._relation_stats_locked r, pg_class c, pg_namespace n
712	883	WHERE relid = c.oid
713	884	AND c.relnamespace = n.oid
714	885	AND n.nspname = $1
715	886	ORDER BY relid
	887	+ FOR UPDATE
716	888	LOOP
717	889	DELETE FROM dbms_stats._relation_stats_locked
718	890	WHERE relid = unlock_id;

		@@ -736,12 +908,14 @@ BEGIN
736	908	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
737	909	END IF;
738	910
739		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
740		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	911	+ /* Lock the locked table stats */
	912	+ PERFORM * from dbms_stats.relation_stats_locked r
	913	+ WHERE r.relid = $1 FOR SHARE;
	914	+
	915	+ DELETE FROM dbms_stats._column_stats_locked
	916	+ WHERE starelid = $1
	917	+ AND staattnum = set_attnum;
741	918
742		- DELETE FROM dbms_stats._column_stats_locked
743		- WHERE starelid = $1
744		- AND staattnum = set_attnum;
745	919	RETURN QUERY
746	920	SELECT $1;
747	921	END;

		@@ -764,12 +938,14 @@ BEGIN
764	938	RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
765	939	END IF;
766	940
767		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
768		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	941	+ /* Lock the locked table stats */
	942	+ PERFORM * from dbms_stats.relation_stats_locked r
	943	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	944	+
	945	+ DELETE FROM dbms_stats._column_stats_locked
	946	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	947	+ AND staattnum = set_attnum;
769	948
770		- DELETE FROM dbms_stats._column_stats_locked
771		- WHERE starelid = dbms_stats.relname($1, $2)::regclass
772		- AND staattnum = set_attnum;
773	949	RETURN QUERY
774	950	SELECT dbms_stats.relname($1, $2)::regclass;
775	951	END;

		@@ -783,7 +959,7 @@ CREATE OR REPLACE FUNCTION dbms_stats.purge_stats(
783	959	$$
784	960	DECLARE
785	961	delete_id int8;
786		- deleted dbms_stats.backup_history;
	962	+ todelete dbms_stats.backup_history;
787	963	BEGIN
788	964	IF $1 IS NULL THEN
789	965	RAISE EXCEPTION 'backup id required';

		@@ -792,11 +968,8 @@ BEGIN
792	968	RAISE EXCEPTION 'NULL is not allowed as the second parameter';
793	969	END IF;
794	970
795		- LOCK dbms_stats.backup_history IN SHARE UPDATE EXCLUSIVE MODE;
796		- LOCK dbms_stats.relation_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
797		- LOCK dbms_stats.column_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
798		-
799		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
	971	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	972	+ WHERE id = $1 FOR UPDATE) THEN
800	973	RAISE EXCEPTION 'backup id % not found', $1;
801	974	END IF;
802	975	IF NOT $2 AND NOT EXISTS(SELECT *

		@@ -808,15 +981,59 @@ BEGIN
808	981	RETURN;
809	982	END IF;
810	983
811		- FOR deleted IN
	984	+ FOR todelete IN
812	985	SELECT * FROM dbms_stats.backup_history
813	986	WHERE id <= $1
814		- ORDER BY id
	987	+ ORDER BY id FOR UPDATE
815	988	LOOP
816	989	DELETE FROM dbms_stats.backup_history
817		- WHERE id = deleted.id;
818		- RETURN NEXT deleted;
	990	+ WHERE id = todelete.id;
	991	+ RETURN NEXT todelete;
819	992	END LOOP;
820	993	END;
821	994	$$
822	995	LANGUAGE plpgsql;
	996	+
	997	+CREATE OR REPLACE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
	998	+$$
	999	+DECLARE
	1000	+ clean_relid Oid;
	1001	+ clean_attnum int2;
	1002	+ clean_inherit bool;
	1003	+ clean_rel_col text;
	1004	+BEGIN
	1005	+ -- We don't have to check that table-level dummy statistics of the table
	1006	+ -- exists here, because the foreign key constraints defined on column-level
	1007	+ -- dummy static table ensures that.
	1008	+ FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
	1009	+ SELECT r.relname \|\| ', ' \|\| v.staattnum::text,
	1010	+ v.starelid, v.staattnum, v.stainherit
	1011	+ FROM dbms_stats._column_stats_locked v
	1012	+ JOIN dbms_stats._relation_stats_locked r ON (v.starelid = r.relid)
	1013	+ WHERE NOT EXISTS (
	1014	+ SELECT NULL
	1015	+ FROM pg_attribute a
	1016	+ WHERE a.attrelid = v.starelid
	1017	+ AND a.attnum = v.staattnum
	1018	+ AND a.attisdropped = false
	1019	+ FOR UPDATE
	1020	+ )
	1021	+ LOOP
	1022	+ DELETE FROM dbms_stats._column_stats_locked
	1023	+ WHERE starelid = clean_relid
	1024	+ AND staattnum = clean_attnum
	1025	+ AND stainherit = clean_inherit;
	1026	+ RETURN NEXT clean_rel_col;
	1027	+ END LOOP;
	1028	+
	1029	+ RETURN QUERY
	1030	+ DELETE FROM dbms_stats._relation_stats_locked r
	1031	+ WHERE NOT EXISTS (
	1032	+ SELECT NULL
	1033	+ FROM pg_class c
	1034	+ WHERE c.oid = r.relid)
	1035	+ RETURNING relname \|\| ',';
	1036	+ RETURN;
	1037	+END
	1038	+$$
	1039	+LANGUAGE plpgsql;

--- a/ext_scripts/pg_dbms_stats--1.3.3-9.1.sql

+++ b/ext_scripts/pg_dbms_stats--1.3.3-9.1.sql

		@@ -300,6 +300,10 @@ CREATE FUNCTION dbms_stats.backup(
300	300	attnum int2
301	301	) RETURNS int8 AS
302	302	$$
	303	+/* Lock the backup id */
	304	+SELECT * from dbms_stats.backup_history
	305	+ WHERE id = $1 FOR UPDATE;
	306	+
303	307	INSERT INTO dbms_stats.relation_stats_backup
304	308	SELECT $1, v.relid, v.relname, v.relpages, v.reltuples,
305	309	v.curpages, v.last_analyze, v.last_autoanalyze

		@@ -344,7 +348,7 @@ BEGIN
344	348	END IF;
345	349	IF $1 IS NOT NULL THEN
346	350	SELECT relkind INTO backup_relkind
347		- FROM pg_catalog.pg_class WHERE oid = $1;
	351	+ FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
348	352	IF NOT FOUND THEN
349	353	RAISE EXCEPTION 'relation "%" not found', $1;
350	354	END IF;

		@@ -358,7 +362,7 @@ BEGIN
358	362	END IF;
359	363	IF $2 IS NOT NULL THEN
360	364	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
361		- WHERE a.attrelid = $1 AND a.attname = $2;
	365	+ WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
362	366	IF set_attnum IS NULL THEN
363	367	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
364	368	END IF;

		@@ -397,7 +401,8 @@ $$
397	401	DECLARE
398	402	backup_id int8;
399	403	BEGIN
400		- IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	404	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
	405	+ THEN
401	406	RAISE EXCEPTION 'schema "%" not found', $1;
402	407	END IF;
403	408	IF dbms_stats.is_system_schema($1) THEN

		@@ -487,15 +492,18 @@ BEGIN
487	492	IF $2 IS NULL AND $3 IS NOT NULL THEN
488	493	RAISE EXCEPTION 'relation required';
489	494	END IF;
490		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id <= $1) THEN
	495	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	496	+ WHERE id <= $1 FOR SHARE) THEN
491	497	RAISE EXCEPTION 'backup id % not found', $1;
492	498	END IF;
493	499	IF $2 IS NOT NULL THEN
494		- IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class WHERE oid = $2) THEN
	500	+ IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
	501	+ WHERE oid = $2 FOR SHARE) THEN
495	502	RAISE EXCEPTION 'relation "%" not found', $2;
496	503	END IF;
	504	+ -- Grabbing all backups for the relation which is not used in restore.
497	505	IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
498		- WHERE b.id <= $1 AND b.relid = $2) THEN
	506	+ WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
499	507	RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
500	508	END IF;
501	509	IF $3 IS NOT NULL THEN

		@@ -508,21 +516,25 @@ BEGIN
508	516	RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before',$3, $2, $1;
509	517	END IF;
510	518	END IF;
	519	+ PERFORM * FROM dbms_stats._relation_stats_locked r
	520	+ WHERE r.relid = $2 FOR UPDATE;
	521	+ ELSE
	522	+ /* Lock the whole relation stats if relation is not specified.*/
	523	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
511	524	END IF;
512	525
513		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
514		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
515		-
516	526	FOR restore_id, restore_relid IN
517		- SELECT max(b.id), c.oid
518		- FROM pg_class c, dbms_stats.relation_stats_backup b
519		- WHERE (c.oid = $2 OR $2 IS NULL)
520		- AND c.oid = b.relid
521		- AND dbms_stats.is_target_relkind(c.relkind)
522		- AND NOT dbms_stats.is_system_catalog(c.oid)
523		- AND b.id <= $1
524		- GROUP BY c.oid
525		- ORDER BY c.oid::regclass::text
	527	+ SELECT max(id), coid FROM
	528	+ (SELECT b.id as id, c.oid as coid
	529	+ FROM pg_class c, dbms_stats.relation_stats_backup b
	530	+ WHERE (c.oid = $2 OR $2 IS NULL)
	531	+ AND c.oid = b.relid
	532	+ AND dbms_stats.is_target_relkind(c.relkind)
	533	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	534	+ AND b.id <= $1
	535	+ FOR SHARE) t
	536	+ GROUP BY coid
	537	+ ORDER BY coid::regclass::text
526	538	LOOP
527	539	UPDATE dbms_stats._relation_stats_locked r
528	540	SET relid = b.relid,

		@@ -597,6 +609,9 @@ BEGIN
597	609	AND staattnum = restore_attnum;
598	610	END IF;
599	611	END LOOP;
	612	+EXCEPTION
	613	+ WHEN unique_violation THEN
	614	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
600	615	END;
601	616	$$
602	617	LANGUAGE plpgsql;

		@@ -606,15 +621,16 @@ CREATE FUNCTION dbms_stats.restore_database_stats(
606	621	) RETURNS SETOF regclass AS
607	622	$$
608	623	SELECT dbms_stats.restore(m.id, m.relid)
609		- FROM (SELECT max(r.id) AS id, r.relid
610		- FROM pg_class c, dbms_stats.relation_stats_backup r,
611		- dbms_stats.backup_history b
612		- WHERE c.oid = r.relid
613		- AND r.id = b.id
614		- AND b.time <= $1
615		- GROUP BY r.relid
616		- ORDER BY r.relid
617		- ) m;
	624	+ FROM (SELECT max(id) AS id, relid
	625	+ FROM (SELECT r.id, r.relid
	626	+ FROM pg_class c, dbms_stats.relation_stats_backup r,
	627	+ dbms_stats.backup_history b
	628	+ WHERE c.oid = r.relid
	629	+ AND r.id = b.id
	630	+ AND b.time <= $1
	631	+ FOR SHARE) t1
	632	+ GROUP BY t1.relid
	633	+ ORDER BY t1.relid) m;
618	634	$$
619	635	LANGUAGE sql STRICT;
620	636

		@@ -633,18 +649,19 @@ BEGIN
633	649
634	650	RETURN QUERY
635	651	SELECT dbms_stats.restore(m.id, m.relid)
636		- FROM (SELECT max(r.id) AS id, r.relid
637		- FROM pg_class c, pg_namespace n,
638		- dbms_stats.relation_stats_backup r,
639		- dbms_stats.backup_history b
640		- WHERE c.oid = r.relid
641		- AND c.relnamespace = n.oid
642		- AND n.nspname = $1
643		- AND r.id = b.id
644		- AND b.time <= $2
645		- GROUP BY r.relid
646		- ORDER BY r.relid
647		- ) m;
	652	+ FROM (SELECT max(id) AS id, relid
	653	+ FROM (SELECT r.id, r.relid
	654	+ FROM pg_class c, pg_namespace n,
	655	+ dbms_stats.relation_stats_backup r,
	656	+ dbms_stats.backup_history b
	657	+ WHERE c.oid = r.relid
	658	+ AND c.relnamespace = n.oid
	659	+ AND n.nspname = $1
	660	+ AND r.id = b.id
	661	+ AND b.time <= $2
	662	+ FOR SHARE) t1
	663	+ GROUP BY t1.relid
	664	+ ORDER BY t1.relid) m;
648	665	END;
649	666	$$
650	667	LANGUAGE plpgsql STRICT;

		@@ -707,8 +724,12 @@ BEGIN
707	724	RAISE EXCEPTION 'backup id % not found', $1;
708	725	END IF;
709	726
710		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
711		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	727	+ /* Lock the backup */
	728	+ PERFORM * from dbms_stats.relation_stats_backup b
	729	+ WHERE id = $1 FOR SHARE;
	730	+
	731	+ /* Locking only _relation_stats_locked is sufficient */
	732	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
712	733
713	734	FOR restore_relid IN
714	735	SELECT b.relid

		@@ -821,15 +842,12 @@ BEGIN
821	842	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
822	843	END IF;
823	844
824		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
825		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
826		-
827	845	/*
828	846	* If we don't have per-table statistics, create new one which has NULL for
829	847	* every statistic value for column_stats_effective.
830	848	*/
831	849	IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
832		- WHERE ru.relid = $1) THEN
	850	+ WHERE ru.relid = $1 FOR SHARE) THEN
833	851	INSERT INTO dbms_stats._relation_stats_locked
834	852	SELECT $1, dbms_stats.relname(nspname, relname),
835	853	NULL, NULL, NULL, NULL, NULL

		@@ -908,6 +926,9 @@ BEGIN
908	926	END IF;
909	927
910	928	RETURN $1;
	929	+EXCEPTION
	930	+ WHEN unique_violation THEN
	931	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
911	932	END;
912	933	$$
913	934	LANGUAGE plpgsql;

		@@ -934,9 +955,6 @@ BEGIN
934	955	RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
935	956	END IF;
936	957
937		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
938		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
939		-
940	958	UPDATE dbms_stats._relation_stats_locked r
941	959	SET relname = dbms_stats.relname(nspname, c.relname),
942	960	relpages = v.relpages,

		@@ -1035,6 +1053,9 @@ BEGIN
1035	1053	END LOOP;
1036	1054
1037	1055	RETURN $1;
	1056	+EXCEPTION
	1057	+ WHEN unique_violation THEN
	1058	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
1038	1059	END;
1039	1060	$$
1040	1061	LANGUAGE plpgsql;

		@@ -1128,15 +1149,19 @@ BEGIN
1128	1149	IF $1 IS NULL AND $2 IS NOT NULL THEN
1129	1150	RAISE EXCEPTION 'relation required';
1130	1151	END IF;
	1152	+
	1153	+ /*
	1154	+ * Lock the target relation to prevent conflicting with stats lock/restore
	1155	+ */
	1156	+ PERFORM * FROM dbms_stats._relation_stats_locked ru
	1157	+ WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
	1158	+
1131	1159	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1132	1160	WHERE a.attrelid = $1 AND a.attname = $2;
1133	1161	IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1134	1162	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1135	1163	END IF;
1136	1164
1137		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1138		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1139		-
1140	1165	DELETE FROM dbms_stats._column_stats_locked
1141	1166	WHERE (starelid = $1 OR $1 IS NULL)
1142	1167	AND (staattnum = set_attnum OR $2 IS NULL);

		@@ -1165,8 +1190,7 @@ $$
1165	1190	DECLARE
1166	1191	unlock_id int8;
1167	1192	BEGIN
1168		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1169		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1193	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
1170	1194
1171	1195	FOR unlock_id IN
1172	1196	SELECT relid

		@@ -1195,16 +1219,14 @@ BEGIN
1195	1219	RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
1196	1220	END IF;
1197	1221
1198		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1199		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1200		-
1201	1222	FOR unlock_id IN
1202		- SELECT relid
1203		- FROM dbms_stats._relation_stats_locked, pg_class c, pg_namespace n
	1223	+ SELECT r.relid
	1224	+ FROM dbms_stats._relation_stats_locked r, pg_class c, pg_namespace n
1204	1225	WHERE relid = c.oid
1205	1226	AND c.relnamespace = n.oid
1206	1227	AND n.nspname = $1
1207	1228	ORDER BY relid
	1229	+ FOR UPDATE
1208	1230	LOOP
1209	1231	DELETE FROM dbms_stats._relation_stats_locked
1210	1232	WHERE relid = unlock_id;

		@@ -1217,10 +1239,6 @@ LANGUAGE plpgsql STRICT;
1217	1239	CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
1218	1240	RETURNS SETOF regclass AS
1219	1241	$$
1220		-
1221		-LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1222		-LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1223		-
1224	1242	DELETE FROM dbms_stats._relation_stats_locked
1225	1243	WHERE relid = $1
1226	1244	RETURNING relid::regclass

		@@ -1232,10 +1250,6 @@ CREATE FUNCTION dbms_stats.unlock_table_stats(
1232	1250	tablename text
1233	1251	) RETURNS SETOF regclass AS
1234	1252	$$
1235		-
1236		-LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1237		-LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1238		-
1239	1253	DELETE FROM dbms_stats._relation_stats_locked
1240	1254	WHERE relid = dbms_stats.relname($1, $2)::regclass
1241	1255	RETURNING relid::regclass

		@@ -1256,12 +1270,14 @@ BEGIN
1256	1270	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1257	1271	END IF;
1258	1272
1259		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1260		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1273	+ /* Lock the locked table stats */
	1274	+ PERFORM * from dbms_stats.relation_stats_locked r
	1275	+ WHERE r.relid = $1 FOR SHARE;
	1276	+
	1277	+ DELETE FROM dbms_stats._column_stats_locked
	1278	+ WHERE starelid = $1
	1279	+ AND staattnum = set_attnum;
1261	1280
1262		- DELETE FROM dbms_stats._column_stats_locked
1263		- WHERE starelid = $1
1264		- AND staattnum = set_attnum;
1265	1281	RETURN QUERY
1266	1282	SELECT $1;
1267	1283	END;

		@@ -1284,12 +1300,14 @@ BEGIN
1284	1300	RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
1285	1301	END IF;
1286	1302
1287		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1288		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1303	+ /* Lock the locked table stats */
	1304	+ PERFORM * from dbms_stats.relation_stats_locked r
	1305	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	1306	+
	1307	+ DELETE FROM dbms_stats._column_stats_locked
	1308	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	1309	+ AND staattnum = set_attnum;
1289	1310
1290		- DELETE FROM dbms_stats._column_stats_locked
1291		- WHERE starelid = dbms_stats.relname($1, $2)::regclass
1292		- AND staattnum = set_attnum;
1293	1311	RETURN QUERY
1294	1312	SELECT dbms_stats.relname($1, $2)::regclass;
1295	1313	END;

		@@ -1384,11 +1402,8 @@ BEGIN
1384	1402	RAISE EXCEPTION 'NULL is not allowed as the second parameter';
1385	1403	END IF;
1386	1404
1387		- LOCK dbms_stats.backup_history IN SHARE UPDATE EXCLUSIVE MODE;
1388		- LOCK dbms_stats.relation_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1389		- LOCK dbms_stats.column_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1390		-
1391		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
	1405	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	1406	+ WHERE id = $1 FOR UPDATE) THEN
1392	1407	RAISE EXCEPTION 'backup id % not found', $1;
1393	1408	END IF;
1394	1409	IF NOT $2 AND NOT EXISTS(SELECT *

		@@ -1403,7 +1418,7 @@ BEGIN
1403	1418	FOR todelete IN
1404	1419	SELECT * FROM dbms_stats.backup_history
1405	1420	WHERE id <= $1
1406		- ORDER BY id
	1421	+ ORDER BY id FOR UPDATE
1407	1422	LOOP
1408	1423	DELETE FROM dbms_stats.backup_history
1409	1424	WHERE id = todelete.id;

		@@ -1438,6 +1453,7 @@ BEGIN
1438	1453	WHERE a.attrelid = v.starelid
1439	1454	AND a.attnum = v.staattnum
1440	1455	AND a.attisdropped = false
	1456	+ FOR UPDATE
1441	1457	)
1442	1458	LOOP
1443	1459	DELETE FROM dbms_stats._column_stats_locked

--- a/ext_scripts/pg_dbms_stats--1.3.3-9.2.sql

+++ b/ext_scripts/pg_dbms_stats--1.3.3-9.2.sql

		@@ -336,6 +336,10 @@ CREATE FUNCTION dbms_stats.backup(
336	336	attnum int2
337	337	) RETURNS int8 AS
338	338	$$
	339	+/* Lock the backup id */
	340	+SELECT * from dbms_stats.backup_history
	341	+ WHERE id = $1 FOR UPDATE;
	342	+
339	343	INSERT INTO dbms_stats.relation_stats_backup
340	344	SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
341	345	v.curpages, v.last_analyze, v.last_autoanalyze

		@@ -380,7 +384,7 @@ BEGIN
380	384	END IF;
381	385	IF $1 IS NOT NULL THEN
382	386	SELECT relkind INTO backup_relkind
383		- FROM pg_catalog.pg_class WHERE oid = $1;
	387	+ FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
384	388	IF NOT FOUND THEN
385	389	RAISE EXCEPTION 'relation "%" not found', $1;
386	390	END IF;

		@@ -394,7 +398,7 @@ BEGIN
394	398	END IF;
395	399	IF $2 IS NOT NULL THEN
396	400	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
397		- WHERE a.attrelid = $1 AND a.attname = $2;
	401	+ WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
398	402	IF set_attnum IS NULL THEN
399	403	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
400	404	END IF;

		@@ -433,7 +437,8 @@ $$
433	437	DECLARE
434	438	backup_id int8;
435	439	BEGIN
436		- IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	440	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
	441	+ THEN
437	442	RAISE EXCEPTION 'schema "%" not found', $1;
438	443	END IF;
439	444	IF dbms_stats.is_system_schema($1) THEN

		@@ -523,15 +528,18 @@ BEGIN
523	528	IF $2 IS NULL AND $3 IS NOT NULL THEN
524	529	RAISE EXCEPTION 'relation required';
525	530	END IF;
526		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id <= $1) THEN
	531	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	532	+ WHERE id <= $1 FOR SHARE) THEN
527	533	RAISE EXCEPTION 'backup id % not found', $1;
528	534	END IF;
529	535	IF $2 IS NOT NULL THEN
530		- IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class WHERE oid = $2) THEN
	536	+ IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
	537	+ WHERE oid = $2 FOR SHARE) THEN
531	538	RAISE EXCEPTION 'relation "%" not found', $2;
532	539	END IF;
	540	+ -- Grabbing all backups for the relation which is not used in restore.
533	541	IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
534		- WHERE b.id <= $1 AND b.relid = $2) THEN
	542	+ WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
535	543	RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
536	544	END IF;
537	545	IF $3 IS NOT NULL THEN

		@@ -544,21 +552,25 @@ BEGIN
544	552	RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before',$3, $2, $1;
545	553	END IF;
546	554	END IF;
	555	+ PERFORM * FROM dbms_stats._relation_stats_locked r
	556	+ WHERE r.relid = $2 FOR UPDATE;
	557	+ ELSE
	558	+ /* Lock the whole relation stats if relation is not specified.*/
	559	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
547	560	END IF;
548	561
549		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
550		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
551		-
552	562	FOR restore_id, restore_relid IN
553		- SELECT max(b.id), c.oid
554		- FROM pg_class c, dbms_stats.relation_stats_backup b
555		- WHERE (c.oid = $2 OR $2 IS NULL)
556		- AND c.oid = b.relid
557		- AND dbms_stats.is_target_relkind(c.relkind)
558		- AND NOT dbms_stats.is_system_catalog(c.oid)
559		- AND b.id <= $1
560		- GROUP BY c.oid
561		- ORDER BY c.oid::regclass::text
	563	+ SELECT max(id), coid FROM
	564	+ (SELECT b.id as id, c.oid as coid
	565	+ FROM pg_class c, dbms_stats.relation_stats_backup b
	566	+ WHERE (c.oid = $2 OR $2 IS NULL)
	567	+ AND c.oid = b.relid
	568	+ AND dbms_stats.is_target_relkind(c.relkind)
	569	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	570	+ AND b.id <= $1
	571	+ FOR SHARE) t
	572	+ GROUP BY coid
	573	+ ORDER BY coid::regclass::text
562	574	LOOP
563	575	UPDATE dbms_stats._relation_stats_locked r
564	576	SET relid = b.relid,

		@@ -635,6 +647,9 @@ BEGIN
635	647	AND staattnum = restore_attnum;
636	648	END IF;
637	649	END LOOP;
	650	+EXCEPTION
	651	+ WHEN unique_violation THEN
	652	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
638	653	END;
639	654	$$
640	655	LANGUAGE plpgsql;

		@@ -644,15 +659,16 @@ CREATE FUNCTION dbms_stats.restore_database_stats(
644	659	) RETURNS SETOF regclass AS
645	660	$$
646	661	SELECT dbms_stats.restore(m.id, m.relid)
647		- FROM (SELECT max(r.id) AS id, r.relid
648		- FROM pg_class c, dbms_stats.relation_stats_backup r,
649		- dbms_stats.backup_history b
650		- WHERE c.oid = r.relid
651		- AND r.id = b.id
652		- AND b.time <= $1
653		- GROUP BY r.relid
654		- ORDER BY r.relid
655		- ) m;
	662	+ FROM (SELECT max(id) AS id, relid
	663	+ FROM (SELECT r.id, r.relid
	664	+ FROM pg_class c, dbms_stats.relation_stats_backup r,
	665	+ dbms_stats.backup_history b
	666	+ WHERE c.oid = r.relid
	667	+ AND r.id = b.id
	668	+ AND b.time <= $1
	669	+ FOR SHARE) t1
	670	+ GROUP BY t1.relid
	671	+ ORDER BY t1.relid) m;
656	672	$$
657	673	LANGUAGE sql STRICT;
658	674

		@@ -671,18 +687,19 @@ BEGIN
671	687
672	688	RETURN QUERY
673	689	SELECT dbms_stats.restore(m.id, m.relid)
674		- FROM (SELECT max(r.id) AS id, r.relid
675		- FROM pg_class c, pg_namespace n,
676		- dbms_stats.relation_stats_backup r,
677		- dbms_stats.backup_history b
678		- WHERE c.oid = r.relid
679		- AND c.relnamespace = n.oid
680		- AND n.nspname = $1
681		- AND r.id = b.id
682		- AND b.time <= $2
683		- GROUP BY r.relid
684		- ORDER BY r.relid
685		- ) m;
	690	+ FROM (SELECT max(id) AS id, relid
	691	+ FROM (SELECT r.id, r.relid
	692	+ FROM pg_class c, pg_namespace n,
	693	+ dbms_stats.relation_stats_backup r,
	694	+ dbms_stats.backup_history b
	695	+ WHERE c.oid = r.relid
	696	+ AND c.relnamespace = n.oid
	697	+ AND n.nspname = $1
	698	+ AND r.id = b.id
	699	+ AND b.time <= $2
	700	+ FOR SHARE) t1
	701	+ GROUP BY t1.relid
	702	+ ORDER BY t1.relid) m;
686	703	END;
687	704	$$
688	705	LANGUAGE plpgsql STRICT;

		@@ -745,8 +762,12 @@ BEGIN
745	762	RAISE EXCEPTION 'backup id % not found', $1;
746	763	END IF;
747	764
748		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
749		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	765	+ /* Lock the backup */
	766	+ PERFORM * from dbms_stats.relation_stats_backup b
	767	+ WHERE id = $1 FOR SHARE;
	768	+
	769	+ /* Locking only _relation_stats_locked is sufficient */
	770	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
750	771
751	772	FOR restore_relid IN
752	773	SELECT b.relid

		@@ -861,15 +882,12 @@ BEGIN
861	882	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
862	883	END IF;
863	884
864		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
865		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
866		-
867	885	/*
868	886	* If we don't have per-table statistics, create new one which has NULL for
869	887	* every statistic value for column_stats_effective.
870	888	*/
871	889	IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
872		- WHERE ru.relid = $1) THEN
	890	+ WHERE ru.relid = $1 FOR SHARE) THEN
873	891	INSERT INTO dbms_stats._relation_stats_locked
874	892	SELECT $1, dbms_stats.relname(nspname, relname),
875	893	NULL, NULL, NULL, NULL, NULL

		@@ -956,6 +974,9 @@ BEGIN
956	974	END IF;
957	975
958	976	RETURN $1;
	977	+EXCEPTION
	978	+ WHEN unique_violation THEN
	979	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
959	980	END;
960	981	$$
961	982	LANGUAGE plpgsql;

		@@ -982,9 +1003,6 @@ BEGIN
982	1003	RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
983	1004	END IF;
984	1005
985		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
986		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
987		-
988	1006	UPDATE dbms_stats._relation_stats_locked r
989	1007	SET relname = dbms_stats.relname(nspname, c.relname),
990	1008	relpages = v.relpages,

		@@ -1092,6 +1110,9 @@ BEGIN
1092	1110	END LOOP;
1093	1111
1094	1112	RETURN $1;
	1113	+EXCEPTION
	1114	+ WHEN unique_violation THEN
	1115	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
1095	1116	END;
1096	1117	$$
1097	1118	LANGUAGE plpgsql;

		@@ -1185,15 +1206,19 @@ BEGIN
1185	1206	IF $1 IS NULL AND $2 IS NOT NULL THEN
1186	1207	RAISE EXCEPTION 'relation required';
1187	1208	END IF;
	1209	+
	1210	+ /*
	1211	+ * Lock the target relation to prevent conflicting with stats lock/restore
	1212	+ */
	1213	+ PERFORM * FROM dbms_stats._relation_stats_locked ru
	1214	+ WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
	1215	+
1188	1216	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1189	1217	WHERE a.attrelid = $1 AND a.attname = $2;
1190	1218	IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1191	1219	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1192	1220	END IF;
1193	1221
1194		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1195		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1196		-
1197	1222	DELETE FROM dbms_stats._column_stats_locked
1198	1223	WHERE (starelid = $1 OR $1 IS NULL)
1199	1224	AND (staattnum = set_attnum OR $2 IS NULL);

		@@ -1222,8 +1247,7 @@ $$
1222	1247	DECLARE
1223	1248	unlock_id int8;
1224	1249	BEGIN
1225		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1226		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1250	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
1227	1251
1228	1252	FOR unlock_id IN
1229	1253	SELECT relid

		@@ -1252,16 +1276,14 @@ BEGIN
1252	1276	RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
1253	1277	END IF;
1254	1278
1255		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1256		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1257		-
1258	1279	FOR unlock_id IN
1259		- SELECT relid
1260		- FROM dbms_stats._relation_stats_locked, pg_class c, pg_namespace n
	1280	+ SELECT r.relid
	1281	+ FROM dbms_stats._relation_stats_locked r, pg_class c, pg_namespace n
1261	1282	WHERE relid = c.oid
1262	1283	AND c.relnamespace = n.oid
1263	1284	AND n.nspname = $1
1264	1285	ORDER BY relid
	1286	+ FOR UPDATE
1265	1287	LOOP
1266	1288	DELETE FROM dbms_stats._relation_stats_locked
1267	1289	WHERE relid = unlock_id;

		@@ -1274,10 +1296,6 @@ LANGUAGE plpgsql STRICT;
1274	1296	CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
1275	1297	RETURNS SETOF regclass AS
1276	1298	$$
1277		-
1278		-LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1279		-LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1280		-
1281	1299	DELETE FROM dbms_stats._relation_stats_locked
1282	1300	WHERE relid = $1
1283	1301	RETURNING relid::regclass

		@@ -1289,10 +1307,6 @@ CREATE FUNCTION dbms_stats.unlock_table_stats(
1289	1307	tablename text
1290	1308	) RETURNS SETOF regclass AS
1291	1309	$$
1292		-
1293		-LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1294		-LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1295		-
1296	1310	DELETE FROM dbms_stats._relation_stats_locked
1297	1311	WHERE relid = dbms_stats.relname($1, $2)::regclass
1298	1312	RETURNING relid::regclass

		@@ -1313,12 +1327,14 @@ BEGIN
1313	1327	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1314	1328	END IF;
1315	1329
1316		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1317		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1330	+ /* Lock the locked table stats */
	1331	+ PERFORM * from dbms_stats.relation_stats_locked r
	1332	+ WHERE r.relid = $1 FOR SHARE;
	1333	+
	1334	+ DELETE FROM dbms_stats._column_stats_locked
	1335	+ WHERE starelid = $1
	1336	+ AND staattnum = set_attnum;
1318	1337
1319		- DELETE FROM dbms_stats._column_stats_locked
1320		- WHERE starelid = $1
1321		- AND staattnum = set_attnum;
1322	1338	RETURN QUERY
1323	1339	SELECT $1;
1324	1340	END;

		@@ -1341,12 +1357,14 @@ BEGIN
1341	1357	RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
1342	1358	END IF;
1343	1359
1344		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1345		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1360	+ /* Lock the locked table stats */
	1361	+ PERFORM * from dbms_stats.relation_stats_locked r
	1362	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	1363	+
	1364	+ DELETE FROM dbms_stats._column_stats_locked
	1365	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	1366	+ AND staattnum = set_attnum;
1346	1367
1347		- DELETE FROM dbms_stats._column_stats_locked
1348		- WHERE starelid = dbms_stats.relname($1, $2)::regclass
1349		- AND staattnum = set_attnum;
1350	1368	RETURN QUERY
1351	1369	SELECT dbms_stats.relname($1, $2)::regclass;
1352	1370	END;

		@@ -1442,11 +1460,8 @@ BEGIN
1442	1460	RAISE EXCEPTION 'NULL is not allowed as the second parameter';
1443	1461	END IF;
1444	1462
1445		- LOCK dbms_stats.backup_history IN SHARE UPDATE EXCLUSIVE MODE;
1446		- LOCK dbms_stats.relation_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1447		- LOCK dbms_stats.column_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1448		-
1449		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
	1463	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	1464	+ WHERE id = $1 FOR UPDATE) THEN
1450	1465	RAISE EXCEPTION 'backup id % not found', $1;
1451	1466	END IF;
1452	1467	IF NOT $2 AND NOT EXISTS(SELECT *

		@@ -1461,7 +1476,7 @@ BEGIN
1461	1476	FOR todelete IN
1462	1477	SELECT * FROM dbms_stats.backup_history
1463	1478	WHERE id <= $1
1464		- ORDER BY id
	1479	+ ORDER BY id FOR UPDATE
1465	1480	LOOP
1466	1481	DELETE FROM dbms_stats.backup_history
1467	1482	WHERE id = todelete.id;

		@@ -1483,9 +1498,6 @@ DECLARE
1483	1498	clean_inherit bool;
1484	1499	clean_rel_col text;
1485	1500	BEGIN
1486		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1487		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1488		-
1489	1501	-- We don't have to check that table-level dummy statistics of the table
1490	1502	-- exists here, because the foreign key constraints defined on column-level
1491	1503	-- dummy static table ensures that.

		@@ -1500,6 +1512,7 @@ BEGIN
1500	1512	WHERE a.attrelid = v.starelid
1501	1513	AND a.attnum = v.staattnum
1502	1514	AND a.attisdropped = false
	1515	+ FOR UPDATE
1503	1516	)
1504	1517	LOOP
1505	1518	DELETE FROM dbms_stats._column_stats_locked

--- a/ext_scripts/pg_dbms_stats--1.3.3-9.3.sql

+++ b/ext_scripts/pg_dbms_stats--1.3.3-9.3.sql

		@@ -336,6 +336,10 @@ CREATE FUNCTION dbms_stats.backup(
336	336	attnum int2
337	337	) RETURNS int8 AS
338	338	$$
	339	+/* Lock the backup id */
	340	+SELECT * from dbms_stats.backup_history
	341	+ WHERE id = $1 FOR UPDATE;
	342	+
339	343	INSERT INTO dbms_stats.relation_stats_backup
340	344	SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
341	345	v.curpages, v.last_analyze, v.last_autoanalyze

		@@ -380,7 +384,7 @@ BEGIN
380	384	END IF;
381	385	IF $1 IS NOT NULL THEN
382	386	SELECT relkind INTO backup_relkind
383		- FROM pg_catalog.pg_class WHERE oid = $1;
	387	+ FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
384	388	IF NOT FOUND THEN
385	389	RAISE EXCEPTION 'relation "%" not found', $1;
386	390	END IF;

		@@ -394,7 +398,7 @@ BEGIN
394	398	END IF;
395	399	IF $2 IS NOT NULL THEN
396	400	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
397		- WHERE a.attrelid = $1 AND a.attname = $2;
	401	+ WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
398	402	IF set_attnum IS NULL THEN
399	403	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
400	404	END IF;

		@@ -433,7 +437,8 @@ $$
433	437	DECLARE
434	438	backup_id int8;
435	439	BEGIN
436		- IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	440	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
	441	+ THEN
437	442	RAISE EXCEPTION 'schema "%" not found', $1;
438	443	END IF;
439	444	IF dbms_stats.is_system_schema($1) THEN

		@@ -523,15 +528,18 @@ BEGIN
523	528	IF $2 IS NULL AND $3 IS NOT NULL THEN
524	529	RAISE EXCEPTION 'relation required';
525	530	END IF;
526		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id <= $1) THEN
	531	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	532	+ WHERE id <= $1 FOR SHARE) THEN
527	533	RAISE EXCEPTION 'backup id % not found', $1;
528	534	END IF;
529	535	IF $2 IS NOT NULL THEN
530		- IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class WHERE oid = $2) THEN
	536	+ IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
	537	+ WHERE oid = $2 FOR SHARE) THEN
531	538	RAISE EXCEPTION 'relation "%" not found', $2;
532	539	END IF;
	540	+ -- Grabbing all backups for the relation which is not used in restore.
533	541	IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
534		- WHERE b.id <= $1 AND b.relid = $2) THEN
	542	+ WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
535	543	RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
536	544	END IF;
537	545	IF $3 IS NOT NULL THEN

		@@ -544,21 +552,25 @@ BEGIN
544	552	RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before',$3, $2, $1;
545	553	END IF;
546	554	END IF;
	555	+ PERFORM * FROM dbms_stats._relation_stats_locked r
	556	+ WHERE r.relid = $2 FOR UPDATE;
	557	+ ELSE
	558	+ /* Lock the whole relation stats if relation is not specified.*/
	559	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
547	560	END IF;
548	561
549		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
550		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
551		-
552	562	FOR restore_id, restore_relid IN
553		- SELECT max(b.id), c.oid
554		- FROM pg_class c, dbms_stats.relation_stats_backup b
555		- WHERE (c.oid = $2 OR $2 IS NULL)
556		- AND c.oid = b.relid
557		- AND dbms_stats.is_target_relkind(c.relkind)
558		- AND NOT dbms_stats.is_system_catalog(c.oid)
559		- AND b.id <= $1
560		- GROUP BY c.oid
561		- ORDER BY c.oid::regclass::text
	563	+ SELECT max(id), coid FROM
	564	+ (SELECT b.id as id, c.oid as coid
	565	+ FROM pg_class c, dbms_stats.relation_stats_backup b
	566	+ WHERE (c.oid = $2 OR $2 IS NULL)
	567	+ AND c.oid = b.relid
	568	+ AND dbms_stats.is_target_relkind(c.relkind)
	569	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	570	+ AND b.id <= $1
	571	+ FOR SHARE) t
	572	+ GROUP BY coid
	573	+ ORDER BY coid::regclass::text
562	574	LOOP
563	575	UPDATE dbms_stats._relation_stats_locked r
564	576	SET relid = b.relid,

		@@ -635,6 +647,9 @@ BEGIN
635	647	AND staattnum = restore_attnum;
636	648	END IF;
637	649	END LOOP;
	650	+EXCEPTION
	651	+ WHEN unique_violation THEN
	652	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
638	653	END;
639	654	$$
640	655	LANGUAGE plpgsql;

		@@ -644,15 +659,16 @@ CREATE FUNCTION dbms_stats.restore_database_stats(
644	659	) RETURNS SETOF regclass AS
645	660	$$
646	661	SELECT dbms_stats.restore(m.id, m.relid)
647		- FROM (SELECT max(r.id) AS id, r.relid
648		- FROM pg_class c, dbms_stats.relation_stats_backup r,
649		- dbms_stats.backup_history b
650		- WHERE c.oid = r.relid
651		- AND r.id = b.id
652		- AND b.time <= $1
653		- GROUP BY r.relid
654		- ORDER BY r.relid
655		- ) m;
	662	+ FROM (SELECT max(id) AS id, relid
	663	+ FROM (SELECT r.id, r.relid
	664	+ FROM pg_class c, dbms_stats.relation_stats_backup r,
	665	+ dbms_stats.backup_history b
	666	+ WHERE c.oid = r.relid
	667	+ AND r.id = b.id
	668	+ AND b.time <= $1
	669	+ FOR SHARE) t1
	670	+ GROUP BY t1.relid
	671	+ ORDER BY t1.relid) m;
656	672	$$
657	673	LANGUAGE sql STRICT;
658	674

		@@ -671,18 +687,19 @@ BEGIN
671	687
672	688	RETURN QUERY
673	689	SELECT dbms_stats.restore(m.id, m.relid)
674		- FROM (SELECT max(r.id) AS id, r.relid
675		- FROM pg_class c, pg_namespace n,
676		- dbms_stats.relation_stats_backup r,
677		- dbms_stats.backup_history b
678		- WHERE c.oid = r.relid
679		- AND c.relnamespace = n.oid
680		- AND n.nspname = $1
681		- AND r.id = b.id
682		- AND b.time <= $2
683		- GROUP BY r.relid
684		- ORDER BY r.relid
685		- ) m;
	690	+ FROM (SELECT max(id) AS id, relid
	691	+ FROM (SELECT r.id, r.relid
	692	+ FROM pg_class c, pg_namespace n,
	693	+ dbms_stats.relation_stats_backup r,
	694	+ dbms_stats.backup_history b
	695	+ WHERE c.oid = r.relid
	696	+ AND c.relnamespace = n.oid
	697	+ AND n.nspname = $1
	698	+ AND r.id = b.id
	699	+ AND b.time <= $2
	700	+ FOR SHARE) t1
	701	+ GROUP BY t1.relid
	702	+ ORDER BY t1.relid) m;
686	703	END;
687	704	$$
688	705	LANGUAGE plpgsql STRICT;

		@@ -745,8 +762,12 @@ BEGIN
745	762	RAISE EXCEPTION 'backup id % not found', $1;
746	763	END IF;
747	764
748		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
749		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	765	+ /* Lock the backup */
	766	+ PERFORM * from dbms_stats.relation_stats_backup b
	767	+ WHERE id = $1 FOR SHARE;
	768	+
	769	+ /* Locking only _relation_stats_locked is sufficient */
	770	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
750	771
751	772	FOR restore_relid IN
752	773	SELECT b.relid

		@@ -861,15 +882,12 @@ BEGIN
861	882	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
862	883	END IF;
863	884
864		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
865		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
866		-
867	885	/*
868	886	* If we don't have per-table statistics, create new one which has NULL for
869	887	* every statistic value for column_stats_effective.
870	888	*/
871	889	IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
872		- WHERE ru.relid = $1) THEN
	890	+ WHERE ru.relid = $1 FOR SHARE) THEN
873	891	INSERT INTO dbms_stats._relation_stats_locked
874	892	SELECT $1, dbms_stats.relname(nspname, relname),
875	893	NULL, NULL, NULL, NULL, NULL

		@@ -956,6 +974,9 @@ BEGIN
956	974	END IF;
957	975
958	976	RETURN $1;
	977	+EXCEPTION
	978	+ WHEN unique_violation THEN
	979	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
959	980	END;
960	981	$$
961	982	LANGUAGE plpgsql;

		@@ -982,9 +1003,6 @@ BEGIN
982	1003	RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
983	1004	END IF;
984	1005
985		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
986		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
987		-
988	1006	UPDATE dbms_stats._relation_stats_locked r
989	1007	SET relname = dbms_stats.relname(nspname, c.relname),
990	1008	relpages = v.relpages,

		@@ -1092,6 +1110,9 @@ BEGIN
1092	1110	END LOOP;
1093	1111
1094	1112	RETURN $1;
	1113	+EXCEPTION
	1114	+ WHEN unique_violation THEN
	1115	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
1095	1116	END;
1096	1117	$$
1097	1118	LANGUAGE plpgsql;

		@@ -1185,15 +1206,19 @@ BEGIN
1185	1206	IF $1 IS NULL AND $2 IS NOT NULL THEN
1186	1207	RAISE EXCEPTION 'relation required';
1187	1208	END IF;
	1209	+
	1210	+ /*
	1211	+ * Lock the target relation to prevent conflicting with stats lock/restore
	1212	+ */
	1213	+ PERFORM * FROM dbms_stats._relation_stats_locked ru
	1214	+ WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
	1215	+
1188	1216	SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1189	1217	WHERE a.attrelid = $1 AND a.attname = $2;
1190	1218	IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1191	1219	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1192	1220	END IF;
1193	1221
1194		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1195		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1196		-
1197	1222	DELETE FROM dbms_stats._column_stats_locked
1198	1223	WHERE (starelid = $1 OR $1 IS NULL)
1199	1224	AND (staattnum = set_attnum OR $2 IS NULL);

		@@ -1222,8 +1247,7 @@ $$
1222	1247	DECLARE
1223	1248	unlock_id int8;
1224	1249	BEGIN
1225		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1226		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1250	+ LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
1227	1251
1228	1252	FOR unlock_id IN
1229	1253	SELECT relid

		@@ -1252,16 +1276,14 @@ BEGIN
1252	1276	RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
1253	1277	END IF;
1254	1278
1255		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1256		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1257		-
1258	1279	FOR unlock_id IN
1259		- SELECT relid
1260		- FROM dbms_stats._relation_stats_locked, pg_class c, pg_namespace n
	1280	+ SELECT r.relid
	1281	+ FROM dbms_stats._relation_stats_locked r, pg_class c, pg_namespace n
1261	1282	WHERE relid = c.oid
1262	1283	AND c.relnamespace = n.oid
1263	1284	AND n.nspname = $1
1264	1285	ORDER BY relid
	1286	+ FOR UPDATE
1265	1287	LOOP
1266	1288	DELETE FROM dbms_stats._relation_stats_locked
1267	1289	WHERE relid = unlock_id;

		@@ -1274,10 +1296,6 @@ LANGUAGE plpgsql STRICT;
1274	1296	CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
1275	1297	RETURNS SETOF regclass AS
1276	1298	$$
1277		-
1278		-LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1279		-LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1280		-
1281	1299	DELETE FROM dbms_stats._relation_stats_locked
1282	1300	WHERE relid = $1
1283	1301	RETURNING relid::regclass

		@@ -1289,10 +1307,6 @@ CREATE FUNCTION dbms_stats.unlock_table_stats(
1289	1307	tablename text
1290	1308	) RETURNS SETOF regclass AS
1291	1309	$$
1292		-
1293		-LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1294		-LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1295		-
1296	1310	DELETE FROM dbms_stats._relation_stats_locked
1297	1311	WHERE relid = dbms_stats.relname($1, $2)::regclass
1298	1312	RETURNING relid::regclass

		@@ -1313,12 +1327,14 @@ BEGIN
1313	1327	RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1314	1328	END IF;
1315	1329
1316		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1317		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1330	+ /* Lock the locked table stats */
	1331	+ PERFORM * from dbms_stats.relation_stats_locked r
	1332	+ WHERE r.relid = $1 FOR SHARE;
	1333	+
	1334	+ DELETE FROM dbms_stats._column_stats_locked
	1335	+ WHERE starelid = $1
	1336	+ AND staattnum = set_attnum;
1318	1337
1319		- DELETE FROM dbms_stats._column_stats_locked
1320		- WHERE starelid = $1
1321		- AND staattnum = set_attnum;
1322	1338	RETURN QUERY
1323	1339	SELECT $1;
1324	1340	END;

		@@ -1341,12 +1357,14 @@ BEGIN
1341	1357	RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
1342	1358	END IF;
1343	1359
1344		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1345		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
	1360	+ /* Lock the locked table stats */
	1361	+ PERFORM * from dbms_stats.relation_stats_locked r
	1362	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	1363	+
	1364	+ DELETE FROM dbms_stats._column_stats_locked
	1365	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	1366	+ AND staattnum = set_attnum;
1346	1367
1347		- DELETE FROM dbms_stats._column_stats_locked
1348		- WHERE starelid = dbms_stats.relname($1, $2)::regclass
1349		- AND staattnum = set_attnum;
1350	1368	RETURN QUERY
1351	1369	SELECT dbms_stats.relname($1, $2)::regclass;
1352	1370	END;

		@@ -1441,11 +1459,8 @@ BEGIN
1441	1459	RAISE EXCEPTION 'NULL is not allowed as the second parameter';
1442	1460	END IF;
1443	1461
1444		- LOCK dbms_stats.backup_history IN SHARE UPDATE EXCLUSIVE MODE;
1445		- LOCK dbms_stats.relation_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1446		- LOCK dbms_stats.column_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1447		-
1448		- IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
	1462	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	1463	+ WHERE id = $1 FOR UPDATE) THEN
1449	1464	RAISE EXCEPTION 'backup id % not found', $1;
1450	1465	END IF;
1451	1466	IF NOT $2 AND NOT EXISTS(SELECT *

		@@ -1460,7 +1475,7 @@ BEGIN
1460	1475	FOR todelete IN
1461	1476	SELECT * FROM dbms_stats.backup_history
1462	1477	WHERE id <= $1
1463		- ORDER BY id
	1478	+ ORDER BY id FOR UPDATE
1464	1479	LOOP
1465	1480	DELETE FROM dbms_stats.backup_history
1466	1481	WHERE id = todelete.id;

		@@ -1481,9 +1496,6 @@ DECLARE
1481	1496	clean_inherit bool;
1482	1497	clean_rel_col text;
1483	1498	BEGIN
1484		- LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1485		- LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1486		-
1487	1499	-- We don't have to check that table-level dummy statistics of the table
1488	1500	-- exists here, because the foreign key constraints defined on column-level
1489	1501	-- dummy static table ensures that.

		@@ -1498,6 +1510,7 @@ BEGIN
1498	1510	WHERE a.attrelid = v.starelid
1499	1511	AND a.attnum = v.staattnum
1500	1512	AND a.attisdropped = false
	1513	+ FOR UPDATE
1501	1514	)
1502	1515	LOOP
1503	1516	DELETE FROM dbms_stats._column_stats_locked

pg_dbms_stats Fork

提交

標籤

Frequently used words (click to add to your profile)

Commit MetaInfo

Log Message

Change Summary

差異

pg_dbms_stats
Fork