nyuuzyou/wikis huggingface dataset:
StateOfICU

= Intro =
Collation is how strings are compared and sorted. The simplest approach is to "memcmp" the strings, which is what the C locale does.
Collation affects predicates, but it also affects the structure of an index, which depends on a consistent ordering.
= Providers =
More complex collations use a provider, which may be either ICU or glibc. A different provider or different version may produce a different collation order, which risks corrupting indexes (necessesitating REINDEX).
= Benefits of ICU =
* Platform-independent semantics
* Performance
** Abbreviated keys
** Seems to be faster in general (at least compared to some versions of glibc)
** http://smalldatum.blogspot.com/2023/05/postgres-16beta1-looks-good-vs-sysbench.html
* Features https://www.postgresql.org/docs/devel/collation.html#ICU-CUSTOM-COLLATIONS:
** Case-insensitive and/or accent-insensitive
** Ignore punctuation
** Treat sequence of digits as a single number
* Not libc
** collations change in different versions
** limited ability to control the version of libc that you use
= Risks =
* Unknown unknowns
* Ordering differences
** Though that can happen due to different libcs, or different versions of any provider)
* ICU has its own bugs
** u_versionToString(): https://unicode-org.atlassian.net/browse/ICU-22215 ("astonishingly bad" -- Robert Haas)
* some "obsolete" locales are no longer recognized in newer versions of ICU
** C
** fr_FR@euro
** de__PHONEBOOK
= Done =
* Canonicalization to language tag
* Consistency in interpretation of "und"
* Handle language tags in ICU < 54
* New built-in collations (Peter Eisentraut):
** UNICODE: root collation
** UCS_BASIC: code point order (memcmp for UTF-8)
* ICU rules
* Documentation
** https://www.postgresql.org/docs/devel/locale.html#ICU-LOCALES
** https://www.postgresql.org/docs/devel/collation.html#ICU-CUSTOM-COLLATIONS
= TODO =
* Redefine iculocale=C/POSIX (and "C.anything"/"POSIX.anything"?) to mean memcmp/pg_ascii
* Make LOCALE (and --locale) apply to ICU
** the fact that locale doesn't apply to ICU creates a situation described as "maximally confusing"
= Questions =
Opinions about ICU technically?
* Quality?
* Performance?
* User experience?
Direction: opinions about pushing users toward ICU to be the preferred collation provider?
Timing: opinions about the steps that have been taken or should be taken in version 16? Defaults?
= Notes =
* ...
Statistics the planner doesn't have that it really needs

The planner/optimizer is a voracious consumer of information, no matter how
much we feed it it always wants more and better data. Some things we really
need to make better decisions:
* "clusteredness" metric to replace the use of "correlation" for estimating how much random access and cache hit rate for index scans
* Cross-column dependencies to deal with selectivity estimates for clauses
on multiple columns. Currently we assume they're independent which can lead to
overly optimistic estimates.
* Estimating n_distinct is a hard problem and
probably requires scanning much larger samples to get good data. A good
algorithm was [? published].
Category:Development
Storing Archival Data

Archival data is data that will never be updated or deleted, stored in tables that will never have additional inserts. It is most commonly found in history tables, which are often partitioned, where a table holds some subset of data generated some time in the past. The following list is a checklist for storaging that data in the most efficient manner possible. 
verify table has fillfactor of 100%
Since the data will never be modified again, we should use as few pages as possible. Note this is the default setting as of postgresql 8.3, but should be verified in case it has been modified. 
modify storage parameters
This presumes your table has varlen columns to deal with. Anyone have strong recommendations for which storage setting to use? ISTM it is very data dependent, but might be good to list use cases
 re-write the table
two methods for this, depending on if you want to cluster the data in your table. 
 vacuum full method 
drop indexes 
vacuum full table
create indexes with fillfactor 100% 
 cluster method 
verify indexes have fillfactor of 100%
cluster table on index
vacuum freeze
vacuum freeze helps stave off any vacuuming in the future, reducing overhead on a number of levels. Anyone know if it also removes any pages for recent changes from the fsm?
move table/indexes to archive tablespace
if you have setup special storage for archival data, you can then move the tables and indexes over to that storage. again, this is pretty implementation dependent, but anyone have thoughts on archival storage systems they want to share?
Category:Performance Category:Administration
String Functions and Operators Compatibility

Various databases have various standard and non-standard string functions and operators, but they often don't explain enough in the edge cases. This page shows the compatibility of those operations.
== Functions ==
=== concat ===
Feature 	code 	PostgreSQL 9.1	MySQL	Oracle 	DB2
non-text args	concat('a', 123) 	a123 	a123 	a123 	a123
NULL args 	concat('a', NULL) 	a 	NULL 	a 	a
3+ args 	concat('a', 'b', 'c')	abc 	abc 	(not supported)	(not supported)
=== concat_ws ===
'ws' means 'with separator'.
Feature 	code 	PostgreSQL 9.1	MySQL
non-text args 	concat_ws(',', 'a', 123) 	a,123 	a,123
NULL args 	concat_ws(',', 'a', NULL) 	a 	a
3+ args 	concat_ws(',', 'a', 'b', 'c')	a,b,c 	a,b,c
NULL separator	concat_ws(NULL, 'a', 'b') 	NULL 	NULL
=== left ===
Feature 	code 	PostgreSQL 9.1	MySQL 	SQL Server	DB2
positive length	left('abcde', 2) 	ab 	ab 	ab 	ab
zero length 	left('abcde', 0) 	(empty) 	(empty)	(empty) 	(empty)
negative length	left('abcde', -2)	abc 	(empty)	ERROR 	ERROR
* Negative length in PostgreSQL: left(str, -len) = left(str, length(str) - len)
=== right ===
Feature 	code 	PostgreSQL 9.1	MySQL 	SQL Server	DB2
positive length	right('abcde', 2) 	de 	de 	de 	de
zero length 	right('abcde', 0) 	(empty) 	(empty)	(empty) 	(empty)
negative length	right('abcde', -2)	cde 	(empty)	ERROR 	ERROR
* Negative length in PostgreSQL: right(str, -len) = right(str, length(str) - len)
=== reverse ===
Feature 	code 	PostgreSQL 9.1	MySQL	SQL Server
multibyte safe?	reverse('...')	Yes 	Yes 	Yes
== Operators ==
=== || ===
Feature 	code	PostgreSQL	Oracle	DB2 	SQL Server (+)	
non-text args	'a' 	123 	a123 	a123	a123 	ERROR
NULL args 	'a' 	NULL 	NULL 	a 	NULL 	NULL
* MySQL doesn't support || operator to join strings. Use concat() instead. (|| means OR in MySQL.)
* SQL Server doesn't support || operator to join strings. Use + instead.
Category:Documentation, Category:MySQL , Category:Migration to PostgreSQL
Strip accents from strings

create or replace function unaccent(text) returns text language plpythonu as $$
import unicodedata
rv = plpy.execute("select setting from pg_settings where name = 'server_encoding'");
encoding = rv[0]["setting"]
s = args[0].decode(encoding)
s = unicodedata.normalize("NFKD", s)
s = .join(c for c in s if ord(c) < 127)
return s
$$;
=== See also ===
Strip accents from strings, and output in lowercase
Category:PL/Python
Strip accents from strings, and output in lowercase

