From: Eric.Boyer at insa-toulouse.fr (Eric BOYER)
Date: Thu, 18 May 2006 09:12:36 +0200
Subject: [Pljava-dev] Problem with primary key
In-Reply-To: <4469F186.1020509@tada.se>
References: <4469E7C0.4040502@insa-toulouse.fr> <4469F186.1020509@tada.se>
Message-ID: <446C1E64.3050003@insa-toulouse.fr>

Thomas Hallgren a ?crit :

>Hi Eric,
>Not sure what is happening here. You have a trigger that triggers after 
>insert. If that trigger in turn does yet another insert on the same 
>table it will trigger itself with an endless recursion as the result. I 
>would expect an exception that would say something about stack limit 
>exceeded or something. Is the trace from the backend log complete?
>  
>

The log is complet and I'm rigth it looks like a recursivity. It was 
just to have a simple test on primary key.
In our application, the problem appears when the trigger insert a data 
which is a primary key in another table ...
If the exception is catched, postgres is in a strange status, we have to 
restart it, and a double primary key !!!!!

>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
>>  
>>    
>>
>
>_______________________________________________
>Pljava-dev mailing list
>Pljava-dev at gborg.postgresql.org
>http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>
>  
>