Обсуждение: insert aborted commands ignored

Поиск
Список
Период
Сортировка

insert aborted commands ignored

От
Pepe TD Vo
Дата:
Hello expert DBAs,

I get the insert script from ora2PG and when I run the script, I get error

INSERT INTO cidrmgmt.search_results( id,scheduled_search_id,people_doc_id,receipt_number,form_number,a_number,first_name,middle_name,last_name,date_of_birth,dod,gender,country_of_birth,country_of_citizenship,country_of_residence,street,street2,city,state,zip,provice,postcal_code,country,res_street,res_city,res_state,res_zip,res_province,res_postal_code,res_country,ssn,tax_number,firm_name,service_center,last_indexed,ds_name,applicant_type,doe,citizenship,agency_code,tap_number,classification,mf_a_number, mf_first_name,mg_middle_name,mf_last_name,mf_agency_code, mf_tape_number,mf_fbi_result,mf_classification,indicator,inserted_date,updated_date,deleted_date) VALUES
(4896,92,E'7fdfcd2a-2da2-4f9f-bee3-4417a56c1a00',E'EAC1390072161','E'l130',NULL,E'JIEMEI',NULL,E'XIE',NULL,'1960-07-21 00:00:00',null,E'CHINA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,E'V','2017-1010 22:43:16',E'C3',E'Beneficiary',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,;2017-12-19 01:32:26','2018-04025 01:31:50','2018-01-11 01:33:44');

ERROR:current transaction is aborted, commands ignored until end of transaction block

I see each column has extra E' in front, is that right in postgres?  How can I prevent this issue?

thank you for your input.
v/r,
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success

Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Jan 31, 2019, at 12:14 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> ERROR:current transaction is aborted, commands ignored until end of transaction block
>
> I see each column has extra E' in front, is that right in postgres?  How can I prevent this issue?

The e should not be a problem, it's for "extended" strings, which allow some things that the SQL standard doesn't. But
'E'l130'is not legit; was that a copy/paste error. 

And you need to post the FIRST error in the logs, not the last one.

Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
that is the first error.  I get the same one over and over with 
ERROR:current transaction is aborted, commands ignored until end of transaction block from the first one until the last one for each insert.

thank you,
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 2:28 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:



> On Jan 31, 2019, at 12:14 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> ERROR:current transaction is aborted, commands ignored until end of transaction block
>
> I see each column has extra E' in front, is that right in postgres?  How can I prevent this issue?


The e should not be a problem, it's for "extended" strings, which allow some things that the SQL standard doesn't. But 'E'l130' is not legit; was that a copy/paste error.

And you need to post the FIRST error in the logs, not the last one.


Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Jan 31, 2019, at 12:35 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> that is the first error.  I get the same one over and over with
> ERROR:current transaction is aborted, commands ignored until end of transaction block from the first one until the
lastone for each insert. 

Then you had a prior error, and are now continually trying to run commands in a transaction that is aborted, because of
thatprior error, so rollback. 




Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
how to roll back?
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 2:39 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:


> On Jan 31, 2019, at 12:35 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:

>
> that is the first error.  I get the same one over and over with
> ERROR:current transaction is aborted, commands ignored until end of transaction block from the first one until the last one for each insert.


Then you had a prior error, and are now continually trying to run commands in a transaction that is aborted, because of that prior error, so rollback.





Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Jan 31, 2019, at 12:42 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
> 
> how to roll back?

rollback;

You really need to set some time aside to read the docs.




Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
I run by script and from the document show roll back with transaction_id.  I don't see the transaction_id.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 2:44 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:


> On Jan 31, 2019, at 12:42 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:

>
> how to roll back?

rollback;


You really need to set some time aside to read the docs.






Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Jan 31, 2019, at 12:46 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> ...from the document show roll back with transaction_id.  I don't see the transaction_id.

I have no idea what you're talking about here. Rollback is a command that works within the current transaction.

And of course, you need to know when the first error occurred.

Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
sir,

there's no transaction in progress when i type rollback command.  Also, all tables are emptiness right now.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 2:51 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:


> On Jan 31, 2019, at 12:46 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:

>
> ...from the document show roll back with transaction_id.  I don't see the transaction_id.


I have no idea what you're talking about here. Rollback is a command that works within the current transaction.

And of course, you need to know when the first error occurred.


Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Jan 31, 2019, at 1:12 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> there's no transaction in progress when i type rollback command.  Also, all tables are emptiness right now.

then there is an earlier error when you run the script




Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
there's no error before I run insert.sql
I exported schema from oracle using ora2pg, I imported (by runing) tables.sql, procedure.sql, sequence.sql fine.  When I execute insert.sql I get the error as I posted.  Check the tables, they are empty, no data insert. Rollback, - no transaction in process.

thank you,
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 3:16 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:


> On Jan 31, 2019, at 1:12 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:

>
> there's no transaction in progress when i type rollback command.  Also, all tables are emptiness right now.


then there is an earlier error when you run the script





Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Jan 31, 2019, at 1:23 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> When I execute insert.sql I get the error as I posted.

There MUST be an earlier error--that is what the error you have posted means. Check the output from insert.sql
carefully.



Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
here how I run the script

$ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

I got all repeat error .....
"ERROR: current transaction is aborted, commands ignored until  end of transaction block "

even view insert_cidrmgmt.txt.  All the same.  There's not no other output.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 3:26 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:


> On Jan 31, 2019, at 1:23 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:

>
> When I execute insert.sql I get the error as I posted.


There MUST be an earlier error--that is what the error you have posted means. Check the output from insert.sql carefully.




Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Jan 31, 2019, at 1:35 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt
>
> I got all repeat error .....
> "ERROR: current transaction is aborted, commands ignored until  end of transaction block "

Is it possible you're not seeing output because your scrollback buffer is limited? Can you see the command as you
enteredit, followed by the first line of output? Because there IS an earlier error, and you need to find it. 

Re: insert aborted commands ignored

От
Andrew Gierth
Дата:
>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:

 Pepe> here how I run the script
 Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

That tells psql to stop on the first error, so you'll be able to see
what the real error was.

Your .txt file does not capture the error because you redirected only
stdout, and errors go to stderr instead.

-- 
Andrew (irc:RhodiumToad)


Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
Thank you so much, I will run your command tomorrow.

v/r,

 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:


>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:

Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

That tells psql to stop on the first error, so you'll be able to see
what the real error was.

Your .txt file does not capture the error because you redirected only
stdout, and errors go to stderr instead.

--
Andrew (irc:RhodiumToad)



Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
thank you for your tip.
I ran a insert.sql again with AN_ERROR_STOP=1 and output to insert.txt, I get:
ERROR: insert or update on table "cidr_ds_roles" violates foreign key constraint "cidr_ds_roles_cidr_roles_fk1"
DETAIL: key (role_id)=(3) is not present in table "cidr_roles"

from insert.txt I get 
INSERT 0 1 (repeat for the rest of insert)

thank you.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:


>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:

Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

That tells psql to stop on the first error, so you'll be able to see
what the real error was.

Your .txt file does not capture the error because you redirected only
stdout, and errors go to stderr instead.

--
Andrew (irc:RhodiumToad)




Re: insert aborted commands ignored

От
Shreeyansh Dba
Дата:
Hi Pepe,

It look like foreign key reference by child table, so you are trying to insert values into chile table "cidr_ds_roles" that don't match with Parent table 'cidr_roles".


Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


On Fri, Feb 1, 2019 at 6:51 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
thank you for your tip.
I ran a insert.sql again with AN_ERROR_STOP=1 and output to insert.txt, I get:
ERROR: insert or update on table "cidr_ds_roles" violates foreign key constraint "cidr_ds_roles_cidr_roles_fk1"
DETAIL: key (role_id)=(3) is not present in table "cidr_roles"

from insert.txt I get 
INSERT 0 1 (repeat for the rest of insert)

thank you.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:


>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:

Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

That tells psql to stop on the first error, so you'll be able to see
what the real error was.

Your .txt file does not capture the error because you redirected only
stdout, and errors go to stderr instead.

--
Andrew (irc:RhodiumToad)




Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
it worked fine using oracle export and import to oracle unclass and then import to postgres using Amazon RDS, the problem is AWS is for unclass and currently I am doing in the high side and not using AWS.
How do I export oracle to postgres to avoid this issue?

thank you.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, February 1, 2019 8:54 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:


Hi Pepe,

It look like foreign key reference by child table, so you are trying to insert values into chile table "cidr_ds_roles" that don't match with Parent table 'cidr_roles".


Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


On Fri, Feb 1, 2019 at 6:51 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
thank you for your tip.
I ran a insert.sql again with AN_ERROR_STOP=1 and output to insert.txt, I get:
ERROR: insert or update on table "cidr_ds_roles" violates foreign key constraint "cidr_ds_roles_cidr_roles_fk1"
DETAIL: key (role_id)=(3) is not present in table "cidr_roles"

from insert.txt I get 
INSERT 0 1 (repeat for the rest of insert)

thank you.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:


>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:

Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

That tells psql to stop on the first error, so you'll be able to see
what the real error was.

Your .txt file does not capture the error because you redirected only
stdout, and errors go to stderr instead.

--
Andrew (irc:RhodiumToad)






Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
sorry, my server was out of network for couple days.

I have tried to remove all " E' " of each column and when i run the insert script.  I get another error, "value too long for type character varying(34).  How do I know which table



sorry, my server was out of network for a coupole days.  
I have tried to remove all " E' " of each column and rerun the insert script.  I get an error, "value too long for type character varying(34)."
I am unclear as how and which table this error message upon attemting?
Also, when I run another schema insert script, I get right away as same as the one above after remove 
ERROR: invalid input syntax for integer:"42P01"
CONTEXT: PL/pgSQL function cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins() line 29 at assignment.

the trigger is:
Create or replace function cidr_staging.trigger_fct_tr_stg_adjudicative_status_in() RETURNS trigger AS $BODY$
declare
v_seq bigint:=0;
v_ErrorCode bigint;
V_ErrorMsg varchar(512)'
v_Module varchar(32):= 'TR_STG_ADJUDICATIVE_STATUS_INS';
BEGIN
BEGIN
select nextval('sq_staging') into STRICT v_seq;
if NEW.mig_seg is null then
   NEW.mig_seq:=v_seq;
enf if;
if NEW.mig_filename is null then
   NEW.mig_filename :='Unknown';
end if;
exception
when others then
   v_ErrorCode := SQLSTATE'
   v_ErrorMsg := SQLERRM;
   insert into cidrmgmt.errorlog(stamp, os_user, host, module, errorcode, erromsg) values (CURRENT_TIMESTAMP, sys_context('userenv','session_user'), sys_context('userenv','host'),v_Module, v_ErrorCode, v_ErrorMsg);
END;
RETURN NEW;
end
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER tr_stg_adjudicative_status_ins BEFORE INSERT ON cidr_staging.stg_adjudicative_status FOR EACH ROW
  EXECUTE PROCEDURE cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins();

all scripts, tables/insert/procedure/triggers are inherited from ora2pg.  I have corrected the scripts to make them work for tables, triggers, functions.  All created well excepted the inserts are the issue and I couldn't find much information for those errors.  Thank you for your helps.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, February 1, 2019 9:18 AM, Pepe TD Vo <pepevo@yahoo.com> wrote:


it worked fine using oracle export and import to oracle unclass and then import to postgres using Amazon RDS, the problem is AWS is for unclass and currently I am doing in the high side and not using AWS.
How do I export oracle to postgres to avoid this issue?

thank you.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, February 1, 2019 8:54 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:


Hi Pepe,

It look like foreign key reference by child table, so you are trying to insert values into chile table "cidr_ds_roles" that don't match with Parent table 'cidr_roles".


Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


On Fri, Feb 1, 2019 at 6:51 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
thank you for your tip.
I ran a insert.sql again with AN_ERROR_STOP=1 and output to insert.txt, I get:
ERROR: insert or update on table "cidr_ds_roles" violates foreign key constraint "cidr_ds_roles_cidr_roles_fk1"
DETAIL: key (role_id)=(3) is not present in table "cidr_roles"

from insert.txt I get 
INSERT 0 1 (repeat for the rest of insert)

thank you.
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, January 31, 2019 4:09 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:


>>>>> "Pepe" == Pepe TD Vo <pepevo@yahoo.com> writes:

Pepe> here how I run the script
Pepe> $ psql -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

psql -v ON_ERROR_STOP=1 -U postgres -d CIDR < insert.sql > insert_cidrmgmt.txt

That tells psql to stop on the first error, so you'll be able to see
what the real error was.

Your .txt file does not capture the error because you redirected only
stdout, and errors go to stderr instead.

--
Andrew (irc:RhodiumToad)








Re: insert aborted commands ignored

От
Scott Ribe
Дата:
> On Feb 6, 2019, at 8:05 AM, Pepe TD Vo <pepevo@yahoo.com> wrote:
> 
> I have tried to remove all " E' " of each column

Why???



Re: insert aborted commands ignored

От
"David G. Johnston"
Дата:
On Wed, Feb 6, 2019 at 8:05 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
>  get another error, "value too long for type character varying(34).  How do I know which table

How many tables in your schema are defined with a column having a type
of "character varying(34)"?

To narrow down if >1 you'd need to trace the load script and see at
what point it is failing; or change half of them to "text", try again,
change some back to varchar(34), try again repeat until only one of
them is "text" and that is your culprit.

Or just get rid of "varchar(n)" columns, make them all text and, for
those were you really want to keep length limits, add explicit
constraints.

There have been discussions somewhat recently to make the error
message itself more helpful but I don't believe anyone is actively
working on it.  Its better, IMO, to avoid using "varchar(n)" in your
schema anyway.  More precise check constraints are a better option not
the least of which is because they can be named and self-identify with
the table to which they are attached.

David J.


Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
Thank you sir,

I have tried one by one to show create table back and found a few fields with character varying(34) and I don't know exactly which one failed.   The data type was right from Oracle and count the characters on the insert script was fine.  The target column names listed in order but the values supplied by the values are associated with the explicit or implicit column list left to right  and I need to check one by one column and see what they are.  The problems are so many and the more I query, psql hung and I needed to ctrl C to get out from psql and get back in.

I am checking one by one table insert row now.  I need to pull all insert of each table out first and see if insert ok and so far it's ok.  With your information I will check back and increase the values (n) and see the insert work or not, since the insert script was 20Gb and it will stop running as soon as the error kick in.  Even without setting "-v ON_ERROR_STOP=1 ".  The 1st 200 insert lines are ok from the psql prompt but stop insert if I running psql -U postgres -d CIDR < insert_cidrdba.sql > insert_cidrdba.txt.  I am inserting one by one from psql now.  Hope to find where the error occurs.

thank you for your input again.

v/r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Wednesday, February 6, 2019 10:18 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:


On Wed, Feb 6, 2019 at 8:05 AM Pepe TD Vo <pepevo@yahoo.com> wrote:

>  get another error, "value too long for type character varying(34).  How do I know which table


How many tables in your schema are defined with a column having a type
of "character varying(34)"?

To narrow down if >1 you'd need to trace the load script and see at
what point it is failing; or change half of them to "text", try again,
change some back to varchar(34), try again repeat until only one of
them is "text" and that is your culprit.

Or just get rid of "varchar(n)" columns, make them all text and, for
those were you really want to keep length limits, add explicit
constraints.

There have been discussions somewhat recently to make the error
message itself more helpful but I don't believe anyone is actively
working on it.  Its better, IMO, to avoid using "varchar(n)" in your
schema anyway.  More precise check constraints are a better option not
the least of which is because they can be named and self-identify with
the table to which they are attached.

David J.



Re: insert aborted commands ignored

От
Pepe TD Vo
Дата:
Mr. Johnston,

for the error, "value too long for type character varying(34)thank you for your information.  I resolved the problem. 

v.r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Wednesday, February 6, 2019 2:59 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:


Thank you sir,

I have tried one by one to show create table back and found a few fields with character varying(34) and I don't know exactly which one failed.   The data type was right from Oracle and count the characters on the insert script was fine.  The target column names listed in order but the values supplied by the values are associated with the explicit or implicit column list left to right  and I need to check one by one column and see what they are.  The problems are so many and the more I query, psql hung and I needed to ctrl C to get out from psql and get back in.

I am checking one by one table insert row now.  I need to pull all insert of each table out first and see if insert ok and so far it's ok.  With your information I will check back and increase the values (n) and see the insert work or not, since the insert script was 20Gb and it will stop running as soon as the error kick in.  Even without setting "-v ON_ERROR_STOP=1 ".  The 1st 200 insert lines are ok from the psql prompt but stop insert if I running psql -U postgres -d CIDR < insert_cidrdba.sql > insert_cidrdba.txt.  I am inserting one by one from psql now.  Hope to find where the error occurs.

thank you for your input again.

v/r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Wednesday, February 6, 2019 10:18 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:


On Wed, Feb 6, 2019 at 8:05 AM Pepe TD Vo <pepevo@yahoo.com> wrote:

>  get another error, "value too long for type character varying(34).  How do I know which table


How many tables in your schema are defined with a column having a type
of "character varying(34)"?

To narrow down if >1 you'd need to trace the load script and see at
what point it is failing; or change half of them to "text", try again,
change some back to varchar(34), try again repeat until only one of
them is "text" and that is your culprit.

Or just get rid of "varchar(n)" columns, make them all text and, for
those were you really want to keep length limits, add explicit
constraints.

There have been discussions somewhat recently to make the error
message itself more helpful but I don't believe anyone is actively
working on it.  Its better, IMO, to avoid using "varchar(n)" in your
schema anyway.  More precise check constraints are a better option not
the least of which is because they can be named and self-identify with
the table to which they are attached.

David J.