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
