nyuuzyou/wikis huggingface dataset:
Skytools3Todo

= Skytools 3 TODO =
Currently this page is used for documentation coordination, later for other stuff too.
== Documentation ==
=== Generic stuff ===
==== Skytools package overview ====
http://skytools.projects.postgresql.org/skytools-3.0/doc/README.html
==== pgq overview ====
* db side queue
* producer, consumer, ticker
* batches
==== londiste overview ====
* pgq consumer
* trigger-based
* handlers?
* lazy copy?
=== User howtos: Londiste ===
==== How to set up simple replication ====
* Set up provider and subscriber
* Adding/removing tables
* mention that tables are copied one-by-one, but fkeys will still work
==== How to set up cascaded replicaiton ====
* setting up cascaded replication
* change-provider
* takeover
* status
==== How to set up table partitioning (handlers) ====
* Usage of existing handlers
==== How to merge partitioned database ====
* Set up partitions
* Set up merged database
* Set up replication
=== Other User HOWTOs ===
==== walmgr: setup failover ====
==== walmgr: backup ====
==== qadmin: queue & replication management ====
=== Script manpages ===
==== londiste ====
* sub-command reference docs
==== walmgr ====
http://skytools.projects.postgresql.org/skytools-3.0/doc/walmgr.html
==== qadmin ====
http://skytools.projects.postgresql.org/skytools-3.0/doc/qadmin.html
=== Developer Howto ===
==== How to write PgQ consumer ====
==== How to write PgQ cascaded consumer ====
==== How to write PgQ cooperative consumer ====
==== How to write Londiste handler ====
=== SQL API docs ===
Most functions have docstrings, although some of them may be quite light.
Needed is intro for each module, why is it for, and overview of
technical design.
==== pgq SQL API ====
* pgq
==== pgq_coop SQL API ====
* pgq_coop
==== pgq_node SQL API ====
* pgq_node
==== pgq_ext SQL API ====
* Intro about task it does: pgq-nodupes.html
* Needs docstrings to couple functions
==== londiste SQL API ====
* londiste
=== Python API docs ===
* skytools, pgq, londiste: http://skytools.projects.postgresql.org/skytools-3.0/api/
Category:Tool
SlonyToDo

= Slony short todo/roadmap =
This page discusses plans for futures versions of slony.
Patches Requested sections tracks items desired and which would make good introductory tasks for
developers wanting to get familiar with the Slony code base.
== Slony 2.2 ==
/!\ This section is not up-to-date /!\
=== Targetted for 2.2 ===
The slony team is planning on adding the following items to 2.2.
Feature 	BugZilla Entry	Notes
Multi-Node FAILOVER	Bug 178 	was blocked by Bug 179
===Patches Requested===
The following items are desired for version 2.2.
If you would like to work on one of these items then let the slony1-hackers mailing list know.
Feature 	BugZilla Entry	Notes
slonik -f option for Preamble	Bug 183 	
== Slony 2.1 RoadMap ==
Consequences of SlonyBrainstorming November/December 2010.
===Targetted for 2.1===
The slony team is planning on adding the following items to 2.1
Feature 	BugZilla Entry	Notes
Automatic WAIT FOR 	Bug 179 	Completed for 2.1
Multi-Node FAILOVER 	Bug 178 	Postpone to 2.2, was blocked by Bug 179
TCP KEEP ALIVE support 	Bug 126 	Completed for 2.1 (windows status ?)
TIMESTAMP with TIMEZONE	Bug 163 	Completed for 2.1
Easier adding of tables	Bug 181 	Completed for 2.1
===Patches Requested===
The following items are desired for version 2.1.
If you would like to work on one of these items then let the slony1-hackers mailing list know.
Feature 	BugZilla Entry	Notes
slonik -f option for Preamble 	Bug 183 	Postpone to 2.2
'DATE' slonik command 	Bug 180 	Completed for 2.1
Option to not echo DDL from Slonik	Bug 152 	Completed for 2.1
Category:Slony
Slow Counting

Index-only scans have been implemented in Postgres 9.2. providing some performance improvements where the visibility map of the table allows it.
A full count of rows in a table can be comparatively slow in PostgreSQL:
SELECT count(*) FROM tbl;
The reason is related to the MVCC implementation in PostgreSQL. The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table. PostgreSQL must walk through all rows to determine visibility. This normally results in a sequential scan reading information about every row in the table. EXPLAIN ANALYZE reveals what's going on:
postgres=# EXPLAIN ANALYZE SELECT COUNT(*) FROM tbl;
 QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=4499.00..4499.01 rows=1 width=0) (actual time=465.588..465.591 rows=1 loops=1)
 -> Seq Scan on tbl (cost=0.00..4249.00 rows=100000 width=0) (actual time=0.011..239.212 rows=100000 loops=1)
 Total runtime: 465.642 ms
(3 rows)
Worth noting that only the full count must be so pessimistic; if augmented with a "WHERE" clause like:
SELECT COUNT(*) FROM tbl WHERE status = 'something';
PostgreSQL will take advantage of available indexes against the restricted field(s) to limit how many records must be counted. This can greatly accelerate such queries. PostgreSQL will still need to read the resulting rows to verify that they exist. Other database systems may only need to reference the index in this situation.
== Estimating the row count ==
PostgreSQL can query "estimates" or "cached" values for a table's size (much faster). See Count estimate for details.
Category:FAQ
Category:Performance
Slow Counting/ja

以下の記事は9.2より前のPostgreSQLバージョンにのみ適用されることに注意してください。今はインデックスオンリースキャンが実装されています。
以下の例のようなテーブル内の全行数を数えることは、PostgreSQLの性能が遅いことが分かっている操作の１つです。
SELECT COUNT(*) FROM table
これが低速となる理由はPostgreSQLのMVCC実装に関連します。
複数のトランザクションが異なるデータ状態を参照することができることは、"COUNT(*)"のためにテーブル全体に渡るデータをまとめる簡単な方法があり得ないことを意味します。
別の見方をすると、PostgreSQLは必ずすべての行をたどります。
これは通常、テーブル内の全行に関する情報をシーケンシャルスキャンを使用して読み取ることになります。
問い合わせがどのように進んでいるかを確認する優れた方法はEXPLAIN ANALYZEを使用することです。
postgres=# EXPLAIN ANALYZE SELECT COUNT(*) FROM accounts;
 QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=4499.00..4499.01 rows=1 width=0) (actual time=465.588..465.591 rows=1 loops=1)
 -> Seq Scan on accounts (cost=0.00..4249.00 rows=100000 width=0) (actual time=0.011..239.212 rows=100000 loops=1)
 Total runtime: 465.642 ms
(3 rows)
悲観的にならざるを得ないのがこの厳密な集約構文だけであることがわかることには価値があります。もし次のように"WHERE"句があったとすると、PostgreSQLは限定されたフィールドに対して利用可能なインデックスを利用して、数えなければならないレコードの行数を制限します。
SELECT COUNT(*) FROM table WHERE status = 'something'
これによりこうした問い合わせは大きく高速化されます。PostgreSQLはまだ、行が存在するかどうかを検証するために結果行を読み取る必要があります。他のデータベースシステムでは、こうした状況ではインデックスを参照する必要があるだけかもしれません。
== 行数の推定 ==
おおよその行数だけが必要である場合、PostgreSQLには1つの代替方法があります。
これは以下のようにpg_classカタログのreltuplesフィールドを使用することです。
pgbench=# select reltuples from pg_class where relname='tellers';
 reltuples 
-----------
 250
この前提となるのは、統計情報が最新情報を維持できるほど十分にテーブルに対してANALYZEを実行していることです。
他にもトリガを基にした機構を使用してテーブル内の行数を数える方法がよく使われます。
これらの技法の片方、または両方の説明は以下にあります。
* Counting Rows
* Tracking the Row Count
* 元原稿: Why PostgreSQL Instead of MySQL (これにはMySQLとどのように異なるかについても議論されています)
Category:FAQ
Category:Performance
Category:Japanese
SLRU improvements

