postgresql 8.4.1 running very slow after converting to UTF8

The same database was running fine in ASCII, but now it is taking up  
90% CPU doing a few sum and select.  I tried VACUMM but it didn't  
help.  Dumping the database back to ASCII makes it go faster.  Any ideas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Re: Strange message followed by server crash

"Donald Fraser" <postgres <at> kiwi-fraser.net> writes:
> Log messages:
> <1Tmenteshvili 10709 2006-08-24 17:48:19 BST 0> ERROR:  invalid =
> message format
> < 3670 2006-08-24 17:48:19 BST > LOG:  server process (PID 10709) was =
> terminated by signal 11

> At the same time, an engineer, who thought nobody was in the =
> building, was working on the network and changing a network switch.

I tried to reproduce this by using gdb to force the "invalid message
format" failure --- that is, deliberately changing one of the values 
pq_getmsgend compares.  No luck, the backend issued the error and
continued just as it should.  My best guess now that the crash was not
directly related to that error, but was caused by insufficiently robust
processing of whatever garbage was received following that.  However,
with no clue what that garbage might have looked like, there's too many
possible code paths to try to chase through with no leads.

Anyone care to do some random-data stress testing, ie, connect to a
backend and squirt random data at it to see if you can make it crash
rather than just complain and disconnect?  Be prepared to show a backend
stack trace and the exact data sent if you succeed.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Re: problem with initlocation

Tom Lane wrote:

>"Josef J. Micka" <postgres <at> maylo.cz> writes:
>  
>
>>i want to make database in another place and i'm unable to use 
>>initlocation, and i don't know, what i'm doing wrong.
>>    
>>
>
>  
>
>>this is what i do, and i think it's correct.
>>    
>>
>
>  
>
>>www:/home/db/postgres$ su postgres
>>    
>>
>
>Try "su - postgres" ... plain su retains root's environment.
>
>			regards, tom lane
>  
>
thanks, that works
i'm using su only sometimes, and don't know about this apearance. (never 
had problems with "su" becausy mostly i'm running it from non-privileged 

Re: pg_dump: schema with OID 16396 does not exist

Hi Tom,

deleted entry from pg_conversion. Problem solved. pg_dump works again.

Thanks,
Robert

Tom Lane wrote:

>>>Look through the system catalogs to find the object(s) that claim to be
>>>in that namespace, and then tell us what they are and their history.
>>>      
>>>
>
>  
>
>>Found it in pg_conversion:
>>    
>>
>
>  
>
>>mps_dev=#  select * from pg_conversion where connamespace = 16396;
>>       conname       | connamespace | conowner | conforencoding | 
>>contoencoding |      conproc      | condefault
>>---------------------+--------------+----------+----------------+---------------+-------------------+------------
>> iso_8859_1_to_utf_8 |        16396 |    16389 |              8 
>>|             6 | iso8859_1_to_utf8 | t
>>(1 row)
>>    

Vacuum error on database postgres

I'm running postgreSQL 8.1.4 on Red Hat Enterprise Linux 3.

Things have been working well for a while but in the last few days, I've 
gotten the following error during a nightly vacuum.

  postgres=# vacuum analyze;
  ERROR:  duplicate key violates unique constraint 
"pg_statistic_relid_att_index"

I can vacuum that table individually without problems.

  postgres=# vacuum pg_statistic;
  VACUUM
  postgres=# vacuum analyze pg_statistic;
  VACUUM
  postgres=#

I found a posting from 2004 that suggested the following query.

   postgres=# select starelid, staattnum, count(*) from pg_statistic 
group by 1,2 having count(*) > 1;
   starelid | staattnum | count
  ----------+-----------+-------
       2608 |         3 |     2
      10723 |         7 |     2
      10723 |         4 |     2
      10723 |         5 |     2
      10723 |         2 |     2
      10723 |         3 |     2
      10728 |         1 |     2

Problem using pg_restore with -a option

Hi all,

I'm using PostgreSQL 7.4.7-6sarge2 (debian)

Using the schema below, doing a pg_dump -Fc, delete all data, and a
pg_restore -a, the sequence of inserting data isn't correct issuing an
error.
Could this be a bug?

Notice that on schema, table periodo depends on tipo, but the sequence
is table periodo, then table  tipo and after table periodo is changed to
reference tipo.

My schema file:
drop table periodo cascade ;
drop table tipo cascade ;

create table periodo (
  "idPeriodo"               serial not null,
  "idTipo"                  int4,
  designacao                varchar(50),
  constraint pk_Periodo primary key ("idPeriodo")
) ;

create table tipo (
  "idTipo"                  serial not null,
  tipo                      varchar(5),
  designacao                varchar(50),
  constraint pk_Tipo primary key ("idTipo")
) ;

Re: Vacuum error on database postgres

"Paul B. Anderson" <paul.a <at> pnlassociates.com> writes:
> I did delete exactly one of each of these using ctid and the query then 
> shows no duplicates.  But, the problem comes right back in the next 
> database-wide vacuum.

That's pretty odd --- I'm inclined to suspect index corruption.

> I also tried reindexing the table.

Get rid of the duplicates (actually, I'd just blow away all the
pg_statistic entries for each of these tables) and *then* reindex.
Then re-analyze and see what happens.

Worst case you could just delete everything in pg_statistic, reindex it,
do a database-wide ANALYZE to repopulate it.  By definition there's not
any original data in that table...

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Re: Vacuum error on database postgres

Re: problem with initlocation

Joshua D. Drake wrote:

> Scott Marlowe wrote:
>
>> On Thu, 2006-08-31 at 10:05, Josef J. Micka wrote:
>>
>>> i want to make database in another place and i'm unable to use 
>>> initlocation, and i don't know, what i'm doing wrong.
>>>
>>> this is what i do, and i think it's correct.
>>>
>>> www:/home/db/postgres$ su postgres
>>> postgres <at> www:/home/db/postgres$ ls -l
>>> total 12
>>> drwx------  6 postgres postgres 4096 Aug 31 14:59 data
>>> drwxr-xr-x  2 postgres postgres 4096 Aug 31 14:19 data-web
>>> drwx------  3 postgres postgres 4096 Oct 15  2004 dumpall
>>> postgres <at> www:/home/db/postgres$ /usr/lib/postgresql/bin/initlocation 
>>> /home/db/postgres/data-web/
>>> initlocation: cannot be run as root
>>
>>
>>
>> What does the command whoami say right before you would run the
>> initlocation?
>
>
>
> I think a more appropriate question would be why is he running a 
> version of PostgreSQL that still has initlocation?
>
> Sincerely,
>
> Joshua D. Drake
>
>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>
because it's latest stable version for debian sarge.
and i rather use possibly "older but stable" software, then "unstable" 
or "development" software on production server.
also i don't need features of new version, in fact, i may stay on 6.5, 
but from my point of view is 7.4 faster, and as i said, it's "stable" 
for debian.

regards
Josef J. Micka

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Problem using pg_restore with -a option

Luís Sousa wrote:
> Hi all,
> 
> I'm using PostgreSQL 7.4.7-6sarge2 (debian)
> 
> Using the schema below, doing a pg_dump -Fc, delete all data, and a
> pg_restore -a, the sequence of inserting data isn't correct issuing an
> error.
> Could this be a bug?

Not a bug -- rather a known deficiency.  pg_dump 8.0 and beyond knows
how to sort stuff so that these problems do not occur, but 7.4 doesn't.

The typical workaround was to use pg_restore -l/-L to generate the list
of objects to restore and sort them appropiately, then do the actual
restore.

--

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match