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

Re: simple update closes connection. why?

From: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: simple update closes connection. why?
Date: 2004-02-01 21:07:07
Message-ID: 20040201210707.GA5875@www.sirfsup.com (view raw or flat)
Thread:
Lists: pgsql-novice
okay.  I"m making progress here.  :)   I want to know if I should just delete the oid that's too very large?

because, I can issue a select up to row 1100.  So, Select * from calling limit 1100 returns all rows, whereas 
select * from calling limit 1101 kills the process.

Here is what i get for the select you asked for:
calling=# select * from pg_attribute where attnum > 0 and attrelid = 'calling'::regclass;
-[ RECORD 1 ]-+------------
attrelid      | 577720
attname       | filename
atttypid      | 1043
attstattarget | -1
attlen        | -1
attnum        | 1
attndims      | 0
attcacheoff   | -1
atttypmod     | 44
attbyval      | f
attstorage    | x
attisset      | f
attalign      | i
attnotnull    | f
atthasdef     | f
attisdropped  | f
attislocal    | t
attinhcount   | 0
-[ RECORD 2 ]-+------------
attrelid      | 577720
attname       | description
atttypid      | 1043
attstattarget | -1
attlen        | -1
attnum        | 2
attndims      | 0
attcacheoff   | -1
atttypmod     | 184
attbyval      | f
attstorage    | x
attisset      | f
attalign      | i
attnotnull    | f
atthasdef     | f
attisdropped  | f
attislocal    | t
attinhcount   | 0
-[ RECORD 3 ]-+------------
attrelid      | 577720
attname       | uname
atttypid      | 1043
attstattarget | -1
attlen        | -1
attnum        | 3
attndims      | 0
attcacheoff   | -1
atttypmod     | 34
attbyval      | f
attstorage    | x
attisset      | f
attalign      | i
attnotnull    | f
atthasdef     | f
attisdropped  | f
attislocal    | t
attinhcount   | 0


and here is also something intersting 

calling=# select oid from calling;

snip
.....
    583116
     583117
     583118
     583119
     583120
     584227
 2617245723
     588066
     588067
     578306
     578307
     578310
     588068
...... snip

( so thinking I can try to get where > 584227...)
calling=# select oid from calling where oid > 584227;
    oid
------------
 1342255253
     588066
     588067
     588068
     588069
     588090
     588091
     588093
     588094
     596284
     596285
     596286
     596287

snip
..................................
(it's a moving target!)
calling=# select oid from calling where oid > 584227;
-[ RECORD 1 ]---
oid | 1006632986
-[ RECORD 2 ]---
oid | 588066
-[ RECORD 3 ]---
oid | 588067
-[ RECORD 4 ]---

calling=# select * from pg_class where relname='calling';
-[ RECORD 1 ]--+--------------------------------------------------
relname        | calling
relnamespace   | 2200
reltype        | 577721
relowner       | 110
relam          | 0
relfilenode    | 577720
relpages       | 159
reltuples      | 1685
reltoastrelid  | 0
reltoastidxid  | 0
relhasindex    | t
relisshared    | f
relkind        | r
relnatts       | 3
relchecks      | 0
reltriggers    | 0
relukeys       | 0
relfkeys       | 0
relrefs        | 0
relhasoids     | t
relhaspkey     | f
relhasrules    | f
relhassubclass | f
relacl         | {=,joe=arwdRxt,perlcalling=arwdRxt,perl_user=arwdRxt}

calling=# select count(*) from calling;
-[ RECORD 1 ]
count | 1820

calling=#

On Sun, Feb 01, 2004 at 12:02:50PM -0500, Tom Lane wrote:
> joseph speigle <joe(dot)speigle(at)jklh(dot)us> writes:
> > i had altered the table when it was okay with the following sequence
> 
> > alter table calling add column uname character varying(20);
> >  alter table calling alter column uname set not null;
> > said I couldn't do that because there were null values in it, so that's when i tried the
> > update calling set uname='joe';
> > and got errors.
> 
> Hmm.  That's interesting and possibly relevant, but it can't be the
> whole story --- people have been doing that for years.  What else can
> you tell us about the history of this table?
> 
> > (gdb) bt
> > #0  0x0806b854 in nocachegetattr (tuple=0x82f5b78, attnum=1, tupleDesc=0x406f23a0, isnull=0xbfffe9d3 "") at heaptuple.c:349
> 
> I'm beginning to think you must have corruption in the system catalogs,
> because there's no way that control should have reached that line for
> this table, seeing that all the columns are variable-width.  Could we
> see the output of
> select * from pg_attribute where attnum > 0 and attrelid = 'calling'::regclass;
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

-- 
joe speigle

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-02-01 21:35:14
Subject: Re: simple update closes connection. why?
Previous:From: Tom LaneDate: 2004-02-01 19:18:59
Subject: Re: Have you received this? Fwd: Lots of nan's

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