On Wed, Nov 25, 2015 at 9:29 AM, Jeff Janes <jeff.ja...@gmail.com> wrote:
>
> I'll prepare a patch for core for the January commitfest, and see if
> it flies. If not, there is always the extension to fall back to.
Here is an updated patch. I've added type and permission checks,
docs, and some regression tests.
Cheers,
Jeff
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 8ef9fce..6cbe11b
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** postgres=# SELECT * FROM pg_xlogfile_nam
*** 17919,17924 ****
--- 17919,17928 ----
<primary>brin_summarize_new_values</primary>
</indexterm>
+ <indexterm>
+ <primary>gin_clean_pending_list</primary>
+ </indexterm>
+
<para>
<xref linkend="functions-admin-index-table"> shows the functions
available for index maintenance tasks.
*************** postgres=# SELECT * FROM pg_xlogfile_nam
*** 17939,17944 ****
--- 17943,17955 ----
<entry><type>integer</type></entry>
<entry>summarize page ranges not already summarized</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>gin_clean_pending_list(<parameter>index_oid</> <type>regclass</>)</function></literal>
+ </entry>
+ <entry><type>bigint</type></entry>
+ <entry>move fast-update pending list entries into main index structure</entry>
+ </row>
</tbody>
</tgroup>
</table>
*************** postgres=# SELECT * FROM pg_xlogfile_nam
*** 17952,17957 ****
--- 17963,17975 ----
into the index.
</para>
+ <para>
+ <function>gin_clean_pending_list</> accepts a GIN index OID argument
+ and moves the fast-update entries from the pending list into the
+ main index structure for that index. It returns the number of pages
+ removed from the pending list.
+ </para>
+
</sect2>
<sect2 id="functions-admin-genfile">
diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml
new file mode 100644
index 262f1e4..c6f19de
*** a/doc/src/sgml/gin.sgml
--- b/doc/src/sgml/gin.sgml
***************
*** 728,734 ****
from the indexed item). As of <productname>PostgreSQL</productname> 8.4,
<acronym>GIN</> is capable of postponing much of this work by inserting
new tuples into a temporary, unsorted list of pending entries.
! When the table is vacuumed, or if the pending list becomes larger than
<xref linkend="guc-gin-pending-list-limit">, the entries are moved to the
main <acronym>GIN</acronym> data structure using the same bulk insert
techniques used during initial index creation. This greatly improves
--- 728,736 ----
from the indexed item). As of <productname>PostgreSQL</productname> 8.4,
<acronym>GIN</> is capable of postponing much of this work by inserting
new tuples into a temporary, unsorted list of pending entries.
! When the table is vacuumed or autoanalyzed, or when
! <function>gin_clean_pending_list(regclass)</function> is called, or if the
! pending list becomes larger than
<xref linkend="guc-gin-pending-list-limit">, the entries are moved to the
main <acronym>GIN</acronym> data structure using the same bulk insert
techniques used during initial index creation. This greatly improves
diff --git a/src/backend/access/gin/ginfast.c b/src/backend/access/gin/ginfast.c
new file mode 100644
index 4bb22fe..373b52d
*** a/src/backend/access/gin/ginfast.c
--- b/src/backend/access/gin/ginfast.c
***************
*** 24,29 ****
--- 24,30 ----
#include "miscadmin.h"
#include "utils/memutils.h"
#include "utils/rel.h"
+ #include "utils/acl.h"
#include "storage/indexfsm.h"
/* GUC parameter */
*************** ginInsertCleanup(GinState *ginstate,
*** 962,964 ****
--- 963,999 ----
MemoryContextSwitchTo(oldCtx);
MemoryContextDelete(opCtx);
}
+
+ /*
+ * SQL-callable function to clean the insert pending list
+ */
+ Datum
+ gin_clean_pending_list(PG_FUNCTION_ARGS)
+ {
+ Oid indexoid = PG_GETARG_OID(0);
+ Relation indexRel = index_open(indexoid, AccessShareLock);
+ IndexBulkDeleteResult stats;
+ GinState ginstate;
+
+ /* Must be a GIN index */
+ if (indexRel->rd_rel->relkind != RELKIND_INDEX ||
+ indexRel->rd_rel->relam != GIN_AM_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a GIN index",
+ RelationGetRelationName(indexRel))));
+
+ /* User must own the index (comparable to privileges needed for VACUUM) */
+ if (!pg_class_ownercheck(indexoid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(indexRel));
+
+
+ memset(&stats, 0, sizeof(stats));
+ initGinState(&ginstate, indexRel);
+ ginInsertCleanup(&ginstate, false, true, &stats);
+
+ index_close(indexRel, AccessShareLock);
+
+ PG_RETURN_INT64((int64) stats.pages_deleted);
+ }
diff --git a/src/include/access/gin_private.h b/src/include/access/gin_private.h
new file mode 100644
index 5021887..9373ef5
*** a/src/include/access/gin_private.h
--- b/src/include/access/gin_private.h
*************** extern void ginFreeScanKeys(GinScanOpaqu
*** 878,883 ****
--- 878,886 ----
/* ginget.c */
extern Datum gingetbitmap(PG_FUNCTION_ARGS);
+ /* ginfast.c */
+ extern Datum gin_clean_pending_list(PG_FUNCTION_ARGS);
+
/* ginlogic.c */
extern void ginInitConsistentFunction(GinState *ginstate, GinScanKey key);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index d8640db..4f76b84
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 3087 ( gin_extract_ts
*** 4616,4621 ****
--- 4616,4622 ----
DESCR("GIN tsvector support (obsolete)");
DATA(insert OID = 3088 ( gin_tsquery_consistent PGNSP PGUID 12 1 0 0 0 f f f f t f i s 6 0 16 "2281 21 3615 23 2281 2281" _null_ _null_ _null_ _null_ _null_ gin_tsquery_consistent_6args _null_ _null_ _null_ ));
DESCR("GIN tsvector support (obsolete)");
+ DATA(insert OID = 13952 ( gin_clean_pending_list PGNSP PGUID 12 1 0 0 0 f f f f f f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ gin_clean_pending_list _null_ _null_ _null_ ));
DATA(insert OID = 3662 ( tsquery_lt PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "3615 3615" _null_ _null_ _null_ _null_ _null_ tsquery_lt _null_ _null_ _null_ ));
DATA(insert OID = 3663 ( tsquery_le PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "3615 3615" _null_ _null_ _null_ _null_ _null_ tsquery_le _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/gin.out b/src/test/regress/expected/gin.out
new file mode 100644
index c015fe7..cc7601c
*** a/src/test/regress/expected/gin.out
--- b/src/test/regress/expected/gin.out
*************** create table gin_test_tbl(i int4[]);
*** 8,14 ****
--- 8,27 ----
create index gin_test_idx on gin_test_tbl using gin (i) with (fastupdate = on);
insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
+ select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
+ many
+ ------
+ t
+ (1 row)
+
+ insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g;
vacuum gin_test_tbl; -- flush the fastupdate buffers
+ select gin_clean_pending_list('gin_test_idx'); -- nothing to flush
+ gin_clean_pending_list
+ ------------------------
+ 0
+ (1 row)
+
-- Test vacuuming
delete from gin_test_tbl where i @> array[2];
vacuum gin_test_tbl;
diff --git a/src/test/regress/sql/gin.sql b/src/test/regress/sql/gin.sql
new file mode 100644
index 4b35560..31890b4
*** a/src/test/regress/sql/gin.sql
--- b/src/test/regress/sql/gin.sql
*************** create index gin_test_idx on gin_test_tb
*** 10,17 ****
--- 10,23 ----
insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
+ select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
+
+ insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g;
+
vacuum gin_test_tbl; -- flush the fastupdate buffers
+ select gin_clean_pending_list('gin_test_idx'); -- nothing to flush
+
-- Test vacuuming
delete from gin_test_tbl where i @> array[2];
vacuum gin_test_tbl;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
