Thread: 7.1->7.2 data import timestamp problems

7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
I'm importing the data from my 7.1.2 database into 7.2 and I've found
a few problems.

The first one was:

ERROR: Bad timestamp external representation 'current_timestamp'

I fixed that problem by removing the quotes within the input data.

The next problem seems slightly harder to fix.

$ psql fp2migration < fp2migration.sql.timestamp.library
[snip]
ERROR:  copy: line 2537, Bad timestamp external representation 'current'
lost synchronization with server, resetting connection
^C

Any clues as to what will fix this problem?
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
Peter Eisentraut
Date:
Dan Langille writes:

> $ psql fp2migration < fp2migration.sql.timestamp.library
> [snip]
> ERROR:  copy: line 2537, Bad timestamp external representation 'current'
> lost synchronization with server, resetting connection
> ^C
>
> Any clues as to what will fix this problem?

Quoth the release notes:

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

--
Peter Eisentraut   peter_e@gmx.net


Re: 7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
On 23 Feb 2002 at 12:19, Peter Eisentraut wrote:

> Dan Langille writes:
>
> > $ psql fp2migration < fp2migration.sql.timestamp.library
> > [snip]
> > ERROR:  copy: line 2537, Bad timestamp external representation 'current'
> > lost synchronization with server, resetting connection ^C
> >
> > Any clues as to what will fix this problem?
>
> Quoth the release notes:
>
>      * The date/time value 'current' is no longer available. You will need
>      to
>        rewrite your applications.

Thank you.  This isn't an application.  It is data dumped from 7.1.2.  How
are we to upgrade if we can't import our data from previous versions?
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
On 23 Feb 2002 at 0:59, Dan Langille wrote:

> I'm importing the data from my 7.1.2 database into 7.2 and I've found a few
> problems.

[snip]

> $ psql fp2migration < fp2migration.sql.timestamp.library
> [snip]
> ERROR:  copy: line 2537, Bad timestamp external representation 'current'
> lost synchronization with server, resetting connection ^C

I've just been able to confirm that 7.1.2 can import the above data.  My
plans for 7.2 are now on hold while I try to find a way to get 7.2 to
accept the data that has been exported from 7.1.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
Peter Eisentraut
Date:
Dan Langille writes:

> >      * The date/time value 'current' is no longer available. You will need
> >      to
> >        rewrite your applications.
>
> Thank you.  This isn't an application.

If it isn't, then what do you need the data for?  ;-)

> It is data dumped from 7.1.2.  How are we to upgrade if we can't
> import our data from previous versions?

You can't.  You need to adjust your application first to do without the
'current' value, then fix your data, then upgrade.

--
Peter Eisentraut   peter_e@gmx.net


Re: 7.1->7.2 data import timestamp problems

From
Sean Chittenden
Date:
> I've just been able to confirm that 7.1.2 can import the above data.  My
> plans for 7.2 are now on hold while I try to find a way to get 7.2 to
> accept the data that has been exported from 7.1.

perl -p -i -e 's#current#now#igo' pgsql_dump.sql

Double check that you don't have the word 'current' in your dump other
than timestamps.

grep -i current pgsql_dump.sql

HTH  -sc

--
Sean Chittenden

Re: 7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
On 23 Feb 2002 at 11:22, Sean Chittenden wrote:

> > I've just been able to confirm that 7.1.2 can import the above data.  My
> > plans for 7.2 are now on hold while I try to find a way to get 7.2 to
> > accept the data that has been exported from 7.1.
>
> perl -p -i -e 's#current#now#igo' pgsql_dump.sql
>
> Double check that you don't have the word 'current' in your dump other than
> timestamps.
>
> grep -i current pgsql_dump.sql
>
> HTH  -sc

Sean: you are on the same track I am.  Yes, I found 9 such values in a
timestamp field.  I corrected the data, exported, and imported into 7.2.
All went well[1].

Now my concern is how the value 'current' found its way into a timestamp
field....  I'm looking at the application.

thank you.

[1] - I did have to manually modify the pg_dump output to remove the
quotes from around current_timestamp (as mentioned elsewhere).
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
On 23 Feb 2002 at 14:28, Dan Langille wrote:

> On 23 Feb 2002 at 11:22, Sean Chittenden wrote:
>
> > > I've just been able to confirm that 7.1.2 can import the above data.  My
> > > plans for 7.2 are now on hold while I try to find a way to get 7.2 to
> > > accept the data that has been exported from 7.1.
> >
> > perl -p -i -e 's#current#now#igo' pgsql_dump.sql
> >
> > Double check that you don't have the word 'current' in your dump other than
> > timestamps.
> >
> > grep -i current pgsql_dump.sql
> >
> > HTH  -sc
>
> Sean: you are on the same track I am.  Yes, I found 9 such values in a
> timestamp field.  I corrected the data, exported, and imported into 7.2.
> All went well

