persistant psql feature suggestion

On Fri, 2003-06-27 at 03:21, James Pye wrote:

Greets,

Just a thought for a psql enhancement, afiak, it is not easily possible for persistent connections to a database in a shell script..
The ability for psql to remain in the background reading from stdin and writing to stdout until explicitly killed. More specifically, so a shell scriptor can have "persistent" connections to the database by calling psql once(leaving it in the bg), and redirecting stdio through a fifo(mkfifo)(sending queries by echo > fifo, and fetching results by cat fifo).
When I have tried this in the past it will read the query, execute it, and exit when the results are cat'd from the fifo.

Not sure if it's exactly what you are looking for, nor how well it's
still maintained, but....

I believe there is a took out there called pgbash which is a modified
version of bash that understands database queries natively. I think
it's just what you are looking for.

Check out: http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

Looks like it was updated for 7.3

Matthew

Another way is to put a little shim between the fifo and psql. Here's one I
quickly whipped up in perl (code stolen shamelessly from the perl man
pages). To run in background, invoke thus
( perl myperlfile myfifo | psql gatabase ) &

The only wrinkle I found was that I had to send the \q twice to make it
quit - I have no idea why.
andrew

------------------------------
use strict;

my $curpos;
my $fifofile = shift || usage();

$|=1;

open(FILE,$fifofile) || die $!;
for (;;)
{
for ($curpos = tell(FILE); $_ = <FILE>; $curpos = tell(FILE))
{
print $_;
}
sleep(1);
seek(FILE, $curpos, 0);
}

sub usage
{
print STDERR "usage: ",$0," fifofile\n";
exit 1;
}

----- Original Message -----
From: "Matthew T. O'Connor" <matthew@zeut.net>
To: "James Pye" <jwp@rhid.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Friday, June 27, 2003 1:44 PM
Subject: Re: [HACKERS] persistant psql feature suggestion

On Fri, 2003-06-27 at 03:21, James Pye wrote:

Greets,

Just a thought for a psql enhancement, afiak, it is not easily possible

for persistent connections to a database in a shell script..

The ability for psql to remain in the background reading from stdin and

writing to stdout until explicitly killed. More specifically, so a shell
scriptor can have "persistent" connections to the database by calling psql
once(leaving it in the bg), and redirecting stdio through a
fifo(mkfifo)(sending queries by echo > fifo, and fetching results by cat
fifo).

When I have tried this in the past it will read the query, execute it,

and exit when the results are cat'd from the fifo.

OK, worked out the wrinkle. psql is behaving perfectly well, but the shim
doesn't get a SIGPIPE until it tries to write to it after psql has exited.

A slightly hackish fix for this would be to put this line after the "print
$_" line:

if ($_ eq "\\q\n") { sleep 1; print " "; } # get SIGPIPE if client gone

cheers

andrew

----- Original Message -----
From: "Andrew Dunstan" <andrew@dunslane.net>
To: "Matthew T. O'Connor" <matthew@zeut.net>; "James Pye" <jwp@rhid.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Saturday, June 28, 2003 5:44 PM
Subject: Re: [HACKERS] persistant psql feature suggestion

Another way is to put a little shim between the fifo and psql. Here's one

I

quickly whipped up in perl (code stolen shamelessly from the perl man
pages). To run in background, invoke thus
( perl myperlfile myfifo | psql gatabase ) &

The only wrinkle I found was that I had to send the \q twice to make it
quit - I have no idea why.
andrew

------------------------------
use strict;

my $curpos;
my $fifofile = shift || usage();

$|=1;

open(FILE,$fifofile) || die $!;
for (;;)
{
for ($curpos = tell(FILE); $_ = <FILE>; $curpos = tell(FILE))
{
print $_;
}
sleep(1);
seek(FILE, $curpos, 0);
}

sub usage
{
print STDERR "usage: ",$0," fifofile\n";
exit 1;
}

----- Original Message -----
From: "Matthew T. O'Connor" <matthew@zeut.net>
To: "James Pye" <jwp@rhid.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Friday, June 27, 2003 1:44 PM
Subject: Re: [HACKERS] persistant psql feature suggestion

On Fri, 2003-06-27 at 03:21, James Pye wrote:

Greets,

Just a thought for a psql enhancement, afiak, it is not easily

possible

for persistent connections to a database in a shell script..

The ability for psql to remain in the background reading from stdin

and

On Sun, Jun 29, 2003 at 10:22:49AM -0400, Andrew Dunstan wrote:

OK, worked out the wrinkle. psql is behaving perfectly well, but the shim
doesn't get a SIGPIPE until it tries to write to it after psql has exited.

A slightly hackish fix for this would be to put this line after the "print
$_" line:

if ($_ eq "\\q\n") { sleep 1; print " "; } # get SIGPIPE if client gone

cheers

andrew

NAME
IPC::Open2, open2 - open a process for both reading and writing

http://www.perl.com/doc/manual/html/lib/IPC/Open2.html

