Forum OpenACS Q&A: problem with to

Posted by Marcin Bajer on 12/20/00 01:00 PM

While trying to implement the TCL-based bboard search solution in my OpenACS installation I installed file postgres-pgtcl.sql from /doc/sql directory. I guess this was a wrong move, because since then I get server errors everytime I try to display forum statistics. The error is

   Error: dbinit: error(localhost::tabaza,ERROR:  Function
'to_date(unknown, unknown)'
does not exist
        Unable to identify a function that satisfies the given
argument types
        You may need to add explicit typecasts

I tried to do dump/restore, then dump/ restore to another database but that did not help. I tried also deleting (DROP FUNCTION) functions from the mentioned .sql file, but this didn't help either. The same for droplanguage pgtcl. I guess the TCL functions from that file broke in some way their SQL equivalents. I have no idea how to fix it. Any suggestions, please?

2: Response to problem with to_date function (response to 1)

Posted by Don Baccus on 12/20/00 04:26 PM

That's weird. For starters, how about creating a new db, doing a
fresh install of the ACS without the postgres-pgtcl.sql functions, and
then see if things work? This way you can test a bit without
destroying your current OpenACS installation.

You're running PG 7.0.3, I presume?

3: Response to problem with to_date function (response to 1)

Posted by Marcin Bajer on 12/20/00 06:21 PM

I am running PostgreSQL 7.0.2 and AOLserver 3.2+ad10. Both installed from tarball and compiled (PostgreSQL with TCL). I'll try out your
suggestion and post results here. But I am wondering: the file
postgres-pgtcl.sql is not loaded during normal install (because
functions defined there are TCL functions, and normal installation
comes with pgsql as the only language). Is it a leftover from
previous version?

There is also another weird thing: after seeing that dump/restore
does not solve my problem, I removed all TCL functions from the dump
file and restored from it once more. Did not help, either. But I am
sure it worked before. Now I have search in forums (great!) but can
not run statistics. If only that, but I guess it will generate server errors elsewhere, where any of these date functions is used.

Since my installation has already some users, reinstalling
the stuff from scratch is not what I want to do. But I wonder about
dumps -- anytime I restore from them, I get a few errors. Is
pg_dumpall a better solution? Or am I the only one having problems
with that?

I'll appreciate any reply.

4: Response to problem with to_date function (response to 1)

Posted by Marcin Bajer on 12/29/00 10:53 AM

I got it fixed by editing the dump file and reloading it again.
The part containing function definitions should be moved towards
beginning of the file. There are still some minor errors during
restore process, but I got my system working again.

I noticed that
pg_dump does not use 'CREATE VIEW'. Instead it puts into output file equivalent CREATE TABLE and rules. This may lead to problems, when the view/table name is long. I don't remember exact limitation of name length, but I noticed that four views from ecommerce module
could not be restored properly. Since I don't use ecommerce, I don't care, but for those of you interested - probably you will have to refer to /doc/sql directory and re-create these views.

Forum OpenACS Q&A: problem with to_date function