nyuuzyou/wikis huggingface dataset:
Using Echelon SQL Scripter with PostgreSQL

== Using Echelon SQL Scripter with PostgreSQL ==
Echelon SQL Scripter (http://www.digitalthought.net/echelon/) is your best friend when it comes to rapid development of web scripts to interface with databases.
Originally created by Matt Springfield (current maintainer of Echelon-PHP), Echelon allows the web developer to modify database information very quickly when using HTML forms.
By simply posting your HTML form to Echelon, it will create all of your INSERT, UPDATE and DELETE SQL on the fly, then redirect back to a user specified target URL.
The key with the Echelon script is it uses the form element names as the column names in the SQL statement. So in building a HTML form you simply name the form elements the same as the corresponding column names in your table.
Echelon then loops through the array, builds the appropriate SQL statement, connects to the database, then redirects to the file of your choice.
Echelon was initially designed to work with PostgreSQL. All of the scripting languages which are presently supported by Echelon, PHP and JSP/Servlets as of this writing, fully support PostgreSQL.
== Installation ==
Implementing it into your PostgreSQL backend is quite simple. Here are the steps you need to perform :
# Uncompress and install Echelon
#* For PHP: Simply place it in your web directory.
#* For JSP: Place the JSP scripts within your web directory and place the .class files in your servlet container's CLASSPATH.
# Configure Echelon to use your database
#* Either echelon.php or echelon.jsp needs to be edited.
#* It needs to know the database server name, database name, port number, username, and password required to access your data.
# Setup your database tables. If you have existing database tables you may skip this step. Keep in mind though your HTML form input names must be the same as your database column names.
# Create your HTML form. BE SURE to name your input field names the same as your database columns! If you don't, Echelon will not work!
Examples of how to write your HTML for INSERT, UPDATE, and DELETE queries are in the README files of each distribution.
== The Future ==
Echelon SQL Scripter is an open source project distributed under the GNU GPL license. As advocates of open source, we believe this project has incredible potential in the open source community.
As Echelon becomes a greater asset to developers, we would like to see this project continue to grow in user base and functionality. Future versions of Echelon will include a web-based configuration script to tailor it to your personal app and support for all databases.
We also plan to develop an Active Server Pages version, and database and web development tools within Echelon. If you are interested in contributing to the Echelon project please email any of the maintainers listed below.
== Acknowledgements ==
The Echelon Project Home Page.
Echelon-PHP is maintained by Matt Springfield
Echelon-Java is maintained by Branden R. Williams
This document was co-written by Branden R. Williams and Matt Springfield.
Category:Tool
Using EXPLAIN/ja

文の実行に時間がかかる原因を見つけ出すためにはEXPLAINコマンドを使用します。
2つの方法で実行できます。
EXPLAIN ANALYZEを使用する場合、文は実際に実行されます。このため、プランナがどのように想定して進行したかと、実際に何が起こったかを比較することができます。
データを変更する文の場合、EXPLAIN ANALYZEを実行した時にも変更が発生することに注意してください。文によりデータベースに何も変化を与えないようにさせたければ、単にEXPLAINを使用してください。
EXPLAINの出力の解析を助けるツールがいくつかあります。
* pgadminには、実際に何が発生しているかの詳細の理解を助ける、ビジュアルEXPLAINツールが含まれます。Reading PgAdmin Graphical Explain Plansを参照してください。
* Visual Explain。元々はEnterprise DBで維持、改良され、EnterpriseDB Advanced ServerパッケージにバインドされていたRedHat component。これはソースコードを使用した他のPostgreSQLインストレーションに対して Developer Studioパッケージを実行することで構築可能です。
* explain.depesz.comは抽出・要約した回数を付けて計画を示します。また選択した基準を元に強調表示します。
* explain-analyze.infoは問い合わせを割合で分析し、推定値が現実と大きくずれた部分を強調表示します。（現在利用できません。）
また、この話題に関するチュートリアルがいくつか存在します。ただほとんどのタイトルが"Explaining Explain"となっています(;;)
* Performance Tips
* Efficient SQL by Greg Sabino Mullane (2003)
* Explaining Explain: pdf OpenOffice Presentation by Robert Treat (2005)
* Explaining Explain by Lukas Smith (2006)
* Explaining Explain by Greg Stark (2008)
* Query Execution Techniques in PostgreSQL by Neil Conway
* Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT by Jim Nasby
* The PostgreSQL Query Planner by Robert Haas (2010)
* PostgreSQL 9.0 High Performance (2010) はEXPLAINの使用、問い合わせ計画結果の理解、異なる計画に変更させる方法に関して長く説明された書籍です。
プランナがおかしな決定を行う原因としてよくある問題は、Planner Statisticsの更新が維持されていないことです。
他にも、サーバに利用可能なメモリ量を通知するチューニングパラメータをデフォルトの非常に小さな値のままにしていることがあります。
例えば、標準的な設定では1MB以上必要なソートはメモリ内で処理するには大きすぎるためディスクに吐き出されます。
Tuning Your PostgreSQL Serverでは、メモリサイズの指定や問い合わせ計画作成に大きく影響するチューニングパラメータに関して優れた実践を紹介しています。
計画に関して途方に暮れた時の行動のひとつとして、完全なEXPLAIN ANALYZEの出力を問い合わせとそこに含まれるスキーマを付けてpgsql-performanceメーリングリストに投稿することがあります。
より早く有益な回答を得るために投稿する前にSlowQueryQuestionsを読んでください。
PostgreSQLの最新バージョンに近いものを使用していない場合は、ほとんどの回答はおそらく以降のバージョンで問題は解消しているというものになるでしょう。
少なくとも使用中のバージョンの最新のマイナーバージョン（つまり8.2.3を使用しているならば8.2.6(最新のバージョンが8.2.6ならば)までアップグレード）で試し、計画が改良されるかどうかを確認することで助かることがあるかもしれません。
計画の解釈時間を省き、テーブル内のデータが増大した時にどう変化するかがわかる、先端的な技法があります。
これは統計情報の収集頻度をどの程度にすればよいかを検討する際にも参考になります。
Putting EXPLAIN results into a tableは自動化用の技法を提供します。explainでよく使われるオプションにはpl/pgsqlの簡単な例があります。
Category:FAQCategory:PerformanceCategory:Japanese
Using EXPLAIN/ru

Чтобы выяснить причины длительного выполнения запроса, воспользуйтесь командой EXPLAIN.
Выполнить ее можно двумя способами:
* EXPLAIN ANALYZE фактически выполнит запрос, что позволит сравнить ожидаемый план запроса с непосредственно выполненным. Внимание! Если запрос манипулирует данными, то выполнение с EXPLAIN ANALYZE также приведет к изменениям.
* EXPLAIN выведет ожидаемый план выполнения без обращения к данным.
Существуют инструменты, упрощающие анализ вывода EXPLAIN:
* pgAdmin включает инструмент визуализации вывода EXPLAIN. См. Интерпретация графического представления планов в PgAdmin.
* Visual Explain, изначально выпущенный в составе компонента RedHat, и позже доработанный EnterpriseDB, сейчас поставляется в составе пакета EnterpriseDB Advanced Server. Его можно собрать для использования с PostgreSQL из исходного кода Developer Studio.
* explain.depesz.com выводит план с акцентом на суммарные и промежуточные временные затраты и выбранные критерии.
Ниже приведены ссылки на материалы:
* Подсказки по производительности
* Эффективный SQL Грега Сабино Муллана (Greg Sabino Mullane), 2003
* Разъяснения по Explain: pdf и презентация OpenOffice Роберта Трита (Robert Treat), 2005
* Разъяснения по Explain Лукаса Смита (Lukas Smith), 2006
* Разъяснения по Explain Грега Старка (Greg Stark), 2008
* Техники выполнения запросов в PostgreSQL Нила Конвея (Neil Conway)
* Введение в VACUUM, ANALYZE, EXPLAIN, и COUNT Джима Нэсби (Jim Nasby)
* Планировщик запросов PostgreSQL Роберта Хааса (Robert Haas), 2010
* Высокопроизводительный PostgreSQL 9.0, 2010. Это книга, глубоко затрагивающая предмет использования EXPLAIN, чтения планов и принятия мер по оптимизации.
Основной проблемой выбора плохих планов является устаревание статистики планировщика. Другая проблема заключается в игнорировании настройки параметров памяти сервера, чаще всего используются значения по умолчанию, а они ничтожно малы. Например, в минимальной конфигурации, сортировки, для которых требуется больше 1MB памяти, будут писаться на диск. Настройка вашего сервера PostgreSQL освещает лучшие практики по расчету памяти и другим параметрам оптимизации, оказывающим влияние на планы запросов.
Если вы не можете самостоятельно разобраться с планом, обратитесь к рассылке pgsql-performance, предоставив полный вывод EXPLAIN ANALYZE, схему и проблемные запросы. Чтобы получить ответы стоящие и быстро, прочтите вопросы по медленным запросам перед тем, как задать вопрос. Также, если вы используете версию PostgreSQL отличную от текущей стабильной, вполне возможно, что эта проблема уже решена в последней. Обновление в рамках мажорной версии потенциально может сэкономить ваше время. Например, обновитесь с версии 8.2.3 до 8.2.6 и посмотрите повторно на план выполнения. Помогло?
Продвинутой техникой является периодическое сохранение планов по мере роста объема данных таблиц и проведение их сравнительного анализа. Это может натолкнуть на мысль об изменении агрессивности сбора статистики, например. Простой пример на pl/pgsql приведен в обсуждении Общие возможности Explain.
Category:FAQ
Category:Performance
Category:PGAdmin
Category:Russian
Uso de Vacuum

* El vacuum es el proceso en el cual se eliminan definitivamente tuplas marcadas para borrar y hay una reorganización de datos a nivel físico.
* Puede realizar vacuum utilizando el comando externo 'vacuumdb' y el cual puede recibir parámetros para realizar los diferentes tipos de vaciamiento:
** FREEZE
** FULL
** ANALYZE
** S/Parametros de tipo
* Este comando es muy útil para automatizar vaciamientos a través de cualquier sincronizador de tareas (ya sea el cron de *nix u otro de Windows).
* Asimismo, existe la opción del Autovacuum, cuya funcionalidad es ir realizando de manera paulatina la mantención de nuestra base. Previamente y antes de activar esta funcionalidad, es recomendable leer sobre las consideraciones a tener en cuenta, para no degradar la performance de nuestro servidor.
Category:Español
V13 build failures for extensions

This page includes common failures of the PostgreSQL extensions built against v13.
= Overview =
PostgreSQL APT and RPM repo maintainers are testing builds against v13, and found the following issues so far:
= convert_tuples_by_name() =
Error message: too many arguments to function call, expected 2, have 3
Reason: The function changed in v13
Related commit: fe661259
Category:Extensions
Category:Development
Vacuuming/ja

原文最終更新日：4 August 2016
適切なVacuum作業、特に 自動バキュームデーモン 設定は、高速で信頼性のあるデータベースのために重要な事柄です。
*定常的なバキューム作業
*Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT by Jim Nasby.
*VACUUM FULL とそれを避けるべき理由（訳注：8.4以前が対象の記事です。）
*Usama Munin Dar's blog entry: "Autovacuum internals"（訳注：英語サイト）
*自動Vacuum作業
*pg_autovacuum（訳注：pg_autovacuumカタログは8.4以降はなくなりました。）
Category:AdministrationCategory:Performance
Category:SQL
Category:Japanese
Version History

The most authoritative way to find out what has changed in between PostgreSQL versions is to read the Release Notes.
A summary of major changes in features is at Feature Matrix.
For general guidelines about the most common issues encountered when upgrading from one PostgreSQL version to a newer one, see Postgres Upgrades - Ten Problems and Solutions and Upgrading old versions of Postgres
= 9.1 Changes =
= 9.0 Changes =
* What's new in PostgreSQL 9.0
= 8.4 Changes =
* PostgreSQL 8.4 Feature List
* Waiting for 8.4
* Performance Enhancements in PostgreSQL 8.4
* PostgreSQL 8.4 Performance Features
= 8.3 Changes =
One of the biggest migration concerns the removal of implicit casts has broken many applications. While ultimately these programs should be rewritten to correct this incompatibility (samples), it's possible to add back in missing casts to ease migration to 8.3. See Re-adding implicit casts in PostgreSQL 8.3 for scripts
Here are a list of articles about new features in 8.3:
* PostgreSQL 8.3 Feature List
* PostgreSQL 8.3 Cheat Sheet Overview
* Synchronized Scans
* PostgreSQL 8.3 Features: Arrays of Compound Types
* PostgreSQL 8.3 Features: Plan Invalidation
* Optimizing function calls in PostgreSQL 8.3
* Converting tsearch2 to 8.3 (also note the manual section Migration from Pre-8.3 Text Search)
* Checkpoints and the Background Writer: PostgreSQL 8.3 Improvements and Migration
* PostgreSQL Performance Features in 8.3
Some of the articles previously available were lost in a server crash; these are currently unavailable but might be restored by their authors eventually:
* work_mem tuning and log_temp_files
* Upgrading to 8.3 - MediaWiki lessons learned (talks about implicit casts and tsearch migration). Can instead try Upgrading MediaWiki from PostgreSQL 8.2 to 8.3 and Upgrading PostgreSQL 8.2 to 8.3 (mediawiki)
* Integrated Security in PostgreSQL 8.3
Category:FAQ
Version History/ja

PostgreSQLのバージョン間でなされた変更を見つけ出す、最も確実な方法はリリースノートを参照することです。
機能に関する主要な変更をまとめたものが機能マトリックスです。
あるバージョンのPostgreSQLからより新しいバージョンにアップグレードする時によく発生する問題についての一般的なガイドラインとしてPostgresのアップグレード - 10個の問題とその解決方法および古いバージョンのPostgreSQLのアップグレードを参照してください。
	
= 9.1 Changes =
= 9.0 Changes =
* What's new in PostgreSQL 9.0
= 8.4 Changes =
* PostgreSQL 8.4 Feature List
* Waiting for 8.4
* Performance Enhancements in PostgreSQL 8.4
* PostgreSQL 8.4 Performance Features
= 8.3 の変更点 =
移行に関してもっとも考慮しなければならないことの1つは、暗黙的なキャストが削除されたことにより、多くのアプリケーションが誤動作することです。
根本的にはこれらのプログラムを修正し、この互換性の問題（サンプル）を解消しなければなりませんが、8.3への移行を簡単にするために無くなったキャストを追加することも可能です。
「キャストを追加してバージョン互換性を保つには」を参照してください。
以下に8.3の新しい機能に関する記事の一覧を示します。
* PostgreSQL 8.3 Feature List
* PostgreSQL 8.3 Cheat Sheet Overview
* Synchronized Scans
* PostgreSQL 8.3 Features: Arrays of Compound Types
* PostgreSQL 8.3 Features: Plan Invalidation
* Optimizing function calls in PostgreSQL 8.3
* Converting tsearch2 to 8.3 (also note the manual section Migration from Pre-8.3 Text Search)
* Checkpoints and the Background Writer: PostgreSQL 8.3 Improvements and Migration
* PostgreSQL Performance Features in 8.3
これまでアクセスできた記事の一部がサーバクラッシュのためなくなってしまいました。
これらは現時点ではアクセスできませんが、いつかは作者により復旧されるかもしれません。
* work_mem tuning and log_temp_filesnd-Up]
* Upgrading to 8.3 - MediaWiki lessons learned (talks about implicit casts and tsearch migration). 代わりにUpgrading MediaWiki from PostgreSQL 8.2 to 8.3 とUpgrading PostgreSQL 8.2 to 8.3 (mediawiki)を参照してください。
* Integrated Security in PostgreSQL 8.3
Category:FAQ
Category:Japanese
VTigerCRM

== vTiger CRM ==
vTiger CRM version 4.2.4 works with Postgres. It can be downloaded 
here
Note that this version is from June 07, 2005 - the vTiger project had a 
sub-project to implement PG support in their 5.0x versions; they project 
having it available in 5.0.3 or 5.0.4. Note that 5.0.3 has been in "RC2" 
status for a very long time now.
vTiger is a fork of SugarCRM with some nice additions; if you don't mind 
using software for which you can't get any form of support (the forums are 
unresponsive for old versions), and you have time to test for your 
environment (load testing, etc), then I highly recommend this product.
Category:Software Ports
Waiting for 8.4

The 8.4 Release Notes briefly mention all of the features in the new version.
== Waiting for 8.4 ==
The "Waiting for 8.4" series from Hubert Lubaczewski covers features added to 8.4 as they are committed. A similar listing to this one is presented in "final post" or you can just go directly to series index:
*final post (?)
*no more -d in pg_dump!
*parallel restoration of dumps
*window functions
*pg_stat_statements
*remove system objects from \dX
*Window Functions - teaser
*Default values for function arguments + integer in any base
*Visibility maps
*TABLE
*auto-explain
*array aggregate and array unpacker
*suppress_redundant_updates_trigger
*Waiting for 8.4 - sql-wrappable RETURNING
*pl/* srf functions in selects
*Common Table Expressions (WITH queries)
*new FSM (Free Space Map)
*database-level lc_collation and lc_ctype
*pgbench with timed execution
*case insensitive text ( citext )
*UNION / INTERSECT / EXCEPT
*ordered data loading in pg_dump
*hash based DISTINCT
*RETURNS TABLE
*variadic functions
*storage types for table columns
*sequence details
*timing on/off
*partial-match support in GIN, and sequence restart
*CASE in pl/PgSQL
*function stats
*pl/PgSQL RAISE
*psql vs. tabs + wrapped output
*pg_conf_load_time, time-related generate_series and enum values in \dT+
*RETURN QUERY EXECUTE and cursor_tuple_fraction
*\o and \d+ in psql
*generate_subscripts
*“EXPLAIN VERBOSE”
*current_query()
*EXECUTE USING in plpgsql
*\l+ and \d in psql
*Statement-level ON TRUNCATE triggers
*quote_nullable()
*Report queries in a deadlock
*enum type for guc parameters
*limit (select ) and optional as in select
==8.4 Talks and Discussion==
* Intro to Postgres 8.4 Tutorial by Robert Treat
* What's new in PostgreSQL 8.4 by Magnus Hagander
* What's your favorite 8.4 feature?
* PostgreSQL 8.4 Beta: "We've got momentum"
* David Fetter talks: "Trees and More in SQL" and "Windowing Functions: Putting the TPS in TPS Reports" cover 8.4 features
* Upcoming PostgreSQL Features by Bruce Momjian
Category: PostgreSQL 8.4
WikiEditing

Editing this wiki now requires "editor" privileges
There are two ways to request editor access:
* Email the PostgreSQL -www Mailinglist.
 Example: "I would like editor access to the wiki, my username is ... and I would like to modify ..."
* Post a message on the PostgreSQL IRC Channel (mention 'pginfra' in your request).
 Example: "pginfra: I would like editor access to the wiki, my username is .... and I would like to modify ...."
Include your community account name and what you would like to change in your request.
This is due, in large part, to recent spam activity.
WIP alternative Main Page

__NOTOC__
= Welcome to the PostgreSQL Wiki! =
This wiki is designed to provide a comprehensive resource for end-users of PostgreSQL and the PostgreSQL community, and to discover the PostgreSQL ecosystem.
== End-users ==
If you're a user of PostgreSQL, you probably want to explore the following category: :Category:End-Users
Do not forget the official PostgreSQL documentation and additional learning resources:
* PostgreSQL Manuals
* PostgreSQL Books
* PostgreSQL Tutorials
== Community ==
For those interested in contributing to PostgreSQL or staying updated on the community:
You can connect with PostgreSQL users and developers on #postgresql on Libera. Check out the list of IRC nicknames with real-world names here.
There is a root category to explore: :Category:Community
== PostgreSQL ecosystem ==
For discovering PostgreSQL related software, tools, extensions, ...
There is a root category to explore: :Category:Ecosystem
== Contributing and Maintaining the Wiki ==
We encourage users like you to actively contribute to this PostgreSQL Wiki, making it a valuable resource for the community. Your contributions can range from adding new content to improving existing articles, ensuring that users can find the information they need easily.
There is a root category to explore: :Category:PgWiki
== Wiki in other languages ==
If you're more comfortable with languages other than English, you can find and edit relevant content here:
* deutsch
* español 
* français
* עברית
* 日本語
* português
* русский
* 中文
* 中文（繁體
WIP:plpython3

Feature-wise, this is done--actually, I plan to remove some features.
If you are interested in seeing this in PostgreSQL, please drop an e-mail as I would like to know( x <at sign> jwp.name ).
== Info ==
pl/python3 is a procedural language implementation for Python 3.x
This WIP is targeting a patch for 8.5.
The git branch is located at http://git.postgresql.org/gitweb?p=plpython3.git;a=summary
== Progress ==
* Module based functions
* Function PEP302 support (__loader__, for linecache support)
* Tracebacks in CONTEXT
* IST support (subtransactions)
* Database Error support; Postgres Error -> Python Exception -> Postgres Error
* SRFs (VPC and Materialized & Postgres.Cursor optimization)
* Triggers (statement & row)
* Type Interfaces (native typing)
* Polymorphic types.
* SPI/Prepared Statements and Cursors
* Postgres.execute(sql_statements_string) (SPI_execute_statements() for bulk, parameter-less DDL/DML)
* Large Object class (io.IOBase; some protocol conformance not possible, atm)
* @pytypes decorator for converting Postgres.Object parameters to standard Python types
* Properties to get parts of time types (ts.day, ddate.month, intv.second, etc)
* Direct Function Calls (limited to non-polymorphic, non-SRF, and non-TRIGGER)
* Stateful functions (Regular functions whose main is a generator =)
* DO-statement support.
* Postgres.preload(...) (Load all the Python functions in the specified schemas)
* Tests [basic I/O, ISTs, Errors, SRFs, Triggers, Domains, Enums, SPI, Polymorphing]
* Documentation with a substantial reference section
== Quick Start ==
Basic installation using git.
=== Getting PostgreSQL with pl/python3 ===
Use git, fresh clone of pg(if you don't already have one):
 git clone git://git.postgresql.org/git/postgresql.git
 cd postgresql
Fetch and checkout the plpython3 branch:
 git remote add plpython3 git://git.postgresql.org/git/plpython3.git
 git fetch plpython3
 git branch plpython3 plpython3/plpython3
 git checkout plpython3
=== Installing PostgreSQL with pl/python3 ===
Like pl/python, pl/python3 can be configured and installed using a configure argument: --with-python3:
 $ ./configure --with-python3
However, for testing, the following is recommended:
 $ env "CFLAGS=-O0 -ggdb3" ./configure --enable-depend --enable-debug --enable-cassert --with-python3
 # Don't forget to add an appropriate --prefix=
This relies on the python3 executable being accessible in the PATH. (Note: Python 3.0 is not supported, ATM. Use Python 3.1)
Once installed, the language can be created in a database using:
 CREATE LANGUAGE plpython3u;
This uses the pg_pltemplate entry like other PLs.
=== Documentation ===
Temporarily available here:
http://python.projects.postgresql.org/pldocs/plpython3.html
Category:PL/Python
With Returning

=== WITH [RECURSIVE] RETURNING ===
* TODO for David Fetter
** Write up history 
** Create presentations 
** Doc changes
** Regression tests
** Test for breakage
* TODO for Dan Colish
** Get some more bounty queries
** merge refactoring of executor into writable_cte branch
* TODO for Marko Tiikkaja
** Make ExecutePlan() a bit prettier ( http://archives.postgresql.org/pgsql-hackers/2009-11/msg01860.php )
** Change the grammar to allow WITH before INSERT, UPDATE and DELETE 
** Fix DO ALSO SELECT .. rule logic 
** Implement execute-once property for CTEs with INSERT/UPDATE/DELETE 
** Allow non-referenced CTEs have INSERT/UPDATE/DELETE without RETURNING 
** Fix trigger behaviour inside queries which have SELECT as top-level query 
** Rethink how result relations should be handled 
Category:WITH Queries