Column blocks.pages has no default value and therefore must always be specified when inserting a new row. If it is not, MySQL uses an "implicit default value" but PostgreSQL properly fails the query.
The new _block_rehash, from http://drupal.org/node/80963, does not specify a value for pages so the query fails on that db.

Patch attached.

CommentFileSizeAuthor
#9 block-rehash-pages-190128-8.patch935 bytesbjaspan
#8 postgresql_sucks.patch557 byteschx
block-rehash-pages.patch694 bytesbjaspan

Comments

bjaspan’s picture

bjaspan commented

I deleted some previous comments of mine that turned out to be wrong.

The reason blocks.pages has no default value is that mysql does not allow default values on text columns. You can actually specify default '' when creating a column but if you then ask mysql what the column's default value is, it says it does not have one.

Confusion arises because of mysql's (IMHO stupid) "implicit default value" rules which will use '' as the default value for a text column if no other value is provided and the column is NOT NULL. This has allowed a lot of sloppy coding.

If we want to have a single database schema for mysql and pgsql and have schema.module able to verify that the database matches the schema, we cannot allow default values for text columns. Therefore, my patch to block.module is correct; a value for pages must be provided explicitly.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

Maybe add a short onliner above it like "// Empty value required by PostgreSQL" or something would be good, otherwise it looks puzzling.

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented

How would someone test this patch?

Enable a module that provides a block?

Edit the block admin page?

chx’s picture

chx commented
Status: Needs review » Needs work

You need to check whether pages is set and only if not then set default -- we want to let people to specify whatever in their hook_block.

chx’s picture

chx commented
Status: Needs work » Reviewed & tested by the community
StatusFileSize
new postgresql_sucks.patch557 bytes
bjaspan’s picture

bjaspan commented
Status: Reviewed & tested by the community » Needs review
StatusFileSize
new block-rehash-pages-190128-8.patch935 bytes

#5 and #7: Fixed. New patch attached.

#6: To test this patch: Use pgsql. Display admin/build/blocks (or call _block_rehash()), removing any obsolete blocks from the db. Enable a new module that defines a block but does not specify a 'pages' value in the hook_block('list') output. Display admin/build/blocks (or call _block_rehash()) again. Without the patch, a query error results; with the patch, it does not. This could be done manually or via simpletest.

bjaspan’s picture

bjaspan commented
Status: Needs review » Reviewed & tested by the community

I guess chx attached his patch while I was rewriting mine. They are functionally identical so it is fair to say we have reviewed each other's patches, so I'll agree with his RTBC. :-)

BTW, in this case I have to say it is mysql that sucks, not pgsql. mysql's "implicit default values" are a horrible idea that leads to sloppy coding and it is mysql, not pgsql, that cannot handle a default value on text columns.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Status: Reviewed & tested by the community » Fixed

Thanks, committed.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

BTW same problem with the log field here: http://drupal.org/node/188462

bjaspan’s picture

bjaspan commented
Component: block.module » documentation
Category: bug » task

I need to document this to the Schema API handbook.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Title: _block_rehash fails on pgsql » Document default value usage in INSERT statements
Priority: Critical » Normal
Status: Fixed » Active

In which case it is not fixed, and not critical anymore.

bjaspan’s picture

bjaspan commented
Status: Active » Fixed

Documented at http://drupal.org/node/190504.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.