[Firebird-devel] "set decfloat bind double precision" and others

[liviuslivius]

Hi
 
there are
 
SET DECFLOAT BIND double precision;
SET TIME ZONE BIND
 
Is there a way to read current settings?
Is there a way to restore it then?
eg.
 
var_previous = GET DECFLOAT BIND;
 
SET DECFLOAT BIND double precision;
 
/* calculate something */
 
/* and now restore previous settings */
 
SET DECFLOAT BIND :var_previous;
 
And what is the scope of those settings? Are they assigned to whole connection 
or current transaction?
 
 
regards,
Karol Bieniaszewski
 
 
 Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] "set decfloat bind double precision" and others

[Mark Rotteveel]

On 9-9-2019 12:35, liviuslivius wrote:

Hi
there are
SET DECFLOAT BIND double precision;
SET TIME ZONE BIND
Is there a way to read current settings?
Is there a way to restore it then?
eg.
var_previous = GET DECFLOAT BIND;
SET DECFLOAT BIND double precision;
/* calculate something */
/* and now restore previous settings */
SET DECFLOAT BIND :var_previous;
And what is the scope of those settings? Are they assigned to whole 
connection or current transaction?


They apply for the connection until you issue an `alter session reset` 
or change the value. An `alter session reset` will either reset to the 
default (native), or to the bind configuration specified in the database 
parameter buffer.


Note that `alter session reset` will reset other things as well, so it 
might be better to explicitly revert using `set decfloat bind native`.


I'm not aware of an option to query the current configuration.

Mark
--
Mark Rotteveel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Inserts and FKs

[Rashid Abzalov]

Behavior of Oracle 11 + Postgres 9.6.

*Common DDL*:
create table master (id number primary key);
create table detail (id number primary key, master number);
alter table detail add foreign key (master) references master(id) on delete
cascade;

*Oracle (2 sqlplus windows)*:
tx1:
set transaction isolation level read committed;
insert into master(ID) values(1);

tx2:
set transaction isolation level serializable;

tx1:
commit;

tx2:
insert into detail(ID, master) values(1, 1);

ERROR at line 1:
ORA-08177: can't serialize access for this transaction

*Postgres (2 psql windows):*
tx1:
begin transaction isolation level read committed;
insert into master(ID) values(1);

tx2:
begin transaction isolation level serializable;

tx1:
commit;

tx2:
insert into detail(ID, master) values(1, 1);

ERROR:  insert or update on table "detail" violates foreign key constraint
"il_master_fkey"
DETAIL:  Key (master)=(1) is not present in table "master".

пт, 6 сент. 2019 г. в 22:38, Carlos H. Cantu :

> Thanks Ann!
>
> So far all the opinions are that the currently behavior is wrong (or
> inconsistent, at last).
>
> I'll be glad if someone who has other RDBMS installed can compare how
> they behave with the exactly described scenario, and report the
> results back here. Please pay attention in the transaction isolations
> and make sure they are started/commit in the correct times.
>
> Thanks!
>
> Carlos
> http://www.firebirdnews.org
> FireBase - http://www.FireBase.com.br
>
>
> AH> Cheers,
>
>
> AH> Ann
>
> >> On Sep 6, 2019, at 8:24 AM, Mark Rotteveel 
> wrote:
> >>
> >>> On 6-9-2019 01:46, Carlos H. Cantu wrote:
> >>> I understand that there are other scenarios where the currently FK
> >>> behavior is correct and makes sense, for example, in the case of
> >>> avoiding deleting a master record with "commited but not visible
> >>> childs",
>
> AH> Yes.  Unique, Primary Key, and Foreign Key constraints are
> AH> handled in a special omniscient mode to avoid concurrent,
> AH> incompatible changes. Triggers and check constraints operate in
> AH> the mode of the user transaction.
>
> >>> but for the reported example, the currently behavior looks
> >>> incorrect, and for people with business logic implemented in triggers,
> >>> it may/will lead to incorrect results.
> >>
> >> I think you're right. You should only be able to insert records that
> reference records that are visible to your transaction. Given Tx2 started
> before Tx1 committed, the effects from Tx1 aren't visible to your
> transaction. Your insert in Tx2 should fail as the master record from Tx1
> doesn't exist from the perspective of Tx2.
>
> AH> Interesting.  In the case of inserting a child, the master must
> AH> be visible to the transaction doing the insert.  In the case of
> AH> deleting a master, the existence of a child - even if uncommitted must
> block the delete.
> >>
> >>> Does anyone knows if this behavior is following the Standard?
> >>
> >> I don't think this behaviour is correct in view of the standard, but I
> haven't looked it up.
> >>
>
> AH> No, this behavior is not standard compliant.
>
> AH> Good luck,
>
> AH> Ann
>
>
>
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Inserts and FKs

