Skip site navigation (1) Skip section navigation (2)

Re: Curious about dead rows.

From: Jean-David Beyer <jeandavid8(at)verizon(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Curious about dead rows.
Date: 2007-11-13 23:09:33
Message-ID: 473A2EAD.9040107@verizon.net (view raw or flat)
Thread:
Lists: pgsql-performance
Andrew Sullivan wrote:
> I'm not a private support organisation; please send your replies to the
> list, not me.

Sorry. Most of the lists I send to have ReplyTo set, but a few do not.
And then I forget.
> 
> On Tue, Nov 13, 2007 at 04:57:23PM -0500, Jean-David Beyer wrote:
>> What is it controlled by? The following are the non-default values in
>> postgresql.conf:
>>
>> redirect_stderr = on
>> log_directory = '/srv/dbms/dataB/pgsql/pg_log'
>> log_filename = 'postgresql-%a.log'
>> log_truncate_on_rotation = on
>> log_rotation_age = 1440
>> log_rotation_size = 0
>> log_min_messages = debug2
> 
> This will certainly include error messages, then.  Or it ought to.  You do
> see errors in the log when you create one, right?  (Try causing an error in
> psql to make sure.)
Right: I do see an error message when I try to insert a duplicate entry. It
happens to violate the (company_name, company_permno) uniqueness constraint.

2007-11-13 17:58:30 EST ERROR:  duplicate key violates unique constraint
"company_name_x"

(I tried to insert a duplicate entry in the company_name field of relation
_company_. company_name_x is defined as:
"company_name_x" UNIQUE, btree (company_name, company_permno), tablespace
"stockd" )
> 
>> log_line_prefix = '%t '
>> log_statement = 'none'   (this was 'mod', but it uses too much
>>                           disk to leave it turned on -- only
>>                           4 GBytes in that partition)
>>
>> They are; they are the primary keys of two tables. But those are all done
>> before the last VACUUM FULL ANALYZE runs, so the dead rows should have been
>> eliminated. And the output of the sequence is the only way of generating a
>> primary key, so it should be impossible anyhow.
> 
> I thought you were doing INSERTs? 

Yes.

> It's not true that the output of the
> sequence is the only way -- if you insert directly, it will happily insert
> into that column. 

Yes, but I get those keys from a sequence only. I never enter them manually
or from a data file.

> But it should cause an error to show in the log, which is
> what's puzzling me.
> 
Me too.


-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 17:50:01 up 21 days, 11:08, 4 users, load average: 5.12, 4.77, 4.68

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2007-11-13 23:51:33
Subject: Re: Curious about dead rows.
Previous:From: Andrew SullivanDate: 2007-11-13 22:10:28
Subject: Re: Curious about dead rows.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group