My PostgreSQL Page

• Nice picture from PostgreSQL conference (Toronto, 2006).
• PGCon2007 conference in Ottawa, 2007

Tue Apr 17 2007

We present new FTS in PostgreSQL at RIT-2007 conference. Presentation can be found on PostgreSQL Talks page (in Russian).

Fri Apr 7 2006

We released first prototype of Gin - Generalized Inverted Index.

Fro Apr 7 2006

We wrote a paper "What's new in PostgreSQL 8.1 ?" (in Russian).

Tue Aug 2 2005

We wrote a paper "GiST programming tutorial" (in Russian). English version will be available. This is the only and first GiST tutorial with introduction to GiST and annotated examples.

Tue Apr 5 2005

I wrote a paper "What is a PostgreSQL ?" (in Russian) for Corporative DBMS-2005 conference (PDF version). Also, flyer is available in russian.

Wed Jan 19 00:44:36 MSK 2005

Long awaited PostgreSQL version 8 is out ! Check master site or russian site for press release pages.
I took part in russian translation of .po files and press pages.

Wed Jan 21 19:29:50 MSK 2004

• www.pgsql.ru - search PostgreSQL related resources !
• Generic Crawler (Search-OpenFTS-Crawler-0.4.tar.gz) for OpenFTS has uploaded. Find it here.

Fri Jun 13 21:53:22 MSD 2003
Short mini-howto UTF8 and KOI8

May 31,2001
New version of contrib-intarray for postgresql v.7.1 and above, patch for multi-key GiST index and full implementation of R-Tree using GiST are available from GiST page.

May 26,2001Patch for postgresql 7.1.2 with multi-key GiST index support is available from GiST page.

Apr 27, 2001
Search postgresql mailing list archive This interface utilizes power of PostgreSQL and FTS - full text search engine fully based on postgres. Currently archive contains more than 100K messages

Apr 19, 2001
PostgreSQL 7.1 is out ! We are working on Web interface to postgresql mailing list archive we plan to setup at www.postgresql.org instead of very slow UdmSearch.

January 11, 2001
GiST page with patches for 7.0.3, 7.1 and short description.

November 15, 2000
Bugfix release 7.0.3 is available.
Porting PostgreSQL to Windows 2000 installation procedures
Porting PostgreSQL to NT

November 30, 1999
Russian translation of several PostgreSQL doc available ! Check it out:
http://spslug.sposad.ru/doc/postgres/tutorial/
http://spslug.sposad.ru/doc/postgres/admin/

August 9, 1999
Some explanation of MVCC by author Vadim Mikheev (in Russian)
and my implementation of accumulated hits statistics uisng MVCC - not available yet :-(

Auguts 6, 1999
pg-bench.pl - simple perl script for Postgres benchmarking.
My results: Dual PPRO 200, 256Mb, Linux 2.0.36 SMP, 6.5.1, slightly loaded. Use it for fun, it's not prefect but you get some numbers.

Testing empty loop speed ...
100000 iterations in 0.5 cpu+sys seconds (204081 per sec)

Testing connect/disconnect speed ...
2000 connections in 14.7 cpu+sys seconds (136 per sec)

Testing CREATE/DROP TABLE speed ...
500 files in 1.8 cpu+sys seconds (276 per sec)

Testing INSERT speed ...
500 rows in 0.9 cpu+sys seconds (561 per sec)

Testing UPDATE speed ...
500 rows in 0.8 cpu+sys seconds (602 per sec)

Testing SELECT speed ...
100 single rows in 0.2 cpu+sys seconds (666.7 per sec)

Testing SELECT speed (multiple rows) ...
100 times 100 rows in 0.7 cpu+sys seconds (138.9 per sec)

Crash Postgres script mkjoindata.pl. YOu need Perl to run it. Script generates all data and queries for big multi-tables join. I use it for testing. Typical usage:
mkjoindata.pl --joins 7 --rows 200 | psql test
My motivation to write this script was to test postgres's capability to serve multi-tables joins. It's known now 6.4.2 has a bug and thank to Tom Lane 6.5 works good enough to do 20 tables (even 60 tables) join on my P166, 64 Mb RAM. This is one reason (among billion others :-) you should go to 6.5 release which is coming really soon.

In coming soon 6.5 we have --with-mb=KOI8 option to configure ! It does online translation from server's to client's encoding on fly ! You can specify client's encoding dynamically using set CLIENT_ENCODING TO 'WIN'|'KOi8'|'ALT'
I have locale-test.koi8.tgz to test your KOI8-R locale (collate).

Old News:
Oleg Broytman has made new patch for locale support ! Now PostgreSQL has locale support for all text types ! Many people were confused when triyng locale support for text types other than varchar.
Download patch for 6.3.2 locale-patch.tar.gz.

My stuff: Locale support

Doing project for some company in Moscow, Russia I've encountered with a problem that postgresql had no support of national alphabet and after looking for possible workaround I decided to make support of locale myself. I'm not a C-programer but already had some experience with locale programming when I work with perl (debugging) and glimpse. After several days of digging postgresql source tree I've made very minor corections to src/backend/utils/adt/varlena.c and src/backend/main/main.c and got what I need ! I did support only for LC_CTYPE and LC_COLLATE ! Later LC_MONETARY was added by somebody (sorry). I got many messages from people about this patch so I've decide to send it to developers and (to my surprise) it was incorporated into postgresql distribution. People often complain that locale doesn't working for them.

Several common mistakes:

• Didn't properly configure postgresql before compilation.

  You must run configure with --enable-locale option to enable locale support.

• Didn't setup environment correctly when starting postmaster.
  You must define environment variables $LC_CTYPE and $LC_COLLATE before running postmaster because backend gets information about locale from environment. I use following shell script (runpostgres):

  #!/bin/sh
  
  export LC_CTYPE=koi8-r
  export LC_COLLATE=koi8-r
  postmaster -B 1024 -S -D/usr/local/pgsql/data/ -o '-Fe'
  

  and run it from rc.local as
  

  /bin/su - postgres -c "/home/postgres/runpostgres"
  

• Broken locale support in OS (for example, locale support in libc under Linux several times has changed and this caused a lot of problems). Latest perl has also support of locale and if locale is broken perl -v will complain something like:

  8:17[mira]:~/WWW/postgres>setenv LC_CTYPE not_exist
  8:18[mira]:~/WWW/postgres>perl -v
  perl: warning: Setting locale failed.
  perl: warning: Please check that your locale settings:
          LC_ALL = (unset),
          LC_CTYPE = "not_exist",
          LANG = (unset)
      are supported and installed on your system.
  perl: warning: Falling back to the standard locale ("C").
    

• Wrong location of locale files !

  Possible location: /usr/lib/locale (Linux, Solaris), /usr/share/locale (Linux), /usr/lib/nls/loc (DUX 4.0) Check man locale for right place. Under Linux I did symbolical link between /usr/lib/locale and /usr/share/locale to be sure next libc will not broke my locale :-)

What are the benefits ?

You can use ~* and order by operators for strings contain characters from national alphabets. Non-english users definitely need that. If you won't use locale stuff just undefine USE_LOCALE variable.

What about drawbacks ?

There is one evident drawback of using locale - it's speed ! So, use locale only if you really need it.

If you have a problem with typing of 8-bit characters in psql, you need to create file ~/.inputrc and insert two lines:

  set convert-meta Off
  set output-meta On

If your OS is Linux and you're looking for koi8-r locale, you can try these files or another locales.

I've made locale_patch.60 for PostgreSQL v.6.0. Most probably you don't need this patch because locale support was incorporated into postgresql distribution since development version 6.1 !

Sometimes people asking about where to get locale. I did locale (koi8-r) myself from sources available from WG15 group.

I maintain a daily mirror of ftp.postgresql.org at our ftp-site ftp://ftp.sai.msu.su/pub/mirrors/pgsql. We have no restriction policy so european people could find it useful when link to US is down.