SET standard_conforming_string kills transactions when using older postgres
Reported by Tony Pitale | October 21st, 2009 @ 09:48 PM
ERROR: unrecognized configuration parameter "standard_conforming_strings"
Comes from line 61 of connection.rb and is a warning that comes from do_postgres_ext.c's full_connect method. The three SET commands run there (in C) are preventing future statements from being executed. It seems they are being wrapped in a transaction along with the connection initialize.
After that error, everything I try and run returns this:
"… data_objects_adapter.rb:89:in execute_reader':
ERROR: current transaction is aborted, commands ignored until end
of transaction block (DataObjects::TransactionError)"
The reason the SET commands are failing is because I'm trying to use postgres 7.4, and perhaps that's my problem to handle. Either way, two things don't make sense:
- Why are these in the same transaction as the query I'm trying
to run?
- Why are the SET commands placed in the C code and not elsewhere in ruby parts?
In fact, everything which uses a connection appears to be implicitly wrapped in a transaction. This does not seem like the responsibility of the library to me. The control of transactions is the responsibility of the query-writer. The query-writer is further up the chain, or the user of DM.
Comments and changes to this ticket
-
Dirkjan Bussink October 23rd, 2009 @ 01:52 AM
- State changed from “new” to “unconfirmed”
Well, first of all I've never been testing this stuff with 7.4, so that's definitely part of the problem.
I think it would be nice if we could move some of this initialization code to ruby, since it's only run once on each connect and makes it easier for people to change things.
On the transaction issue, I have no idea where that is coming from. Afaik nothing is implicitly wrapped in a transaction and I've never seen the issue with version >= 8.0. There it only uses transactions where explicitly requested. Maybe the options are doing some nasty things with 7.4?
Could you try testing with all the options disabled, whether that fixes it for you or not? If it does, we probably should work on implementing some versions checks, but we have to make sure we check the server version and not the client one (easy mistake to make).
-
Tony Pitale October 23rd, 2009 @ 08:58 AM
Removing the SET standard_conforming_string line from the C, and recompiling, fixes that issue on 7.4. I'm not certain how this affects the rest of DO and DM, if they are expecting strings to be a particular way.
I was looking into how AR handles this. They do it in Ruby by asking to SHOW that setting, and rescuing. That sets a flag for the rest of AR to use. I would love to be able to use 7.4 (not have to upgrade), but I'm not sure making a widespread change on how strings are handled is worth it.
Simply having the SET parts in the Ruby, so I could get rid of them if needed. Perhaps even options for #setup. After that, I can handle making sure strings are properly escaped in my queries.
Thoughts?
-
Tony Pitale October 23rd, 2009 @ 09:04 AM
The reason I thought there was in implicit transaction was that the opening of a connection runs through the Transaction class, my test query is not using a transaction in it, but the error returned from postgres claims that the transaction was aborted due to the previous error and I can't run any more queries.
Very strange.
-
Tony Pitale October 26th, 2009 @ 09:08 PM
Okay, I've taken the SET statements out of do_postgres_ext.c and added a call from the c for a ruby method after_connect. I've also created a DO::Postgres::Settings class which takes a connection and has methods to run those 3 SETs as commands. This works with all the application code I could throw at it.
Now to write some tests since my proof-of-concept (and learning out to send to ruby from c) worked out so well. Any input from you dbussink? We spoke briefly about doing Server/Client Version stuff, and then running the appropriate commands for a server version. I would not be up for doing this, yet, as I have no idea how to get that information at this time. I can probably find it soon, but will speak to you about it when I can.
-
Dan Kubb (dkubb) May 22nd, 2010 @ 01:24 AM
- Assigned user set to “Dirkjan Bussink”
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Dan Kubb (dkubb)
Dirkjan Bussink
Piotr Solnica (solnic)
Tony Pitale