== Mailing List Threads ==
Below is a list of recent mailing list threads covering SLRU improvements:
* XID formatting and SLRU refactorings. The work has completed.
* SLRU optimization - configurable buffer pool and partitioning the SLRU lock. The work has completed.
* SLRUs in the main buffer pool. The thread is inactive.
* Proposal to add page headers to SLRU pages. The work is ongoing.
== Ideas ==
Many ideas have been proposed and worked on. This section provides a summary of the discussions.
=== 64-bit identifiers ===
In XID formatting and SLRU refactorings, to avoid the wraparound headache, all 32-bit identifiers have been upgraded to 64-bit. No change has been made to the SLRU mechanisms. The work has completed.
=== Add the buffer page header to each SLRU page ===
In SLRUs in the main buffer pool, among many ideas proposed in the thread, it proposed to add a page header to each SLRU page. Rishu Bagga, Andres Freund, Robert Hass, and Stephen Frost discussed over this topic and agreed that adding page headers to SLRU pages are beneficial for the long run and using the existing buffer page header is preferable.
The idea has been revived by Proposal to add page headers to SLRU pages. The benefit of adding page headers includes:
* It allows checksum to be enabled for SLRU pages as well.
* Page LSN is tracked within the page, and removes the need of the ad-hoc LSN group structure within the SLRU control data.
* It paves the way for further enhancements, e.g. encryption, combining SLRU caches with the main buffer pool.
=== Move SLRU to the main buffer pool ===
This is the main topic of SLRUs in the main buffer pool. The discussion lacked focus and clarity. Here is a summary of opinions:
* There should be a single pool for buffers and SLRU caches.
* Different eviction strategies between buffer and SLRU caches may be beneficial.
* Improvements on per-SLRU latch may still be needed to reduce lock contention.
=== Lock partition ===
This is the main topic of SLRU optimization - configurable buffer pool and partitioning the SLRU lock. The idea is straightforward. Instead of using a single latch for the entire SLRU cache, partition the cache into multiple banks and use a separate latch for each bank. Due to performance considerations, the design also allows using a single latch for a group of banks to reduce the number of latches.
== SLRU Related Articles ==
* https://www.enterprisedb.com/blog/life-bug-customer-escalation-postgresql-commit
* https://pganalyze.com/blog/5mins-postgres-17-configurable-slru-cache
* https://aws.amazon.com/blogs/database/multixacts-in-postgresql-usage-side-effects-and-monitoring/
* https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
Soundex

Soundex is a simple algorithm for comparing/normalizing English words based on phonetic similarity.
PostgreSQL 8.3 and later include a soundex(text) function in the fuzzystrmatch extension. Note that this function behaves differently from some other implementations, as it does not skip similar consonants that are separated by 'w' and 'h'.
== PL/pgSQL version ==
If you can't install the fuzzystrmatch extension (due to limited privieges or other reasons), here is an alternative version in PL/pgSQL. This function behaves identically to the fuzzystrmatch implementation. (Tested on PostgreSQL 9.0 and 9.1, using dictionaries in many different languages).
----
CREATE OR REPLACE FUNCTION soundex(input text) RETURNS text
IMMUTABLE STRICT COST 500 LANGUAGE plpgsql
AS $$
DECLARE
 soundex text = ;
 char text;
 symbol text;
 last_symbol text = ;
 pos int = 1;
BEGIN
 WHILE length(soundex) < 4 LOOP
 char = upper(substr(input, pos, 1));
 pos = pos + 1;
 CASE char
 WHEN THEN
 -- End of input string
 IF soundex = THEN
 RETURN ;
 ELSE
 RETURN rpad(soundex, 4, '0');
 END IF;
 WHEN 'B', 'F', 'P', 'V' THEN
 symbol = '1';
 WHEN 'C', 'G', 'J', 'K', 'Q', 'S', 'X', 'Z' THEN
 symbol = '2';
 WHEN 'D', 'T' THEN
 symbol = '3';
 WHEN 'L' THEN
 symbol = '4';
 WHEN 'M', 'N' THEN
 symbol = '5';
 WHEN 'R' THEN
 symbol = '6';
 ELSE
 -- Not a consonant; no output, but next similar consonant will be re-recorded
 symbol = ;
 END CASE;
 IF soundex = THEN
 -- First character; only accept strictly English ASCII characters
 IF char ~>=~ 'A' AND char ~<=~ 'Z' THEN
 soundex = char;
 last_symbol = symbol;
 END IF;
 ELSIF last_symbol != symbol THEN
 soundex = soundex || symbol;
 last_symbol = symbol;
 END IF;
 END LOOP;
 RETURN soundex;
END;
$$;
== Example ==
db=# select column1, soundex(column1) from (values ('Robert'), ('Rupert'), ('Rubin'), ('Ashcroft')) data;
 column1 | soundex 
----------+---------
 Robert | R163
 Rupert | R163
 Rubin | R150
 Ashcroft | A226
db=# create table words(word text);
db=# copy words from '/usr/share/dict/words';
db=# create index on words(soundex(word));
db=# select * from words where soundex(word)=soundex('hotel');
 word 
--------
 hotel
 hotly
 huddle
Category:PL/pgSQL
SQL MERGE

=== Status ===
Note: MERGE is often (incorrectly) used interchangeably with the term UPSERT.
UPSERT functionality will be in the PostgreSQL 9.5 release -- see What's new in PostgreSQL 9.5
Simon Riggs proposed a patch to implement MERGE in 2017, as part of the Postgres v11 release cycle. See the dedicated wiki page for details of that.
=== Description ===
MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent. 
To implement this cleanly requires that the table have a unique index so duplicate checking can be easily performed. It is possible to do it without a unique index if we require the user to LOCK the table before the MERGE.
=== History ===
Discussion of MERGE progress in PostgreSQL:
* 2005-11-11 - Someone working to add merge?, by Jaime Casanova
* 2005-11-11 - MERGE vs REPLACE, by Peter Eisentraut
* 2008-04-16 - MERGE SQL Statement, by Simon Riggs
* 2008-04-21 - MERGE Specification, by Simon Riggs
* 2008-04-30 - Internal design of MERGE, with Rules, by Simon Riggs
* 2010-05-12 - MERGE Syntax, by Peter Eisentraut
* 2010-05-13 - Add MERGE command GSoC 2010, by Bxzhai
** 2010-08-02 - MergeImplementationDetails, by Bxzhai
** 2010-08-06 - MergeTestExamples, by Bxzhai
* 2011-01-18 - Discussion of Merge patch challenges, by Greg Smith
* 2014-07-20 - SQL MERGE is quite distinct from UPSERT, by Peter Geoghegan
* 2017-10-27 - New MERGE project, by Simon Riggs
== Likely Implementation Order ==
Note: This section is from 2010 and may not reflect current progress.
Due to the way this feature needs to be implemented internally, the following is the likely sequence in which this feature will be built:
# PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking (SQL Server for example). Improvements to the index implementation are needed to allow this feature.
# Add a subset of MERGE support sufficient to implement REPLACE/UPSERT. The full MERGE feature set is a bit broader than this.
# Document how to use the new MERGE feature in lieu of REPLACE/UPSERT, to make transitions easier for users of other databases. Actually adding other vendor's non-standard syntax to make other apps work transparently is unlikely to be accepted as a patch.
# Complete implementing the entirety of the feature set for SQL MERGE.
Category:SQL Keyword
SQL Server PostgreSQL terminology

= SQL Server -> PostgreSQL terminology =
SQL Server term 	PostgreSQL term
Instance 	Cluster
Schema 	Schema
Database 	Database
User 	Login role
Table 	Relation or Table
Indexed View 	Materialized View
Square brackets 	Double quote (Identifiers and Key Words)
Buffer Pool 	shared_buffers
User connections 	max_connections
Dedicated administrator connection (DAC)	superuser_reserved_connections
File locations 	data_directory
DBMS (Database Management System) 	ORDBMS (Object Relational Database Management System)
Query Cache 	N/A
Query Store 	N/A
JSON 	JSON & JSON types
Licensing 	PostgreSQL License
Transaction Log 	WAL (Write Ahead Log)
GETDATE() 	CURRENT_TIMESTAMP (returns timestamptz)
CURRENT_TIMESTAMP 	CURRENT_TIMESTAMP (returns timestamptz)
DATEADD(day, 1, '2022-01-01') 	'2022-01-01'::date + '1 day'::interval
MERGE 	INSERT ... ON CONFLICT & MERGE (PG15+)
SELECT * FROM information_schema.tables 	\dt (in psql)SELECT * FROM information_schema.tables (in general)
SELECT * from sys.databases 	\l (in psql)SELECT * FROM pg_catalog.pg_database (in general, information_schema is limited to the current database)
EXEC sp_help 'dbo.table_name' 	\d table (in psql, other tools vary)
back to terminology
Category:Migration to PostgreSQL
SQL standard

This page contains some information for developers about the SQL standard.
First, there is some information in the PostgreSQL documentation:
* https://www.postgresql.org/docs/current/features.html
* https://www.postgresql.org/docs/current/sql-keywords-appendix.html
== Getting the standard ==
The standard needs to be purchased from ISO or a member organization.
At ISO, search for "9075" (the number of the standard), e.g., https://www.iso.org/search.html?q=9075.
You can also go to an ISO member organization (ANSI, BSI, DIN, etc.) and buy it there. Buying from a member organization might be less expensive (or not).
For most work related to PostgreSQL, you only need part 2 (that is, ISO/IEC 9075-2), also called "SQL/Foundation". The other parts are for special areas of functionality, not all of which are implemented in PostgreSQL. (See the PostgreSQL documentation linked above for more details on the parts.)
== Participating in the development of the standard ==
The SQL standard is developed by ISO/IEC JTC1 SC32 WG3, which stands for joint technical committee 1, subcommittee 32, working group 3. The committee and subcommittee levels are mostly for administrative work, so don't worry about that initially. The real work happens in the working group.
To be able to participate in the working group, you need to join a national body that is a member of ISO, and join the local committee or group that mirrors the international group. They will then delegate you to the international group. The appropriate national body is ideally the one in the country where you live, or in some cases alternatively in the country where your employer is. Each national body sets their own rules about this. Start by checking the web site of the national body most relevant to you and contact them. (It might just work to ask something like "I want to join ISO/IEC JTC1 SC32 WG3, can you get me in touch with the local mirror committee".)
When you join, what you get is a login to the content management system, and then you can start reading documents and see the meeting invitations and go to meetings.
SC32 has a web page at ISO: https://www.iso.org/committee/45342.html. If you have questions about how to get in touch with the working group or the corresponding national body, you can contact the committee manager listed there.
Of course, PostgreSQL community members who are WG3 participants can also help you.
SSIS Components for PostgreSQL by CData

== About ==
The SSIS Components for PostgreSQL by CData enable you to connect SQL Server with PostgreSQL data through SSIS Workflows. The components wrap the complexity of accessing PostgreSQL data in standard SSIS data flow components. You can then connect and synchronize PostgreSQL tables with SQL Server tables.
The components hide the complexity of accessing data and provide additional security features, smart caching, batching, socket management, and more.
Category:Tool
SSIS Data Flow Components for PostgreSQL

== About ==
Devart SSIS Data Flow Components for PostgreSQL allows you to integrate PostgreSQL data with other databases and cloud applications via SQL Server Integration Services (SSIS). They include optimized PostgreSQL Source, PostgreSQL Destination, and PostgreSQL Lookup components and provide their own PostgreSQL Connection Manager with lots of PostgreSQL-specific connectivity features.
== Key Features: ==
* PostgreSQL-specific Connection Features. Devart SSIS components for PostgreSQL include their own connection manager, offering support for a lot of PostgreSQL-specific and other features. It offers support for secure SSL connections, ability to connect via proxy, to set protocol version, etc.
* Convenient Devart PostgreSQL Source Editor. Devart PostgreSQL Source component offers a convenient editor, which displays all the connection tables and their columns and allows you to quickly build a query to PostgreSQL via drag-n-drop. It also displays the list of available system and user variables of the package, allowing you to quickly add them to your select statements. If necessary, you can also quickly preview data, returned by the source, and check whether you query returns what you need.
* Advanced Lookup Optimizations. Devart PostgreSQL Source component offers very high performance because of the advanced optimization techniques used. It uses select statements that check several rows at once, and caches the returned results to reduce the number of round-trips to the server.
* Database-specific Optimizations in Devart PostgreSQL Destination To speed up loading data to PostgreSQL, Devart PostgreSQL Destination uses the PostgreSQL COPY command. This is the fastest mode for bulk inserting data to PostgreSQL.
Category:Tool
SSIS Data Flow Components for PostgreSQL by Devart

== About ==
SSIS Data Flow Components for PostgreSQL by Devart allows you to integrate PostgreSQL data with other databases and cloud applications via SQL Server Integration Services (SSIS). They include optimized PostgreSQL Source, PostgreSQL Destination, and PostgreSQL Lookup components and provide their own PostgreSQL Connection Manager with lots of PostgreSQL-specific connectivity features.
== Key Features: ==
* PostgreSQL-specific Connection Features. Devart SSIS components for PostgreSQL include their own connection manager, offering support for a lot of PostgreSQL-specific and other features. It offers support for secure SSL connections, ability to connect via proxy, to set protocol version, etc.
* Convenient Devart PostgreSQL Source Editor. Devart PostgreSQL Source component offers a convenient editor, which displays all the connection tables and their columns and allows you to quickly build a query to PostgreSQL via drag-n-drop. It also displays the list of available system and user variables of the package, allowing you to quickly add them to your select statements. If necessary, you can also quickly preview data, returned by the source, and check whether you query returns what you need.
* Advanced Lookup Optimizations. Devart PostgreSQL Source component offers very high performance because of the advanced optimization techniques used. It uses select statements that check several rows at once, and caches the returned results to reduce the number of round-trips to the server.
* Database-specific Optimizations in Devart PostgreSQL Destination To speed up loading data to PostgreSQL, Devart PostgreSQL Destination uses the PostgreSQL COPY command. This is the fastest mode for bulk inserting data to PostgreSQL.
Category:Tool
SSIS Driver for PostgreSQL by CData

== About ==
The SSIS Driver for PostgreSQL by CData enable you to connect SQL Server with PostgreSQL data through SSIS Workflows. The components wrap the complexity of accessing PostgreSQL data in standard SSIS data flow components. You can then connect and synchronize PostgreSQL tables with SQL Server tables.
The components hide the complexity of accessing data and provide additional security features, smart caching, batching, socket management, and more.
Category:Tool
Stable Release Backport Packages

== Ubuntu ==
* https://launchpad.net/~pitti/+archive/postgresql
== Redhat/CentOS ==
* http://yum.pgrpms.org/howtoyum.php
== SuSE ==
* http://svn.pgrpms.org/browser/rpm/suse
== Suggestions for a better name for this resource ==
Please paint the bikeshed here!
Category:Linux distribution
Stado

== Project Overview ==
Stado is an open source shared-nothing database system for data warehousing designed for parallel querying. Sequel to GridSQL.
== Project Status ==
Version 2.5 released on 2011-09-14 
== Resorces ==
* http://youtu.be/yOt5Zavslig Jim Mlodgenski discusses scaling PostgreSQL queries with Stado. Topics covered include Stado architecture, query flow, scaling, and limitations.
== Project Contacts ==
* Project website: http://launchpad.net/stado
Category:Replication
Standard conforming strings

It is being proposed to turn standard_conforming_strings on by default in PostgreSQL 9.1. Use this page to track which drivers and (widely used) applications are prepared or not. Please provide version numbers and references.
== OK ==
=== Drivers ===
* DBD::Pg 2.7.0 (May 2008) 
* psycopg2 2.0.7 
* JDBC 8.2-504 (Dec 2006) 
== Not OK ==
== Yet to check ==
Category:PostgreSQL 9.1