[geeklog-devel] postgresql and Geeklog 2.0.0b1

[Dan Stoner]

I thought about this some more and poked a bit.

Newly created databases in PostgreSQL inherit from the template1 database.

On my server, template1 does not include the plpgsql language (this
has been the default on all postgres database servers I have ever
administered).  Maybe most hosting environments do add that language
to the template database, so most users would not see this issue.


The two likely alternatives to meeting this requirement and allow the
install to go through are:

  1.  When the geeklog postgres database is created, also add the
plpgsql language with:

  CREATE LANGUAGE plpgsql;

  2.  Before creating a new geeklog database, make sure that the
template1 database contains the plpgsql language.  Same command as
above, just on the template1 database.  All future databases that are
created would have this language available.


Here's some SQL...

template1=# select * from pg_language where lanname='plpgsql';
 lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
---------+---------+--------------+---------------+--------------+--------
(0 rows)

template1=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE

template1=# select * from pg_language where lanname='plpgsql';
 lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
---------+---------+--------------+---------------+--------------+--------
 plpgsql | t       | t            |         17625 |        17626 |
(1 row)


Regards,

- Dan Stoner


On Wed, Jul 4, 2012 at 11:09 AM, Dan Stoner <danstoner at gmail.com> wrote:
> I tried the nightly tarball with root debugging enabled.
>
> I had a few issues related to filesystem permissions (not postgres),
> once those were fixed things got better.
>
> I was able to log in with Admin and default password.  After clicking
> around a bit, most of the functionality seems to be ok (the admin
> panels are accessible).
>
>
> However, the main index page fails with the following:
>
> An error has occurred:
>
> This is being displayed as "Root Debugging" is enabled in your Geeklog
> configuration.
>
> If this is a production website you must disable this option once you
> have resolved any issues you are investigating.
>
> 2 - pg_query(): Query failed: ERROR: column "gl_stories.date" must
> appear in the GROUP BY clause or be used in an aggregate function @
> /var/www/private/pgblog_private/system/databases/pgsql.class.php line
> 350
>
> Call Stack
> #       Function        File    Line
> 1       pg_query        /var/www/private/pgblog_private/system/databases/pgsql.class.php        350
> 2       dbQuery /var/www/private/pgblog_private/system/lib-database.php 213
> 3       DB_query        /var/www/private/pgblog_private/system/lib-syndication.php      99
> 4       SYND_feedUpdateCheckAll /var/www/private/pgblog_private/system/lib-syndication.php      210
> 5       SYND_feedUpdateCheck    /var/www/sites/thatlinuxbox.com/pgblog/lib-common.php   2662
> 6       COM_rdfUpToDateCheck    /var/www/sites/thatlinuxbox.com/pgblog/index.php        154
>  array(3) {
>   ["sql"]=>
>   string(229) "SELECT sid FROM gl_stories, gl_topic_assignments ta
> WHERE draft_flag = 0 AND date <= NOW()  AND ta.type = 'article' AND
> ta.id = sid AND (ta.tid IN ('General','Geeklog')) AND perm_anon > 0
> GROUP BY sid ORDER BY date DESC  LIMIT 10"
>   ["ignore_errors"]=>
>   int(0)
>   ["matches"]=>
>   array(0) {
>   }
> }
>
>
> In this case I didn't drop my database, so the install script did an
> Upgrade, so I didn't get any issue about Superuser role.  However, it
> seems that the install script is adding the PLPGSQL stored procedure
> language to the database (to the schema?), which does require
> superuser role.
>
> I guess some people's databases are created with that language by
> default, in my stock config it is not.
>
>
> Regards,
>
> - Dan Stoner
>
>
> On Tue, Jul 3, 2012 at 4:34 AM, Dirk Haun <dirk at haun-online.de> wrote:
>> Quoting Dan Stoner <danstoner at gmail.com>:
>>
>>> I just got back from a tech conference and I'm a little fired up about
>>> open source again.  I allocated some time to test Geeklog with
>>> postgresql.
>>
>>
>> Thanks for doing that.
>>
>> Please note that Postgres support in 2.0.0b1 is very buggy and barely
>> working. I made a few tweaks since then, so if you have the time, try a
>> nightly tarball (or wait for b2).
>>
>>
>>
>>> 3.  The admin account with default password of "password" did not seem
>>> to exist or let me log in.
>>
>>
>> Works for me (now - I think this was one of the post-b1 fixes that I had to
>> make).
>>
>>
>>
>>> 4.  The main page seemed to render ok, as do some of the sub-pages,
>>> but many of them error out with "Unfortunately, an error has occurred
>>> rendering this page. Please try again later."
>>
>>
>> You may want to enable root debugging (in siteconfig.php). It'll not only
>> print the error message directly in the browser window, but also displays a
>> call stack so that you can see where the call is coming from (an error in
>> pgsql.class.php isn't all that helpful - the faulty SQL was usually created
>> elsewhere).
>>
>>
>>
>>> Looking in my geeklog error log, I see:
>>
>> (snip)
>> These look familiar and should hopefully be fixed now.
>>
>>
>>
>>> Let me know what I can do to help.
>>
>>
>> See above. Please note that I'm testing locally with Postgres 9.1, so it'll
>> be interesting to see if there are other problems with 8.1.
>>
>> bye, Dirk
>>
>>
>>
>> _______________________________________________
>> geeklog-devel mailing list
>> geeklog-devel at lists.geeklog.net
>> http://eight.pairlist.net/mailman/listinfo/geeklog-devel