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?
Regards,
James