Re: Curious about dead rows.

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