Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Maxim Smyatkin]

Yes, it makes sence. I was wrong as the procedure still depends on the
table, so it's the intented behaviour. But what I still can't understand is
why did it work before? I mean, old tests were using Jaybird's
AutoCommitTransaction which is just a wrapper around Manageable
transaction, so it should give the same results. And my playing around
transactions supports it:

*cleanup...*

*In auto-commit transaction...*
*Successfully created table*
*Successfully created procedure*
*unsuccessful metadata update*
*-cannot delete*
*-COLUMN TEST_BLOB.ID *
*-there are 1 dependencies*
*Successfully dropped procedure*

*cleanup...*

*In manageable transaction...*
*Successfully created table*
*Successfully created procedure*
*Successfully dropped table*
*Successfully dropped procedure*

*cleanup...*

*In wrapped transaction...*
*Successfully created table*
*Successfully created procedure*
*Successfully dropped table*
*unsuccessful metadata update*
*-cannot delete*
*-COLUMN TEST_BLOB.ID *
*-there are 1 dependencies*
*Successfully dropped procedure*

*cleanup...*

Anyway, it looks like the current behaviour is actually the right one. But
it differs from the previous one.

On Sat, May 30, 2015 at 7:09 PM, Dmitry Yemanov  wrote:

> 29.05.2015 17:10, Maxim Smyatkin wrote:
> >
> > - we create a procedure P using a table T.
> > - the P states its interest in the T (while looking for P's
> dependencies);
> > - transaction commits the changes with retaining flag and forgets to say
> > that it isn't interested in the T anymore;
>
> Externally (for API user) it's the same transaction (even if having
> different ID), so it *is* still interested. If you restart some query
> against T in the same transaction, you should not get a "table not
> found" error. This is the idea behind existence locks.
>
>
> Dmitry
>
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>



-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin
#include 
#include 
#include 

using namespace std;

const char* db_name = "localhost:fbtest";

isc_db_handle attach();
bool detach(isc_db_handle* db);
bool executeStatement(const char* query, isc_tr_handle* tr, isc_db_handle* db, bool quiet = false);
isc_tr_handle startManageableTransaction(isc_db_handle* db);
isc_tr_handle startAutoCommitTransaction(isc_db_handle* db);
bool commit(isc_tr_handle* tr);
bool checkError(ISC_STATUS* status_vector, bool quiet = false);
void tryManageable();
void tryWrapped();
void tryAutocommit();
void cleanUp();

const char* CREATE_TABLE = "CREATE TABLE test_blob("
   "  id INTEGER, "
   "  bin_data BLOB, "
   "  char_data BLOB SUB_TYPE 1  "
   ")";

const char* DROP_TABLE = "DROP TABLE test_blob";

const char* CREATE_PROCEDURE =
"CREATE PROCEDURE test_procedure(id INTEGER, char_data BLOB SUB_TYPE 1) "
"AS BEGIN "
"  INSERT INTO test_blob(id, char_data) VALUES (:id, :char_data);"
"END";

const char* DROP_PROCEDURE = "DROP PROCEDURE test_procedure";

int main(void) {
cleanUp();
tryAutocommit();
cleanUp();
tryManageable();

// now in different order - to be 100% sure
/*cleanUp();
tryManageable();
cleanUp();
tryAutocommit();*/

cleanUp();
tryWrapped();
cleanUp();
}

void cleanUp() {
cout << endl << "cleanup..." << endl;
isc_db_handle db = attach();
isc_tr_handle tr = startManageableTransaction(&db);
executeStatement(DROP_TABLE, &tr, &db, true);
executeStatement(DROP_PROCEDURE, &tr, &db, true);
commit(&tr);
detach(&db);
}

void tryManageable() {
cout << endl << "In manageable transaction..." << endl;

isc_db_handle db = attach();
isc_tr_handle tr = startManageableTransaction(&db);
if (executeStatement(CREATE_TABLE, &tr, &db)) {
cout << "Successfully created table" << endl;
}
if (executeStatement(CREATE_PROCEDURE, &tr, &db)) {
cout << "Successfully created procedure" << endl;
}
if (executeStatement(DROP_TABLE, &tr, &db)) {
cout << "Successfully dropped table" << endl;
}
if (executeStatement(DROP_PROCEDURE, &tr, &db)) {
cout << "Successfully dropped procedure" << endl;
}
commit(&tr);
detach(&db);
}

