Thread: pg_dump/pg_restore problems with 7.4.1
I'm trying to migrate from 7.2 to 7.4.1 via pg_dump/pg_restore and have encountered a couple of problems: 1) Index creation on a table fails: db=# CREATE UNIQUE INDEX person_info_username_ix ON person_info USING btree (username); ERROR: could not create unique index DETAIL: Table contains duplicated values. Of course, this index existed in the 7.2 DB, so it seems odd that it should not be possible to recreate. Furthermore, a search for duplicate values fails: db=# select username, count(*) from person_info group by username having count(*) > 1; username | count ----------+------- (0 rows) Is this a known issue or possibly a bug? 2) While importing I get: "ERROR: invalid memory alloc request size 1073741824". I tracked this down to a line with some non-ASCII characters. The type of the new database is LATIN1, and these are valid LATIN1 characters. I googled for the error message and found a bug report that sounds like the same problem: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg07363.html Anyone know if this was confirmed as a bug, and whether it's being looked into? Thanks. Ben Ben __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
Ben Marklein <the_pharcyde@yahoo.com> writes:
> I'm trying to migrate from 7.2 to 7.4.1 via
> pg_dump/pg_restore and have encountered a couple of
> problems:
> 1) Index creation on a table fails:
> db=# CREATE UNIQUE INDEX person_info_username_ix ON
> person_info USING btree (username);
> ERROR: could not create unique index
> DETAIL: Table contains duplicated values.
> Of course, this index existed in the 7.2 DB, so it
> seems odd that it should not be possible to recreate.
That's fairly interesting. It could be an issue of strings that are
equal under your current locale but weren't equal before ... but why
wouldn't the GROUP BY search find those duplicates too? I agree this
sounds like a bug. Can you provide test data to duplicate the problem?
> 2) While importing I get: "ERROR: invalid memory
> alloc request size 1073741824". I tracked this down
> to a line with some non-ASCII characters.
I have been trying to reproduce a recent report of this, without
success. Again, a test case would be of great value.
Also, what is your platform exactly, what locale settings, etc? Did
you build Postgres yourself (with what config settings) or use an RPM
(whose)?
regards, tom lane
I can send you the test data, but it's confidential so I'll need to ask that you handle it carefully. Can you contact me off-list about this? I tried to send to your account directly but was bounced by your spam filter from both this and another account. Ben --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ben Marklein <the_pharcyde@yahoo.com> writes: > > I'm trying to migrate from 7.2 to 7.4.1 via > > pg_dump/pg_restore and have encountered a couple > of > > problems: > > > 1) Index creation on a table fails: > > > db=# CREATE UNIQUE INDEX person_info_username_ix > ON > > person_info USING btree (username); > > ERROR: could not create unique index > > DETAIL: Table contains duplicated values. > > > Of course, this index existed in the 7.2 DB, so it > > seems odd that it should not be possible to > recreate. > > That's fairly interesting. It could be an issue of > strings that are > equal under your current locale but weren't equal > before ... but why > wouldn't the GROUP BY search find those duplicates > too? I agree this > sounds like a bug. Can you provide test data to > duplicate the problem? > > > 2) While importing I get: "ERROR: invalid memory > > alloc request size 1073741824". I tracked this > down > > to a line with some non-ASCII characters. > > I have been trying to reproduce a recent report of > this, without > success. Again, a test case would be of great > value. > > Also, what is your platform exactly, what locale > settings, etc? Did > you build Postgres yourself (with what config > settings) or use an RPM > (whose)? > > regards, tom lane __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
Hope you got this fixed. We have never heard of similar problems with upgrades --- other problems, yea, but not these. :-) --------------------------------------------------------------------------- Ben Marklein wrote: > I'm trying to migrate from 7.2 to 7.4.1 via > pg_dump/pg_restore and have encountered a couple of > problems: > > 1) Index creation on a table fails: > > db=# CREATE UNIQUE INDEX person_info_username_ix ON > person_info USING btree (username); > ERROR: could not create unique index > DETAIL: Table contains duplicated values. > > Of course, this index existed in the 7.2 DB, so it > seems odd that it should not be possible to recreate. > Furthermore, a search for duplicate values fails: > > db=# select username, count(*) from person_info group > by username having count(*) > 1; > username | count > ----------+------- > (0 rows) > > Is this a known issue or possibly a bug? > > 2) While importing I get: "ERROR: invalid memory > alloc request size 1073741824". I tracked this down > to a line with some non-ASCII characters. The type of > the new database is LATIN1, and these are valid LATIN1 > characters. I googled for the error message and found > a bug report that sounds like the same problem: > > http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg07363.html > > Anyone know if this was confirmed as a bug, and > whether it's being looked into? Thanks. > > Ben > > > Ben > > > > __________________________________ > Do you Yahoo!? > Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes > http://hotjobs.sweepstakes.yahoo.com/signingbonus > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Ben Marklein wrote: > I'm trying to migrate from 7.2 to 7.4.1 via > pg_dump/pg_restore and have encountered a couple of > problems: > > 1) Index creation on a table fails: > > db=# CREATE UNIQUE INDEX person_info_username_ix ON > person_info USING btree (username); > ERROR: could not create unique index > DETAIL: Table contains duplicated values. > > Of course, this index existed in the 7.2 DB, so it > seems odd that it should not be possible to recreate. > Furthermore, a search for duplicate values fails: > > db=# select username, count(*) from person_info group > by username having count(*) > 1; > username | count > ----------+------- > (0 rows) > > Is this a known issue or possibly a bug? Not sure. Never heard of this before. > 2) While importing I get: "ERROR: invalid memory > alloc request size 1073741824". I tracked this down > to a line with some non-ASCII characters. The type of > the new database is LATIN1, and these are valid LATIN1 > characters. I googled for the error message and found > a bug report that sounds like the same problem: > > http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg07363.html > > Anyone know if this was confirmed as a bug, and > whether it's being looked into? Thanks. Sounds like the same problem, but I don't remember the solution. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I like how postgres backends modify the 'command' display in a (unix) process listing to show what database they are connected to, and the query being executed. Has any thought been put into being able to make this configurable per backend connection (maybe via a SET variable)? I'm thinking it would be cool if a user process could provide its own status there, identifying application-specific information. Similar to how Java let's you set the "name" of a Thread (which sadly doesn't appear in a process listing, but oh well). I realize this could open up some security concerns by allowing us to expose more information than we should, but for those of us that want to shoot ourselves in the foot, it might be a useful feature. Any thoughts? eric
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Ben Marklein wrote:
>> db=# CREATE UNIQUE INDEX person_info_username_ix ON
>> person_info USING btree (username);
>> ERROR: could not create unique index
>> DETAIL: Table contains duplicated values.
>>
>> Of course, this index existed in the 7.2 DB, so it
>> seems odd that it should not be possible to recreate.
> Not sure. Never heard of this before.
It seems odd to me too. Is it possible that the new database uses a
different character set encoding and/or different locale, such that
two strings previously considered unequal are now equal? (Of course,
you'd think the count(*) check would find any such cases...)
>> 2) While importing I get: "ERROR: invalid memory
>> alloc request size 1073741824". I tracked this down
>> to a line with some non-ASCII characters.
> Sounds like the same problem, but I don't remember the solution.
I think this is this problem:
2004-01-17 21:15 tgl
* src/backend/commands/: copy.c (REL7_4_STABLE), copy.c: Don't use
%s-with-precision format spec to truncate data being displayed in a
COPY error message. It seems that glibc gets indigestion if it is
asked to truncate strings that contain invalid UTF-8 encoding
sequences. vsnprintf will return -1 in such cases, leading to
looping and eventual memory overflow in elog.c. Instead use our
own, more robust pg_mbcliplen routine. I believe this problem
accounts for several recent reports of unexpected 'out of memory'
errors during COPY IN.
Until we get around to issuing a 7.4.2, the easiest solution is to fix
the data to match the database encoding (or vice versa). You'd actually
have to do that anyway --- the bug is merely that you get a useless
error message instead of a helpful one when the incoming data is bogus
according to the database encoding.
regards, tom lane
Eric Ridge wrote: > I like how postgres backends modify the 'command' display in a (unix) > process listing to show what database they are connected to, and the > query being executed. > > Has any thought been put into being able to make this configurable per > backend connection (maybe via a SET variable)? I'm thinking it would > be cool if a user process could provide its own status there, > identifying application-specific information. Similar to how Java > let's you set the "name" of a Thread (which sadly doesn't appear in a > process listing, but oh well). > > I realize this could open up some security concerns by allowing us to > expose more information than we should, but for those of us that want > to shoot ourselves in the foot, it might be a useful feature. No one really has thought of that before. We could do it, though there are admin reasons for restricting that ability. If we said only superusers could change it, it wouldn't be very useful. It would be cool if SET could change it, but it seems that would make it pretty useless for administrator usage. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Feb 11, 2004, at 10:00 PM, Bruce Momjian wrote: > No one really has thought of that before. We could do it, though there > are admin reasons for restricting that ability. If we said only > superusers could change it, it wouldn't be very useful. That's a good point. > It would be cool if SET could change it, but it seems that would make > it pretty > useless for administrator usage. Ran into a situation yesterday where all connections were exhausted on a development database, and thanks to our nat-ing firewall, couldn't tell where all the connections were coming from. It made me think that intelligently mucking with the ps output might have made things easier for me to find the person to yell at. One could just as easily report info like "real" client ip, client application state, etc, to a table, but having that stuff via 'ps' just seemed like a cool idea. In addition, some of our applications have a few background threads that maintain persistent connections to the database. Being able to logically label those processes would make it easier to identify which backend processes are still connected. eric
Eric Ridge wrote: > On Feb 11, 2004, at 10:00 PM, Bruce Momjian wrote: > > No one really has thought of that before. We could do it, though there > > are admin reasons for restricting that ability. If we said only > > superusers could change it, it wouldn't be very useful. > > That's a good point. > > > It would be cool if SET could change it, but it seems that would make > > it pretty > > useless for administrator usage. > > Ran into a situation yesterday where all connections were exhausted on > a development database, and thanks to our nat-ing firewall, couldn't > tell where all the connections were coming from. It made me think that > intelligently mucking with the ps output might have made things easier > for me to find the person to yell at. > > One could just as easily report info like "real" client ip, client > application state, etc, to a table, but having that stuff via 'ps' just > seemed like a cool idea. > > In addition, some of our applications have a few background threads > that maintain persistent connections to the database. Being able to > logically label those processes would make it easier to identify which > backend processes are still connected. Well, let's see if someone else like the feature because adding it might reduce its usability as a guaranteed value for admins. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Feb 12, 2004, at 11:54 AM, Bruce Momjian wrote: > Well, let's see if someone else like the feature because adding it > might > reduce its usability as a guaranteed value for admins. Maybe making the ability to do this a configuration option (off by default, of course) would make everyone happy? eric
On Wed, 2004-02-11 at 15:12, Bruce Momjian wrote: > > 2) While importing I get: "ERROR: invalid memory > > alloc request size 1073741824". I tracked this down > > to a line with some non-ASCII characters. The type of > > the new database is LATIN1, and these are valid LATIN1 > > characters. I googled for the error message and found > > a bug report that sounds like the same problem: We saw this behavior on one of several development boxes we converted to 7.4.1. We tracked it down to some 8 bit ascii in a particular table.