[geeklog-devel] postgresql and Geeklog 2.0.0b1
Dan Stoner
danstoner at gmail.com
Thu Jul 5 12:38:04 EDT 2012
Hi Stanislav,
According to postgresql docs:
"To create a database, you must be a superuser or have the special
CREATEDB privilege."
http://www.postgresql.org/docs/8.1/static/sql-createdatabase.html
"The user must have the PostgreSQL superuser privilege to register a
new language."
http://www.postgresql.org/docs/8.1/static/sql-createlanguage.html
When I create a new database for geeklog I am doing that step as a Superuser.
Creating the language in that particular db would be just one
additional step as Superuser.
If template1 already has the language then this additional step is not needed.
By the time I run the geeklog install script, the database should
already exist (created by a Superuser). The database should also
already have plpgsql added (added by a Superuser either to the
individual database, or to template1 prior to creating the geeklog
database).
- Dan Stoner
On Thu, Jul 5, 2012 at 12:13 PM, Stanislav Palatnik
<yankees26an at gmail.com> wrote:
> Hello Dan,
>
>> 1. When the geeklog postgres database is created, also add the
>> plpgsql language with:
>> CREATE LANGUAGE plpgsql;
>
>
> Hmm why would this work compared to what already exists? Doesn't this
> command also need superuser privileges to execute?
>
> On Thu, Jul 5, 2012 at 9:54 AM, Dan Stoner <danstoner at gmail.com> wrote:
>>
>> 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
>> _______________________________________________
>> geeklog-devel mailing list
>> geeklog-devel at lists.geeklog.net
>> http://eight.pairlist.net/mailman/listinfo/geeklog-devel
>>
>> --
>> --
>> My Best,
>>
>> Stanislav Palatnik
>>
>
> _______________________________________________
> geeklog-devel mailing list
> geeklog-devel at lists.geeklog.net
> http://eight.pairlist.net/mailman/listinfo/geeklog-devel
>
More information about the geeklog-devel
mailing list