PostgreSQL › PostgreSQL - admin

insert aborted commands ignored

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

24 messages Options

Pepe TD Vo

insert aborted commands ignored

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

Scott Ribe-2

Re: insert aborted commands ignored

> 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.

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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:

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.

Scott Ribe-2

Re: insert aborted commands ignored

> 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.

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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.

Scott Ribe-2

Re: insert aborted commands ignored

> 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.

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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.

Scott Ribe-2

Re: insert aborted commands ignored

> 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.

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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.

Scott Ribe-2

Re: insert aborted commands ignored

> 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

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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

Scott Ribe-2

Re: insert aborted commands ignored

> 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.

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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.

Scott Ribe-2

Re: insert aborted commands ignored

> 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.

Andrew Gierth

Re: insert aborted commands ignored

In reply to this post by Pepe TD Vo

>>>>> "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)

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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)

Pepe TD Vo

Re: insert aborted commands ignored

In reply to this post by Andrew Gierth

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.

**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:

Shreeyansh dba

Re: insert aborted commands ignored

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 <[hidden email]> 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 <[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)

Pepe TD Vo

Re: insert aborted commands ignored

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.

**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".

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

On Fri, Feb 1, 2019 at 6:51 PM Pepe TD Vo <[hidden email]> 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 <[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)

Pepe TD Vo

Re: insert aborted commands ignored

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

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.

**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:

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.

**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".

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

On Fri, Feb 1, 2019 at 6:51 PM Pepe TD Vo <[hidden email]> 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 <[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)