Thread: Alternative to psql -c ?

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

On Wed, 25 Jun 2014 10:09:18 -0400
Andrew Sullivan <ajs@crankycanuck.ca> wrote:

> On Wed, Jun 25, 2014 at 02:43:25PM +0100, James Le Cuirot 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.
>
> Why not just read a file?  -f filename.
>
> A

Same problem as stdin, the transactional behaviour is different. There
is the --single-transaction option but as the man page says...

"If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will
not have the desired effects."

Regards,
James

On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote:
> Same problem as stdin, the transactional behaviour is different. There
> is the --single-transaction option but as the man page says...
>
> "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will
> not have the desired effects."

Hmm.  I've _used_ transactions in such files, I'm pretty sure.  You
don't need the --single-transaction setting for this, just do the
BEGIN; and COMMIT; yourself.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Wed, 25 Jun 2014 10:24:53 -0400
Andrew Sullivan <ajs@crankycanuck.ca> wrote:

> On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote:
> > Same problem as stdin, the transactional behaviour is different.
> > There is the --single-transaction option but as the man page says...
> >
> > "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option
> > will not have the desired effects."
>
> Hmm.  I've _used_ transactions in such files, I'm pretty sure.  You
> don't need the --single-transaction setting for this, just do the
> BEGIN; and COMMIT; yourself.
>
> A

Sorry, you're missing the point. I'm trying not to alter the existing
behaviour of the Chef database cookbook which is used by countless
people to execute scripts big and small, with and without transactions.
If I just naively wrapped them all in BEGIN/COMMIT then it would
override any additional transactions within the scripts.

James

On Wed, Jun 25, 2014 at 03:37:11PM +0100, James Le Cuirot wrote:

> Sorry, you're missing the point. I'm trying not to alter the existing
> behaviour of the Chef database cookbook

Ah, got it.  Sorry, I'm clueless.  No, I don't think I have a
suggestion, then.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Wed, 25 Jun 2014 16:42:53 +0200
hubert depesz lubaczewski <depesz@gmail.com> wrote:

> On Wed, Jun 25, 2014 at 4:37 PM, James Le Cuirot
> <chewi@aura-online.co.uk> wrote:
>
> > On Wed, 25 Jun 2014 10:24:53 -0400
> > Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> >
> > > On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote:
> > > > Same problem as stdin, the transactional behaviour is different.
> > > > There is the --single-transaction option but as the man page
> > > > says...
> > > >
> > > > "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this
> > > > option will not have the desired effects."
> > >
> > > Hmm.  I've _used_ transactions in such files, I'm pretty sure.
> > > You don't need the --single-transaction setting for this, just do
> > > the BEGIN; and COMMIT; yourself.
> > >
> > > A
> >
> > Sorry, you're missing the point. I'm trying not to alter the
> > existing behaviour of the Chef database cookbook which is used by
> > countless people to execute scripts big and small, with and without
> > transactions. If I just naively wrapped them all in BEGIN/COMMIT
> > then it would override any additional transactions within the
> > scripts.
> >
> > James
>
> Perhaps you can explain what is the functionality you want to
> achieve, as I, for one, don't understand. Do you want transactions?
> Or not?

I want an implicit transaction around the whole script if no explicit
transactions are present in the script. This is what psql's -c option
does and this is also what the pg gem does because both use PQexec.
This behaviour is described near the top of these pages:

http://www.postgresql.org/docs/devel/static/libpq-exec.html
http://www.postgresql.org/docs/devel/static/app-psql.html

> Also - I have no idea what "peer authentication" has to do with Pg
> gem - care to elaborate? The gem is for client, and authentication
> happens in server, so ... ?

Right but peer authentication is all to do with the operating system
user that the client is connecting from. In the case of chef-client,
this is almost always root. Chef cookbooks typically want to create
databases and populate them with tables. Creating databases is
usually done by the postgres user. Since the database cookbook
currently doesn't shell out, the postgres user must be given a
password. Similarly, creating tables is usually done by the user that
will eventually use them, and this user must also be given a password.
Needlessly creating passwords seems like a bad idea, especially since
these passwords get stored in the central Chef database.

Now I did consider that root could shell out to Ruby and use the pg gem
as another user but that would lose the benefit of not having to
install build-essential.

Hope this makes sense now.

Regards,
James

Re: James Le Cuirot 2014-06-25 <20140625144325.49d1124d@red.yakaraplc.local>
> 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

No idea on the "psql -c" part, but I'd try using the pg gem, and use a
.pgpass file along with md5 authentication. If you set the PGHOST env
var (or PGUSER, PGSERVICE, ...), you don't even need to change any
code for that to work with the usual PG default configs.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

James Le Cuirot <chewi@aura-online.co.uk> 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@comcast.net
p: 312.241.7800

James Le Cuirot <chewi@aura-online.co.uk> writes:
> hubert depesz lubaczewski <depesz@gmail.com> wrote:
>> Perhaps you can explain what is the functionality you want to
>> achieve, as I, for one, don't understand. Do you want transactions?
>> Or not?

> I want an implicit transaction around the whole script if no explicit
> transactions are present in the script. This is what psql's -c option
> does and this is also what the pg gem does because both use PQexec.

Well, psql -c (and also multiple commands in PQexec) have more odd
behaviors than that.  Notably, any error causes abandonment of the *whole*
string, even if it's got begin/end commands in it.

Most people think that psql -c's behavior is a legacy thing that we ought
to change someday; if backwards compatibility weren't such a big deal
around here, we might've done it already.  You can find multiple bugs in
the pgsql-bugs archives that amount to complaints that psql -c ought to
work more like reading from stdin or a file.  I don't think it's a good
idea for you to try to arrange things so that untold amounts of code is
depending on that behavior never changing.

In short, I think it would be good to push back on the way Chef is doing
things now, not perpetuate a dependency on a legacy behavior.

            regards, tom lane

On Wed, Jun 25, 2014 at 8:43 AM, James Le Cuirot
<chewi@aura-online.co.uk> 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

On Wed, 25 Jun 2014 09:04:44 -0700
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> James Le Cuirot <chewi@aura-online.co.uk> writes:
> > hubert depesz lubaczewski <depesz@gmail.com> wrote:
> >> Perhaps you can explain what is the functionality you want to
> >> achieve, as I, for one, don't understand. Do you want transactions?
> >> Or not?
>
> > I want an implicit transaction around the whole script if no
> > explicit transactions are present in the script. This is what
> > psql's -c option does and this is also what the pg gem does because
> > both use PQexec.

> In short, I think it would be good to push back on the way Chef is
> doing things now, not perpetuate a dependency on a legacy behavior.

The mention of "legacy behaviour" and "unexpected results" in the
psql man page hadn't gone unnoticed but I didn't think I would be able
to convince the Chef guys to change their approach based on that. I
think I stand a much better chance now that you of all people have said
it though so thanks. :)

This got me wondering what Rails uses. I dug into ActiveRecord and
found that apart from the odd call to PQexec with hardcoded single
statements, it uses PQsendQuery. The libpq docs state a few of the
differences but don't mention whether PQsendQuery automatically creates
a transaction like PQexec does. Please could you clarify this?

Regards,
James

On Wed, 25 Jun 2014 13:21:44 -0500
Merlin Moncure <mmoncure@gmail.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.

Thanks anyway,
James

On Wed, 25 Jun 2014 10:34:57 -0500
Jerry Sievers <gsievers19@comcast.net> 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

On Wed, 25 Jun 2014 17:30:15 +0200
hubert depesz lubaczewski <depesz@gmail.com> wrote:

> On Wed, Jun 25, 2014 at 5:18 PM, James Le Cuirot
> <chewi@aura-online.co.uk> wrote:
>
> > > Also - I have no idea what "peer authentication" has to do with Pg
> > > gem - care to elaborate? The gem is for client, and authentication
> > > happens in server, so ... ?
> > Right but peer authentication is all to do with the operating system
> > user that the client is connecting from. In the case of chef-client,
> >
>
> Any reason why you can't reconfigure Pg to allow root connections to
> postgres account?

I had considered this. In practise, the "end user" would also need to
be added. The postgresql cookbook currently doesn't generate the
pg_ident.conf file but it could.

However, this would still require the installation of build-essential
and Tom Lane has pointed out that using PQexec is flawed anyway so
maybe I could use stdin after all.

James

On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot
<chewi@aura-online.co.uk> wrote:
> On Wed, 25 Jun 2014 13:21:44 -0500
> Merlin Moncure <mmoncure@gmail.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.

It'd be neat if psql had some xargs compatible facility for passing
multiple files.  This is complicated by the assumption that the
unadorned argument is the database.  Suppose though if the -f switch
is arguments past the first are assumed to be files. Then you could
do:

psql foodb -1f foo.sql bar.sql baz.sql

or find . | xargs psql foodb -1f

(it's arguably cleaner to allow multiple -f arguments, but that's a
lot more problematic for xargs type usage).

As things stand today though, AFAICT the best way to consolidate
scripts is to build a big script out of small ones.  I realize that
stinks from your point of view since not everyone will want to use
unix/bash...

merlin

On Thu, 26 Jun 2014 07:23:02 -0500
Merlin Moncure <mmoncure@gmail.com> wrote:

