Re: [SQL] Subselect strange behaviour - bug?

2008-03-17 Thread Mario Splivalo 

Tom Lane wrote:

Mario Splivalo <[EMAIL PROTECTED]> writes:
  

And here is the 'problematic' query:
melem=# select * from t2 where id1 in (select id1 from t1);

I guess postgres should tell me that column name id1 is nonexistant in

table t1.



No, it shouldn't, because that's a perfectly legal outer reference;
that is, what you wrote is equivalent to
select * from t2 where id1 in (select t2.id1 from t1);

  

Huh, you're right. Appologies for the noise :)

   Mario

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] postgres server crashes unexpectedly

2008-03-17 Thread Chadwick Horn 
hi all,

we've had a db to stop working for some reason and have searched through google 
to it's ends to no avail. we have reindexed the database and system database as 
tried to determine other things that would keep it from working... again, to no 
avail.

this just started occuring about 2 weeks ago and below is a recent snippet of 
error log that we have.  any idea what would/could be causing these crashes? 
any response is much appreciated as this is not a time-critial issue for us.


thanks!

chadwick

error log:


LOG:  redo is not required
LOG:  database system is ready
PANIC:  corrupted item pointer: offset = 0, size = 0
LOG:  autovacuum process (PID 3037) was terminated by signal 6
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2008-03-18 02:55:30 PDT
LOG:  checkpoint record is at 25/6068AE30
LOG:  redo record is at 25/6068AE30; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/414366728; next OID: 240102
LOG:  next MultiXactId: 1132; next MultiXactOffset: 2431
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 25/6068AE80
LOG:  redo is not required
LOG:  database system is ready
PANIC:  corrupted item pointer: offset = 0, size = 0
LOG:  autovacuum process (PID 3045) was terminated by signal 6
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2008-03-18 02:56:31 PDT
LOG:  checkpoint record is at 25/6068AE80
LOG:  redo record is at 25/6068AE80; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/414366728; next OID: 240102
LOG:  next MultiXactId: 1132; next MultiXactOffset: 2431
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 25/6068AED0
LOG:  redo is not required
LOG:  database system is ready
PANIC:  corrupted item pointer: offset = 0, size = 0
LOG:  autovacuum process (PID 3072) was terminated by signal 6
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server process exited abnormally 
and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat 
your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2008-03-18 02:57:32 PDT
LOG:  checkpoint record is at 25/6068AED0
LOG:  redo record is at 25/6068AED0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/414366728; next OID: 240102
LOG:  next MultiXactId: 1132; next MultiXactOffset: 2431
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 25/6068AF20
LOG:  redo is not required
LOG:  database system is ready

Re: [SQL] postgres server crashes unexpectedly

2008-03-17 Thread Tom Lane 
"Chadwick Horn" <[EMAIL PROTECTED]> writes:
> PANIC:  corrupted item pointer: offset = 0, size = 0
> LOG:  autovacuum process (PID 3037) was terminated by signal 6

Hmm ... the only instances of that error text are in PageIndexTupleDelete
and PageIndexMultiDelete, so we can fairly safely say that you have a
partially zeroed-out page in some index somewhere.  If that's the only
damage then you're in luck: you can recover by reindexing.

What I'd do is turn off autovacuum and instead do a manual VACUUM
VERBOSE to see where it crashes; then you could just reindex the one
problem table instead of the whole database.

You ought to look into why this happened, too.  Since you've provided
precisely 0 context about PG version or platform, it's hard to speculate
about that ...

regards, tom lane

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql