Hi!

When creating a database with tinyERP 4.2.2 and postgres 8.3 the following error occurs:

Code:

Wed, 13 Feb 2008 11:56:46 INFO:init:addon:report_analytic_line Wed, 13 Feb 2008 11:56:46 INFO:init:addon:report_analytic_line:creating or updating database tables Wed, 13 Feb 2008 11:56:46 ERROR:web-services:Exception in call: Traceback (most recent call last):   File "netsvc.pyo", line 200, in _dispatch   File "service\web_services.pyo", line 422, in execute   File "service\web_services.pyo", line 402, in _execute   File "wizard\__init__.pyo", line 160, in execute   File "wizard\__init__.pyo", line 75, in execute_cr   File "C:\Program Files\TinyERP Server\addons\base_setup\wizard\wizard_base_setup.py", line 220, in _update   File "pooler.pyo", line 64, in restart_pool   File "pooler.pyo", line 53, in get_db_and_pool   File "addons\__init__.pyo", line 274, in load_modules   File "addons\__init__.pyo", line 204, in load_module_graph   File "addons\__init__.pyo", line 181, in init_module_objects   File "C:\Program Files\TinyERP Server\addons\report_analytic_line\report_analytic_line.py", line 70, in init   File "sql_db.pyo", line 75, in execute ProgrammingError: ERROR:  function pg_catalog.substring(date, integer, integer) does not exist at character 134 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I already reported it here

http://tinyforge.org/tracker/index.php?func=detail&aid=610&group_id=13&atid=129

knerz

I'm getting the same error only when installing some modules, mainly reporting and analysis.

Looking at the response to the postgres bug logged it appears that it's a tinyerp problem. I guess I will have to go back to previous version of postgres.

Just un-installed Postgres8.3 and installed postgres8.2.6-2 for win32 (on XP) and using the same settings. TinyERP client is now freezing and then eventually comes up with an error saying it can't connect to server, then an error reported in the client that an error occured during database creation when I try and create a new database TinyServer and database are talking as I manually created the terp database in postgres and it populates the database with tables.

error in server.log

Thu, 14 Feb 2008 16:38:40 ERROR:web-services:Exception in call: Traceback (most recent call last):
File "netsvc.pyo", line 330, in run
File "service\web_services.pyo", line 130, in get_progress
KeyError: False

Investigating further.....

I encountered similar problems. Since I work on na Win2003 Server for the first time I was not sure whether the problem was due to my lack of knowledge or due to some incompatibility of tiny with postgres 8.2.6. I'll try using 8.2.5 and post results.

EDIT: 8.2.5 seems to be working with tinyerp 4.2.2. Do know if it had any effect, but when client was frozen on startup, I closed it, stopped and restarted postgres and tinyserver, started tinyclient again and it was working. Maybe this is also working for postgres 8.2.6? All in all not a behavior I like when it comes to software...

knerz

Last edited by knerz on Thu Feb 14, 2008 1:30 pm; edited 1 time in total

ok, found something:
http://tinyerp.org/forum/post17602.html

When you can confirm this I'll submit it to bug-tracker

knerz

[quote="knerz"]I encountered similar problems. Since I work on na Win2003 Server for the first time I was not sure whether the problem was due to my lack of knowledge or due to some incompatibility of tiny with postgres 8.2.6. I'll try using 8.2.5 and post results.

EDIT: 8.2.5 seems to be working with tinyerp 4.2.2. Do know if it had any effect, but when client was frozen on startup, I closed it, stopped and restarted postgres and tinyserver, started tinyclient again and it was working. Maybe this is also working for postgres 8.2.6? All in all not a behavior I like when it comes to software...

knerz[/quote]

I've tried a number of things yesterday in various forms and the only thing that seems to work successfully is the demo package installed to the default location. I wanted it installed to my d:, but this was causing some freezes of the client, although TinyERP installed to d: postgres did not. I noticed that the demo install seems to install postgres from the extracted msi then after postgres is installed it extracts postgres config files from the demo installer.

I did have problems with stopping the server as you did in your previous post, but was resolved when I went to the demo installer.

I don't really want to leave Tiny on my c: which is what led me to trying the Seperate install of TinyERP Client and server and Postgres 8.3. I was happy with this installation once I got a working server config file, however the errors encountered when installing some TinyERP modules made me roll back to the demo install at default locations I think that postgres8.3 won't be able to be used until the modules or tiny is fixed as per the response to the postgres bug report.

So what I have done is uninstall everything...postgres 8.2.6-2 and TinyERP server and Client. I've then installed the TinyERP demo installer to the default location and all appears to be working, just need to make sure that you unselect the demo data when creating a new database.

I would like to see a future resolution to this as using postgres 8.3 for the short time I did seem to be more responsive than 8.2, however it was only limited and un-scientific testing.

Has anyone used postgres8.3 and tiny on a linux platform successfully?

Stephen...

Hello,

I posted 01/01/2008 on postgresql 8.3 beta, user list this:
http://archives.postgresql.org/pgsql-bugs/2008-01/msg00001.php

and the answer was:

Quote:

From: Sam Mason <sam> To: pgsql-bugs ( at ) postgresql ( dot ) org Subject: Re: BUG #3848: function pg_catalog.substring(date, integer, integer) does not exist Date: Tue, 1 Jan 2008 21:14:07 +0000 -------------------------------------------------------------------------------- On Tue, Jan 01, 2008 at 08:11:01PM +0000, Ion wrote: > Email address: sorry_not_for_spam ( at ) yahoo ( dot ) fr lets hope Ion is subscribed! > I tested PostgreSQL 8.3 beta4 with tinyerp and I have this problem: > pg_catalog.substring(date, integer, integer) does not exist This is most likely a bug in tinyerp. substring has only ever been defined for string data types and isn't valid for date types at all. > I have not this problem with postgresql 8.2.5. Prior to PG 8.3, all types would be silently coerced into TEXT types in certain cases, leading to all sorts of strangeness. I've just downloaded the source of tinyerp and had a look though and this practice seems pretty endemic. They should really be using to_char(date_exp, 'YYYY-MM') instead of substring(date_exp FOR 7) in all their queries. Sam

and

Quote:

From: Tom Lane <tgl> To: Sam Mason <sam> Subject: Re: BUG #3848: function pg_catalog.substring(date, integer, integer) does not exist Date: Tue, 01 Jan 2008 16:29:47 -0500 -------------------------------------------------------------------------------- Sam Mason <sam> writes: > I've just downloaded the source of tinyerp and had a look though and > this practice seems pretty endemic. They should really be using > to_char(date_exp, 'YYYY-MM') > instead of > substring(date_exp FOR 7) > in all their queries. Or at least explicitly casting the date to text. But your way is better, since it won't break if the datestyle is something other than ISO. regards, tom lane

I can confirm this for Ubuntu 8.04 (Hardy Heron) with postgres 8.3 and TinyERP 4.2.0. After changing:

substring(date for 7)

to

to_char(date, 'YYYY-MM') (change "date" to the according variable)

in all of the following files:

/usr/share/tinyerp-server/addons/report_sale/report_sale.py
/usr/share/tinyerp-server/addons/report_project/report_project.py
/usr/share/tinyerp-server/addons/report_analytic_line/report_analytic_line.py
/usr/share/tinyerp-server/addons/report_crm/report_crm.py
/usr/share/tinyerp-server/addons/report_intrastat/report_intrastat.py
/usr/share/tinyerp-server/addons/report_purchase/report_purchase.py
/usr/share/tinyerp-server/addons/report_timesheet/report_timesheet.py

recompiling in each directory with:

py_compilefiles report_...py (replace the full names from above)

restarting the server (Important!!) with:

sudo /etc/init.d/tinyerp-server start

I could populate the database without any errors.
Hope this step-by-step instructions help other rookies like me.

Cheers,
Aron

Can anyone confirm if making the TO_CHAR() fix is backwards compatible with Postgres 8.2 ?

In that case the developers should merge this patch into the head as soon as possible because the Ubuntu 8.04 version will certainly take a prominent place as Tiny/OpenERP hosting machine for the coming 3 years (at least).

I tried this on Slackware and still got an error. Turned out one SQL-Command is still using SUBSTRING instead of TO_CHAR. I applied the following patch:
http://hw.3kisses.net/files/src/report_analytic_line.diff

Afterwards everything worked fine.

Here's an awk that should make the change in a file

Code:

awk '{print gensub(/(substring).(.*)( for 7)/, "to_char\50\\2, \47YYYY-MM\47", "g")}'

It would help f someone could improve it