Re: Must re-connect to see tables
Blake McBride wrote: > data.sql is an untouched dump of a database from PostgreSQL 9.5.12. > > The import seems to have worked. I just need another \c after the \i. An effect of reconnecting is to reset the search_path to what it was before including your dump file. You're most certainly affected by the search_path-related changes in pg_dump done in the latest versions following CVE-2018-1058 See https://www.postgresql.org/support/security/ or https://www.postgresql.org/about/news/1834/ Manually issuing RESET search_path; after \i data.sql should get "public" back in the search_path without reconnecting and after that \dt would work as usual. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
Re: Must re-connect to see tables
On Tue, Mar 27, 2018 at 6:48 AM, rob stone wrote: > Hello Blake, > > On Tue, 2018-03-27 at 06:23 -0500, Blake McBride wrote: > > I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit > > Linux box. > > > > On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride > > wrote: > > > Hi, > > > > > > I have been using PostgreSQL for many years but all of a sudden a > > > db load script I've been using no longer works. What id does > > > (essentially) is: > > > > > > psql postgres postgres > > > drop database test; > > > create database test; > > > \c test > > > \i data.sql > > > \dt > > > \q > > > > > > > > > I tested this with a small data.sql and it worked fine. However, > > > when I use a real (much larger) data.sql then \dt replies "No > > > relations" ! I found this if I stick an extra '\c test' after the > > > '\i data.sql' it works. > > > > > > The data.sql is a PostgreSQL dump from another database. When the > > > '\i data.sql' is done, the prompt still shows 'test#' so it's not > > > like I've changed databases. > > > > > > I only have one schema, 'public'. > > > > > > Can't figure out why a simple example works but a large file > > > requires an extra \c. > > > > > > Sure appreciate any help. > > > > > > Blake McBride > > > > > > > > > > > > 1) Where does your e-mail subject line "Must re-connect to see tables" > relate to the import? > I do the import and then \dt return no relations. I must *re-connect* via \c in order to see the tables. (According to the prompt, I was still connected to the database.) > > 2) Does the larger data.sql file have a "create database" line or any > other construct at the beginning of the file that would cause it to NOT > insert rows into database test? > data.sql is an untouched dump of a database from PostgreSQL 9.5.12. The import seems to have worked. I just need another \c after the \i. Thanks! Blake > > Cheers, > Rob >
Re: Must re-connect to see tables
Hello Blake, On Tue, 2018-03-27 at 06:23 -0500, Blake McBride wrote: > I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit > Linux box. > > On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride > wrote: > > Hi, > > > > I have been using PostgreSQL for many years but all of a sudden a > > db load script I've been using no longer works. What id does > > (essentially) is: > > > > psql postgres postgres > > drop database test; > > create database test; > > \c test > > \i data.sql > > \dt > > \q > > > > > > I tested this with a small data.sql and it worked fine. However, > > when I use a real (much larger) data.sql then \dt replies "No > > relations" ! I found this if I stick an extra '\c test' after the > > '\i data.sql' it works. > > > > The data.sql is a PostgreSQL dump from another database. When the > > '\i data.sql' is done, the prompt still shows 'test#' so it's not > > like I've changed databases. > > > > I only have one schema, 'public'. > > > > Can't figure out why a simple example works but a large file > > requires an extra \c. > > > > Sure appreciate any help. > > > > Blake McBride > > > > 1) Where does your e-mail subject line "Must re-connect to see tables" relate to the import? 2) Does the larger data.sql file have a "create database" line or any other construct at the beginning of the file that would cause it to NOT insert rows into database test? Cheers, Rob
Re: Must re-connect to see tables
If it worked in 9.3.22 and now it's failed in 9.3.22, then I'd look to see if something has changed in data.sql. On 03/27/2018 06:22 AM, Blake McBride wrote: Hi, I have been using PostgreSQL for many years but all of a sudden a db load script I've been using no longer works. What id does (essentially) is: psql postgres postgres drop database test; create database test; \c test \i data.sql \dt \q I tested this with a small data.sql and it worked fine. However, when I use a real (much larger) data.sql then \dt replies "No relations" ! I found this if I stick an extra '\c test' after the '\i data.sql' it works. The data.sql is a PostgreSQL dump from another database. When the '\i data.sql' is done, the prompt still shows 'test#' so it's not like I've changed databases. I only have one schema, 'public'. Can't figure out why a simple example works but a large file requires an extra \c. Sure appreciate any help. Blake McBride -- Angular momentum makes the world go 'round.
Re: Must re-connect to see tables
I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit Linux box. On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride wrote: > Hi, > > I have been using PostgreSQL for many years but all of a sudden a db load > script I've been using no longer works. What id does (essentially) is: > > psql postgres postgres > drop database test; > create database test; > \c test > \i data.sql > \dt > \q > > > I tested this with a small data.sql and it worked fine. However, when I > use a real (much larger) data.sql then \dt replies "No relations" ! I > found this if I stick an extra '\c test' after the '\i data.sql' it works. > > The data.sql is a PostgreSQL dump from another database. When the '\i > data.sql' is done, the prompt still shows 'test#' so it's not like I've > changed databases. > > I only have one schema, 'public'. > > Can't figure out why a simple example works but a large file requires an > extra \c. > > Sure appreciate any help. > > Blake McBride > >
Must re-connect to see tables
Hi, I have been using PostgreSQL for many years but all of a sudden a db load script I've been using no longer works. What id does (essentially) is: psql postgres postgres drop database test; create database test; \c test \i data.sql \dt \q I tested this with a small data.sql and it worked fine. However, when I use a real (much larger) data.sql then \dt replies "No relations" ! I found this if I stick an extra '\c test' after the '\i data.sql' it works. The data.sql is a PostgreSQL dump from another database. When the '\i data.sql' is done, the prompt still shows 'test#' so it's not like I've changed databases. I only have one schema, 'public'. Can't figure out why a simple example works but a large file requires an extra \c. Sure appreciate any help. Blake McBride