Transaction handling in extended query mode and Sync message issues

Carlos Guzman Alvarez wrote:

Hello:

Hello Carlos.

I continue to work in the implementation of the 3.0 protocol in C#, i'm
making a test that consist on:

- Create a database.
- Create a table in the new database.
- Start transaction.
- Insert 100 rows of data in the new table.
- Commit transaction.

For start a transaction i send a Query message with this as statement text:

START TRANSACTION ISOLATION LEVEL READ COMMITTED

For commit a transaction i send a Query message with this as statement
text:

COMMIT TRANSACTION

For execute the inserts i use parametrized commands with Extended query
mode, sending this sequence of messages:

- Parse & Flush ( Only for the first command )
- Describe & Flush ( Only for the first command )
- Close Portal ( begining at second command )
- Bind & Flush
- Execute & Sync

But the Sync message seems to be commiting the transaction, if i send a
Flush message all works as expected with inserts, but create database &
table will not work.

Sorry for late response...
I could finally get Npgsql to talk protocol 3.0 version :) It is not
100% but it is near...
I give it a try in a test similar to yours... I didn't send the create
database commands just the row insertion.

in both sequences, I could get the desired behaviour.
I could send the begin transaction in simple query mode, send the insert
in extended mode and send a commit or rollback in simple mode sending
the sync message in the end of extended mode.

Are you still having problems with it?

I hope it helps.

--
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

Hello:

Sorry for late response...
I could finally get Npgsql to talk protocol 3.0 version :) It is not
100% but it is near...

Great !! I'm going to open my code at sourceforge :)

I give it a try in a test similar to yours... I didn't send the create
database commands just the row insertion.

in both sequences, I could get the desired behaviour.
I could send the begin transaction in simple query mode, send the insert
in extended mode and send a commit or rollback in simple mode sending
the sync message in the end of extended mode.

Are you still having problems with it?

I have it fixed sending the Sync message only for ALTER, CREATE AND DROP
commands and seems to work as expected, iwill try to do more tests with
transactions and the sync message soon,

--
Best regards

Carlos Guzm�n �lvarez
Vigo-Spain

- Miembro del Proyecto FirebirdSQL.
- Miembro honorario de la Fundaci�n FirebirdSQL.

http://www.firebirdsql.org

Carlos Guzman Alvarez <carlosga@telefonica.net> writes:

In the extended query mode documentation i see this:
"Note: Sync does not cause a transaction block opened with BEGIN to be
closed. It is possible to detect this situation since the ReadyForQuery
message includes transaction status information."
I have tested the same cycle with a BEGIN WORK but having the same
problem, any idea on what i'm doing wrong ???

Did you figure this out? I can't see any way that a Sync message would
close a transaction started with BEGIN (or START TRANSACTION). It runs
the same code we run at the end of a simple-Query message, and we'd
definitely have noticed if that were causing premature commit ...

regards, tom lane

Hello:

Did you figure this out? I can't see any way that a Sync message would
close a transaction started with BEGIN (or START TRANSACTION). It runs
the same code we run at the end of a simple-Query message, and we'd
definitely have noticed if that were causing premature commit ...

Thanks for answer, I'm going to make more tests but in first tests the
sync message is committing the transaction :) but i don't know why.

--
Best regards

Carlos Guzm�n �lvarez
Vigo-Spain