On Wed, Jun 25, 2014 at 8:43 AM, James Le Cuirot <chewi@xxxxxxxxxxxxxxxxx> wrote: > 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? If I understand you properly (not sure), I pretty commonly get around this via 'cat'. cat \ <(echo "BEGIN;") \ <(echo "\set ON_ERROR_STOP") \ foo.sql bar.sql etc <(echo "COMMIT;") \ | psql ... merlin
