Postgres Pro
Downloads
Home   >   mailing lists

Thread: persistant psql feature suggestion

persistant psql feature suggestion

From
James Pye
Date:
Greets,
Just a thought for a psql enhancement, afiak, it is not easily possible for persistent connections to a database in a
shellscript..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(leavingit in the bg), and redirecting stdio through a fifo(mkfifo)(sending queries by echo > fifo, and fetching
resultsby cat fifo).When I have tried this in the past it will read the query, execute it, and exit when the results
arecat'd from the fifo.
 
Just wanted to make sure nothing similar enough was already in existence, and if anyone could easily implement this. If
noone wants to, I suppose I'll look into doing it if it's worth doing. :)
 


Cheers,
James

Re: persistant psql feature suggestion

From
"Matthew T. O'Connor"
Date:
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
ina 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(leavingit in the bg), and redirecting stdio through a fifo(mkfifo)(sending queries by echo > fifo, and fetching
resultsby 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
thefifo.
 

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

Re: persistant psql feature suggestion

From
"Andrew Dunstan"
Date:
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.
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: persistant psql feature suggestion

From
"Andrew Dunstan"
Date:
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
> 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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: persistant psql feature suggestion

From
PeterKorman
Date:
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?

Re: persistant psql feature suggestion

From
"Andrew Dunstan"
Date:
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
> >
> > 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: persistant psql feature suggestion

From
PeterKorman
Date:
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

Re: persistant psql feature suggestion

From
PeterKorman
Date:
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