[Pljava-dev] Problem with primary key

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.

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
>

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
>

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

Thomas Hallgren a ?crit :

>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.
>
>
It was a simple test. In reality, we use log4j !

>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.
>
>
OK but we wan't catch exception for different reasons ...

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

Eric BOYER wrote:
> Thomas Hallgren a ?crit :
>
>> 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.
>>
>>
> It was a simple test. In reality, we use log4j !
Really? Why is that? You are aware that PL/Java is adapting the standard
java.util.logging stuff on top of the PostgreSQL logger? More info here:
http://wiki.tada.se/display/pljava/Logging

> OK but we wan't catch exception for different reasons ...

You must use savepoints if you plan to catch SQLExceptions without
throwing them again (see:
http://wiki.tada.se/display/pljava/Exception+handling) If you don't it
really shouldn't matter if you catch a ServerException (the subclass of
SQLException that you must have encountered). That exception will always
be re-thrown and cause a rollback once your method returns. And that's
regardless of if you catch it or not.

The fact that the backend reached an unstable state actually indicates a
bug in PL/Java. My guess is that the endless recursion that you caused
somehow messed up the internal error handling. I'll look into that.

Regards,
Thomas Hallgren