database corruption

Joe Conway wrote:

I don't have any more detail yet on exactly what he was doing at this
point, but I grabbed a copy of $PGDATA and looked at it on my own
machine (since he doesn't have debug and assert support). Logging into
any other database works fine, but the offending database produces this
backtrace:

It turns out the "corruption" was user error. He ran a statement that
inadvertantly set reltriggers = 1 for every row in pg_class. This is
what led to the infinite recursion.

For the archives, I was able to hack my way into the database by
wrapping the body of RelationBuildTriggers() in "if (relation->rd_id >
17000) {}", thus excluding system tables. Once back in the database,
updating reltriggers in pg_class to appropriate values restored access
to the database with an unhacked backend.

Joe

On Sat, Aug 30, 2003 at 12:57:26PM -0700, Joe Conway wrote:

Joe Conway wrote:

I don't have any more detail yet on exactly what he was doing at this
point, but I grabbed a copy of $PGDATA and looked at it on my own
machine (since he doesn't have debug and assert support). Logging into
any other database works fine, but the offending database produces this
backtrace:

It turns out the "corruption" was user error. He ran a statement that
inadvertantly set reltriggers = 1 for every row in pg_class. This is
what led to the infinite recursion.

For the record, how were you able to detect this?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ellos andaban todos desnudos como su madre los pari�, y tambi�n las mujeres,
aunque no vi m�s que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de m�s de XXX a�os" (Crist�bal Col�n)

Alvaro Herrera wrote:

On Sat, Aug 30, 2003 at 12:57:26PM -0700, Joe Conway wrote:

Joe Conway wrote:

I don't have any more detail yet on exactly what he was doing at this
point, but I grabbed a copy of $PGDATA and looked at it on my own
machine (since he doesn't have debug and assert support). Logging into
any other database works fine, but the offending database produces this
backtrace:

It turns out the "corruption" was user error. He ran a statement that
inadvertantly set reltriggers = 1 for every row in pg_class. This is
what led to the infinite recursion.

For the record, how were you able to detect this?

The backtrace from the core file showed that the recursion was going on
between RelationSysNameGetRelation(), relation_openr(), heap_openr(),
RelationBuildTriggers() and RelationBuildDesc(). It seemed odd that
pg_trigger seemed to be getting a trigger applied to it, so I guessed
that bypassing RelationBuildTriggers() for system tables would allow me
in to the database.

About the time I got in and started looking around, I finally got in
touch with the user, who confirmed he had been trying to disable then
re-enable triggers when the problem occurred. He ran a statement like:
update pg_class set reltriggers = 1;
thinking that reltriggers was a "flag" (0 == off, 1 == on). That's when
it all suddenly made sense to me ;-)

Fortunately this was his own development database he was messing with,
but it was an interesting exercise none-the-less. Maybe this reinforces
the need to have a command for enabling/disabling triggers. I vaguely
remember discussion about that -- did it make it into 7.4?

Joe

Joe Conway <mail@joeconway.com> writes:

Fortunately this was his own development database he was messing with,
but it was an interesting exercise none-the-less. Maybe this reinforces
the need to have a command for enabling/disabling triggers.

No, it reinforces the cardinal rule about not experimenting with system
catalog alterations in databases you care about. If he'd done his
experimentation in a scratch database, he'd not have been in trouble.

Perhaps we need some documentation about What Not To Do As Superuser.

regards, tom lane

(Perhaps a newbie question, but I tried to google this out without success).

Why postgres does an expensive seqscan to find the max(value) for an indexed
column? I think MAX() does not know or cares if a column is indexed, but...
Should not it? BTW, is there some smarter trick to do that?

I know I can just do a very fast (SELECT pk FROM foo ORDER BY pk DESC LIMIT
1) instead, but my coleagues are arguing that MAX(indexed_column) seems to
be a lot
more smarter in MS-SQLServer and I end up without a good response.

Thank you,
--
Paulo Scardine
Brazil

On 4 Sep 2003 at 11:32, Paulo Scardine wrote:

(Perhaps a newbie question, but I tried to google this out without success).

Why postgres does an expensive seqscan to find the max(value) for an indexed
column? I think MAX() does not know or cares if a column is indexed, but...
Should not it? BTW, is there some smarter trick to do that?

No. Postgresql uses MVCC which mean there could be multiple views of sample
tuple active at the same time. There is no way to tell which is max. value for
a column as definition of a committed value can be a moving target.

It can not be cached, at least easily. That's the price to pay for MVCC. Same
goes for select count(*) from table. That query has to end up with a sequential
scan.

I know I can just do a very fast (SELECT pk FROM foo ORDER BY pk DESC LIMIT
1) instead, but my coleagues are arguing that MAX(indexed_column) seems to
be a lot
more smarter in MS-SQLServer and I end up without a good response.

Well, postgresql earns solid concurrency due to MVCC. Set up postgresql and MS
SQL server on same machine and do a rudimentary benchmark with 100 clients
hitting database hard. See where you get more tps'.s

In postgresql, readers and writers don't block each other. AFAIK, in MS SQL
server rows are ocked for update. So if you lock a row in transaction and does
not commit for long, MS SQL will have serious problems.

All night long transactions are no problem to postgresql except for the fact
that vacuum can not clean the tuples locked in tranactions.

HTH

Bye
Shridhar

--
Blutarsky's Axiom: Nothing is impossible for the man who will not listen to
reason.

On Thu, 4 Sep 2003, Shridhar Daithankar wrote:

column? I think MAX() does not know or cares if a column is indexed, but...

No. Postgresql uses MVCC which mean there could be multiple views of sample
tuple active at the same time. There is no way to tell which is max. value for
a column as definition of a committed value can be a moving target.

It can not be cached, at least easily. That's the price to pay for MVCC. Same
goes for select count(*) from table. That query has to end up with a sequential
scan.

It does not have to be like that. Even with a mvcc database it can use the
index for max/min and in my opinion it should.

As far as I know the only reason why it's not implemented in postgresql is
because pg has a general aggregate model and max/min are implemented using
that. Still, max/min are special in that they are almost the only
aggregates that can use an index to deliver the result directly. Some day
someone should make max/min a special case in pg. Exactly how is the
question.

I don't know mssql much, but I guess you can't define your own aggregate
functions there? Then all aggregate functions are special anyway.

--
/Dennis

This is an FAQ, BTW -- try searching the archives again. It's also
mentioned in the documentation:

http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-aggregate.html

On Thu, 2003-09-04 at 11:10, Dennis Bjorklund wrote:

On Thu, 4 Sep 2003, Shridhar Daithankar wrote:

It can not be cached, at least easily. That's the price to pay for MVCC. Same
goes for select count(*) from table. That query has to end up with a sequential
scan.

It does not have to be like that. Even with a mvcc database it can use the
index for max/min and in my opinion it should.

Right, AFAIK MVCC isn't relevant to MAX() (given a btree index, you can
just read the index in the right order and return the first valid
tuple), although it makes optimizing COUNT(*) trickier, I believe.

As far as I know the only reason why it's not implemented in postgresql is
because pg has a general aggregate model and max/min are implemented using
that. Still, max/min are special in that they are almost the only
aggregates that can use an index to deliver the result directly. Some day
someone should make max/min a special case in pg. Exactly how is the
question.

Well, it's an open question whether it's worth uglifying the backend to
support this optimization, given that there is a trivial workaround that
people can use. It would make it easier to port code to PostgreSQL from
other RDBMSs, though...

-Neil

Hello

In my opinion, in 7.4 this optimized max() aggregate function would be a
very small, but significant improvement. As one of the members on the list
said, it would be a lot easier to port from/to other RDBMSes, with keeping
the same optimalization of the queries.

Bye,

Gergely Czuczy
mailto: phoemix@harmless.hu
PGP: http://phoemix.harmless.hu/phoemix.pgp

The point is, that geeks are not necessarily the outcasts
society often believes they are. The fact is that society
isn't cool enough to be included in our activities.

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:

On 4 Sep 2003 at 11:32, Paulo Scardine wrote:

(Perhaps a newbie question, but I tried to google this out without success).

Why postgres does an expensive seqscan to find the max(value) for an indexed
column? I think MAX() does not know or cares if a column is indexed, but...
Should not it? BTW, is there some smarter trick to do that?

No. Postgresql uses MVCC which mean there could be multiple views of sample
tuple active at the same time. There is no way to tell which is max. value for
a column as definition of a committed value can be a moving target.

It has nothing to do with MVCC. It has to do with implementing this is hard in
the general case.

Think of examples like:

select max(foo) group by bar;

or

select max(foo) where xyz = z;

To do it properly max/min have to be special-cased and tightly integrated with
other code to handle index scans and aggregates. As it currently stands
they're implemented the same way as any other aggregate, which means they get
to see all the records in the grouping.