void tryWrapped() {
cout << endl << "In wrapped transaction..." << endl;

isc_db_handle db = attach();
isc_tr_handle tr = startManageableTransaction(&db);
if (executeStatement(CREATE_TABLE, &tr, &db)) {
cout << "Successfully created table" << endl;
}
commit(&tr);
if (!tr) tr = startManageableTransaction(&db);
if (executeStatement(CREATE_PROCEDURE, &tr, &db)) {
cout << "Successfully created procedure" << endl;
}
commit(&tr);
if (!tr) tr = st

[Firebird-devel] [FB-Tracker] Created: (CORE-4821) grant create database to ROLE doesn`t work: "no permission for CREATE access to DATABASE ..."

[Pavel Zotov (JIRA)]

grant create database to ROLE doesn`t work: "no permission for CREATE access to 
DATABASE ..."
-

 Key: CORE-4821
 URL: http://tracker.firebirdsql.org/browse/CORE-4821
 Project: Firebird Core
  Issue Type: Bug
  Components: Security
Reporter: Pavel Zotov


Prepare:
===

set bail on;
shell del c:\temp\e30_bill_scott.fdb 2>nul;
set wng off;

set term ^;
execute block as
begin
  begin execute statement 'drop role db_creator'; when any do begin end end
end^
set term ;^
commit;

create or alter user bill_scott password '123';
create role db_creator;
commit;

revoke all on all from bill_scott;
revoke all on all from role db_creator;
commit;

grant create database to role db_creator;
grant db_creator to bill_scott;
--grant rdb$admin to bill_scott;

commit;

show grants;
commit;

EXIT; 


Output:
==

/* Grant permissions for this database */
GRANT DB_CREATOR TO BILL_SCOTT
GRANT CREATE DATABASE TO ROLE DB_CREATOR

Run:


set bail on;
set echo on;
connect 'localhost/:e30' user 'bill_scott' password '123' role 'DB_CREATOR';

set list on; select current_user, current_role, m.*, a.* from mon$database m 
join mon$attachments a on a.mon$attachment_id = current_connection;
commit;

create database 'localhost/:c:\temp\e30_bill_scott.fdb' user 'bill_scott' 
password '123' role 'DB_CREATOR';
commit;

set list on; select current_user, current_role, m.*, a.* from mon$database m 
join mon$attachments a on a.mon$attachment_id = current_connection;
commit;
drop database;

STDOUT:

connect 'localhost/:e30' user 'bill_scott' password '123' role 'DB_CREATOR';

set list on; select current_user, current_role, m.*, a.* from mon$database m 
join mon$attachments a on a.mon$attachment_id = current_connection;

USERBILL_SCOTT
ROLEDB_CREATOR
MON$DATABASE_NAME   C:\FBTESTING\qa\fbt-repo\tmp\E30.fdb
. . .
MON$OWNER   SYSDBA  
 
MON$SEC_DATABASEDefault
. . .
MON$USERBILL_SCOTT  
 
MON$ROLEDB_CREATOR  
 
MON$REMOTE_PROTOCOL TCPv4
MON$REMOTE_ADDRESS  127.0.0.1
. . .
MON$REMOTE_PROCESS  C:\1INSTALL\FIREBIRD\fb30sS\isql.exe
MON$CLIENT_VERSION  WI-T3.0.0.31845 Firebird 3.0 Beta 2
MON$REMOTE_VERSION  P13
MON$REMOTE_HOST balaha
MON$REMOTE_OS_USER  john smith
MON$AUTH_METHOD Srp
MON$SYSTEM_FLAG 0

commit;

create database 'localhost/:c:\temp\e30_bill_scott.fdb' user 'bill_scott' 
password '123' role 'DB_CREATOR';


STDERR:

Statement failed, SQLSTATE = 28000
no permission for CREATE access to DATABASE C:\TEMP\E30_BILL_SCOTT.FDB

(on attempt to perform statement: "create database 
'localhost/:c:\temp\e30_bill_scott.fdb' user 'bill_scott' password '123' 
role 'DB_CREATOR';").

PS. firebird.conf:
===
RemoteServicePort = 3330
SharedCache = false
SharedDatabase = true
DefaultDbCachePages = 256

AuthClient = Srp,Legacy_Auth,Win_Sspi
AuthServer = Srp,Legacy_Auth

ExternalFileAccess = Restrict C:\1INSTALL\FIREBIRD\FB30SNAP

FileSystemCacheThreshold = 65536K
LockHashSlots = 22111
MaxUserTraceLogSize = 9

UserManager = Srp
#WireCrypt = Disabled
===

FB version:  WI-T3.0.0.31845

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira



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

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Dmitry Yemanov]

29.05.2015 17:10, Maxim Smyatkin wrote:
>
> - we create a procedure P using a table T.
> - the P states its interest in the T (while looking for P's dependencies);
> - transaction commits the changes with retaining flag and forgets to say
> that it isn't interested in the T anymore;

Externally (for API user) it's the same transaction (even if having 
different ID), so it *is* still interested. If you restart some query 
against T in the same transaction, you should not get a "table not 
found" error. This is the idea behind existence locks.


Dmitry


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

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Vlad Khorsun]

