DB table creation fails on Postgres [#384944]

I belive the approach to install is done without using drupal database abstraction. this occurs for me on drupal 6.10 on apache 2 php 5 postgres 8.3 the module installs with errors pertaining to bad postrgres syntax:



    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "offset" LINE 15: offset int_unsigned NOT NULL default 0, ^ in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: CREATE TABLE node_import_tasks ( taskid serial CHECK (taskid >= 0), name varchar(64) NOT NULL default '', uid int_unsigned NOT NULL default 0, created int_unsigned NOT NULL default 0, changed int_unsigned NOT NULL default 0, fid int_unsigned NOT NULL default 0, has_headers int_unsigned NOT NULL default 1, file_options text NOT NULL, headers text NOT NULL, type varchar(64) NOT NULL default '', map text NOT NULL, defaults text NOT NULL, options text NOT NULL, offset int_unsigned NOT NULL default 0, row_done int_unsigned NOT NULL default 0, row_error int_unsigned NOT NULL default 0, status int_unsigned NOT NULL default 0, PRIMARY KEY (taskid) ) in /var/www/sparcs/includes/database.inc on line 515.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "offset" LINE 3: offset int_unsigned NOT NULL default 0, ^ in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: CREATE TABLE node_import_status ( taskid int_unsigned NOT NULL default 0, offset int_unsigned NOT NULL default 0, errors text NOT NULL, objid int_unsigned NOT NULL default 0, status int_unsigned NOT NULL default 0, PRIMARY KEY (taskid, offset) ) in /var/www/sparcs/includes/database.inc on line 515.

Comment	File	Size	Author
#8	node_import-6.x.1-0-rc4-pgsql.tar_.gz	94.46 KB	davidwhthomas

Comments

Comment #1

Robrecht Jacques commented 26 February 2009 at 20:38

Title:

Does not work with postgres

» DB table creation fails on Postgres

I am using the Drupal database abstraction (see node_import.install).

Seems "offset" is a reserved word for Postgres (see http://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html) and not in standard SQL.

I'll try to find a solution. This may be a bug in the postgres abstraction layer (allowing use of the "offset" word). Normally you could create the table by quoting the "offset" column, eg (at the top of my head):

CREATE TABLE node_import_tasks (
taskid serial CHECK (taskid >= 0),
name varchar(64) NOT NULL default '',
uid int_unsigned NOT NULL default 0,
created int_unsigned NOT NULL default 0,
changed int_unsigned NOT NULL default 0,
fid int_unsigned NOT NULL default 0,
has_headers int_unsigned NOT NULL default 1,
file_options text NOT NULL, headers text NOT NULL,
type varchar(64) NOT NULL default '',
map text NOT NULL, defaults text NOT NULL,
options text NOT NULL,
`offset` int_unsigned NOT NULL default 0,
row_done int_unsigned NOT NULL default 0,
row_error int_unsigned NOT NULL default 0,
status int_unsigned NOT NULL default 0,
PRIMARY KEY (taskid)
)

I'm not running Postgres regulary, so I'll need to investigate a bit more. Maybe by just renaming the column.

Comment #2

spydmobile commented 26 February 2009 at 23:44

Sorry, was not trying to imply anything negative, it was just an Off the head thought, This module could play a critical role in my drupal solution, and I would be glad to test your ideas on my postgres based drupal systems, let me know how you want to proceed...
Franco

Comment #3

spydmobile commented 26 February 2009 at 23:47

More info, I just did a test here and created a table with a cloumn named offset with no problems.

-- Column: "offset"

-- ALTER TABLE test.tester DROP COLUMN "offset";

ALTER TABLE test.tester ADD COLUMN "offset" integer;
ALTER TABLE test.tester ALTER COLUMN "offset" SET STORAGE PLAIN;
ALTER TABLE test.tester ALTER COLUMN "offset" SET DEFAULT 0;

Comment #4

Robrecht Jacques commented 27 February 2009 at 08:05

I didn't interpret it as negative.

I think the reason why you can create the "offset" column in your test is because you have quoted the "offset" column. Eg, could you try:

-- Column: "offset"

-- ALTER TABLE test.tester DROP COLUMN offset;

ALTER TABLE test.tester ADD COLUMN offset integer;
ALTER TABLE test.tester ALTER COLUMN offset SET STORAGE PLAIN;
ALTER TABLE test.tester ALTER COLUMN offset SET DEFAULT 0;

so without the quotes.

If it works without quotes, the problem lies somewhere else.

Comment #5

spydmobile commented 27 February 2009 at 15:49

again more info, when I run the abov query I get the error, but It is about the quotes I think, not the name offset.


ERROR:  syntax error at or near "`"
LINE 13: `offset` int_unsigned NOT NULL default 0,
         ^

********** Error **********

ERROR: syntax error at or near "`"
SQL state: 42601
Character: 463

changing the quotes to double quotes allowed the word offset but then it did not like the data type. This might be related to changes in postgres syntax between 8.1 and 8.3

Comment #6

Robrecht Jacques commented 9 April 2009 at 10:09

Status:

Active

» Fixed

Fixed in CVS by renaming the "offset" column to "file_offset". You will need to run update.php. Will be included in -rc5. On PGSQL you'll need to reinstall the module.