nyuuzyou/wikis huggingface dataset:
GSoC 2015

This page is for collecting ideas for future Summer of Code projects.
== Accepted Projects ==
# Improve the performance of "ALTER TABLE .. SET LOGGED / UNLOGGED" statement
# Refining Simulated Annealing approach for query optimization
# Sequential disk access during VACUUM for GiST
== Project Ideas ==
Project ideas are to be added here by community members.
=== Core ===
* UPDATE ... RETURNING OLD link
* Add RETURNING to DDL (CREATE, ALTER, DROP) and possibly DCL (GRANT, REVOKE) link
* Allow different datatypes to be sliced differently, when TOASTed link
* Sequential disk access during VACUUM for GiST/GIN
* Support for microvacuum for GiST
* Parallel GIN build using background workers
* Implement WAL logging for hash indexes
* Indexing prolonged geometrical objects (i.e. boxes, circles, polygons, not points) with SP-GiST by mapping to 4d-space.
* Make index selection sublinear. Useful for index partitioning (vs table partitioning).
* Optimization- add to the join removal code the ability to remove joins to tables when the join is on a foreign key column (where all entries must exist in the parent anyway)
* Optimization- Check the set of conditionals on a WHERE clause against CHECK constraints on the table being queried and remove any conditionals which *must* be true due to the CHECK constraints
* Investigate the HashJoin code- specifically, review cases where the larger table is hashed instead of the smaller, test making NTUP_PER_BUCKET variable, etc.
* Improve plpgsql error checking- look at existing discussions around GUCs which can be set to increase the warnings that plpgsql throws.
* per-function temp tables (or some kind of scoping for temp tables when used inside of plpgsql code)
* date_trunc() supporting intervals
* Modify the postgres_fdw to allow a server to be defined as "autocommit" or similar- to allow for autonomous transactions
* Modify plpgsql to complain about unused variables
* Comments for function parameters
* Finally clause for pl/pgsql exception handling
* Foreign keys for arrays link 
* Invent an async interface for the Append() node to use, allowing a way to parallelize queries across multiple remote FDWs or local tablespaces.
* Implement a way to store/re-use the constraint exclusion analysis results for an inheiritence-based partition setup
* Modify snapshot export to also export lock info, to avoid deadlocks between parallel pg_dump and concurrent DDL
* PL/PgSQL x++; x+= 2; operators.
* bytea '&' and '|' operators which returns a bytea, written efficiently in C by stepping through the bytea's in chunks, perhaps.
=== Extensions ===
* extending JDBC_FDW to support qual pushdown and/or writeable foreign tables API.
== Project Admins ==
* Thom Brown
* David Fetter
* Kevin Grittner
== 2015 Mentors ==
Mentors volunteered who have been active on -hackers list:
* Alexander Korotkov
* Alvaro Herrera
* Andreas Scherbaum
* Andrew Dunstan
* Ashutosh Bapat
* Atri Sharma
* Stephen Frost
Category:Google Summer of Code
GSOC 2018:Sorting algorithms benchmark and implementation

== Introduction ==
This project tries to improve the current sorting routine used in PostgreSQL.
Originally, PostgreSQL is using the QuickSort implemented by J. L. Bentley and M. D. McIlroy in "Engineering a sort function" with some modifications. This sorting routine is very fast, yet may fall to O(n^2) time complexity in the worst case scenario. I am trying to find faster sorting algorithms with guaranteed O(nlogn) time complexity.
In this project, I have compared several sorting algorithms and proposed a new sorting implementation.
== Benefits to the PostgreSQL Community ==
* Using a more optimized sorting algorithm could bring a performance gain for many parts of PostgreSQL. 
* The benchmark could be used for further improvement of the sorting routine.
== Proposal == 
Proposal archive: https://github.com/Strider-Alex/GSoC-2018-archive/blob/master/proposal_gsoc2018.pdf
== Deliverables ==
* Sorting routines and benchmark: https://github.com/Strider-Alex/PostgreSQLSorting
* Research results: https://github.com/Strider-Alex/postgres_sorting_benchmark
* Sorting patch (intro sort implemenation): https://github.com/Strider-Alex/GSoC-2018-archive/tree/master/patch
* Community discussion of the resulting algorithm: https://www.postgresql.org/message-id/flat/5b5f8149.1c69fb81.e06c6.d054%40mx.google.com#ce794227b4efef1ae44884386933734b
== Todos ==
* New hash table implementation
GSoD

== GSoD ==
Google may select the PostgreSQL Project for the 2019 Season of Docs. This page is your starting point for the GSoD program. Please watch this page for updates.
=== Technical Writers ===
* Learn about GSoD (Read this first)
* Are you eligible for GSoD?
* Project ideas
** Note that you don't have to pick one of the project ideas listed in this page, you can submit your own project idea instead. Your idea will be evaluated by the mentors.
==== Applying ====
To apply we ask that you create:
* A list of deliverables
* A detailed description
* Outline your approach
* Outline your approximate schedule
* A document describing your background
Please also include if you have other commitments during the period of GSoD. This includes jobs (part time or full time), exams, vacation, and anything else which might prevent you from working on your project.
We encourage you to contact the PostgreSQL Project before submitting your proposal through the GSoD website, and discuss the details with us. Proposal changes after the deadline can't be accepted..
Please send your proposal to: pgsql-docs@postgresql.org (you must be subscribed to the list). The deadline for proposals is June 28, 2019.
==== Help ====
There are several resources which can provide help.
* #postgresql channel on Libera IRC
* #gsod2019-techwriters channel on PostgreSQL Slack (Details and Signup)
* pgsql-docs Documentation Mailing List
==== Communication ====
We will announce dedicated communication channels for technical writers soon.
=== Organization Administrators ===
If you want to participate, please contact one of the PostgreSQL org admins:
* Stephen Frost <sfrost@snowman.net>
* Sarah Conway <xenophenes@gmail.com>
=== Timeline ===
* April 2, 2019: Organizations Apply
* April 30, 2019: Organizations Announced
* April 30 - May 28, 2019: Technical Writer Exploration
* May 29 - June 28, 2019: Technical Writer Application Period
* June 28 - July 30, 2019: Application Review Period
* July 30, 2019: Technical Writing Projects Announced
* August 1 - September 1, 2019: Community Bonding
* September 2 - November 22, 2019: Documentation Development
* November 25 - 29, 2019: Project Finalization
* December 2 - 6, 2019: Mentors Submit Evaluations
* December 10, 2019: Results Announced
* February 24 - 28, 2020: Final week for Long Running Projects
* March 6, 2020: Google adds the long-running projects to the results of Season of Docs 2019
=== Useful Links ===
* Season of docs home
* Official Google Season of Docs 2019 Timeline
* PostgreSQL GSoD 2019
=== Press/News ===
Guía para el uso del Administrador de la replicación para los clústeres de PostgreSQL repmgr

Requisitos de instalación:
----------------------------
Instalar (ubuntu): libxslt1-dev, libedit-dev, libpam0g-dev, postgresql-server-dev-9.1
----------------------
Instalación de repmgr
----------------------
(1) Bajar el código fuente de repmgr desde http://projects.2ndquadrant.com/repmgr
(2) Copiar y descomprimir en /tmp.
 $ cp repmgr-1.2.0.tar.gz /tmp
 $ cd /tmp repmgr
 $ tar xvzf repmgr-1.2.0.tar.gz
(3) Compilar
 $ make USE_PGXS=1
(4) Instalar
 $ make USE_PGXS=1 install
(5) Ver versión de repmgr
 $ repmgr --version
 $ repmgrd --version
(6) Creación de las llaves de intercambio:
--Para Nodo1
 $ su postgres
 $ ssh-keygen -t rsa 
--Para Nodo2
 $ su postgres
 $ ssh-keygen -t rsa
--Intercambio de llaves entre ambos nodos
 $ cp /var/lib/postgresql/.ssh/id_rsa.pub /home/gilbertoc/postgres/.ssh/authorized_keys
 $ cp /home/gilbertoc/postgres/.ssh/id_rsa.pub /var/lib/postgresql/.ssh/authorized_keys
--Prueba de funcionamiento de las llaves
 $ ssh localhost 
 $ ssh localhost -l postgres
(7) Configuración del postgresql.conf del Nodo1:
 listen_addresses = '*'
 wal_level = hot_standby
 checkpoint_segments=30
 archive_mode=on
 archive_command='cd .'
 max_wal_senders=10
 wal_keep_segments=5000
(8) Configuración del postgresql.conf del Nodo2:
 hot_standby=on
(9) Configuración del pg_hba.conf del Nodo1 y Nodo2:
 host all all 127.0.0.1/32 trust
 host replication all 127.0.0.1/32 trust
(10) Crea en fichero /var/lib/pgsql/repmgr/repmgr.conf en el Nodo1:
 cluster=test
 node=1
 conninfo='host=127.0.0.1 dbname=test'
(11) Vaciar el dirctorio de datos del nodo2:
 $ cd /home/gilbertoc/postgres/mycluster2
 $ rm -rf *
