Re: [SQL] handling duplicate row exception

[Filip Rembiałkowski]

Hi

There is no IGNORE_DUP_KEY equivalent in PostgreSQL.

If you are 100% sure that you want to ignore unique key violations, you can
wrap your INSERT code in PL/PgSQL block and handle the exception yourself.

I mean:

DO $$
BEGIN
 INSERT INTO foo (bar,baz) SELECT 42, 666;
EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'row skipped';
END;
$$


BTW - which version of PostgreSQL are you using?

Filip



2011/9/21 Amar Dhole 

> Hi All,
>
> I have a requirement where my application tries to enter the duplicate row
> in table using batchexceute code. And batch exception is thrown we checked
> error code and skip it but after this exception all my next update/insert
> gets error out with following exception
>
> "Database error. SQL state 25P02. Database specific error code (if any) was
> 0. Database error message (if any) was: org.postgresql.util.PSQLException:
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block.:
>
>
>  Is there any way to proceed ahead like in sql server we have options while
> creating table IGNORE_DUP_KEY = ON if this is set warning is generated
> instead of Exception so the other insert/update can proceed ahead.
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] select xpath ...

[Brian Sherwood]

Boris,

Can you send me your final solution?
I am trying to do something similar and I think I am stuck at the namespace.

Thanks


On Mon, Sep 19, 2011 at 11:49 AM, boris  wrote:
> On 09/19/2011 10:49 AM, Rob Sargent wrote:
>>
>> Having a name space in the doc requires it's usage in the query.
>
> yeah, I got it... I was using wrong one...
> thanks.
>
>
>>
>>
>> On 09/17/2011 11:48 AM, boris wrote:
>>>
>>> hi all,
>>> I've inserted xml file :
>>>
>>> 
>>> >> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
>>>     
>>>     zz
>>> ..
>>>
>>>
>>> to a table:
>>>
>>> CREATE TABLE "temp".tempxml
>>> (
>>>   record xml
>>> )
>>>
>>> I can get it using:
>>> select * from temp.tempxml
>>>
>>>
>>> but, I can't get any values using xpath. ex:
>>>
>>>
>>>   select (xpath('/document/title/text()', record ))[1] from temp.tempxml
>>>
>>>
>>> am I doing it right?
>>>
>>> thanks.
>>>
>>>
>>>
>>>
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] ambiguous local variable name in 9.0 proc

[Samuel Gendler]

I've got a stored proc (which worked fine in 8.3 and 8.4) that is declared
as such:

CREATE OR REPLACE FUNCTION reporting_mgmt.aggregate_timescales_impl (
   div_start TIMESTAMP WITHOUT TIME ZONE,
*   tbl_schema VARCHAR, *
   tbl_root VARCHAR,
   fine_timescale VARCHAR,
   coarse_timescale VARCHAR,
   coarser_timescale VARCHAR,
   fact_fields VARCHAR,
   dim_fields VARCHAR,
   sum_fields VARCHAR)
RETURNS INTEGER
AS $$

Within that proc, I've got the following line:

IF EXISTS ( SELECT table_name FROM information_schema.tables
  WHERE table_schema = tbl_schema
AND table_name = tbl_fine_part_old ) THEN
IF EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e
* WHERE tbl_schema = e.tbl_schema*
   AND tbl_root = e.tbl_root
   AND div_start_old = e.fine_time
   AND coarse_timescale = e.coarse_scale
   AND status = 0 ) THEN

And in 9.0, it is generating the following error:

ERROR:  column reference "tbl_schema" is ambiguous
LINE 2:  WHERE tbl_schema = e.tbl_schema
  ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e
WHERE tbl_schema = e.tbl_schema
  AND tbl_root = e.tbl_root
  AND div_start_old = e.fine_time
  AND coarse_timescale = e.coarse_scale
  AND status = 0 )
CONTEXT:  PL/pgSQL function "aggregate_timescales_impl" line 52 at IF
PL/pgSQL function "aggregate_timescales" line 23 at RETURN

Of course, it is true that tbl_schema could refer to the column in table
etl_status, except older versions of postgres seemed to correctly figure out
that comparing a column to itself isn't terribly useful, so it must be
referring to the pl/pgsql variable rather than the table column.

I'm happy to modify the proc definition, except that I am unsure how to do
so other than to rename the variable, which is my least favourite way to do
that.  I'd far rather qualify the name somehow, so that it knows that I am
refering to a local variable, if at all possible.  Suggestions?

Re: [SQL] ambiguous local variable name in 9.0 proc

[David Johnston]

 

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Samuel Gendler
Sent: Wednesday, September 21, 2011 7:35 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] ambiguous local variable name in 9.0 proc

 

I've got a stored proc (which worked fine in 8.3 and 8.4) that is declared
as such:

 

CREATE OR REPLACE FUNCTION reporting_mgmt.aggregate_timescales_impl (

   div_start TIMESTAMP WITHOUT TIME ZONE,

   tbl_schema VARCHAR, 

   tbl_root VARCHAR, 

   fine_timescale VARCHAR,

   coarse_timescale VARCHAR, 

   coarser_timescale VARCHAR,

   fact_fields VARCHAR, 

   dim_fields VARCHAR, 

   sum_fields VARCHAR) 

RETURNS INTEGER

AS $$

 

Within that proc, I've got the following line:

 

IF EXISTS ( SELECT table_name FROM information_schema.tables

  WHERE table_schema = tbl_schema

AND table_name = tbl_fine_part_old ) THEN

