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

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

On Tue, Mar 27, 2018 at 6:48 AM, rob stone <floriparob@gmail.com> 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 <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?

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

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