Backups are losing their default function (breaking "model#create")

hhff commented 6 years ago

Thank you so much for the hard work!

What command did you execute?

production backup
staging restore-from production
staging console
> Post.create!

ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, f, null, 1, 2018-09-01 01:08:11.290753, 2018-09-01 01:08:11.290753, ed2cfe43-973f-4ae8-b929-cdc758b986ae, null).
: INSERT INTO "posts" ("blog_id", "created_at", "updated_at", "slug") VALUES ($1, $2, $3, $4) RETURNING "id"
    from (irb):3

FYI - on staging:

irb(main):001:0> Spree::ShippingCategory.columns_hash['id']
=> #<ActiveRecord::ConnectionAdapters::PostgreSQLColumn:0x00007f76e0644778 @name="id", @table_name="spree_shipping_categories", @sql_type_metadata=#<ActiveRecord::ConnectionAdapters::SqlTypeMetadata:0x00007f76e0644890 @sql_type="integer", @type=:integer, @limit=nil, @precision=nil, @scale=nil>, @null=false, @default=nil, @default_function=nil, @collation=nil, @comment=nil>

and on production:

irb(main):001:0> Spree::ShippingCategory.columns_hash['id']
=> #<ActiveRecord::ConnectionAdapters::PostgreSQLColumn:0x00007f6585c3f810 @name="id", @table_name="spree_shipping_categories", @sql_type_metadata=#<ActiveRecord::ConnectionAdapters::SqlTypeMetadata:0x00007f6585c3f928 @sql_type="integer", @type=:integer, @limit=nil, @precision=nil, @scale=nil>, @null=false, @default=nil, @default_function="nextval('spree_shipping_categories_id_seq'::regclass)", @collation=nil, @comment=nil>

It appears that @default_function is not being persisted with the database backup.

I also observe the same issue when using pg:copy:

heroku pg:copy production_app::DATABASE_URL DATABASE_URL -a staging_app

What did you expect to happen?

A post would be created.

What actually happened?

This error:

ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, f, null, 1, 2018-09-01 01:08:11.290753, 2018-09-01 01:08:11.290753, ed2cfe43-973f-4ae8-b929-cdc758b986ae, null).
: INSERT INTO "posts" ("blog_id", "created_at", "updated_at", "slug") VALUES ($1, $2, $3, $4) RETURNING "id"
    from (irb):3

Some information about your installation

What's your operating system? MacOS
What's the output of which development, which staging, which production? Parity has had multiple installation channels, and it's not uncommon for an old version to be somewhere else in your path.

/usr/local/bin/development
/usr/local/bin/staging
/usr/local/bin/production

If installed via Homebrew, what does brew list parity output?

/usr/local/Cellar/parity/2.4.0/bin/development
/usr/local/Cellar/parity/2.4.0/bin/production
/usr/local/Cellar/parity/2.4.0/bin/staging
/usr/local/Cellar/parity/2.4.0/lib/parity/ (5 files)
/usr/local/Cellar/parity/2.4.0/lib/parity.rb

geoffharcourt commented 6 years ago

Hi,

Since you can reproduce this using heroku pg:copy, I think this is an issue with Heroku CLI (and maybe with arguments it passes to pg_restore). Our remote environment restore command is a wrapper around Heroku CLI's pg:backups, so it doesn't do anything beyond get the backup URL and set the destination.

If you open a support ticket with Heroku and the end result is that there's some extra arguments that need to be passed to carry PG functions over, etc. in a restore and we need to include or accommodate those in our restore command, I'd be happy to review a PR that adds those flags.

Until you can successfully move this data over with Heroku CLI using either pg:backups:restore or pg:copy, we're not going to be able to fix this issue in Parity.

hhff commented 6 years ago

Thanks, @geoffharcourt !

In case anyone else stumbles across this - I realized this morning that my staging database was on Heroku's hobby-dev plan, and my production backup was blowing out it's row limit.

Not sure if the issue was with the row limit (heroku claims not to block inserts until 7 days after the violation) or with hobby-dev not importing sequencing functions, but either way, upgrading staging to hobby-basic fixed this issue 👍

thoughtbot / parity