Ticket #201 (closed defect: fixed)

Opened 7 months ago

Last modified 6 months ago

error with postgresql 8.3

Reported by: trac_user Assigned to: fox
Priority: major Milestone: 1.2.23
Component: backend Version: 1.2.21
Keywords: postgresql Cc:

Description

with version1.2.21 on apache 1.3.41, php 5.2.5 and postgresql 8.3.1, FreeBSD 7, it pops up an error and another error appears in the page (see attachement)

Attachments

error.png (162.1 kB) - added by trac_user on 04/17/08 22:38:22.

Change History

04/17/08 22:38:22 changed by trac_user

  • attachment error.png added.

04/17/08 22:40:07 changed by trac_user

  • version set to 1.2.21.

04/17/08 22:41:48 changed by fox

Try running this query: SELECT SUBSTRING(last_updated, 1, 19) FROM ttrss_feeds LIMIT 1;

04/17/08 22:47:04 changed by fox

To add: basically, I think that the fix is changing the query above to something like this:

SEECT SUBSTRING(CAST(last_updated AS TEXT), 1, 19) FROM ttrss_feeds LIMIT 1;

04/17/08 22:49:50 changed by trac_user 

ttrss=> SELECT SUBSTRING(last_updated, 1, 19) FROM ttrss_feeds LIMIT 1;
ERROR:  function pg_catalog.substring(timestamp without time zone, integer, integer) does not exist
LIGNE 1 : SELECT SUBSTRING(last_updated, 1, 19) FROM ttrss_feeds LIMIT...
                 ^
ASTUCE : No function matches the given name and argument types. You might need to add explicit type casts.
ttrss=> SELECT SUBSTRING(CAST(last_updated AS TEXT), 1, 19) FROM ttrss_feeds LIMIT 1;
 substring 
-----------
 
(1 ligne)

ttrss=>

it seems casting is necessary with 8.3 (http://archives.postgresql.org/pgsql-bugs/2008-01/msg00003.php)

04/17/08 22:54:12 changed by fox

How nice of them. Having the casting syntax incompatible with MySQL is a nice touch.

Maybe this can be solved with a new server-side function (e.g. substring(date, blah, blah)? I'm not rewriting all queries where stuff like this is used in two variants for MySQL and PSQL, that's for sure.

04/17/08 22:54:22 changed by fox

  • status changed from new to assigned.

04/18/08 13:32:19 changed by fox

  • milestone set to 1.2.23.

04/21/08 13:00:25 changed by www-ttrss

(In [2336]) [project @ add workaround for psql 8.3 stricter typechecking on SUBSTRING() (refs #201)]

04/28/08 17:57:03 changed by trac-user

Actually, this happened here only for substring() on timestamps. This fixed it for me (1.2.22) 

--- functions.php.orig  2008-04-28 15:49:59.000000000 +0200
+++ functions.php       2008-04-28 15:50:24.000000000 +0200
@@ -1108,8 +1108,8 @@
 
                                $result = db_query($link, "SELECT 
                                                id,content_hash,no_orig_date,title,
-                                               substring(date_entered,1,19) as date_entered,
-                                               substring(updated,1,19) as updated,
+                                               ".SUBSTRING_FOR_DATE."(date_entered,1,19) as date_entered,
+                                               ".SUBSTRING_FOR_DATE."(updated,1,19) as updated,
                                                num_comments
                                        FROM 
                                                ttrss_entries

Regards

Martin Emrich <emme@emmes-world.de>

04/28/08 18:06:49 changed by fox

Yeah, I knew I forgot some substring()s.

04/29/08 11:14:32 changed by www-ttrss

(In [2340]) [project @ more work on PGSQL 8.3 compatibility (refs #201)]

05/20/08 14:56:47 changed by fox

  • status changed from assigned to closed.
  • resolution set to fixed.

OK, I can't find more of those so I consider the issue settled. Please report any other issues with SUBSTRING_FOR_DATE() here.