psql show me the : and ask user input, when running one sql file
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
Re: psql show me the : and ask user input, when running one sql file
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, 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? > > > > > Thanks. > > Arden > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: psql show me the : and ask user input, when running one sql file
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,
wrote:
> 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.kla...@aklaver.com
>
Re: psql show me the : and ask user input, when running one sql file
Does that RETURNING need any user input? On Sun., Apr. 5, 2020, 1:10 p.m. 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: > > ... > CREATE TABLE > ALTER TABLE > INSERT 0 4333 > INSERT 0 9326 > INSERT 0 13 > ALTER TABLE > ALTER TABLE > ... > > > > > > > > >> Thanks. > >> Arden > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: psql show me the : and ask user input, when running one sql file
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, 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, > > mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: psql show me the : and ask user input, when running one sql file
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 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 >