> On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot
> <chewi@aura-online.co.uk> wrote:
> > On Wed, 25 Jun 2014 13:21:44 -0500
> > Merlin Moncure <mmoncure@gmail.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

On Thu, Jun 26, 2014 at 7:59 AM, James Le Cuirot
<chewi@aura-online.co.uk> wrote:
> On Thu, 26 Jun 2014 07:23:02 -0500
> Merlin Moncure <mmoncure@gmail.com> wrote:
>> 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.

yup.  Passing huge scripts via -c is definitely not a good practice.
-c is designed for things like firing off a stored procedure from cron
or bash variable assignment (for example: FOO=`psql -tXAqc"select
bar()"`)

> 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.

yup -- i keep forgetting that.  too many years of not having it i suppose.

merlin

James Le Cuirot <chewi@aura-online.co.uk> writes:
> This got me wondering what Rails uses. I dug into ActiveRecord and
> found that apart from the odd call to PQexec with hardcoded single
> statements, it uses PQsendQuery. The libpq docs state a few of the
> differences but don't mention whether PQsendQuery automatically creates
> a transaction like PQexec does. Please could you clarify this?

PG is not capable of executing queries that are not in transactions,
so yes, PQsendQuery will create a single-statement transaction if you
haven't sent BEGIN.  However, there's a huge difference for the
purposes we're discussing here: PQsendQuery does not allow more than
one SQL command in the string.  So most of this discussion is irrelevant
when you're going through that API.

            regards, tom lane

On Thu, 26 Jun 2014 11:02:09 -0700
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> James Le Cuirot <chewi@aura-online.co.uk> writes:
> > This got me wondering what Rails uses. I dug into ActiveRecord and
> > found that apart from the odd call to PQexec with hardcoded single
> > statements, it uses PQsendQuery. The libpq docs state a few of the
> > differences but don't mention whether PQsendQuery automatically
> > creates a transaction like PQexec does. Please could you clarify
> > this?
>
> PG is not capable of executing queries that are not in transactions,
> so yes, PQsendQuery will create a single-statement transaction if you
> haven't sent BEGIN.  However, there's a huge difference for the
> purposes we're discussing here: PQsendQuery does not allow more than
> one SQL command in the string.  So most of this discussion is
> irrelevant when you're going through that API.

Heh, are you sure? From the docs...

"Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a
command string contains multiple SQL commands, the results of those
commands can be obtained individually."

I also seem to be able to execute multiple statements at a time through
ActiveRecord. This method is just a thin wrapper around PQsendQuery.
Granted I only get the last result but if I change the first statement
to something erroneous, it does fail.

1.9.1 :001 > ActiveRecord::Base.connection.execute("SELECT 1; SELECT 2").first
 => {"?column?"=>"2"}

Regards,
James

James Le Cuirot <chewi@aura-online.co.uk> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> PG is not capable of executing queries that are not in transactions,
>> so yes, PQsendQuery will create a single-statement transaction if you
>> haven't sent BEGIN.  However, there's a huge difference for the
>> purposes we're discussing here: PQsendQuery does not allow more than
>> one SQL command in the string.  So most of this discussion is
>> irrelevant when you're going through that API.

> Heh, are you sure? From the docs...

> "Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a
> command string contains multiple SQL commands, the results of those
> commands can be obtained individually."

Oh, sorry, I was confusing that with the extended-query-mode API
(PQexecParams).

Yeah, PQsendQuery is like PQexec for this purpose --- the backend does
not actually know the difference.

            regards, tom lane

On Thu, 26 Jun 2014 15:00:56 -0700
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> James Le Cuirot <chewi@aura-online.co.uk> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> PG is not capable of executing queries that are not in
> >> transactions, so yes, PQsendQuery will create a single-statement
> >> transaction if you haven't sent BEGIN.  However, there's a huge
> >> difference for the purposes we're discussing here: PQsendQuery
> >> does not allow more than one SQL command in the string.  So most
> >> of this discussion is irrelevant when you're going through that
> >> API.
>
> > Heh, are you sure? From the docs...
>
> > "Using PQsendQuery and PQgetResult solves one of PQexec's problems:
> > If a command string contains multiple SQL commands, the results of
> > those commands can be obtained individually."
>
> Oh, sorry, I was confusing that with the extended-query-mode API
> (PQexecParams).
>
> Yeah, PQsendQuery is like PQexec for this purpose --- the backend does
> not actually know the difference.

Okay so you're implying that it does the same kind of transaction
wrapping as PQexec, right? But is it considered to be problematic and
deprecated like PQexec or is it safe for multiple statements? Sorry for
the never ending questions. :)

James