nyuuzyou/wikis huggingface dataset:
Reverse string

The reverse(text) function returns the input string with all characters in reverse order.
This is mainly useful for suffix pattern matching via indexes. For example:
CREATE TABLE words (word text);
CREATE INDEX ON words (word text_pattern_ops);
CREATE INDEX ON words (reverse(word) text_pattern_ops);
And then write queries like this:
SELECT * FROM words WHERE word LIKE 'ag%ng' AND reverse(word) LIKE reverse('ag%ng');
PostgreSQL will automatically pick one, or both, of the indexes for execution; for example:
 Bitmap Heap Scan on words
 Filter: ((word ~~ 'ag%ng'::text) AND (reverse(word) ~~ 'gn%ga'::text))
 -> BitmapAnd
 -> Bitmap Index Scan on words_word_idx
 Index Cond: ((word ~>=~ 'ag'::text) AND (word ~<~ 'ah'::text))
 -> Bitmap Index Scan on words_reverse_idx
 Index Cond: ((reverse(word) ~>=~ 'gn'::text) AND (reverse(word) ~<~ 'go'::text))
==PostgreSQL 9.1+==
PostgreSQL 9.1 and later already include a built-in version of the reverse(text) function (documented here)
==Slower, portable SQL version==
This is a simple and portable SQL-language implementation with no external dependencies. Compatible with PostgreSQL 8.2+.
CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
SELECT array_to_string(ARRAY(
 SELECT substring($1, s.i,1) FROM generate_series(length($1), 1, -1) AS s(i)
 ), );
$$ LANGUAGE SQL IMMUTABLE STRICT;
Source: http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#LIKE_optimalization
==Portable PL/pgSQL version==
This is a portable PL/pgSQL implementation with no external dependencies, faster than the above. Compatible with PostgreSQL 8.2+. It's a slightly tweaked version of a function that's been reposted many times on the mailing list, from 2005.
CREATE FUNCTION reverse(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
 result text = ;
 i int;
BEGIN
 FOR i IN 1..length(input) BY 2 LOOP
 result = substr(input,i+1,1) || substr(input,i,1) || result;
 END LOOP;
 RETURN result;
END$$;
==Faster, PL/Perl version==
This function is much faster than the above for longer inputs (10 and more characters), but incurs Perl interpreter overhead for each session calling it.
CREATE FUNCTION reverse(text) RETURNS text
LANGUAGE plperl IMMUTABLE STRICT AS
'return reverse $_[0]';
Source: http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#LIKE_optimalization
==Fastest, C version: orafce extension==
A faster version written in C is avaiblable in PGXN, in the orafce extension (Oracle compatibility functions): http://pgxn.org/dist/orafce/
Original Pgfoundry project is archived.
Category:PL/pgSQL
Reviewing a Patch/ja

PostgreSQL に対して投稿されたパッチに対して、多くの人々が、自身には完全なレビューをするだけの力が無いと感じています。しかし、もしあなたがまだコミッタで無いならば、それは技術的には当然なのです。プロジェクト全体もかなり大きくなっており、全貌を把握するのは難しくなっています。しかし、レビューにはたくさんの異なった仕事があり、もしあなたがそのすべてをできなかったにしｔめお、早期段階の仕事に貢献することは十分に可能です。
もしあなたがパッチを適用し、新しい機能を試したのならば、それはすでにレビューをし始めていることになります。
最終的なコミッタは、コードベースに取り込む際に彼ら独自のレビューを行うでしょう。レビューアの仕事は、前もって問題を取り除くことで、コミッタの仕事を減らすことです。レビューアには完璧を求められることは無く、見つけられるだけの問題を見つけることが仕事になります。もしあなたが、そのパッチがコミッタの詳細レビューに足る品質になったと判断したら、そこでレビューは完了します。レビュー結果の報告の形式は、このパッチレビューを参考にしてください。もちろん、パッチの目的が異なれば、レビュー結果はかなり異なるものになるかもしれません。
もしあなたがこれから説明する仕事に取り組んでくれるのならば、とても助かります。どのような人材が求められているかは、に、冗談めかしながらも、それほど不正確でない説明があります。
現在のコミットフェスタは ここで、あなたに手伝ってもらいたいことが多くあります。ラウンド・ロビン・レビューアへの参加は ここからできます。参加したならば、まずは自己紹介をして欲しいです。
一般的には、パッチをレビューしていく段階は、以下の順になります：
== 提案レビュー (必要スキル: パッチを交えた英語での議論) ==
* パッチは コンテキストDIFF 形式か？
* 現在の git master にエラー無く適用できるか？
* テストやドキュメントを含んでいるか？
== 仕様レビュー (必要スキル: 仕様を読む) == 
そのパッチが何を実現しようとしているかを読み取り、以下を考察します：
* そのパッチは実際に何をしようとしているのか？
* そのパッチは本当に必要か？
* 既存の機能との重複は無いか？
* 標準SQLに従っているか？ またはコミュニティの同意を得た動作か？
* pg_dump のサポートがあるか？
* 適用することで、何か危険性は無いか？
* 基本がなっているか？ (原文: Have all the bases been covered?)
== 機能テスト (必要スキル: パッチの適用, コンパイル, エラーログの確認) ==
パッチを適用し、コンパイルして、以下をテストします：
* 公言通りの動作をするか？
* パッチの作者が見逃している重箱の隅は無いか？
* アサーションの失敗や、クラッシュは無いか？
** レビューアは、configure する際に --enable-cassert と --enable-debug オプションを有効にすべきです。方法は Working with CVS に載っています。これらを使うと、ミスを発見しやすくなります。しかし、これらのオプションを使うと PostgreSQL の性能が劣化します。もし、パッチが他の用途の性能を劣化させていないかなどの性能試験を行うならば、これらのオプションを off にすべきです。また、すべてではないものの、--enable-cassert の性能ペナルティのうちのいくらかは、postgresql.conf に debug_assertions = false を指定することで低減できます。詳細は Developer Options を参照してください。このオプションは --enable-cassert 付きでビルドするとデフォルトで有効 (true) になります。また、--enable-debug オプションは、パフォーマンスには影響が無いはずです。少なくとも gcc では確認されており、他のコンパイラでも同様である可能性が高いと考えられます。
== 性能試験 (必要スキル: 性能測定) ==
* そのパッチが他のシンプルなテストの性能を劣化させないか？
* 性能向上のt前のパッチであるならば、本当に性能は向上しているか？
* そのパッチの影響で、他の機能の性能が劣化することはないか？
== コーディング・レビュー (必要スキル: コーディング規約の確認, 移植性検討, 少々のCを読む技術) ==
そのコードが行った変更を詳細に読み解き、以下のことを確認します：
* このプロジェクトの コーディング規約 に従っているか？
* (他のプラットホームへの) 移植性に問題は無いか？
* (Linux だけでなく) Windows や BSD でも動作するか？
* コード中のコメントが十分かつ正確か？
* コードの説明とその動作で、言行一致しているか？
* コンパイルする際に警告を出力しないか？
* クラッシュにつながる操作を許していないか？
== アーキテクチャ・レビュー (必要スキル: PostgreSQL全体のアーキテクチャの理解) ==
そのコードが行った変更を、PostgreSQL全体の構成と照らし合わせて、以下を確認します：
* 他の機能やモジュールと照らし合わせても、一貫性があるか？
* 将来問題になり得る相互依存関係が無いか？
== レビューのレビュー ==
* そのレビューアは本来やるべきことをすべて行ったか？
Category:Japanese
Ride Board PGDAY SJ 2009

If you need a ride or can provide one, post here! Please include some contact info.
===Drivers:===
Driver 	Leaving From 	Riders 	Comments
Steve Crawford 	El Cerrito 	Miho 	Alternately, can swap passengers w/SF driver and get riders on East side. Need to arrive at/before 9am with audio eqpt.
Quinn Weaver 	South Berkeley	Brian 	Going down east side. Can take two more. Address quinn, domain pgexperts.com.
Dave Tittle 	Sacramento 	 	No radio. Must converse.
John Fabiani 	Woodland 	 	Room for one.
Steve Buttgereit	San Francisco 	Joe Conway	If you can meet me at Balboa Park BART/Muni station at 7:30AM or SFO around 7:54AM, I do have room for 2 others. scb at the domain musesystems com. I'll be in SJ though the Party.
Igor Polishchuk 	San Francisco 	Yehuda 	I can be at Glen Park BART at 8:30AM. I have room for 3. If somebody wants to go with me, send me a note at i_polishchuk@yahoo.com
===Riders:===
Rider 	Leaving From 	Has ride	Comments
Joe Conway	SFO 	yes 	Flight arrives 7:54
Miho 	San Francisco 	yes 	
Yehuda 	San Francisco (Mission)	yes 	
Back to: PgDaySanJose2009
Category:PostgreSQL Events
RLS Options

Option #1:
row_security	BypassRLS	ForceRLS	Owner	Result
on 	true 	true 	true 	Apply
on 	true 	true 	false	Apply
on 	true 	false 	true 	Don't Apply
on 	true 	false 	false	Apply
on 	false 	true 	true 	Apply
on 	false 	true 	false	Apply
on 	false 	false 	true 	Don't Apply
on 	false 	false 	false	Apply
row_security	BypassRLS	ForceRLS	Owner	Result
off 	true 	true 	true 	Don't Apply
off 	true 	true 	false	Don't Apply
off 	true 	false 	true 	Don't Apply
off 	true 	false 	false	Don't Apply
off 	false 	true 	true 	Don't Apply
off 	false 	true 	false	Error
off 	false 	false 	true 	Don't Apply
off 	false 	false 	false	Error
Option #2:
row_security	BypassRLS	ForceRLS	Owner	Result
on 	true 	true 	true 	Don't Apply
on 	true 	true 	false	Don't Apply
on 	true 	false 	true 	Don't Apply
on 	true 	false 	false	Don't Apply
on 	false 	true 	true 	Apply
on 	false 	true 	false	Apply
on 	false 	false 	true 	Don't Apply
on 	false 	false 	false	Apply
row_security	BypassRLS	ForceRLS	Owner	Result
off 	true 	true 	true 	Don't Apply
off 	true 	true 	false	Don't Apply
off 	true 	false 	true 	Don't Apply
off 	true 	false 	false	Don't Apply
off 	false 	true 	true 	Don't Apply
off 	false 	true 	false	Error
off 	false 	false 	true 	Don't Apply
off 	false 	false 	false	Error
Robert Haas' Development Projects

This is a list of projects that I'm interested in working on. It isn't guaranteed to be up-to-date or complete, and there's no particular timeline for anything listed here. Feel free to email me regarding my current plans (SELECT 'robert' || 'm' || 'haas' || '@gm' || 'ail' || '.com').
* foreign key inference (includes inner join removal)
* Global temporary tables
* model caching effects in the planner
* WITH foo AS (EXPLAIN ...) returning text
* Logical replication
* Background writer improvements
* JSON
* Time-delayed replication
* Concurrent DDL/unauthorized lock waits
* CLOG contention
* ProcArrayLock contention
* BufFreelistLock contention
* BufMappingLock contention
See also rhaas.blogspot.com.
Category:Development
Round time

Round a timestamp to the nearest 5 minute mark.
CREATE OR REPLACE FUNCTION round_time(timestamp with time zone) 
RETURNS timestamp with time zone AS $$ 
 SELECT date_trunc('hour', $1) + interval '5 min' * round(date_part('minute', $1) / 5.0) 
$$ LANGUAGE sql;
 
sample usage:
postgres=# select now(), round_time('2010-09-17 16:48');
 now | round_time 
-------------------------------+------------------------
 2010-09-19 08:36:31.701919+02 | 2010-09-17 16:50:00+02
(1 row)
postgres=# select now(), round_time('2010-09-17 16:58');
 now | round_time 
-------------------------------+------------------------
 2010-09-19 08:36:43.860858+02 | 2010-09-17 17:00:00+02
(1 row)
postgres=# select now(), round_time('2010-09-17 16:57');
 now | round_time 
-------------------------------+------------------------
 2010-09-19 08:36:53.273612+02 | 2010-09-17 16:55:00+02
(1 row)
postgres=# select now(), round_time('2010-09-17 23:58');
 now | round_time 
------------------------------+------------------------
 2010-09-19 08:37:09.41387+02 | 2010-09-18 00:00:00+02
(1 row)
Alternative approach:
CREATE FUNCTION date_round(base_date timestamptz, round_interval interval) RETURNS timestamptz AS $BODY$
SELECT to_timestamp((EXTRACT(epoch FROM $1)::integer + EXTRACT(epoch FROM $2)::integer / 2)
 / EXTRACT(epoch FROM $2)::integer * EXTRACT(epoch FROM $2)::integer)
$BODY$ LANGUAGE SQL STABLE;
This will also work across one hour boundaries, e.g. round to nearest hour and a half. Sample usage:
SELECT date_round(now(), '15 minutes');
RPM Packaging

=== How are RPMs packaged? ===
This was written by Lamar Owen and Devrim Gündüz, 2006-10-16, rewritten 2014-09 by Craig Ringer.
A git repository is maintained at http://git.postgresql.org/gitweb/?p=pgrpms.git, which for each package contains:
# A set of patches to make certain portions of the sources conform more closely with how Red Hat packages its own matching RPMs, better integrate into multiarch installs, etc;
# The initscript / systemd service file;
# Any other ancillary scripts and files;
# A README.rpm-dist document that tries to adequately document both the differences between the RPM build and the WHY of the differences, as well as useful RPM environment operations (like, using syslog, upgrading, getting postmaster to start at OS boot, etc);
# The spec file that throws it all together, specifying what files are in what subpackages, the version, dependencies, and a lot more.
A given "package" like PostgreSQL will have many copies of the above, one for each distro release and PostgreSQL version. For example, the package for PostgreSQL 9.4 on Fedora 20 is in 9.4/postgresql/F-20
The PGDG RPM maintainer builds the SRPM, then executes RPM builds on a variety of build worker machines and pushes the resulting RPMs and SRPMs to http://yum.postgresql.org/srpms/$version/$distro/$distro-$release-$arch/ and http://yum.postgresql.org/$version/$distro/$distro-$release-$arch/ , e.g. http://yum.postgresql.org/srpms/9.4/fedora/fedora-20-x86_64/ and http://yum.postgresql.org/9.4/fedora/fedora-20-x86_64/ .
The Yum repository metadata is then updated so that yum can find the new packages.
Category:Linux distribution
Rubyrep

== NOTE ==
Project seems stalled. There does not seem to have been any updates in 2 years.
== Project Overview ==
rubyrep provides 
* asynchronous
* master-master replication 
for PostgreSQL and MySQL.
Focus is on easy installation and use.
== Project Status ==
rubyrep is in production.
== Project Contacts ==
* Developer: Arndt Lehmann
* Project website: www.rubyrep.org
* Mailing list: link
* Source code: GitHub
Category:Replication
Running pgindent on non-core code or development code

Note: Copied from http://adpgtech.blogspot.com/2015/05/running-pgindent-on-non-core-code-or.html
Running pgindent is not nearly as hard as some people seem to think it is. The hardest part of getting a workable set of typedefs to use. That's why the buildfarm now constructs these lists automatically for each live branch.
But that doesn't help if you're working on non-core code. Here's what I did to get a working typedefs list for the Redis FDW code:
 objdump -W redis_fdw.so |\
 egrep -A3 DW_TAG_typedef |\
 perl -e ' while (<>) { chomp; @flds = split;next unless (1 < @flds);\
 next if $flds[0] ne "DW_AT_name" && $flds[1] ne "DW_AT_name";\
 next if $flds[-1] =~ /^DW_FORM_str/;\
 print $flds[-1],"\n"; }' |\
 sort | uniq > redis_fdw.typedefs
This is a slight adaptation of what the buildfarm code does on Linux to get a typedefs list.
After that, indenting the code was a matter of just doing this:
 pgindent --typedefs=redis_fdw.typedefs redis_fdw.c
What if you're developing a piece of core code and you'd like to run pgindent on it, but you've introduced some new typedefs, so pgindent mucks up the indentation by adding extraneous spaces. You have a couple of options. Let's assume that what you're working on is backend code. Then you could run the above extraction on the built backend - it doesn't have to be installed, just run it against src/backend/postgres. Then use that to run pgindent against each of the files you're working on. You don't have to run it separately for each file - you can name as many files to indent as you like on the command line.
If you do that, look at the results carefully. It's possible that the absence of some platform-dependent typedef has mucked up your file. So a safer procedure is to grab the latest typedefs list from the buildfarm server and combine it with the typedefs list you just constructed, like this:
 wget -q -O - "http://www.pgbuildfarm.org/cgi-bin/typedefs.pl?branch=HEAD" |\
 cat - mytypedefs | sort | uniq > mynewtypedefs
and then use that file to pgindent your code.
None of this is as easy as it might be. But none of it is very hard either.
Hint:
If you only have a handful of new typedefs, you can pass them on the command line to pgindent, like this:
 pgindent --typedefs=mytypedefs --list-of-typedefs="typedef1 typedef2" myfile1.c myfile2.c
Russian/Policies

Здесь находятся рекомендации и утверждённая политика проекта PostgreSQL относительно веб-сайта, инфраструктуры, списка рассылок, и прочего.
* Утверждённые политики
** Утверждённая политика для "Новости и События"
** Утверждённая политика для "Профессиональные услуги и хостинг"
** Политика Planet PostgreSQL
* Cоставление перечня участников
* PostgreSQL.Org Политика стендов для выставок
* Подготовка релиза
* On the Project Name
* Fundraising Group
* Being a Sponsor
* Политика архивов
* Политика торговой марки
* PostgreSQL Release Support Policy
Category:Russian
Salva

1-Srcipt de salva programada en Perl
 $PGSQL_HOME/bin/psql -d template1 -U postgres -e -f
 $BACKUP_SQL/begin_backup.sql >> $BACKUP_LOGS_DIR/$backup_log_filename
 echo "Copying data dirs now..." >> $BACKUP_LOGS_DIR/$backup_log_filename
 echo " " >> $BACKUP_LOGS_DIR/$backup_log_filename
 ## copy data directory into backup location including postgres.conf and any soft
 links used for tablespaces.
 cp -r -P $PGDATA $BACKUP_HOME/a01_data.${TIMESTAMP}
 if [ $? -eq 0 ] then
 echo "data dir copy of a01 is successful." >> $BACKUP_LOGS_DIR/$backup_log_filename
 echo " " >> $BACKUP_LOGS_DIR/$backup_log_filename
 else
 echo "data dir copy of a01 failed." >> $BACKUP_LOGS_DIR/$backup_log_filename
 echo " " >> $BACKUP_LOGS_DIR/$backup_log_filename
 fi
 ## get the database out of backup mode
 $PGSQL_HOME/bin/psql -d template1 -U postgres -e -f
 $BACKUP_SQL/end_backup.sql >> $BACKUP_LOGS_DIR/$backup_log_filename
 ## put the database in backup mode
2-Backup_diario.sh
 #! /bin/sh 
 #############################################
 #Nombre: backup_diario.sh Fecha: 18/04/2007 
 # Script para realizar Backup de las Bases de datos existentes
 # Ademas se controla la cantidad de copias que se desea guardar
 # Luego de esto envia por SSH a otro servidor el respaldo,
 # evitando asi cualquier tipo de riesgo por daño en el soporte
 # físico (HDD)
 #############################################
 BACKUP_DIR=/home/postgres/backup
 BACKUP_DIR_REMOTO=192.168.7.1:/home/backup/
 BACKUP_NUM=7 
 # Realizar Backup de las DB'S
 databases=`su -l postgres -c 'psql -q -t -c "select datname from pg_database;" template1'`
 for d in $databases; do
 if [ ! -d $BACKUP_DIR/$d ]; 
 then echo -n "Creando directorio de respaldo $BACKUP_DIR/$d... "
 su -l postgres -c "mkdir $BACKUP_DIR/$d" ] || continue
 echo "done."
 fi
 # Establecer cantidad maxima del mismo backup $BACKUP_NUM
 archive=$BACKUP_DIR/$d/$d.gz 
 if [ -f $archive.$BACKUP_NUM ]; then 
 rm -f $archive.$BACKUP_NUM; 
 fi 
 n=$(( $BACKUP_NUM - 1 )) 
 while [ $n -gt 0 ]; do
 if [ -f $archive.$n ]; then 
 mv $archive.$n $archive.$(( $n + 1 )) 
 fi
 n=$(( $n - 1 )) 
 done
 if [ -f $archive ]; then
 mv $archive $archive.1;
 fi 
 echo -n "Respaldando la base $d... " 
 su -l postgres -c "(pg_dump $d |gzip -9) > $archive" 
 echo "Transfiriendo archivo $archive" 
 scp $archive root@$BACKUP_DIR_REMOTO 
 echo "Tarea Finalizada." 
 done
Category:Snippets
SCALE14x

We had a two-day, two-track Postgres event at SoCal Linux Expo (SCALE) in January, 2016.
Conference website: SCALE14x
Events shown for placeholding purposes. See the "Editing help" for instructions on how to add a link to your slides.
== Thursday, Jan 21 ==
* PostgreSQL Adores a Vacuum (Quinn Weaver)
* Don't Forget the Elephant (Keith Fiske)
* A Journey Down the Amazon: RDS Postgres (Gabrielle Roth)
* Introduction to Greenplum (Dave Cramer)
* MVCC Unmasked (Bruce Momjian)
* Backups: The Good, the Bad and the Ugly (Joshua Drake)
* SERIALIZABLE, Eventual Consistency and Really Scary Stuff (Simon Riggs)
* Debugging Your PL/pgSQL Code (Jim Mlodgenski)
* JSON Home Improvement (Christophe Pettus)
* MLS PostgreSQL (Joe Conway)
* LOCKED UP: ADVANCES IN POSTGRES DATA ENCRYPTION (Vibhor Kumar)
* Linux tuning to improve PostgreSQL performance (Ilya Kosmodemiansky)
== Friday, Jan 22 ==
* UPSERT use-cases (Peter Geoghegan)
* Techniques for Managing Postgresql with Containers (Jeff McCormick, Steven Pousty)
* ToroDB: open-source MongoDB-compatible database with SQL superpowers (Alvaro Hernandez)
* PostgreSQL (System) Administration (Stephen Frost)
* PostgreSQL packaging in Debian (Michael Meskes)
* EXPLAIN Explained (Josh Berkus)
* PostgreSQL Performance Presentation, 9.6devel Edition (Mark Wong)
* All the Big Data Sciency stuff you never knew Postgres could do (Jim Nasby)
SCALE15x

We had a two-day, two-track Postgres event at SoCal Linux Expo (SCaLE) March 2 & 3, 2017.
Conference website: SCaLE15x
Events shown for placeholding purposes. See the "Editing help" for instructions on how to add a link to your slides.
== Thursday, Mar 2 ==
* Managing OS Provided PostgreSQL Packages Keith Fiske
* PostgreSQL + ZFS: Best Practices and Standard Procedures Sean Chittenden
* Linux IO internals for database administrators, Ilya Kosmodemiansky
* Think Your Postgres Backups and Disaster Recovery Are Safe? Let's Talk. Payal Singh
* Securing PostgreSQL Christophe Pettus
* [PostgreSQL snap packages, why!?] Joshua Drake
* [Logical Replication in PostgreSQL] Mark Wong
* Postgresql as an Application Modernization and Consolidation Cornerstone Charles Finley
* Amazon RDS PostgreSQL: Enabling Innovation with Cloud Managed Databases Grant McAlister
* JDBC Performance Guru Tips and Tricks Dave Cramer
* Gophers Riding Elephants AJ Bahnken
* PostgreSQL Hooks for Fun and Profit David Fetter
== Friday, Mar 3 ==
* A 30 Year History of the Postgres Project Peter vanHardenberg
* Intro to PostgreSQL Joe Conway
* [Becoming A SQL Guru] Stella Nisenbaum
* What's new in PostgreSQL 9.6 Magnus Hagander
* How to safely use WITH in Postgres Jim Nasby
* Strategic Autovacuum Scott Mead
* CCP: Containerized Clustered Postgres Josh Berkus
* Inside PostgreSQL Shared Memory Bruce Momjian
* Creating a Postgres disaster recovery automation plan Nina Kaufman
* Jaywalking in Traffic: Safe Migrations at Scale Brad Urani
* Minimizing Major Version Upgrade Downtime Utilizing Slony scripts Jeff Frost
* [Postgres at any Scale] Will Leinweber
SCALE16x

We had a two-day, two-track Postgres event at SoCal Linux Expo (SCaLE) March 8 & 9, 2018.
Conference website: SCaLE16x
Events shown for placeholding purposes. See the "Editing help" for instructions on how to add a link to your slides.
== Thursday, Mar 8 ==
* PostgreSQL Administration Training - Keith Fiske
* Postgres GUCS: A Three-Hour Tour - Josh Berkus
* Exploring Common Table Expressions and Window Functions - Bruce Momjian
== Friday, Mar 9 ==
* PostgreSQL Partitioning - Robert Treat
* PostgreSQL Operator - Sarah Conway
* Monitoring PostgreSQL 101 - Jason Yee
* Advanced SQL - Stephen Frost
* Tuning PostgreSQL for High Write Workloads - Grant McAlister
* PostgreSQL and REST API's The Easy Way Supplement - Charles Finley
* Hidden Talents of the PostgreSQL JDBC Driver - Dave Cramer
* The Many Faces of PostgreSQL Replication - Phil Vacca
* Improving Postgres' Efficiency using JIT and other techniques - Andres Freund
* Securing Your Data on PostgreSQL - Payal Singh
* Automated Failover With PostgreSQL - Scott Mead
* TimescaleDB: Re-engineering PostgreSQL as a time-series database - David Kohn
Schema Size

Author: Emanuel Calvo
=== Schema Size ===
This function provides a simple way to get the size of one schema. Note that this script returns 0 if the specified schema does not exist.
CREATE OR REPLACE FUNCTION pg_schema_size(text) returns bigint AS $$
select sum(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint from pg_tables where schemaname = $1
$$ language sql;
One way to execute:
general_base=# select pg_size_pretty(pg_schema_size('public'));
-[ RECORD 1 ]--+--------
pg_size_pretty | 4782 MB
Here is a modified version of the script that allows you to supply a case-sensitive regular expression to only consider a subset of table names within the schema:
CREATE OR REPLACE FUNCTION pg_schema_size_filter(text, text) returns bigint AS $$
select sum(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint from pg_tables where schemaname = $1 and tablename ~ $2
$$ language sql;
=== Database Size ===
Sometimes we need to know fast the size of our database, if you are lazy, just compile this function in every database that you have and execute it!
CREATE OR REPLACE FUNCTION sizedb() RETURNS text AS $$
SELECT pg_size_pretty(pg_database_size(current_database()));
$$ LANGUAGE sql;
SchemaSpy

== About ==
SchemaSpyis a java tool that with the aid of Graphviz can generate a rough documentation for existing database
as a collection of interlinked HTML pages.
== Huge tables support ==
As of 2012-05-18, the latest jar file with SchemaSpy 5.0.0 incorrectly handles large number of rows, i.e. if it exceeds 2^31-1 . Therefore it is necessary to compile it from the source code. It is straightforward using Eclipse. Compiled version can be exported into a jar.
Default property file for PostgreSQL does not make use of efficient row estimation. Here is the snippet let's call it mypg.properties that can be used in place of default property file.
#
# see http://schemaspy.sourceforge.net/dbtypes.html
# for configuration / customization details
#
description=PostgreSQL
connectionSpec=jdbc:postgresql://<host>/<db>
host=some host
db=some db
driver=org.postgresql.Driver
# Sample path to the postgresql drivers.
# Use -dp to override.
driverPath=./postgresql-9.1-902.jdbc4.jar
selectRowCountSql=select reltuples as row_count from pg_class where relname=:table
Note that in spite of selectRowCountSql, last binary will fail in case of huge tables.
The tool can be called like
java -jar schemaSpy.jar -t ./mypg -db mydb -u myuser -host myhost -o output_folder -p mypass -schemas "public,something,else" -noviews
== Shortcomings ==
Does not document table inheritance.
Category:Tool