automatic crash recovery

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

Hi Lincoln,

I'm not sure I'm understanding your question, but it seems like this is
something that
ought to be handled programmatically. That is, query the table to see if
the row exists,
then decide what you are going to do (insert or update) based on the results
of your
query.

Am I completely missing the point?

David Boerwinkle

-----Original Message-----
From: Lincoln Yeoh <lylyeoh@mecomb.com>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Monday, April 24, 2000 1:13 AM
Subject: [GENERAL] Revisited: Transactions, insert unique.

davidb@vectormath.com wrote:

Hi Lincoln,

I'm not sure I'm understanding your question, but it seems like this is
something that
ought to be handled programmatically. That is, query the table to see if
the row exists,
then decide what you are going to do (insert or update) based on the results
of your
query.

Good point. And you can combine the check David suggests with the insert
statement, e.g.,

INSERT INTO mytable (id, ...)
SELECT 7, ...
FROM mytable
WHERE NOT EXISTS (SELECT * FROM mytable WHERE id = 7)

And then check the return result for number of rows inserted. '0' means an
update is needed. I don't remember if there is cleaner more efficient
manner for doing that, but probably so...

Regards,
Ed Loehr

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 ...

Today, in a message to pgsql-general, David Boerwinkle wrote:

it seems like this is something that ought to be handled
programmatically. That is, query the table to see if the row exists,
then decide what you are going to do (insert or update) based on the
results of your query.

It certainly 'can' be handled the way you describe, but to say that it
'ought' to be handled this way is going too far. It is common practice in
database programming to simply try the most likely case and fall back to
alternatives when an error is encountered. For example, if one expects 99%
of inserts to be unique one may simply try the insert and when this fails
because of a duplicate key error one can update instead. This is slightly
more efficient than doing the extra query in 100% of cases.

In any case, if I'm not mistaken the SQL standard permits an automatic
rollback only for deadlock errors and equivalent types of errors where the
rollback may be required to resolve a lockup situation.

Joachim

