Обсуждение: 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.

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 <blake@mcbride.name>
> 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

    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