Обсуждение: Abort state on duplicated PKey in transactions

Поиск
Список
Период
Сортировка

Abort state on duplicated PKey in transactions

От
Haroldo Stenger
Дата:
Hi dear people,

Now I'm reposting this on hackers from general, sorry if no fully
suitable.

We are building a RAD tool (GeneXus) support, for PostgreSQL, as I have
mentioned before a number of times.

A problem which arose, is that within a transaction, if one inserts on a
table and the PK restriction is violated, the transaction aborts and
leaves itself in abort state. One has to END the transaction and start
a new one. This is a problem, in large transactions, where lots of
things have been done to the database, and an insertion is to be done,
which may yield an error just because the PK already existed. The whole
transaction should have to be redone if the insertion failed. A
solution, could be to query for the existance of the PK, just before the
insertion. But there is a little span between the test and the
insertion, where another insertion from another transaction could void
the existance test. Any clever ideas on how to solve this? Using
triggers maybe? Other solutions?

I'm aware that savepoints and nested transactions will be implemented in
future versions, but how to solve the problem before that starts
working?

Thanks

Regards,
Haroldo.


Re: Abort state on duplicated PKey in transactions

От
"Matthew T. O'Connor"
Дата:
> A solution, could be to query for the existance of the PK, just before the
> insertion. But there is a little span between the test and the
> insertion, where another insertion from another transaction could void
> the existance test. Any clever ideas on how to solve this? Using
> triggers maybe? Other solutions?
>

All you need to do is use a sequence.  If you set the sequence to be the
primary key with a default value of nextval(seq_name) then you will never
have a collision.  Alternatly if you need to know that number before you
start inserting you can select next_val(seq_name) before you inser and use
that.  By the way the datatype serial automates exactly what I described.



Re: Abort state on duplicated PKey in transactions

От
Haroldo Stenger
Дата:

"Matthew T. O'Connor" wrote:
> 
> > A solution, could be to query for the existance of the PK, just before the
> > insertion. But there is a little span between the test and the
> > insertion, where another insertion from another transaction could void
> > the existance test. Any clever ideas on how to solve this? Using
> > triggers maybe? Other solutions?
> >
> 
> All you need to do is use a sequence.  If you set the sequence to be the
> primary key with a default value of nextval(seq_name) then you will never
> have a collision.  Alternatly if you need to know that number before you
> start inserting you can select next_val(seq_name) before you inser and use
> that.  By the way the datatype serial automates exactly what I described.

Yes, but there are situations where a sequenced PK isn't what is needed.
Imagine a DW app, where composed PKs such as (ClientNum, Year, Month,
ArticleNum) in a table which has ArticleQty as a secondary field are
used, in order to consolidate detail record from other tables. There,
the processing cycle goes like checking for the existance of the PK, if
it exists, add ArticleQtyDetail to ArticleQty, and update; and if it
doesn't exist, insert the record with ArticleQtyDetail as the starting
value of ArticleQty. See it? Then, if between the "select from" and the
"insert into", other process in the system (due to parallel processing
for instance) inserts a record with the same key, then the first
transaction would cancel, forcing redoing of all the processing. So,
sort of atomicity of the check?update:insert operation is needed. How
can that be easily implemented using locks and triggers for example?

Regards,
Haroldo.


Re: Abort state on duplicated PKey in transactions

От
Lincoln Yeoh
Дата:
I had a similar issue.

I needed to make sure I had a unique row- insert if not there, update if
there. 

So I resorted to locking the whole table, then select, then insert/update.

What Tom told me to do was to use lock table tablename in exclusive mode
for my case.

This blocks select for updates, but doesn't block selects.

So you must check with a select for update, then only do the insert if it's
ok.

If you don't check with a select for update it will not block, and bad
things could happen :).

However I couldn't do a "for update" with an aggregate, so in my
generalised "putrow" routine I can't use "in exclusive mode".