(12) Clonar el Nodo2:
 
 $ repmgr -D /home/gilbertoc/postgres/mycluster2 -f /etc/PostgreSQL/9.1/main/repmgr.conf -d test -p 5432 -U postgres -R postgres standby clone localhost --verbose
(13) Crear repmgr.conf en el Nodo2:
 cluster=test
 node=2
 conninfo='host=localhost port=5433 user=postgres dbname=test'
(14) Crear recovery.conf en el Nodo2:
 standby_mode = 'on'
 primary_conninfo = 'port=5432 host=localhost user=postgres'
(15) Iniciar Nodo2:
 $ pg_ctl -D /home/gilbertoc/postgres/mycluster2 -l logfile start
(16) Registrar ambos nodos:
 $ repmgr -f /home/prime/repmgr/repmgr.conf --verbose master register
 $ repmgr -f /home/standby/repmgr/repmgr.conf --verbose standby register
(17) Pruebas de los Nodos:
--En el Nodo2:
--------------
 $ psql -h localhost -p 5434 -d test
 insert into mitabla (id, nombre) values (4, 'Martinez');
 *--ERROR: cannot execute INSERT in a read-only transaction
--En el Nodo1:
--------------
 $ psql -h localhost -p 5432 -d test
 insert into mitabla (id, nombre) values (4, 'Martinez');
GUI Database Design Tools/ja

複雑なデータモデルを構築している場合、エンティティリレーションシップ図や似たような技法の生成機能を提供するツールによるサポートは非常に役に立ちます。
PostgreSQLコミュニティで有用という意見があったツールがいくつか存在します。
* Data Architect
* Open System Architect
* Power*Architect
* dbwrench
* Datastudio
* dbForge Studio for PostgreSQL
* xml to DDL
* DbDesigner fork
* Toad Data Modeler
* DeZign for Database
* PostgreSQL Maestro
* WaveMaker Visual Ajax Studio
* ModelRight
* TCM - Toolkit for Conceptual Modelling
* Druid III
また、pgadmin3は本物の設計ツールではありませんが、閲覧や簡単な追加の際に役に立つでしょう。
Category:Japanese
GUI Database Design Tools/ru

Крайне полезно при построении сущность-связь и ей подобных диаграмм, использовать специализированные инструменты проектирования. Некоторые из рекомендованных сообществом PostgreSQL:
* Datastudio
* DbDesigner fork
* dbForge Studio for PostgreSQL
* DbSchema
* dbwrench
* DeZign for Database
* Druid III
* ERBuilder Data Modeler
* ModelRight
* Moskitt and its spatial plugin for PostGIS users
* Open System Architect
* PGmodeler
* PostgreSQL Maestro
* Power*Architect
* TCM - Toolkit for Conceptual Modelling
* Toad Data Modeler
* Valentina Studio
* Vertabelo (designing db online)
* WaveMaker Visual Ajax Studio
* xml to DDL
Также можно рассматривать pgadmin3 для простых операций с базами.
Category:Tool
Category:Russian
Hackers' Lounge

