Thread: Is this legal SQL? Is it a good practice?

I just noticed that from a C or C++ program using
libpq or libpq++, I can send *one* command that
contains several SQL statements separated by
semicolon.  Something like:

PgDatabase db (" .... ");

const char * const sql =
     "insert into blah (...); insert into blah (...)";

if (db.Exec (sql) == PGRES_COMMAND_OK)
{
     cout << "Fine!" << endl;
}

And I verify the table, and all the inserts took place
(and of course, the program outputs "Fine!").

But I'm wondering -- is this a PostgreSQL extension,
or is it "legal SQL"?  In particular, I'm wondering
if it is a feature that in the future you might
decide to eliminate for not being ANSI-SQL compliant.

What happens if the first command is ok but the second
one fails?  I guess PgDatabase::Exec would return an
error code, and PgDatabase::ErrorMessage would return
the error message corresponding to the second statement
(the one that failed).  Am I correct in thinking this?

Any reason why this should be avoided?  (on the plus
side, I think this might increase efficiency for
transactions where one executes several insert or
update statements).

Thanks for any comments,

Carlos
--

Carlos Moreno wrote:
> I just noticed that from a C or C++ program using
> libpq or libpq++, I can send *one* command that
> contains several SQL statements separated by
> semicolon.

> But I'm wondering -- is this a PostgreSQL extension,
> or is it "legal SQL"?

The whole libpq API is made up out of thin air, so it's not conforming
to any public standard.

> In particular, I'm wondering
> if it is a feature that in the future you might
> decide to eliminate for not being ANSI-SQL compliant.

Because of the above, that cannot be a reason for eliminating any
interfaces.

> What happens if the first command is ok but the second
> one fails?

All the commands are run in one transaction, so if one fails, the whole
sequence is rolled back.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

>>I just noticed that from a C or C++ program using
>>libpq or libpq++, I can send *one* command that
>>contains several SQL statements separated by
>>semicolon.
>
>>But I'm wondering -- is this a PostgreSQL extension,
>>or is it "legal SQL"?
>
> The whole libpq API is made up out of thin air, so it's not conforming
> to any public standard.

Oh, wait.  Though you didn't say it explicitly, I guess
you're implying that it is libpq the one that splits the
thing into the individual SQL statements and then send
each of those to the backend?  (wrapped around a
transaction?)

>>In particular, I'm wondering
>>if it is a feature that in the future you might
>>decide to eliminate for not being ANSI-SQL compliant.
>
> Because of the above, that cannot be a reason for eliminating any
> interfaces.

Well, I was under the impression that the backend would
receive *one* command with *one* string that contains
all the SQL's separated by semicolons.  This is what I
thought might be removed in future releases, if it is
considered that it's an unnecessary extension, etc.

Thanks,

Carlos
--

Carlos Moreno wrote:

> But I'm wondering -- is this a PostgreSQL extension,
> or is it "legal SQL"?

This is a reasonably standard result from an "exec" or "execute" call,
but I am not an SQL expert.  How else could the psql command
line utility process command files?

> What happens if the first command is ok but the second
> one fails?

I've not used this interface extensively because I do
mostly transactional work. The easy way to test would be to
make the second statement fail on a syntax error.

> Any reason why this should be avoided?

Yes. You've raised the core issue in your question about what happens
when a command fails.  For efficiency and convenience, use prepared
statements.
For greater efficiency, execute stored procedures to do your work

In general, my web applications use prepared statements for moderate
efficiency.
For batch applications, I would use stored procedures or embedded SQL (both
provide the advantage of stored, optimized query plans).

-bill

Bill Harper wrote:

>> But I'm wondering -- is this a PostgreSQL extension,
>> or is it "legal SQL"?
>
> This is a reasonably standard result from an "exec" or "execute" call,
> but I am not an SQL expert.  How else could the psql command
> line utility process command files?

Well, notice that the keyword in here would be
"utility" -- psql is a program that interacts with
the user, and as such, it can do any processing and
add any logic it wants.

I always thought the semicolon was a psql thing,
which it would use to determine when the user is
done entering the SQL statement.  For libpq or other
client libraries, the way I saw it, it is the end
of the received string what would allow the backend
to figure out where the SQL statement ends.

Thanks,

Carlos
--

Carlos Moreno <moreno@mochima.com> writes:
> I always thought the semicolon was a psql thing,
> which it would use to determine when the user is
> done entering the SQL statement.

Exactly.  psql parses what you type sufficiently well to locate
command-ending semicolons, and then sends one command at a time to the
backend.  libpq is much dumber; it just takes the string you give it and
ships that to the backend.

The backend's current behavior with multi-command input strings has been
that way since the beginning (or at least since before I got here).
People have occasionally proposed removing the feature, but it's pretty
well entrenched on backwards-compatibility grounds.

Note that the new "v3" fe/be protocol does *not* allow multiple commands
in an extended-query-mode input string, so if you are of the opinion
that allowing multiple commands is a bad idea you can just use extended
query mode all the time.  (In libpq that translates to not using PQexec,
but one of the newer functions.)

            regards, tom lane