[SQL] Invalid message format Exception
Hi, Because there was no response for this question already posted in pgsql-j...@postgresql.org mailing list, I'm posting it here. I'm using PostgreSQL 8.2 and my production server is based on CentOS release 5.2 (Final). JDBC Jar: postgresql-8.2-508.jdbc4.jar I noticed from my server log that some INSERT statements are failing with "invalid message format" PSQLException. Once this exception is thrown, it is not committed to the database. What could be the reason for this? EXCEPTION org.postgresql.util.PSQLException: ERROR: invalid message format STACKTRACE org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI mpl.java:1592) org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja va:1327) org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:193) org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j ava:452) org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St atement.java:337) org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State ment.java:283) I also read about this on: http://archives.postgresql.org/pgsql-jdbc/2008-04/msg00085.php "JDBC driver is doing something wrong when calculating a message length or message content for the frontend/backend protocol" But how do I resolve this issue? http://archives.postgresql.org/pgsql-jdbc/2004-03/msg00142.php you can't represent a \0 byte in a text/varchar constant Even though I don't have much idea on encoding, I have a little doubt on whether encoding is a problem. Here is my database encoding: mydb=# \l List of databases Name | Owner | Encoding --+--+--- mydb | zoniac | SQL_ASCII Any pointers in right direction are appreciated. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Invalid message format Exception
Hi Rob, > I'm sure most will urge you to move to UTF-8 encoding asap. Did you mean the database encoding to changed from SQL_ASCII to UTF-8? > Have you tracked down the "offending" insert statement? Perhaps it's a > trigger trying to generate a log message? No, I don't have any trigger on this table for INS/UPD/DEL. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Regular Expression Match Operator escape character
Hi, We're running PostgreSQL v8.2.3 on RHEL5. In some places in our application, we use Regular Expression Match Operator (~* => Matches regular expression, case insensitive) inside WHERE criteria. Example: SELECT ... FROM ... WHERE (SKILLS ~* '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)C#(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)' OR SKILLS ~* '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$).NET(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)') In this case, we're trying to search/match for either "C#" OR ".NET" in SKILLS column. My question here is, do I need to escape the characters "#" and "." here? Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? "MYTABLE" columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Any ideas? -Original Message- From: Gnanakumar [mailto:gna...@zoniac.com] Sent: Thursday, February 17, 2011 12:36 PM To: pgsql-sql@postgresql.org Subject: Is it possible to get DISTINCT rows from RETURNING clause? Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? "MYTABLE" columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Hi Dmitriy Igrishin, Thanks. That's a good idea too. From: Dmitriy Igrishin [mailto:dmit...@gmail.com] Sent: Saturday, February 19, 2011 3:31 PM To: gna...@zoniac.com Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause? Hey Gnanakumar, You can wrap you UPDATE query into SQL function returning TABLE, e.g: CREATE OR REPLACE FUNCTION public.update_mytable() RETURNS TABLE(email text, column1 text, column2 text, column3 text) LANGUAGE sql AS $function$ UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; $function$; Next you can write, e.g: SELECT DISTINCT * FROM (SELECT update_mytable()) AS foo(email, column1, column2, column3); And so on. 2011/2/18 Gnanakumar Any ideas? -Original Message- From: Gnanakumar [mailto:gna...@zoniac.com] Sent: Thursday, February 17, 2011 12:36 PM To: pgsql-sql@postgresql.org Subject: Is it possible to get DISTINCT rows from RETURNING clause? Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? "MYTABLE" columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- // Dmitriy. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Calculating relative time/distance from CURRENT_TIMESTAMP
Hi, Is there any built-in function/add-on module available in PostgreSQL, that converts a given "timestamp"/"timestamptz" value into its relative distance from CURRENT_TIMESTAMP? For example, relative distance as today, yesterday, 5 minutes ago, 1 week ago, etc. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP
Hi Amitabh, Yes, I already took a glance of the Date/Time functions. But what I'm expecting is something more meaningful/user-friendly value to be returned than from the actual return value of these functions available here. I'm just finding out whether this could be solved at query level itself. For example, there is age(timestamp, timestamp) function. If I call like this select age(current_timestamp, (current_timestamp - interval '1 day')) it returns "1 day". But what I'm expecting to be returned from the function is something more meaningful/user-friendly to the end users. In this case, I wanted it to return "yesterday", similarly today, 15 minutes ago, 1 week ago, etc. Regards, Gnanam From: Amitabh Kant [mailto:amitabhk...@gmail.com] Sent: Thursday, August 04, 2011 4:34 PM To: gna...@zoniac.com Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP Have you looked into the date time functions already ? http://www.postgresql.org/docs/9.0/static/functions-datetime.html Amitabh Kant On Thu, Aug 4, 2011 at 1:24 PM, Gnanakumar wrote: Hi, Is there any built-in function/add-on module available in PostgreSQL, that converts a given "timestamp"/"timestamptz" value into its relative distance from CURRENT_TIMESTAMP? For example, relative distance as today, yesterday, 5 minutes ago, 1 week ago, etc. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Calculating relative time/distance from CURRENT_TIMESTAMP
> If you really want a flexible function for doing this, I suspect you could replicate the functionality of jquery's 'timeago' module in a stored proc relatively easily. http://timeago.yarp.com/ It is MIT licensed, so you can copy the logic without restriction. It makes reference to being derivative of a ruby project, so you could probably grab that code and convert it to ruby-pg very easily. Thanks for that useful link. I believe in my case this will work out. Thanks again. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
