Re: problems restoring 7.2.1 dump to 7.3.2

Hi Tom,

> CREATE TABLE zit (
> lastchanged timestamp(13) without time zone
> );

I checked that issue again and compared each table where this error occurs
with each table where the error doesn't occur. Now listing what all the
affected tables have in common:

(1) contain more than one field of the type TIMESTAMP
(2) contain a primary key field generated by sequence
(3) at least on of their TIMESTAMP field is a foreign key for other tables
(4) there are views on the affected table which implicit convert the
timestamp field to another format

However I don't know how those common thing could affect pg_dump. Even
restarted the database without allowing any other connections than mine,
just to be sure it's not a problem coming from table locks or things like
that.

One other thing to mention: I created the test table you mentioned above
with only one single field and this particular table gets dumped
correctly, as gets a table dumped correctly if I recreate it from scratch.

Hummmm... any ideas what else I could check?

> Now, the (13) version is still going to fail in 7.3, because we
> tightened the allowed range of timestamp precisions:
> ERROR: TIMESTAMP(13) precision must be between 0 and 6

So the only difference between TIMESTAMP(13) and TIMESTAMP(6) is the
the precision of the second's fraction, which means it holds only
",xxxxxx" six digits or a maximum precision of 1/999999 second?

> I wonder whether we should reduce that ERROR to a WARNING, and
> substitute the max allowed precision instead of failing out.

[x] ACK - and maybe having a switch to silently convert it on the fly
while reloading the export file.

> As-is, it's going to be painful to load dump files containing
> what had been a perfectly legitimate declaration in 7.2.
> Comments anyone?

It's really painful considering you have to edit it manually after
exporting the database, which is 481 TIMESTAMP fields in my case. Oh man,
did I mention how much I love "sed" and regular expressions? ;-)

Kind regards
... Ralph ... (now on his way to check the -Fc option and pg_restore!)