Autor: Thom Brown
Extraídas de la lista de correo pgsql-general.
CREATE OR REPLACE FUNCTION unaccent_string(text) RETURNS text AS $$
my ($input_string) = @_;
$input_string =~ s/[âãäåāăą]/a;
$input_string =~ s/[ÁÂÃÄÅĀĂĄ]/A;
$input_string =~ s/[èééêëēĕėęě]/e;
$input_string =~ s/[ĒĔĖĘĚ]/E;
$input_string =~ s/[ìíîïìĩīĭ]/i;
$input_string =~ s/[ÌÍÎÏÌĨĪĬ]/I;
$input_string =~ s/[óôõöōŏő]/o;
$input_string =~ s/[ÒÓÔÕÖŌŎŐ]/O;
$input_string =~ s/[ùúûüũūŭů]/u;
$input_string =~ s/[ÙÚÛÜŨŪŬŮ]/U;
return $input_string;
$$ LANGUAGE plperl;
CREATE OR REPLACE FUNCTION unaccent_string(text)
RETURNS text
IMMUTABLE
STRICT
LANGUAGE SQL
AS $$
SELECT translate(
 $1,
 'âãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
 'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
);
$$;
=== Convert any text to URI (case folded ascii) from string ===
Author: Pim Koeman
License: CC Attribution-ShareAlike 3.0 Unported 
CREATE OR REPLACE FUNCTION name2uri(text)
RETURNS text
IMMUTABLE
STRICT
LANGUAGE SQL
AS $$
SELECT replace(trim(regexp_replace(translate(
 lower($1),
 'áàâãäåāăąèééêëēĕėęěìíîïìĩīĭḩóôõöōŏőùúûüũūŭůäàáâãåæçćĉčöòóôõøüùúûßéèêëýñîìíïş',
 'aaaaaaaaaeeeeeeeeeeiiiiiiiihooooooouuuuuuuuaaaaaaeccccoooooouuuuseeeeyniiiis'
), '[^a-z0-9\-]+', ' ', 'g')),' ', '-');
$$;
=== See also ===
Strip accents from strings
Category:PL/pgSQL
Category:PL/Perl
Strposrev

The string function strposrev(instring text, insubstring text) returns the position of a substring within a string, starting from the end, reverse to the built in strpos(text,text) function. It is like the instrrev() function known from vb, or the string.lastindexof() method known from java and csharp.
CREATE OR REPLACE FUNCTION strposrev(instring text, insubstring text)
 RETURNS integer AS
$BODY$
 DECLARE result INTEGER;
 BEGIN
 IF strpos(instring, insubstring) = 0 THEN
 -- no match
 result:=0;
 ELSEIF length(insubstring)=1 THEN
 -- add one to get the correct position from the left.
 result:= 1 + length(instring) - strpos(reverse(instring), insubstring);
 ELSE 
 -- add two minus the legth of the search string
 result:= 2 + length(instring)- length(insubstring) - strpos(reverse(instring), reverse(insubstring));
 END IF;
 RETURN result;
 END;
$BODY$
 LANGUAGE plpgsql IMMUTABLE STRICT
 COST 4;
== see also ==
* functions-string strpos(text,text)
Category:PL/pgSQL
Sudoku puzzle

Published by , based on an Oracle version by Anton Scheffer 
WITH RECURSIVE x( s, ind ) AS
( SELECT sud, position( ' ' IN sud )
 FROM (SELECT '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28 419 5 8 79'::text 
 AS sud) xx
 UNION ALL
 SELECT substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
 , position(' ' in repeat('x',ind) || substr( s, ind + 1 ) )
 FROM x
 , (SELECT gs::text AS z FROM generate_series(1,9) gs) z
 WHERE ind > 0
 AND NOT EXISTS ( SELECT null
 FROM generate_series(1,9) lp
 WHERE z.z = substr( s, ( (ind - 1 ) / 9 ) * 9 + lp, 1 )
 OR z.z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
 OR z.z = substr( s, mod( ( ( ind - 1 ) / 3 ), 3 ) * 3
 + ( ( ind - 1 ) / 27 ) * 27 + lp
 + ( ( lp - 1 ) / 3 ) * 6
 , 1 )
 )
)
SELECT s
FROM x
WHERE ind = 0;
Sudoku solver

