1

Topic: Error at usage Merge in FB3

Server Version: WI-V3.0.3.32891 Firebird 3.0
Test example

CREATE TABLE TEST_MERGE (
PR_ID INTEGER NOT NULL;
PR_PRICE NUMERIC (18,5) NOT NULL;
CNT_PLUS NUMERIC (8,3) DEFAULT 0;
CNT_MINUS NUMERIC (8,3) DEFAULT 0
);
ALTER TABLE TEST_MERGE ADD CONSTRAINT PK_TEST_MERGE PRIMARY KEY (PR_ID, PR_PRICE);

Request

merge
into test_merge d
using
(
select 1 id, 100.54 pr, 2.4 cnt from rdb$database
union all
select 1 id, 100.4 pr, 8.6 cnt from rdb$database
union all
select 1 id, 100.54 pr, 21 cnt from rdb$database
union all
select 1 id, 100.54 pr, 7.3 cnt from rdb$database
union all
select 1 id, 100.4 pr, 2 cnt from rdb$database
union all
select 1 id, 100. pr, 2.4 cnt from rdb$database
) as dd
on (d.pr_id = dd.id) and (d.pr_price = dd.pr)
WHEN MATCHED THEN
UPDATE SET
d.cnt_plus = d.cnt_plus + dd.cnt
WHEN NOT MATCHED THEN
insert (pr_id, pr_price, cnt_plus)
values (dd.id, dd.pr, dd.cnt)

Produces an error
Invalid insert or update value (s): object columns are constrained - no 2 table rows can have duplicate column values.
violation of PRIMARY or UNIQUE KEY constraint "PK_TEST_MERGE" on table "TEST_MERGE".
unknown ISC error 335545072.
The source in Megre demands unique rowset, using group by request is fulfilled.
For FB2.5 errors were not. It is an error or behavior change? In dock for FB3:
"Each record of a source is used for
Updates (sentence UPDATE) or removals (sentence DELETE) one or more
Purpose records, or insertions (sentence INSERT) records in the destination table, or for this purpose;
For another."

2

Re: Error at usage Merge in FB3

Dairy Alexander;
The stable cursor does not allow request to see own changes.
So group by here just that is necessary.

3

Re: Error at usage Merge in FB3

hvlad;
I.e. now there will be such behavior?

4

Re: Error at usage Merge in FB3

Dairy Alexander;
Yes, now there is such, correct, behavior.
See http://tracker.firebirdsql.org/browse/CORE-3362 and connected with it

5

Re: Error at usage Merge in FB3

hvlad;
It turns out now that if in a source not the unique line always will be insert, update will not be will be fulfilled. Then likely it is necessary to specify in dock the description merge

6

Re: Error at usage Merge in FB3

Alexander wrote:

turns out now that if in a source not the unique line always will be insert, update will not be will be fulfilled

It is impossible.

7

Re: Error at usage Merge in FB3

Alexander wrote:

if in a source not the unique line always will be insert, update will not be will be fulfilled.

Sometime and this bug correct also such source will cause an error.

8

Re: Error at usage Merge in FB3

hvlad wrote:

yes, now there is such, correct, behavior.
See http://tracker.firebirdsql.org/browse/CORE-3362 and connected with it

Vlad, and what "correct" behavior should be at command UPDATE OR INSERT INTO MATCHING (<PRIMARY KEY>)?
If at attempt to fulfill this slander I receive the same Violation of PRIMARY or UNIQUE KEY constraint
How such can happen? The server 3.0.3.32900

9

Re: Error at usage Merge in FB3

Maxim Kovalenko wrote:

Vlad, and what "correct" behavior should be at command UPDATE OR INSERT INTO MATCHING (<PRIMARY KEY>)?
If at attempt to fulfill this slander I receive the same Violation of PRIMARY or UNIQUE KEY constraint
How such can happen? 3.0.3.32900

It would be desirable the server with an example.

10

Re: Error at usage Merge in FB3

H'm, that's just the point, that an error floating.
That is:
1. The table where record is interposed
2. On trigger AI the stage from pair procedures twitches
3. And on extreme procedure also it is caused UPDATE OR INSERT INTO
As result an error.
I looked on a diagonal of Pashiny scripts in , very much at it all .
Can it is finite more close to Violation PK at an intensive data interchange between tables. I can not understand the reason.
The nastiest that it on distributed system at reception of a portion of the data periodically arises such .
The exchange program if there is an error at reception of a file dataful, next time again tries to load the same file, but on a trace. Time is accepted normally. I.e. it is not possible while to receive a played back example.
Can eat thoughts as such to catch?

11

Re: Error at usage Merge in FB3

Alexander wrote:

turns out now that if in a source not the unique line always will be insert, update will not be will be fulfilled. Then likely it is necessary to specify in dock the description merge

Uniqueness here at all and. MERGE produces connection RIGHT JOIN of the destination table and a source. If record corresponding to a connection condition is found in the destination table, for it is fulfilled UPDATE (or DELETE), fur-trees it is not found INSERT. And again interposed records are not visible in results of connection (since 3.0 and it under the standard). Therefore no stipulations in this respect in the documentation are present. On the contrary, there are stipulations in the documentation 2.5 because in it this behavior was not standard.
And generally under standard MERGE should produce an error if under a condition of connection of a source and the destination table gets more than 1 record, but now it does not become. I.e. the same record under the standard cannot is updated some times.

