Обсуждение: It looks like transaction, but it isn't transaction

Hello.

PostgreSQL driver I'm using let me execute queries made by combining
some commands into one statement:

pgsql:squery(Conn, "CREATE TABLE foo (id int); INSERT INTO foo VALUES
(1), (2)");

There is one thing I find especially interesting: queries I pass to
the pgsql:squery() are executed with some properties specific to
transactions!

For example the following query will be rejected, because bar table
doesn't exist:

pgsql:squery(Conn, "DELETE FROM foo; SELECT * FROM bar") % DELETE
command won't actually delete rows from foo because there is no bar
table

First I thought that my driver just encloses queries I pass in
BEGIN/COMMIT, but I haven't found any of those keywords in the
sources. And SAVEPOINT's also don't work inside this "transactions" as
it would be if driver used BEGIN/COMMIT to make a query into
transaction.

Then I made a decision that this is some kind of convention on how
complex queries delivered from drivers are executed by PostgreSQL.

I'm going to rely on this behaviour as "transactions without
SAVEPOINT/ROLLBACK TO" in some cases but I'm a bit afraid of troubles
that may appear in the future.

Is this behavior explained somewhere in documentation in more detail?
How does this behaviour differ from the traditional transactions which
are defined by enclosing SQL commands in BEGIN/COMMIT?

Thanks.

--
Sergey Samokhin

On Wed, Aug 26, 2009 at 05:06:27AM +0400, Sergey Samokhin wrote:
> There is one thing I find especially interesting: queries I pass to
> the pgsql:squery() are executed with some properties specific to
> transactions!

This behavior seems to be what libpq exposes by default:

  http://www.postgresql.org/docs/current/static/libpq-exec.html

Not sure if it's actually PG imposing these semantics or the libpq driver
itself.  I'd guess it's PG and that's why you're seeing the strange lack
of support for savepoints.

> I'm going to rely on this behaviour as "transactions without
> SAVEPOINT/ROLLBACK TO" in some cases but I'm a bit afraid of troubles
> that may appear in the future.

I always tend to bracket things in an explicit BEGIN+COMMIT, why
wouldn't you do this?

--
  Sam  http://samason.me.uk/

Hello, Sam.

Thanks for the answer.

> This behavior seems to be what libpq exposes by default:
>
>  http://www.postgresql.org/docs/current/static/libpq-exec.html

The driver I was talking about isn't written in C and doesn't use
libpq behind the scene like interfaces for Perl/Python/Tcl do, so what
I see is more likely refers to how PostgreSQL itself executes queries.
I haven't found a good explanation of this behaviour yet.

> I always tend to bracket things in an explicit BEGIN+COMMIT, why
> wouldn't you do this?

Probably that is what I will end up with. But anyway I would like to
know for sure what causes queries to be executed in "almost
transactional" context.

I should rejig my question: is this normal for a query made up from
several commands to be completely discarded if an error occurs? If the
answer is "no, it seems unusual" - I should write a bug report to the
author of the driver.

--
Sergey Samokhin

Sergey Samokhin <prikrutil@gmail.com> writes:
> I should rejig my question: is this normal for a query made up from
> several commands to be completely discarded if an error occurs?

Yes, if it's submitted as a single Query message, that's what happens.
Also, the implicit transaction is around the whole Query message not
just single SQL statements, which probably explains your confusion
on other points.

There's been some discussion of disallowing multiple statements per
Query, partially to avoid these sorts of corner cases and partially
as a defense against SQL-injection attacks.  But so far the conclusion
has been that it would break a lot of code while not buying much.

            regards, tom lane

Hello, Tom.

On Thu, Aug 27, 2009 at 5:25 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Yes, if it's submitted as a single Query message, that's what happens.
> <skipped>

Thanks for the detailed explanation.

--
Sergey Samokhin