nyuuzyou/wikis huggingface dataset:
FullTransactionId

Some history: PostgreSQL has used 32 bit transaction IDs forever. Before PostgreSQL 7.2, when you ran out of 32 bit transaction IDs you had to dump and restore the database cluster. In 7.2 (2005) we got "safe transaction wraparound", but it means that we need to 'freeze' tuples (ie visit them again and rewrite them) some time before the xid counter wraps around. In some deployments that can be a very IO intensive and has been known to lead to outages (avoidable by better planning, but not helped by the inadequate algorithm that autovacuum uses to trigger the work that has no defences against deciding that the whole database needs freeze vacuuming due to wraparound when there isn't enough time left). In theory, by making judicious use of 64 bit xids, we might be able to avoid having to do that completely, so that we only ever write tuples once (with a 64 bit xid you could do 50,000 TPS for over 11 million years; admittedly you'd have to dump-and-restore after that).
In PostgreSQL 12 we introduced a 64 bit transaction ID type called FullTransactionId and started using it as the main generator of transaction IDs, and we started using that in the transaction stack, meaning that any AM code can now use GetTopFullTransactionId() or GetCurrentFullTransactionId().
A couple of small things now take advantage of this:
* GIST indexes use it for page recycling (according to the comment, this is also a problem for btrees, and should be looked into)
* As described in the original thread that lead to the creation of FullTransactionId, txid_current() is now reliable (previously it could, in theory at least on a very busy system, report the wrong epoch depending on the phasing of checkpoints and wraparound)
Some "small projects" about expanding the use of FullTransactionId:
* Proposal to introduce xid8 SQL type and use it in administration interfaces
* It might be nice to switch the xid-based SLRUs (CLOG etc) over to using filenames that are derived from the fxid, so we can get rid of their wraparound support logic; why not just have filenames that go up forever?
* ... more things here ...
Some future "big projects" to use FullTransactionId as the end goal of this work:
* the traditional heap format could be changed to use 64 bit xids without making the tuple header larger, by putting a reference fxid on the page header; then you only need to freeze tuples if the reference fxid gets too old, and that can only happen while you're already writing to the page, so you win
** 2017 thread
** 2019 thread
* new table AMs such as zheap, zedstore have no freezing as a basic design goal and will use fxid rather than xid (along with other systems that make xids less important and get rid of other other reasons to need "vacuum")
TODO: Help find links to previous discussions of 64 bit xid ideas and collate them all here!
Some other relevant blogs and material:
* Post-mortems of wraparound-based outages:
** Dave Cramer's blog
** ... add wraparound rage here ...
* Discussions of solutions
** Thomas Munro's pgcon 2019 talk that touched on this topic
** ...
Functions and anonymous types

It is possible to define a function that returns RECORD, consisting of multiple columns, which forms an Anonymous Type 1
Calling the function with parameters, and retrieving the results as named columns, needs particular syntax...
Here is a working example (pg9) and the work-arounds. (note - the PostGIS geometry is ancillary to the example..)
CREATE OR REPLACE FUNCTION get_buffered_avals_out(
 in_id150 int, 
 in_dist double precision, 
 in_geom geometry,
 OUT id150 int, 
 OUT distance double precision, 
 OUT wkb_geometry geometry,
 OUT a_res_sum float,
 OUT a_emp_sum float,
 OUT du_sum float,
 OUT pop_sum float,
 OUT emp_sum float,
 OUT emp_retail_sum float,
 -- 20 more parameters here --
 OUT pop_age65_up_sum float
 ) 
AS 
$$ 
 select $1 as id150, 
 $2 as distance, 
 $3 as wkb_geometry, 
 sum(r.a_res) as a_res_sum, 
 sum(r.a_emp) as a_emp_sum, 
 sum(r.du) as du_sum, 
 sum(r.pop) as pop_sum, 
 sum(r.emp) as emp_sum, 
 sum(r.emp_retail) as emp_retail_sum, 
 -- 20 more sums here --
 sum(r.pop_age65_up) 
 FROM s_base r WHERE st_dwithin( $3, r.wkb_geometry, $2); 
