On Wed, 25 Jun 2014 10:34:57 -0500
Jerry Sievers <gsievers19@comcast.net> 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