Would this help?

We don't need to read and write on the same fd.

The real right way is to detect when the psql client exits - possible when
the perl program spawns it, like shown below.

As always, TIMTOWTDI

andrew

--------------------------

use strict;
use IO::Handle;
use POSIX ":sys_wait_h";

my $curpos;
my $fifofile = shift || usage();
my $database = shift || usage();

open(FILE,$fifofile) || die $!;
my $psqlpid = open(OUTPIPE,"|-");
unless (defined($psqlpid)) { die $!; }
if ($psqlpid == 0)
{
exec("psql -a $database") || die $!;
}

# must be parent here
sub REAPER
{
my $waitedpid;
while (($waitedpid = waitpid(-1,WNOHANG)) > 0)
{
if ($waitedpid == $psqlpid) { exit 0; }
}
$SIG{CHLD} = \&REAPER; # loathe sysV
}

$SIG{CHLD} = \&REAPER;

OUTPIPE->autoflush();

for (;;)
{
for ($curpos = tell(FILE); $_ = <FILE>; $curpos = tell(FILE))
{
print OUTPIPE $_;
}
sleep(1);
seek(FILE, $curpos, 0);
}

sub usage
{
print STDERR "usage: ",$0," fifofile database\n";
exit 1;
}

----- Original Message -----
From: "PeterKorman" <calvin-pgsql-ml@eigenvision.com>
To: "Andrew Dunstan" <andrew@dunslane.net>
Cc: <pgsql-hackers@postgresql.org>
Sent: Sunday, June 29, 2003 11:40 AM
Subject: Re: [HACKERS] persistant psql feature suggestion

On Sun, Jun 29, 2003 at 10:22:49AM -0400, Andrew Dunstan wrote:

OK, worked out the wrinkle. psql is behaving perfectly well, but the

shim

doesn't get a SIGPIPE until it tries to write to it after psql has

exited.

A slightly hackish fix for this would be to put this line after the

"print

$_" line:

if ($_ eq "\\q\n") { sleep 1; print " "; } # get SIGPIPE if client

gone

On Sun, Jun 29, 2003 at 02:15:26PM -0400, Andrew Dunstan wrote:

We don't need to read and write on the same fd.

The real right way is to detect when the psql client exits - possible when
the perl program spawns it, like shown below.

As always, TIMTOWTDI

andrew

--------------------------

use strict;
use IO::Handle;
use POSIX ":sys_wait_h";

my $curpos;
my $fifofile = shift || usage();
my $database = shift || usage();

open(FILE,$fifofile) || die $!;
my $psqlpid = open(OUTPIPE,"|-");
unless (defined($psqlpid)) { die $!; }
if ($psqlpid == 0)
{
exec("psql -a $database") || die $!;
}

# must be parent here
sub REAPER
{
my $waitedpid;
while (($waitedpid = waitpid(-1,WNOHANG)) > 0)
{
if ($waitedpid == $psqlpid) { exit 0; }
}
$SIG{CHLD} = \&REAPER; # loathe sysV
}

$SIG{CHLD} = \&REAPER;

OUTPIPE->autoflush();

for (;;)
{
for ($curpos = tell(FILE); $_ = <FILE>; $curpos = tell(FILE))
{
print OUTPIPE $_;
}
sleep(1);
seek(FILE, $curpos, 0);
}

sub usage
{
print STDERR "usage: ",$0," fifofile database\n";
exit 1;
}

I dropped into autopilot without considering James Pye's
original phrase:

"The ability for psql to remain in the background reading
from stdin and writing to stdout until explicitly killed"

I went straight to thinking about handling the psql output
which matched /ERROR/ differently from other output.

I like your solution. But I think I'd code it:

my $psqlpid = open(OUTPIPE,"|-") || die qq(cant fork: $!);

Sometimes I'm too stupid to remember that that perl gives
you the fork (the knife and the kitchen sink) even if you
dont realize you asked for it;-)

Cheers,

JPK

On Sun, Jun 29, 2003 at 05:24:18PM -0400, Andrew Dunstan wrote:

----- Original Message -----
From: "PeterKorman" <calvin-pgsql-ml@eigenvision.com>

I like your solution. But I think I'd code it:

my $psqlpid = open(OUTPIPE,"|-") || die qq(cant fork: $!);

Won't that cause the child to die because it will have 0 in $psqlpid?

Doh!!

A rhetorical question, no doubt.

----------------------------------------------------------
#!/usr/bin/perl

#except for sigchld, This is better.

my $pid;
defined($pid=open(X, "|-")) || die qq(cant fork: $!\n);

if ($pid){
system(qq(touch parent));
}
else{
system(qq(touch child));
};

----------------------------------------------------------

I wrongly guessed that control would resume at the line
following:

my $psqlpid = open(OUTPIPE,"|-") || die qq(cant fork: $!);

There are, evidently, many things I'm too stupid to remember;-\

Cheers,

JPK