Manageiq: "WARNING: out of shared memory" when running test:vmdb:setup with PARALLEL and PG 9.6

[himdel]

Trying to run PARALLEL=1 bundle exec bin/rake test:vmdb:setup (with no existing vmdb_test* databases), I get..

** Confirming rails environment does not connect to the database
Dropped database 'vmdb_test6'
Dropped database 'vmdb_test'
Dropped database 'vmdb_test8'
Dropped database 'vmdb_test3'
Dropped database 'vmdb_test5'
Dropped database 'vmdb_test2'
Dropped database 'vmdb_test4'
Dropped database 'vmdb_test7'
Created database 'vmdb_test3'
Created database 'vmdb_test5'
Created database 'vmdb_test6'
Created database 'vmdb_test'
Created database 'vmdb_test8'
Created database 'vmdb_test7'
Created database 'vmdb_test2'
Created database 'vmdb_test4'
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
.WARNING:  out of shared memory
WARNING:  out of shared memory
....WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
.WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
..WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
LINE 1:     SELECT relname, array_agg(tgname) AS triggers
                            ^
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
WARNING:  out of shared memory
LINE 1: SELECT pg_advisory_unlock(7246237429952223045)
               ^
WARNING:  out of shared memory

(The LINE part keeps changing.. also seen..)

LINE 1: INSERT INTO "schema_migrations" ("version") VALUES ($1) RETU...
LINE 6:                            strpos(split_part(pg_get_expr(def...

All 8 databases get created. But apparently the ar_internal_metadata table is sometimes empty, and when that happens, you get a ActiveRecord::NoEnvironmentInSchemaError running test:vmdb:setup and that database gets skipped. Dropping it manually fixes that though.

@jrafanie

I'm using PostgreSQL 9.6.1, possibly related?

[chrisarcand]

@himdel Definitely related. Still looking in to this to see if it's a fixable issue programmatically but I recommend upping max_locks_per_transaction to 128 (default is 64) in your postgresql.conf (that's in /usr/local/var/postgres if you're on MacOS), which should fix the issue.

[himdel]

@chrisarcand aah, thanks! Upping max_locks_per_transaction works for me too :) (and that's /etc/postgresql/9.6/main/postgresql.conf - Debian ;)).

Any idea why the difference? The default has always been 64 it seems..

[jrafanie]

Wow, thanks for looking into this @chrisarcand @himdel, very interesting

[himdel]

So.. do we want to track 9.6 problems? Apart from this one, I also have 2 failing specs..

rspec ./spec/models/vmdb_database_connection_spec.rb:133 # VmdbDatabaseConnection.log_statistics normal
got: "MIQ(VmdbDatabaseConnection.log_statistics) Unable to log stats, 'undefined local variable or method `waiting' for #<VmdbDatabaseConnection:0x0055798d7efb10>\n"

rspec ./spec/models/vmdb_database_spec.rb:75 # VmdbDatabase.report_client_connections will return an array of hashes and verify hash keys for client connections query
   ActiveRecord::StatementInvalid:
       PG::UndefinedColumn: ERROR:  column "waiting" does not exist
       LINE 4:                        , waiting       AS is_waiting

I can create an issue.. :)

EDIT: https://github.com/ManageIQ/manageiq/issues/13200

[chrisarcand]

Any idea why the difference? The default has always been 64 it seems..

Yeah that's the part I don't understand yet 😕 The only shot in the dark idea I have is that with the new parallel queries feature, some internal queries add to the number of table writes at the same time and finally push it over the edge of the current 64 setting.

[chrisarcand]

I can create an issue.. :)

@himdel Yeah go ahead and make a separate one, let's leave this issue specific to this problem.

[miq-bot]

This issue has been automatically marked as stale because it has not been updated for at least 6 months.

If you can still reproduce this issue on the current release or on master, please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions!

[carbonin]

This one feels like a local config issue and probably doesn't have a solution within our application.

I'm not seeing this in PG 10. How do you want to handle this @himdel ? I'd be fine closing it ...

[himdel]

@carbonin You're right .. for 9.6, I had to up the value in the config, for 11, I'm running the default config again.

So, I'm fine with closing, except: our default config for 9.5 does not override max_locks_per_transaction, and 64 is the default value.

So if we ever build an appliance with 9.6, the config change would still be needed, right?

But if the plan is to skip ahead to 10 or 11 (or if the problem can't happen on our appliances for some reason), I'm fine with closing too :)

[carbonin]

Based on the PG source it looks like the default was last changed 14 years ago (https://github.com/postgres/postgres/blame/REL_10_STABLE/src/backend/utils/misc/postgresql.conf.sample#L601). So I bet it's an issue with whatever distribution was being installed rather than the PG version.

I'll go ahead and close this.