Thread: 7.1->7.2 data import timestamp problems
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
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
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
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
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
> 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
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
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
"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
> 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
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
> [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
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
>> [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
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
"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