29.05.2015 17:10, Maxim Smyatkin wrote:

> 2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in 
> Firebird transaction engine. When transaction started in
> auto-commit mode actually commits (via commit_retaining) it doesn't free any 
> resources. E.g., what happens in these tests is:
> - we create a procedure P using a table T.
> - the P states its interest in the T (while looking for P's dependencies);
> - transaction commits the changes with retaining flag and forgets to say that 
> it isn't interested in the T anymore;
> - we try to drop the T, but we can't because it is marked as "in use"!
> I attached a patch (for 2.5 branch) to release resources on commit_retaining. 
> But I believe the problem is rather in
> modify_procedure()'s code, because it should release rel_use_counts on its 
> own.

   Open cursors are left open after commit_retaining. Therefore i think it is
not correct to release existence locks as part of commit_retaining.

Regards,
Vlad

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

Re: [Firebird-devel] Semantics of isc_tpb_autocommit

[Vlad Khorsun]

30.05.2015 11:29, Mark Rotteveel wrote:
> To better understand the implications of the pull request by Maxim, I'd
> like to know the semantics of isc_tpb_autocommit. Is this documented
> anywhere?

   AFAIR, no

> I'd especially like to know at which point the commit is executed, and
> the impact this has on open resources (cursor, blob, etc). As far as I
> understand commit_retaining is used. What impact does that have on
> visibility of transactions and resource use?

   A bit simplified overview of autocommit code:

   when transaction, marked as TRA_autocommit performs any of following
actions, it is marked also as TRA_perform_autocommit:
- insert
- update
- delete
- select with lock
- post event

   TRA_perform_autocommit flag is checked when:
- engine receives input message
- engine send output message
- engine stars to execute request
- engine finished to execute DDL request

   When TRA_perform_autocommit flag is detected, engine run on-commit triggers
(not for DDL, btw, looks like a bug) and performs commit retaining. New
transaction have TRA_autocommit flag set and TRA_perform_autocommit not set.

Regards,
Vlad

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

Re: [Firebird-devel] Windows snapshot builds corrupted

[Dmitry Yemanov]

30.05.2015 11:42, Mark Rotteveel wrote:

> I just tried to download
> http://web.firebirdsql.org/download/snapshot_builds/win/3.0/Firebird-3.0.0.31845-0_x64.7z
> and the archive is only 3 MB (previous snapshot I downloaded was 7 MB).
> Unpacking gives an error that archive is corrupted.
>
> Another curious thing is that
> http://web.firebirdsql.org/download/snapshot_builds/win/3.0/ doesn't
> list any file sizes for Firebird-3.0.0.31845-0_x64.zip,
> Firebird-3.0.0.31845-0_x64_pdb.7z and
> Firebird-3.0.0.31845-0_x64_pdb.zip. Those downloads seem to be entirely
> empty.

Re-uploaded.


Dmitry



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

Re: [Firebird-devel] Semantics of isc_tpb_autocommit

[Dmitry Yemanov]

30.05.2015 11:29, Mark Rotteveel wrote:

> To better understand the implications of the pull request by Maxim, I'd
> like to know the semantics of isc_tpb_autocommit. Is this documented
> anywhere?

I'm not aware of any place.

> I'd especially like to know at which point the commit is executed, and
> the impact this has on open resources (cursor, blob, etc). As far as I
> understand commit_retaining is used. What impact does that have on
> visibility of transactions and resource use?

Autocommit is executed at the end of every execute/fetch DSQL API call 
and every start/send/receive/transact BLR API call, and *only* if that 
API call modified some data or posted some event.

As a side effect, this means that a select from a stored procedure that 
modifies data may cause multiple autocommits.

A regular commit-retaining is used internally, so the same 
visibility/resource characteristics apply.


Dmitry


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

Re: [Firebird-devel] Semantics of isc_tpb_autocommit

[Dmitry Yemanov]

30.05.2015 11:53, Mark Rotteveel wrote:

> I'd also like to know when isc_tpb_autocommit was introduced.

Somewhen before Firebird was created.


Dmitry


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

[Firebird-devel] [FB-Tracker] Created: (CORE-4820) Get "Different logins in connect and attach packets - client library error" when DENY to create database using valid pair login+password of user who

[Pavel Zotov (JIRA)]

Get  "Different logins in connect and attach packets - client library error" 
when DENY to create database using valid pair login+password of user who was 
granted this privilege


 Key: CORE-4820
 URL: http://tracker.firebirdsql.org/browse/CORE-4820
 Project: Firebird Core
  Issue Type: Bug
  Components: Engine, Security
Affects Versions: 3.0 Beta 2
Reporter: Pavel Zotov
 Attachments: trace-when-choose-NOT-to-create-database-as-user-john.zip

On WI-T3.0.0.31845

C:\TEMP>isql localhost/:e30 -user sysdba -pas masterke
Database:  localhost/:e30, User: sysdba
SQL> drop user john; commit;
SQL> revoke all on all from john;
SQL> commit;
SQL>
C:\TEMP>isql localhost/:e30 -user sysdba -pas masterke
Database:  localhost/:e30, User: sysdba
SQL> create or alter user john password '123';
SQL> revoke all on all from john;
Warning: ALL on ALL is not granted to JOHN.
SQL> grant create database to john;
SQL> create database 'localhost/:c:\temp\e30n.fdb' user 'john' password 
'123';
Commit current transaction (y/n)?n
Rolling back work.
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to 
set up a Firebird login.
-Different logins in connect and attach packets - client library error
SQL>

On LI-T3.0.0.31842
===
$ /opt/fb30ss/bin/isql localhost/:e30
Database:  localhost/:e30
SQL> drop user john; commit;
Statement failed, SQLSTATE = HY000
record not found for user: JOHN
SQL> revoke all on all from john;
Warning: ALL on ALL is not granted to JOHN.
SQL> commit;
SQL> exit;

$ /opt/fb30ss/bin/isql localhost/:e30 -user sysdba -pas masterke
Database:  localhost/:e30, User: sysdba
SQL> create or alter user john password '123';
SQL> revoke all on all from john;
Warning: ALL on ALL is not granted to JOHN.
SQL> grant create database to john;
SQL> create database 'localhost/:/var/db/fb30/e30n.fdb' user 'john' 
password '123';
Commit current transaction (y/n)?n
Rolling back work.
Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to 
set up a Firebird login.
-Different logins in connect and attach packets - client library error
SQL>

Trace shows that after pressing 'n' (as answer to rollback Tx) user `john` 
actually DOES some actions - see attach:
===
2015-05-30T15:10:29.6710 (4092:01FD3B60) FAILED CREATE_DATABASE
localhost/:c:\temp\e30n.fdb (ATT_0, JOHN, NONE, )

2015-05-30T15:10:29.6710 (4092:01FD3B60) ERROR AT JProvider::createDatabase
localhost/:c:\temp\e30n.fdb (ATT_0, JOHN, NONE, )
335544375 : unavailable database

2015-05-30T15:10:29.6710 (4092:01FD3B60) TRACE_FINI
SESSION_2  
===

PS.  Why I get "Your user name and password are not defined" if I decided only 
NOT  to *start*  'CREATE DATABASE' statement and wanted to return to SYSDBA 
session ?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira



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

[Firebird-devel] [FB-Tracker] Reopened: (CORE-4740) SIMILAR TO with quantifier {n, } in the pattern: 1) fails on 2.5 ("Invalid pattern"), 2) strange result in 3.0

[Pavel Zotov (JIRA)]

 [ 
http://tracker.firebirdsql.org/browse/CORE-4740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Pavel Zotov reopened CORE-4740:
---


Ticket should be reopened. Following query:
===
with
d(txt,ptn) as(
  select
'aggagg',   '(a{1}gg){2}' from rdb$database union all select
'aggagg',   '(a{0,}gg){2}' from rdb$database union all select
'aggagg',   '(a{1}__){2}' from rdb$database union all select
'aggagg',   '(a{1}__){1,}' from rdb$database union all select
'aggagg',   '(a{1}[b-z]{2}){2}' from rdb$database union all select
'XabaXa',   '([X](a|b){1,3}){2}' from rdb$database union all select
'XabaXaba', '([X](a|b){3}){2}' from rdb$database union all select
'XabaX','([X](a|b){0,3}){1,}' from rdb$database union all select
'XabaX','([X](a|b){0,3}){2}' from rdb$database union all select
'XaX',  '([X](a){0,1}){2}' from rdb$database union all select
'XaXa', '([X](a){1}){2}' from rdb$database union all select
'XaXa', '([X]a{1}){2}' from rdb$database
)
select txt, ptn, case when trim(txt) similar to trim(ptn) then 1 else 0 end 
is_match
from d;
===

-- shows totally different results in:

1. FB2.5:

TXT PTN IS_MATCH
aggagg  (a{1}gg){2} 1
aggagg  (a{0,}gg){2}1
aggagg  (a{1}__){2} 1
aggagg  (a{1}__){1,}1
aggagg  (a{1}[b-z]{2}){2}   1
XabaXa  ([X](a|b){1,3}){2}  0
XabaXaba([X](a|b){3}){2}1
XabaX   ([X](a|b){0,3}){1,} 0
XabaX   ([X](a|b){0,3}){2}  0
XaX ([X](a){0,1}){2}0
XaXa([X](a){1}){2}  1
XaXa([X]a{1}){2}1

2. FB 3.0:
===
TXT PTN IS_MATCH
aggagg  (a{1}gg){2} 0
aggagg  (a{0,}gg){2}0
aggagg  (a{1}__){2} 0
aggagg  (a{1}__){1,}1
aggagg  (a{1}[b-z]{2}){2}   0
XabaXa  ([X](a|b){1,3}){2}  0
XabaXaba([X](a|b){3}){2}0
XabaX   ([X](a|b){0,3}){1,} 0
XabaX   ([X](a|b){0,3}){2}  0
XaX ([X](a){0,1}){2}0
XaXa([X](a){1}){2}  0
XaXa([X]a{1}){2}0

3. Postgresql 9.4.2:

TXT PTN IS_MATCH_PG_94
aggagg  (a{1}gg){2} 1
aggagg  (a{0,}gg){2}1
aggagg  (a{1}__){2} 1
aggagg  (a{1}__){1,}1
aggagg  (a{1}[b-z]{2}){2}   1
XabaXa  ([X](a|b){1,3}){2}  1
XabaXaba([X](a|b){3}){2}1
XabaX   ([X](a|b){0,3}){1,} 1
XabaX   ([X](a|b){0,3}){2}  1
XaX ([X](a){0,1}){2}1
XaXa([X](a){1}){2}  1
XaXa([X]a{1}){2}1



> SIMILAR TO with quantifier {n,} in the pattern: 1) fails on 2.5 ("Invalid 
> pattern"),  2) strange result in 3.0
> --
>
> Key: CORE-4740
> URL: http://tracker.firebirdsql.org/browse/CORE-4740
> Project: Firebird Core
>  Issue Type: Bug
>Reporter: Pavel Zotov
>Assignee: Adriano dos Santos Fernandes
> Fix For: 3.0 Beta 2
>
>
> case-1: select iif( 'abcZ' similar to '[[:lower:]]{1,}Z', 1, 0) result from 
> rdb$database; -- NB: letter 'Z' - in upper case
> case-2: select iif( 'abcz' similar to '[[:lower:]]{1,}z', 1, 0) result from 
> rdb$database; -- NB: letter 'Z' - in lower case, as all preceding ones
> Result in WI-V2.5.4.26857 for both cases:
> ===
> Statement failed, SQLSTATE = 42000
> Invalid SIMILAR TO pattern
> ===
> Result in WI-T3.0.0.31780:
> SQL> select iif( 'abcZ' similar to '[[:lower:]]{1,}Z', 1, 0) result from 
> rdb$database;
>   RESULT
> 
>1
> SQL> select iif( 'abcz' similar to '[[:lower:]]{1,}z', 1, 0) result from 
> rdb$database;
>   RESULT
> 
>0
> Why second matching fails if we have three letters in lowercase left side 
> from 'z'  ? 
> If SIMILAR-engine count characters upto final one ('z') when it encounteres 
> {1,} than why it does NOT so in the following cases (and these are also seems 
> "invalid" for 2.5 as above):
> SQL> select iif( 'abcz' similar to '[[:lower:]]*z', 1, 0) result from 
> rdb$database;
>   RESULT
> 
>1
> SQL> select iif( 'abcz' similar to '[[:lower:]]+z', 1, 0) result from 
> rdb$database;
>   RESULT
> 
>1
> -- ?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira



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

Re: [Firebird-devel] Semantics of isc_tpb_autocommit

[Mark Rotteveel]

On 30-5-2015 10:29, Mark Rotteveel wrote:
> To better understand the implications of the pull request by Maxim, I'd
> like to know the semantics of isc_tpb_autocommit. Is this documented
> anywhere?
>
> I'd especially like to know at which point the commit is executed, and
> the impact this has on open resources (cursor, blob, etc). As far as I
> understand commit_retaining is used. What impact does that have on
> visibility of transactions and resource use?

I'd also like to know when isc_tpb_autocommit was introduced.

Mark
-- 
Mark Rotteveel

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

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Maxim Smyatkin]

