Обсуждение: 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