{"action":"created","issue":{"url":"https://api.github.com/repos/kraih/mojo-pg/issues/22","repository_url":"https://api.github.com/repos/kraih/mojo-pg","labels_url":"https://api.github.com/repos/kraih/mojo-pg/issues/22/labels{/name}","comments_url":"https://api.github.com/repos/kraih/mojo-pg/issues/22/comments","events_url":"https://api.github.com/repos/kraih/mojo-pg/issues/22/events","html_url":"https://github.com/kraih/mojo-pg/issues/22","id":160258004,"number":22,"title":"Previously cached connections do not use a new search_path after change","user":{"login":"jberger","id":735765,"avatar_url":"https://avatars.githubusercontent.com/u/735765?v=3","gravatar_id":"","url":"https://api.github.com/users/jberger","html_url":"https://github.com/jberger","followers_url":"https://api.github.com/users/jberger/followers","following_url":"https://api.github.com/users/jberger/following{/other_user}","gists_url":"https://api.github.com/users/jberger/gists{/gist_id}","starred_url":"https://api.github.com/users/jberger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jberger/subscriptions","organizations_url":"https://api.github.com/users/jberger/orgs","repos_url":"https://api.github.com/users/jberger/repos","events_url":"https://api.github.com/users/jberger/events{/privacy}","received_events_url":"https://api.github.com/users/jberger/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/kraih/mojo-pg/labels/needs%20feedback","name":"needs feedback","color":"eeeeee"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-06-14T18:58:45Z","updated_at":"2016-06-14T19:13:45Z","closed_at":null,"body":"### Steps to reproduce the behavior\r\nWhen following the example of using search_path to implement isolation (seen here http://mojolicious.org/perldoc/Mojo/Pg#search_path) I ran into the problem of the search path not being set correctly for the connection that was already used to create the schema. I first demonstrated the problem via this test (when I realized that replacing the Mojo::Pg object would \"fix\" the problem).\r\n\r\n```perl\r\n\r\nuse Mojo::Base -strict;\r\n\r\nuse Mojo::Pg;\r\nuse Test::More;\r\n\r\n# NB each run of this test has to be on a fresh database\r\n\r\nmy $url = 'postgresql://...';\r\nmy $pg = Mojo::Pg->new($url)->auto_migrate(0);\r\n$pg->db->query('drop schema if exists my_test cascade');\r\n$pg->db->query('create schema my_test'); \r\n# uncomment the following line for the test to pass\r\n#$pg = Mojo::Pg->new($url)->auto_migrate(0);\r\n$pg->search_path(['my_test']);\r\n$pg->migrations->from_data->migrate(0)->migrate;\r\n\r\nmy $count = eval { $pg->db->query('select count(*) from my_test.my_table')->array->[0] };\r\nis $count, 1, 'correct number of items';\r\n$count = undef;\r\n$count = eval { $pg->db->query('select count(*) from public.my_table')->array->[0] };\r\nok ! defined($count), 'items not in public schema';\r\n\r\ndone_testing;\r\n\r\n\r\n__DATA__\r\n\r\n@@ migrations\r\n\r\n-- 1 up\r\n\r\nCREATE TABLE my_table (id BIGSERIAL PRIMARY KEY);\r\nINSERT INTO my_table DEFAULT VALUES;\r\n\r\n-- 1 down\r\n\r\nDROP TABLE IF EXISTS my_first_table;\r\n```\r\n\r\nHowever as I began to realize that this was really about connection caching I realized that the following patten would work reliably and without the confusion. Note the setting of `max_connections` which prevents connection caching until after the schema is created and the search path would go into effect.\r\n\r\n```perl\r\nuse Mojo::Base -strict;\r\n\r\nuse Mojo::Pg;\r\nuse Test::More;\r\n\r\nmy $url = 'postgresql://hubble:hubble@localhost/hubble';\r\nmy $pg = Mojo::Pg->new($url)->auto_migrate(0)->max_connections(0);\r\n$pg->db->query('drop schema if exists my_test cascade');\r\n$pg->db->query('create schema my_test'); \r\n$pg->max_connections(5)->search_path(['my_test']);\r\n$pg->migrations->from_data->migrate(0)->migrate;\r\n\r\nmy $count = eval { $pg->db->query('select count(*) from my_test.my_table')->array->[0] };\r\nis $count, 1, 'correct number of items';\r\n$count = undef;\r\n$count = eval { $pg->db->query('select count(*) from public.my_table')->array->[0] };\r\nok ! defined($count), 'items not in public schema';\r\n\r\ndone_testing;\r\n\r\n\r\n__DATA__\r\n\r\n@@ migrations\r\n\r\n-- 1 up\r\n\r\nCREATE TABLE my_table (id BIGSERIAL PRIMARY KEY);\r\nINSERT INTO my_table DEFAULT VALUES;\r\n\r\n-- 1 down\r\n\r\nDROP TABLE IF EXISTS my_first_table;\r\n```\r\n\r\n### Expected behavior\r\nThe example would be expected to create new objects in the current database schema. I see three possible solutions here. \r\n\r\n1. The easiest would be to add the `->max_connections(0)` and later reset to the documentation in that example. It works without any code changes but it isn't very pretty.\r\n2. The hardest (but perhaps the most expected) would be to flush the connection cache whenever `search_path` is set. This is the least cognitive load on the user but is the most intrusive solution.\r\n3. A possibly halfway house is to add a method which will flush the current connections. Calling this method could be added to the noted example."},"comment":{"url":"https://api.github.com/repos/kraih/mojo-pg/issues/comments/225986297","html_url":"https://github.com/kraih/mojo-pg/issues/22#issuecomment-225986297","issue_url":"https://api.github.com/repos/kraih/mojo-pg/issues/22","id":225986297,"user":{"login":"kraih","id":30094,"avatar_url":"https://avatars.githubusercontent.com/u/30094?v=3","gravatar_id":"","url":"https://api.github.com/users/kraih","html_url":"https://github.com/kraih","followers_url":"https://api.github.com/users/kraih/followers","following_url":"https://api.github.com/users/kraih/following{/other_user}","gists_url":"https://api.github.com/users/kraih/gists{/gist_id}","starred_url":"https://api.github.com/users/kraih/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kraih/subscriptions","organizations_url":"https://api.github.com/users/kraih/orgs","repos_url":"https://api.github.com/users/kraih/repos","events_url":"https://api.github.com/users/kraih/events{/privacy}","received_events_url":"https://api.github.com/users/kraih/received_events","type":"User","site_admin":false},"created_at":"2016-06-14T19:13:45Z","updated_at":"2016-06-14T19:13:45Z","body":"Not sure what to do here, for now i've just fixed the error in the docs. https://github.com/kraih/mojo-pg/commit/99cccdd7c2a7e659d81fd62fd361735aaaf63385"}}
{"actor":{"display_login":"kraih"}}