Oh, and it worth mentioning - I tried it only with latest FB 2.5

On Sat, May 30, 2015 at 11:33 AM, Mark Rotteveel 
wrote:

> On 30-5-2015 10:21, Maxim Smyatkin wrote:
> > Surely I would like to see the same optimization in Jaybird 3, but I'm
> > currently more interested in 2.2.*.
>
> I understand and it will end up in 2.2, just not in 2.2.8 ;). This
> change has some implications that I need to understand better before I
> can release it.
>
> Mark
> --
> Mark Rotteveel
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>



-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin
--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] Windows snapshot builds corrupted

[Mark Rotteveel]

I just tried to download 
http://web.firebirdsql.org/download/snapshot_builds/win/3.0/Firebird-3.0.0.31845-0_x64.7z
 
and the archive is only 3 MB (previous snapshot I downloaded was 7 MB). 
Unpacking gives an error that archive is corrupted.

Another curious thing is that 
http://web.firebirdsql.org/download/snapshot_builds/win/3.0/ doesn't 
list any file sizes for Firebird-3.0.0.31845-0_x64.zip, 
Firebird-3.0.0.31845-0_x64_pdb.7z and 
Firebird-3.0.0.31845-0_x64_pdb.zip. Those downloads seem to be entirely 
empty.

Mark
-- 
Mark Rotteveel

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

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Mark Rotteveel]

On 30-5-2015 10:21, Maxim Smyatkin wrote:
> Surely I would like to see the same optimization in Jaybird 3, but I'm
> currently more interested in 2.2.*.

I understand and it will end up in 2.2, just not in 2.2.8 ;). This 
change has some implications that I need to understand better before I 
can release it.

Mark
-- 
Mark Rotteveel

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

[Firebird-devel] Semantics of isc_tpb_autocommit

[Mark Rotteveel]

To better understand the implications of the pull request by Maxim, I'd 
like to know the semantics of isc_tpb_autocommit. Is this documented 
anywhere?

I'd especially like to know at which point the commit is executed, and 
the impact this has on open resources (cursor, blob, etc). As far as I 
understand commit_retaining is used. What impact does that have on 
visibility of transactions and resource use?

Mark
-- 
Mark Rotteveel

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

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Maxim Smyatkin]

Yes, that's right. Commit_retaining doesn't release acquired resources and
it shouldn't. But I still believe it's a bug in the Firebird itself,
because procedure after creation should decrement relation use count, but
it doesn't. Any of the steam blob tests fail even when executed separetely.
Surely I would like to see the same optimization in Jaybird 3, but I'm
currently more interested in 2.2.*.