--
private: joachim@kraut.bc.ca (http://www.kraut.bc.ca)
work: joachim@mercury.bc.ca (http://www.mercury.bc.ca)

Joachim Achtzehnter wrote:

Today, in a message to pgsql-general, David Boerwinkle wrote:

it seems like this is something that ought to be handled
programmatically. That is, query the table to see if the row exists,
then decide what you are going to do (insert or update) based on the
results of your query.

It certainly 'can' be handled the way you describe, but to say that it
'ought' to be handled this way is going too far. It is common practice in
database programming to simply try the most likely case and fall back to
alternatives when an error is encountered. For example, if one expects 99%
of inserts to be unique one may simply try the insert and when this fails
because of a duplicate key error one can update instead. This is slightly
more efficient than doing the extra query in 100% of cases.

More efficient, yes. However, given the lack of "statement-only" aborts
and the state of pg error codes (all strings, no numeric codes), the
programmatic/query check appears not only compulsory but simpler code-wise.

Regards,
Ed Loehr

Today, in a message to Joachim Achtzehnter, Ed Loehr wrote:

More efficient, yes. However, given the lack of "statement-only"
aborts and the state of pg error codes (all strings, no numeric
codes), the programmatic/query check appears not only compulsory but
simpler code-wise.

Given current limitations of the postgresql implementation, you are
certainly right: there is no other choice. I was merely countering the
suggestion that there was something wrong with the approach of simply
trying the insert first. It is a perfectly valid approach when used with
an SQL92 compliant database. We just have to live without it until
postgresql improves on this point. This is certainly not a show stopper
for most of us unless perhaps when somebody has to port a ton of code from
another database :-(

Joachim

--
private: joachim@kraut.bc.ca (http://www.kraut.bc.ca)
work: joachim@mercury.bc.ca (http://www.mercury.bc.ca)

On Mon, Apr 24, 2000 at 11:01:57AM -0700, Joachim Achtzehnter wrote:

[...] It is a perfectly valid approach when used with an SQL92 compliant
database. We just have to live without it until postgresql improves
on this point. This is certainly not a show stopper for most of us
unless perhaps when somebody has to port a ton of code from another
database :-(

I'm going to jump in here, because this is a particular axe I grind:

I've bent my brain around the SQL92 standards docs, and there's _no_
requirement for this type of behavior on error. Yes, it's a useful thing
to have, and yes, all the bigname commercial RDBMS's work that way, but that
doesn't mean postgres isn't SQL92 compliant on that point (it misses on
other points, though). So, go ahead and complain, I agree it's a pain for
those porting code. But don't say it's a standards issue, until you can
point to chapter and verse to defend your position.

Ross (touchy about SQL92 standards compliance, for some reason)
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

"Ross J. Reedstrom" wrote:

On Mon, Apr 24, 2000 at 11:01:57AM -0700, Joachim Achtzehnter wrote:

[...] It is a perfectly valid approach when used with an SQL92 compliant
database. We just have to live without it until postgresql improves
on this point. This is certainly not a show stopper for most of us
unless perhaps when somebody has to port a ton of code from another
database :-(

I'm going to jump in here, because this is a particular axe I grind:

I've bent my brain around the SQL92 standards docs, and there's _no_
requirement for this type of behavior on error. Yes, it's a useful thing
to have, and yes, all the bigname commercial RDBMS's work that way, but that
doesn't mean postgres isn't SQL92 compliant on that point (it misses on
other points, though). So, go ahead and complain, I agree it's a pain for
those porting code. But don't say it's a standards issue, until you can
point to chapter and verse to defend your position.

Fair enough. My "non-std" comment an unfortunate choice of words. I was
alluding to pgsql "doing it differently" from everyone else (i.e., in a
"non-std manner"), not to a violation of an "official" SQL standard. My
recollection of the recent SQL standards discussion on this point was that
at least one standards document (which one?) was ambiguous on this point.

Regards,
Ed Loehr

Today, in a message to pgsql-general, Ross J. Reedstrom wrote:

I've bent my brain around the SQL92 standards docs, and there's _no_
requirement for this type of behavior on error.

Don't have access to the actual standard text, perhaps somebody who has
can confirm whether the following quotes from an earlier draft (identified
by the code X3H2-92-154/DBL CBR-002) are also in the final text.

In section 4.10.1 (Checking of constraints) I find this:

When a constraint is checked other than at the end of an SQL-
transaction, if it is not satisfied, then an exception condition
is raised and the SQL-statement that caused the constraint to be
checked has no effect other than entering the exception
information into the diagnostics area.

An automatic rollback of the whole transaction in response to a violated
primary key constraint is hardly consistent with the "no effect"
requirement expressed here.

The following passages from section 4.28 (SQL-transactions) also very
strongly imply that an automatic rollback should not occur except in
circumstances where there is no choice (serialization failure and
unrecoverable errors):

The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects the inability to guarantee the
serializability of two or more concurrent SQL-transactions. When
this error occurs, an exception condition is raised: transaction
rollback-serialization failure.

The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects unrecoverable errors. When
such an error occurs, an exception condition is raised: transaction
rollback with an implementation-defined subclass code.

The execution of an SQL-statement within an SQL-transaction has
no effect on SQL-data or schemas other than the effect stated in
the General Rules for that SQL-statement, in the General Rules
for Subclause 11.8, "<referential constraint definition>", and
in the General Rules for Subclause 12.3, "<procedure>".

Perhaps, you can make the argument that an automatic rollback in all error
situations is compliant by claiming that all errors are unrecoverable. In
my view this is definitely against the spirit of the standard. As you said
yourself, all big-name databases behave according to my interpretation,
hence it is understandable that the authors of the standard didn't see a
need to spell this out more explicitly.

Joachim

--
joachim@kraut.bc.ca (http://www.kraut.bc.ca)
joachim@mercury.bc.ca (http://www.mercury.bc.ca)

Joachim Achtzehnter wrote:

Today, in a message to pgsql-general, Ross J. Reedstrom wrote:

I've bent my brain around the SQL92 standards docs, and there's _no_
requirement for this type of behavior on error.

Don't have access to the actual standard text, perhaps somebody who has
can confirm whether the following quotes from an earlier draft (identified
by the code X3H2-92-154/DBL CBR-002) are also in the final text.

In section 4.10.1 (Checking of constraints) I find this:

When a constraint is checked other than at the end of an SQL-
transaction, if it is not satisfied, then an exception condition
is raised and the SQL-statement that caused the constraint to be
checked has no effect other than entering the exception
information into the diagnostics area.

An automatic rollback of the whole transaction in response to a violated
primary key constraint is hardly consistent with the "no effect"
requirement expressed here.

The following passages from section 4.28 (SQL-transactions) also very
strongly imply that an automatic rollback should not occur except in
circumstances where there is no choice (serialization failure and
unrecoverable errors):

The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects the inability to guarantee the
serializability of two or more concurrent SQL-transactions. When
this error occurs, an exception condition is raised: transaction
rollback-serialization failure.

The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects unrecoverable errors. When
such an error occurs, an exception condition is raised: transaction
rollback with an implementation-defined subclass code.

The execution of an SQL-statement within an SQL-transaction has
no effect on SQL-data or schemas other than the effect stated in
the General Rules for that SQL-statement, in the General Rules
for Subclause 11.8, "<referential constraint definition>", and
in the General Rules for Subclause 12.3, "<procedure>".

Perhaps, you can make the argument that an automatic rollback in all error
situations is compliant by claiming that all errors are unrecoverable. In
my view this is definitely against the spirit of the standard. As you said
yourself, all big-name databases behave according to my interpretation,
hence it is understandable that the authors of the standard didn't see a
need to spell this out more explicitly.

I found that pretty informative. I dug up the previous conversation on
this, in which Tom Lane cited section 3.3.4.1 (of what std?). Its emphasis
on *statements* as opposed to *transactions* suggests to me that aborting
the transaction is beyond the scope of what they had in mind, though I
admittedly don't fully understand the jargon here...

The phrase "an exception condition is raised:", followed by the
name of a condition, is used in General Rules and elsewhere to
indicate that the execution of a statement is unsuccessful, ap-
plication of General Rules, other than those of Subclause 12.3,
"<procedure>", and Subclause 20.1, "<direct SQL statement>", may
be terminated, diagnostic information is to be made available,
and execution of the statement is to have no effect on SQL-data or
schemas. The effect on <target specification>s and SQL descriptor
areas of an SQL-statement that terminates with an exception condi-
tion, unless explicitly defined by this International Standard, is
implementation-dependent.

Regards,
Ed Loehr

On Mon, Apr 24, 2000 at 01:10:55PM -0700, Joachim Achtzehnter wrote:

Perhaps, you can make the argument that an automatic rollback in all error
situations is compliant by claiming that all errors are unrecoverable. In
my view this is definitely against the spirit of the standard. As you said
yourself, all big-name databases behave according to my interpretation,
hence it is understandable that the authors of the standard didn't see a
need to spell this out more explicitly.

Joachim -
I see you haven't done much Language Lawyering, have you? There is no
such thing as the 'spirit' of the standard, only the written document.
;-) This is exactly my argument, with regard to errors and the standard:
_which_ errors are considered unrecoverable is not spelled out in the
standard, therefore, it is implementation defined. The fact the the
definition chosen by postgresql is inconvenient for users of the database
is, I agree, unfortunate, but it doesn't stand in the way of us claiming
compliance, which is the name of the game for these sort of standards.

Note that postgres is careful not to _automatically_ rollback: the
standard (as you quoted) indicated only certain conditions that allow for
an implicit rollback of that sort. Postgres just won't let you do anything
else in the current transaction. Yes, it's splitting hairs, but if you dig
into any of the 'bigname' DBs, you'll find similar split ends. Often, the
end is able to be split, i.e. the language in the standard is ambigious,
_because_ the commercial DB had representitives on the committee, making
sure the standard didn't get too far from their exisiting implementation.

I might even argue that the original definition was a good, conservative
choice, for the early days of postgres as a research database: you
_know_ people have been messing with the server code, and if something
throws an error, bailing out is the safest course. Now that the core
developers have done an amazing job at cleaning up and stabilizing
the code, a more relaxed attitude toward certain classes of errors is
desirable. There's been a fair amount of discussion about cleaning up
(defining!) the error codes returned, as well, so a complete overhaul
may be in the works. That'd clearly be the time to fix this up. I beleive
it's already on the TODO list.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

To All -
O.K., I give! I'm surprised at the number of people willing to dig into
the standards docs, and try to come up with justifications. Note the fact
that whole paragraphs must be quoted in order to get at the general tone
of the standard supports my argument that the behavior on error is _not_
spelled out in the standard: the exact case we're talking about is almost
conspicious by it's absence, given the number of other cases covered,
and the depth of the coverage. The rest of the standard is written in
such away that I keep thinking that all there Exception Conditions must
be defined somewhere, with appropriate actions, but, to the best of my
knowledge, they're not.

Makes me think there must have been a big commercial DB without good
error recovery with representitives on the committee ;-)

Suffice to say, the _right_ thing to do is make the users happy:
now that postgres is more robust, there's little reason not to allow
capture or 'recoverable' errors, and allow the DB developer to decide
when to rollback.

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Today, in a message to pgsql-general, Ross J. Reedstrom wrote:

I see you haven't done much Language Lawyering, have you?

No, and I have no intention of getting into it now :-)

There is no such thing as the 'spirit' of the standard, only the
written document. ;-)

The spirit of the standard comes into play when people who are not
Language Lawyers try to decide how something should work that is not
spelled out explicitly, but where the standard text contains suggestive
statements that imply that the authors assumed something without spelling
it out, because they thought everybody reading the standard would agree on
this as a matter of course. Of course, as soon as somebody comes along who
has some motivation to make a contrary assumption, perhaps to claim
compliance, the fact that the assumption was not spelled out leads to the
kinds of arguments we are having.

This is exactly my argument, with regard to errors and the standard:
_which_ errors are considered unrecoverable is not spelled out in the
standard, therefore, it is implementation defined. The fact the the
definition chosen by postgresql is inconvenient for users of the database
is, I agree, unfortunate, but it doesn't stand in the way of us claiming
compliance, which is the name of the game for these sort of standards.

This is precisely NOT the game I'm playing! I don't care whether something
is technically 100% compliant or not. I do care a lot about improving a
free software database management system that is in the same league as the
big-name databases.

The reason I entered this discussion was not to discuss whether postgresql
is or is not 100% compliant with SQL92. Supporting statement level aborts
is a feature that should be supported at some point, and this talk about
the current practice somehow being compliant with the letter of the
standard doesn't help.

Note that postgres is careful not to _automatically_ rollback: the
standard (as you quoted) indicated only certain conditions that allow for
an implicit rollback of that sort.

The standard is very explicit about some types of errors, namely
constraint violations, where it says that this must have no effect except
an entry in the diagnostics area. It is precisely these errors where one
would like to be able to continue the transaction.

Joachim

--
private: joachim@kraut.bc.ca (http://www.kraut.bc.ca)
work: joachim@mercury.bc.ca (http://www.mercury.bc.ca)

On Mon, Apr 24, 2000 at 02:41:46PM -0700, Joachim Achtzehnter wrote:

The spirit of the standard comes into play when people who are not
Language Lawyers try to decide how something should work that is not
spelled out explicitly, but where the standard text contains suggestive
statements that imply that the authors assumed something without spelling
it out, because they thought everybody reading the standard would agree on
this as a matter of course. Of course, as soon as somebody comes along who
has some motivation to make a contrary assumption, perhaps to claim
compliance, the fact that the assumption was not spelled out leads to the
kinds of arguments we are having.

While I agree with you in theory, that while many more casual standards
documents need to be read this way, the SQL standards are highly
engineered, passing through multiple national and international committee
bodies. Heck, any document that goes to the trouble to define the BNF
for <simple Latin letter>, let alone <digit> (section 5.1), clearly
aspires to being complete in and of itself. If that is so, omissions
are as significant as inclusions. As to other motives, the complete hash
that these same bodies have made of the SQL3 spec. leads me to believe
that every possible contrary assumption is already present.

is, I agree, unfortunate, but it doesn't stand in the way of us claiming
compliance, which is the name of the game for these sort of standards.

This is precisely NOT the game I'm playing! I don't care whether something
is technically 100% compliant or not. I do care a lot about improving a
free software database management system that is in the same league as the
big-name databases.

The reason I entered this discussion was not to discuss whether PostgreSQLql
is or is not 100% compliant with SQL92. Supporting statement level aborts
is a feature that should be supported at some point, and this talk about
the current practice somehow being compliant with the letter of the
standard doesn't help.

But it doesn't hurt (much). This is why we're having this discussion
on GENERAL, and not HACKERS: the developers have already agreed that
the error system needs an overhaul, mostly to provide the interface
writers with consistent error numbers, rather than the current text
strings. Inclusion of the ability to ignore some errors will happen.

I would not have started this branch of the discussion if the original
complaint had not ventured from 'other DBMSs' to 'SQL92 compliant DBMSs'
I was _very_ specific that the _only_ thing I disagree with in this
is being careful to not provide the enemy with ammunition, as it were,
and over interpret the standard to PostgreSQL's detriment. This is why
_not_ having this discussion can hurt. In order to aid acceptance of
PostgreSQL into many enviroments, being able to play the 'technically
SQL92 compliant' card, without having to cross your fingers behind your
back, is very important. Heck, I'd be wrestling with Oracle right now,
and had a lot less grant money to put into the hardware for my server,
if I hadn't been able to play the 'mostly SQL92 compliant, and climbing'
card.

The standard is very explicit about some types of errors, namely
constraint violations, where it says that this must have no effect except
an entry in the diagnostics area. It is precisely these errors where one
would like to be able to continue the transaction.

And this interpretation will guide the developers in _extending_
the standard in a consistent way. I know, because the developers that
implemented the constraints for 7.0 used this (and the SQL3 spec) as
guides. How's that?

Ross
P.S. I think we're in (quasi) violent agreement, don't you?
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Ross J. Reedstrom

And this interpretation will guide the developers in _extending_
the standard in a consistent way. I know, because the developers that
implemented the constraints for 7.0 used this (and the SQL3 spec) as
guides. How's that?

I don't know what is standard.
However as far as I see,few people prefer entire rollback on abort.
The problem is that PostgreSQL lacks a per statement rollback
functionality and unfortunately it isn't easy to implement.
Vadim has already planned the implementation. AFAIK one of the
purpose of WAL is to implement savepoint functionality. Savepoint
functionality would enable per statement rollback functionality easily.

The following is an extract of Vadim's posting about 10 months ago.

Re: [HACKERS] RE: [GENERAL] Transaction logging
Well, I'm thinking about WAL last two weeks. Hiroshi pointed me
problems in my approach to savepoints (when a tuple was marked
for update and updated after it) and solution would require
new tid field in header and both t_cmin/t_cmax => bigger header.
I don't like it and so I switched my mind -:).

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Hi David,

That can work if there's only one session using the database. But it's not
100% if there are multiple users. There's a small chance that a row may not
exist during the select, but exist by the time of the insert. If I'm wrong
please correct me - then I'll go optimize some code :).

By having the unorthodox locking mechanism suggested I can ensure at the
application level that no one else is going to insert stuff before my
select, update/insert, without having to lock the whole table.

So it will be
LOCK arbitrary
select
if exist update
else insert
UNLOCK arbitrary

Which would be faster- doing the lock arbitrary method, or doing an insert
with unique indexes and recovering if necessary (assuming postgresql does
what other databases do)? I suspect unique indexes could slow inserts and
updates down significantly.

If we don't want to do all that, how about we have a select for insert (and
update), which locks things? But I figured that it would be problematic to
implement in a number of scenarios tho.

Cheerio,

Link.

At 09:56 AM 24-04-2000 -0500, davidb@vectormath.com wrote:

Lincoln Yeoh wrote:

Hi David,

That can work if there's only one session using the database. But it's not
100% if there are multiple users. There's a small chance that a row may not
exist during the select, but exist by the time of the insert. If I'm wrong
please correct me - then I'll go optimize some code :).

By having the unorthodox locking mechanism suggested I can ensure at the
application level that no one else is going to insert stuff before my
select, update/insert, without having to lock the whole table.

So it will be
LOCK arbitrary
select
if exist update
else insert
UNLOCK arbitrary

Which would be faster- doing the lock arbitrary method, or doing an insert
with unique indexes and recovering if necessary (assuming postgresql does
what other databases do)? I suspect unique indexes could slow inserts and
updates down significantly.

If we don't want to do all that, how about we have a select for insert (and
update), which locks things? But I figured that it would be problematic to
implement in a number of scenarios tho.

PostgreSQL implements SELECT...FOR UPDATE to allow for the
sequence you'be described:

Session 1:

BEGIN;
SELECT webuser FROM webusers WHERE webuser = 'webuser1';

Session 2:

BEGIN;
UPDATE webusers SET webuser = 'webuser2' WHERE webuser =
'webuser1';*

*At this point Session 2 blocks, waiting for Session 1 to
complete. This prevents the race condition you've described and
only locks those rows which were selected by Session 1's SELECT.
With MVCC, table-level locking is largely a thing of the past.
The MVCC documentation describes this in detail:

http://www.postgresql.org/docs/postgres/mvcc4496.htm

Hope that helps,

Mike Mascari