== PgWest 2009 (Seattle) Hackers' Lounge ==
Time: all day Saturday at PGWest 2009
Location: Room SAM-102
Please bring power strips if you got 'em.
List your project here, or sign up to work on a project with others.
(There's a blackboard in the room for signups/project org too!)
===pg_top & pg_proctab===
* Mark Wong
===Pg plugins for munin===
* Magnus Hagander
* Gabrielle Roth
===Concurrent psql===
* David Fetter
===PL/Haskell===
* Dan Colish
Category:PostgreSQL Events
Handling Security Issues

This page contains information for developers on how to deal with security issues and how to prepare a security release.
Note: If you are a user who wants to report a security issue or who wants to learn about how the PostgreSQL project deals with security issues, please go to http://www.postgresql.org/support/security. The wiki page you are looking at is aimed at developers.
== security@postgresql.org ==
The email address security@postgresql.org is the recommended contact point for all inquiries about security issues. It currently points at pgsql-security@postgresql.org.
== Security team ==
The security team is a closed-subscription list whose purpose is to be able to discuss security issues in a controlled group. The current members are:
* Álvaro Herrera
* Andres Freund
* Andrew Dunstan
* Bruce Momjian
* Dave Page
* Greg Stark
* Heikki Linnakangas
* Joe Conway
* Jonathan Katz
* Magnus Hagander
* Michael Paquier
* Noah Misch
* Peter Eisentraut
* Robert Haas
* Stefan Kaltenbrunner
* Tom Lane
== Responding to security bug reports ==
Respond to a security bug report reported via the security@ address in the same way as you would with a normal bug report on pgsql-bugs. The only difference is that the issue should not be disclosed to anyone outside the security team and the original reporter.
== Committing patches for security issues ==
If the git commit log message for a commit matches
 /^Security:/
then the message on pgsql-committers will be held for moderation. This facility should be applied when committing a patch for a security issue, so that details of the fix don't get broadcast to the world before the release including the fix is made. See this example for a commit message where this was done. As in that example, the recommended style is something like "Security: CVE-number" or some other reference to the reason for the security marker.
== CVE numbers ==
A CVE number should be requested for every security issue. This number should be included in all the commit messages, announcements, and so on. A document describing the process can be found on developer.postgresql.org at ~petere/can-request.txt (only available with shell access, because it is not a public document). The person who drives the process of fixing the issue and preparing the security release (often Tom Lane) is usually the one who deals with requesting the CVE numbers.
Category:Development
Hash proposal

== Abstract ==
Hash index is well used index in equality queries. But it perform no better than B-tree in PostgreSQL.
The PostgreSQL community has discuss the problem before
 http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php
This project tries to deal with this problem by storing the hash value instead of real key. It's one of schemes to improve hash index performance listed in PostgreSQL's TODO list.
This work will deal with this problem by storing the hash value instead of the real key. Then I proposed an idea to deal with the dark side of the scheme. I'll benchmark both of them against original implementation and B-Tree to show the improvement.
== Detailed Description ==
* PART A. Store the hash value instead of the real key
It has been discussed here: http://archives.postgresql.org/pgsql-hackers/2004-06/msg00168.php
Neil Conway had posted a patch doing this with an old version of PostgreSQL.
 http://archives.postgresql.org/pgsql-patches/2005-05/msg00164.php
But it makes it significantly more expensive to skip over entries that have the same hash code but aren't really equal to the key being sought (since you need to an additional disk access to visit the heap to find out they aren't equal).
* PART B. Variety of the first scheme
We still store hash value and tuple pointer in bucket. But when we insert a tuple, we can determine whether or not store the real key.
If we encounter with the same hash value when inserting, we store both of the hash value and the real key. That is to say, when there's many tuples with the same hash code, the first one can only store the hash code but the other tuples will store both hash code and real value.
* Part C. Benchmark and maybe look at other ways to improve performance
Because of the application area of hash index, the benchmark should satisfy these conditions:
 - Workload - All of the queries should be equality queries.
 - Data - The key should be almost unique (i.e. most of the equality queries are point queries instead of multipoint queries). 
 Multipoint queries can only be efficient for cluster file.
 
I'll test the first scheme version and the 2nd scheme version against the original version and the B-Tree index to show the improvement.
 
And we can also try to look at other ways to improve performance when benchmarking and discuss with other developers to make future improvement.
* Part D. Alternative Plan
 
If the schemes don't work well, I'll have to start an alternative plan. At least, it can work well with large index field in my opinion. So I'll add a clause in Alter Index so it will be optional when users encounter with the special conditions sometimes.
So even make worst of plan, it's still can be used in some situations.
Category:Indexes
HashTable

This code demonstrates use of the PostgreSQL hash table
#include "postgres.h"
#include "fmgr.h"
#include "utils/hsearch.h"
PG_MODULE_MAGIC;
/* Hash table tester */
const int NUM_ELEMS = 10;
typedef struct hashkey {
 int num;
} HashKey;
typedef struct hashelem {
 HashKey key;
 int mydata;
} HashElem;
static HTAB *hashtable = NULL;
void _PG_init(void) {
 HASHCTL ctl;
 Assert(hashtable == NULL);
 memset(&ctl, 0, sizeof(ctl));
 ctl.keysize = sizeof(HashKey);
 ctl.entrysize = sizeof(HashElem);
 hashtable = hash_create("test hash table", 2, &ctl, HASH_ELEM | HASH_BLOBS);
}
Datum hash_tester(PG_FUNCTION_ARGS) {
 int i;
 HashKey key;
 HashElem *elem;
 bool found;
 for (i = 0; i < NUM_ELEMS; i++) {
 key.num = i;
 elem = hash_search(hashtable, (void *)&key, HASH_ENTER, &found);
 if (found)
 elog(WARNING, "Found entry %d before it was supposed to be added", i);
 else 
 elog(NOTICE, "Didn't find %d -- good", i);
 
 elem->mydata = i;
 }
 for (i = 0; i < NUM_ELEMS; i++) {
 key.num = i;
 elem = hash_search(hashtable, (void *)&key, HASH_FIND, &found);
 if (found)
 elog(NOTICE, "Found key id %d with element %d", i, elem->mydata);
 else
 elog(NOTICE, "Couldn't find key %d, though it should be there", i);
 }
}
Hash test

This page is about the detail of the test step by step.
== Before Test ==
Create table
postgres=# CREATE TABLE dict (word varchar(100));
postgres=# COPY dict FROM '/tmp/words';
Clear cache - shutdown the database and clear the kernel cache
$ sudo echo 3 | sudo tee /proc/sys/vm/drop_caches
Create a table to collect the io statistics
CREATE TABLE hash_stat(
	heap_blks_read integer,
	heap_blks_hit integer,
	idx_blks_read integer,
	idx_blks_hit integer
);
== Test Hash Index ==
Create Index and get the build time
postgres=# \timing
postgres=# CREATE INDEX wordhash ON dict USING hash (word);
504650.100 ms
Get the stastics before query
postgres=# INSERT INTO 
hash_stat (heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit)
SELECT
heap_blks_read,
heap_blks_hit,
idx_blks_read,
idx_blks_hit
FROM pg_statio_all_tables
WHERE relname = 'dict';
Clear cache again and run pgbench. Get the query time of millisecond by 1000/tps.
$ sudo echo 3 | sudo tee /proc/sys/vm/drop_caches
$ pgbench -U postgres -n -f /tmp/query.sql dict
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 0.192666 (including connections establishing)
tps = 0.192684 (excluding connections establishing)
Get the statistics after query
postgres=# INSERT INTO ...
== Test Btree Index ==
Create btree index, get the build time and get the statistics before query
postgres=# DROP INDEX wordhash;
postgres=# \timing
postgres=# CREATE INDEX wordbtree ON dict USING btree (word);
844495.867 ms
It's the same with hash index test after that.
For pgbench test, we get
$ pgbench -U postgres -n -f /tmp/query.sql dict
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 0.176786 (including connections establishing)
tps = 0.176802 (excluding connections establishing)
== Result of IO Statistics ==
state 	heap_blks_read	heap_blks_hit	idx_blks_read	idx_blks_hit
hash - before query 	394317 	39916824 	0 	0
hash - after query 	414394 	39916947 	19724 	20276
btree - before query	610065 	39916947 	0 	0
btree - after query 	629956 	39917056 	21736 	58425
we can get blocks_read during the query by (heap_blks_read + idx_blks_read). You've saw the result here.
Category:Indexes
Hebrew

מהדורות
 מהדורה 9.6.2
קהילה
	 אתר קהילת PostgreSQL בישראל
	 קבוצת הקהילה ב Facebook
	 קבוצת הקהילה ב Telegram
 קבוצת Meetup PostgreSQL Israel
	 רשימת התפוצה בעברית
אירועים
	 pgDay Israel 2017
 סיכום של pgDay Israel 2017
תיעוד המוצר
חברת Postgres מקצוענים התחילה בתהליך תרגום של תיעוד המוצר. עדכונים יישלחו ברשימת תפוצה.
לאחר השלמת המשימה הקישור לתיעוד יתעדכן.
תמיכה מסחרית
חברות מסחריות המציעות תמיכה במוצר ניתן למצוא ב לינק
שונות
 FAQ הרשמי בעברית
	 סקירה קטנה על PostgreSQL למתחילים
	 אל תשתמשו בהגדרות ברירת המחדל
	 שינוי הגדרות של השרת - שימוש בזיכרון
 זיכרון מיון תוצאת השאילתה work_mem
 מצגת הסברה של Postgres מקצוענים
 זכרון עבור פקודת VACUUM
 שינוי הגדרות של השרת (המשך)
 הקדמה ל PostgreSQL replication
 Physical Log Streaming Replication - PLSR
 שכפול סינכרוני
 כלים של PLSR
 Parallel queries and transaction isolation levels
 שכפול לוגי
 יומן הטרנזקציות (Write-Ahead Log) ונקודות הבקרה (Checkpoints)
 pglogical
 PostgreSQL Bi-Directional Replication
 fsync, synchronous_commit והאם ניתן לגעת בהם
 הגדרות נוספות
 הגדרות אחרות (המשך)
 הגדרות של Planner/Optimizer
 אופטימיזציה של מסד הנתונים ויישומים
 שימוש באינדקסים
 שימוש בסטטיסטיקה
 אפשרויות האינדקסים ב PostgreSQL
 מצגת הסברה של Postgres מקצוענים
קישורים חיצוניים
הבלוג של Postgres מקצוענים באתר הקהילה בישראל
Category:Hebrew