On Sat, May 30, 2015 at 10:58 AM, Mark Rotteveel 
wrote:

> On 29-5-2015 16:10, Maxim Smyatkin wrote:
> > Hello,
> > Recently we've got an information from some company that they would like
> > to use Firebird/Red Database with OpenCMS, but they couldn't afford it
> > because of bad performance. We compared it with PostgreSQL and it was
> > true - on some page views PG showed 4 seconds while for Firebird it was
> > almost 27 seconds. Skimming through performance audit log explained us,
> > that CMS executes lots (thousends) of small selects per page view. I
> > agree that it seems like a horrible design, but anyway PG handled it
> > much faster.
> > The reasons were:
> > 1. In auto-commit mode Jaybird wraps any statement into start/commit
> > calls. So, it does two additional calls to DBMS per statement;
> > 2. It doesn't really matter if the statement changes anything or not. It
> > executes start/commit in both cases.
> > Here is a pull request with changes to fix this behaviour using DBMS
> > API's auto-commit flag:
> > https://github.com/FirebirdSQL/jaybird/pull/1
> > Any further information about implementation or results is in comment to
> > the pull request.
> >
> > It seems to work excellent with OpenCMS, but Jaybird's unit tests show
> > several problems:
> >
> > 1. testBatch shows failure, because now every statement in the batch
> > execution mode is commited separately. This behaviour is perfectly fine
> > as JDBC specification states that you can't rely on batch mode +
> > auto-commit mode. Maybe, Firebird has some clients who rely on this
> > behaviour, I don't know. Anyway, you can wrap batch execution into
> > freshly started non auto-commit transaction, if you wish. I decided to
> > leave this decision up to you and didn't touch it.
>
> I am not sure if others depend on this behavior, but I'd prefer to keep
> the behavior of this as it was. Your suggestion to wrap it in its own
> transaction seems a good one, but it might be complex to implement (a
> lot of Jaybird is built around a "single active transaction" model of
> transactions.
>
> > 2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in
> > Firebird transaction engine. When transaction started in auto-commit
> > mode actually commits (via commit_retaining) it doesn't free any
> > resources. E.g., what happens in these tests is:
> > - we create a procedure P using a table T.
> > - the P states its interest in the T (while looking for P's
> dependencies);
> > - transaction commits the changes with retaining flag and forgets to say
> > that it isn't interested in the T anymore;
> > - we try to drop the T, but we can't because it is marked as "in use"!
> > I attached a patch (for 2.5 branch) to release resources on
> > commit_retaining. But I believe the problem is rather in
> > modify_procedure()'s code, because it should release rel_use_counts on
> > its own.
>
> As far as I know the keeping resources like the cursor and blobs open on
> commit_retaining is intentional (I believe it is one of the reasons for
> the "retaining" in the name). The error itself is probably related to
> the fact that some Jaybird tests unnecessarily cleanup after themselves.
>
> > 3. TestDriverConnectionPoolDataSource.testPreparedStatement fails with
> > an Error. I don't know why it happens and how to fix it. Looks like some
> > kind of race conditions, but I'm not sure. If anyone could look into it,
> > it would be awesome.
>
> I'll look into it. I hadn't noticed before that you made the pull
> request for Branch_2_2, I will first release Jaybird 2.2.8 (this
> weekend) before looking at it.
>
> >
> > Here is my commit for OpenCMS, so that it could effectively use modified
> > Jaybird:
> >
> https://github.com/Smyatkin-Maxim/opencms-core/commit/4b1901f4d339c4ec710ad84d316e70f694068dc8
> > I will issue pull request as soon as I verify that this Jaybird
> > improvement is fine.
>
> Mark
>
> --
> Mark Rotteveel
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>



-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin
--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Mark Rotteveel]

