Desert & Postgresql

[Andrei Erdoss]

Hello,

I am working with a platform (Tog) that uses Desert and rails 2.3. Also I am trying to deploy this app to Heroku which uses Postgresql. When I ran rake db:migrate, this worked partially (it created some of the tables for one plugin) and then failed (both on Heroku and then locally).

Here are the error http://pastie.org/584952

Also this is what I get in the log http://pastie.org/584954

I noticed that these errors do occur in mysql and sqlite3 also, but because they don't user transactions the script continues to run, vs postgresql which stops.

Please let me know if you had any such trouble with Postgresql and if you found a fix.

Thanks,


--
Andrei Erdoss

[Andrei Erdoss]

I'm a bit lost with this issue. Can someone at least point me in the right direction. Thanks,

--
Andrei Erdoss

[Josh Susser]

Rails 2 changed the table it uses to record the current migration
version from schema_info to schema_migrations. Desert also moved from
plugin_schema_info to plugin_schema_migrations. But ithe converter is
not 100% robust and if there is an issue with a migration when
converting things can get messed up. I'm guessing you don't have a
plugin_schema_migrations table, which would be a problem. Can you
dump the app's schema and see what tables exist that have "schema" in
the name?

--josh

[Andrei Erdoss]

Thanks for your reply.

I thought I found a bug in: / lib / desert / plugin_migrations / 2.1 / extensions / schema_statements.rb

plugins_and_versions = select_all("SELECT plugin_name, version from #{Desert::PluginMigrations::Migrator.schema_info_table_name}")

where the schema_info_table_name is called instead of schema_migrations_table_name

At this point it's giving an error that the schema info table does not exist.

When I replaced that with

plugins_and_versions = select_all("SELECT plugin_name, version from #{Desert::PluginMigrations::Migrator.schema_migrations_table_name}")

and then that error went away.

Next, the this statement:

execute "CREATE TABLE #{Desert::PluginMigrations::Migrator.schema_migrations_table_name} (plugin_name #{type_to_sql(:string)}, version #{type_to_sql(:string)})"

seems to giving an error, that PGError: ERROR: relation "plugin_schema_migrations" already exists

I'm at a loss now.

Btw, I looked in the schema and the plugin_schema_migrations table exists.

  create_table "plugin_schema_migrations", :id => false, :force => true do |t|
    t.string "plugin_name"
    t.string "version"
  end

What I noticed that the first error also occurs in mysql and sqlite3 but because those don't use transactions, the migration goes through and it doesn't stop as it does in Postgresql.

--
Andrei Erdoss

[Andrei Erdoss]

Any good news on this issue? Any ideas on how to fix it?

--
Andrei Erdoss

[Andrei Erdoss]

Joe, I created a gem from your repo but I'm getting a different error in the logs:

