Search Postgresql Archives

Re: Alternative to psql -c ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 25 Jun 2014 10:34:57 -0500
Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:

> > The cookbook currently uses PQexec so multiple SQL commands are
> > wrapped in a transaction unless an explicit transaction
> > instruction appears. I don't want to change this behaviour but
> > the only way to get exactly the same effect from psql is to use
> > the -c option.
> >
> > I suspect some may shove rather large SQL scripts through this to
> > the extent that it may break the command line limit, if not on
> > Linux, then perhaps on Windows, where I gather it's 32,768. Passing
> > these scripts on the command line doesn't seem particularly elegant
> > in any case. I'd really like to use stdin but this has different
> > transactional behaviour. I thought about looking for instances of
> > transaction instructions in advance but I have seen that PostgreSQL
> > does not do this naively; it uses the lexer.
> >
> > Is there another way?  
> 
> Forget about trying to use psql -c since you try doing anything
> non-trivial via this method and  quoting will be at least one of your
> headaches.

Ruby executes psql using a kernel exec call and each argument is passed
distinctly without any quoting required so that doesn't seem to be a
problem at least.

> Write a simpel $your-fav-scripting-lang client that passes stdin into
> a single executor call...
> 
> #!/usr/bin/python
> 
> import psycopg2, sys
> 
> conn = psycopg2.connect(...)
> cur = conn.cursor(
> 
> cur.execute(sys.stdin.read())
> conn.commit()

This would work but probably wouldn't fly with the Chef guys as they'd
want to know why it's installing modules for $my-fav-scripting-lang,
which may != $their-fav-scripting-lang. Chances are that this would be
Ruby in both cases but not every system packages the pg gem and that
leads to the build-essential headache.

> PS: Complex multi-statement executor calls are somewhat nuanced in
> their own ways and I would be trying hard *not* to do this without
> very good reason.

Tom Lane has since said as much so I agree that a different approach is
needed. I'll see if I can convince them.

James



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux