Date: Tue, 13 Nov 2007 18:09:33 -0500
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Curious about dead rows.
In-reply-to: <20071113221028.GH11563@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Message-id: <473A2EAD.9040107@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <20071112171805.GQ32349@crankycanuck.ca>
	<47389504.4020700@verizon.net> <20071112185218.GS32349@crankycanuck.ca>
	<4738A2EC.9090500@verizon.net> <20071112190833.GY32349@crankycanuck.ca>
	<4739BD65.9070605@verizon.net> <20071113152111.GG11563@crankycanuck.ca>
	<473A0023.5020609@verizon.net> <20071113213510.GF11563@crankycanuck.ca>
	<473A1DC3.3070608@verizon.net> <20071113221028.GH11563@crankycanuck.ca>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)

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