From: Eric.Boyer at insa-toulouse.fr (Eric BOYER)
Date: Tue, 16 May 2006 16:54:56 +0200
Subject: [Pljava-dev] Problem with primary key
Message-ID: <4469E7C0.4040502@insa-toulouse.fr>

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.