Thread: schema error upgrading from 7.1 to 7.2

During the upgrade from 7.1.3 to 7.2, I encountered an incompatibility
with the schema defs.

Prior to the upgrade, I used the 7.1.3 pg_dump program to create a
compressed dump:

pg_dump -Fc vk > vk.dump

then, using the 7.2 pg_restore, I exctracted the table schema
definitions:

pg_restore -l vk.dump >vk.1
edit vk.1 to just extract TABLE defs and ACLs (everything prior to
 DATA parts)
pg_restore -L vk.1 vk.dump > vk.schema
psql vk < vk.schema

results in the complaint about 'CURRENT_DATE' as shown in the boiled
down example below.  The line it complains about from the schema is

 "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL,

The fix seems to be to remove the single quotes around CURRENT_DATE.
pg_restore should be taught this, I think.

pg_dumpall from 7.1.3 creates the same (now invalid) schema too.

This is not documented in the list of changes to the Schema
Manipulation.



A boiled down example:

khera=> create table foo ("owner_id" integer not null, "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL);
ERROR:  Bad date external representation 'CURRENT_DATE'
khera=> create table foo ("owner_id" integer not null, "owner_lastbilled" date DEFAULT CURRENT_DATE NOT NULL);
CREATE
khera=> \d foo
                           Table "foo"
      Column      |  Type   |             Modifiers
------------------+---------+------------------------------------
 owner_id         | integer | not null
 owner_lastbilled | date    | not null default date('now'::text)

khera=> insert into foo (owner_id) values (1);
INSERT 16966 1
khera=> select * from foo;
 owner_id | owner_lastbilled
----------+------------------
        1 | 2002-02-20
(1 row)

khera=> select version();
                              version
-------------------------------------------------------------------
 PostgreSQL 7.2 on i386-unknown-freebsd4.4, compiled by GCC 2.95.3
(1 row)

Vivek Khera <khera@kcilink.com> writes:
>  "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL,

The above was never correct.  I believe that 7.1's rather lax date
parser might have interpreted the literal as being 'current'.  7.2's
date parser would reject it, even if 'current' were still an allowed
value, which it is not.

On the other hand, invoking the function CURRENT_DATE

    "owner_lastbilled" date DEFAULT CURRENT_DATE NOT NULL,

was and remains valid.

> This is not documented in the list of changes to the Schema
> Manipulation.

Because it is not one: it is a datatype behavioral change.

            regards, tom lane

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> Vivek Khera <khera@kcilink.com> writes:
>> "owner_lastbilled" date DEFAULT 'CURRENT_DATE' NOT NULL,

TL> The above was never correct.  I believe that 7.1's rather lax date
TL> parser might have interpreted the literal as being 'current'.  7.2's

Then how come pg_dump outputs it that way?  Is it because that's how I
did it when creating the schema in the first place?

I guess I extended putting the quotes around that because of the
warnings about putting quotes around 'NOW()' as a default.  My
mistake...

TL> Because it is not one: it is a datatype behavioral change.

It isn't documented in the HISTORY file in any way shape or form.
Where else should I look for potential traps in validating my app
under 7.2?

Thanks.

Vivek Khera <khera@kcilink.com> writes:
> Then how come pg_dump outputs it that way?  Is it because that's how I
> did it when creating the schema in the first place?

Presumably.

> TL> Because it is not one: it is a datatype behavioral change.

> It isn't documented in the HISTORY file in any way shape or form.

HISTORY says, under "Migration to version 7.2":

     * The date/time value 'current' is no longer available. You will need to
       rewrite your applications.

            regards, tom lane

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> HISTORY says, under "Migration to version 7.2":

TL>      * The date/time value 'current' is no longer available. You
TL>        will need to rewrite your applications.

I see now.  Thanks.  I didn't make the connection between "current"
and "CURRENT_DATE".