nyuuzyou/wikis huggingface dataset:
9.4CF4Triage

This page handles suggested patch triage for 9.4 final commitfest patches.
==Good To Go==
These patches are ready for commit as soon as the CF starts.
* Add support to "IF NOT EXISTS" to others "CREATE" statements
* SSL: Show protocol and ciphersuite in server log
* SSL: libpq: Support TLSv1.1/TLSv1.2
* pg_stat_statements external query text storage
* Bugfix for timeout in LDAP connection parameter resolution
==Needs a Little Work==
Smaller patches which can be included in 9.4 if
they get a few hours of love from a committer or major hacker.
* Hstore 2.0
* CustomScan API
* REINDEX CONCURRENTLY (?)
* Inverse Aggregate Transition Functions
==Big Patches==
Very large, important patches which need a dedicated reviewer/committer for the duration of the commitfest. Ideally should list all dependant patches of each Big Patch.
* Logical Changeset Extraction (link?)
==Not Nearly Ready==
Patches which need major work and/or spec discussions before commitment.
* Store Extension Options
* Standalone Synchronous Master
==WIP==
Patches which are acknowledged not to be ready, or which are non-trivial patches which have not been introduced prior to CF4.
9.6betareleasedraft

= PostgreSQL 9.6 Beta Released (DRAFT) =
The PostgreSQL Global Development Group announces today that the first beta release of PostgreSQL 9.6 is available for download. This release contains previews of all of the features which will be available in the final release of version 9.5, although some details will change before then. Users are encouraged to begin testing their applications against this latest release.
== Major Features of 9.6 ==
Version 9.6 includes significant changes and exciting enhancements including:
* Parallel sequential scans, joins and aggregates
* Support for consistent, read-scaling clusters through multiple synchronous standbys and "remote_apply" synchronous commit.
* Full text search on phrases
* postgres_fdw pushes down sorts, joins, UPDATEs and DELETEs, enhancing federated database support
* Decreased autovacuum impact on big tables by avoiding "refreezing" old data.
In particular, the the parallelism feature should bring a noticeable increase in performance to supported queries.
== Help Test for Bugs ==
As with other major releases, the improvements in PostgreSQL include changes to large amounts of code. We count on you to test the altered version with your workloads and testing tools in order to find bugs and regressions before the release of PostgreSQL 9.6.0. In addition to testing that the new features work as documented, consider testing the following:
* Do parallel queries actually improve performance for you?
* Can you make parallel queries crash or lose data?
* Do our code changes cause PostgreSQL to not function on your platform?
* Does improved vacuum freezing safely reduce autovacuum of large tables?
* Does phrase search return the results you expect?
Version 9.6 Beta 1 also makes changes to the binary backup API. Users should test version 9.6 with PostgreSQL backup tools, including pgBackRest, Barman, WAL-E, and other packaged and in-house software.
As this is an Beta, minor changes to database behaviors, feature details, and APIs are still possible. Your feedback and testing will help determine the final tweaks on the new features, so test soon. The quality of user testing helps determine when we can make a final release.
== Beta Schedule ==
This is first beta release of version 9.6. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2016. For further information please see the Beta Testing page.
ABORT

===== 名字 =====
ABORT -- 中止当前事务
===== 语法概要 =====
 ABORT [ WORK | TRANSACTION ]
===== 描述 =====
ABORT 回滚当前事务，丢弃在事务中产生的所有变更。 这个命令和 SQL 命令 ROLLBACK 的行为完全一样， 只是由于历史原因而保留下来。
===== 参数 =====
;WORK
;TRANSACTION
:可选的关键字。它们不起作用。
===== 注意 =====
使用 COMMIT 命令提交一个成功的事务。
如果不在事务内部发出 ABORT 不会有问题，但是会产生一个警告信息。
===== 示例 =====
中止所有变更：
 ABORT;
===== 兼容性 =====
此命令是 PostgreSQL 基于历史原因做的扩展。 ROLLBACK 是 SQL 中等价的命令。
===== 参见 =====
BEGIN, COMMIT, ROLLBACK
Administración clústeres mediante repmgr

