Обсуждение: automatic crash recovery

Generally after a crash most of the database indices need to
be rebuilt, has anyone put together a script to bring up the
database in 'single user mode' where network connections are
refused, then drop and recreate all indices and vacuum?

This sort of functionality would be very helpful in assisting
automatic-restart, sort of like fsck after crash and restart.

Any pointers?

thanks,
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Hi,

Previously I wanted to ensure that I am inserting something unique into a
table, the answer was to create a unique index on the relevant columns.

But what if I don't want to get an error which would force a rollback? Say
I want to insert something if it doesn't already exist, but update it if it
does.

Do I have to lock the whole table?

Would it be a good idea to be able to request a lock on an arbitrary string
like in MySQL? Then I could perhaps do something like

LOCK HANDLE('max255charstring',TimeoutInSeconds)
e.g.
LOCK HANDLE('mytable,field1=x,field2=y',10)

Then I could control access to a row that may not even exist, or do other
snazzy transaction stuff.

Cheerio,
Link.

Lincoln Yeoh wrote:
>
> Hi,
>
> Previously I wanted to ensure that I am inserting something unique into a
> table, the answer was to create a unique index on the relevant columns.
>
> But what if I don't want to get an error which would force a rollback? Say
> I want to insert something if it doesn't already exist, but update it if it
> does.

I think the best answer to this is to correct the non-std error-handling to
abort only the current statement and not the entire transaction.  IIRC,
Peter Eisenstraut recently posted a one-line patch to facilitate this,
though I don't know how well it's working for those who tried it.  I have
not seen anything that indicated that the core developers were ready to
adopt this, though recent discussions appeared to be heading that way.

Regards,
Ed Loehr

>
> Do I have to lock the whole table?
>
> Would it be a good idea to be able to request a lock on an arbitrary string
> like in MySQL? Then I could perhaps do something like
>
> LOCK HANDLE('max255charstring',TimeoutInSeconds)
> e.g.
> LOCK HANDLE('mytable,field1=x,field2=y',10)
>
> Then I could control access to a row that may not even exist, or do other
> snazzy transaction stuff.
>
> Cheerio,
> Link.

Ed Loehr wrote:
>
> Lincoln Yeoh wrote:
> >
> > Hi,
> >
> > Previously I wanted to ensure that I am inserting something unique into a
> > table, the answer was to create a unique index on the relevant columns.
> >
> > But what if I don't want to get an error which would force a rollback? Say
> > I want to insert something if it doesn't already exist, but update it if it
> > does.

I think you could SELECT from etc using the key value, before trying to
insert anything; if it returns 0 rows, then you insert, else you update.
>
> I think the best answer to this is to correct the non-std error-handling to
> abort only the current statement and not the entire transaction.  IIRC,
> Peter Eisenstraut recently posted a one-line patch to facilitate this,
> though I don't know how well it's working for those who tried it.  I have
> not seen anything that indicated that the core developers were ready to
> adopt this, though recent discussions appeared to be heading that way.
>

I tested the mentioned patch. I worked fine as far as I could try. I
agree with you in that this is the way to go, including what Bruce
suggested of using a SET statement to select behaviour ...

> Regards,
> Ed Loehr
>
> >
> > Do I have to lock the whole table?
> >
> > Would it be a good idea to be able to request a lock on an arbitrary string
> > like in MySQL? Then I could perhaps do something like
> >
> > LOCK HANDLE('max255charstring',TimeoutInSeconds)
> > e.g.
> > LOCK HANDLE('mytable,field1=x,field2=y',10)
> >
> > Then I could control access to a row that may not even exist, or do other
> > snazzy transaction stuff.
> >
> > Cheerio,
> > Link.