nyuuzyou/wikis huggingface dataset:
GIN generalization

== General ==
The initial purpose of GIN generalization was to improve in-core fulltext search so that it becomes comparable on search speed with stand-alone solutions like Sphinx, Solr etc. After some investigation it appears that GIN generalization could be also useful in following areas (at least):
* Similarity search on arrays
* Positioned n-grams (could provide better acceleration than just n-grams)
* Search of regular expressions (inverse task: search regular expressions which matches given string)
== Prototype ==
Prototype was demonstrated on PGConf.EU 2012 . General conclusion is that we can even outperform Sphinx in search speed with new GIN. 
Prototype is pretty big and tangled. So we decided to split it into several clear patches.
== Part 1: New posting lists/trees storage ==
This patch introduces following:
* Store additional information in posting lists/trees (store arbitrary Datum with each item pointer)
* Corresponding interface changes
* Posting lists/trees compression
Question to discuss: how to handle partial match with additional information?
== Part 2: Fast scan ==
This patch addresses problem of "frequest_term && rare_term". It implements algorithm which is able to skip parts of posting tree while scan. It introduces new interface method "preConsistent" which is used for preliminary checks and allows false positives on imput.
== Part 3: Ordering in index ==
This patch implements returning ordered data from GIN index. Now it behaves like KNN-GiST. It inroduces new interface function which calculates distance. When GIN index have to return ordered data it calculates distance for each item pointer (using additional information), sort that dataset, and then returns results one by one. This is probably a wrong desing. Probably we should teach planner/executor that GIN can return value of "column op constant", then other planner node would be responsible for sorting.
Question to discuss: what is right design for ordering using GIN index?
== Part 4: TSearch opclass revision ==
This patch modifies tsvector_ops to support features of parts 1-3. It stores compressed representation of positions vector as additional information. It introduces "tsvector <-> tsquery" operator which returns inverse value to ts_rank(). Also it implements new GIN interface method to calculate this operator from index.
== Part 5: RUM access method ==
PostgreSQL 9.6 introduced custom AM and generic WAL, which made possible development of a new AM as an extension - RUM (), which implements all the features of Generalized GIN. RUM is available from the Postgres Professional [repository].
Google Translate

This function translates a string using the Google Translate service.
CREATE OR REPLACE FUNCTION gtranslate(src text, target text, phrase text) RETURNS text
LANGUAGE plpythonu
AS $$
import re
import urllib
import simplejson as json
class UrlOpener(urllib.FancyURLopener):
 version = "py-gtranslate/1.0"
base_uri = "http://ajax.googleapis.com/ajax/services/language/translate"
default_params = {'v': '1.0'}
def translate(src, to, phrase):
 args = default_params.copy()
 args.update({
 'langpair': '%s%%7C%s' % (src, to),
 'q': urllib.quote_plus(phrase),
 })
 argstring = '%s' % ('&'.join(['%s=%s' % (k,v) for (k,v) in args.iteritems()]))
 resp = json.load(UrlOpener().open('%s?%s' % (base_uri, argstring)))
 try:
 return resp['responseData']['translatedText']
 except:
 # should probably warn about failed translation
 return phrase
return translate(src, target, phrase)
$$;
The code was originally derived from the code provided at http://code.google.com/p/py-gtranslate .
Example:
﻿SELECT gtranslate('de', 'en', 'Die Würde des Menschen ist unantastbar. Sie zu achten und zu schützen ist Verpflichtung aller staatlichen Gewalt.');
 gtranslate
--------------------------------------------------------------------------------------------------
 Human dignity is inviolable. To respect and protect it shall be the duty of all state authority.
(1 row)
(from the constitution of the Federal Republic of Germany)
Category:PL/PythonU
Greatest Common Divisor

