Postgresql

There is a nice feature matrix on the PostgreSQL page in case you are wondering what features are available in different version of PostgreSQL.

http://www.postgresql.org/about/featurematrix

Looks like 8.3 is really a killer release!

I am building a web app with Postgres, that also uses Drupal with Postgres. I am new to all these frameworks.

There is some data that I'll need to cross-reference between the two databases.

Can I do a cross-schema/catalog join? Or is a cross-database join better?
Are there any gotchas for the cross-schema/cross-database joins that I need to be aware of? Such as when using Shared hosting?

Any suggestions/comments appreciated.

Thanks

Can someone please post a VMware VM appliance with Drupal working with Postgres, that us newbies can play with?
Additionally, all popular modules working with Postgres may be enabled in this VM. This can help increase a beeline for the unsupported modules to add support.

With the release of Drupal 6 support for PostgreSQL has come a long way in that core and contrib modules no longer have to know PostgreSQL syntax for CREATE TABLE and other table/index creation or alteration actions.

However when it comes to writing actual SQL in core and contrib modules there will be many situations where SQL that works fine in MySQL will not work in PostgreSQL. Most of the time this is due to the use of MySQL-specific SQL functions or to PostgreSQL's stricter adherence to SQL 92 or SQL 99 standards.

Now that D6 is out it's likely that many module authors will begin to port their modules to D6. With the schema API addition to D6, most authors will assume that PostgreSQL support is included without thinking about the SQL they write in their module functions. For those of us that are PostgreSQL supporters and have the knowledge to help module authors with their SQL this is a great time to make a push for more solid support of PostgreSQL in Drupal.

This Wiki can help to identify different types of SQL that will have problems in PostgreSQL and include solutions if any to these problems. Please add or update anything you think is helpful.

Hello,

I am new to Drupal, and am using Mono/Postgres with my personal project. I'd like to integrate a CMS with it, and zeroed on to Drupal over MojoPortal, for its much higher range of features.

However, from my search, I find that Postgres support seems kind of step-motherly. As a newbie, I wanted your comments on whether I should still bite the bullet & go with Drupal 6, or should I wait for 7 till the support gets better.

Here are the Drupal features I need:
1. Books
2. Wiki
3. Blog
4. Forum
5. Rating system
6. Statistics

Thanks

I have been using Drupal with Postgres for some time now. I actually choose Drupal because of it supported Postgres (I really like the database). Throughout this time I’ve helped fix several issues related to module’s support to Postgres. This is a kind of chicken and egg problem. Since not all modules support Postgres, a lot of people are forced to use MySQL, and thus never improve Postgres support. Drupal 6 Schema API promises to improve this very much.

However, after reading a lot of flames on dropping postgres supports from core, and how to fix it, I have come to the conclusion that we need a more coordinated effort to support Postgres. Fixing issues is not really hard, but you are normally focusing on your problems. I believe we need a team of user to help test and fix issues with postgres database support.

I start my Drupal + Oracle research since the end of Drupal 4.7 life cycle (around Oct 2006), based on my client's request. The project is still running, but I am not satisfy about its progress. After keep trace in Drupal database stack implementation for more than years, it is time for focusing on Drupal 7.x development.

So what are the changes since 4.7.x? And what will be happened for 7.x? I would like to share my research progress with you, and so let's brain storming for what's next :-)

Abstract

Drupal 6.0 is revamped with Schema API, so what's next for Drupal 7.x? PDO for sure! With this powerful data-access abstraction layer, workload will much reduced for DB abstraction layer designers and developers, and finally benefit our contribute developers and end users.

By the way, together with the decision of Drupal 7.x + PDO, there is also some voice about moving PostgreSQL (and so other potential databases support, e.g. Oracle, DB2, MSSQL, etc) support away from core, but contribute; on the other hand, add official SQLite support into Drupal core, together with MySQL.

Is this really a good idea? Or even if it is possible? As an existing Drupal + PostgreSQL users, what will this affect your daily work? As a potential customer of Drupal + Oracle/DB2/MSSQL/etc, is this a good new for you, or just an evil? I would like to provide some brief idea for you within this article.

