|
|
12
|
|
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
|
|
|
> On Jan 31, 2019, at 12:14 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
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 <[hidden email]> wrote:
> On Jan 31, 2019, at 12:14 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
> On Jan 31, 2019, at 12:35 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
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 <[hidden email]> wrote:
> On Jan 31, 2019, at 12:35 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
> On Jan 31, 2019, at 12:42 PM, Pepe TD Vo < [hidden email]> wrote:
>
> how to roll back?
rollback;
You really need to set some time aside to read the docs.
|
|
|
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 <[hidden email]> wrote:
> On Jan 31, 2019, at 12:42 PM, Pepe TD Vo < [hidden email]> wrote:
>
> how to roll back?
rollback;
You really need to set some time aside to read the docs.
|
|
|
> On Jan 31, 2019, at 12:46 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
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 <[hidden email]> wrote:
> On Jan 31, 2019, at 12:46 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
> On Jan 31, 2019, at 1:12 PM, Pepe TD Vo < [hidden email]> 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
|
|
|
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 <[hidden email]> wrote:
> On Jan 31, 2019, at 1:12 PM, Pepe TD Vo < [hidden email]> 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
|
|
|
> On Jan 31, 2019, at 1:23 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
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 <[hidden email]> wrote:
> On Jan 31, 2019, at 1:23 PM, Pepe TD Vo < [hidden email]> 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.
|
|
|
> On Jan 31, 2019, at 1:35 PM, Pepe TD Vo < [hidden email]> 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 entered it, followed by the first line of output? Because there IS an earlier error, and you need to find it.
|
|
|
>>>>> "Pepe" == Pepe TD Vo < [hidden email]> 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)
|
|
|
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 <[hidden email]> wrote:
>>>>> "Pepe" == Pepe TD Vo < pepevo@...> 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)
|
|
|
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 <[hidden email]> wrote:
>>>>> "Pepe" == Pepe TD Vo < [hidden email]> 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)
|
|
|
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".
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 <[hidden email]> wrote:
>>>>> "Pepe" == Pepe TD Vo < [hidden email]> 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)
|
|
|
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 <[hidden email]> 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".
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 <[hidden email]> wrote:
>>>>> "Pepe" == Pepe TD Vo < [hidden email]> 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)
|
|
|
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 <[hidden email]> 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 <[hidden email]> 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".
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 <[hidden email]> wrote:
>>>>> "Pepe" == Pepe TD Vo < [hidden email]> 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)
|
12
|
Locked