IF EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e

 WHERE tbl_schema = e.tbl_schema

   AND tbl_root = e.tbl_root

   AND div_start_old = e.fine_time

   AND coarse_timescale = e.coarse_scale

   AND status = 0 ) THEN

 

And in 9.0, it is generating the following error:

 

ERROR:  column reference "tbl_schema" is ambiguous

LINE 2:  WHERE tbl_schema = e.tbl_schema

  ^

DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

QUERY:  SELECT EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e

WHERE tbl_schema = e.tbl_schema

  AND tbl_root = e.tbl_root

  AND div_start_old = e.fine_time 

  AND coarse_timescale = e.coarse_scale

  AND status = 0 )

CONTEXT:  PL/pgSQL function "aggregate_timescales_impl" line 52 at IF

PL/pgSQL function "aggregate_timescales" line 23 at RETURN

 

Of course, it is true that tbl_schema could refer to the column in table
etl_status, except older versions of postgres seemed to correctly figure out
that comparing a column to itself isn't terribly useful, so it must be
referring to the pl/pgsql variable rather than the table column.

 

I'm happy to modify the proc definition, except that I am unsure how to do
so other than to rename the variable, which is my least favourite way to do
that.  I'd far rather qualify the name somehow, so that it knows that I am
refering to a local variable, if at all possible.  Suggestions?

 

 

Not tested but I think all local variables are implicitly scoped to the
function name so you should be able to do the following:

 

WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema

 

You are going to have the same problem with other fields as well (e.g.,
tbl_root).

 

I believe there is some way to define the function so that it does not throw
that particular error; it would be up to you make sure that the ambiguity is
being resolved correctly (which it should in this particular case).  Maybe
look for "SET" variables.

 

You could also copy the tbl_schema variable value into a different variable.

 

DECLARE tbl_schema_copy VARCHAR;  tbl_schema_copy := tbl_schema;

 

. WHERE tbl_schema_copy = e.tbl_schema .

 

David J.

 

 

 

Re: [SQL] ambiguous local variable name in 9.0 proc

[Samuel Gendler]

On Wed, Sep 21, 2011 at 4:49 PM, David Johnston  wrote:

> ** **
>
> *
> *
>
> Not tested but I think all local variables are implicitly scoped to the
> function name so you should be able to do the following:
>
> ** **
>
> WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema**
> **
>
> **
>

Yep.  It is also possible to change the db behaviour globally or on a
per-function basis

from
http://developer.postgresql.org/pgdocs/postgres/plpgsql-implementation.html

"To change this behavior on a system-wide basis, set the configuration
parameter plpgsql.variable_conflict to one of error, use_variable, or
use_column(where error is the factory default)."

You can also set the behavior on a function-by-function basis, by inserting
one of these special commands at the start of the function text:

#variable_conflict error
#variable_conflict use_variable

#variable_conflict use_column


Sorry for jumping straight a mailing list query.  It was actually relatively
easy to find in the docs.  Now to figure out if the function name scoping
trick works in 8.4 so that I can modify my procs prior to upgrading my db.

--sam

Re: [SQL] ambiguous local variable name in 9.0 proc

[Tom Lane]

"David Johnston"  writes:
> On Behalf Of Samuel Gendler
> I'm happy to modify the proc definition, except that I am unsure how to do
> so other than to rename the variable, which is my least favourite way to do
> that.  I'd far rather qualify the name somehow, so that it knows that I am
> refering to a local variable, if at all possible.  Suggestions?
 
> Not tested but I think all local variables are implicitly scoped to the
> function name so you should be able to do the following:
> WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema

More accurately: you can qualify a local variable's name with the label
attached to the block in which it's declared; or with the function's
name if the variable is a function parameter.  See the fine manual:

http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html

> I believe there is some way to define the function so that it does not throw
> that particular error; it would be up to you make sure that the ambiguity is
> being resolved correctly (which it should in this particular case).

Yes, see variable_conflict in
http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html

I wouldn't really recommend turning off the conflict detection, though.
We put it in because of the number of hours people had wasted on
unrecognized conflicts.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] handling duplicate row exception

[Amar Dhole]

Hi Filip,

No not sure 100% when this can happen. This approach will not be possible as in 
our application we are programmatically handling these cases and going in other 
route to add the record with increased key.

I am using 9.0.4 version.

Thanks
Amar


From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Filip Rembialkowski
Sent: 21 September 2011 20:37
To: Amar Dhole
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] handling duplicate row exception

Hi

There is no IGNORE_DUP_KEY equivalent in PostgreSQL.

If you are 100% sure that you want to ignore unique key violations, you can 
wrap your INSERT code in PL/PgSQL block and handle the exception yourself.

I mean:

DO $$
BEGIN
 INSERT INTO foo (bar,baz) SELECT 42, 666;
EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'row skipped';
END;
$$


BTW - which version of PostgreSQL are you using?

Filip


2011/9/21 Amar Dhole mailto:adh...@tibco.com>>
Hi All,

I have a requirement where my application tries to enter the duplicate row in 
table using batchexceute code. And batch exception is thrown we checked error 
code and skip it but after this exception all my next update/insert gets error 
out with following exception

"Database error. SQL state 25P02. Database specific error code (if any) was 0. 
Database error message (if any) was: org.postgresql.util.PSQLException: ERROR: 
current transaction is aborted, commands ignored until end of transaction 
block.:


 Is there any way to proceed ahead like in sql server we have options while 
creating table IGNORE_DUP_KEY = ON if this is set warning is generated instead 
of Exception so the other insert/update can proceed ahead.



--
Sent via pgsql-sql mailing list 
(pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql