From: thomas at tada.se (Thomas Hallgren)
Date: Tue, 16 May 2006 17:46:46 +0200
Subject: [Pljava-dev] Problem with primary key
In-Reply-To: <4469E7C0.4040502@insa-toulouse.fr>
References: <4469E7C0.4040502@insa-toulouse.fr>
Message-ID: <4469F3E6.6070802@tada.se>

Eric,
You say that you re-throw the exception in your catch. Yet, in your 
example you don't do that. Also, to do an e.printStackTrace() might be a 
bad idea sometimes. If the postmaster runs as a service, there will be 
no System.out assigned.

Try using the logger instead. Or even better, don't catch the exception 
at all. Let PL/Java catch it for you. It will print it using the logger. 
If you set the log_min_messages to DEBUG1, you will get the full 
stack-trace. Setting the client_min_messages to DEBUG1 means that you'll 
get the messages propagated to your client.

I.e.:

public static void traiter(TriggerData td) throws SQLException {
   Connection conn = DriverManager.getConnection("jdbc:default:connection");
   Statement stmt = conn.createStatement();
   stmt.execute("insert into test (lib) values('monlib')");
}

and then do:

SET client_min_messages TO DEBUG1;
INSERT INTO test VALUES('monlib');

Regards,
Thomas Hallgren

Eric BOYER wrote:
> Hello,
>
> I have a problem with primary key !
>
> I have a simple table like :
>
>     Table "public.test"
>  Column | Type | Modifiers
> --------+------+-----------
>  lib    | text | not null
> Indexes:
>     "test_pkey" PRIMARY KEY, btree (lib)
>
> I have a fonction :
>                          List of functions
>  Schema |      Name       | Result data type | Argument data types
> --------+-----------------+------------------+---------------------
>  public | allominitrigger | "trigger"        |
> (1 row)
>
> My code for this fonction is :
> public static void traiter(TriggerData td) {
>              try {
>             Connection conn = 
> DriverManager.getConnection("jdbc:default:connection");
>             Statement stmt = conn.createStatement();
>             stmt.execute("insert into test (lib) values('monlib')");
>              }
>              catch (Exception e) {
>              e.printStackTrace();
>           }
> }
>
> I have the trigger :
> trigtest AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE 
> allominitrigger()
>
> ---------
> My test is :
> I want insert a row and in my fonction, i wan't to insert the same row 
> to test the primary key control
>
> I insert :
> insert into test values('monlib');
>
> And I have :
> WARNING:  buffer refcount leak: [003] (rel=1663/16922/164335, 
> blockNum=1, flags=0x27, refcount=1 1)
> WARNING:  relcache reference leak: relation "pk_test" not closed
> WARNING:  relcache reference leak: relation "test" not closed
> WARNING:  transaction left non-empty SPI stack
> HINT:  Check for missing "SPI_finish" calls.
>
> And When I select values :
> SELECT * from test;
>   lib
> --------
>  monlib
>  monlib
> (2 rows)
>
> My primary key is duplicate !
>
> Is it normal ?
>
> Of course, if in my catch I have :
>
> catch (Exception e) {
>              e.printStackTrace();
>             throw e;
>           }
>
> All is OK, postgres doesn't duplicate the primary key !
>
>
> Thanks for your point of vue !
>
> eric.
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>