James Le Cuirot <chewi@xxxxxxxxxxxxxxxxx> writes: > Hello, > > I've been using the Chef database cookbook and found it > frustrating because it doesn't allow you to use peer > authentication. The client process generally runs as root and > connects to PostgreSQL using the Ruby pg gem. > > I have patched it to shell out to psql instead. This has the > added benefit of not having to install "build-essential" on boxes > that might otherwise be very minimal. This is normally needed to > build the pg gem. I would therefore like to push the shell out > approach but one problem remains. > > 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. 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() HTH) 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. > Regards, > James -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800