Not so fast sudoku solver by me. The input format is: '_' for empty cells, while 'b' to 'j' means 1..9. The board is saved in column-major order, meaning the first column is encoded from top to bottom in the first 9 chars of the string, the following 9 chars are the second column, and so on.
TIP: You can create new sudokus saving a game with ksudoku.
with recursive board(b, p) as (
 -- sudoku board expressed in column-major order, so substr() can be used to fetch a column
 values ('__g_cd__bf_____j____c__e___c__i___jd__b__h___id____e__g__b__f_e_f____g____j_h__c_'::char(81), 0)
 union all select b, p from (
 -- generate boards:
 select overlay(b placing new_char from strpos(b, '_') for 1)::char(81), strpos(b, '_'), new_char
 from board, (select chr(n+ascii('b')) from generate_series(0, 8) n) new_char_table(new_char)
 where strpos(b, '_') > 0
 ) r(b, p, new_char) where
 -- make sure the new_char doesn't appear twice in its column
 -- (there are two checks because we are excluding p itself):
 strpos(substr(b, 1+(p-1)/9*9, (p-1)%9), new_char) = 0 and
 strpos(substr(b, p+1, 8-(p-1)%9), new_char) = 0 and
 -- make sure the new_char doesn't appear twice in its row:
 new_char not in (select substr(b, 1+i*9+(p-1)%9, 1)
 from generate_series(0, 8) i
 where p <> 1+i*9+(p-1)%9) and
 -- make sure the new_char doesn't appear twice in its 3x3 block:
 new_char not in (select substr(b, 1+i%3+i/3*9+(p-1)/27*27+(p-1)%9/3*3, 1)
 from generate_series(0, 8) i
 where p <> 1+i%3+i/3*9+(p-1)/27*27+(p-1)%9/3*3)
) select
 -- the following subquery is used to represent the board in a '\n' separated human-readable form:
 ( select string_agg((
 select string_agg(chr(ascii('1')+ascii(substr(b, 1+y+x*9, 1))-ascii('b')), ) r
 from generate_series(0, 8) x), E'\n')
 from generate_series(0, 8) y
 ) human_readable,
 b board,
 p depth,
 (select count(*) from board) steps
 from board where strpos(b,'_') = 0 limit 5000;
This example shows how to generically code any backtracking algorithm using Recursive CTE with the following steps:
* Place the initial case before the union of the recursive CTE.
* After the UNION place a query generating the next possible candidate values. In this query you'll be reading the Recursive CTE; make sure you only read incomplete candidates by specifying such condition in a where of that query.
* You may (and probably should) prune invalid candidates if you surround the subquery generating candidate values in another subquery, filtering it with the pruning condition. Pruning is important, not only for speed but because PostgreSQL stores every row this query returns (all the candidate solutions) until it's not needed anymore, and you may run out of disk space (although you can limit that space in PostgreSQL 9.2+ with temp_file_limit, or use a separate temp tablespace otherwise).
* Finally, in the outmost you'll have all the candidates available in the Recursive CTE table. As in this case, if you pruned all the invalid candidates, then all the complete candidates will also be the valid solutions.
The space of possible states will be processed in BFS (breadth-first search).
Support KNN for SP-GiST GSoC 2014

== Intro ==
As of version 9.4 Postgres' space-partitioned gists lack the support of the k-nearest-neighbour search query though this set of data structures allow rather effective implementation of one. So the goal of the project is to introduce such support. 
== Outcome ==
Currently Postgres SP-GiST generalization includes three main datastructures: text trie, k-d tree and quad tree. Trie deals with sets of text strings and the most common use of both k-d and quad trees is the partition of points in the n-dimensional space. In terms of operating with point sets, the query logic is straightforward and allows to retrieve k points from it with the least distance to a given one over the whole set. The usecases of such a request for text datasets are less intuitive but still can prove to be usefull (example of such could be e.g. bioinformatics genome manipulation applications). 
== Goals == 
* (main) Implement KNN support for k-d and quad trees
* (additional) Implement KNN support for text tries
* (additional, under concideration) Implement tree-rebuild techniques to guarantee logarithmic complexity over all types of point distribution in a dataset. (as introduced here: )
== Schedule ==
Pre- May 22
* dig into codebase to estimate the actual implementation
* discussions with community:
** practicability and usefullness of implementing the tree-rebuild technique 
*** (in case concidered usefull) - it's usage details - auto\manual, syntax, etc.
** any uncertainities with codebase in case they appear
** any additional goals
May 22 - June 23
* basic implementation of knn for quad tree so that it works inside the pgsql environment
June 23 - June 27
* submitting midterm meta 
* discussion of the achived results
June 27 - August 18
* adjust quad tree implementation to act as a basement for the one of k-d tree
* based on quad tree implementation experience, implement knn for k-d tree
* implement knn for text tries (different from the two previous and much easier, given the actual experience of coding in pgsql environment)
* any adjustments that arised
August 18 - August 22
* preparation for final term and meta
Surveys

== Community Surveys ==
=== www.postgresql.org/community ===
We run an ongoing one-question survey on the www.postgresql.org/community page. Typically these are answered by between 200 and 400 users. They are highly unscientific and are interesting rather than terribly informative.
Survey Results
=== 8.4.0 Release Community Survey ===
Run jointly with EnterpriseDB marketing staff, this was a survey of
users that ran for 6 weeks immediately following the version 8.4.0 in
July 2009. Most of the respondents were people who downloaded the
One-Click Installer. This survey is more complete and informative
covering Postgres users, applications, environments, and some
commercial considerations. Please note that the audience is limited
to early adopters of version 8.4. Also, the questions on the survey
pretty much exclude any useful responses from OEMs.
View Online: SurveyMethods
Download files: Media:PostgreSQL_Community_Survey_20090722_Final.zip (includes graphs, XLS and CSV data)
=== New Community Survey ===
We need to develop a comprehensive community survey to be run 1-2 months after each new release. This survey should be designed to answer questions more useful to the community. It should probably also run on our infrastructure. Work on this has not been started.
Category:Advocacy
SVG using ditaa

== ditaa ==
ditaa (github; binary version 0.11) is a command-line utility written in Java, that handles diagrams. The syntax of source files is similar to ascii art, the default file extention is .txt .
ditaa supports SVG since version 0.11.
java -jar ditaa.jar <sourcefile> --svg -E -S -o
== Limitations (version 0.11) ==
* There is no escape mechanism for key-characters which may occur in a text or formula: minus (-), plus (+), equal (=), ... 
* No support for Markdown or similar formatting rules
Category:Documentation
Category:SVG
SVG using Graphviz

== Graphviz ==
Graphviz (https://graphviz.org/) is a package of tools which visualize directed and undirected graphs with different strategies:
* dot - default for directed graphs
* neato - "spring model" layouts
* fdp - similar to neato
* sfdp - multiscale version of fdp for the layout of large graphs
* twopi - radial layouts
* circo - circular layout
The syntax of source files is DOT (https://en.wikipedia.org/wiki/DOT_(graph_description_language) ), the default file extention is .gv.
== Example ==
The typical header of our files looks like this:
digraph {
	layout=dot;
	// default values
	node [shape=box, label="", fontname="sans-serif", style=filled, fillcolor=white, width=2.2, fontsize=8];
	graph [fontname="sans-serif"]; // must be specified separately
	edge [fontname="sans-serif"]; // must be specified separately
	// an unobtrusive background color
	pad="1.0, 0.5";
	bgcolor=whitesmoke;
	// layout of edges and nodes
	splines=ortho;
	nodesep=0.3;
	ranksep=0.3;
	label="An example graph" fontsize=26;
	...
	...
 
Category:Documentation
Category:SVG
Swiss PGDay 2016

Swiss PGDay 2016 - Die Schweizer Tagung zur freien Datenbank Postgres
* Date: Fr. 24. Juni 2016
* Location: University of Applied Sciences Rapperswil (HSR Hochschule für Technik Rapperswil), Switzerland
* Homepage: http://www.pgday.ch/
* Twitter/News: https://twitter.com/swiss_pgday
Category:PostgreSQL Events
Switching PostgreSQL documentation from SGML to XML

For about the last 10 years, there have been occasional discussions about switching the PostgreSQL documentation from SGML to XML (whatever that might mean). Here are some points to consider.
== Technical points ==
There is a distinction between changing the source format from SGML to XML versus switching the tool chain from DSSSL to XSLT. Even though DSSSL is typically associated with SGML and XSLT with XML, in fact all combinations work more or less equally well.
== Arguments on SGML vs XML ==
* DocBook 5, the next major version, will no longer publish an SGML DTD. But DocBook 5 has been in the works for years and it might be many more years until it is the standard version and all previous versions have disappeared.
* SGML is easier to edit than XML, because of tag minimization and some other simplifications.
* SGML supports conditional sections (used to build the INSTALL file, for example). That needs a different solution for XML.
* There might be more editing tools that support XML, but this needs to be substantiated.
* Translation teams are using XML.
* We'd probably want to rename *.sgml -> *.xml. Easier now with Git, but needs some planning.
* With xmllint, we could a sort of pgindent on the documentation.
== Arguments on DSSSL vs XSLT ==
* The DSSSL tools have been unmaintained for years.
* Small question marks on FOP (XSLT to PDF tool): Is it maintained, is there a stable version (as opposed to endless development pre-releases), can it run on an entirely free Java installation? Should dblatex be used instead of FOP?
* The customizations that we have done to the DSSSL stylesheets would have to be ported to XSLT. This work could start already.
* The XSLT build is currently a lot slower than the DSSSL build.
* XSLT might offer more flexibility such as partial builds.
* There are more tools that work with XSLT in various ways.
== Formats We Generate ==
A compleat solution needs to be able to generate all of the following output forms, 
* HTML
* Manual pages
* RTF
* PDF and Postscript
** A4 format
** US format
* TeXinfo
* ePub
== Toolchain Components ==
In order to do a switchover, it will be necessary to replace all of the components. It is worth enumerating these components, so that nobody makes the error of thinking that some partial solution to one part represents a complete solution.
; docbook-utils
: Consists of scripts that transform DocBook into some useful forms (html, pdf, rtf, man pages)
; docbook-dsssl
: These are the DSSSL stylesheets used to render DocBook into various output forms. They might be replaced by XSLT stylesheets.
; jade
: A DSSSL engine
; openjade
: An alternative DSSSL engine
; jadetex
: A DSSSL engine generating TeX-based output
Category:Development
Synchronous replication

Synchronous replication is available starting in PostgreSQL 9.1 by enabling the synchronous_standby_names parameter. It includes user-controlled durability specified on the master using the synchronous_commit parameter. The design also provides high throughput by allowing concurrent processes to handle the WAL stream. 
== Design Notes ==
See also Synchronous Replication 9/2010 Proposal, though those notes pertain to a patch different than what has been committed.
Category:Replication
SysBench

SysBench is an open-source benchmarking program originally targeted for use on MySQL databases. It can also be used for a variety of low-level system tests, from CPU to disk I/O.
When running the more complicate OLTP tests included, it has been known to some issues with locking on PostgreSQL, but at least read-only it seems to work fine. 
Suggested reading:
* Sysbench with PostgreSQL on Solaris
* Introducing FreeBSD 7.0: This OS was developed using SysBench as an OS and database agnostic test framework usable for comparing across many platform combinations.
* Running Sysbench 0.4.12 on Ubuntu 9.04: SysBench can be difficult to compile on some systems, this gives some suggestions for one popular combination.
* PostgreSQL History: Shows how various PostgreSQL versions improved over time using the SysBench read-only OLTP test as the common benchmark
Category:Benchmarking