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!)