PostreSQL error upgrading from 0.11.1 to 0.12 on table attachment

[joao.antunes@…]

when executing the step:

trac-admin /path/to/env upgrade

I get the following error:

ProgrammingError: syntax error at or near "TYPE"
LINE 1: ALTER TABLE attachment ALTER COLUMN time TYPE bigint
                                                 ^

Even when running the above command with the —no-backup directive doesn't help

[joao.antunes@…]

*correction:

Even when running the above command with the —no-backup directive doesn't help

[Remy Blank]

Could you please check that your PostgreSQL installation supports the BIGINT type? This may be configurable at compile-time. Some more information about your OS, DB version, Python DB bindings would also be welcome.

[anonymous]

Hi!

I confirmed that my postgreSQL supports the BigInt type by trying to create a table with a bigint, more specifically I used the following commands:

CREATE TABLE bigintexample (

id bigint, CONSTRAINT pk_bigintexample_id PRIMARY KEY (id)

);

INSERT INTO bigintexample (id) VALUES (100000349053153);

Which I saw that worked by doing a SELECT which showed me the value I inserted.

My OS version is:

Linux version 2.6.26-2-amd64 (Debian 2.6.26-15) (dannf@…) (gcc version 4.1.3 20080704 (prerelease) (Debian 4.1.2-25)) #1 SMP Fri Mar 27 04:02:59 UTC 2009

The PostgreSQL version is: 8.3.7-0lenny1

How can I check the rest of the info that you required (the Python DB bindings) ?

Thanks in advance

[Remy Blank]

Replying to anonymous:

How can I check the rest of the info that you required (the Python DB bindings) ?

You can find that on the "About Trac" page, the bindings are called psycopg2.

I wonder if the error isn't due to the "time" column name, maybe it's a reserved word for SQL. Could you please try the following:

CREATE TABLE example
(
    time int,
    CONSTRAINT example_pk PRIMARY KEY (time)
);
ALTER TABLE example ALTER COLUMN time TYPE bigint;

Mmh… Works here, with PostgreSQL 8.3.12.

[joao.antunes@…]

Replying to rblank:

Replying to anonymous:

How can I check the rest of the info that you required (the Python DB bindings) ?

You can find that on the "About Trac" page, the bindings are called psycopg2.

Here it is (the psycopg2 and more): — System Information

Trac: 0.11.1 Python: 2.5.2 (r252:60911, Jan 4 2009, 22:17:10) [GCC 4.3.2] setuptools: 0.6c8 psycopg2: 2.0.7 Genshi: 0.6 mod_python: 3.3.1 Pygments: 0.10 Subversion: 1.5.1 (r32289) jQuery: 1.2.6 —

I wonder if the error isn't due to the "time" column name, maybe it's a reserved word for SQL. Could you please try the following:

CREATE TABLE example
(
    time int,
    CONSTRAINT example_pk PRIMARY KEY (time)
);
ALTER TABLE example ALTER COLUMN time TYPE bigint;

Mmh… Works here, with PostgreSQL 8.3.12.

Sure i can, I'm glad to. So, I guess that you were on to something, basicly, I could create the table but when I tried to alter it I had an error. Output:

ALTER TABLE example ALTER COLUMN time TYPE bigint;
ERROR:  syntax error at or near "TYPE" at character 39

It seems that this is the problem!

[Remy Blank]

And does the following work?

ALTER TABLE example ALTER COLUMN "time" TYPE bigint;

If it does, it's definitely due to the column name. Strangely, the PostgreSQL documentation mentions that "time" isn't reserved, an says "non-reserved (cannot be function or type)". We can probably avoid the issue with the following patch:

trac/db/postgres_backend.py

@@ -143 +143 @@
                 alterations.append((name, to))
         if alterations:
             yield "ALTER TABLE %s %s" % (table,
-                ', '.join("ALTER COLUMN %s TYPE %s" % each
+                ', '.join('ALTER COLUMN "%s" TYPE %s' % each
                           for each in alterations))
 
     def backup(self, dest_file):

But still, why is it working here, and not for you? And why does the issue only pop up with ALTER TABLE and not with other statements?

[joao.antunes@…]

Replying to rblank:

And does the following work?

ALTER TABLE example ALTER COLUMN "time" TYPE bigint;

I'm afraid it doesn't work, output:

dbname=# \d example
    Table "public.example"
 Column |  Type   | Modifiers 
--------+---------+-----------
 time   | integer | not null
Indexes:
    "example_pk" primary key, btree ("time")

dbname=# ALTER TABLE example ALTER COLUMN "time" TYPE bigint;
ERROR:  syntax error at or near "TYPE" at character 41

If it does, it's definitely due to the column name. Strangely, the PostgreSQL documentation mentions that "time" isn't reserved, an says "non-reserved (cannot be function or type)". We can probably avoid the issue with the following patch:

trac/db/postgres_backend.py

@@ -143 +143 @@
                 alterations.append((name, to))
         if alterations:
             yield "ALTER TABLE %s %s" % (table,
-                ', '.join("ALTER COLUMN %s TYPE %s" % each
+                ', '.join('ALTER COLUMN "%s" TYPE %s' % each
                           for each in alterations))
 
     def backup(self, dest_file):

But still, why is it working here, and not for you? And why does the issue only pop up with ALTER TABLE and not with other statements?

Dunno, tried to search for the time word in here: ​http://www.postgresql.org/docs/8.3/static/release-8-3.html but found nothing related with this.

I'm afraid that because the test didn't work the patch won't as well.

[Remy Blank]

So the column name wasn't the issue. The syntax error really points to the "TYPE" keyword. The ALTER TABLE ... ALTER COLUMN ... TYPE ... syntax was introduced in 8.0. Are you absolutely sure that your PostgreSQL server is running 8.x? This error would typically occur if the server is running 7.x.

[anonymous]

Replying to rblank:

So the column name wasn't the issue. The syntax error really points to the "TYPE" keyword. The ALTER TABLE ... ALTER COLUMN ... TYPE ... syntax was introduced in 8.0. Are you absolutely sure that your PostgreSQL server is running 8.x? This error would typically occur if the server is running 7.x.

You are right! I'm terribly sorry about the unintentional misleading, aptitude does show me version 8.3-lenny[etc] installed but running a

select version(); shows:

                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.19 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

[Remy Blank]

There you go. I couldn't find any indication in our documentation as to the minimum version of PostgreSQL that we require. So we need to either document a minimum requirement of 8.0, or change the way we alter column types.

[Thijs Triemstra]

Replying to rblank:

There you go. I couldn't find any indication in our documentation as to the minimum version of PostgreSQL that we require. So we need to either document a minimum requirement of 8.0, or change the way we alter column types.

No reason why that documentation change shouldn't be in 0.12-stable then..?

[anonymous]

Anyway, PostgreSQL 7.x is not supported anymore in the PostgreSQL community:

​http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

[Remy Blank]

Simple doc fix, should be in 0.12.2. I'll do that.

[Christian Boos]

Hope you don't mind, I just did it before preparing the .rc1.

[Remy Blank]

Replying to cboos:

Hope you don't mind, I just did it before preparing the .rc1.

No worries, sorry I didn't find the time yesterday evening.