Using a CTE, the calculation of greatest common divisor (gcd) is trivial.
CREATE OR REPLACE FUNCTION gcd( a bigint, b bigint)
RETURNS bigint
IMMUTABLE
STRICT
LANGUAGE SQL
AS $$
WITH RECURSIVE t(a,b) AS (
 VALUES (abs($1)::bigint, abs($2)::bigint)
UNION ALL
 SELECT b, mod(a,b) FROM t
 WHERE b > 0
)
SELECT a FROM t WHERE b = 0
$$;
Thanks to David Fetter for this query.
Thanks to Pasha Golub for the faster, more correct version.
From PostgreSQL 13, a built-in function gcd() is available (PostgreSQL documentation: Mathematical Functions ).
Greg Stark's Development Projects

This page lists the projects and ideas I'm working on.
== Non-recursive WITH / recursive WITH ==
== Preread blocks using posix_fadvise ==
The main issue is whether we want to rejigger to buffer manager to allocated
buffers to preread pages. This effectively would use the buffer manager to
cache pages which have already been preread. It also would possibly open the
door to using async i/o to preread pages directly into the buffer manager
instead of into the filesystem cache.
Hans-Juergen Schoenig & Zoltan Boszormenyi have also done additional work on
the use of posix_fadvise.
Discussion
== Ordered append executor node (merge-append) ==
This is a new executor node to handle appending multiple partitions taking
the next record from the partition with the earliest order key. If every
input partition is already in the desired order this will guarantee that the
output is still in that order. This is important for partitioning since it
allows us to use some plan types such as merge joins which are currently
unavailable if you partition your tables.
== Concurrent psql ==
I want to rewrite this to clean things up and iron out the race conditions with 
cancel requests.
 
Discussion
== EXPLAIN queries in progress ==
This is a proof-of-concept patch with a few different goals. We want to:
* allow the user to get EXPLAIN ANALYZE info for queries taking too long to run to completion.
* allow the user to get an idea what percentage of their query has run and what stage of the query is currently executing.
* allow applications like pgadmin or apache to pull out the progress info and report it graphically through whatever interface is appropriate.
Discussion
== Hash joins using bitmap index scans ==
http://archives.postgresql.org/pgsql-hackers/2007-12/msg00507.php
== Column-wise data compression ==
A type of dictionary based compression, in which duplicate values in a column 
may be stored in an external dictionary table, similar to the way that TOAST 
tables work.
Category:Development
GridSQL