[Carlos H. Cantu]

Title: Re: [Firebird-devel] Inserts and FKs


Thank you!

[]s
Carlos
http://www.firebirdnews.org
FireBase - http://www.FireBase.com.br





Behavior of Oracle 11 + Postgres 9.6.

Common DDL:
create table master (id number primary key);
create table detail (id number primary key, master number);
alter table detail add foreign key (master) references master(id) on delete cascade;

Oracle (2 sqlplus windows):
tx1:
set transaction isolation level read committed;
insert into master(ID) values(1);

tx2:
set transaction isolation level serializable;

tx1:
commit;

tx2:
insert into detail(ID, master) values(1, 1);

ERROR at line 1:
ORA-08177: can't serialize access for this transaction

Postgres (2 psql windows):
tx1:
begin transaction isolation level read committed;
insert into master(ID) values(1);

tx2:
begin transaction isolation level serializable;

tx1:
commit;

tx2:
insert into detail(ID, master) values(1, 1);

ERROR:  insert or update on table "detail" violates foreign key constraint "il_master_fkey"
DETAIL:  Key (master)=(1) is not present in table "master".

пт, 6 сент. 2019 г. в 22:38, Carlos H. Cantu :




Thanks Ann!

So far all the opinions are that the currently behavior is wrong (or
inconsistent, at last).

I'll be glad if someone who has other RDBMS installed can compare how
they behave with the exactly described scenario, and report the
results back here. Please pay attention in the transaction isolations
and make sure they are started/commit in the correct times.

Thanks!

Carlos
http://www.firebirdnews.org
FireBase - http://www.FireBase.com.br


AH> Cheers,


AH> Ann

>> On Sep 6, 2019, at 8:24 AM, Mark Rotteveel  wrote:
>>
>>> On 6-9-2019 01:46, Carlos H. Cantu wrote:
>>> I understand that there are other scenarios where the currently FK
>>> behavior is correct and makes sense, for example, in the case of
>>> avoiding deleting a master record with "commited but not visible
>>> childs",

AH> Yes.  Unique, Primary Key, and Foreign Key constraints are
AH> handled in a special omniscient mode to avoid concurrent,
AH> incompatible changes. Triggers and check constraints operate in
AH> the mode of the user transaction.

>>> but for the reported example, the currently behavior looks
>>> incorrect, and for people with business logic implemented in triggers,
>>> it may/will lead to incorrect results.
>>
>> I think you're right. You should only be able to insert records that reference records that are visible to your transaction. Given Tx2 started before Tx1 committed, the effects from Tx1 aren't visible to your transaction. Your insert in Tx2 should fail as the master record from Tx1 doesn't exist from the perspective of Tx2.

AH> Interesting.  In the case of inserting a child, the master must
AH> be visible to the transaction doing the insert.  In the case of
AH> deleting a master, the existence of a child - even if uncommitted must block the delete.
>>
>>> Does anyone knows if this behavior is following the Standard?
>>
>> I don't think this behaviour is correct in view of the standard, but I haven't looked it up.
>>