On 29-5-2015 16:10, Maxim Smyatkin wrote:
> Hello,
> Recently we've got an information from some company that they would like
> to use Firebird/Red Database with OpenCMS, but they couldn't afford it
> because of bad performance. We compared it with PostgreSQL and it was
> true - on some page views PG showed 4 seconds while for Firebird it was
> almost 27 seconds. Skimming through performance audit log explained us,
> that CMS executes lots (thousends) of small selects per page view. I
> agree that it seems like a horrible design, but anyway PG handled it
> much faster.
> The reasons were:
> 1. In auto-commit mode Jaybird wraps any statement into start/commit
> calls. So, it does two additional calls to DBMS per statement;
> 2. It doesn't really matter if the statement changes anything or not. It
> executes start/commit in both cases.
> Here is a pull request with changes to fix this behaviour using DBMS
> API's auto-commit flag:
> https://github.com/FirebirdSQL/jaybird/pull/1
> Any further information about implementation or results is in comment to
> the pull request.
>
> It seems to work excellent with OpenCMS, but Jaybird's unit tests show
> several problems:
>
> 1. testBatch shows failure, because now every statement in the batch
> execution mode is commited separately. This behaviour is perfectly fine
> as JDBC specification states that you can't rely on batch mode +
> auto-commit mode. Maybe, Firebird has some clients who rely on this
> behaviour, I don't know. Anyway, you can wrap batch execution into
> freshly started non auto-commit transaction, if you wish. I decided to
> leave this decision up to you and didn't touch it.

I am not sure if others depend on this behavior, but I'd prefer to keep 
the behavior of this as it was. Your suggestion to wrap it in its own 
transaction seems a good one, but it might be complex to implement (a 
lot of Jaybird is built around a "single active transaction" model of 
transactions.

> 2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in
> Firebird transaction engine. When transaction started in auto-commit
> mode actually commits (via commit_retaining) it doesn't free any
> resources. E.g., what happens in these tests is:
> - we create a procedure P using a table T.
> - the P states its interest in the T (while looking for P's dependencies);
> - transaction commits the changes with retaining flag and forgets to say
> that it isn't interested in the T anymore;
> - we try to drop the T, but we can't because it is marked as "in use"!
> I attached a patch (for 2.5 branch) to release resources on
> commit_retaining. But I believe the problem is rather in
> modify_procedure()'s code, because it should release rel_use_counts on
> its own.

As far as I know the keeping resources like the cursor and blobs open on 
commit_retaining is intentional (I believe it is one of the reasons for 
the "retaining" in the name). The error itself is probably related to 
the fact that some Jaybird tests unnecessarily cleanup after themselves.

> 3. TestDriverConnectionPoolDataSource.testPreparedStatement fails with
> an Error. I don't know why it happens and how to fix it. Looks like some
> kind of race conditions, but I'm not sure. If anyone could look into it,
> it would be awesome.

I'll look into it. I hadn't noticed before that you made the pull 
request for Branch_2_2, I will first release Jaybird 2.2.8 (this 
weekend) before looking at it.

>
> Here is my commit for OpenCMS, so that it could effectively use modified
> Jaybird:
> https://github.com/Smyatkin-Maxim/opencms-core/commit/4b1901f4d339c4ec710ad84d316e70f694068dc8
> I will issue pull request as soon as I verify that this Jaybird
> improvement is fine.

Mark

-- 
Mark Rotteveel

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