Re: Alternative to psql -c ?

On Thu, 26 Jun 2014 07:23:02 -0500
Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot
> <chewi(at)aura-online(dot)co(dot)uk> wrote:
> > On Wed, 25 Jun 2014 13:21:44 -0500
> > Merlin Moncure <mmoncure(at)gmail(dot)com> 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?
> >>
> >> 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 ...
> >
> > This would work but given that this will be part of a public and
> > widely-used cookbook, it needs to be able to deal with any scripts
> > that will be thrown at it. Some of these may contain transactional
> > statements and these will not work properly if wrapped in a big
> > BEGIN/COMMIT. Having said that, Tom Lane has suggested that we
> > should not rely on the existing transactional behaviour so maybe
> > we'll need to be more explicit about whether we actually want a
> > transaction or not.
>
> To be clear, Tom was advising not to rely on some of the quirky
> aspects of -c. psql as it stands right now has a some limitations:
> single transaction mode does not work with stdin and there is no
> reasonable way to pass multiple scripts through the command line.
> Adding it up this means that for generic multiple .sql passing you
> have to wrap psql with a script.

I never said that I wanted to pass multiple scripts, just singular
scripts who's contents might be too large for the command line limit,
and that they might contain transaction statements.

I was looking for some way to replicate the -c behaviour without
actually passing the contents on the command line but Tom says I would
not want that behaviour anyway. So in that case, assuming I allow the
existing behaviour to be modified and make single transaction mode an
explicit option for the Chef users...

These scripts come from a Ruby string buffer so passing them via stdin
would be preferable to having to dump them out to a file first. You say
that single transaction mode doesn't work with stdin but it looks like
this was fixed in 9.3. I'd like this to work with earlier versions
though so maybe "psql -1 -f -" would be the way to go. The man page
says that this is subtly different from using < but doesn't mention
transactions specifically. I hope this works.

Regards,
James