Enhance cross database compatibility is important for our Drupal Core, especially when we are going to ship D7 with PHP5.2.x PDO implementation. BTW, this will require number of individual patches, so your help and review are required - add it to the top list with a brief description and an estimate of the time to review. Once reviewed, move the issues to the bottom "reviewed" list, perhaps with your name attached.

I'm assuming it's too late, but if not here's an easy performance win for D6:

http://drupal.org/node/215080

<

blockquote>The type column of the {system} table is currently a varchar(255) field. Yet in 99% of all Drupal installations it stores either the text 'module' or the text 'theme' and nothing else. This is already a strong case for making it an int column and defining constants for module and theme. Then, on every page, there is this query:
SELECT filename FROM system WHERE name = 'user' AND type = 'module';

If you know of an issue that is being postponed while waiting for a Postgres review or that adds PostgreSQL compliance to a module and therefore needs testing on PostgreSQL - add it to the top list with a brief description and an estimate of the time to review. Once reviewed, move the issues to the bottom "reviewed" list, perhaps with your name attached.

The more patches you review, the less chance there is of this happening

NOTE: I suppose you could use a converter on your schema, but I was using MySQL 4.1 and wanted the constraints that came with Postgresql. I was able to just convert it with no problems. I started with MySQL 4.1 and moved to PostgreSQL 7.4

1. Figure out which modules might not support Postgresql. I put this part first since it might take you a while to track down upgrades, patches, or fixes you apply yourself. Find out all the modules that you are using.
   # modules=`echo "SELECT name FROM system WHERE status=1 AND type='module'" | mysql --skip-column-names -u <em>prototype -p prototype`

   Find all the install files that contain the word db_type:

   # cd /path/to/modules
# installs=`for module in $modules; do find . -name $module.install -exec grep db_type {} /dev/null \; ; done | awk -F: '{ print 1ドル }' |sort | uniq`

   Find all the install files that do not contain the word pgsql:

   # for install in $installs; do grep -q 'pgsql' $install /dev/null || echo "$install does not contain psql schema code."; done
./devel/devel.install does not contain psql schema code.

   With this list you should see if you can find a patch like this one:

   http://drupal.org/files/issues/devel_18.patch

   Or perhaps you need to upgrade the version of the module you are using. Otherwise you have to leave it off the list when we update the fresh database we are about to create.

2. Create a fresh database on you development server. This way we can get a fresh schema for importing into a second database. This works better than try to import data into an existing fresh installation.
   $ createdb -U <em>drupal5 -W drupal5fresh -E UNICODE
CREATE DATABASE
   
   NOTE: You should already have a drupal5 user setup that can add databases