12

Re: Error at usage Merge in FB3

Maxim Kovalenko wrote:

As such can happen?

Parallel transactions, curve parameters of insulation, wrong system of key generation,
Garbage collection is not in time. Plus any combination from the aforesaid. You an error entirely
To begin with read. There there is also a title of restriction and value which break it.

13

Re: Error at usage Merge in FB3

hvlad wrote:

It would be desirable with an example.

UPDATE OR INSERT the same record from parallel transactions  results
To an error in one of them. At missing record it will be key violation, at
Present - the update conflict. Also it is the regular mechanics. But optimists think that it
- Tablet from conflicts. In vain.

14

Re: Error at usage Merge in FB3

Dimitry Sibiryakov wrote:

garbage collection is not in time

Yes well?

15

Re: Error at usage Merge in FB3

Dimitry Sibiryakov wrote:

from parallel transactions

What there is  a transaction?
If one transaction gets into another and can prevent it it means that "sturgeon rotten".
I ask not to confuse with !

16

Re: Error at usage Merge in FB3

SQL2008;
What else ? Where found

17

Re: Error at usage Merge in FB3

SQL2008 wrote:

That there is  a transaction?

What is not present in MS SQL: the transaction executable simultaneously from the given. Not very well in the same
Or other session.

18

Re: Error at usage Merge in FB3

Dimitry Sibiryakov;
You did not add
"What is not present in MS SQL: the transaction executable simultaneously with given"
In the same connection.
In different connections at them transactions quite parallely exist.
Besides, it seems to me that a question

SQL2008 wrote:

If one transaction gets into another and can prevent it it means that "sturgeon rotten".

More likely concerns rottenness of blocking server MS SQL.
In  nobody hinders to change the data read by another transactions, and about any "rottenness" of speech cannot be.

19

Re: Error at usage Merge in FB3

kdv wrote:

concerns rottenness of blocking server MS SQL more likely.
In  nobody hinders to change the data read by another transactions, and about any "rottenness" of speech cannot be.

About rottenness MS SQL it is vain. I it not the defender, but there am implemented all normally.
After 2005 servers when  , it became absolutely normal.

wrote:

what is not present in MS SQL: the transaction executable simultaneously from the given. Not very well in the same
Or other session.

It is strongly told! . you suppose, what in MS SQL there is no possibility to work in the transaction executable simultaneously with another? Even in one session?
I correctly understood you?

20

Re: Error at usage Merge in FB3

SQL2008;
In ms sql there is no more than one transaction in one connection. Speech about it.

21

Re: Error at usage Merge in FB3

Dimitry Sibiryakov wrote:

UPDATE OR INSERT the same record from parallel transactions  results
To an error in one of them. At missing record it will be key violation, at
Present - the update conflict. Also it is the regular mechanics. But optimists think that it
- Tablet from conflicts. In vain.

Failure at a record insertion in the table "SGM". ID_SGM = 2253453392155252.

Violation of PRIMARY or UNIQUE KEY constraint "PK_TSTTN" on table "TSTTN".
Problematic key value is ("ID_TSTTN" = 2252130937471591).
At procedure ' TSTTN_RESET_ONE ' line: 968, col: 5
At procedure ' TSTTN_RESET_BY_GM ' line: 11, col: 19
At trigger ' SGM_AI_SET_TSGM ' line: 59, col: 1.

understood.
Here really there was such situation. It was necessary to get to the bottom long
I.e. that happened. In one concerning long transaction (1 with copecks) the data which on a stage, through UPDATE OR INSERT created in table TSTTN record with ID 2252130937471591 were interposed and, running forward, any conflict of update did not cause for in other short transaction interposed into table SGM record and the same on a stage business reached to UPDATE OR INSERT in table TSTTN and at  the error report (above) got out. Well and it is natural .
And that is why 1 "long" told nothing also all transited regularly because by the time of  2nd already fell off?

22

Re: Error at usage Merge in FB3

Maxim Kovalenko;
Restrictions in Firebird are checked at once, not on .

23

Re: Error at usage Merge in FB3

SQL2008 wrote:

. you suppose that in MS SQL there is no possibility to work in the transaction executable
Simultaneously with another?

Yes, I so suppose, being based on your surprise concerning the term "the parallel
Transaction "and any delirium about" gets into another and can prevent it ". If in
Any server one transaction cannot absolutely prevent another in any way, means
They are fulfilled strictly sequentially. Well and MS - it is pure from .

24

Re: Error at usage Merge in FB3

kdv wrote:

SQL2008;
In ms sql there is no more than one transaction in one connection. Speech about it.

Pancake! And muzhiks do not know that !

25

Re: Error at usage Merge in FB3

SQL2008 wrote:

After 2005 servers when  , it became absolutely normal.

I do not want to investigate into discussion  to sturgeon, but  I will dare to remind a catechism.  is when one transaction  record and, and then record , and another at first , and then and. And here  also rise in the puzzled pose. . Therefore it and , that is tightly. And  it, , is not present well simply any possibility,  as on the classic one of transactions to roll away, and that only if   wait. All remaining mentions  in vain are  and apocryphal stories, with all that it implies in the plan  brains each other arguing, each of which  about the, without hearing the interlocutor.