public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 08/21] *an exclusive 25+ messages / 3 participants [nested] [flat]
* [PATCH 08/21] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --jI8keyz6grp/JLjh Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH 08/18] *an exclusive @ 2021-02-06 21:13 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Justin Pryzby @ 2021-02-06 21:13 UTC (permalink / raw) 3c84046490bed3c22e0873dc6ba492e02b8b9051 --- doc/src/sgml/ref/drop_index.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 85cf23bca2..b6d2c2014f 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -45,7 +45,7 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r <para> Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal <command>DROP INDEX</command> - acquires exclusive lock on the table, blocking other accesses until the + acquires an exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. </para> -- 2.17.0 --lc9FT7cWel8HagAv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0009-Doc-review-for-psql-dX.patch" ^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: cataloguing NOT NULL constraints @ 2023-03-29 14:46 Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 25+ messages in thread From: Peter Eisentraut @ 2023-03-29 14:46 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; Pg Hackers <[email protected]> On 27.03.23 15:55, Peter Eisentraut wrote: > The information schema should be updated. I think the following views: > > - CHECK_CONSTRAINTS > - CONSTRAINT_COLUMN_USAGE > - DOMAIN_CONSTRAINTS > - TABLE_CONSTRAINTS > > It looks like these have no test coverage; maybe that could be addressed > at the same time. Here are patches for this. I haven't included the expected files for the tests; this should be checked again that output is correct or the changes introduced by this patch set are as expected. The reason we didn't have tests for this before was probably in part because the information schema made up names for not-null constraints involving OIDs, so the test wouldn't have been stable. Feel free to integrate this, or we can add it on afterwards. From 8cb5f81176e06dded88d49179debddab992ff1ce Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 29 Mar 2023 16:42:16 +0200 Subject: [PATCH 1/2] Add tests for information schema constraints views --- src/test/regress/sql/constraints.sql | 16 ++++++++++++++++ src/test/regress/sql/domain.sql | 24 ++++++++++++++++++++++++ 2 files changed, 40 insertions(+) diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index ae427d25e9..0c8f681b51 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -589,6 +589,22 @@ CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL)); ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk; \d notnull_tbl3 +-- +-- Information schema +-- + +SELECT * FROM information_schema.check_constraints + WHERE constraint_schema = 'public' + ORDER BY constraint_name; + +SELECT * FROM information_schema.constraint_column_usage + WHERE constraint_schema = 'public' + ORDER BY table_name, column_name, constraint_name; + +SELECT * FROM information_schema.table_constraints + WHERE constraint_schema = 'public' + ORDER BY constraint_name; + -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index 75703940f9..3096b377eb 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -812,3 +812,27 @@ CREATE TABLE thethings (stuff things); alter domain testdomain1 rename constraint unsigned to unsigned_foo; alter domain testdomain1 drop constraint unsigned_foo; drop domain testdomain1; + + +-- +-- Information schema +-- + +SELECT * FROM information_schema.column_domain_usage + WHERE domain_schema = 'public' AND table_schema = 'public' + ORDER BY domain_name; + +SELECT * FROM information_schema.domain_constraints + WHERE domain_schema = 'public' + ORDER BY constraint_name; + +SELECT * FROM information_schema.domains + WHERE domain_schema = 'public' + ORDER BY domain_name; + +SELECT * FROM information_schema.check_constraints + WHERE (constraint_schema, constraint_name) + IN (SELECT constraint_schema, constraint_name + FROM information_schema.domain_constraints + WHERE domain_schema = 'public') + ORDER BY constraint_name; -- 2.40.0 From 093905fb9bddd073b93128893ecceae5da6801d5 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 29 Mar 2023 16:42:37 +0200 Subject: [PATCH 2/2] Update information schema for catalogued not-null constraints --- src/backend/catalog/information_schema.sql | 57 ++-------------------- 1 file changed, 5 insertions(+), 52 deletions(-) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 0555e9bc03..414fd0c6ba 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -435,31 +435,15 @@ CREATE VIEW check_constraints AS SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, CAST(rs.nspname AS sql_identifier) AS constraint_schema, CAST(con.conname AS sql_identifier) AS constraint_name, - CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data) + CAST(CASE con.contype WHEN 'c' THEN left(substring(pg_get_constraintdef(con.oid) from 8), -1) + WHEN 'n' THEN substring(pg_get_constraintdef(con.oid) from 10) || ' IS NOT NULL' END AS character_data) AS check_clause FROM pg_constraint con LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace) LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid) LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid) WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE') - AND con.contype = 'c' - - UNION - -- not-null constraints - - SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, - CAST(n.nspname AS sql_identifier) AS constraint_schema, - CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX - CAST(a.attname || ' IS NOT NULL' AS character_data) - AS check_clause - FROM pg_namespace n, pg_class r, pg_attribute a - WHERE n.oid = r.relnamespace - AND r.oid = a.attrelid - AND a.attnum > 0 - AND NOT a.attisdropped - AND a.attnotnull - AND r.relkind IN ('r', 'p') - AND pg_has_role(r.relowner, 'USAGE'); + AND con.contype IN ('c', 'n'); GRANT SELECT ON check_constraints TO PUBLIC; @@ -822,7 +806,7 @@ CREATE VIEW constraint_column_usage AS AND d.classid = 'pg_catalog.pg_constraint'::regclass AND d.objid = c.oid AND c.connamespace = nc.oid - AND c.contype = 'c' + AND c.contype IN ('c', 'n') AND r.relkind IN ('r', 'p') AND NOT a.attisdropped @@ -1832,6 +1816,7 @@ CREATE VIEW table_constraints AS CAST(r.relname AS sql_identifier) AS table_name, CAST( CASE c.contype WHEN 'c' THEN 'CHECK' + WHEN 'n' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END @@ -1856,38 +1841,6 @@ CREATE VIEW table_constraints AS AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints AND r.relkind IN ('r', 'p') AND (NOT pg_is_other_temp_schema(nr.oid)) - AND (pg_has_role(r.relowner, 'USAGE') - -- SELECT privilege omitted, per SQL standard - OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') - OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') ) - - UNION ALL - - -- not-null constraints - - SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, - CAST(nr.nspname AS sql_identifier) AS constraint_schema, - CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX - CAST(current_database() AS sql_identifier) AS table_catalog, - CAST(nr.nspname AS sql_identifier) AS table_schema, - CAST(r.relname AS sql_identifier) AS table_name, - CAST('CHECK' AS character_data) AS constraint_type, - CAST('NO' AS yes_or_no) AS is_deferrable, - CAST('NO' AS yes_or_no) AS initially_deferred, - CAST('YES' AS yes_or_no) AS enforced, - CAST(NULL AS yes_or_no) AS nulls_distinct - - FROM pg_namespace nr, - pg_class r, - pg_attribute a - - WHERE nr.oid = r.relnamespace - AND r.oid = a.attrelid - AND a.attnotnull - AND a.attnum > 0 - AND NOT a.attisdropped - AND r.relkind IN ('r', 'p') - AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') -- SELECT privilege omitted, per SQL standard OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') -- 2.40.0 Attachments: [text/plain] 0001-Add-tests-for-information-schema-constraints.patch.nocfbot (2.3K, ../../[email protected]/2-0001-Add-tests-for-information-schema-constraints.patch.nocfbot) download | inline diff: From 8cb5f81176e06dded88d49179debddab992ff1ce Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 29 Mar 2023 16:42:16 +0200 Subject: [PATCH 1/2] Add tests for information schema constraints views --- src/test/regress/sql/constraints.sql | 16 ++++++++++++++++ src/test/regress/sql/domain.sql | 24 ++++++++++++++++++++++++ 2 files changed, 40 insertions(+) diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index ae427d25e9..0c8f681b51 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -589,6 +589,22 @@ CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL)); ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk; \d notnull_tbl3 +-- +-- Information schema +-- + +SELECT * FROM information_schema.check_constraints + WHERE constraint_schema = 'public' + ORDER BY constraint_name; + +SELECT * FROM information_schema.constraint_column_usage + WHERE constraint_schema = 'public' + ORDER BY table_name, column_name, constraint_name; + +SELECT * FROM information_schema.table_constraints + WHERE constraint_schema = 'public' + ORDER BY constraint_name; + -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index 75703940f9..3096b377eb 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -812,3 +812,27 @@ CREATE TABLE thethings (stuff things); alter domain testdomain1 rename constraint unsigned to unsigned_foo; alter domain testdomain1 drop constraint unsigned_foo; drop domain testdomain1; + + +-- +-- Information schema +-- + +SELECT * FROM information_schema.column_domain_usage + WHERE domain_schema = 'public' AND table_schema = 'public' + ORDER BY domain_name; + +SELECT * FROM information_schema.domain_constraints + WHERE domain_schema = 'public' + ORDER BY constraint_name; + +SELECT * FROM information_schema.domains + WHERE domain_schema = 'public' + ORDER BY domain_name; + +SELECT * FROM information_schema.check_constraints + WHERE (constraint_schema, constraint_name) + IN (SELECT constraint_schema, constraint_name + FROM information_schema.domain_constraints + WHERE domain_schema = 'public') + ORDER BY constraint_name; -- 2.40.0 [text/plain] 0002-Update-information-schema-for-catalogued-not.patch.nocfbot (4.9K, ../../[email protected]/3-0002-Update-information-schema-for-catalogued-not.patch.nocfbot) download | inline diff: From 093905fb9bddd073b93128893ecceae5da6801d5 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 29 Mar 2023 16:42:37 +0200 Subject: [PATCH 2/2] Update information schema for catalogued not-null constraints --- src/backend/catalog/information_schema.sql | 57 ++-------------------- 1 file changed, 5 insertions(+), 52 deletions(-) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 0555e9bc03..414fd0c6ba 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -435,31 +435,15 @@ CREATE VIEW check_constraints AS SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, CAST(rs.nspname AS sql_identifier) AS constraint_schema, CAST(con.conname AS sql_identifier) AS constraint_name, - CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data) + CAST(CASE con.contype WHEN 'c' THEN left(substring(pg_get_constraintdef(con.oid) from 8), -1) + WHEN 'n' THEN substring(pg_get_constraintdef(con.oid) from 10) || ' IS NOT NULL' END AS character_data) AS check_clause FROM pg_constraint con LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace) LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid) LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid) WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE') - AND con.contype = 'c' - - UNION - -- not-null constraints - - SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, - CAST(n.nspname AS sql_identifier) AS constraint_schema, - CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX - CAST(a.attname || ' IS NOT NULL' AS character_data) - AS check_clause - FROM pg_namespace n, pg_class r, pg_attribute a - WHERE n.oid = r.relnamespace - AND r.oid = a.attrelid - AND a.attnum > 0 - AND NOT a.attisdropped - AND a.attnotnull - AND r.relkind IN ('r', 'p') - AND pg_has_role(r.relowner, 'USAGE'); + AND con.contype IN ('c', 'n'); GRANT SELECT ON check_constraints TO PUBLIC; @@ -822,7 +806,7 @@ CREATE VIEW constraint_column_usage AS AND d.classid = 'pg_catalog.pg_constraint'::regclass AND d.objid = c.oid AND c.connamespace = nc.oid - AND c.contype = 'c' + AND c.contype IN ('c', 'n') AND r.relkind IN ('r', 'p') AND NOT a.attisdropped @@ -1832,6 +1816,7 @@ CREATE VIEW table_constraints AS CAST(r.relname AS sql_identifier) AS table_name, CAST( CASE c.contype WHEN 'c' THEN 'CHECK' + WHEN 'n' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END @@ -1856,38 +1841,6 @@ CREATE VIEW table_constraints AS AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints AND r.relkind IN ('r', 'p') AND (NOT pg_is_other_temp_schema(nr.oid)) - AND (pg_has_role(r.relowner, 'USAGE') - -- SELECT privilege omitted, per SQL standard - OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') - OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') ) - - UNION ALL - - -- not-null constraints - - SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, - CAST(nr.nspname AS sql_identifier) AS constraint_schema, - CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX - CAST(current_database() AS sql_identifier) AS table_catalog, - CAST(nr.nspname AS sql_identifier) AS table_schema, - CAST(r.relname AS sql_identifier) AS table_name, - CAST('CHECK' AS character_data) AS constraint_type, - CAST('NO' AS yes_or_no) AS is_deferrable, - CAST('NO' AS yes_or_no) AS initially_deferred, - CAST('YES' AS yes_or_no) AS enforced, - CAST(NULL AS yes_or_no) AS nulls_distinct - - FROM pg_namespace nr, - pg_class r, - pg_attribute a - - WHERE nr.oid = r.relnamespace - AND r.oid = a.attrelid - AND a.attnotnull - AND a.attnum > 0 - AND NOT a.attisdropped - AND r.relkind IN ('r', 'p') - AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') -- SELECT privilege omitted, per SQL standard OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') -- 2.40.0 ^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: cataloguing NOT NULL constraints @ 2023-08-31 10:02 Alvaro Herrera <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 25+ messages in thread From: Alvaro Herrera @ 2023-08-31 10:02 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: Pg Hackers <[email protected]> On 2023-Mar-29, Peter Eisentraut wrote: > On 27.03.23 15:55, Peter Eisentraut wrote: > > The information schema should be updated. I think the following views: > > > > - CHECK_CONSTRAINTS > > - CONSTRAINT_COLUMN_USAGE > > - DOMAIN_CONSTRAINTS > > - TABLE_CONSTRAINTS > > > > It looks like these have no test coverage; maybe that could be addressed > > at the same time. > > Here are patches for this. I haven't included the expected files for the > tests; this should be checked again that output is correct or the changes > introduced by this patch set are as expected. > > The reason we didn't have tests for this before was probably in part because > the information schema made up names for not-null constraints involving > OIDs, so the test wouldn't have been stable. > > Feel free to integrate this, or we can add it on afterwards. I'm eyeing patch 0002 here. I noticed that in view check_constraints it defines the not-null constraint definition as substrings over the pg_get_constraintdef() function[q1], so I wondered whether it might be better to join to pg_attribute instead. I see two options: 1. add a scalar subselect in the select list for each constraint [q2] 2. add a LEFT JOIN to pg_attribute to the main FROM list [q3] ON con.conrelid=att.attrelid AND con.conkey[1] = con.attrelid With just the regression test tables in place, these forms are all pretty much the same in execution time. I then created 20k tables with 6 columns each and NOT NULL constraint on each column[4]. That's not a huge amount but it's credible for a medium-size database with a bunch of partitions (it's amazing what passes for a medium-size database these days). I was surprised to find out that q3 (~130ms) is three times faster than q2 (~390ms), which is in turn more than twice faster than your proposed q1 (~870ms). So unless you have another reason to prefer it, I think we should use q3 here. In constraint_column_usage, you're adding a relkind to the catalog scan that goes through pg_depend for CHECK constraints. Here we can rely on a simple conkey[1] check and a separate UNION ALL arm[q5]; this is also faster when there are many tables. The third view definition looks ok. It's certainly very nice to be able to delete XXX comments there. [q1] SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, rs.nspname::information_schema.sql_identifier AS constraint_schema, con.conname::information_schema.sql_identifier AS constraint_name, CASE con.contype WHEN 'c'::"char" THEN "left"(SUBSTRING(pg_get_constraintdef(con.oid) FROM 8), '-1'::integer) WHEN 'n'::"char" THEN SUBSTRING(pg_get_constraintdef(con.oid) FROM 10) || ' IS NOT NULL'::text ELSE NULL::text END::information_schema.character_data AS check_clause FROM pg_constraint con LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace LEFT JOIN pg_class c ON c.oid = con.conrelid LEFT JOIN pg_type t ON t.oid = con.contypid WHERE pg_has_role(COALESCE(c.relowner, t.typowner), 'USAGE'::text) AND (con.contype = ANY (ARRAY['c'::"char", 'n'::"char"])); [q2] SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, rs.nspname::information_schema.sql_identifier AS constraint_schema, con.conname::information_schema.sql_identifier AS constraint_name, CASE con.contype WHEN 'c'::"char" THEN "left"(SUBSTRING(pg_get_constraintdef(con.oid) FROM 8), '-1'::integer) WHEN 'n'::"char" THEN FORMAT('CHECK (%s IS NOT NULL)', (SELECT attname FROM pg_attribute WHERE attrelid = conrelid AND attnum = conkey[1])) ELSE NULL::text END::information_schema.character_data AS check_clause FROM pg_constraint con LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace LEFT JOIN pg_class c ON c.oid = con.conrelid LEFT JOIN pg_type t ON t.oid = con.contypid WHERE pg_has_role(COALESCE(c.relowner, t.typowner), 'USAGE'::text) AND (con.contype = ANY (ARRAY['c'::"char", 'n'::"char"])); [q3] SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, rs.nspname::information_schema.sql_identifier AS constraint_schema, con.conname::information_schema.sql_identifier AS constraint_name, CASE con.contype WHEN 'c'::"char" THEN "left"(SUBSTRING(pg_get_constraintdef(con.oid) FROM 8), '-1'::integer) WHEN 'n'::"char" THEN FORMAT('CHECK (%s IS NOT NULL)', at.attname) ELSE NULL::text END::information_schema.character_data AS check_clause FROM pg_constraint con LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace LEFT JOIN pg_class c ON c.oid = con.conrelid LEFT JOIN pg_type t ON t.oid = con.contypid LEFT JOIN pg_attribute at ON (con.conrelid = at.attrelid AND con.conkey[1] = at.attnum) WHERE pg_has_role(COALESCE(c.relowner, t.typowner), 'USAGE'::text) AND (con.contype = ANY (ARRAY['c'::"char", 'n'::"char"])); [4] do $$ begin for i in 0 .. 20000 loop execute format('create table t_%s (a1 int not null, a2 int not null, a3 int not null, a4 int not null, a5 int not null, a6 int not null);', i); if i % 1000 = 0 then commit; end if; end loop; end $$; [q5] SELECT CAST(current_database() AS sql_identifier) AS table_catalog, CAST(tblschema AS sql_identifier) AS table_schema, CAST(tblname AS sql_identifier) AS table_name, CAST(colname AS sql_identifier) AS column_name, CAST(current_database() AS sql_identifier) AS constraint_catalog, CAST(cstrschema AS sql_identifier) AS constraint_schema, CAST(cstrname AS sql_identifier) AS constraint_name FROM ( /* check constraints */ SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND d.refclassid = 'pg_catalog.pg_class'::regclass AND d.refobjid = r.oid AND d.refobjsubid = a.attnum AND d.classid = 'pg_catalog.pg_constraint'::regclass AND d.objid = c.oid AND c.connamespace = nc.oid AND c.contype = 'c' AND r.relkind IN ('r', 'p') AND NOT a.attisdropped UNION ALL /* not-null constraints */ SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND r.oid = c.conrelid AND a.attnum = c.conkey[1] AND c.connamespace = nc.oid AND c.contype = 'n' AND r.relkind in ('r', 'p') AND not a.attisdropped UNION ALL /* unique/primary key/foreign key constraints */ SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND nc.oid = c.connamespace AND r.oid = CASE c.contype WHEN 'f' THEN c.confrelid ELSE c.conrelid END AND a.attnum = ANY (CASE c.contype WHEN 'f' THEN c.confkey ELSE c.conkey END) AND NOT a.attisdropped AND c.contype IN ('p', 'u', 'f') AND r.relkind IN ('r', 'p') ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname) WHERE pg_has_role(x.tblowner, 'USAGE') ; -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ ^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2023-08-31 10:02 UTC | newest] Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/21] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2021-02-06 21:13 [PATCH 08/18] *an exclusive Justin Pryzby <[email protected]> 2023-03-29 14:46 Re: cataloguing NOT NULL constraints Peter Eisentraut <[email protected]> 2023-08-31 10:02 ` Re: cataloguing NOT NULL constraints Alvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox