Hi,

I have made a small tool called "pgbench," that may be useful for
some stress testings and performance measurements for PostgreSQL.
pgbench can be obtained from:

	ftp://ftp.sra.co.jp/pub/cmd/postgres/pgbench/

Pgbench runs small transactions similar to TPC-B concurrently and
reports the number of transactions actually done per second (tps).
Pgbench uses the asynchronous functions of libpq to simulate
concurrent clients. Example outputs from pgbench are shown below:

number of clients: 4
number of transactions per client: 100
number of processed transactions: 400/400
tps = 19.875015(including connections establishing)
tps = 20.098827(excluding connections establishing)

(above result was reported on my PowerBook with 603e CPU(180MHz), 80MB
mem running PostgreSQL 6.5.2 with -F option, and Linux 2.2.1 kernel)

Pgbench does not require any special libraries other than libpq. It
comes with a configure script and should be very easy to build.

*CAUTION*
pgbench will blow away tables named accounts, branches, history and
tellers. It is best to create a new database for pgbench before
running it.

BTW, the greatest tps I have ever seen was around 260 on a Linux box
running RedHat 6.0, having 2 Pentiumn III 600MHz CPUs and 512MB mem.

Enjoy,
---
Tatsuo Ishii

Thu, 30 Sep 1999 10:15:59 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Announcement: pgbench-1.1 released
Hi,

  With many cooperators, I have made a *bash built-in command* for
PostgreSQL called "pgbash".

  The pgbash is the system which offers the 'direct SQL'/'embedded
SQL' interface for PostgreSQL by being included in the bash-2.x 
shell. 


Features of pgbash
-------------------

1.The pgbash has a function which is equivalent to psql except for
  the interactive input processing function. 

2.It is possible that pgbash carries out the interactive input
  processing  using the hysteresis editing function ( history, !, 
  fc command ) of bash.

3.An output of retrieval result and database information of pgbash 
  uses PSprint() which improved PQprint(). By PSprint(), it is 
  possible to output by plain table type, plain table + outer frame 
  type and HTML table type. And, it is possible to display NULL 
  value string(like '-NULL-') and bit zero string(like '-0-').

4.It is possible that pgbash manipulates multiple databases using
  CONNECT, DISCONNECT and SET CONNECTION (or -d option ).

5.The pgbash has a function which substitutes the retrieval result 
  for the shell variable using FETCH INTO statement. 

6.It is possible to set CGI mode. In CGI mode, the pgbash switches 
  the output to HTML, and read the datat by GET/POST method, and 
  read the data of HTTP_COOKIE.

7.The pgbash sets "error code", "error message", "number of tuples",
  etc to the shell variable. Therefore, it is possible to know the
  condition after the SQL execution. 


  Details is as follows.  
  http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

# I am very glad, if many people will use the pgbash.

--
Regards.

SAKAIDA Masaaki  -- Osaka, Japan　
# Sorry, I am not good at English. 


Fri, 01 Oct 1999 17:24:57 +0900
SAKAIDA <sakaida@psn.co.jp>
pgbash-1.1.1 release
That's really cool !
I just install and played a little bit.
I found a minor problem :
I have to connect to any database to issue
exec_sql -l database
I have no my personal database
Here is a log:

bash-2.03$ exec_sql -l database
(-402)FATAL 1:  Database megera does not exist in pg_database
bash-2.03$ exec_sql "connect to discovery"
# PostgreSQL 6.5.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
# CONNECT TO  discovery:5432  AS  discovery  USER  megera

bash-2.03$ exec_sql -l database
# Databases list

datname  |datdba|encoding|datpath  
---------+------+--------+---------
template1|   505|      16|template1
apod     |    11|      16|apod     

I don't understand this requirements just to list all databases

	Regards,

			Oleg


On Fri, 1 Oct 1999, SAKAIDA wrote:

> Date: Fri, 01 Oct 1999 17:24:57 +0900
> From: SAKAIDA <sakaida@psn.co.jp>
> To: hackers@postgreSQL.org
> Cc: pgsql-interfaces@postgreSQL.org
> Subject: [HACKERS] pgbash-1.1.1 release
> 
> Hi,
> 
>   With many cooperators, I have made a *bash built-in command* for
> PostgreSQL called "pgbash".
> 
>   The pgbash is the system which offers the 'direct SQL'/'embedded
> SQL' interface for PostgreSQL by being included in the bash-2.x 
> shell. 
> 
> 
> Features of pgbash
> -------------------
> 
> 1.The pgbash has a function which is equivalent to psql except for
>   the interactive input processing function. 
> 
> 2.It is possible that pgbash carries out the interactive input
>   processing  using the hysteresis editing function ( history, !, 
>   fc command ) of bash.
> 
> 3.An output of retrieval result and database information of pgbash 
>   uses PSprint() which improved PQprint(). By PSprint(), it is 
>   possible to output by plain table type, plain table + outer frame 
>   type and HTML table type. And, it is possible to display NULL 
>   value string(like '-NULL-') and bit zero string(like '-0-').
> 
> 4.It is possible that pgbash manipulates multiple databases using
>   CONNECT, DISCONNECT and SET CONNECTION (or -d option ).
> 
> 5.The pgbash has a function which substitutes the retrieval result 
>   for the shell variable using FETCH INTO statement. 
> 
> 6.It is possible to set CGI mode. In CGI mode, the pgbash switches 
>   the output to HTML, and read the datat by GET/POST method, and 
>   read the data of HTTP_COOKIE.
> 
> 7.The pgbash sets "error code", "error message", "number of tuples",
>   etc to the shell variable. Therefore, it is possible to know the
>   condition after the SQL execution. 
> 
> 
>   Details is as follows.  
>   http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html
> 
> # I am very glad, if many people will use the pgbash.
> 
> --
> Regards.
> 
> SAKAIDA Masaaki  -- Osaka, Japan$B!!(B
> # Sorry, I am not good at English. 
> 
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Fri, 1 Oct 1999 15:38:11 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] pgbash-1.1.1 release

Oleg Bartunov <oleg@sai.msu.su>  wrote:
> That's really cool !

  Thank you.

> I just install and played a little bit.
> I found a minor problem :
> I have to connect to any database to issue
> exec_sql -l database
> I have no my personal database
> Here is a log:
> 
> bash-2.03$ exec_sql -l database
> (-402)FATAL 1:  Database megera does not exist in pg_database

  If CONNECT have not been executed yet, "CONNECT TO DEFAULT" 
will be automatically issued when -l option is executed.

  If user name is "megera", then "CONNECT TO DEFAULT" is equal
to "CONNECT TO megera USER megera". 


> bash-2.03$ exec_sql "connect to discovery"
> # PostgreSQL 6.5.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
> # CONNECT TO  discovery:5432  AS  discovery  USER  megera
> 
> bash-2.03$ exec_sql -l database
> # Databases list
> 
> datname  |datdba|encoding|datpath  
> ---------+------+--------+---------
> template1|   505|      16|template1
> apod     |    11|      16|apod     
> 
> I don't understand this requirements just to list all databases

  This approach is equal to psql.  

# However, I consider that "CONNECT TO template1" may be better 
  than "CONNECT TO <User_naame>" in the case of "-l database".


--
Regard.

SAKAIDA Masaaki -- Osaka, Japan
# Sorry, I am not good at English.


Fri, 01 Oct 1999 23:08:10 +0900
SAKAIDA Masaaki <sakaida@psn.co.jp>
Re: [HACKERS] pgbash-1.1.1 release

Oleg Bartunov <oleg@sai.msu.su> wrote:
> Sakaida,
> 
> sorry for bothering you but I didn't find
> TODO file and wondering what new features (if any)
> you plan to implement to pgbash.

  Please give me your opinion. My plan is not yet the concrete,
but a few of my new plan are:

 1. Improvement of function of an HTML output.

   exec_sql -H -O "<TABLE option>"
            -O "*:<TH option1>" -O "name:<TH option2>"
            -O "*:<TD option3>" -O "addr:<TD option4>"
            -F "name:<font color='0000ff' size=4>%-7.7s</font>"
            -F "addr:<fonr color='ff0000'>%s</font>"
            "select * from test where ..."

 2. Snapshot cursor operation.  
   exec_sql -c cur "select * from test where ..."
   exec_sql "open cur"
   exec_sql "fetch in cur into :r1, :r2"
   echo "r1=$r1  r2=$r2"
   exec_sql "close cur"
   # Declare cursor operation is already implemented.


> I'm a little boried to enclose SQL statements
> into double quotes. Is't really need ?

  I think that it is necessary, because in the inside of the 
double quotes, it can be used $variable and it is possible to
describe the SQL statement in the multiple line.  

  ex) exec_sql "select * from test 
        where name='$DATA' and 
              addr='$ADATA'"

> I'm doubt it's possible, because
> SQL statement must begins from valid SQL word.
> 
> If it's impossible to avoid probably pgbash
> might have a possibility to redefine quote character,
> so user could use
> exec_sql [select * from test]
> Notice, no need to press shift key !
> I think with a little more effort this could be
> achieved without explicit redefining of
> quote character. But this is not a big problem,
> I could use alias to define [] as a quote characters
> just as an example:
> alias sql='exec_sql -Q "[]"'
> and then use sql instead of exec_sql.
> 

  I use alias too, but I do not know the method for aliasing 
double quotes.
  
  ex)
  alias E='exec_sql'

  (Shell program)
  -----------------------------------------------------
  #!/usr/local/bin/bash 
  function E {  exec_sql "$@" }
  -----------------------------------------------------
  (KUBO Takehiro taught me this method.)


> Anyway, I'm just speculating about enhancement
> after playing for several hours with pgbash.
> I like it !
 
  I hope a new idea.

--
Regards.

SAKAIDA Masaaki  -- Osaka, Japan　
# Sorry, I am not good at English. 


Sat, 02 Oct 1999 12:39:47 +0900
SAKAIDA <sakaida@psn.co.jp>
Re: [HACKERS] pgbash-1.1.1 release

Oleg Bartunov <oleg@sai.msu.su> wrote:
> Sakaida,
> 
> sorry for bothering you but I didn't find
> TODO file and wondering what new features (if any)
> you plan to implement to pgbash.

  Please give me your opinion. My plan is not yet the concrete,
but a few of my new plan are:

 1. Improvement of function of an HTML output.

   exec_sql -H -O "<TABLE option>"
            -O "*:<TH option1>" -O "name:<TH option2>"
            -O "*:<TD option3>" -O "addr:<TD option4>"
            -F "name:<font color='0000ff' size=4>%-7.7s</font>"
            -F "addr:<fonr color='ff0000'>%s</font>"
            "select * from test where ..."

 2. Snapshot cursor operation.  
   exec_sql -c cur "select * from test where ..."
   exec_sql "open cur"
   exec_sql "fetch in cur into :r1, :r2"
   echo "r1=$r1  r2=$r2"
   exec_sql "close cur"
   # Declare cursor operation is already implemented.


> I'm a little boried to enclose SQL statements
> into double quotes. Is't really need ?

  I think that it is necessary, because in the inside of the 
double quotes, it can be used $variable and it is possible to
describe the SQL statement in the multiple line.  

  ex) exec_sql "select * from test 
        where name='$DATA' and 
              addr='$ADATA'"

> I'm doubt it's possible, because
> SQL statement must begins from valid SQL word.
> 
> If it's impossible to avoid probably pgbash
> might have a possibility to redefine quote character,
> so user could use
> exec_sql [select * from test]
> Notice, no need to press shift key !
> I think with a little more effort this could be
> achieved without explicit redefining of
> quote character. But this is not a big problem,
> I could use alias to define [] as a quote characters
> just as an example:
> alias sql='exec_sql -Q "[]"'
> and then use sql instead of exec_sql.
> 

  I use alias too, but I do not know the method for aliasing 
double quotes.
  
  ex)
  alias E='exec_sql'

  (Shell program)
  -----------------------------------------------------
  #!/usr/local/bin/bash 
  function E {  exec_sql "$@" }
  -----------------------------------------------------
  (KUBO Takehiro taught me this method.)


> Anyway, I'm just speculating about enhancement
> after playing for several hours with pgbash.
> I like it !
 
  I hope a new idea.

--
Regards.

SAKAIDA Masaaki  -- Osaka, Japan　
# Sorry, I am not good at English. 


Sat, 02 Oct 1999 13:00:49 +0900
SAKAIDA <sakaida@psn.co.jp>
Re: [HACKERS] pgbash-1.1.1 release

This works like a charm.  Thanks tons!

;) Clark

On Fri, 1 Oct 1999, SAKAIDA wrote:

> Hi,
> 
>   With many cooperators, I have made a *bash built-in command* for
> PostgreSQL called "pgbash".
> 
>   The pgbash is the system which offers the 'direct SQL'/'embedded
> SQL' interface for PostgreSQL by being included in the bash-2.x 
> shell. 
> 
> 
> Features of pgbash
> -------------------
> 
> 1.The pgbash has a function which is equivalent to psql except for
>   the interactive input processing function. 
> 
> 2.It is possible that pgbash carries out the interactive input
>   processing  using the hysteresis editing function ( history, !, 
>   fc command ) of bash.
> 
> 3.An output of retrieval result and database information of pgbash 
>   uses PSprint() which improved PQprint(). By PSprint(), it is 
>   possible to output by plain table type, plain table + outer frame 
>   type and HTML table type. And, it is possible to display NULL 
>   value string(like '-NULL-') and bit zero string(like '-0-').
> 
> 4.It is possible that pgbash manipulates multiple databases using
>   CONNECT, DISCONNECT and SET CONNECTION (or -d option ).
> 
> 5.The pgbash has a function which substitutes the retrieval result 
>   for the shell variable using FETCH INTO statement. 
> 
> 6.It is possible to set CGI mode. In CGI mode, the pgbash switches 
>   the output to HTML, and read the datat by GET/POST method, and 
>   read the data of HTTP_COOKIE.
> 
> 7.The pgbash sets "error code", "error message", "number of tuples",
>   etc to the shell variable. Therefore, it is possible to know the
>   condition after the SQL execution. 
> 
> 
>   Details is as follows.  
>   http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html
> 
> # I am very glad, if many people will use the pgbash.
> 
> --
> Regards.
> 
> SAKAIDA Masaaki  -- Osaka, Japan$B!!(B
> # Sorry, I am not good at English. 
> 
> 
> ************
> 


Thu, 14 Oct 1999 21:48:58 -0400 (EDT)
"Clark C. Evans" <clark.evans@manhattanproject.com>
Re: [INTERFACES] pgbash-1.1.1 release