== Project Overview ==
GridSQL is an open source shared-nothing clustered database system for data warehousing designed for PostgreSQL. GridSQL includes intelligence to parallelize over multiple servers for achieving faster query response times.
== Project Status ==
Not actively developed and maintained any more, replaced by Stado (http://sourceforge.net/projects/gridsql/forums/forum/787558/topic/4514520).
== Project Contacts ==
* Project website: http://sourceforge.net/projects/gridsql/
* Source code: http://gridsql.cvs.sourceforge.net/viewvc/gridsql/
Category:Replication
Group commit

=== Description of feature ===
Group commit is a feature planned for PostgreSQL 9.2 . 
The feature is being developed by Simon Riggs and Peter Geoghegan. The latest -hackers thread on the feature is: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00804.php .
Broadly speaking, a group commit feature enables PostgreSQL to commit a group of transactions in batch, amortizing the cost of flushing WAL. The proposed implementation this page describes is heavily based on the existing synchronous replication implementation. It supercedes the commit_siblings "group commit" implementation of prior versions. This earlier implementation was never really considered to be effective, and its use was weighed down by caveats, so in practice it was only used very infrequently. It is anticipated that the proposed implementation will be turned on by default, and it may not be possible to turn off.
=== Benchmark ===
Benchmarking of this feature has been performed by Greg Smith's pgbench tool (https://github.com/gregs1104/pgbench-tools) . Here are results for the initial benchmark:
http://wiki.postgresql.org/images/5/50/Group-commit-pgbench-tools.pdf
Revised results, with semaphore implementation:
http://wiki.postgresql.org/images/c/c6/Group-commit-semaphore-results.pdf
These results were obtained on an ext4 (Linux kernel 3.1) filesystem with LVM. The harddisk used was a WDC WD3200BEKT-08PVMT1 7200 RPM sata disk, with write caching enabled.
Category:PostgreSQL 9.2Category:Performance
Gsoc08-collation

==Proposal==
===Abstract===
Current version of PostgreSQL supports only one collation per database cluster set by initdb. This does not meet the requirements of some users developing multi-lingual applications.
The goal of the work will be to implement collation at database level and make foundations for further national language support development. User will be able to set collation when creating database or change collation of existing one. Particulary commands CREATE DATABASE... COLLATE … and ALTER DATABASE … COLLATE … regarding ANSI standard.
Work will also implement.possibility of creating users's own collation collection – commands CREATE COLLATION … FROM … USING and DROP COLLATION regaring ANSI standard.
===Further information===
This work will be used as my bachelor thesis. Learning PostgreSQL's internals better will be great experience for me. I will continue working on this project as a master thesis and will be adding more functionality. The idea is to implement collation per colmun. For my batchof this work I'm applying for in the Google Summer of Code 2008 will implement collation functionality at database level and create foundation for further multi language support development. This will be a significant benefit for open source community.
The initial part of my work has been completed and submitted as part of a patch contributed by Alexey Slynko. I'm now in stage of adding collation catalogs, that will be important for further multi language support.
Users and developers have been asking for improvement of multi language support. This requirement has been already added to official PostgreSQL TODO list.
==Implementation==
===Catalogs===
* new catalog pg_collation will be defined
* pg_collation will contain SQL standard collations + optional default collation (when set other than SQL standard one)
* pg_type, pg_attribute, pg_namespace will be extended with references to default records in pg_collation
===initdb===
* pg_collation will contain pre-defined records regarding SQL standard and optionally one record that will be non-standard set when creating initdb (the one using system locales)
* this record will be referenced by pg_type, pg_attribute, pg_namespace in concerned columns and will be concidered as default collation that will be inherited
===CREATE DATABASE ... COLLATE ...===
* after copying the new database the collation will be default (same as cluster collation) or changed by COLLATE statement. Then we update pg_type, pg_attribute and pg_namespace catalogs
* reindex database
When changing databases the database collation will be retrieved from type text from pg_type.
==Mail archive==
* Collation at database level (16 Apr 2008)
* Proposal - Collation at database level (28 May 2008)
* [WIP] patch - Collation at database level (1 Jul 2008)
* [WIP] collation support revisited (10 Jul 2008)
===Downloads===
* Work in progress patch
Category:Google Summer of Code
Gsoc08-hashindex

Proposal Of GSoC
== Project Info ==
Improve hash index performance.
This patch implements the item in the ToDo's list:
* In hash indexes, consider storing the hash value with or instead of the key itself
== Ideas ==
There's two basic ideas in the patch.
* Store the hash value instead of real key in the bucket.
We can keep more tuples in a bucket and reduce the index size. It also means all hash indexscans become lossy and have to be rechecked at the heap.
* Keep the contents of each index page ordered by hash value and use binary instead of linear search to find the matching item(s) during an indexscan.
== Performance ==
Here is some results of hash index with patch vs. btree index.
You can see Details About The Test for more information about the test.
* Platform
Linux = 2.6.24-16-generic
Processor = Intel(R) Core(TM)2Duo CPU T7500@2.20GHz
Memory = 1GB
* Workload
Table size - 39916800 unique words, 1529MB
Query - 2000 equality query
* Result
index	build time 	index size	query time 	blocks read
hash 	504650.100 ms	1024MB 	5189.844 ms	39801
btree	844495.867 ms	1027MB 	5656.045ms 	41627
== Other ==
Hash index without the patch is huge. Here are some simple results of index building on a smaller workload. 
* Workload
table Size - 3628800 unique words, 139MB
* Result
index 	build time 	index size
btree 	51961.123 ms 	93MB
hash without patch	411069.264 ms	2048MB
hash with patch 	36288.931 ms 	128MB
Category:Google Summer of Code
Category:Indexes
Gsoc08-pgscript

== Project Summary ==
pgScript was a scripting language for pgAdmin and PostgreSQL based on a T-SQL syntax. Therefore it ran on the client side.
Project development was ceased with PgAdmin version 4.
Archived project code: https://www.postgresql.org/ftp/projects/pgFoundry/pgscript/
== Detailed info ==
pgScript enhances PostgreSQL SQL commands with these additional features:
* Control-of-flow language
* Local variables
* Random data generators 
For instance:
 SET @A = INTEGER(0, 10); -- Random integer generator into @A
 IF (SELECT 1 FROM table) -- Then table exists
 BEGIN
 SET @B = SELECT * FROM table; -- Stores result of the query in @B
 INSERT INTO table VALUES (@A); -- Inserts a random integer
 PRINT @B; -- Prints result of the previous query
 END
 ELSE -- Else table does not exist
 BEGIN
 CREATE TABLE table ( ... ); -- Regular PostgreSQL command
 END
pgScript interprets the kind of script above. For more details please look at http://pgscript.projects.postgresql.org.
pgScript is (will) also integrated into pgAdmin. You can access pgScript through the Query tool in pgAdmin: see http://www.pgadmin.org for information & download.
== Timeline ==
There are two parts in the project:
* (07/06) Make a standalone version (program or library) of pgScript
* (08/10) Integrate it into pgAdmin
Category:Google Summer of Code
Category:PGAdmin
Category:Historical information
GSoC 2008

PostgreSQL is proud to participate in the Google Summer of Code program once again. More information on our participation can be found on the main website.
== Projects ==
The following projects were accepted into the program:
* Text search selectivity functions for PostgreSQL (Jan Urbański, mentored by Heikki Linnakangas)
* pgScript - Scripting language for pgAdmin (Mickael Deloison, mentored by Magnus Hagander)
* Improving performance of hash index (Xiao Meng(孟啸), mentored by Jonah H. Harris)
* Graphical SQL Query Builder Improvement for pgAdmin III (Luis Alberto Ochoa Paz, mentored by Dave Page)
* XML Indexing: Add special indexing types for XML data, capable of supporting indexed XPath queries. (Pragnesh, mentored by Julius Stroffek)
* Collation implementation at database level in PostgreSQL (Radek Strnad, mentored by Zdenek Kotala)
The pages linked from the list above are (optionally) produced and maintained by the students of each project.
== Resources ==
We have created the PostgreSQL GSOC Project on pgFoundry to help manage our participation in GSoC. This allows for sharing of non-wiki documents, mailing lists, and a few other things. The mailing lists for gsoc are:
* gsoc-students@pgfoundry.org: private list for students only
* gsoc-mentors@pgfoundry.org: private list for mentors only
* gsoc-discuss@pgfoundry.org: public list for all participants
The GSoc adminstrators this year are Josh Berkus (josh-at-postgresql.org) and Selena Deckelman (selenamarie-at-gmail.com).
== Timeline ==
The official timeline is available on on the GSOC site.
Upcoming deadlines as of April 25:
* All students should have already heard from their mentors. Contact the administrator if you have not.
* April 28: Any students wanting to go to pgCon should have contacted the Administrator.
* May 20: Have a project specification and timeline worked out with your mentor.
* May 26: coding begins for GSoC (not that you have to wait).
Category:Google Summer of Code
GSoC 2009

* Stuff to do:
== Admin PEOPLE ==
* Prof Len Shapiro - list contacts,
* Robert Treat - mentor 2x, PHP stuff phppgadmin/pgadmin etc. FTS for pgadmin/phppgadmin
* Selena Deckelmann - other stuff
* Michael Brewer - contact about Higher Ed - ME
* Devrim Gunduz - Admin
* Josh Berkus -
== Mentors? ==
-- TOM
* Stephen Frost - column level permissions
* Magnus
* Dave Page
== Examples: ==
Florian - read/only on snapshots
no advancement of xid on select statements
== GOALS: ==
* usable code
** useful/novel ideas
** research projects
* longer term contributors
== TODOs: ==
* Update GSOC page - SELENA submit changes that Robert can push
* Slightly different email text for advertising - SELENA, run it by Len Shapiro for his advice
* Blog that we're participating and seeking students
* Round of private emails to people who have participated in the past: Heikki, Simon, Mark,
** request interest, and then follow up in asking about possible topics for students
* Mentor recruitment and then email to -hackers
** do this much later when we have some proposals in
* Research which projects were done in the past and how they morphed into contributions
* Selena needs to think of a convincing way to get mentors bought into students blogging etc. 
* Recruitment -- no organized group effort?
** -announce, -general, -hackers
** user group lists?
** phppgadmin/pgadmin
** berkeley
** linux user group in gainsville -- university of florida
** sysadmin at U of Alabama
** Michael: Q -- is there something better we can do? , Len Shapiro
** Univ. of Maryland -- contact them?
* BLOG posts Robert and I could do about what is happening
** Research Postgres-related projects in other projects... -- ROBERT go do this later
* Identify the commitfest that the code will be submitted to
** Selena - #postgressql-gsoc (register, then done)
* Handle the submission process -- ROBERT handle the submission process
== TIMELINE ==
* Create the timeline: due dates, etc.
* Create an orientation to -hackers and -general for students
* Have students communicate on -hackers where appropriate (didn't really work?)
** Or other relevant -devel lists
* Stuff to keep students together:
** Daily blogging for students
** weekly group IRC checkin -- two checkin times maybe?
* Mailing list
** pgsql-students (?)
** mentors mailing list -admin mailing list, berkus said?
Category:Google Summer of Code
GSoC 2010

== Projects ==
The GSoC projects accepted for 2010 are:
* Improve phpPgAdmin ergonomy by Leonardo Augusto Sápiras (blog)
* Add MERGE command by Boxuan Zhai
* Database Designer for pgAdmin by luis ochoa
* Materialized Views by Pavel Baros
* Database Schema Difference Visualizer by adeel khan
* Add JSON datatype by Joseph Adams
We are also working on a twitter list for those involved this year. If you are missing, please send us your info:
== Key Info ==
* March 8th - 12th, Mentoring Application Due
* March 29th - April 9th, Student Applications Due
* GSOC 2010 Home page 
* GSOC 2010 FAQ
* Postgres GSOC Page, Needs Updating!
* Twitter stream of mentors and students
== Project Admins ==
* Robert Treat - Past mentor 2x, co-admin, Mentor Summit attendee. 
* Selena Deckelmann - Co-admin, Mentor Summit attendee.
== 2010 Mentors ==
* Dave Page - Former mentor - pgAdmin, Windows, Packaging, Infrastructure 
* Robert Haas - CommitFest Manager
* Heikki Linnakangas - Postgres Committer
* Magnus Hagander - Postgres Committer, Windows, pgAdmin
* Guillaume Lelarge - pgAdmin
* Jehan-Guillaume de Rorthais - phpPgAdmin
=== additional reviewers === 
* Josh Berkus - auto-configuration, performance testing
* Andreas Scherbaum - performance, ddl, configuration
== Past Success ==
* Florian - read-only on snapshots, no advancement of xid on select statements
* Ivan - Full-Text Support in phpPgAdmin
* Mickael Deloison - pgScript engine for pgAdmin
* Luis Alberto Ochoa Paz - Graphical query builder for pgAdmin
more?
== GOALS: ==
* usable code
** useful/novel ideas
** research projects
* longer term contributors
== TODOs: ==
* Kick-off Meeting for Community Members
* Update GSOC page 
* Advertising?
* Blog that we're participating and seeking students
* Round of private emails to people who have participated in the past: Heikki, Simon, Mark,
** request interest, and then follow up in asking about possible topics for students
* Mentor recruitment and then email to -hackers
** do this much later when we have some proposals in?
* Recruitment -- no organized group effort?
** -announce, -general, -hackers
** user group lists
** phppgadmin/pgadmin
** berkeley
** Univ. of Maryland -- contact them?
* Identify the commitfest that the code will be submitted to
== Expectations ==
* Stuff to keep students together:
** Regular blogging from students
** weekly group IRC checkin? -- two checkin times maybe?
* Have students communicate on -hackers where appropriate (didn't really work?)
** Or other relevant -devel lists
* Mailing list
** pgsql-students (?) vs. -hackers (?) maybe up to mentor?
** mentors mailing list -admin mailing list, berkus said?
** students mailing list via gsoc
Category:Google Summer of Code