[Pljava-dev] Problem with primary key

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
>
>
>