problem with PostgreSQL v8.3

knerz · **Joined:** Tue Oct 24, 2006 10:43 am **Posts:** 591

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? ... 3&atid=129

knerz

seaton · **Posted:** Wed Feb 13, 2008 2:59 pm

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.

knerz · **Joined:** Tue Oct 24, 2006 10:43 am **Posts:** 591

Quote:

I guess I will have to go back to previous version of postgres

that's what I did. Now using 8.2.6-2

seaton · **Posted:** Thu Feb 14, 2008 9:28 am

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

knerz · **Joined:** Tue Oct 24, 2006 10:43 am **Posts:** 591

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

knerz · **Joined:** Tue Oct 24, 2006 10:43 am **Posts:** 591

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

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

knerz

seaton · **Posted:** Fri Feb 15, 2008 4:35 am

knerz wrote:

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

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

i · **Joined:** Thu Mar 23, 2006 10:16 am **Posts:** 45

Hello,

I posted 01/01/2008 on postgresql 8.3 beta, user list this:
http://archives.postgresql.org/pgsql-bu ... g00001.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

gegard · **Posted:** Sun Feb 24, 2008 9:47 pm

i wrote:

... the answer was:

Quote:

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

That shows one of the strengths of Open Source - there may have been a hidden fault (and with work of this complexity, that's no surprise to me) but someone in an entirely different community could quickly point to a possible solution!

Good work in asking the question so constructively!

Regards,
Geoff

cornflake · **Joined:** Sun Apr 06, 2008 1:56 pm **Posts:** 6

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

red15 · **Posted:** Wed May 28, 2008 12:48 pm

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

ppr-kut · **Joined:** Thu Jun 05, 2008 1:39 pm **Posts:** 1

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.

flengure · **Joined:** Thu Oct 09, 2008 12:33 am **Posts:** 1

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