Pondrémos a su disposición un ejemplo de como utilizar repmgr para que entienda su funcionamiento. Este ejemplo es para uso académico.
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.it/sites/default/files/repmgr-1.1.0.tar.gz
(2) Copiar y descomprimir en /tmp.
(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
--Copiar 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');
ADO.NET Driver for PostgreSQL by CData

== About ==
The ADO.NET Driver for PostgreSQL by CData offers the most natural way to access PostgreSQL data from .NET applications. The provider wraps the complexity of accessing PostgreSQL data in an easy-to-integrate, fully managed ADO.NET Data Provider.
The provider hides the complexity of accessing data and provides additional powerful security features, smart caching, batching, socket management, and more.
Category:Tool
ADO.NET Provider for PostgreSQL by CData

== About ==
The ADO.NET Provider for PostgreSQL by CData offers the most natural way to access PostgreSQL data from .NET applications. The provider wraps the complexity of accessing PostgreSQL data in an easy-to-integrate, fully managed ADO.NET Data Provider.
The provider hides the complexity of accessing data and provides additional powerful security features, smart caching, batching, socket management, and more.
Category:Tool
Advocacy

__NOTOC__* Upcoming events & conferences
* Guides to promoting PostgreSQL
* Software Ports: Getting PostgreSQL support into applications
* Things Advocacy could be doing better
* Postgres - Changing the name: Requirements and issues
===Case Studies===
* Case Studies: Anything that's added to the category
* Case Study Links
===PostgreSQL Booths - Conference/Event===
* Booth Preparation Checklist
* Organizing Booth Volunteers
* How To Booth for both Booth Staff and Organizers
* Booth Related Documents List
=== Branding Resources===
* Identity Guidelines, Color Palette
* Logo, Buttons, Banners
* Posters, Rollups
* Flyers, Brochures
* Surveys
* Marketing Blurbs
===New release management===
Instructions on how to do beta/minor/major releases are in PostgreSQL's redmine.
* Press Releases for Cumulative/Security Updates
* HOWTO Translate The Release
* PostgreSQL 8.4 - History of last completed release
* 84WebTasks - Preparation for 8.4 release
* PostgreSQL 9.0 Press Coverage
===Work in Progress===
* PUG Operating Manual
* Advocacy WIP
* Advice on running a PgDay
* Short Topic Books
* Code Sprint Session at Pg Conference West 2008
* Open Source Projects Using PostgreSQL
===Other Resources===
* Education Curriculum Development
* Your @postgresql.org e-mail address
* How to be a Regional Contact
Category:Advocacy
Advocacy/Better

Please add items to the list! Later, I'll go through and categorize things and prioritize them in terms of resources and do-ability. -Selena
* Getting press releases placed in publications which help promote PostgreSQL
** (/press page as suggested here: http://www.itworld.com/open-source/76011/four-things-open-source-projects-should-know-about-dealing-press)
* Identify who our target market is
* Create a list of copy to be written (includes marketing, How Tos, in-depth explanations)
* Write copy for website/wiki that targets our audiences, as well as new/emerging markets
* Identify how we can help user groups more
* Further promote user groups and help them out somehow
* Improve the user group website
* Help with recruitment of hackers to the project
* Help with recruitment of non-hackers to the project
* Identify non-hacker projects, and connect people who want to be helpful with those projects
* Report out on success/failures of advocacy projects at major conferences
* Encourage new people to give talks on PostgreSQL, attend conferences
* Improve the Wiki Toolset, allowing greater ease of participation & media presentation.
* Produce more papers about how Postgres improved business (ie: what was the business impact of using PG)
* More (and new/better) merchandising
* More participating on events, mostly non-PG events
Category:Advocacy
After-Thing

----
UPDATE: EnterpriseDB will be hosting a happy-hour style party on Saturday.
Where: Cayenne Bar & Grill. It's less than a half-mile (~0.75km) from the conference site, directly south on Broadway.
When: Saturday 5 - 7:30 pm or thereabouts
What: Open bar, appetizers
----
...and what about lunch, or after the party? Input from Seattle natives is much appreciated!
It sounds like most people are staying at the Marriot Springhill (1800 Yale Ave). It should be pretty easy to get to Pike Street, but bring walking shoes & expect hills. (As a Seattle native, I believe everything mentioned so far is within 30 minutes of sober walking from that hotel.)
Requests so far:
* arcade
* pub crawl
* get Magnus thrown out of a bar
===Fun===
* Jillian's Billiards Club recommended by locals, but would require cabs to get there (~2.5 miles from Springhill)
* Gameworks huge arcade. ~0.5 mile from the hotel, also has a full bar
* climbing wall at the Seattle REI
* chocolate factory tour at Theo Chocolates
===Pubs===
* Six Arms (~0.5 mile from the hotel, may not be walkable) McMenamin's, decent pub food & local brews
* Kell's, standard Irish Pub. Probably not walking distance.
* Owl 'n Thistle, Irish Pub. Also probably not walking distance, but I'm assured it's good.
* Elysian Brewing
* Linda's Tavern
===Food===
* Bimbos Cantina Margaritas!
* Honeyhole Sandwiches Boxed lunches available.
* Meza
* 611 Supreme
* In The Bowl
* Queen Sheba
* Tango
* Baguette Box
* Machiavellis (better for dinner)
* Wild Ginger (better for dinner, has many private rooms)
* Assiago (better for dinner, a one private room)
* Shiro's Sushi (better for dinner, has a private room)
===Attendees===
Want to join? Add your name below!
* Jeff Davis
* gabrielle
* Scott Bailey
* Andrew Lardinois
* Tim Bruce
* Jim Nasby
* Brian Aker
* Josh Berkus
* Jon Erdman
* David Fetter
* Adrian Klaver
* David Gibbons
Category:PostgreSQL Events
AgensGraph

== AgensGraph ==
AgensGraph is an open-source multi-model database management system, developed by Bitnine Global Inc, to support both relational and NoSQL property graph data models.
AgensGraph was developed and integrated from PostgreSQL RDMS and supports querying with Cypher and SQL extended for graph traversal.
== Editions & licenses ==
*AgensGraph Community Edition is free for any use (Apache 2 license). The Enterprise Edition extends the Community Edition for more robust and demanding use cases and requires a commercial license.
*AgensGraph version 1.0 was released in January 2017
*AgensGraph version 1.1 was released in March 2017
*AgensGraph version 1.2 was released in August 2017
*AgensGraph version 1.3 was released in December 2017
*AgensGraph version 2.0 was released in September 2018
*AgensGraph version 2.1 was released in January 2019
*AgensGraph version 2.5 was released in October 2021
*AgensGraph version 2.12 was released in June 2022
*AgensGraph version 2.13 was released in October 2022
*AgensGraph version 2.14 was released in January 2025
== Features ==
*Integrated queries supported in PostgreSQL (supports standard ANSI SQL) and Cypher either separately or in combination in a single qu*ery
*Multi-model support of the property graph data model, relational data model, and JSON documents
*Hierarchical graph label organization using ‘Label Inheritance’
*Property indexing on both vertexes and edges
*Constraints: unique, mandatory and check constraints
*ACID transactions
== Architecture ==
 
----
 
----
 
== See also ==
*Cypher 
== References ==
*AgensGraph 
*Current projects using Cypher 
*AgensGraph - Bitnine Global 
*Announcing the release of AgensGraph 2.0 
*Database of Databases - AgensGraph 
*Graph DBMS Performance Comparison AgensGraph vs. Neo4j 
*[PostgresConf 2018] Helping to Solve World Hunger through AgensGraph. 
*AgensGraph: A GraphDB Helping to Solve
Aggregate Array

The buildin array_agg() have different behaviour, here using array_cat().
== array_aggcat(anyelement) ==
This snippet is also part of the ulib_agg user-defined library.
CREATE AGGREGATE array_aggcat (anyelement)
( sfunc = array_cat,
 stype = anyarray,
 initcond = '{}'
);
Category:SQL
Category:Snippets
Aggregate Mode

The statistical mode is the value that appears most often in a set of values. 
 
== Postgres 9.4 has built-in aggregate function mode()==
The new ordered-set aggregate function supersedes the custom aggregate function below. More importantly, it also conflicts with it. To install it anyway, use a different name. Functionality is (almost) identical, but the new built-in function is much faster.
Subtle differences:
* Like most built-in aggregate functions, NULL values are ignored. If the most common value is NULL, built-in mode() returns the second most common value.
* The built-in mode() does not return an error if the expression is NULL in all rows. Returns NULL instead.
=== Usage ===
New syntax is different:
SELECT mode() WITHIN GROUP (ORDER BY some_value) AS modal_value FROM tbl;
=== See also ===
* Ordered-set aggregate functions in the current manual (including mode())
* Depesz review of ordered-set aggregate functions
* Stackoverflow post demonstrating confict
== mode() for Postgres 9.3 or earlier (superseded in 9.4) ==
PostgreSQL makes it easy to add custom aggregate functions. This snippet is also part of the ulib_agg user-defined library.
Conceptually the process will be to gather each value into an array. Then once all values are in the array, we will run a function to find the most common value in our array. Then you need to create a function to find the most common value in the array.
CREATE OR REPLACE FUNCTION _final_mode(anyarray)
 RETURNS anyelement AS
$BODY$
 SELECT a
 FROM unnest($1) a
 GROUP BY 1 
 ORDER BY COUNT(1) DESC, 1
 LIMIT 1;
$BODY$
LANGUAGE sql IMMUTABLE;
-- Tell Postgres how to use our aggregate
CREATE AGGREGATE mode(anyelement) (
 SFUNC=array_append, --Function to call for each row. Just builds the array
 STYPE=anyarray,
 FINALFUNC=_final_mode, --Function to call after everything has been added to array
 INITCOND='{}' --Initialize an empty array when starting
);
=== Usage ===
SELECT mode(some_value) AS modal_value FROM t;
=== Caution ===
Returns error if the argument is a column of NULL values.
If you are on PostgreSQL 8.3 or below you will need to add the unnest() function to convert an array to a set of rows.
=== See also ===
* Aggregate Range 
* Aggregate Median
* most_common_vals() pg_stats function 
=== External links ===
* A very simple custom aggregate
Category:SQL
Category:Snippets
Aggregate Random

A random value is obtained by the random() buildin function, but sometimes, you want a random element from a grouping. 
== random() ==
This snippet allows you to use random() as an aggregate function. It is also part of the ulib_agg user-defined library.
It should distribute the choices uniformly over each row in the grouping, whether the value selected is NULL or not. (you could modify SFUNC if you wanted to have it select a random non-NULL element)
CREATE OR REPLACE function _final_random(anyarray) 
 RETURNS anyelement AS
$BODY$
 SELECT $1[array_lower($1,1) + floor((1 + array_upper($1, 1) - array_lower($1, 1))*random())];
$BODY$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE random(anyelement) (
 SFUNC=array_append, --Function to call for each row. Just builds the array
 STYPE=anyarray,
 FINALFUNC=_final_random, --Function to call after everything has been added to array
 INITCOND='{}' --Initialize an empty array when starting
);
=== Usage ===
SELECT random(x) AS array_of_randoms_from1_toX FROM t;
Category:SQL
Category:Snippets
Aggregate Range

...?? ... Same as
SELECT MAX(num_value)-MIN(num_value) AS range_val FROM t;
== range() ==
CREATE OR REPLACE FUNCTION _final_range(numeric[])
 RETURNS numeric AS $$
 SELECT MAX(val) - MIN(val)
 FROM unnest($1) val;
$$ LANGUAGE 'sql' IMMUTABLE;
-- Add aggregate
CREATE AGGREGATE range(numeric) (
 SFUNC=array_append, --Function to call for each row. Just builds the array
 STYPE=numeric[],
 FINALFUNC=_final_range, --Function to call after everything has been added to array
 INITCOND='{}' --Initialize an empty array when starting
);
=== Usage ===
SELECT range(num_value) AS range_val FROM t;
=== Caution ===
If you are on PostgreSQL 8.3 or earlier, you will need to add the Array Unnest function.
=== See Also ===
* Aggregate Mode
* Aggregate Median
Category:SQL
Category: Snippets
Aggregate strict min and max

The aggregates strict_min and strict_max behave like their built-in counterparts, except they will return NULL if any input is NULL. This implementation is substantially slower than the built-in aggregates, and cannot make use of index support.
== Strict min and max aggregates implementation ==
-- If no values have been delivered to the aggregate, the internal state is the
-- NULL array. If a null values has been delivered, it is an array with one
-- element, which is NULL. Otherwise, it is an array with one element,
-- the least/greatest seen to this point.
CREATE OR REPLACE FUNCTION strict_min_agg (anyarray,anyelement )
RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
 SELECT CASE
 WHEN $1 IS NULL THEN ARRAY[$2]
 WHEN $1[1] IS NULL THEN $1
 WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve type
 ELSE ARRAY[least($1[1],$2)] END ;
$$;
CREATE OR REPLACE FUNCTION strict_agg_final (anyarray)
RETURNS anyelement LANGUAGE sql IMMUTABLE AS $$
 SELECT CASE when $1 is null then NULL else $1[1] END ;
$$;
CREATE AGGREGATE strict_min (anyelement) (
 sfunc = strict_min_agg,
 stype = anyarray,
 finalfunc = strict_agg_final
);
CREATE OR REPLACE FUNCTION strict_max_agg (anyarray,anyelement )
RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
 SELECT CASE
 WHEN $1 IS NULL THEN ARRAY[$2]
 WHEN $1[1] IS NULL THEN $1
 WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve type
 ELSE ARRAY[greatest($1[1],$2)] END ;
$$;
CREATE AGGREGATE strict_max (anyelement) (
 sfunc = strict_max_agg,
 stype = anyarray,
 finalfunc = strict_agg_final
);
=== parallel enabled implementation ===
-- for versions 9.6 add two functions and change the aggregates.
CREATE FUNCTION strict_max_combine(anyarray, anyarray) RETURNS anyarray
 LANGUAGE sql IMMUTABLE
 AS $_$
 select case 
 when $1 is null then $2
 when $2 is null then $1
 when $1[1] is null then $1
 when $2[1] is null then $2
 else ARRAY[greatest($1[1],$2[1])] END ;
$_$;
CREATE FUNCTION strict_min_combine(anyarray, anyarray) RETURNS anyarray
 LANGUAGE sql IMMUTABLE
 AS $_$
 select case 
 when $1 is null then $2
 when $2 is null then $1
 when $1[1] is null then $1
 when $2[1] is null then $2
 else ARRAY[least($1[1],$2[1])] END ;
$_$;
CREATE AGGREGATE strict_max(anyelement) (
 SFUNC = strict_max_agg,
 STYPE = anyarray,
 FINALFUNC = strict_agg_final,
 COMBINEFUNC = strict_max_combine,
 PARALLEL = safe
);
CREATE AGGREGATE strict_min(anyelement) (
 SFUNC = strict_min_agg,
 STYPE = anyarray,
 FINALFUNC = strict_agg_final,
 COMBINEFUNC = strict_min_combine,
 PARALLEL = safe
);
 
== Usage ==
select strict_min(x) from (values (1),(-4),(NULL),(-87)) f(x);
SELECT group_id, strict_max(some_date) FROM t group by group_id;
Category:SQL
Category: Snippets
AIX

Opportunities to improve AIX support in PostgreSQL
* we don't have the current version of AIX, namely 7.3, in the build farm so we have no idea if it still works...
* based on experience with epoll and kqueue, large AIX systems would probably benefit from using pollset for latch.c (to reduce contention on eg the postmaster pipe in the poll set which otherwise gets locked with high frequency and concurrency across many processes via the poll() system call); here is an experimental patch that seems to work OK, but I have lost access to serious AIX equipment; if there is demand we could propose this and I bet it would speed up NUMA systems (because every network wait with ye olde poll() will do inter-socket locking ping pong on the postmaster pipe without this patch)
* PostgreSQL scales a lot better with huge memory pages; here is an experimental patch that needs to be finished and tested by someone with access to AIX + the ability to get the CAP_BYPASS_RAC_VMM privilege, to test
* PostgreSQL is in the process of gaining optional direct I/O support; see debug_io_direct settings in PostgreSQL 16, with more support features (asynchronous I/O) to follow in 17+. Should we be using O_CIO instead of O_DIRECT on AIX to avoid per-file serialization, or should it be an option, and what does it all mean?
And for the POWER architecture generally (ie would benefit Linux on POWER too):
* some SIMD support was added (see src/include/port/simd.h) to speed up various operations on amd64 and aarch64 architectures, but POWER altivec was not included (early patches that might have some starting points)