I basically wanted to do a select count(*) from datable where whereclause
for update.

If the count was 0 then only insert, else if 1 update, else make some noise
:).

The alternative is to actually fetch the rows which can be slower.

Regards,
Link.


At 12:20 PM 08-09-2001 -0500, Haroldo Stenger wrote:
>transaction should have to be redone if the insertion failed. A
>solution, could be to query for the existance of the PK, just before the
>insertion. But there is a little span between the test and the
>insertion, where another insertion from another transaction could void
>the existance test. Any clever ideas on how to solve this? Using
>triggers maybe? Other solutions?




Re: Abort state on duplicated PKey in transactions

От
"Henshall, Stuart - WCP"
Дата:
I believe LOCK TABLE <table> IN EXCLUSIVE MODE should block everything but
selects, but it locks for the entire transaction I think. Maybe in tcl you
could create your own locking using global variables. If the spin lock code
is available to user functions you might be able to use that.
Alternativley, inside a plpgsql function, could you use something like this:

INSERT INTO ex_tbl (a,b,pk) SELECT var1 AS a,var2 AS b,var3 AS pk WHERE NOT
EXISTS (SELECT * FROM ex_tbl WHERE pk=var3) LIMIT 1;
GET DIAGNOSTICS rc =ROW_COUNT;

where pk is the primary key is the primary key of ex_tbl.
if rc=0 then you'd know the primary key already existed and if rc=1 then it
would have inserted succesfully
- Stuart

"Haoldo Stenger" wrote:

> "Matthew T. O'Connor" wrote:
> > 
> > > A solution, could be to query for the existance of the PK, just before
> the
> > > insertion. But there is a little span between the test and the
> > > insertion, where another insertion from another transaction could void
> > > the existance test. Any clever ideas on how to solve this? Using
> > > triggers maybe? Other solutions?
> > >
> > 
> > All you need to do is use a sequence.  If you set the sequence to be the
> > primary key with a default value of nextval(seq_name) then you will
> never
> > have a collision.  Alternatly if you need to know that number before you
> > start inserting you can select next_val(seq_name) before you inser and
> use
> > that.  By the way the datatype serial automates exactly what I
> described.
> 
> Yes, but there are situations where a sequenced PK isn't what is needed.
> Imagine a DW app, where composed PKs such as (ClientNum, Year, Month,
> ArticleNum) in a table which has ArticleQty as a secondary field are
> used, in order to consolidate detail record from other tables. There,
> the processing cycle goes like checking for the existance of the PK, if
> it exists, add ArticleQtyDetail to ArticleQty, and update; and if it
> doesn't exist, insert the record with ArticleQtyDetail as the starting
> value of ArticleQty. See it? Then, if between the "select from" and the
> "insert into", other process in the system (due to parallel processing
> for instance) inserts a record with the same key, then the first
> transaction would cancel, forcing redoing of all the processing. So,
> sort of atomicity of the check?update:insert operation is needed. How
> can that be easily implemented using locks and triggers for example?
> 
> Regards,
> Haroldo.


Re: Abort state on duplicated PKey in transactions

От
Haroldo Stenger
Дата:
Thanks. You saved me work by pointing me to the FOR UPDATE detail, and
the aggregate non-locking restriction. Can anyone comment on why this is
so? Reposting in HACKERS was a good idea :)