AH> No, this behavior is not standard compliant.  

AH> Good luck,

AH> Ann



Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel








Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] "set decfloat bind double precision" and others

[liviuslivius]

>>I'm not aware of an option to query the current configuration.Will be good to 
>>have it. Consider situation when in some stored proc you need different 
>>behvior. You do not know to what walue you should restore settings, as you do 
>>not have a way to read it at start of proc.Regards,Karol Bieniaszewski
nullFirebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ODP: ODP: ODP: ODP: Inserts and FKs

[Vlad Khorsun]

07.09.2019 19:35, Ann Harrison wrote:
...

It's also an old bug, probably dating to the implementation of foreign keys in
InterBase.

The obvious implementation - validate the foreign key in the context of the client transaction - fails miserably in snapshot mode 
when the parent record is deleted by one transaction and a matching child record is inserted by a second concurrent transaction. 
  That leads to orphaned child records, which is very wrong.


The next possible implementation is to use the same internal omniscient mode that maintains unique and primary key constraints.  The 
omniscient mode sees the current state of the database is that there is a committed parent record that matches the proposed insert. 
  That  eliminates the orphan child problem, but introduces the problem Carlos discovered.  The transaction that stores the child 
record can "see" the master record for the purpose of validating the relationship between the two records, but for no other purpose. 
  That's slightly obscure case - updating the master when a child is stored tends to create a hotspot - but it's certainly legitimate.


  So, to fix this we need to repeat validation of master record in context of 
client transaction,
correct ? Of course, it should be made for concurrency (snapshot) transaction 
only and if current
state of master record is not visible for client transaction.


Regards,
Vlad



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] "set decfloat bind double precision" and others

[Mark Rotteveel]

On 9-9-2019 14:37, liviuslivius wrote:

 >>I'm not aware of an option to query the current configuration.

Will be good to have it. Consider situation when in some stored proc you 
need different behvior. You do not know to what walue you should restore 
settings, as you do not have a way to read it at start of proc.


Why would you use these settings in a stored procedure? They are meant 
to influence the bind information communicated between the client and 
server. A stored procedure shouldn't touch it in my opinion. If you want 
equivalent behavior in a stored procedure, you need to apply a cast.


Mark

--
Mark Rotteveel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] "set decfloat bind double precision" and others

[liviuslivius]

Maybe this sample will be better. Some sql tool which need run itself some 
internall queries but user need different settings for he/she queries by this 
tool.P.S. I also think about introducing some new SET command for sql plan 
format retrived in MON$Statements i need to know if i should implement some 
read command for this setting. I see that maybe it is not needed to read. But i 
always like to know the current settings.Maybe instead of SET commands better 
will be to use writable context variables? It is feature with already 
implemented read/write operations.But maybe SET is something from some 
standard?Regards,Karol Bieniaszewski
nullFirebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ODP: ODP: ODP: ODP: Inserts and FKs

[Ann Harrison]

> On Sep 9, 2019, at 9:45 AM, Vlad Khorsun  wrote:
> 
> 07.09.2019 19:35, Ann Harrison wrote:
> ...
>> It's also an old bug, probably dating to the implementation of foreign keys 
>> in
>> InterBase.
>> ...it's certainly legitimate.
> 
>  So, to fix this we need to repeat validation of master record in context of 
> client transaction,
> correct ?

Yes, I think so.  Only in the case of an insert by a snapshot transaction 
inserting a child record.  The current test returns an error in  the case of a 
recently deleted parent record. We need a second test to verify that th  master 
is visible to the inserting transaction. 

> Of course, it should be made for concurrency (snapshot) transaction only and 
> if current
> state of master record is not visible for client transaction.
> 

Right.  A small number of inserts will fail that might have succeeded, but the 
problem Carlos demonstrated will be gone. (I think)

Cheers,

Ann
> 
> 
> 
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel