CODEWITHSUNDEEP
sqlpostgresqlsql-scripts
NAC
NAC

Reputation: 135

Logging SQL script errors

I have a SQL script for postgres file with following command.

COPY product_master(productId, productName) FROM 'product.txt' DELIMITERS ',' CSV;

I want to handle errors of this command (log the error)

example

ERROR: duplicate key value violates.

Does the COPY command return any value ? If no, then how to log the output of the shell script?

Upvotes: 1

Views: 1844

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

You can log any and all messages (error, warning, ..) with a plethora of additional information in the database log file. This is standard behavior. Of course your database cluster has to be configured to do so. Read the fine manual here.

Dpending on your client you should also be able to get error messages as direct answer from the database server. Note that errors are reported on a different stream than data output. Like stout and stderr in the shell.

From the shell you would probably call psql -f to execute a script. See what happens in this demo:

Create a dummy SQL script in the shell:

vim test.sql

Put something like this into it:

CREATE temp table x (a int primary key, b int);
insert into x values (1,2),(3,4);
COPY x TO '/var/lib/postgres/dev/test.out';
COPY x FROM '/var/lib/postgres/dev/test.out';

Execute it:

psql mydb -f test.sql

Output depends on various settings like client_min_messages:

psql:test.sql:2: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for table "x"
CREATE TABLE
INSERT 0 2
psql:test.sql:4: ERROR:  duplicate key value violates unique constraint "x_pkey"
KONTEXT:  COPY x, line 1: "1    2"

Since I have configured log_statement = all (among others) my server log reads:

2011-11-15 22:36:23 CET postgres LOG:  statement: CREATE temp table x (a int primary key, b int);
2011-11-15 22:36:23 CET postgres LOG:  statement: insert into x values (1,2),(3,4);
2011-11-15 22:36:23 CET postgres LOG:  statement: COPY x FROM '/var/lib/postgres/dev/test.out';
2011-11-15 22:36:23 CET postgres ERROR:  duplicate key value violates unique constraint "x_pkey"
2011-11-15 22:36:23 CET postgres CONTEXT:  COPY x, line 1: "1  2"
2011-11-15 22:36:23 CET postgres STATEMENT:  COPY x FROM '/var/lib/postgres/dev/test.out';

I would not use log_statement = all on a productive server. That produces huge log files.

Upvotes: 2

Related Questions