Thread: 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
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
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 58031687 | 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 youmust 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 ManifestVoting 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.ddlI 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.ddlI 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:
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:
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
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:
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
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 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
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:
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
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:
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