Side note: GeneXus (http://www.genexus.com) support, will be formally
announced in 10 days in an important international event
(http://www.artech.com.uy/cgi-bin/webartech/HEvver01.exe?S,131,0,10).
This will leverage PostgreSQL in the business environments which GeneXus
deals with. Is anyone interested in receiving more information on what
GeneXus is and does?

Regards,
Haroldo.

Lincoln Yeoh wrote:
> 
> I had a similar issue.
> 
> I needed to make sure I had a unique row- insert if not there, update if
> there.
> 
> So I resorted to locking the whole table, then select, then insert/update.
> 
> What Tom told me to do was to use lock table tablename in exclusive mode
> for my case.
> 
> This blocks select for updates, but doesn't block selects.
> 
> So you must check with a select for update, then only do the insert if it's
> ok.
> 
> If you don't check with a select for update it will not block, and bad
> things could happen :).
> 
> However I couldn't do a "for update" with an aggregate, so in my
> generalised "putrow" routine I can't use "in exclusive mode".
> 
> I basically wanted to do a select count(*) from datable where whereclause
> for update.
> 
> If the count was 0 then only insert, else if 1 update, else make some noise
> :).
> 
> The alternative is to actually fetch the rows which can be slower.
> 
> Regards,
> Link.
> 
> At 12:20 PM 08-09-2001 -0500, Haroldo Stenger wrote:
> >transaction should have to be redone if the insertion failed. A
> >solution, could be to query for the existance of the PK, just before the
> >insertion. But there is a little span between the test and the
> >insertion, where another insertion from another transaction could void
> >the existance test. Any clever ideas on how to solve this? Using
> >triggers maybe? Other solutions?
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl


Re: Abort state on duplicated PKey in transactions

От
Haroldo Stenger
Дата:
Hi dear all,

Now, we need to know if it is possible from the ODBC interface to access to
diagnostic registers like "GET DIAGNOSTICS rc =ROW_COUNT". It seems not to
work from odbc, maybe it need some changes to work. Can anybody help?,
thanks.

"Henshall, Stuart" wrote:
> I believe LOCK TABLE  IN EXCLUSIVE MODE should block everything but
> selects, but it locks for the entire transaction I think. Maybe in tcl you
> could create your own locking using global variables. If the spin lock code
> is available to user functions you might be able to use that.
> Alternativley, inside a plpgsql function, could you use something like this:
> 
> INSERT INTO ex_tbl (a,b,pk) SELECT var1 AS a,var2 AS b,var3 AS pk WHERE NOT
> EXISTS (SELECT * FROM ex_tbl WHERE pk=var3) LIMIT 1;
> GET DIAGNOSTICS rc =ROW_COUNT;
> 
> where pk is the primary key is the primary key of ex_tbl.
> if rc=0 then you'd know the primary key already existed and if rc=1 then it
> would have inserted succesfully
> - Stuart
> 
> "Haoldo Stenger" wrote:
> 
> > "Matthew T. O'Connor" wrote:
> > >
> > > > A solution, could be to query for the existance of the PK, just before
> > the
> > > > insertion. But there is a little span between the test and the
> > > > insertion, where another insertion from another transaction could void
> > > > the existance test. Any clever ideas on how to solve this? Using
> > > > triggers maybe? Other solutions?
> > > >
> > >
> > > All you need to do is use a sequence.  If you set the sequence to be the
> > > primary key with a default value of nextval(seq_name) then you will
> > never
> > > have a collision.  Alternatly if you need to know that number before you
> > > start inserting you can select next_val(seq_name) before you inser and
> > use
> > > that.  By the way the datatype serial automates exactly what I
> > described.
> >
> > Yes, but there are situations where a sequenced PK isn't what is needed.
> > Imagine a DW app, where composed PKs such as (ClientNum, Year, Month,
> > ArticleNum) in a table which has ArticleQty as a secondary field are
> > used, in order to consolidate detail record from other tables. There,
> > the processing cycle goes like checking for the existance of the PK, if
> > it exists, add ArticleQtyDetail to ArticleQty, and update; and if it
> > doesn't exist, insert the record with ArticleQtyDetail as the starting
> > value of ArticleQty. See it? Then, if between the "select from" and the
> > "insert into", other process in the system (due to parallel processing
> > for instance) inserts a record with the same key, then the first
> > transaction would cancel, forcing redoing of all the processing. So,
> > sort of atomicity of the check?update:insert operation is needed. How
> > can that be easily implemented using locks and triggers for example?
> >
> > Regards,
> > Haroldo.
>