setval and currval don't work through AR model unless data is already present in underlying table

[ak.from.w...@gmail.com]

I have a model that loads data directly to the database with the pg gem. It does this by creating a CSV file on the fly, and assigning the values to the id field by calling: self.class.select("nextval('apps_id_seq'::regclass)").first['nextval'] in a to_csv instance method. This works great in production. However, I'm in the process of filling in some tests, and if this test is run first, or by itself, it fails because the DB has just been reset by the Database Cleaner gem and the sequences are reset. But they aren't given a starting value. Postgres documentation says you can call setval on the sequence in question to set it up. So let's say I want to set it up to start at 1, so that the next time nextval is called, it will return a 1: select setval('apps_id_seq'::regclass, 1, false); But I can't call this the same way as I call nextval above, because it just plain doesn't work. It returns nil, and does not set up the sequence. No iteration of anything I try: self.class.select("setval('apps_id_seq'::regclass)").first App.connection.execute("select setval('apps_id_seq'::regclass,1,false)") ActiveRecord::Base.connection.execute("select setval('apps_id_seq'::regclass,1)") ActiveRecord::Base.connection.execute("select setval('apps_id_seq'::regclass,1,false)") ActiveRecord::Base.connection.execute("select setval('apps_id_seq'::regclass,1,false)") nor any combination thereof works, it just refuses to work. I don't know if it's a pg gem problem. These statements work in development mode: App.select("setval('apps_id_seq'::regclass,1)").first and: App.select("nextval('apps_id_seq'::regclass)").first Yet, neither of them work in test. BUT, these statements DO work in test mode: ActiveRecord::Base.connection.execute("select setval('apps_id_seq'::regclass,1,false)").first and: ActiveRecord::Base.connection.execute("select nextval('apps_id_seq'::regclass)").first The ONLY DIFFERENCE in the two environments, as far as I can see, is that one has data, while the other one does not. And my test db is created with rake db:test:prepare from a structure.sql file. I have determined that setval and nextval WILL NOT WORK AS ADVERTISED UNLESS DATA IS PRESENT IN THE UNDERLYING TABLE. I proved this by using AR create on the model to create a dummy record. Then, the commands worked fine. I then deleted the dummy record, tried the commands again, and failed again.

[Lars Kanis]

Am 15.01.2014 21:18, schrieb ak.from.w...@gmail.com:

The ONLY DIFFERENCE in the two environments, as far as I can see, is that one has data, while the other one does not. And my test db is created with rake db:test:prepare from a structure.sql file.

Without trying out the samples you provided, I can say that an important difference between test and other environments is that the tests are usually executed within transactions. These transactions are always rolled back when the test finishes. This way only a single fixture loading has to be done. You can disable this feature by use_transactional_fixtures=false.

--
Regards,
Lars

[Michael Granger]

On 15 Jan 2014, at 12:18, ak.from.w...@gmail.com wrote:

> Postgres documentation says you can call setval on the sequence in
> question
> to set it up. So let's say I want to set it up to start at 1, so that
> the
> next time nextval is called, it will return a 1:
>
> select setval('apps_id_seq'::regclass, 1, false);
>
> But I can't call this the same way as I call nextval above, because it
> just
> plain doesn't work. It returns nil, and does not set up the sequence.
>
> No iteration of anything I try:
>
> self.class.select("setval('apps_id_seq'::regclass)").firstApp.connection.execute("select

> setval('apps_id_seq'::regclass,1,false)")ActiveRecord::Base.connection.execute("select
> setval('apps_id_seq'::regclass,1)")ActiveRecord::Base.connection.execute("select
> setval('apps_id_seq'::regclass,1,false)")ActiveRecord::Base.connection.execute("select

> setval('apps_id_seq'::regclass,1,false)")
>
> nor any combination thereof works, it just refuses to work. I don't
> know if
> it's a pg gem problem.

Have you tried using the raw PG::Connection object? E.g.,

ActiveRecord::Base.connection.raw_connection.
exec( "select setval('apps_id_seq',1, false)" )

ActiveRecord::Base.connection returns an ActiveRecord connection
adapter, so you're not yet talking to PG itself when you call #execute.

If that works in your test case, it might be something outside of PG. In
any case, I'd also check the server logs, potentially adjusting the
logging to show queries to be sure you know what's being executed.

--
Michael Granger <g...@FaerieMUD.org>
Rubymage, Architect, Believer
The FaerieMUD Consortium <http://faeriemud.org/>