PGError: ERROR: syntax error at or near "NOT"
LINE 1: CREATE TABLE IF NOT EXISTS plugin_schema_migrations (plugin_...
 ^
: CREATE TABLE IF NOT EXISTS plugin_schema_migrations (plugin_name character varying(255), version character varying(255))

This error is because Postgresql doesn't have this syntax. Is there a workaround?


Also when building the gem I had to change in the gemspec:

"lib/desert/plugin_templates/edge/action_view.rb", to lib/desert/plugin_templates/2.2.0/action_view.rb"

Any ideas?

Thanks,

On Thu, Aug 20, 2009 at 8:08 PM, Joe Moore <j...@pivotallabs.com> wrote:

---------- Forwarded message ----------
From: Joe Moore <j...@pivotallabs.com>
Date: Mon, Aug 17, 2009 at 1:51 PM
Subject: Re: Desert & Postgresql

To: Michael Dalessio <mi...@pivotallabs.com>
Cc: Jeff Dean <jd...@pivotallabs.com>, Chad Woolley <ch...@pivotallabs.com>, fl...@pivotallabs.com


Yes, I found the plugin-migration bug when upgrading LikeMe from Rails 2.2.2 to 2.3.2.  I forked Desert (http://github.com/joemoore/desert/tree/master) and have a fix.

-- Joe

On Mon, Aug 17, 2009 at 1:22 PM, Michael Dalessio <mi...@pivotallabs.com> wrote:

Casebook had a problem with plugin schema tables when we upgraded to 2.3.2. The workaround was to re-run db:migrate, and the second time through, things magically worked. Srsly.

On Mon, Aug 17, 2009 at 10:55 AM, Jeff Dean <jd...@pivotallabs.com> wrote:

It doesn't look like a postgres bug - it looks like desert isn't properly creating the plugin schema tables.  I'm not sure how any desert migrations are working for him if these tables don't exist.

On Mon, Aug 17, 2009 at 10:30 AM, Chad Woolley <ch...@pivotallabs.com> wrote:

Anyone???

--
Andrei Erdoss

[Andrei Erdoss]

Any news on the update? I would like to use the official gem in an app i'm about to deploy.

Thanks,

On Thu, Aug 20, 2009 at 11:33 PM, Joe Moore <j...@pivotallabs.com> wrote:

Great!  Send a pull request and we'll try to get it done.

-- Joe

On Thu, Aug 20, 2009 at 12:58 PM, Andrei Erdoss <erd...@gmail.com> wrote:

Ok, I spent some time on this and I found the fix. You can see it here: http://github.com/cauta/desert/tree/master

It would be great to get this into the official repo.

Thanks,

On Thu, Aug 20, 2009 at 10:30 PM, Andrei Erdoss <erd...@gmail.com> wrote:

---------- Forwarded message ----------
From: Andrei Erdoss <erd...@gmail.com>

Date: Thu, Aug 20, 2009 at 10:29 PM
Subject: Re: Desert & Postgresql

To: pivotallab...@googlegroups.com


Joe, I created a gem from your repo but I'm getting a different error in the logs:

PGError: ERROR: syntax error at or near "NOT"
LINE 1: CREATE TABLE IF NOT EXISTS plugin_schema_migrations (plugin_...
 ^
: CREATE TABLE IF NOT EXISTS plugin_schema_migrations (plugin_name character varying(255), version character varying(255))

This error is because Postgresql doesn't have this syntax. Is there a workaround?


Also when building the gem I had to change in the gemspec:

"lib/desert/plugin_templates/edge/action_view.rb", to lib/desert/plugin_templates/2.2.0/action_view.rb"

Any ideas?

Thanks,

--
Andrei Erdoss

--
Andrei Erdoss

[Joseph Moore]

We just released Desert 0.5.2 with your fix incorporated.

-- Joe

[Andrei Erdoss]

Thanks for the update.

--
Andrei Erdoss

[Andrei Erdoss]

I just got around using the new Desert in Postgresql. There is still a problem, because my changes where not fully implemented. The problem lies in this line in:

lib/desert/plugin_migrations/2.1/extensions/schema_statements.rb

This is the problem line #10.

plugins_and_versions = select_all("SELECT plugin_name, version from #{Desert::PluginMigrations::Migrator.schema_info_table_name}")

In my repo (http://github.com/cauta/desert) this line was changed to:

plugins_and_versions = select_all("SELECT plugin_name, version from #{smt}")

 
Can you please also make this change so that I can use the official Desert repo. Thank you,

On Thu, Aug 27, 2009 at 3:41 AM, Joseph Moore <j...@pivotallabs.com> wrote:

--
Andrei Erdoss

[Andrei Erdoss]

Is this going to take a while? It should be a quick commit. Thanks,

--
Andrei Erdoss

[Josh Susser]

I spent a little time on this but couldn't get things to work with
that line changed, so it's not going to be a quick fix. We'll take a
look at it on Monday when we have more time and see what we can do.

--josh

[Josh Susser]

Andrei, We took a good look at that method just now. Joe and I think
your code is wrong, and that the code in Desert now is correct.
Basically, the code has to look in the old plugin_schema_info table to
find the max version for existing plugins. That's what the Desert code
does. Your change would have the code look in the
plugin_schema_migrations table to find the old versions, which is the
wrong place to look.

So I think now there aren't any other changes for us to make.
Unfortunately it would take me a bit to get set up to run all these
tests on postgresql, so I haven't been able to try it out for reals.
We're setting up a machine we'll be able to use for that soon, so in
the future this won't be such a hassle.

--josh

[Andrei Erdoss]

Thank you for looking into this. This is two sided. On one hand I'm getting an error from Postgresql w/ the current Desert version for not finding the old table (plugin_schema_info). On the other hand, I noticed that the plugin_schema_migrations table gets populated with alot of records. I had over 36101 records. What can cause this?

--
Andrei Erdoss

[Andrei Erdoss]

Would it make sense to add a dummy plugin_schema_info table so to avoid the 'table not found' error?

--
Andrei Erdoss

[Andrei Erdoss]

Any input on this last message? Your response would be greatly appreciated.

--
Andrei Erdoss