psql show me the : and ask user input, when running one sql file

Arden

Your first argument is a JDBC connection string (see here
https://jdbc.postgresql.org/documentation/80/connect.html). To provide
the details when using the command line psql command, use the -h, -p and
-U parameters (https://www.postgresql.org/docs/12/app-psql.html)

Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

On 05/04/2020 13:50, arden liu wrote:

I am using psql to run this sql
file(https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl)
here is my command:
/usr/bin/psql postgresql://db_user:dbpassword@localhost:5432/my_db -f
/tmp/xbrlPublicPostgresDB.ddl
I do not know why it show me the : , which is asking me to input
something.
Can someone help me?
Thanks.
Arden

Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/

Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.

On Sun, Apr 5, 2020 at 5:50 AM arden liu <ardenbook@gmail.com> wrote:

I am using psql to run this sql file(
https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl
)
here is my command:
/usr/bin/psql postgresql://db_user:dbpassword@localhost:5432/my_db -f
/tmp/xbrlPublicPostgresDB.ddl
I do not know why it show me the : , which is asking me to input
something.
Can someone help me?

If you can manually execute incremental portions of the file until the
final statement introduces the problem I'll be happy to try and explain
what may be wrong with that statement or portion of the file.

Removing stuff instead of just commenting it out is recommended.

David J.

On Apr 5, 2020, at 8:24 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:



On Sun, Apr 5, 2020 at 5:50 AM arden liu <ardenbook@gmail.com> wrote:
I am using psql to run this sql file(https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl)
here is my command:
/usr/bin/psql postgresql://db_user:dbpassword@localhost:5432/my_db -f /tmp/xbrlPublicPostgresDB.ddl
I do not know why it show me the : , which is asking me to input something.
Can someone help me?

If you can manually execute incremental portions of the file until the final statement introduces the problem I'll be happy to try and explain what may be wrong with that statement or portion of the file.

Removing stuff instead of just commenting it out is recommended.

David J.

Or change your semi-colons into \p\g and perhaps we’ll see the lasting thing run.

On Sun, Apr 5, 2020 at 7:47 AM Tim Clarke <tim.clarke@minerva.info> wrote:

Your first argument is a JDBC connection string (see here
https://jdbc.postgresql.org/documentation/80/connect.html). To provide
the details when using the command line psql command, use the -h, -p and
-U parameters (https://www.postgresql.org/docs/12/app-psql.html)

libpq understands URI connection strings:

https://www.postgresql.org/docs/12/libpq-connect.html#LIBPQ-CONNSTRING

David J.

On 4/5/20 5:50 AM, arden liu wrote:

I am using psql to run this sql
file(https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl)
here is my command:
/usr/bin/psql postgresql://db_user:dbpassword@localhost:5432/my_db -f
/tmp/xbrlPublicPostgresDB.ddl
I do not know why it show me the : , which is asking me to input something.

You are going to need to provide more context about where the : is
showing up.

You will need to show the actual complete line that shows up?

Also it would be helpful to see anything directly preceding the line.

I would also suggest looking at the Postgres log(assuming you have
log_statement set to at least mod) to see where in the sequence of
commands you run into the 'input'.

Can someone help me?
Thanks.
Arden

--
Adrian Klaver
adrian.klaver@aklaver.com

On 4/5/20 5:50 AM, arden liu wrote:

I am using psql to run this sql
file(https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl)
here is my command:
/usr/bin/psql postgresql://db_user:dbpassword@localhost:5432/my_db -f
/tmp/xbrlPublicPostgresDB.ddl
I do not know why it show me the : , which is asking me to input something.
Can someone help me?

Well I ran the file(basically a modified dump file) and what I found is it:

1) Hung on:

INSERT INTO industry (industry_id, industry_classification,
industry_code, industry_description, depth, parent_id) VALUES
...
RETURNING industry_id;

INSERT 0 4333

and

INSERT INTO industry_level (industry_level_id, industry_classification,
ancestor_id, ancestor_code, ancestor_depth, descendant_id,
descendant_code, descendant_depth) VALUES
...
RETURNING industry_level_id;

INSERT 0 9326

2) It did not hang on:

INSERT INTO industry_structure (industry_structure_id,
industry_classification, depth, level_name) VALUES
...
RETURNING industry_structure_id;

INSERT 0 13

3) For the hung cases all the INSERTS completed, I just needed to hit
any key to get the next INSERT statement to kick off.

4) I don't see anything wrong the statements, so I am wondering if it is
a shell issue?

Thanks.
Arden

--
Adrian Klaver
adrian.klaver@aklaver.com

On 4/5/20 9:46 AM, Adrian Klaver wrote:

On 4/5/20 5:50 AM, arden liu wrote:

I am using psql to run this sql
file(https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl)

here is my command:
/usr/bin/psql postgresql://db_user:dbpassword@localhost:5432/my_db -f
/tmp/xbrlPublicPostgresDB.ddl
I do not know why it show me the : , which is asking me to input
something.
Can someone help me?

Well I ran the file(basically a modified dump file) and what I found is it:

1) Hung on:

 INSERT INTO industry (industry_id, industry_classification,
industry_code, industry_description, depth, parent_id) VALUES
...
RETURNING industry_id;

INSERT 0 4333

and

INSERT INTO industry_level (industry_level_id, industry_classification,
ancestor_id, ancestor_code, ancestor_depth, descendant_id,
descendant_code, descendant_depth) VALUES
...
RETURNING industry_level_id;

INSERT 0 9326

2) It did not hang on:

INSERT INTO industry_structure (industry_structure_id,
industry_classification, depth, level_name) VALUES
...
RETURNING industry_structure_id;

INSERT 0 13

3) For the hung cases all the INSERTS completed, I just needed to hit
any key to get the next INSERT statement to kick off.

4) I don't see anything wrong the statements, so I am wondering if it is
a shell issue?

Hit Enter too soon.

5) All the other objects in the file where created.

Thanks.
Arden

--
Adrian Klaver
adrian.klaver@aklaver.com

On 4/5/20 9:46 AM, Adrian Klaver wrote:

On 4/5/20 5:50 AM, arden liu wrote:

4) I don't see anything wrong the statements, so I am wondering if it is
a shell issue?

Seems to be. I removed the RETURNING *_id from the INSERT statements and
the file ran without interruption:

...
CREATE TABLE
ALTER TABLE
INSERT 0 4333
INSERT 0 9326
INSERT 0 13
ALTER TABLE
ALTER TABLE
...

Thanks.
Arden

--
Adrian Klaver
adrian.klaver@aklaver.com

When I run any smaller SQL, psql does not ask any keyboard Input. I don't
know what causes this input

On Sun., Apr. 5, 2020, 12:46 p.m. Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

Hi Adrian,
When I use the following java to run the same command, which does not ask
me any input.  Maybe bash and java launch another process differently.
-------------------------------------
String command = "/usr/bin/psql postgresql://" +userName + ":" + password
+"@"+ host + ":" + port + "/xbrlam -f /tmp/xbrlPublicPostgresDB.ddl";
            try {
Process process = Runtime.getRuntime().exec(command);
StreamGobbler streamGobbler = new StreamGobbler(process.getInputStream(),
System.out::println);
Executors.newSingleThreadExecutor().submit(streamGobbler);

int exitValue = process.waitFor();
if (exitValue == 0) {
System.out.println("XBRL-US db is ready.");
} else {
throw new RuntimeException("XBRL-US db wrong");
}
} catch (Exception e) {
throw new RuntimeException(e);
}

On Sun., Apr. 5, 2020, 12:47 p.m. Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

Does that RETURNING need any user input?

On Sun., Apr. 5, 2020, 1:10 p.m. Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

On 4/5/20 10:51 AM, arden liu wrote:

Does that RETURNING need any user input?

No. I just think actually returning those thousands of values is
stalling the shell. It also not really necessary for the loading as
'INSERT 0 4333' shows you what you need to know. Unless you have super
vision and recall you are not going to track those values anyway. You
can verify in the table itself.

I would see if you could get the project to output the file using COPY
to load the tables instead of INSERT anyway. The file is pretty much
Postgres specific anyway as it doing things like:

1) DROP SCHEMA public CASCADE; create SCHEMA public;

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
-- HF - must have conforming strings on for Postgres interface to work,
as it will include Windows paths sometimes
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

2) ALTER TYPE public.ancestry OWNER TO postgres;

On Sun., Apr. 5, 2020, 1:10 p.m. Adrian Klaver,
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 4/5/20 9:46 AM, Adrian Klaver wrote:

On 4/5/20 5:50 AM, arden liu wrote:

4) I don't see anything wrong the statements, so I am wondering

if it is

a shell issue?

Seems to be. I removed the RETURNING *_id from the INSERT statements
and
the file ran without interruption:

...
CREATE TABLE
ALTER TABLE
INSERT 0 4333
INSERT 0 9326
INSERT 0 13
ALTER TABLE
ALTER TABLE
...

Thanks.
Arden

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,
I also consider it's related to Shell . Because when I run it from Java , I
don't have this input request.

Let me check some configuration of bash and try again.

Thanks a lot.
Arden

On Sun., Apr. 5, 2020, 2:09 p.m. Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

Adrian Klaver wrote:

On 4/5/20 9:46 AM, Adrian Klaver wrote:

On 4/5/20 5:50 AM, arden liu wrote:

4) I don't see anything wrong the statements, so I am wondering if it is
a shell issue?

Seems to be. I removed the RETURNING *_id from the INSERT statements and
the file ran without interruption:

Presumably these results are being displayer with a pager, and it's
the pager that is asking for keyboard input.
You may add -P pager=off to psql options to suppress this,
or remove permanently the RETURNING clauses that seem pointless
in that context.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Hi Daniel,
"-P pager=off" works!!!! I think you found the root cause.

Thanks for your help.
Arden

On Sun, Apr 5, 2020 at 2:32 PM Daniel Verite <daniel@manitou-mail.org>
wrote: