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

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?

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
>