3. Checkout a fresh installation of your drupal code -or- use your existing development setup
4. Update sites/defaults/settings to point to a new database
5. Perform a normal installation (e.i. goto http://127.0.0.1/install.php)
6. Goto the Admin page and bring up the list of modules. By hand add all the modules based on the list you produce with the code below. At this point leave out any modules that don't support PostgreSQL.
   # for module in $modules; do find . -name $module.info -exec grep ^name {} \; ; done | perl -pi -e 's/name = //' | sort
Actions
Aggregator
"Basic event"
Block
Captcha
Content
Devel
Devel Node Access
Drake
Event
. . .

   NOTE: If you get database errors or warning, then the code isn't supporting PostgreSQL very well. So you need to find a solution to fix it. I had problems with Panels, Textimage and Webform. Make sure you submit patches back to the project, or update tickets that note you success or failure. For example there was a small bug in the panels install I had to change dbtype to db_type. Then I uninstalled it (with errors), then reinstalled it and it worked.

7. Now we dump out the schema but now the data.
   # pg_dump -h 127.0.0.1 -U drupal5 -W -s drupal5fresh > /var/tmp/drupal5fresh_schema.sql
8. Create a second database to import the schema with no data.
   # createdb -O drupal5 drupal5pgsql -E UNICODE
CREATE DATABASE
9. Import the schema into the second database
   # cat /var/tmp/drupal5fresh_schema.sql | psql -h 127.0.0.1 -U drupal5 -W drupal5pgsql
10. Dump you existing MySQL database
    # mysqldump -t --skip-opt --compact prototype > prototype_data.sql
11. Convert it. There are two tools, the first one has been worked on recently. But I had success with the second one, during a complete conversion, it failed when I removed the CREATE TABLE syntax. I did have delete the devel_queries, devel_times, cache_menu, cache_views rows. They were just to big or a waste of time.

    NOTE: I had to modify the mysql2pgsql.perl code to remove the E'' notation. I am not using PostgreSQL 8.1.

    #s/'(.*?)'([,)])/E'1ドル'2ドル/g;
# for the E'' see http://www.postgresql.org/docs/8.2/interactive/release-8-1
.html
    # ./mysql2pgsql.perl --debug prototype_data.sql drupal5pgsql_data.sql

    http://gborg.postgresql.org/project/mysql2psql/projdisplay.php
    http://www.omnistarinc.com/~fonin/projects/my2pg/my2pg.pl

12. Import the data, with a little luck you will have a full functional conversion.
    # cat /var/tmp/drupal5pgsql_data.sql | psql -h 127.0.0.1 -U drupal5 -W drupal5pgsql

    NOTE: If you are working with a lot of data, I would run script first. This way you can go back over the output for any errors. I had a couple of CCK tables that didn't get moved over. I am just going to do those by hand.

13. Take care of the sequences
    # echo "SELECT * FROM sequences" | mysql --skip-column-names prototype | awk '{ print "SELECT pg_catalog.setval(\x27"1ドル"_seq\x27,"2ドル",false);" }' | psql -h 127.0.0.1 -U drupal5 -W drupal5pgsql

    NOTE: There are any missing sequences there is a problem with the module. It isn't correctly creating the sequence during installation.

Can anyone suggest what code is needed to enable classiified ads http://drupal.org/project/ed_classified to work on pgsql?

Here is part of the install program which needs updating

Hi folks

i use trust mode for authentication within my DMZ. Drupal "outta da box" doesn't support this, and so i've got a patch for it here:
http://drupal.org/node/125105

Can postgresql folks stop by for a comment?

Hello,

I've may have some time available in the next few months to devote to upgrading Drupal's GIS abilities. One area where I'd like to concentrate is exposing PostGIS data and functionality. In a nutshell, PostGIS allows storing arbitrary geometries (points, lines, polygons, multipolygons and combinations of those types) in database columns.

Sorry for spamming the postgresql group -- but this is database related, so let the elephants lead the charge!

Attached Files:

nested-set-1 => is a represenation of traditional nested set (operating on DAG) vs. chx implementation
chx2 => mfredrickson's visualization of what chx was describing.
chx2_updated => now with labels
chx_edge_case => a more degenerate DAG. Can chx's algorithm find the right numbers?

As a MySQL user I'm finding this a useful resource to help support Postgresql installations. It does mysql>psql conversions (and reverse, but that's usually not the problem!).
http://sql.phpmix.com/index.php

Hi,

Could someone please look at http://drupal.org/node/29706 and see if it's pgsql compliant? How do the CONCAT, MD5 and SUBSTRING functions behave on PostgreSQL?

Thanks,
Johan Forngren

Hello,

I think this is a great group.

I'm not sure if it's within the charter, but I'd like to request some assistance in testing/reviewing the Pathauto module to make sure that it is Postgresql compliant. I'm a maintainer of Pathauto and would love to find a user of both Postgresql and Pathauto to make sure that it works (and that changes I make don't break the compliance!)

Anyone care to help? It's a pretty popular module so I imagine I'd hear about any problems, but it would be nice to know "for sure".

Thanks,
Greg

Almost every developer know that there is an other Open Source Database Server besides MySQL; her name is PostgreSQL. She is a good option if you are planning a scalable website with high traffic or specific transactions (e.g. e-commerce, large community sites). There are more advantages but my aim here is not a list of "top ten list of ... ".