This is a frequently asked question, I'm surprised you didn't find stuff
searching with google. There have been numerous long discussions on this topic
not long ago. People are still trying to think about how to handle this
better.

--
greg

Greg Stark wrote:

It has nothing to do with MVCC. It has to do with implementing this is hard in
the general case.

Think of examples like:

select max(foo) group by bar;

or

select max(foo) where xyz = z;

To do it properly max/min have to be special-cased and tightly integrated with
other code to handle index scans and aggregates. As it currently stands
they're implemented the same way as any other aggregate, which means they get
to see all the records in the grouping.

This is a frequently asked question, I'm surprised you didn't find stuff
searching with google. There have been numerous long discussions on this topic
not long ago. People are still trying to think about how to handle this
better.

The FAQ does have the example of using ORDER BY LIMIT 1 for MAX(). What
we don't have a workaround for is COUNT(*). I think that will require
some cached value that obeys MVCC rules of visibility.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Greg Stark wrote:

It has nothing to do with MVCC. It has to do with implementing this is hard in
the general case.

Think of examples like:

select max(foo) group by bar;

or

select max(foo) where xyz = z;

To do it properly max/min have to be special-cased and tightly integrated with
other code to handle index scans and aggregates. As it currently stands
they're implemented the same way as any other aggregate, which means they get
to see all the records in the grouping.

This is a frequently asked question, I'm surprised you didn't find stuff
searching with google. There have been numerous long discussions on this topic
not long ago. People are still trying to think about how to handle this
better.

The FAQ does have the example of using ORDER BY LIMIT 1 for MAX(). What
we don't have a workaround for is COUNT(*). I think that will require
some cached value that obeys MVCC rules of visibility.

IMHO portability is an important point. People are used to MAX() and
COUNT(*), and will be surprised that they need some special treatment.
While the reasons for this are perfectly explainable, speeding up these
aggregates with some extra effort would make porting a bit easier.

Regards,
Andreas

The world rejoiced as pgadmin@pse-consulting.de (Andreas Pflug) wrote:

Bruce Momjian wrote:

Greg Stark wrote:

It has nothing to do with MVCC. It has to do with implementing this is hard in
the general case.

Think of examples like:

select max(foo) group by bar;

or

select max(foo) where xyz = z;

To do it properly max/min have to be special-cased and tightly integrated with
other code to handle index scans and aggregates. As it currently stands
they're implemented the same way as any other aggregate, which means they get
to see all the records in the grouping.

This is a frequently asked question, I'm surprised you didn't find stuff
searching with google. There have been numerous long discussions on this topic
not long ago. People are still trying to think about how to handle this
better.

The FAQ does have the example of using ORDER BY LIMIT 1 for MAX(). What
we don't have a workaround for is COUNT(*). I think that will require
some cached value that obeys MVCC rules of visibility.

IMHO portability is an important point. People are used to MAX() and
COUNT(*), and will be surprised that they need some special
treatment. While the reasons for this are perfectly explainable,
speeding up these aggregates with some extra effort would make porting
a bit easier.

The availability of cleverness with MAX()/MIN() is no grand surprise;
it would be very nice to get some expansion of that to "SELECT VALUE
FROM TABLE WHERE (CRITERIA) ORDER BY VALUE DESCENDING LIMIT 1;"

But I'm _very_ curious as to what the anticipated treatment to collect
COUNT() more efficiently would be. I would expect that it would only
be able to get tuned much more if there's NO "where" clause, so that
it could use some ("magically-kept-up-to-date") stats on table size.

I don't see any way to optimize COUNT when numbers of rows can
continually vary. Storing stats somewhere will just make updates more
expensive. And if those stats are for the table, that doesn't help me
if I want "COUNT(*) FROM TABLE WHERE UPDATED_ON BETWEEN NOW() - '1
day' and NOW()".
--
(format nil "~S@~S" "aa454" "freenet.carleton.ca")
http://cbbrowne.com/info/linuxdistributions.html
"Recursion is the root of computation since it trades description for time."
-- Alan Perlis

Christopher Browne wrote:

IMHO portability is an important point. People are used to MAX() and
COUNT(*), and will be surprised that they need some special
treatment. While the reasons for this are perfectly explainable,
speeding up these aggregates with some extra effort would make porting
a bit easier.

The availability of cleverness with MAX()/MIN() is no grand surprise;
it would be very nice to get some expansion of that to "SELECT VALUE
FROM TABLE WHERE (CRITERIA) ORDER BY VALUE DESCENDING LIMIT 1;"

But I'm _very_ curious as to what the anticipated treatment to collect
COUNT() more efficiently would be. I would expect that it would only
be able to get tuned much more if there's NO "where" clause, so that
it could use some ("magically-kept-up-to-date") stats on table size.

I don't see any way to optimize COUNT when numbers of rows can
continually vary. Storing stats somewhere will just make updates more
expensive. And if those stats are for the table, that doesn't help me
if I want "COUNT(*) FROM TABLE WHERE UPDATED_ON BETWEEN NOW() - '1
day' and NOW()".

Yes, count would only use the cached stats for non-WHERE clause
COUNT(*).

My idea is that if a transaction doing a COUNT(*) would first look to
see if there already was a visible cached value, and if not, it would do
the COUNT(*) and insert into the cache table. Any INSERT/DELETE would
remove the value from the cache. As I see it, the commit of the
INSERT/DELETE transaction would then auto-invalidate the cache at the
exact time the transaction commits. This would allow MVCC visibility of
the counts.

A trickier idea would be for INSERT/DELETE to UPDATE the cached value.
It might be possible to always have a valid cache value for COUNT(*).
(COPY would also need to update the cache.)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Thu, 2003-09-04 at 22:02, Bruce Momjian wrote:

My idea is that if a transaction doing a COUNT(*) would first look to
see if there already was a visible cached value, and if not, it would do
the COUNT(*) and insert into the cache table. Any INSERT/DELETE would
remove the value from the cache. As I see it, the commit of the
INSERT/DELETE transaction would then auto-invalidate the cache at the
exact time the transaction commits. This would allow MVCC visibility of
the counts.

But this means that some of the time (indeed, *much* of the time),
COUNT(*) would require a seqscan of the entire table. Since at many
sites that will take an enormous amount of time (and disk I/O), that
makes this solution infeasible IMHO.

In general, I don't think this is worth doing.

-Neil

Neil Conway wrote:

On Thu, 2003-09-04 at 22:02, Bruce Momjian wrote:

My idea is that if a transaction doing a COUNT(*) would first look to
see if there already was a visible cached value, and if not, it would do
the COUNT(*) and insert into the cache table. Any INSERT/DELETE would
remove the value from the cache. As I see it, the commit of the
INSERT/DELETE transaction would then auto-invalidate the cache at the
exact time the transaction commits. This would allow MVCC visibility of
the counts.

But this means that some of the time (indeed, *much* of the time),
COUNT(*) would require a seqscan of the entire table. Since at many
sites that will take an enormous amount of time (and disk I/O), that
makes this solution infeasible IMHO.

In general, I don't think this is worth doing.

It is possible it isn't worth doing. Can the INSERT/DELETE
incrementing/decrementing the cached count work reliabily?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Neil Conway wrote:

In general, I don't think this is worth doing.

It is possible it isn't worth doing. Can the INSERT/DELETE
incrementing/decrementing the cached count work reliabily?

I don't even see how the notion of a single cached value makes
theoretical sense, when in principle every transaction may have
a different idea of the correct answer.

You could doubtless maintain a fairly good approximate total this
way, and that would be highly useful for some applications ...
but it isn't COUNT(*).

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Neil Conway wrote:

In general, I don't think this is worth doing.

It is possible it isn't worth doing. Can the INSERT/DELETE
incrementing/decrementing the cached count work reliabily?

I don't even see how the notion of a single cached value makes
theoretical sense, when in principle every transaction may have
a different idea of the correct answer.

You could doubtless maintain a fairly good approximate total this
way, and that would be highly useful for some applications ...
but it isn't COUNT(*).

With MVCC allowing multiple rows with only one visible, I thought the
INSERT/DELETE system would work --- once the delete becomes visible, the
change becomes visible.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

You could doubtless maintain a fairly good approximate total this
way, and that would be highly useful for some applications ...
but it isn't COUNT(*).

With MVCC allowing multiple rows with only one visible, I thought the
INSERT/DELETE system would work --- once the delete becomes visible, the
change becomes visible.

Oh, you're imagining the cache as being a row in an ordinary table?
I doubt that could work. Multiple transactions trying to update these
rows would suffer from contention and deadlock problems, wouldn't they?

regards, tom lane