Turn on query logging (log_statement = all) in postgresql.conf, and check the queries.
My bet is that it's the problem of driver (JDBC).
depesz
2010-01-18 15:00:09
views:
219answers:
3
+2
A:
I've done that. It does not seem like problem with JDBC driver but rather Postgres itself. I've updated my question to give details of last log entries and table rows which caused error.
WildWezyr
2010-01-18 15:39:01
here is pure sql version of the problem: http://stackoverflow.com/questions/2089772/why-this-code-fails-in-postgresql-and-how-to-fix-it-work-around-is-it-postgres
WildWezyr
2010-01-18 22:57:18
+1
A:
If you are trying to index natural language documents with Postgres (for all I can see you are trying to build an inverted index on the words of the documents), I would recommend you to take a look at Full text search in Postgres instead.
If that is not an option, then check your encoding settings:
- database (In Postgres you cannot actually change the database encoding - you will have to recreate the database from scratch.)
- JDBC driver/Postgres client setting (sorry can't remember the details),
- and your Java source (editor)
I suggest to set them all to UTF-8.
If that still did not help, then I suspect some kind of escaping/encoding issue between the source of the data (your Java source code file) and the destination of the data (the database).
Bandi-T
2010-01-18 21:38:20
This is not an issue with selecting proper way of full text searching or any thing like that. I've found very strange error while doing my original task and separated very simple example code to expose this error. Code shown in my question has now nothing to do with FTS, parsing etc. It is just to demonstrate serious problem with executing simplest statements (insert/update) in PostgreSQL. All my encodings are set to UTF-8, all values are correctly stored in DB - I've checked that. So my encoding is not an issue as it is set properly.
WildWezyr
2010-01-18 22:10:13
here is pure sql version of the problem: http://stackoverflow.com/questions/2089772/why-this-code-fails-in-postgresql-and-how-to-fix-it-work-around-is-it-postgres
WildWezyr
2010-01-18 22:59:42
I do understand FTS is not technically related to your problem; I was only suggesting it as a possible workaround since you were asking for workarounds.
Bandi-T
2010-01-18 23:40:24
@Bandi-T: I'm looking for a work-around with provided code - how to change it to get it working. IMHO it is just an example of serious flaw in PostgreSQL. Without a fix - I must escape and move to other DB with my app (but I wish there is work-around or will be a fix for Postgres).
WildWezyr
2010-01-19 00:14:14
@WildWezyr: Ok, in that case I recommend you update the question with a cleaner, more direct testcase consisting of the actual SQL queries that are needed to get issued against Postgres to cause an error. Then we will be able to clearly decide whether it is a problem with Postgres, or with the queries issued. And then we can work backwards to see what you will have to fix to solve your problem.Because I must admit I find your code too complex to actually follow in depth.
Bandi-T
2010-01-19 22:57:26
@Bandi-T: look at my answer - I've managed to simplify my code. It is now quite simple...
WildWezyr
2010-01-22 15:41:21
+2
A:
My further investigation of the problem revealed that the problem is related to pure Postgres SQL, I developed pure plpgsql version which is one-to-one port of the code above. Restated question for pure plpgsql is here: http://stackoverflow.com/questions/2089772/why-this-code-fails-in-postgresql-and-how-to-fix-it-work-around-is-it-postgres.
So - it is not Java/JDBC related problem.
Furthermore, I've managed to simplify test code - now it uses one table. Simplified problem was posted on pgsql-bugs mailing list: http://archives.postgresql.org/pgsql-bugs/2010-01/msg00182.php. It is confirmed to occur on other machines (not only mine).
Here is workaround: change database collation from polish to standard 'C'. With 'C' collation there is no error. But without polish collation polish words are sorted incorrectly (with respect to polish national characters), so problem should be fixed in Postgres itself.
WildWezyr
2010-01-22 15:31:37
Glad to see you found an answer then. Please accept your own answer in this case to flag the question answered.
Bandi-T
2010-01-22 21:21:33