$$ 
COST 10000
language SQL STABLE strict;
-----------------------------------------------------------
-- create a table of input values for convenience --------
CREATE TABLE tmp_res22 as
select 
 s.id150, 
 t.prod_hbw * 1609.0 as msearch_radius, 
 s.wkb_geometry 
FROM s_subset s 
 INNER JOIN sg_grid150m g ON s.id150 = g.id150 
 INNER JOIN sgt_base t ON g.tay_id = t.tay_id ;
-----------------------------------------------------------
-- A trivial execution of the function using pre-stored params
select get_buffered_avals_out(id150, msearch_radius, wkb_geometry) from tmp_res22;
----------------------------------------------------------
-- in order to get params to the function, and retrieve them by name, this syntax works
select (f).* from (select get_buffered_avals_out(id150, msearch_radius,wkb_geometry) as f from tmp_res22 ) s; 
-- in Pg9, OFFSET 0 is required to prevent the query plan from including unneeded calls to the function
select (f).* from (select get_buffered_avals_out(id150, msearch_radius,wkb_geometry) as f from tmp_res22 offset 0) s;
Funding Requests

== This page is currently undergoing significant revision and should be considered a DRAFT. Once finished and approved, this content will be permanently moving to www.postgresql.org ==
== Sponsorship from the PostgreSQL.Org Fundraising Group ==
The PostgreSQL SPI Charter
How the Fundraising Group votes
Fundraising Group members
----
== Requesting Funding ==
Types of fund requests:
Examples of Fundraising Group fund requests:
* Travel to technical conferences or tradeshows 
** We have provided funds to assist speakers of various PostgreSQL related conferences with their travel expenses
* SWAG (T-shirts, folders, flyers)
* Cost of operations
** PostgreSQL community infrastructure (for web, email, git, etc.)
* Professional services (video editing, graphics)
To request funding, send an email to funds-group <at> postgresql.org describing:
* What you are seeking funds for
* Total estimated amount of requested funds
== What is considered for Funding approval ==
* If requesting funds to provide a talk
** Where the talk is being held
** Expected attendance to the talk
** Cost
** Will you provide the talk under a FOSS or Creative Commons license
* If requesting funds for an event
** Are you charging for the event
** Where are the proceeds going
** What will the result of the event be?
* What is the benefit to the community?
== Professional Services Funding ==
Funding requests will be evaluated, if for professional services such as video editing, graphics design etc., according to the following (not necessarily complete) set of criteria:
* What is the benefit to the community
* At least one competitive bid
** References for each vendor
** Your recommendation for which vendor to select and why
* Provide an estimate from the selected vendor in writing
** The estimate should include exactly what will be provided
** How much wiggle room we have (executive edits, moves, adds, changes)
** When the services will be delivered 
** What the deliverable is
We are *not* necessarily looking for the cheapest solution, but rather the overall *best* solution. We may be willing to pay more if a particular provider has a significantly superior track record (for example).
Category:PGFG
Category:Funds
Category:Donations
Fuzhou Truly

Fuzhou Truly Import&Export Trading Co., Ltd, established since 2007, is a professional manufacturer and exporter of various of knitted and woven collection for baby and children. With the advantage of design ability, mass production ability and strict quality control, our products export to over 20 countries and regions including Europe, America, South America, and Middle East areas, etc.
Our greatest advantage is efficient design just for your market. Each season, we have new design about 500 styles for your choice. We can also design for your according the information on your side, and finish in 2 weeks. Over the last 3 years, over 30 customers from different countries have benefited from our high quality design service.
Our factory located in the center of China fashion industry zone, so we can quickly response from the market change. In this case, we can give our customers high-valuable advices on fabric and accessories to meet different purchasing targets.
We have own brand product series (“TauraBabe”) and small lot wholesale is accepted, which also be welcome by a lot of retail and web-sale owners.
Know more about us,please visit:http://www.fztruly.com/
Fzturly Export baby clothes and children clothes of Taurababe from Fuzhou Truly
http://www.fztruly.com/
Fuzhou Truly is a apparel OEM and it own factory for baby and children clothes with good qulity and favorite price.We can also meet you our respetcetable customers requirement by OEM,ODM customization and contract manufacturing services.
Tauramall Tauramall — Online store of baby & children clothes, maternity supplies,pet products and more 
http://www.tauramall.com
Tauramall is a professional online store for children clothes,baby clothes,maternity supplies,baby care,toys & gift,pet products at reasonable prices and quick shipping. 
Fzturly CN Chinese Fzturly
http://www.fztruly.com.cn
GCC 13 build failures

== Known GCC 13 build issues with some extensions ==
* jsquery: https://github.com/postgrespro/jsquery/issues/45
* postgres_osm_pbf_fdw: https://github.com/vpikulik/postgres_osm_pbf_fdw/issues/20
* pgimportdoc: https://github.com/okbob/pgimportdoc/issues/5
Gdblive script

#!/bin/sh
 
 # Usage: gdblive [ arguments to grep output of ps ]
 
 cd $HOME
 
 # tee /dev/tty is for user to see the set of procs considered
 if [ $# -eq 0 ]
 then
 PROCS=`ps auxww | \
 grep postgres: | \
 grep -v -e 'grep postgres:' -e 'postgres: stats' -e 'postgres: writer' -e 'postgres: wal writer' -e 'postgres: checkpointer' -e 'postgres: archiver' -e 'postgres: logger' -e 'postgres: autovacuum' | \
 tee /dev/tty | \
 awk '{print $2}'`
 else
 PROCS=`ps auxww | \
 grep postgres: | \
 grep -v -e 'grep postgres:' -e 'postgres: stats' -e 'postgres: writer' -e 'postgres: wal writer' -e 'postgres: checkpointer' -e 'postgres: archiver' -e 'postgres: logger' -e 'postgres: autovacuum' | \
 grep $@ | \
 tee /dev/tty | \
 awk '{print $2}'`
 fi
 
 if [ `echo "$PROCS" | wc -w` -eq 1 ]
 then
 exec gdb $PGINSTROOT/bin/postgres -silent "$PROCS"
 else
 exec gdb $PGINSTROOT/bin/postgres -silent
 fi
Generating a stack trace of a PostgreSQL backend

A stack trace is a list showing roughly how a program got to a certain point in its execution and what it is doing right now. If you have a copy of PostgreSQL that's unexpectedly using lots of CPU, or is sitting there doing nothing when it should be busy, or is throwing an unexplainable error, a stack trace may help you or somebody on the PostgreSQL mailing list figure out why.
A stack trace can also be obtained when a program crashes, providing potentially important information about what the program was doing when it crashed and where it went wrong.
Before you go to the trouble of collecting stack trace information please read the Guide to reporting problems and make sure you are running the latest patch release of your major version. If you're running PG 14.1 and the current version in the v14 series is 14.12, you're probably wasting your time tracking down a problem that was long since fixed.
How to get a stack trace varies by operating system.
* Getting a stack trace of a running PostgreSQL backend on Windows
* Getting a stack trace of a running PostgreSQL backend on Linux/BSD
Category:Operating system
GeoIP index

If your are using PostgreSQL + ip2location (or MaxMind), creating the following spatial index on the ip2location table results in a huge performance increase.
Create a spatial index on the ip_from/ip_to attributes:
-- Taken from http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Fast_interval_.28of_time_or_ip_addresses.29_searching_with_spatial_indexes
CREATE INDEX ip2location_range_gist ON ip2location USING gist ((box(point(ip_from,ip_from),point(ip_to,ip_to))) box_ops);
analyze verbose ip2location;
explain analyze
select *
from ip2location
where box(point(ip_from,ip_from),point(ip_to,ip_to)) @> box(point (3512069689,3512069689), point(3512069689,3512069689));
Query version where the IP needs to be converted to bigint:
explain analyze
select *
from ip2location
where box(point(ip_from,ip_from),point(ip_to,ip_to))
 @> box(
 point(inet_to_bigint('209.85.238.57'),inet_to_bigint('209.85.238.57')),
 point(inet_to_bigint('209.85.238.57'),inet_to_bigint('209.85.238.57'))
 );
The inet_to_bigint functions come from that same page:
-- from http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Bigint_to_ip
drop function if exists bigint_to_inet(bigint);
create function bigint_to_inet(bigint) returns inet as $$
select (($1>>24&255)||'.'||($1>>16&255)||'.'||($1>>8&255)||'.'||($1>>0&255))::inet
$$ language sql strict immutable;
grant execute on function bigint_to_inet(bigint) to public;
drop function if exists inet_to_bigint(inet);
CREATE OR REPLACE FUNCTION inet_to_bigint(inet) RETURNS bigint AS $$
SELECT $1 - inet '0.0.0.0'
$$ LANGUAGE SQL strict immutable;
grant execute on function inet_to_bigint(inet) to public;
added by bricklen
== See also ==
* GeoIP via PLPython
Category:Indexes
GeoIP via PLPython

Simple Python functions for accessing a local MaxMind GeoIP database. Usually the GeoIP databases are installed in /usr/share/GeoIP/, files named `GeoIP.dat`, `GeoIPCity.dat` and `GeoIPOrg.dat`. A sample `GeoIP.dat` is usually included with the geoip package, for free.
== GeoIP Country database ==
CREATE TYPE geoip_country as (
 country_code text,
 country_name text
);
CREATE OR REPLACE FUNCTION
geoip_country_by_addr(addr inet) RETURNS geoip_country
LANGUAGE plpythonu AS $$
 import GeoIP
 gi = GeoIP.open('/usr/share/GeoIP/GeoIP.dat', GeoIP.GEOIP_STANDARD)
 code = gi.country_code_by_addr(addr)
 name = gi.country_name_by_addr(addr).decode('iso8859-1')
 return (code, name)
$$;
Example:
db=> select * from geoip_country_by_addr('200.46.204.71');
 country_code | country_name 
--------------+--------------
 PA | Panama
db=> select (geoip_country_by_addr('200.46.204.71')).country_name;
 country_name 
--------------
 Panama
== GeoIP City database ==
CREATE TYPE geoip_record as (
 country_code text,
 country_code3 text,
 country_name text,
 region text,
 region_name text,
 city text,
 postal_code text,
 latitude real,
 longitude real,
 metro_code int,
 area_code int,
 dma_code int,
 time_zone text
);
CREATE OR REPLACE FUNCTION
geoip_record_by_addr(addr inet) RETURNS geoip_record
LANGUAGE plpythonu AS $$
 import GeoIP
 gi = GeoIP.open('/usr/share/GeoIP/GeoIPCity.dat', GeoIP.GEOIP_STANDARD)
 rec = gi.record_by_addr(addr)
 if not rec:
 return None
 return (
 rec['country_code'],
 rec['country_code3'],
 rec['country_name'].decode('iso8859-1'),
 rec['region'],
 rec['region_name'].decode('iso8859-1') if rec['region_name'] else None,
 rec['city'].decode('iso8859-1') if rec['city'] else None,
 rec['postal_code'],
 rec['latitude'],
 rec['longitude'],
 rec['metro_code'],
 rec['area_code'],
 rec['dma_code'],
 rec['time_zone'])
$$;
Example:
db=> \x
db=> select * from geoip_record_by_addr('207.173.203.187');
-[ RECORD 1 ]-+--------------------
country_code | US
country_code3 | USA
country_name | United States
region | OR
region_name | Oregon
city | Portland
postal_code | 
latitude | 45.5183982849
longtitude | -122.655403137
metro_code | 820
area_code | 503
dma_code | 820
time_zone | America/Los_Angeles
== GeoIP Organization database ==
CREATE OR REPLACE FUNCTION
geoip_org_by_addr(addr inet) RETURNS text
LANGUAGE plpythonu AS $$
 import GeoIP
 gi = GeoIP.open('/usr/share/GeoIP/GeoIPOrg.dat', GeoIP.GEOIP_STANDARD)
 org = gi.org_by_addr(addr)
 if org:
 return org.decode('iso8859-1')
$$;
Example
db=> SELECT geoip_org_by_addr('189.61.111.0');
 geoip_org_by_addr 
----------------------------------
 NET Serviços de Comunicação S.A.
== See also ==
* GeoIP index
German-Speaking PostgreSQL Conference 2011

= German-Speaking PostgreSQL Conference 2011 Talks =
* Conference Page: http://2011.pgconf.de/
== Talks: Friday 11th November, 2011 ==
=== Room 1 ===
* 09:50 - 10:35: 
* 10:40 - 11:25: Stop Tuning your Data Warehouse (Andreas Scherbaum)
* 11:30 - 12:15: 
* 13:00 - 13:45: 
* 13:50 - 14:35: 
* 15:00 - 15:45: (Sebastian "tokkee" Harl)
* 15:50 - 16:35: 
=== Room 2 ===
* 09:00 - 09:45: Keynote: Darf ich vorstellen: PostGIS! (Astrid Emde)
* 09:50 - 10:35: 
* 10:40 - 11:25: Entwickeln Sie Ihre “powered by PostgreSQL” Anwendung innerhalb von Minuten. Native - Web - Mobile (Geert Houben)
* 11:30 - 12:15: Michael Meskes: Kostenkiller Datenbank? - Raus aus der Kostenfalle (Michael Meskes)
* 13:00 - 13:45: Einführung in Anwendungsentwicklung mit PostgreSQL 9 (Peter Andreas Möller)
* 13:50 - 14:35: 
* 15:00 - 15:45: Servoy und PostgreSQL bringen Ihre Anwendung in die Cloud (Geert Houben)
* 15:50 - 16:35: 
* 16:40 - 17:10: 
=== Room 3 ===
* 09:50 - 10:35: 
* 10:40 - 11:25: 
* 11:30 - 12:15: BI goes Big Data (Stefan Schmit)
* 13:00 - 13:45: 
* 13:50 - 14:35: 
* 15:00 - 15:45: 
* 15:50 - 16:35: Logfile oder Datenbank, wohin mit all den Eventlogs? (Felix Borchers)
Category:PostgreSQL Europe Category:PostgreSQL Germany Category:PostgreSQL EventsCategory:Deutsch
German-Speaking PostgreSQL Conference 2013

= German-Speaking PostgreSQL Conference 2013 Talks =
== Conference Website ==
http://2013.pgconf.de/
== Talks: Friday 8th November, 2013 ==
=== Room 1 ===
* Elefanten sind in Mode (Jan Mussler)
* Whats New In Postgres 9.3 (EN]) (Robert Treat)
* (Markus Winand)
* (Achim Eisele, Jens Wilke)
* (Marc Balmer)
* (Christoph Berg)
* (Michael Gengenbach)
=== Room 2 ===
* The World's Largest Postgres Install (Peter van Hardenberg)
* PostgreSQL - Eines der wichtigsten Open-Source Projekte (Michael Meskes)
* (Hans-Jürgen Schönig)
* (Eric Winter)
* PostgreSQL in 5 Jahren (Marc Linster)
* PostgreSQL & Business Intelligence (Simon Riggs)
* (Harald Armin Massa)
* The state of in-core changeset extraction (Andres Freund)
* (Andreas Scherbaum)
=== Room 3 ===
* Bäume und Graphen (Stephan Wagner) (Ausgefallen)
* Einfach Blättern mit PostgreSQL (Markus Winand)
* (Björn Häuser)
* (Bernd Helmle)
* (Stefan Keller)
* (Michael Renner)
* (Gabriele Bartolini)
* Time-series Datensätze in PostgreSQL (Sebastian Harl)
Category:PostgreSQL Europe Category:PostgreSQL Germany Category:PostgreSQL EventsCategory:Deutsch
German-Speaking PostgreSQL Conference 2015

= German-Speaking PostgreSQL Conference 2015 Talks =
== Conference Website ==
http://2015.pgconf.de/
== Workshops: Thursday 26th November, 2015 ==
=== Lotse ===
* PostgreSQL Hochverfügbar - Adrian Vondendriesch, Michael Banck
=== Reeper ===
* PostgreSQL Replikation - Hans-Jürgen Schönig
* Backup - tomorrow you will wish you did it today - Simon Riggs
=== Seiler ===
* Tipps und Tricks zur besseren PostgreSQL-Performance - Sandra Wiecki
* Einführung in die Verwaltung von Geodaten in der PostgreSQL Datenbank mit PostGIS (Folien/Übungen) - Astrid Emde
== Talks: Friday 27th November, 2015 ==
=== Keynotes ===
* Working in and with Open Source Communities - Bernd Erk
* Open Source - Segen oder Fluch? - Michael Meskes
=== Lotse ===
* A Postgres DBA's Toolbelt - Kaarel Moppel
* Migration von Oracle nach PostgreSQL aus Sicht eines Postgres-DBA - Alvar C.H. Freude
* Bugs fixed, systems integrated - Gianni Ciolli
* PostgreSQL Hooks - Wie man mit Modulen das DB-Verhalten ändert - Georg Hasibether, Thomas Warwaris
* Der wachsende Einsatz von Postgres weltweit: Zahlen und Fakten - Sandra Wiecki
* Logdateien verarbeiten mit Postgres - Danilo Endesfelder
=== Reeper ===
* Performance improvements in 9.5 and beyond - Tomas Vondra
* Weg vom Mainframe: Migrationsstudie von DB2 UDB zu PostgreSQL - Michael Banck
* Indexes of PostgreSQL - Emre Hasegeli
* Wenn Seltenes den Unterschied macht - Extreme Verteilungen in PostgreSQL - Stefan Litsche
* PostgreSQL als NoSQL Datenbank - Harald Armin Massa
* Die PostgreSQL Performance Schnelldiagnose - Hans-Jürgen Schönig
=== Seiler ===
* EXPLAIN verstehen - Hans-Jürgen Schönig
* Tis but a scratch! - Gunnar "Nick" Bluth
* State of BDR 2015 - Christoph Moench-Tegeder
* Lessons learned and future plans - Simon Riggs
* Spilo: highly-available PostgreSQL clusters anywhere - Oleksii Kliukin
* Data science mit PostgreSQL - Balázs Bárány
Category:PostgreSQL Europe Category:PostgreSQL Germany Category:PostgreSQL EventsCategory:Deutsch
Getting list of all children from adjacency tree

== Description ==
Assuming you have Adjacency List based tree - i.e. table with structure similar to this:
 Table "public.test"
 Column | Type | Modifiers
-----------+---------+---------------------------------------------------
 id | integer | not null default nextval('test_id_seq'::regclass)
 parent_id | integer |
 x | text |
Indexes:
 "test_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
 "test_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES test(id)
you might want to get all children of given element.
To do it, you can use this function:
CREATE OR REPLACE FUNCTION get_all_children_array(use_parent INT4) RETURNS INT4[] as $$
DECLARE
 process_parents INT4[] := ARRAY[ use_parent ];
 children INT4[] := '{}';
 new_children INT4[];
BEGIN
 WHILE ( array_upper( process_parents, 1 ) IS NOT NULL ) LOOP
 new_children := ARRAY( SELECT id FROM test WHERE parent_id = ANY( process_parents ) AND id <> ALL( children ) );
 children := children || new_children;
 process_parents := new_children;
 END LOOP;
 RETURN children;
END;
$$ LANGUAGE plpgsql;
== Usage ==
# select get_all_children_array(3);
 get_all_children_array
------------------------
 {5,6,9}
(1 row)
# select * from test where id = any( get_all_children_array(3) );
 id | parent_id | x
----+-----------+---
 5 | 3 | e
 6 | 3 | f
 9 | 5 | i
(3 rows)
==PostgreSQL 8.4+==
Common Table Expressions (CTE) make it much easier to write this as a query instead of as a procedure:
First, we write the CTE to return all ancestors of the item:
WITH RECURSIVE tree AS (
 SELECT id, ARRAY[]::integer[] AS ancestors
 FROM test WHERE parent_id IS NULL
 UNION ALL
 SELECT test.id, tree.ancestors || test.parent_id
 FROM test, tree
 WHERE test.parent_id = tree.id
) SELECT * FROM tree WHERE 0 = ANY(tree.ancestors);
This query could return something like this:
 id | ancestors
------------------------
 3 | {}
 5 | {3}
 6 | {3}
 9 | {3,5}
(4 rows)
Now that we have an array of ancestors for each item, retrieving the descendants is a simple WHERE clause:
WITH RECURSIVE tree AS (
 SELECT id, ARRAY[]::integer[] AS ancestors
 FROM test WHERE parent_id IS NULL
 UNION ALL
 SELECT test.id, tree.ancestors || test.parent_id
 FROM test, tree
 WHERE test.parent_id = tree.id
) SELECT * FROM tree WHERE 3 = ANY(tree.ancestors);
Given the result above, this query would return the following:
 id | ancestors
------------------------
 5 | {3}
 6 | {3}
 9 | {3,5}
(3 rows)
Category:PL/pgSQL