[email protected] writes:
>> I get a couple of issues - there are commands that relate to to
>> postgres' which isn't installed by the port (which installs pgsql) -
>> and is easily fixed by defining the role - but the other is baffling.
>
>Can you submit patches, perhaps?
Not sure what to? This was an issue with the output from pg_dump of the
archiveopteryx db on Ubuntu not installing into a fresh postgres
install on FreeBSD. On Ubuntu the default superuser name is 'postgres',
the FreeBSD port uses 'pgsql'.
When the dump says this:
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
its not going to work. I just created the 'postgres' login role on the
new server.
>> I get:
>>
>> psql:archiveopteryx.backup:1316974: ERROR: could not create unique
>> index "addresses_nld_key"
>> DETAIL: Key (name, localpart, lower(domain))=(0amamacint, alert,
>> date.com) is duplicated.
>>
>> Now I can understand getting an error like this when messing around
>> normally using DML, but this is a backup and the index only exists
>> because the original PostgreSQL thought it did.
>>
>> Is this a difference in lower(domain) behaviour between the older
>> Ubuntu and the newer FreeBSD UTF8? Any hint on how to fix?
>
>Baffling indeed.
>
>The constraint is from 2007, but there was another before it,
>practically identical. I am not aware of any changes in how lower()
>works, at least not with ASCII letters.
There were three rows that were indeed duplicated (except for the id
field) as far as the text dump from pg_dump was concerned. I just
deleted the duplicates by id and then I could create the index.
One of the rows had funky unicode characters but the other is quite
plain. Snap from displaying it in nano in putty:
376316 pgsql-hackers-owner+M204959 postgresql.org
376317 0amamacint alert date.com
376318 0amamacint noreply date.com
376319 0amamacint pacificthunder.com
376320 0amamacint alert date.com
376321 0amamacint noreply date.com
376322 pgsql-hackers-owner+M204960 postgresql.org
There is no obvious whitespace difference; the inter-field characters
are a single tab in each case.
Given that the index was only defined in the dump because it existed in
the 9.0 db, I guess there must be something about the text that causes
them to be non-identical in that db but the same in the dump to text.
It is possible that the db has been corrupted but I would have thought
that the symptoms would be much more obvious.
I don't see how this can be an aox bug: its more that I wondered if
anyone has seen anything similar.
Haven't fired up aox on this system except to run installer, which
upgraded the db schema. It does seem that the FreeBSD port (3.1.3)
didn't set up the config files needed and I haven't finished setting the
system up on this box yet.