I spoke too soon:

now I'm getting:  ERROR: SearchSysCache: Bad cache id 27

But I'll try to track down more and see.  Just a heads up in case this
raises any concerns with anyone.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
Tom Lane
Date:
"Dan Langille" <dan@langille.org> writes:
> now I'm getting:  ERROR: SearchSysCache: Bad cache id 27

I believe we've seen this from trying to load a 7.1 plpgsql.so
into 7.2.

            regards, tom lane

Re: 7.1->7.2 data import timestamp problems

From
Bruce Momjian
Date:
> Sean: you are on the same track I am.  Yes, I found 9 such values in a
> timestamp field.  I corrected the data, exported, and imported into 7.2.
> All went well[1].
>
> Now my concern is how the value 'current' found its way into a timestamp
> field....  I'm looking at the application.

I have seen other reports of this.  Did anyone realized 'current' could
be stored in the database?  I remember discussion about it but I didn't
realize anyone actually used it.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: 7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
On 23 Feb 2002 at 14:50, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> > now I'm getting:  ERROR: SearchSysCache: Bad cache id 27
>
> I believe we've seen this from trying to load a 7.1 plpgsql.so
> into 7.2.

Tom!  Well done.  Thank you.  That was the problem.[1] After correcting
that, I started to get:

   ERROR:  fmgr_info: function 944463: cache lookup failed

I tried to the just reload the stored procedures, but had to reload the
entire db to fix that one.

Much appreciated.

[1] - the change I made: this

    CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
    '/usr/local/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';

became

    CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
    '/usr/local/lib/postgresql/plpgsql.so', 'plpgsql_call_handler'
    LANGUAGE 'C';

which is the default location under the latest FreeBSD port.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
Sean Chittenden
Date:
> [1] - the change I made: this
>
>     CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
>     '/usr/local/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';
>
> became
>
>     CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
>     '/usr/local/lib/postgresql/plpgsql.so', 'plpgsql_call_handler'
>     LANGUAGE 'C';
>
> which is the default location under the latest FreeBSD port.

I've run into this a few times and have talked with the maintainer and
think that a note regarding this change may come in future versions of
the pgsql port.

Real quick, FreeBSD is currently building with MIT kerberos support
out of the box.  Are there any plans to merge in the Heimdal kerberos
patches?  -sc

--
Sean Chittenden

Re: 7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
On 23 Feb 2002 at 12:18, Sean Chittenden wrote:

> Real quick, FreeBSD is currently building with MIT kerberos support
> out of the box.  Are there any plans to merge in the Heimdal kerberos
> patches?  -sc

I don't know.  I just asked around on IRC... I guess everyone is watching
the Belarus/Russia game... ;)
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
Tom Lane
Date:
>> [1] - the change I made: this
>>
>> CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
>> '/usr/local/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';
>>
>> became
>>
>> CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
>> '/usr/local/lib/postgresql/plpgsql.so', 'plpgsql_call_handler'
>> LANGUAGE 'C';
>>
>> which is the default location under the latest FreeBSD port.

> I've run into this a few times and have talked with the maintainer and
> think that a note regarding this change may come in future versions of
> the pgsql port.

Actually, under 7.2 the preferred definition is just

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
'plpgsql', 'plpgsql_call_handler' LANGUAGE 'C';

It's no longer necessary to supply a full path to the shlib, and
in fact it's best to let the shlib loader fill in the default path.
That should prevent future occurrences of this sort of problem.
Almost certainly, the reason you got burnt to begin with was that
the dumped definition of plpgsql_call_handler contained the full
path to the *old* installation's plpgsql.so --- am I not right?
We got smart about that at last, and now there is a search path
for shlibs in the loader code.  But it won't help you unless you
use it.

> Are there any plans to merge in the Heimdal kerberos patches?  -sc

Peter said he committed it yesterday...

            regards, tom lane

Re: 7.1->7.2 data import timestamp problems

From
"Dan Langille"
Date:
On 23 Feb 2002 at 16:06, Tom Lane wrote:

> Actually, under 7.2 the preferred definition is just
>
> CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
> 'plpgsql', 'plpgsql_call_handler' LANGUAGE 'C';

Is there a correct way to replace such definitions?  I tried just a drop
function and then a create function, but:

ERROR:  fmgr_info: function 16557: cache lookup failed

> Almost certainly, the reason you got burnt to begin with was that
> the dumped definition of plpgsql_call_handler contained the full
> path to the *old* installation's plpgsql.so --- am I not right?

You are correct.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: 7.1->7.2 data import timestamp problems

From
Tom Lane
Date:
"Dan Langille" <dan@langille.org> writes:
> Is there a correct way to replace such definitions?  I tried just a drop
> function and then a create function, but:

> ERROR:  fmgr_info: function 16557: cache lookup failed

CREATE OR REPLACE FUNCTION (new in 7.2) would do the trick.

            regards, tom lane