from:"Vlad Khorsun via Firebird\-devel"

Re: [Firebird-devel] PostgreSQL 13 can efficiently handle duplicate data in B-tree indexes, the standard database index.

2020-10-20 Thread Vlad Khorsun via Firebird-devel


19.10.2020 12:42, marius adrian popa wrote:

 From pg release notes
https://www.postgresql.org/about/news/postgresql-13-released-2077/
https://www.postgresql.org/docs/13/btree-implementation.html#BTREE-DEDUPLICATION

ps: I don't know if Firebird can be optimized in a similar


  AFAIU, PG tried to fix its own issue. Firebird is free from similar problem 
thanks
to prefix compression of index keys. Duplicate chains already contains no 
duplicated
keys. Feel free to correct me, if necessary.

Regards,
Vlad


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

Re: [Firebird-devel] Conversion error from string to date in Firebird 4

2020-10-20 Thread Vlad Khorsun via Firebird-devel


20.10.2020 13:43, Dmitry Yemanov wrote:

20.10.2020 13:40, Roman Simakov wrote:



Because documentantion here
(https://firebirdsql.org/en/firebird-date-literals/) says they are valid
separators.


Keep in mind, that link is an excerpt from Helen Borrie's Firebird Book
from 2004 (Firebird 1.5 era). It is descriptive of what worked at the
time, it is not prescriptive as to how Firebird is supposed to behave.


Why not? For me and lots of other developers, who have been using such
code, it's exactly how Firebird is supposed to behave :)
In other words, what are the reasons to break the backward compatibility?


AFAIU, it was discussed here in February 2018, thread "Valid date or not".


  I've re-read that thread quickly and I saw nor final decision, nor proposition
to change (or break) rules for traditional (legacy) date\time types (without 
TZ).

Regards,
Vlad


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

Re: [Firebird-devel] RFC: External Connections Pool

2018-06-18 Thread Vlad Khorsun via Firebird-devel


   All,

   I going to merge into master implementation of pool of external connections.
The feature was initially developed more than a year ago, and works at 
production
with good feedback.

   Some bugs was fixed since then, so it should be stable enough and definitely
good for beta release of v4. The branch ExternalConnectionsPool was created at
our repository 3 months ago, btw ;)


  The branch is merged into master, finally

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ALTER SESSION RESET not allowed if there are transactions?

2018-06-10 Thread Vlad Khorsun via Firebird-devel




  Mark, all

  I just committed changes as we discussed above. I.e. session reset now ignores
prepared transactions, rollback currently active user transaction and start new
one, issue warning if user transaction made changes in tables.

Here is how it looks now:

SQL> alter session reset;
Statement failed, SQLSTATE = 01002
Cannot reset user session
-There are open transactions (2 active)
SQL>
SQL> commit;
SQL> set autoddl off;
SQL>
SQL> alter session reset;
SQL>
SQL> insert into x values (current_transaction);
SQL> select * from x;

  ID

 311
 362

SQL> alter session reset;
Session was reset with warning(s)
-Transaction is rolled back due to session reset, all changes are lost
SQL>
SQL> select * from x;

  ID

 311

SQL> select current_transaction from rdb$database;

  CURRENT_TRANSACTION
=
  363

SQL> set warning off;
SQL> insert into x values (current_transaction);
SQL> select * from x;

  ID

 311
 363

SQL> alter session reset;
SQL> select * from x;

  ID

 311

SQL> select current_transaction from rdb$database;

  CURRENT_TRANSACTION
=
  364

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] TRANSACTION START trigger

2018-06-08 Thread Vlad Khorsun via Firebird-devel


08.06.2018 12:16, Dimitry Sibiryakov wrote:

08.06.2018 11:01, Vlad Khorsun via Firebird-devel wrote:

   Another question is how to handle rollback error. I mean rollback that run
at catch block. I offer to ignore rollback error (maybe log it into 
firebird.log,
if database is not bug-checked) - we can do nothing with it anyway.


   I'd prefer rollback errors to be considered as bugchecks because they leave 
database in undefined (most likely broken) state.


  I try to avoid bugchecks as much as possible. In many cases it could be 
replaced
by usual error and not kill all other activity in database. It was more or less 
ok
in CS (as it affects just one connection) but it is overkill in many cases when 
we
speak about SS.

  As for rollback errors - it could happen due to bugcheck (and we don't need to
raise another one) or by another reason. In later case it is hard to imagine
how dead transaction could make database state broken. To be more precise - how 
it
could make additional harm after original error (say, disk write failure). Also,
note, bugcheck handling it not ideal currently. For example it not flushes page
cache and this really could make more damage than it could be.


Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] TRANSACTION START trigger

2018-06-08 Thread Vlad Khorsun via Firebird-devel


  All,

  I have a questions about error handling after TRANSACTION START trigger and
I want to clear my doubts or fix the bug (if confirmed).

  From README.db_triggers.txt:

- TRANSACTION START
Triggers are fired in the newly user created transaction - uncaught
exceptions are returned to the client and the transaction is rolled-back.

  There are two places in code when TRIGGER_TRANS_START is fired:
1. jrd.cpp\start_transaction(), and
2. StmtNodes.cpp\InAutonomousTransactionNode::execute()

  The first case looks wrong - it just pass exception to the client and 
nullifies
client's transaction handle. It is nor rolled back transaction, nor allows to do
it at client side (transaction handle is lost).

  I think in this case transaction should be rolled back (in catch block).


  The second case also have no immediate error handler and let error to be
handled later by EXE (execution engine). It should pass control flow to the
branch (in the same routine) where request->req_operation == req_unwind and
it run TRANSACTION ROLLBACK trigger and only then rollback the transaction.
This is correct if error happens when some statement is executed in given
autonomous transaction. But i doubt we should run TRANSACTION ROLLBACK trigger
if TRANSACTION START trigger failed.

  In this case i think we should work as above - put TRIGGER_TRANS_START into
try block, rollback transaction at catch block, restore state as it was before
transaction start and re-throw exception.

  Another question is how to handle rollback error. I mean rollback that run
at catch block. I offer to ignore rollback error (maybe log it into 
firebird.log,
if database is not bug-checked) - we can do nothing with it anyway.

  Opinions ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ALTER SESSION RESET not allowed if there are transactions?

2018-06-04 Thread Vlad Khorsun via Firebird-devel

02.06.2018 19:54, Mark Rotteveel wrote:

On 2-6-2018 17:56, Vlad Khorsun via Firebird-devel wrote:

02.06.2018 18:07, Mark Rotteveel wrote:

I just saw the following commit:
https://github.com/FirebirdSQL/firebird/commit/bbf8348817c4592999fc137b18ba1be7326ad42d

This disallows execution of ALTER SESSION RESET if there are transactions
active. I think this is too restrictive.

Only from client POV and only at first look. Think about at as replacement
of detach\attach calls. At detach you have no active transactions or it will
fail with error.

For example, in Jaybird queries executed through the JDBC API will always be executed in a transaction. With this change, this
statement must be executed without transaction, which means I must either include explicit support to detect this statement, or
people need to break out of the JDBC API and use the underlying API, both are options I want to avoid (especially the last one).
I think the same applies for other drivers.

Could you add new method to reset connection ?
Will it "break out of the JDBC API" ?

That method would not be part of the JDBC API, so it would not be easily accessible. Technically, JDBC 4.3 introduced a feature
where I could execute a session reset, but the rules for that specific API limits its usefulness and flexibility compared to being
able to execute it like a normal statement.

And it would be hard to use for users in earlier Java versions (or people using older Jaybird versions) to use ALTER SESSION RESET
without breaking out of the JDBC API to the Firebird-extensions to that API or even to the underlying internal API.

And it would make it harder to use this with external libraries like connection pool libraries (which allow init on checkout of the
pool by executing a statement).

BTW, does JDBC API requires that any statement shoud run within explicit
transaction ? Does Jaybird allow to not start implicit transaction with
statement ? Does Jaybirs support execution of "SET TRANSACTION" statement ?
Also, all new session management statements could run with no transaction
context.

JDBC API requires that statements are executed in a transaction. In theory I can break that rule, but the problem with that is that
it requires explicit handling, which is 1) annoying to do and 2) brittle given the other API requirements involving transactions;
and given my current health issues I don't see myself implementing that anytime soon.

And no, Jaybird has no specific handling for SET TRANSACTION, because the JDBC API specifies that users should use the methods
defined in the API for things like transaction configuration, etc.

All above is a good explanation why generic\universal API is bad :)

BTW, looks like implicit transaction start (not supported by Firebird)
could solve this issues, agree ?

_"all new session management statements could run with no transaction context."_

There is a big difference between _could_ and _must_.

For flexibility sake, especially in the light of drivers whose API makes it harder to execute transaction-less (Jaybird and Firebird
ADO.net, but AFAIK also FDB/pyfirebirdsql, possibly others), Firebird should allow execution of these statements in a transaction.

Don't you think that 3 API calls where just one is enough not looks perfect ?

Well, i understand your point and will not argue against it (while nor like it
nor agree with it).
Consider returning a warning if execution of ALTER SESSION RESET occurs within a transaction. If you must restrict it to only a
single active transaction that's fine.

Such warning is useless. Users will never read it. Engine not benefit from it
too.

I could even live with a rule that this must be the first statement of a transaction (maybe even with requiring it to be the only
statement of a transaction).

What do you think if engine will internally rollback immediately before reset
and start new transaction (with the same properties as old one) after reset ?
Transaction handles will not be changed. I.e. for end user it looks like
rollback
retaining but not retains old context.

Instead I propose that execution of ALTER SESSION RESET within a transaction will not fail if the current transaction is the only
active transaction of the connection.

It must check too much things to not break it by reset. And list of things
to check could be changed in the future.

And I think that restricting ALTER SESSION RESET to only transaction-less execution will make it hard to use, and restricts its
usefulness.

I strongly not agree here but... see above

It's currently not even possible to execute it from ISQL. In ISQL you get the
error

"""
Statement failed, SQLSTATE = 01002
Cannot reset user session with open transactions (2 active)
"""

isql will be adjusted a bit later

As an aside, the sqlstate is 01002 is warning(!) "disconnect error" acc

Re: [Firebird-devel] ALTER SESSION RESET not allowed if there are transactions?

2018-06-02 Thread Vlad Khorsun via Firebird-devel

02.06.2018 18:07, Mark Rotteveel wrote:

I just saw the following commit:
https://github.com/FirebirdSQL/firebird/commit/bbf8348817c4592999fc137b18ba1be7326ad42d

This disallows execution of ALTER SESSION RESET if there are transactions
active. I think this is too restrictive.

Only from client POV and only at first look. Think about at as replacement
of detach\attach calls. At detach you have no active transactions or it will
fail with error.

Could you add new method to reset connection ?
Will it "break out of the JDBC API" ?

Instead I propose that execution of ALTER SESSION RESET within a transaction will not fail if the current transaction is the only
active transaction of the connection.

It must check too much things to not break it by reset. And list of things
to check could be changed in the future.

Also, I'm not 100% sure, but it also looks like the current restriction also doesn't allow for prepared but not yet committed
transactions. Prepared transactions are not active, and presence of these should not block execution of ALTER SESSION RESET.

If you speak about 2PC tranaction in prepared state - it is really active
and must be committed or rolled back (as any other active transaction).
If it in limbo state - it is inactive already.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-29 Thread Vlad Khorsun via Firebird-devel

29.05.2018 2:06, Adriano dos Santos Fernandes wrote:

On 28/05/2018 09:13, Vlad Khorsun via Firebird-devel wrote:

   There is still no agreement. Current offers:
- single ON SESSION RESET trigger
- pair of new triggers: BEFORE SESSION RESET and AFTER SESSION RESET
- use existing ON CONNECT and ON DISCONNECT triggers and introduce a way
to distinguish
   true connect\disconnect from reset (for ex. new system function like
INSERTING\UPDATING\
   DELETING)

I said again my position that got uncommented here:

  It was answered, but i can repeat again

Make RESET reset to the state defined after the ON CONNECT trigger,
instead of the initial (before ON CONNECT).

  It means we should know what changes was made by ON CONNECT trigger.
I.e. we need to save names and values of context variables, save values
of session properties and save contents of GTT's.
  Looks overkill for me.

> So ON CONNECT can define context variables that is not reset.

  You mean - all context variables in USER_SESSION namespace that was
assigned by ON CONNECT trigger ? Or you speak about some way to mark
context variables as "DON'T RESET" ?

And add BEFORE / AFTER RESET triggers.

So heavy attachment data may be maintained in session or reset manually
via the AFTER RESET trigger when necessary.

>>I strongly suggest to consider existing DISCONNECT\CONNECT triggers
>> also. I think, most of the code will be the same in both set of events
>> therefore it is very questionable if we need another pair of triggers.
>>
>
> Yes, but for me it seems as a hack. User can always do a single SP and
> call it on both triggers.

  Hmm... do you consider INSERT OR UPDATE OR DELETE triggers as hack also ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-28 Thread Vlad Khorsun via Firebird-devel

28.05.2018 18:03, Dimitry Sibiryakov wrote:

28.05.2018 16:45, Vlad Khorsun via Firebird-devel wrote:
On one hand third option would be consistent with behavior of isc_detach_database() on client. On the other hand server cannot
handle such error and it will result in endless transaction with all consequences.

Not sure i understand you, explain please.

Currently when isc_detach_database() is called with some transaction(s) active, it yeld an error "cannot disconnect database with
open transactions" which is exactly what you have suggested for reset. How are you going to handle this error in pool's code?

First, such error means some bug in pool code. Second, there is special case
for
application that should disconnect forcibly:
fb_cancel_operation(fb_cancel_abort).

I personally hate current isc_detach_database() behavior because there is no sane way to handle the error by code, so I would
vote for the second option: rollback transaction (and may be issue/log a warning).

What is sane way, for you ?

Take decision to commit or rollback active transactions. But right code already does it before disconnect, so the only way to get
the disconnect error is a bug which loose transaction handle. Without handle, transaction rollback with isc_rollback_transaction()
is impossible.

Good, i have the same opinion.

Do you aware of fb_cancel_operation(fb_cancel_abort) ?

No. This function is new for me.

So, its time to read README.fb_cancel_operation and\or ReleaseNotes.
It is documented since introduction at 2.5.0

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-28 Thread Vlad Khorsun via Firebird-devel


28.05.2018 15:57, Dimitry Sibiryakov wrote:

28.05.2018 14:13, Vlad Khorsun via Firebird-devel wrote:

- forcebly rollback active transactions and reset connection
   same as if connection was broken
- raise error and don't reset connection.

   Obviously, first case is not an option and we must choose between second and 
third.
So far i prefer to raise error and don't reset.


   On one hand third option would be consistent with behavior of isc_detach_database() on client. On the other hand server cannot 
handle such error and it will result in endless transaction with all consequences.


  Not sure i understand you, explain please.

   I personally hate current isc_detach_database() behavior because there is no sane way to handle the error by code, so I would 
vote for the second option: rollback transaction (and may be issue/log a warning).


  What is sane way, for you ? Do you aware of 
fb_cancel_operation(fb_cancel_abort) ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-28 Thread Vlad Khorsun via Firebird-devel

All,

Some update on this topic

18.05.2018 19:44, Vlad Khorsun via Firebird-devel wrote:

All,

I going to merge into master implementation of pool of external connections.
The feature was initially developed more than a year ago, and works at
production
with good feedback.

According to discussion in this topic i made following changes:

- New system privilege MODIFY_EXT_CONN_POOL to manage pool properties (instead
of
initiallly used 'ALTER DATABASE' user right).

- New session management statement ALTER SESSION RESET is implemented (see
CORE-5832).
Ticket is not marked as closed for a while as i want to make sure
implementation
is complete. Thus any suggestions, notes, etc are welcome.

The issue that left unresolved is what to do if there is active
transaction(s) exists
at the connection about to be reset. The possible answers is:
- do nothing and reset connection
if there is active transaction that made changes of GTT ON COMMIT PRESERVE
and if it
will be rolled back (or just undo savepoint) - there will be bugcheck, as
GTT's was
reset and contains no pages
- forcebly rollback active transactions and reset connection
same as if connection was broken
- raise error and don't reset connection.

Obviously, first case is not an option and we must choose between second and
third.
So far i prefer to raise error and don't reset.

I still have some questions to agree (or not):

- Should connections pool always use ALTER SESSION RESET when connection become
idle ?
At first looks - yes, it should. It allows to make (almost) no difference
for user
code with non-pooled connections. But from practical POV i have some doubts
and want
to discuss it here:
- pre v4 Firebird versions have no such statement and it will fail (of course
connections pool should handle it correctly)
- it adds at least one additional network roundtrip which could be
unnecessary if
user code doesn't alter session and not uses GTT ON COMMIT PRESERVE on
remote side.
For some applications it could add too much performance penalty for nothing

The committed code always reset connection and closes it if reset was failed
by any reason.

- At tracker there was proposition to add new database trigger ON RESET which
should
fire when ALTER SESSION RESET is run. Should we implement it ?

There is still no agreement. Current offers:
- single ON SESSION RESET trigger
- pair of new triggers: BEFORE SESSION RESET and AFTER SESSION RESET
- use existing ON CONNECT and ON DISCONNECT triggers and introduce a way to
distinguish
true connect\disconnect from reset (for ex. new system function like
INSERTING\UPDATING\
DELETING)

I prefer to not introduce new triggers.

- If i missed something else - please tell me.

Please, read and comment:

https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool

It was updated according to suggestions in this topic.

Updated again.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-27 Thread Vlad Khorsun via Firebird-devel


23.05.2018 15:07, Adriano dos Santos Fernandes wrote:

On 23/05/2018 08:51, Vlad Khorsun via Firebird-devel wrote:

23.05.2018 13:40, Adriano dos Santos Fernandes wrote:

On 23/05/2018 06:48, Dimitry Sibiryakov wrote:


    In this case I guess there must be two triggers: BEFORE RESET and
AFTER RESET.


I like this more than the other approaches.


   I strongly suggest to consider existing DISCONNECT\CONNECT triggers
also. I think, most of the code will be the same in both set of events
therefore it is very questionable if we need another pair of triggers.



Yes, but for me it seems as a hack. User can always do a single SP and
call it on both triggers.


  Hmm... do you consider INSERT OR UPDATE OR DELETE triggers as hack also ?


I have an idea with I think is good.

What if RESET resets not to the initial (before ON CONNECT) state, but
to the state present after ON CONNECT?


  It means we should know what changes was made by ON CONNECT trigger.
I.e. we need to save names and values of context variables, save values
of session properties and save contents of GTT's.
  Looks overkill for me.


So ON CONNECT can define context variables that is not reset.


  You mean - all context variables in USER_SESSION namespace that was
assigned by ON CONNECT trigger ? Or you speak about some way to mark
context variables as "DON'T RESET" ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-25 Thread Vlad Khorsun via Firebird-devel


25.05.2018 18:33, Dimitry Sibiryakov wrote:

25.05.2018 17:26, Vlad Khorsun via Firebird-devel wrote:

I.e. all works a bit slower than before v4.


   I think that it can be a good motivation to upgrade remote server to v4.


  Bad joke

All works much faster than before v4. 


   Do you have some numbers for this "much" to see if speed overweight support 
problems?


  My local testing show 5-10 times faster execution in artificial tests, iirc.
And both servers was on same host.
You may try it by yourself, the code was published 3 months ago.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-25 Thread Vlad Khorsun via Firebird-devel


25.05.2018 17:05, Dimitry Sibiryakov wrote:

25.05.2018 15:53, Vlad Khorsun via Firebird-devel wrote:

   Remote statement could check some context variable and run this or that 
branch
of code in dependence of variable value. Then it could assign another value to
this context variable. It all could be done in the single stored procedure call
(which could call another SP and so on).
Then run same remote statement again at another local transaction.

   With pre-v4 remote server there could be *new* connection with *empty* 
context variable
or reused (and *not reset*) connection with *some value* in context variable.


   I see.
   Let me summarize in simple words:

1) Local v3, remote v3: always new connection with empty variables.
2) Local v3, remote v4: always new connection with empty variables.
3) Local v4, remote v3: behavior may vary:
3.1) Implemented (1): always new connection with empty variables.

+ pool is effectively disabled and give no benefits
+ overhead executing statement that can't be handled by remote
I.e. all works a bit slower than before v4.


3.2) Implemented (2): some connection with some values in variables.

+ pool works and add big performance benefits
+ overhead executing statement that can't be handled by remote
All works much faster than before v4.

3.3) Implemented (3): some connection with some values in variables in
simple case. Pool works. No overhead. User could prevent unwanted contex
reuse but it is not always easy to do.
All works much faster than before v4.


4) Local v4, remote v4: some connection with empty variables.

   Is this table right?


  Now it is more complete

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-25 Thread Vlad Khorsun via Firebird-devel

25.05.2018 16:40, Dimitry Sibiryakov пишет:

25.05.2018 15:28, Vlad Khorsun via Firebird-devel wrote:

We have local server v4 and remote server v3. v4 runs external statements
against v3
and remote sessions have some context that is re-used by remote statements
somehow.
Then remote server is upgraded to v4 and remote sessions gets reset on re-use.
Now
remote statements can't re-use that context and behaviour could be changed.

Ok. When you are getting a connection from a pool and when return it? I was sure that these point are exactly the same as
current connect and disconnect of external connection. Was my guess wrong again?

It is correct.

Then I don't see how behavior of external connection can change: if currently it is completely closed, situation "remote sessions
have some context that is re-used by remote statements somehow" is impossible.

Remote statement could check some context variable and run this or that branch
of code in dependence of variable value. Then it could assign another value to
this context variable. It all could be done in the single stored procedure call
(which could call another SP and so on).
Then run same remote statement again at another local transaction.

With pre-v4 remote server there could be *new* connection with *empty*
context variable
or reused (and *not reset*) connection with *some value* in context variable.

When remote server is upgraded and connection begin
to be reset instead of closed, everything will be the same from local side POV or do you see any difference between brand new
connection and connection after reset which I overlook?

With v4 remote server there could be *new connection* with *empty* context
variable
or reused (and *reset*) connection with *empty* context variable.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-25 Thread Vlad Khorsun via Firebird-devel

24.05.2018 12:01, Mark Rotteveel wrote:

On 2018-05-24 01:08, Vlad Khorsun via Firebird-devel wrote:

24.05.2018 0:39, Dimitry Sibiryakov wrote:
So far we have following propositions:

1. Always reset external connection when it gets out of use. Close connection if
any kind of error happens.

It actually disables connections pool for pre-v4 remote servers. It could be
done by disabling pool in config.

I think we can be slightly more fine-grained by checking the error code(s).

And (2) below is exactly about it

2. Always reset external connection when it gets out of use. Do not
close connection
if syntax error happens and let it to be re-used as is.

[..]

BTW, we could backport ALTER SESSION RESET into v3 and even into
v2.5: it should
just truncate GTT's and clear user context variables as pre-v4 engines
can't change
other session properties.

I think that is a good idea.

Accounted

3. Never reset external connection when it gets out of use.

It also could make system work differently - when local system was
upgraded from
v3 to v4 and start to use connection pooling *and* if remote
statements depends on
session-scoped data. But in this case user could run 'ALTER SESSION
RESET' when it
is required and get correct behaviour. It requires coding discipline and careful
planning but possible. It have no overhead on session reset when it is
not needed.

But that will be awkward, because each EXECUTE STATEMENT ON EXTERNAL will obtain a (possibly) different connection from the pool, so
an EXECUTE doing a session reset followed by another EXECUTE could be using different statements. This means one would have to use
an EXECUTE BLOCK that does both the session reset (will that even be possible from execute block?) and then the other statement. I
think this will make solutions less flexible.

Ignored, as you wrote in another mesage.

4. Implement EXTERNAL DATA SOURCE database object and one of its property should
be flag to [not] reset external connection on re-use.

This is the best solution (as for me) but it might not fit into v4 release
schedule.

What did i missed ?

I disagree, I think using an unconditional session reset will be no problem, because in existing use cases of external connections,
there won't any expectation of preserving session state. In its current form, EXECUTE STATEMENT WITH COMMON TRANSACTION within the
same transaction will use the same connection anyway (and changing that will break things), and across transactions, or multiple
executes WITH AUTONOMOUS TRANSACTION, the connection will be new and fresh anyway

After first execution of EXECUTE STATEMENT WITH COMMON TRANSACTION the remote
connection is bound with local one and all next EXECUTE STATEMENT WITH
AUTONOMOUS
TRANSACTION will use the same connection, until local transaction end.

So, there is no expectation of session preservation (outside of WITH COMMON TRANSACTION), so we also don't need to worry about
keeping it working.

To be more exact: there is no expectation if session preserved state and
no expectation if session not preserved state.

Therefor: I stand by my original proposal: unconditional session reset. When this session reset yields a syntax error, ignore
(indicates older server version), if another error: invalidate connection.

This is described above as (2) and I going to implement it this way. While I
still consider it as not best possible solution, it looks easyest for end users.
The best could be (4) but I am not sure it could be implemented and tested
enough
in v4 timeframe.

It will behave the same as older versions, and we prevent annoying bugs by inconsistent behaviour caused by retained session state,
or wrong expectations.

It can't behave the same as older versions as older versions have no
connection pool.

For example: it "worked" locally, as only a single user was active locally, so it repeatedly used the same connection from the pool.
When then moved to production (or broader test environment), a solution trying to abuse session state across EXECUTE STATEMENT ON
EXTERNAL suddenly stops working because there no longer is a guarantee that you get the same connection.

I think we should avoid that, and make it clear: no session state across
multiple invocations of EXECUTE STATEMENT.

We can't make such statement. Older engine can't reset session state.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-25 Thread Vlad Khorsun via Firebird-devel

24.05.2018 12:06, Dimitry Sibiryakov wrote:

24.05.2018 10:50, Vlad Khorsun via Firebird-devel wrote:
   From reading documentation I have a feeling that currently external connection is reused only within transaction. When local 
transaction ends, connection is disconnected.

   Yes, almost

   Am I wrong?

   Yes. Above i speak about v4 with connections pool, not about how it works 
"currently".

   Ok. When you are getting a connection from a pool and when return it? I was sure that these point are exactly the same as current 
connect and disconnect of external connection. Was my guess wrong again?

  It is correct.

4. Implement EXTERNAL DATA SOURCE database object and one of its property should
be flag to [not] reset external connection on re-use.

   This is the best solution (as for me) but it might not fit into v4 release
schedule.

   Release is not in sight yet and this object basically is one system table. I 
think it will fit easily.

   Not as easy. It also includes a set of DDL statements to manage this objects,
set of permissions, support in gbak, and, the most complex, good and as much as
possible complete desing.

   Don't be so modest. Implementation of new privilegies and DDL for ALTER SESSION RESET took you just a couple of days. Dmitry for 
sure won't commit mandatory replication so soon.
   About design: as the first approach I think it would be enough to copy all external attachment's attributes into the table's 
fields to let ES syntax to be shortened to simple "ON EXTERNAL ", omitting "AS USER", "PASSWORD" and "ROLE" attributes. 
Then add flags field for flags like "disable pool use", "do not reset state" and so on.

   It will be enough for now and easily expandable later.

  Well, I will return to this particular question later, after finishing other 
tasks.

  As for reset session, i going to implement (2) (as, probably, less evil):
> 2. Always reset external connection when it gets out of use. Do not close 
connection
> if syntax error happens and let it to be re-used as is.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-24 Thread Vlad Khorsun via Firebird-devel

24.05.2018 11:35, Dimitry Sibiryakov wrote:

24.05.2018 1:08, Vlad Khorsun via Firebird-devel wrote:

24.05.2018 0:39, Dimitry Sibiryakov wrote:

What visible changes can happen after upgrade of server version?

From reading documentation I have a feeling that currently external connection is reused only within transaction. When local
transaction ends, connection is disconnected.

Yes, almost

Am I wrong?

Yes. Above i speak about v4 with connections pool, not about how it works
"currently".

4. Implement EXTERNAL DATA SOURCE database object and one of its property should
be flag to [not] reset external connection on re-use.

This is the best solution (as for me) but it might not fit into v4 release
schedule.

Release is not in sight yet and this object basically is one system table. I
think it will fit easily.

Not as easy. It also includes a set of DDL statements to manage this objects,
set of permissions, support in gbak, and, the most complex, good and as much as
possible complete desing.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel

24.05.2018 0:39, Dimitry Sibiryakov wrote:

23.05.2018 21:18, Vlad Khorsun via Firebird-devel wrote:

At second, there is no way to upgrade server without breaking established
connection (obviously).

Nobody tell about it. And it change nothing.

Then could you, please, explain what you have on mind when said "remote
server could be
upgraded at some moment (v3 -> v4) and whole system behaviour will be changed
(automatically and not very expectedly)"?
What visible changes can happen after upgrade of server version?

At third, behavior of pooled connection must exactly match behavior of
non-pooled connection no mater what happen.

So, why we discuss ON RESET trigger(s) ? ;)

I mean that user application must not be able to distinguish them. I.e. using of connection pool with default settings should
have no visible side-effects.

Ideally - yes. But real world is not ideal.

In this discussion we trying to find a way to minimize "surprizes" and allow
system to run in consistent way.

So far we have following propositions:

1. Always reset external connection when it gets out of use. Close connection if
any kind of error happens.

It actually disables connections pool for pre-v4 remote servers. It could be
done by disabling pool in config.

2. Always reset external connection when it gets out of use. Do not close
connection
if syntax error happens and let it to be re-used as is.

It *could* make system work differently and it depends on remote side
version.
If remote statements not changes session-scoped data (such as user contex
variables,
content of GTT ON COMMIT PRESERVE, etc) - such system is not affected and will
have
benefits from connections pooling.
Also, it add some dumb overhead - when remote system is pre-v4 and can't
handle
ALTER SESSION RESET or when remote statements is not changes the session-scoped
data
above. At some use cases it could be bad or very bad.

BTW, we could backport ALTER SESSION RESET into v3 and even into v2.5: it
should
just truncate GTT's and clear user context variables as pre-v4 engines can't
change
other session properties.

3. Never reset external connection when it gets out of use.

It also could make system work differently - when local system was upgraded
from
v3 to v4 and start to use connection pooling *and* if remote statements depends
on
session-scoped data. But in this case user could run 'ALTER SESSION RESET' when
it
is required and get correct behaviour. It requires coding discipline and careful
planning but possible. It have no overhead on session reset when it is not
needed.

4. Implement EXTERNAL DATA SOURCE database object and one of its property should
be flag to [not] reset external connection on re-use.

This is the best solution (as for me) but it might not fit into v4 release
schedule.

What did i missed ?

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel

23.05.2018 15:59, Dimitry Sibiryakov wrote:

23.05.2018 14:40, Mark Rotteveel wrote:
I think it should unconditionally do a session reset on return to the pool if the protocol is v16 or higher (assuming v16 is the
Firebird 4 protocol version).

This is not truly unconditionally :)

But relying on protocol version is also not perfect - remote server could be
upgraded at some moment (v3 -> v4) and whole system behaviour will be changed
(automatically and not very expectedly).

At first, at API level there is no way to find out protocol version and no
easy way to find out remote server version/capacities.

Protocol version could be made known to the engine, no doubt

At second, there is no way to upgrade server without breaking established
connection (obviously).

Nobody tell about it. And it change nothing.

At third, behavior of pooled connection must exactly match behavior of
non-pooled connection no mater what happen.

So, why we discuss ON RESET trigger(s) ? ;)

If gather all this together, IMHO, it is enough to do reset unconditionally and, if it has failed for any reason, to mark EDS as
non-pooled in metadata cache. It will eliminate extra round-trip in cases when pooling is impossible.

There is no such thing as "EDS" in metadata cache. So, no - it will not save
round-trip.

As for overhead of this round-trip in pooled connection, I think that the statement can be sent by pool in background mode after
the connection is returned to it. Of course, during this the connection won't be available for re-use, but for pool it doesn't
matter as it always can have some spare connections at hand.

I already told about it at my previous answer to Mark.

BTW, MSSQL run its sp_reset_connection with first statement of reused
connection.
It set special bit at protocol level to do it:

https://blogs.msdn.microsoft.com/psssql/2014/03/03/sp_reset_connection-rate-usage-dont-fight-over-the-grapes/

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel

23.05.2018 15:40, Mark Rotteveel wrote:

As I said in another mail, I think the RDB$GET/SET_CONTEXT USER_SESSION should
also be cleared.

Read ticket description once more, please, it is clearly stated there:
> - remove all context variables in 'USER_SESSION' namespace

I looked at the commit, but I don't see anything that clears the USER_SESSION.
What did I miss?

void Jrd::Attachment::resetSession(thread_db* tdbb)
{
...
// reset context variables
att_context_vars.clear();

I also noticed that you unconditionally reset the statement idleTimeout and statementTimeout to 0. Does this mean there is no DPB
property to set a connection-wide config for these options? Will setting it to 0 ignore the default config, or will it apply the
default config?

Zero at attachment level means value from the config is effective.

...

Ok, so it is not possible to pre-configure the connection-level configuration
in the DPB?

I still have some questions to agree (or not):

I think it should unconditionally do a session reset on return to the pool if the protocol is v16 or higher (assuming v16 is the
Firebird 4 protocol version).

This is not truly unconditionally :)

But relying on protocol version is also not perfect - remote server could be
upgraded at some moment (v3 -> v4) and whole system behaviour will be changed
(automatically and not very expectedly).

What other option is there? The only alternative would be to define a pool per db + user, and specify whether the connection can be
reset or not.

Additional clause at EXECUTE STATEMENT ON EXTERNAL ?
Not sure i like it

I can't think of alternatives that wouldn't be complex or plain magic compared to "if session reset is supported, perform session
reset" (apart from: "never perform session reset").

I don't think the pool-side should conditionally reset the session, because 1) it becomes complex fast to check if session reset
should apply or not and 2) will disallow independent evolution of what is reset or not, nor would it be possible do additional
reset in the ON RESET trigger proposed below.

I don't offer conditional reset of the session. I offer not to do it at all
and let users to do it if really required. At least in v4. After v4 we could
introduce pools and\or external data sources as database objects and create
corresponding properties at this level.

Hmm, I'm not sure if I like that connections could potentially be in an inconsistent state as left by a different
application/database (or different 'part' of the same database). That can result in transient bugs that depend on specific order of
execution which can be a pain to debug.

If session reset is strongly required, one may issue ALTER SESSION RESET as
first
external statement in current transaction. External connection is bound to the
current
transaction (unless it uses autonomous transactions *only*).

One more question: can ALTER SESSION RESET be executed without an active
transaction?

All session management statements requires no transaction, AFAIU.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel

23.05.2018 13:48, Mark Rotteveel wrote:

On 23-5-2018 11:24, Vlad Khorsun via Firebird-devel wrote:

18.05.2018 19:44, Vlad Khorsun via Firebird-devel wrote:

All,

I going to merge into master implementation of pool of external connections.
The feature was initially developed more than a year ago, and works at
production
with good feedback.

According to discussion in this topic i made following changes:

- New system privilege MODIFY_EXT_CONN_POOL to manage pool properties (instead
of
initiallly used 'ALTER DATABASE' user right).

As I said in another mail, I think the RDB$GET/SET_CONTEXT USER_SESSION should
also be cleared.

Read ticket description once more, please, it is clearly stated there:
> - remove all context variables in 'USER_SESSION' namespace

Zero at attachment level means value from the config is effective.

doc\README.session_idle_timeouts:

- idle session timeout could be set:
- at database level, by setting value in firebird.conf (or databases.conf) by
database
administrator
...
- effective value of idle timeout is evaluated every time user API call leaves
the engine
as:
- if not set at connection level, look at database level

doc\README.statement_timeouts

- timeout value could be set:
- at database level, by setting value in firebird.conf (or databases.conf) by
database
administrator
...
- effective value of timeout is evaluated every time statement starts execution
(or cursor is opened) as:
- if not set at statement level, look at connection level
- if not set at connection level, look at database level

I still have some questions to agree (or not):

I think it should unconditionally do a session reset on return to the pool if the protocol is v16 or higher (assuming v16 is the
Firebird 4 protocol version).

This is not truly unconditionally :)

But relying on protocol version is also not perfect - remote server could be
upgraded at some moment (v3 -> v4) and whole system behaviour will be changed
(automatically and not very expectedly).

You might want to consider if it can be executed asynchronously (eg execute on check-in, read response
on check-out, decide if error response invalidates connection or not?).

At first look it adds a lot of complexity at engine level as currently we
don't
support such processing at remote protocol. I.e. it can't be implemented
directly
as you say above. We could put connections to be reset into another list at the
pool and process this list by another thread... don't know how much it will
affect
release schedule of v4...

- At tracker there was proposition to add new database trigger ON RESET which
should
fire when ALTER SESSION RESET is run. Should we implement it ?

I suggest to call it ON SESSION RESET (or ON SESSION_RESET) instead of ON RESET.

If\when we will agree to implement it - i will not object.

- If i missed something else - please tell me.

Please, read and comment:

https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool

It was updated according to suggestions in this topic.

I see a number of typos, but mayb

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel


23.05.2018 13:40, Adriano dos Santos Fernandes wrote:

On 23/05/2018 06:48, Dimitry Sibiryakov wrote:


   In this case I guess there must be two triggers: BEFORE RESET and
AFTER RESET.


I like this more than the other approaches.


  I strongly suggest to consider existing DISCONNECT\CONNECT triggers
also. I think, most of the code will be the same in both set of events
therefore it is very questionable if we need another pair of triggers.


Also, a RESET may cause problems to an database application, so an
exception in BEFORE RESET should be possible to abort the reset.


  Connection pool will close external connection in this case, correct ?
I.e. any error on session reset (except of syntax error, of course)
will close such a session.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel


23.05.2018 12:58, Dmitry Yemanov wrote:

23.05.2018 12:39, Vlad Khorsun wrote:


   What useful could such trigger do?


I suppose - the most of the thing that users do on CONNECT (init some context variables, for example) and DISCONNECT (free some 
resources).


Wouldn't it make sense to call ON DISCONNECT triggers when the connection is released into the pool and ON CONNECT triggers when the 
connection gets reused from the pool?


  Connection pool and actual connection could be at different processes and\or 
different
hosts. Probably, we could fire DISCONNECT and CONNECT triggers as part of 
execution of
ALTER CONNECTION RESET statement ? Some way to detect real action (RESET or 
[DIS]CONNECT)
will be good to have, in this case.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel


23.05.2018 12:32, Dimitry Sibiryakov wrote:

23.05.2018 11:24, Vlad Khorsun via Firebird-devel wrote:

- At tracker there was proposition to add new database trigger ON RESET which 
should
   fire when ALTER SESSION RESET is run. Should we implement it ?


   What useful could such trigger do?


  I suppose - the most of the thing that users do on CONNECT (init some context
variables, for example) and DISCONNECT (free some resources).

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-23 Thread Vlad Khorsun via Firebird-devel

18.05.2018 19:44, Vlad Khorsun via Firebird-devel wrote:

All,

I going to merge into master implementation of pool of external connections.
The feature was initially developed more than a year ago, and works at
production
with good feedback.

According to discussion in this topic i made following changes:

- New system privilege MODIFY_EXT_CONN_POOL to manage pool properties (instead
of
initiallly used 'ALTER DATABASE' user right).

I still have some questions to agree (or not):

- Should connections pool always use ALTER SESSION RESET when connection become
idle ?
At first looks - yes, it should. It allows to make (almost) no difference for
user
code with non-pooled connections. But from practical POV i have some doubts
and want
to discuss it here:
- pre v4 Firebird versions have no such statement and it will fail (of course
connections pool should handle it correctly)
- it adds at least one additional network roundtrip which could be
unnecessary if
user code doesn't alter session and not uses GTT ON COMMIT PRESERVE on
remote side.
For some applications it could add too much performance penalty for nothing

- At tracker there was proposition to add new database trigger ON RESET which
should
fire when ALTER SESSION RESET is run. Should we implement it ?

- If i missed something else - please tell me.

Please, read and comment:

https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool

It was updated according to suggestions in this topic.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-21 Thread Vlad Khorsun via Firebird-devel

21.05.2018 14:58, Rashid Abzalov wrote:

21.05.2018 14:42, Vlad Khorsun via Firebird-devel wrote:

I don't see how persistent connections in Postgres could be reused by
different user sessions.

No, in Postgres you can reuse only your own connections.

You see - all approaches have its weak and strong points :)

Nevertheless, it seems to me that this approach also solves the original task
that faced the implementation of the connection pool.

Not agree. Connection pool allows to minimize number of simultaneously
open external connections. Also it allows to avoid cost of connect\disconnect
in case of many short-lived user connections actively works with external
data sources. And this was the main goal as users complains on performance.

But you do not need to deal with the side effects of managing this pool by the
system, not the user.
Because reset the state of the session is half the task, and often this will not solve the problem, because sometimes you need to
execute queries in the context of the previous state. That is, on a good need to implement a reset state somewhere out, and then
restore it from there (on request or maybe always).

It makes sence. But currently we have no way to reuse external connection
and there is no existing applications that depends on external connection
state.

Probably we should take into account this PG feature when (if) implement
persistent external data sources.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-21 Thread Vlad Khorsun via Firebird-devel


21.05.2018 14:25, Rashid Abzalov wrote:
Instead of implementing a pool of connections that the system implicitly manages, it might be better to implement named connections 
as it is done in Postgres (https://www.postgresql.org/docs/current/static/contrib-dblink-function.html)? Which the user manages and 
re-uses as he needs - he knows with which tables and context variables he already worked, and brings them to the desired state.


  I don't see how persistent connections in Postgres could be reused by
different user sessions.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-21 Thread Vlad Khorsun via Firebird-devel


21.05.2018 11:47, Dimitry Sibiryakov wrote:

21.05.2018 10:13, Mark Rotteveel wrote:

  if pool contain connection to database 1
and user try to connect to also database 1 he can use pool instead normal 
connection.


That wouldn't really work


   Yes, that wouldn't work. But generally speaking pools in Y-valve could be potentially a little more useful than in engine. For 
Web-applications for example. 


  In general - i agree. But usually Web-applications uses connection pools
provided by frameworks, afaiu.


But this is out of this topic.


  Sure ;)

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel


20.05.2018 19:14, Dimitry Sibiryakov wrote:

20.05.2018 17:29, Vlad Khorsun via Firebird-devel wrote:

   Where do you see it ?


   You are right, it is nowhere in main tree. Sorry, I missed it up with one of 
my branches.
   In any case it is easy to add cache object to ConfigImpl constructor. 
ConfigFile is ready for that.



  I don't think engine is ready to deal with changing on the fly config.
In any case - this topic is not about manage of configuration.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel


20.05.2018 14:11, liviuslivius wrote:

Hi,

can i ask why this is only for external connections? 2 databases. One user run execute statement on database 1 from 2 second on 
database 2 from 1. Third connect simply to database 1 why it can not benefit from pool?


  Explain, please. The statement above could be understood in many different 
ways.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel


20.05.2018 17:53, Dimitry Sibiryakov wrote:

20.05.2018 16:25, Adriano dos Santos Fernandes wrote:

Isn't (some parts of) the config file already reloaded sometimes (timeout)?


   Firebird.conf is reloaded when its timestamp changed. Plugins' configs are 
not reloaded.


  Where do you see it ? I see ConfigCache::checkLoadConfig() only and it is used
with aliasesConf() only. I.e. databases.conf (and its includes) is reloaded when
necessary. What i am missing ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel


20.05.2018 14:15, Mark Rotteveel wrote:

On 20-5-2018 13:00, Vlad Khorsun via Firebird-devel wrote:

   I plan to introduce new database object "EXTERNAL DATA SOURCE" since initial
implementation of EXECUTE STATEMENT ON EXTERNAL, but I never have time\priority
to implement it, unfortunately.

   The question at this topic is: should we add some way to clear session state 
when
connection is known to be reused ? If yes - how it should be seen by a user (SQL
statement, API call, both ?)


I can think of use cases where clearing session state can be useful (eg connections in a application-side connection pool), and I 
think exposing it as SQL should be OK, as that will give more flexibility in use. I would expect this to work with execute immediate 
(but also using prepare/execute).


  Ok, SQL statement, accounted


I would expect such a feature to reset all session state to the initial state 
on connect, including things like

* Clear context USER_SESSION
* Reset role to the initial role specified on attach
* Reset other session configuration (eg DECFLOAT behavior, timeouts, session 
timezone, etc)


  Clear GTT's also.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel


20.05.2018 14:13, Dimitry Sibiryakov пишет:

20.05.2018 13:00, Vlad Khorsun via Firebird-devel wrote:
   I'd suggest to add to EDS an option to use "isolated" connection, which is guaranteed the connection to be new and after use 
to be deleted, not returned to pool.


   Why one should need it ? If one don't want to use pool - just disable it.


   For example, if there is a routine for adding a user to remote database which use SET ROLE RDB$ADMIN for temporary bust of 
privileges. It won't be a good idea to leave such connection in pool for reuse. But such single routine is not a reason to disable 
pool for whole server.


  Very special case. And there is no reason to delete good connection.
Much metter is to re-initialize it automatically (as we discuss currently).


  The question at this topic is: should we add some way to clear session state 
when
connection is known to be reused ? If yes - how it should be seen by a user (SQL
statement, API call, both ?)


   SQL statement (something like ALTER SESSION RESET) is fine for me.


  Accounted

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel

20.05.2018 14:06, Mark Rotteveel wrote:

On 20-5-2018 12:47, Vlad Khorsun via Firebird-devel wrote:

20.05.2018 12:55, Mark Rotteveel wrote:

On 19-5-2018 17:11, Vlad Khorsun via Firebird-devel wrote:

19.05.2018 13:08, Mark Rotteveel wrote:
2. Fine-grained privilege that applies only to this single option: ALTER_EXTERNAL_CONNECTIONS_POOL or if shorter is preferred:
ALTER_EXT_CONN_POOL

3. In addition to option 2, maybe allow even finer-grained control, eg support granting people only the privilege to clear the
pool, but not change the config: privilege CLEAR_EXT_CONN_POOL (or something like that).

It looks as not necessary as pool could be cleared by setting its size to
zero.

You are missing my point: this privilege is about giving a user/role the right to clear the pool from its current connections,
while at the same time not allowing those users to change the configuration like pool size, lifetime, etc.

I don't miss it. I just say that privilege to clear the pool looks redundant
as
setting pool size to zero have same effect. If you insist - i can live with it.
But
i consider as not good to make new system privilege for every single task.

There is a difference between just clearing the pool, and setting the size to zero. Clearing it will just remove the current
connections (and allowing it to be repopulated), while setting the size to zero disables the pool. One has a transient effect and is
therefor less disruptive, the other will be persistent and can have significant and continuous impact on the performance
characteristics of the database server.

I understand the difference. But look from security POV: user with
MODIFY_EXT_CONN_POOL
privilege could set pool size to zero and reset it to the previous value again
and he not
need CLEAR_EXT_CONN_POOL privilege do clear the pool. I think we should avoid
such cases.

That is why I proposed an additional (more restricted) privilege. On the other hand, this may be something that we can defer until
the pool has seen some use and we have established a clear need for this.

Yes, lets defer it for a while. Introduce new system privilege is easy task
and it could
be done before final release, if necessary.

We may need to consider doing all three.

So far i see only (2) as necessary change.

Existing set of system privleges have no ALTER_XXX, so i think we should use
MODIFY_EXT_CONN_POOL here.

Is fine with me :)

Very good. I'll commit is soon.

Next step could be to implement external pool as
database object and allow user to CREATE\ALTER\DROP multiply pools.

At the moment, I don't really see the use case of being able to define multiple pools (and how you would then use them). How do
you envision this feature works?

Pool will be choosed based on external database name. It could be full match
or regexp pattern. In latter case we could introduce explicit ordering used to
search what pool to use to avoid ambiguity. The main goal is to fine tune number
and lifetime of pooled connections for differefent external databases.

Ok, I understand, that could be useful.

Good. Put it into the future plans

Other questions I have:
1. What happens if the pool configuration is done through DDL?

Its runtime values are applied immediately. It is described in
README.external_connections_pool.

Will it persist in the firebird.conf (or engine13.conf)?

No.

Will a restart of Firebird clear it again?

Yes.

Hmm, this could make for odd performance changes between restarts. This will need to be clearly documented. I would expect the
config changes done this way to be permanent.

Agree, it should be documented. Will do.

Playing devil's advocate, if this configuration change isn't persistent, why expose it as DDL at all? Especially given the
difference exposed below.

2. How will the pool work in case of Classic Server? Will pool config changes
apply to all processes?

Current process only. Since we have pool per process, not per whole
system\instance.

Cause i needed a some way to manage pool at runtime, without server restart,
and i saw no better\easy way to do it. If you offer some - it will be considered
of course.

Pool management statements (even without persistence) allows DBA to play with
pool properties to find an optimal values and then put in into config - this was
initial idea.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel

20.05.2018 12:38, Dimitry Sibiryakov wrote:

19.05.2018 18:44, Vlad Khorsun via Firebird-devel wrote:

Nothing special was done in this area - GTT contents will be preserved.
The same for context variables.

So, we have a question - should we clear session-level state when connection
become unused (or when it about to be re-used) ?

I'd suggest to add to EDS an option to use "isolated" connection, which is guaranteed the connection to be new and after use to
be deleted, not returned to pool.

Why one should need it ? If one don't want to use pool - just disable it.

Or, better yet, create a table of "dblinks" which can be used as EDS and add this option as a flag to the table. With this table
database developer/administrator can decide when using or not using of pool. It may have other usages as well: simplify ES ON EDS
syntax, allow to preset (and hide) connection credential, limit usage of EDS to chosen users, etc.

I plan to introduce new database object "EXTERNAL DATA SOURCE" since initial
implementation of EXECUTE STATEMENT ON EXTERNAL, but I never have time\priority
to implement it, unfortunately.

The question at this topic is: should we add some way to clear session state
when
connection is known to be reused ? If yes - how it should be seen by a user (SQL
statement, API call, both ?)

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-20 Thread Vlad Khorsun via Firebird-devel

20.05.2018 12:55, Mark Rotteveel wrote:

On 19-5-2018 17:11, Vlad Khorsun via Firebird-devel wrote:

19.05.2018 13:08, Mark Rotteveel wrote:
2. Fine-grained privilege that applies only to this single option: ALTER_EXTERNAL_CONNECTIONS_POOL or if shorter is preferred:
ALTER_EXT_CONN_POOL

It looks as not necessary as pool could be cleared by setting its size to
zero.

You are missing my point: this privilege is about giving a user/role the right to clear the pool from its current connections, while
at the same time not allowing those users to change the configuration like pool size, lifetime, etc.

We may need to consider doing all three.

So far i see only (2) as necessary change.

Existing set of system privleges have no ALTER_XXX, so i think we should use
MODIFY_EXT_CONN_POOL here.

Having a monitoring table for these would be good.

Accounted, thanks.

Next step could be to implement external pool as
database object and allow user to CREATE\ALTER\DROP multiply pools.

At the moment, I don't really see the use case of being able to define multiple pools (and how you would then use them). How do you
envision this feature works?

Maybe this is something that needs to be deferred until this feature has seen
some use?

Very probably. I want to show how i see direction of future development in
this area.

Another little
improvement could be new property of pooled connection - recycle time. It means
that
even if connection is actively used it should be closed after "recycle time" (of
course when it become inactive).

What do you think ?

Limiting the lifetime of a connection in the pool. Yes, that is a good idea. In the Java world, this property is usually called
something like maximum connection lifetime.

I already implement idle connection lifetime but it could be useful to make
hard limit
even for connections that used actively.

Another thought, I think this may be something that needs to be logged in firebird.log (eg changed external
connection pool lifetime from to ; cleared old|all connections, etc)

Another setting to enable\disable such logging ? Please, no ;) It would be
much
better to discuss (separately, yes) how logging could be structured and moved to
the public interface\new plugin, IMHO.

I said nothing about having settings for enabling or disabling said logging, I think changes to the configuration need to be logged,
so they can be audited.

Please, let leave theme of how to manage configuration changes for another
discussion,
else we never finish this one. For audit we have another feature. So we could
add
corresponding events in the Trace interface.

Other questions I have:
1. What happens if the pool configuration is done through DDL?

Its runtime values are applied immediately. It is described in
README.external_connections_pool.

Will it persist in the firebird.conf (or engine13.conf)?

No.

Will a restart of Firebird clear it again?

Yes.

2. How will the pool work in case of Classic Server? Will pool config changes
apply to all processes?

Current process only. Since we have pool per process, not per whole
system\instance.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-19 Thread Vlad Khorsun via Firebird-devel


19.05.2018 19:38, Dimitry Sibiryakov wrote:


   BTW, what behavior is expected from GTT ON COMMIT PRESERVE in pooled 
connection?


  Nothing special was done in this area - GTT contents will be preserved.
The same for context variables.

  So, we have a question - should we clear session-level state when connection
become unused (or when it about to be re-used) ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-19 Thread Vlad Khorsun via Firebird-devel


19.05.2018 18:52, Dimitry Sibiryakov wrote:

>>Also, i want to speak about possible extension of the feature. I think it 
would
>> be good to have new monitoring table with list of all external connections. 
Not
>> sure if we should allow to DELETE here but it should be at least considered 
too.
>> It will replace two of four new context variables (EXT_CONN_POOL_IDLE_COUNT 
and
>> EXT_CONN_POOL_ACTIVE_COUNT). Next step could be to implement external pool as
>> database object and allow user to CREATE\ALTER\DROP multiply pools. Another 
little
>> improvement could be new property of pooled connection - recycle time. It 
means that
>> even if connection is actively used it should be closed after "recycle time" 
(of
>> course when it become inactive).
>>
>>What do you think ?
>
   Say, there are two databases using different security databases/plugins. In one database user John used an external connection 
and leaved it idle. In other database user John use external connection and see that the connection is moved from idle pool to 
active pool. Now second John know that his password is the same with first John.


  We have single engine instance (local) which handle connections to two 
databases each
of them have own security database. Both security databases contains user John. 
Both
John's established own connection to the different databases and want to make 
external
query to the same (3rd) database. Correct ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-19 Thread Vlad Khorsun via Firebird-devel

19.05.2018 13:08, Mark Rotteveel wrote:

If it is global, then the permission for ALTER EXTERNAL CONNECTIONS POOL needs
to be changed:

"""
New SQL statement is introduced to manage the pool :

ALTER EXTERNAL CONNECTIONS POOL.

When prepared it desribed as DDL statement but have immediate effect: i.e.
it is executed immediately and completely, not waiting for transaction commit.

"ALTER DATABASE" permission is required to run the statement.
"""

The ALTER DATABASE privilege is per database. I don't think that a per-database privilege should be allowed to control
engine-wide configuration options. If the pool (and config) is global, I think this requires a global privilege, preferably a
separate one. If the pool **and** config is local, then it is ok.

Good point, thanks. Since the pool is global it should be guarded by global
privilege.
We could use existing MODIFY_ANY_OBJECT_IN_DATABASE or introduce a new one. For
example
MODIFY_EXTERNAL_CONNECTIONS_POOL, or ALTER_EXTERNAL_CONNECTIONS_POOL, or
something not
so long. Do you have better idea ?

We can consider a number of approaches:

1. Coarse-grained privilege that also allows changing other engine config
options exposed in DDL: ALTER_ENGINE_CONFIG.

I think this is currently the only statement that allows for changing the engine config through DDL, but this would future-proof it.
On the other hand, this could become such a wide privilege that maybe that user should simply have the RDB$ADMIN role instead.

It could be too wide, agree. Also, i don't want to mix engine config theme
here.
It could be discussed separately with all required details. And applied to the
all
involved subsystems, not only external connections pool.

2. Fine-grained privilege that applies only to this single option: ALTER_EXTERNAL_CONNECTIONS_POOL or if shorter is preferred:
ALTER_EXT_CONN_POOL

3. In addition to option 2, maybe allow even finer-grained control, eg support granting people only the privilege to clear the pool,
but not change the config: privilege CLEAR_EXT_CONN_POOL (or something like that).

It looks as not necessary as pool could be cleared by setting its size to
zero.

We may need to consider doing all three.

So far i see only (2) as necessary change.

Also, i want to speak about possible extension of the feature. I think it
would
be good to have new monitoring table with list of all external connections. Not
sure if we should allow to DELETE here but it should be at least considered too.
It will replace two of four new context variables (EXT_CONN_POOL_IDLE_COUNT and
EXT_CONN_POOL_ACTIVE_COUNT). Next step could be to implement external pool as
database object and allow user to CREATE\ALTER\DROP multiply pools. Another
little
improvement could be new property of pooled connection - recycle time. It means
that
even if connection is actively used it should be closed after "recycle time" (of
course when it become inactive).

What do you think ?

Another thought, I think this may be something that needs to be logged in firebird.log (eg changed external
connection pool lifetime from to ; cleared old|all connections, etc)

Another setting to enable\disable such logging ? Please, no ;) It would be
much
better to discuss (separately, yes) how logging could be structured and moved to
the public interface\new plugin, IMHO.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-19 Thread Vlad Khorsun via Firebird-devel

19.05.2018 11:04, Mark Rotteveel wrote:

On 18-5-2018 18:44, Vlad Khorsun via Firebird-devel wrote:

...

Please, read and comment:

https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool

1. I'm not sure how I should read the "Key characteristic" that says: "the pool is common for all local connections handled by the
given Firebird process"

It means that all local (user) connections requests for external connection
are handled by the same single pool instance.

2. (maybe related to 1) What is not clear to me is whether this pool is global
(for the whole engine), or per database.

The whole engine.

If it is global, then the permission for ALTER EXTERNAL CONNECTIONS POOL needs
to be changed:

"""
New SQL statement is introduced to manage the pool :

ALTER EXTERNAL CONNECTIONS POOL.

When prepared it desribed as DDL statement but have immediate effect: i.e.
it is executed immediately and completely, not waiting for transaction commit.

"ALTER DATABASE" permission is required to run the statement.
"""

The ALTER DATABASE privilege is per database. I don't think that a per-database privilege should be allowed to control engine-wide
configuration options. If the pool (and config) is global, I think this requires a global privilege, preferably a separate one. If
the pool **and** config is local, then it is ok.

Regards,
Vlad

Re: [Firebird-devel] RFC: External Connections Pool

2018-05-18 Thread Vlad Khorsun via Firebird-devel


18.05.2018 22:15, Dimitry Sibiryakov wrote:

18.05.2018 20:49, Vlad Khorsun via Firebird-devel wrote:

   Whole EDS feature is implemented as part of engine.


   Shouldn't new configuration parameters to be in the right place for plugin's parameters: engine13.conf instead of legacy pile 
firebird.conf?..


  So far there is no such decision (to consider engine13.conf as a "right 
place" and
that firebird.conf is legacy). And I see no reason to discuss it in this thread.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] RFC: External Connections Pool

2018-05-18 Thread Vlad Khorsun via Firebird-devel


  All,

  I going to merge into master implementation of pool of external connections.
The feature was initially developed more than a year ago, and works at 
production
with good feedback.

  Some bugs was fixed since then, so it should be stable enough and definitely
good for beta release of v4. The branch ExternalConnectionsPool was created at
our repository 3 months ago, btw ;)

  Please, read and comment:

https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Reason for "Missing master config file firebird.conf"

2018-05-18 Thread Vlad Khorsun via Firebird-devel


18.05.2018 11:00, Jiří Činčura wrote:

Hi *,

what could be the reason for "Missing master config file firebird.conf" mesage when doing 
".\firebird.exe -a"? I'm traying to start Firebird on AAppVeyor for tests for 
NETProvider. When I execute it manually it starts fine. Only when from script from AppVeyor then I 
get this messsage. Any hints to check?

It's a vanilla Firebird 3, the CWD is the location where firebird.exe is 
located.


  Firebird root folder (where firebird.conf is expected to be) is detected
as follows:
- value of FIREBIRD environment variable, if present
- path of the fbclient.dll (which is loaded by the firebird.exe)

  So, check FIREBIRD environment variable and ensure you have no
other fbclient.dll at PATH (it could be different for AppVeyor
script executor and when you start Firebird manually)

Hope it helps,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-14 Thread Vlad Khorsun via Firebird-devel


10.05.2018 19:23, Adriano dos Santos Fernandes wrote:

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:


   I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone
rule
to the new data.

   Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


I really don't understood what you mean.

Please give more practical example.


  Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID  NameValid from  Offset
... MSK 2011.03.27 02:00+4
... MSK 2014.10.26 02:00+3

Is it makes sence ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Time zone feature documentation

2018-05-11 Thread Vlad Khorsun via Firebird-devel

12.05.2018 1:12, Lester Caine wrote:

On 11/05/18 20:37, Adriano dos Santos Fernandes wrote:

* PST is Pitcairn or Pacific, with the same offset, they are
considered different time zones

ICU data is derived from TZ data hosted by IANA
(http://site.icu-project.org/tznotice).

I really can't answer questions about specific time zones, but really
grateful if we can clarify them.

The abbreviations are not defined to be unique. Only a subset of them can be used as identifiers, the rest are only 'defined' when
associated with a particular rule, so are only suitable for displaying.

Personally I need to see the different UK/Ireland timezone idents giving the right offsets prior to 1970 and that depends on how the
ICU extract has been processed. It's not a case that 'the ICU data is derived from TZ data', it also depends on how the TZ data is
processed by ICU and I'm not seeing ANY information on that. For the Irish area we then get IST standing for Irish Summer Time prior
to 1968, and Irish Standard Time after that. Nothing is easy on any of this ... and IST is used in a number of other rules with
similar differences in expansion. A quick scan of the ICU files would suggest 'IST' is used by them for India Standard Time but it
would be nice if there was an easy way to confirm that? Certainly I'm not seeing data in zoneinfo64.txt for Isle of man, Jersey,
Guernsey or Belfast so I suspect they are not valid pre-1970 :(

Probably, this answers on (some of) your questions

http://userguide.icu-project.org/datetime
http://userguide.icu-project.org/datetime/calendar
http://userguide.icu-project.org/datetime/timezone

Regards,
Vlad

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


11.05.2018 1:27, Adriano dos Santos Fernandes wrote:

On 10/05/2018 18:03, Vlad Khorsun via Firebird-devel wrote:

...

Could you answer my question below ?

   Please, convert to UTC and explain how to do it:

    2010.03.01 04:00 Europe/Moscow,
    2013.03.01 04:00 Europe/Moscow,
    2016.03.01 04:00 Europe/Moscow



-
SQL> select
CON> timestamp '2010-03-01 04:00 Europe/Moscow' t1,
CON> timestamp '2013-03-01 04:00 Europe/Moscow' t2,
CON> timestamp '2016-03-01 04:00 Europe/Moscow' t3
CON>   from rdb$database;

T1  2010-03-01 04:00:00. Europe/Moscow
T2  2013-03-01 04:00:00. Europe/Moscow
T3  2016-03-01 04:00:00. Europe/Moscow


SQL> select
CON> timestamp '2010-03-01 04:00 Europe/Moscow' at time zone 'GMT' x1,
CON> timestamp '2013-03-01 04:00 Europe/Moscow' at time zone 'GMT' x2,
CON> timestamp '2016-03-01 04:00 Europe/Moscow' at time zone 'GMT' x3
CON>   from rdb$database;

X1  2010-03-01 01:00:00. GMT
X2  2013-03-01 00:00:00. GMT
X3  2016-03-01 01:00:00. GMT


SQL> select
CON> extract(timezone_hour from timestamp '2010-03-01 04:00
Europe/Moscow') tzh1,
CON> extract(timezone_hour from timestamp '2013-03-01 04:00
Europe/Moscow') tzh2,
CON> extract(timezone_hour from timestamp '2016-03-01 04:00
Europe/Moscow') tzh3
CON>   from rdb$database;

TZH13
TZH24
TZH33
-


  Very good, thanks !


Here I show interesting example when DST starts.

Two different GMT dates apparently maps to the same America/Sao_Paulo.

But if you inspect they timezone displacements, they are different.

-
SQL> select
CON> timestamp '2018-02-18 01:00 GMT' at time zone
'America/Sao_Paulo' t1,
CON> timestamp '2018-02-18 02:00 GMT' at time zone
'America/Sao_Paulo' t2
CON>   from rdb$database;

T1  2018-02-17 23:00:00. America/Sao_Paulo
T2  2018-02-17 23:00:00. America/Sao_Paulo


SQL> select
CON> extract(timezone_hour from timestamp '2018-02-18 01:00 GMT' at
time zone 'America/Sao_Paulo') tzh1,
CON> extract(timezone_hour from timestamp '2018-02-18 02:00 GMT' at
time zone 'America/Sao_Paulo') tzh2
CON>   from rdb$database;

TZH1-2
TZH2-3
-



Regards,
Vlad

PS It seems i start to understand you - you want to say that rule for
given area (full TZ name ?) describes all possible moments when offset
from UTC is changed. Including DST start\end and changes due to political
reasons. Correct ?



Yes, let's say that rule is defined from UTC to the time zone, and it
say, between UTC moment x and y, tz has a displacement of +/- hh:mm.

Every range (x-y) must define a displacement.


  Good, now it is much more clear (for me, at least).
It there a way to query\extract this ranges and coresponding displacements ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 22:08, Adriano dos Santos Fernandes wrote:

On 10/05/2018 15:30, Vlad Khorsun via Firebird-devel wrote:


   The key is that DST doesn't affect given time zone. TZ could be
"standard time" based (such as MSK)
or "DST based" (such as MSD or IST you speak about) but TZ itself is
not changed with DST start\end.
What is changed - is what TZ should be used. But we (I) speak not
about it.


That only in this scheme you linked, that is un-mappable to computers.

This scheme also has invalid date/tz.

To convert a timestamp to UTC I need to known the timestamp and its
timezone.

So, I can't convert 2018-05-01 00:00 BRST (Brazilian Summer Time) to
UTC, as there is no summer time in 2018-05-01, only standard time.

And 2018-01-01 00:00 BRST + 4 months will not change to BRT (Brazilian
Standard Time).

This scheme just does not work!

On the other hand, I can convert any timestamp in America/Sao_Paulo to UTC.


  To do it, you need to know TZ (or, offset from UTC) used at America/Sao_Paulo
at given moment, isn't is ? And this offset is also depends on given moment
itself. And in different moments there could be different offsets.
Correct ?


I can add any interval to any America/Sao_Paulo timestamp and it will be
already an America/Sao_Paulo timestamp.

In fact the arithmetic is very simple, just add the interval to the
stored UTC timestamp.

When converting back to a string representation, it will be showed in
America/Sao_Paulo time.


  It is very good (while have no answer on "versioned" time zones question).
Could you answer my question below ?

  Please, convert to UTC and explain how to do it:

   2010.03.01 04:00 Europe/Moscow,
   2013.03.01 04:00 Europe/Moscow,
   2016.03.01 04:00 Europe/Moscow

Regards,
Vlad

PS It seems i start to understand you - you want to say that rule for
given area (full TZ name ?) describes all possible moments when offset
from UTC is changed. Including DST start\end and changes due to political
reasons. Correct ?

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 21:21, Adriano dos Santos Fernandes wrote:


The name for Moskow is Europe/Moscow.


  Please, convert to UTC and explain how to do it:

   2010.03.01 04:00 Europe/Moscow,
   2013.03.01 04:00 Europe/Moscow,
   2016.03.01 04:00 Europe/Moscow

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 21:13, Lester Caine wrote:

On 10/05/18 18:47, Vlad Khorsun via Firebird-devel wrote:
Regions that use Daylight Saving Time (DST) change the time zone name and time during the DST period. The words “daylight” or 
“summer” are then usually included in the time zone name. The areas that don't use DST remain on standard time zone all year.


This view of things is somewhat out of date. There has been a discussion recently on the TZ list over 'Ireland' which uses 'standard 
time' in the summer, and changes from that in the winter ... hence a -ve DST offset rather than positive. Also DST in the southern 
hemisphere is reversed and Australian abbreviations don't follow the same guide. Some tools could not handle the -ve DST so it's 
implementation has been delayed, but the plan is that the correct data will be provided by TZ at least in future.




  The key is that DST doesn't affect given time zone. TZ could be "standard 
time" based (such as MSK)
or "DST based" (such as MSD or IST you speak about) but TZ itself is not 
changed with DST start\end.
What is changed - is what TZ should be used. But we (I) speak not about it.

Regads,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 20:29, Adriano dos Santos Fernandes пишет:

On 10/05/2018 14:23, Vlad Khorsun via Firebird-devel wrote:



    Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID    Name    Valid from    Offset
...    MSK    2011.03.27 02:00    +4
...    MSK    2014.10.26 02:00    +3

Is it makes sence ?


America/Sao_Paulo changes every year because DST (when starting to -02
and ending to -03).


   The case is not about DST. It is about *change of rule* for particular
time zone. Does you read table at link above ?

If i understand it correct:

   2010.03.01 04:00 MSK == 2010.03.01 00:00 UTC,
   2013.03.01 04:00 MSK == 2013.03.01 00:00 UTC, but
   2016.03.01 04:00 MSK == 2016.03.01 01:00 UTC


In practical terms, it's the same as a DST change.


  According to:

https://www.timeanddate.com/time/time-zones.html

---
Daylight Saving Time Zones

Regions that use Daylight Saving Time (DST) change the time zone name and time during the DST period. The words “daylight” or 
“summer” are then usually included in the time zone name. The areas that don't use DST remain on standard time zone all year.


For example, California uses Pacific Daylight Time (PDT) during the DST period, but Pacific Standard Time (PST) during the rest of 
the year.

---

So, in Moscow:
2010.03.01 used MSK (UTC +3)
2010.03.28 DST start (MSK -> MSD)
2010.04.01 used MSD (UTC +4)
2010.10.31 DST end (MSD -> MSK)
2011.03.01 used MSK (UTC +3)
2011.03.27 used MSK (UTC +4)  <<<--- rule for MSK was changed !!!
... don't use DST, don't use MSD
2014.10.26 used MSK (UTC +3)  <<<--- rule for MSK was changed again !!!
... don't use DST, don't use MSD



A DST is nothing different than a "rule change" and another "rule
change" every year.


  Nope. DST doesn't affect time zone. It affects what time zone is actual
at given region. Before 2011 it was MSK "at winter" and MSD "at summer" at
Moscow area.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 19:57, Adriano dos Santos Fernandes пишет:

On 10/05/2018 13:47, Vlad Khorsun via Firebird-devel wrote:

10.05.2018 19:23, Adriano dos Santos Fernandes wrote:

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:


    I guess the question is about the case when users have historical
data
and need to apply old time zone rule to the old data and new timezone
rule
to the new data.

    Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


I really don't understood what you mean.

Please give more practical example.


   Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID    Name    Valid from    Offset
...    MSK    2011.03.27 02:00    +4
...    MSK    2014.10.26 02:00    +3

Is it makes sence ?


America/Sao_Paulo changes every year because DST (when starting to -02
and ending to -03).


  The case is not about DST. It is about *change of rule* for particular
time zone. Does you read table at link above ?

If i understand it correct:

  2010.03.01 04:00 MSK == 2010.03.01 00:00 UTC,
  2013.03.01 04:00 MSK == 2013.03.01 00:00 UTC, but
  2016.03.01 04:00 MSK == 2016.03.01 01:00 UTC


Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 19:23, Adriano dos Santos Fernandes wrote:

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:


   I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone
rule
to the new data.

   Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


I really don't understood what you mean.

Please give more practical example.


  Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID  NameValid from  Offset
... MSK 2011.03.27 02:00+4
... MSK 2014.10.26 02:00+3

Is it makes sence ?

Regards,
Vlad


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 19:02, Adriano dos Santos Fernandes wrote:

On 10/05/2018 12:53, Lester Caine wrote:

...

And how will the version of TZ data be handled! This is a key element
that has screwed normalized data in the past, and some way to manage
that normalized data needs to be included in the process.



If you mean "normalize data" as the fact that people uses an outdated
time zone db, insert data in the database on the outdated time zone, and
later update the db, the way to normalize data is up to the user/admin.


  I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone rule
to the new data.

  Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

2018-05-10 Thread Vlad Khorsun via Firebird-devel


10.05.2018 19:02, Adriano dos Santos Fernandes wrote:

On 10/05/2018 12:53, Lester Caine wrote:

...

And how will the version of TZ data be handled! This is a key element
that has screwed normalized data in the past, and some way to manage
that normalized data needs to be included in the process.



If you mean "normalize data" as the fact that people uses an outdated
time zone db, insert data in the database on the outdated time zone, and
later update the db, the way to normalize data is up to the user/admin.


  I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone rule
to the new data.

  Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Time zones

2018-05-02 Thread Vlad Khorsun via Firebird-devel


30.04.2018 5:50, Adriano dos Santos Fernandes wrote:

Hi!

Time zones branch is almost feature complete.

There is two important subjects left:

- ICU in Windows - as I already said, it need to be upgraded and I would
like it builded as the upstream does, i.e., with full data. It would be
good if someone volunter to do it as I do not have nor even known what
compiler version should be used.


  "Official" compiler for fb3 on Windows is (old) VC10. Snapshot builds of
both fb3 and fb4 uses VC12, iirc. So far there is no decision what we will
use as "official" compiler for fb4.


- Compatibility:

It was been created four new datatypes:
- TIME WITH TIME ZONE
- TIMESTAMP WITH TIME ZONE
- TIME WITHOUT TIME ZONE
- TIMESTAMP WITHOUT TIME ZONE

The old types (TIME and TIMESTAMP) resolves to the "WITHOUT" version.

So the first compatibility problem is when client selects the types WITH
TIME ZONE.


  I suppose you speak about old clients here, which have no support for
new datatypes, correct ?


It can be resolved with same solution as DECFLOAT (SET DECFLOAT BIND ...):
- SET TIME ZONE BIND { NATIVE | LEGACY } (better suggestion welcome)


  What should be used by default ? How conversion "TIME WITH TIME ZONE"
to "TIME" will happen - will it just ignore time zone information, or
will it change value of time (accouting time zone) returned to the client ?


Also there are new expressions, LOCALTIME and LOCALTIMESTAMP with
functionality equivalent to the Firebird's CURRENT_TIME and
CURRENT_TIMESTAMP.

But standard CURRENT_TIME and CURRENT_TIMESTAMP  works different than
Firebird, as they returns the "WITH TIME ZONE" types.

Tweaking CURRENT_TIME/CURRENT_TIMESTAMP for compatibility is problematic.


  How it is different from problem above ?


It may be in stored routines or in ad-hoc queries. Stored routines are
sometimes "recompiled" by tools, i.e., recreated from sources.


  What if store new datatype at BLR level but return datatype according to
the SET TIME ZONE BIND rule ?


If there is attachment option that changes the behavior of DDL commands,
users will soon have problems when sometime it will be used and sometime
it will not.


  Agree, DPB is not good for such things.


Also database config option for this will create problems as each client
(submitting queries) will work different.


  Explain, please. It seems for me that SET TIME ZONE BIND statement with
default values set at database level (via config) could resolve all issues.
Am i wrong ?


So what I propose here is a migration path that does not breaks the new
feature nor the old code if users implement the migration correct.

I propose to add LOCALTIME/LOCALTIMESTAMP to next Firebird 3 version as
synonym for its CURRENT_TIME/CURRENT_TIMESTAMP.

So before migrate to v4, users can adjust their code using Firebird 3
and once upgraded there will be no problems.


  I'd add it into 2.5.9 also, as many users will upgrade from per-v3 versions.

  BTW, we could (should ?) add more such forward-compatibility features into
last release of 2.5

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Android/iOS Embedded with Xamarin Forms

2018-04-25 Thread Vlad Khorsun via Firebird-devel


Jorge,

  Avoid over quoting, please

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables

2018-04-10 Thread Vlad Khorsun via Firebird-devel


10.04.2018 21:51, Rashid Abzalov wrote:

The implemented feature allows changing user indexes to any users.


  It allows to change user indices by the OWNERS and users with ALTER TABLE
privilege granted, see below:

fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB
Database: s:\Temp\A.30.FDB, User: SYSDBA
SQL> show users;
Users in the database
  1 #SYSDBA  0  VLAD
SQL>
SQL> create index idx1 on rdb$dependencies(RDB$DEPENDENT_NAME);
SQL> exit;

fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB -user vlad
Database: s:\Temp\A.30.FDB, User: VLAD
SQL> create index idx1 on rdb$dependencies(RDB$DEPENDENT_NAME);
Statement failed, SQLSTATE = 28000
unsuccessful metadata update
-CREATE INDEX IDX1 failed
-no permission for ALTER access to TABLE RDB$DEPENDENCIES
SQL> drop index idx1;
Statement failed, SQLSTATE = 28000
unsuccessful metadata update
-DROP INDEX IDX1 failed
-no permission for ALTER access to TABLE RDB$DEPENDENCIES
SQL> exit;

fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB
Database: s:\Temp\A.30.FDB, User: SYSDBA
SQL> grant alter any table to user vlad;
SQL> exit;

F:\FB2\fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB -user vlad
Database: s:\Temp\A.30.FDB, User: VLAD
SQL> create index idx1 on rdb$dependencies(RDB$DEPENDENT_NAME);
Statement failed, SQLSTATE = 42S11
unsuccessful metadata update
-CREATE INDEX IDX1 failed
-Index IDX1 already exists
SQL> drop index idx1;
SQL> exit;


Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Getting location of fbclient.dll via IExternalContext

2018-03-26 Thread Vlad Khorsun via Firebird-devel


26.03.2018 18:49, Jiří Činčura wrote:

Hmm, found `context->getMaster()->getConfigManager()->getRootDirectory()`.  Is 
there a better/shorter way?


  This is the best way, especially if there could be more than one fbclient.dll
loaded by host process.


And adding "fbclient.dll" is then trivial. So it works for me.


  Use it. This is the only correct way.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] some issues with sed on macosx firebird master

2018-03-22 Thread Vlad Khorsun via Firebird-devel


22.03.2018 13:21, marius adrian popa wrote:

sed part worked :)

now another one

https://travis-ci.org/mariuz/firebird/jobs/356795126

/Users/travis/build/mariuz/firebird/src/remote/../remote/SockAddr.h:101:2: 
error: Unknown value of AF_INET6 !
#error Unknown value of AF_INET6 !


  Should be fixed now

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ALTER COLUMN and index rebuild

2018-03-19 Thread Vlad Khorsun via Firebird-devel


And, probably, we should add:
(3) - NOT NULL is set and default value was set before


Really, why?

I don't see that as a change that would affect the existing index values.


The question was about existing NULL values. After i tried it i see that NOT
NULL will not be set in this case:

SQL> alter table a alter id set not null;
Statement failed, SQLSTATE = 22006
unsuccessful metadata update
-Cannot make field ID of table A NOT NULL because there are NULLs present


So, not an issue.  Right?


  Sure

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ALTER COLUMN and index rebuild

2018-03-19 Thread Vlad Khorsun via Firebird-devel


19.03.2018 11:55, Leyne, Sean wrote:

Vlad,


Looking at bug CORE-5774 i found very odd behavior of engine: it seems it
rebuilds indices in many unneeded cases, when almost any property of the
indexed field is changed.

I understand that index should be rebuild in this cases only:
(1) - field data type is changed *and* its "index" type changed too
(2) - default value is set at nullable field *and* there was no default value
before
- "computed by" expression is changed


Would criteria (1) and (2) not also need apply to any field which is referenced by a 
"computed by" index, and cause that index to need to be rebuilt?


  Seems, yes. Also, i was a bit wrong at (2) - it should be read as
(2) - default value is set at *non-nullable* field *and* there was no default 
value

And, probably, we should add:
(3) - NOT NULL is set and default value was set before

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] ALTER COLUMN and index rebuild

2018-03-16 Thread Vlad Khorsun via Firebird-devel



  Looking at bug CORE-5774 i found very odd behavior of engine: it seems it 
rebuilds
indices in many unneeded cases, when almost any property of the indexed field 
is changed.

  I understand that index should be rebuild in this cases only:
- field data type is changed *and* its "index" type changed too
- default value is set at nullable field *and* there was no default value before
- "computed by" expression is changed

and indices should not be rebuild when:
- field data type is changed but its "index" type is still the same
- default value is changed (not added)
- default value is added or changed at non-nullable field
- default value is dropped
- field's identity value is restarted
- field name is changed
- field position is changed

Am i missing something ? Am i wrong ?

Thanks,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables

2018-02-27 Thread Vlad Khorsun via Firebird-devel

20.02.2018 14:23, Rashid Abzalov wrote:

The background of this task in CORE-5612, it contains research details and
conclusions.

How much I understood, there is no way to specify what index should be used at
system inquiries from *.epp files?

I have the same understanding

If not, is it possible to enable the create/delete, enable/disable indexes on
system tables, including standard system indexes?
I would try to implement this if the approach is approved.

I have no objection to allow to create\alter\drop\set_stats for user index on
system tables. Note, such indices will not be put into gbak backup and should be
re-created after restore. Of course, it also could be improved, if necessary.

In Firebird 2.5, we get around the problem described in CORE-5612 by creating 2 indexes and turning off the indices RDB$INDEX_27 and
RDB$INDEX_28 at the right time (then turn it back on).

But we can not act in the same way in Firebird 3.0 because these actions are only allowed by GBACK (attachment->isGbak) and only
during the creation of the database (attachment->att_flags & ATT_creator).

Those if you do not want to allow such actions publicly, you could be allowed to do it from external utilities similar to GBACK
(dpb.insertString(isc_dpb_gbak_attach, FB_VERSION, fb_strlen(FB_VERSION))), but then need to remove the check for the creator of
database (attachment->att_flags & ATT_creator).

I could be wrong, but: to make it properly, one should look as
SCL_check_relation(..., SCL_control)
calls at VIO_erase\VIO_modify\VIO_store near the "case rel_indices" and pass
"false" into last parameter
(protectSys) for user indices. The same should be done at checkPermission()
method of CreateIndexNode,
AlterIndexNode, DropIndexNode, and SetStatisticsNode classes.

Regards,
Vlad

Re: [Firebird-devel] [SPAM] Re: Crash Firebird 2.5.8 Embedded with Firebird Ado.net provider.

2018-02-07 Thread Vlad Khorsun via Firebird-devel

07.02.2018 15:21, Mark Rotteveel wrote:

On 7-2-2018 13:54, Vlad Khorsun via Firebird-devel wrote:

07.02.2018 14:41, Mark Rotteveel wrote:

On 7-2-2018 12:36, Vlad Khorsun via Firebird-devel wrote:

07.02.2018 13:26, Mark Rotteveel wrote:

On 7-2-2018 11:55, Mark Rotteveel wrote:
Shouldn't the engine be able to cope with this?

Yes, and fb_shutdown handle it. But it can't handle it correctly when process
exits and Windows already unloaded some dlls and kill all other threads.

That seems at odds with the documentation.

Documentation is not correct, unfortunately. It was written from Linux
perspective,
at times when such cases as current one was not known.

Ok. That means that the documentation may need to be revised there.

Yes, sure.

BTW, in Firebird 3 role of fb_shutdown is even more important as it uses
plugin
architecture and many code layers are at separate DLL's now.

I was just experimenting with Jaybird, and I noticed an odd inconsistency with fb_shutdown_callback on Windows. For other callbacks
in fbclient (ie the event callback), I must define the callback using StdCall calling conventions.

Hmm...

If I don't, things break. For the
shutdown callback, using the 'normal' JNA calling convention (which I assume is cdecl) just works.

In ibase.h all callback prototypes are declared not using ISC_EXPORT macro
(this macro
expands into __stdcall). So, i see no inconsistency there: (almost) all ISC API
functions declared using ISC_EXPORT and all callbacks are without ISC_EXPORT.

However with fb_shutdown_callback, if I do apply the StdCall convention, then the callback is invoked, but the application ends with
error 0xC409 ('unknown exception').

As expected.

I haven't (yet) defined the method fb_shutdown_callback to explicitly accept the StdCall callback (contrary to isc_queue_events),
but I'm surprised that it actually works without changing the calling convention. Is this something specific to the shutdown callback?

So far i see no specific here.

Regards,
Vlad

Re: [Firebird-devel] [SPAM] Re: Crash Firebird 2.5.8 Embedded with Firebird Ado.net provider.

2018-02-07 Thread Vlad Khorsun via Firebird-devel


07.02.2018 14:41, Mark Rotteveel wrote:

On 7-2-2018 12:36, Vlad Khorsun via Firebird-devel wrote:

07.02.2018 13:26, Mark Rotteveel wrote:

On 7-2-2018 11:55, Mark Rotteveel wrote:
Shouldn't the engine be able to cope with this?


   Yes, and fb_shutdown handle it. But it can't handle it correctly when process
exits and Windows already unloaded some dlls and kill all other threads.


That seems at odds with the documentation. 


  Documentation is not correct, unfortunately. It was written from Linux 
perspective,
at times when such cases as current one was not known.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Crash Firebird 2.5.8 Embedded with Firebird Ado.net provider.

2018-02-07 Thread Vlad Khorsun via Firebird-devel

07.02.2018 14:17, Mark Rotteveel пишет:

On 7-2-2018 12:36, Vlad Khorsun via Firebird-devel wrote:

07.02.2018 13:26, Mark Rotteveel wrote:

On 7-2-2018 11:55, Mark Rotteveel wrote:
Someone reported a crash on shutdown of Firebird 2.5.8 Embedded in a C# application on stack overflow:
https://stackoverflow.com/questions/48644878/appcrash-in-fbintl-dll-if-using-embedded-mode-in-my-c-app.

I can reproduce it with the provided example application.

I tried to reproduce this by recreating the example using Jaybird, but there it works OK. Does anyone have an idea what the
cause might be, and if this would be something in Firebird Embedded itself, or with the ADO.net provider?

I found the cause, the connection pool was active in Firebird ADO.net, and the connections weren't closed at the time the engine
was unloaded.

Shouldn't the engine be able to cope with this?

Yes, and fb_shutdown handle it. But it can't handle it correctly when process
exits and Windows already unloaded some dlls and kill all other threads.

But what causes the observed difference between the example table that only has an integer id column (and doesn't trigger the AV),
and the one that also has a varchar column and an expression index (lower(title))?

Varchar column and string function (lower) usage means that intl library
could be loaded and
used by the engine. Later, when process exits without explicit call of
fb_shutdown with open
connection to the database, there could be a case when fbintl.dll is already
unloaded by the
OS while fb_shutdown tries to correctly free resources (and call something in
fbintl.dll).

Note, the order of unload of DLL's by the OS is not defined.

Regards,
Vlad

Re: [Firebird-devel] Crash Firebird 2.5.8 Embedded with Firebird Ado.net provider.

2018-02-07 Thread Vlad Khorsun via Firebird-devel


07.02.2018 13:26, Mark Rotteveel wrote:

On 7-2-2018 11:55, Mark Rotteveel wrote:
Someone reported a crash on shutdown of Firebird 2.5.8 Embedded in a C# application on stack overflow: 
https://stackoverflow.com/questions/48644878/appcrash-in-fbintl-dll-if-using-embedded-mode-in-my-c-app.


I can reproduce it with the provided example application.

I tried to reproduce this by recreating the example using Jaybird, but there it works OK. Does anyone have an idea what the cause 
might be, and if this would be something in Firebird Embedded itself, or with the ADO.net provider?


I found the cause, the connection pool was active in Firebird ADO.net, and the connections weren't closed at the time the engine was 
unloaded.


Shouldn't the engine be able to cope with this?


  Yes, and fb_shutdown handle it. But it can't handle it correctly when process
exits and Windows already unloaded some dlls and kill all other threads.

Regards,
Vlad


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Crash Firebird 2.5.8 Embedded with Firebird Ado.net provider.

2018-02-07 Thread Vlad Khorsun via Firebird-devel


07.02.2018 12:55, Mark Rotteveel wrote:
Someone reported a crash on shutdown of Firebird 2.5.8 Embedded in a C# application on stack overflow: 
https://stackoverflow.com/questions/48644878/appcrash-in-fbintl-dll-if-using-embedded-mode-in-my-c-app.


I can reproduce it with the provided example application.

I tried to reproduce this by recreating the example using Jaybird, but there it works OK. Does anyone have an idea what the cause 
might be, and if this would be something in Firebird Embedded itself, or with the ADO.net provider?


  fb_shutdown should be called explicitly before fbembed.dll is unloaded. Yes, 
it is
called by fbembed.dll itself on DLL unload but it is not fully safe unless 
library is
unloaded explictly (by FreeLibrary call, not because of process exit).

  Additional problem in case on stackoverflow is not closed database connection
when fbembed.dll is unloaded. It could be a reason why you can't reproduce the
crash. Note, on "clean" exit (with no databases open), fb_shutdown have much 
less
work to do.

Hope it helps,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2018-01-29 Thread Vlad Khorsun via Firebird-devel


28.01.2018 10:44, Jiří Činčura wrote:

Sadly it has happened again few hours ago. 2.5.8 x64, running on Windows Server.


  Sad. I'll backport shared memory initialization from v3

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2018-01-19 Thread Vlad Khorsun via Firebird-devel


27.12.2017 0:38, Vlad Khorsun via Firebird-devel wrote:

26.12.2017 16:48, Vlad Khorsun via Firebird-devel wrote:

26.12.2017 11:45, Jiří Činčura wrote:

Looks like the issue is not happening in nightly 3.0.3 (about two weeks old).


   Good to know, thanks. Meanwile, i'm trying to reproduce it on v2.5.8 using 
test
case from CORE-4680. No luck so far.


   Found another issue with events (could be related with your case) and patch 
also
was not backported into v2.5. Could you try next snapshot build of v2.5.8 ?


  Any news ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird 3 and ICU

2018-01-19 Thread Vlad Khorsun via Firebird-devel


19.01.2018 11:41, Adriano dos Santos Fernandes wrote:

On 19/01/2018 07:14, Roman Simakov wrote:

Hello!

It seems that we have a problem with installation of Firebird 3 on
OpenSuse. I get "Could not find acceptable ICU library".

I took a look into sources. FB tries to search ICU modules in some
magic manner combining major and minor versions. But it looks in Suse
icu libraries has additional prefix like ".1" or "_1". I tries to make
symlink and server could find .so! However it could not find functions
because of the same problem. They also use such suffixes.

Anybody can fix a magic of library and functions looking up or at
least explain me an idea of naming?


You should use some heuristics, try each different name scheme...

Unfortunately ICU (or Linux distros) is changing this every time.


  I could be wrong, but... look at UnicodeUtil::getConversionICU():

---
const int majorArray[] = {5, 4, 3, 6, 0};

for (const int* major = majorArray; *major; ++major)
{
for (int minor = 20; minor--; ) // from 19 down to 0
{
if ((*major == favMaj) && (minor == favMin))
{
continue;
}

try
{
if ((convIcu = 
ImplementConversionICU::create(*major, minor)))
return *convIcu;
}
---

it search for ICU library using versions numbers such as 5.19 ... 5.0, 4.19 ... 
4.0 etc.

  But, IIRC, current ICU versions is 51.x, 52.x, 60.x etc. I.e. they changed 
version
numbering from N.xy to Nx.y. Is it correct ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Weird date/time literals

2018-01-18 Thread Vlad Khorsun via Firebird-devel


18.01.2018 13:10, Dimitry Sibiryakov wrote:

18.01.2018 11:57, Dmitry Yemanov wrote:

IMO, no sane person would rely on that intentionally.


   Why? This is the only method to get procedure compilation time, AFAIR.


  Time of CREATE and last ALTER of SP\Trigger\etc should be stored in system 
catalogue.

Vald

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5704) Avoid UPDATE of RDB$DATABASE by ALTER DATABASE statement when possible

2018-01-09 Thread Vlad Khorsun via Firebird-devel


09.01.2018 13:38, Dimitry Sibiryakov wrote:

09.01.2018 12:21, Vlad Khorsun (JIRA) wrote:

Actually, isql connects successfully, but it reads RDB$DATABASE itself right 
after attachment (using READ COMMITTED NO RECORD
VERSION WAIT transaction)


   Shouldn't it be changed as well?


  For what ?

Regards,
Vlad

PS Even if we change isql, it will not fix other applications

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] where is ':' valid in Firebird SQL?

2018-01-03 Thread Vlad Khorsun via Firebird-devel


03.01.2018 7:57, Lionel Elie Mamane wrote:


I'd like to avoid completely parsing the SQL statement for that,
mainly so that the SQL statement is not modified by the LibreOffice
SQL parser (and in particular quoting keywords it doesn't know as
identifiers). So I'm hoping to simply go through the string, keep
track of single and double quotes, and replace any word (outside of
quotes) that starts with ':' by '?'. 


  You also need to track mapping between parameters names and
parameters numbers. You may look at EDS::Statement::preprocess()
for example (see at \src\jrd\extds\ExtDS.cpp)

Hope it helps,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2017-12-26 Thread Vlad Khorsun via Firebird-devel


26.12.2017 16:48, Vlad Khorsun via Firebird-devel wrote:

26.12.2017 11:45, Jiří Činčura wrote:

Looks like the issue is not happening in nightly 3.0.3 (about two weeks old).


   Good to know, thanks. Meanwile, i'm trying to reproduce it on v2.5.8 using 
test
case from CORE-4680. No luck so far.


  Found another issue with events (could be related with your case) and patch 
also
was not backported into v2.5. Could you try next snapshot build of v2.5.8 ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2017-12-26 Thread Vlad Khorsun via Firebird-devel


26.12.2017 11:45, Jiří Činčura wrote:

Looks like the issue is not happening in nightly 3.0.3 (about two weeks old).


  Good to know, thanks. Meanwile, i'm trying to reproduce it on v2.5.8 using 
test
case from CORE-4680. No luck so far.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2017-12-20 Thread Vlad Khorsun via Firebird-devel


20.12.2017 11:16, Jiří Činčura wrote:

v3 after you isolated the databases (what do you mean by this, btw) ?


I'm keeping eye on v3. For sure it's not often. But I think I saw it even on 3 
before. Will wait a week or so to see whether it happens or not.

Isolated: I went through all apps and made sure these are using network 
connection and to be extra sure, all apps are now running in session 0.



  I can make build of v2.5 with that patch from v3 for you, if you wish.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2017-12-20 Thread Vlad Khorsun via Firebird-devel


18.12.2017 14:26, Jiří Činčura wrote:

Right now I isolated the databases, so only apps from the same session
(0) are accessing the database and always via TCP/IP. I'm afraid it's
something else.


  I found one change in v3 not backported into v2.5 which could be
related to this issue. Could you check if it is still happens with
v3 after you isolated the databases (what do you mean by this, btw) ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2017-12-18 Thread Vlad Khorsun via Firebird-devel


17.12.2017 17:33, Jiří Činčura wrote:

I.e. it is failed to initialize shared memory for monitoring snapshot.
I can't explain how it is possible to initialize lock manager shared
memory
first and then fail to initialize shared memory for monitoring snapshot.
Need more time to investigate it.


Any progress on this?


  Yes. I can explain how it happens but it will not solve your issue, 
unfortunately.

  In short: it could happen if Firebird service opens database and initialize 
lock
manager, then embedded process opens same database and initialize monitoring 
snapshot.
Then, when Firebird service need to open montoring shapshot's shared memory, it 
fails,
because mapped file object already created in another (isolated) windows 
session.

  I already explained this issue in this list: look for then message
"SeCreateGlobalObjects, Vista's UAC and Firebird" at 7 Aug 2009. That message 
at the
very end contains recommendations what to do in cases like yours:

--
If some application using embedded engine needs to work with the same database 
from different
users sessions (for ex. at terminal server) we could recommend following :

a) users from the Administrators group must run such application with elevated 
privileges

b) standard users could be granted SeCreateGlobalObjects privilege directly or 
by including them
into special users group which have this privilege granted.
--


Hope it helps,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Memory leak - How can I provide information about it?

2017-12-15 Thread Vlad Khorsun via Firebird-devel


15.12.2017 12:34, Dimitry Sibiryakov wrote:

15.12.2017 10:03, Vlad Khorsun via Firebird-devel wrote:

PS In theory, it should be possible to postpone transliteration into
attachment charset until application opens blob. But i see no easy\direct
way to do it.


   In theory it is possible to postpone transliteration till isc_get_segment() and avoid creation of temporary blob at all. Isn't it 
the same way as blob filters work?


  This is more-or-less the same as i wrote above

Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Memory leak - How can I provide information about it?

2017-12-15 Thread Vlad Khorsun via Firebird-devel


15.12.2017 11:20, Adriano dos Santos Fernandes пишет:

On 15/12/2017 07:03, Vlad Khorsun via Firebird-devel wrote:

15.12.2017 10:45, liviuslivius wrote:

Hi Vlad,


"This temporary blobs could be released on commit (rollback) only"

just curious, why it can not be released after it is used?


   When its become unused ? That blobs returned to the client aplication
and should be accessible until transaction end.

Regards,
Vlad

PS In theory, it should be possible to postpone transliteration into
attachment charset until application opens blob. But i see no easy\direct
way to do it.


I think fact that blob id is returned and really opened only when user
opens it is an implementation detail.


  Let me not agree here. Blob usage is not an implementation detail as we have
special Blob API with separate open\close operations.


If for performance reasons we decide to open it and send content withing
the message, we can.

It's like remote doing pre-fetch.


  This is different kind of optimization tradeoff. It is about bandwith (amount
of data sent) and roundtrips. And it is not good for every case.


So I think we shouldn't bother. User asked for it so will pay the cost.


  The one of the rules of optimization: if you can avoid to do something - 
avoid it.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Memory leak - How can I provide information about it?

2017-12-15 Thread Vlad Khorsun via Firebird-devel


15.12.2017 10:45, liviuslivius wrote:

Hi Vlad,


"This temporary blobs could be released on commit (rollback) only"

just curious, why it can not be released after it is used?


  When its become unused ? That blobs returned to the client aplication
and should be accessible until transaction end.

Regards,
Vlad

PS In theory, it should be possible to postpone transliteration into
attachment charset until application opens blob. But i see no easy\direct
way to do it.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Memory leak - How can I provide information about it?

2017-12-14 Thread Vlad Khorsun via Firebird-devel


12.12.2017 19:55, Gabor Boros wrote:

2017. 12. 12. 18:31 keltezéssel, Vlad Khorsun via Firebird-devel írta:

12.12.2017 17:38, Gabor Boros wrote:


If the problem can't be reproduces with a simple database and simple
application, it's probably another problem.


I have a simple application and three databases. Can I send to you privately? I attached earlier but got a message about the 40 
KB body limit.


   You may send it to me. Or, better, send download link.


Already sent a link to your address with a short message. I hope will arrive.


  I received it, thanks. I traced application and must agree with Adriano -
connection charset is UTF8 and, when it is not the same as blob charset, every
fetched blob is transliterated by the engine into connection charset. To do it,
engine creates temporary blob in connection's charset for every record in result
set. This temporary blobs could be released on commit (rollback) only, but your
application works in the single RCRO transaction.

  Should note, ORDER BY doesn't matters here.

Regards,
Vlad

PS Note, statement below doesn't eat memory :

  SELECT CAST(SUBSTRING(TABLE1.TEXT_BLOB_FIELD FROM 1 FOR 40) AS VARCHAR(40))
FROM TABLE1
  ORDER BY 1;

you may use it as workaround, if possible.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Memory leak - How can I provide information about it?

2017-12-12 Thread Vlad Khorsun via Firebird-devel


12.12.2017 17:38, Gabor Boros wrote:


If the problem can't be reproduces with a simple database and simple
application, it's probably another problem.


I have a simple application and three databases. Can I send to you privately? I attached earlier but got a message about the 40 KB 
body limit.


  You may send it to me. Or, better, send download link.

Thanks,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2017-12-07 Thread Vlad Khorsun via Firebird-devel


07.12.2017 17:16, Jiří Činčura wrote:

Nope. Still happening. Even with `-i`.


  Hmm... i was not attentive enough, error is about database not about listener.

  It means that one engine instance is already open database and another 
instance
going to open it too and both instances runs in different windows sessions.

  For example, one instance run in user session (for ex. embedded connection)
and second instance run as a service.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Database opened in another session

2017-12-07 Thread Vlad Khorsun via Firebird-devel


07.12.2017 16:23, Jiří Činčura wrote:

Hi *,

I'm getting, for quite some time, "Database is probably already opened
by another engine instance in another Windows session" on servers (2x
2.5.7, 1x 3.0.3 (although it's less common here)). The problem is, that
the database is only accessed via TCP/IP from local webserver(s). The
only unusual thing about the servers is the "sharding" - there's about
200 DBs on each and the connections are often very short (few seconds
tops) to all databases. Is there a way I can figure out what's going on?


  This message come from XNET listeners which can't create named object in
shared memory. You may ignore it if you don't use XNET at given Firebird
instance. Or, you may disable XNET: add switch -i to the command line of
firebird executable. Also, you may set IPCName setting in firebird.conf to
the different values at every firebird instance (client should have same
setting to be able to connect).

Regards,
Vlad


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Unique constraint not working on first command after encryption

2017-11-27 Thread Vlad Khorsun via Firebird-devel


27.11.2017 16:50, Jiří Činčura wrote:


But mostly wierd is that even in theory crypt plugin can not affect
integrity constraints - encryption works a few logical levels deeper
than constraints violation, that's absolutely different parts of code.


Yeah. I though the database was somewhat corrupted, but b went
absolutely fine.


  gfix -v -f ?

Regards,
Vlad


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] [SPAM] Re: Build sources

2017-10-13 Thread Vlad Khorsun via Firebird-devel


13.10.2017 12:16, liviuslivius wrote:

Hi,

I try to build Fb master from sources.
What is the recommended environment for Windows. Can i use MSVC14 Express?


  I never used Express version by myself. I use VS 2015 Community Edition 
currently
for testing VC14 builds.


Or it must be MSVC12? I see that for libtomcrypt is only MSVC12.sln.


  MSVC12 is kind of official compiler on Windows. MSVC14 build system is just 
updated.


When i tried to compile it under ide i got that command line switches /ZI and 
/Gy are incompatibile.


  It was error in Win32\Debug project files, fixed.


Is there some guide how to build FB from IDE? Or only possibility is build from 
command line and then run from the ide to debug?


  You need to use command line one time for the very first build for every
platform (32- or 64-bit):

1. open command prompt and point it to the builds\win32 folder

2. You may wish to set FB_PROCESSOR_ARCHITECTURE to the AMD64 or x86 value.
By default it will be set to the PROCESSOR_ARCHITECTURE value.
It defines what platform will be built - 64 or 32 bit correspondingly.

3. run following
- make_icu
- make_boot
- make_all (optional)

4. Now you may open builds\win32\msvc14\Firebird.sln in IDE and work as usual

  Note, build put binaries into temp\$(PlatformName)\$(Configuration)\firebird 
folder.
For example: temp\x64\Debug\firebird for debug x64 build. If you want to 
debug\run
firebird\utilities from IDE, you should put necessary .conf and security4.fdb 
files
there. And, of course, add SYSDBA into security4.fdb. It is required to do once,
after the first build.

  make_all copies all necessary files into output_$(Configuration) folder, this 
is
more-or-less what you see in snapshot builds.

  VC14 produces a lot of the same warning C4595, you may ignore it until we fix 
it.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] FB 3.0 Replacing ICU libraries on Windows

2017-10-12 Thread Vlad Khorsun via Firebird-devel


12.09.2017 15:33, Adriano dos Santos Fernandes wrote:

On 12/09/2017 08:46, Magnus Johansson wrote:

...
   
SELECT FIELD_UTF_SV_CI_NUM

FROM   TABLE_T
WHERE  FIELD_UTF_SV_CI_NUM BETWEEN '' AND 'b'
ORDER BY 1;
  
FIELD_UTF_SV_CI_NUM

===
1
2
10
20
a
A
aa
Aa
AA
b
===
Not the expected result.
Where did the 'B' go?


This should be verified.


  Adriano, did you investigate it ? May i help somehow ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Using ordinary database connection string in isc_service_attach() call

2017-10-11 Thread Vlad Khorsun via Firebird-devel


12.10.2017 0:37, Dimitry Sibiryakov wrote:

11.10.2017 22:56, Vlad Khorsun via Firebird-devel wrote:

   The problem is that you don't undestand that auth parameters (required to 
create
connection) should not be mixed with action's parameters.


   I can't understand what you talking about. Action parameters are in spb at isc_service_start() call. 


  Sure

Auth parameters are in spb parameters at isc_service_attach() call. 


  Exactly


Where you mixed them?


  I mixed ? It is you, who offer to use auth params as actions params !


   Where have you found this limit? What word from my message you read as 
"isc_svc_db_name cannot be used in spb anymore"?


  So, do you offer engine to add all params from isc_service_attach's SPB into 
isc_service_start's
SPB ?


   No. Try to re-read word by word:

--- README.services_extension -
8) Services API extension - passing database connection string as a service 
manager name.
(Dmitry Sibiryakov, 2017)

When database connection string is used in call isc_service_attach() or as
the first parameter of fbsvcmgr utility, this database is used
as a default value for "expected_db". And expected db become a default value
for "dbname".
If "expected_db" or "dbname" tags are specified explicitly, defaults have no
effect.
Old service names are still recognized, so this feature cannot be used with
databases named like "service_mgr", "backup", "anonymous", etc.



  This is substitution of one entity (*action* parameters) by another one
(*auth* params). This is hack, as for me. Currently, developer have single
place to set action's database name. You offer to set it here and there -
this is confusing at least and solves no problem.

Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Using ordinary database connection string in isc_service_attach() call

2017-10-11 Thread Vlad Khorsun via Firebird-devel

11.10.2017 23:33, Dimitry Sibiryakov wrote:

11.10.2017 22:14, Vlad Khorsun via Firebird-devel wrote:

IIRC, it is already implemented.

No. This functionality is in fbsvcmgr, but not in gbak.

It is implemented at Service API level, i.e. by all the providers (engine,
remote, etc)
but used not by all utilities (gbak), yes. And i still see no relation with
subject.

And currently it is rather hack with magic word "stdout" instead of
proper implementation.

Can't say that current way with "stdout" is perfect, but i'm really afraid
of your vision of "proper implementation", sorry

So, turn your imagination on. Service *is not a database*, is it clear ?
Service could
make any kind of job, it is not bound to any database.

Yes. If it does not need dbname, it won't get dbname. What's the problem?

The problem is that you don't undestand that auth parameters (required to
create
connection) should not be mixed with action's parameters.

A little for you to know: isc_service_attach() and isc_service_start() is
different routines
and requires different set of params. More, you may call isc_service_start()
many times within
the same service connection, AFAIU, and i see no reason to limit all the
service tasks by the
same database.

Where have you found this limit? What word from my message you read as
"isc_svc_db_name cannot be used in spb anymore"?

So, do you offer engine to add all params from isc_service_attach's SPB into
isc_service_start's
SPB ? Is it really good ?

You didn't read what I wrote.
As usual.

OMG. Write something more interesting next time, pls

fbsvcmgr.exe host:service_mgr action_nbak dbname employee nbk_file employee.bak
nbk_level 0
fbsvcmgr.exe host:employee action_nbak nbk_file employee.bak nbk_level 0

And what is the best: both these commands works and do exactly the same. No
old functionality is broken or changed.

So why do you need second one if it the same as first ? To save few pressing
of keyboard ?

No, not to save pressing of keyboards. To save app developer a lot of time he must waste on transforming database connection
string into service connection string.

Lot of time ? Are you kidding ???

I already told you that hiding one entity by another one is very bad idea.

Subject is not just hiding. One entity must simply die as completely unnecessary and having no purpose. But not right now. For
awhile it still can be here as fifth wheel.

RTFM: isc_service_attach() and isc_service_start()

Vlad

Re: [Firebird-devel] Using ordinary database connection string in isc_service_attach() call

2017-10-11 Thread Vlad Khorsun via Firebird-devel

11.10.2017 22:22, Dimitry Sibiryakov wrote:

11.10.2017 20:57, Vlad Khorsun via Firebird-devel wrote:
I don't understand your speculations

Ok, turn your imagination on: Performance of gbak must be improved by feeding of backup stream from server using services instead
of sending queries. How will you implement it?

IIRC, it is already implemented. And i see no relation with a way to set
connection string here.
If you need to implement new service action - define it parameters, as usual,
and implement it.
Where the problem ?

Because someone at IB times made decision that service connections is not a
database
connection. And i must agree that service could work with some database. Another
service could work with two databases and third service could work with no
database
at all (read firebird.log, for example). As expirienced user of ISC API you
should
know it.

No, I have no idea what service can work with two databases. Currently there is no one able to do that. Even multifile restore is
impossible with using of services.

So, turn your imagination on. Service *is not a database*, is it clear ?
Service could
make any kind of job, it is not bound to any database.

Every service action have set of parameters. In your second example i see no
paramenter
for action action_db_stats. This is not that kind of simplicity that i would
ask for.

action_db_stats requires no parameters.

It needs to set database name to gather stats from. Since Firebird is a DBMS
(note
first letter), many services works with databases (surprize) and uses some
common
parameters for its actions. But it doesn't mean that action_db_stats requires
no parameters.

I used it as the shortest example. If you insist on longer one, here it is.
Compare

fbsvcmgr.exe host:service_mgr action_nbak dbname employee nbk_file employee.bak
nbk_level 0
fbsvcmgr.exe host:employee action_nbak nbk_file employee.bak nbk_level 0

And what is the best: both these commands works and do exactly the same. No
old functionality is broken or changed.

So why do you need second one if it the same as first ? To save few pressing
of keyboard ?
And you consider it as "technical" reason to fool app devs ?

To finish with it: you make proposal and ask for objections, i gave you
objections,
what else do you want ?

If you forgot: I would like to hear technical problems that you foresee with proposed changes, not "I'm fine with current way, no
need to improve anything".

I already told you that hiding one entity by another one is very bad idea.
And there is no improvement in your proposal.
And you don't read what i wrote.
As expected.

Vlad

Re: [Firebird-devel] Using ordinary database connection string in isc_service_attach() call

2017-10-11 Thread Vlad Khorsun via Firebird-devel


11.10.2017 21:34, Adriano dos Santos Fernandes wrote:


I must agree that host:service_mgr is weird, but not all services
expects (or would expect) a database.


  Can't say it is weird. Probably, we could allow to skip service manager
name, but it will be hard to specify XNET protocol without it. I.e. now
we could use xnet://sevice_mgr.

  And, of course, i agree with you that service could expect no or more than
one database name.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Using ordinary database connection string in isc_service_attach() call

2017-10-11 Thread Vlad Khorsun via Firebird-devel

11.10.2017 21:09, Dimitry Sibiryakov wrote:

11.10.2017 19:47, Vlad Khorsun via Firebird-devel wrote:

I don't see it as a problem, as user already should understand what is
connection string and
how to construct is from host name and database name. From my expirience
"usual" applications
(such as accounting etc) already hide details about connection string from end
users. Tools for
developers, on the contrary, allows to set connection string (among other
params), but developers
should understand what is a host name, database name and service manager name.

So, i see no problem here.

Ok. To make gbak getting backup stream from service for performance purpose you suggest to change command line options from
current "gbak " to "gbak " and tell all users that they should understand it, right?

I don't understand your speculations

I.e. users will ask you for new version of application, good for you, isn't is ?

No, it isn't. At that point I may be already dead.

There is service connection and there is database connection. This is
different things.

Why they must be different? Services are not goal, it just a tool for working with databases. Even with the same connection
string, they use different API. Isn't this difference more than enough?

Your proposal hides the difference and just fools app devs.

I would say that it makes using of API simpler.

I can't say that currently it is hard to use and requires such simplification.

I see no benefits, just attempt to make a bit complex thing more complex and ambiguous.

Compare this:

fbsvcmgr host:service_mgr user sysdba password xxx expected_db employee
action_db_stats dbname employee

and this:

fbsvcmgr host:employee user sysdba password xxx action_db_stats

What is "more complex and ambiguous"?

Every service action have set of parameters. In your second example i see no
paramenter
for action action_db_stats. This is not that kind of simplicity that i would
ask for.

Perhaps, you must start to use services API to feel the benefits. As do I.

Sometime, when you speak for another person, it is so funny

To finish with it: you make proposal and ask for objections, i gave you
objections,
what else do you want ?

Vlad

Re: [Firebird-devel] Using ordinary database connection string in isc_service_attach() call

2017-10-11 Thread Vlad Khorsun via Firebird-devel

10.10.2017 19:00, Dimitry Sibiryakov wrote:

Hello.

Imagine that you are an application developer and develop an application that allow users to enter connection string for database
they want to work with. Imagine that in new version of the application you would like to add some work with services (backup,
restore, stats, etc.)

What options do you have to make a string that can be used in
isc_service_attach() call?

1) Ask user to enter host name and database name separately then combine the string
in form :service_mgr;
2) Parse database connection string entered by user to get host name.

First option has backward compatibility problem because in previous version of the application you asked only for whole
connection string.

So, i see no problem here.

Second option has forward compatibility problem because in new versions of
Firebird format of connection string can be changed.

I.e. users will ask you for new version of application, good for you, isn't
is ?

I'd suggest to offer to the application developer third, more convenient,
option: subj.

There is service connection and there is database connection. This is
different things.
Your proposal hides the difference and just fools app devs.

If database connection string is used this way, server can automatically set the database name to expected_db and dbname tags if
they are not provided explicitly.

isc_spb_expected_db was introduced for another purposes and looks like kind
of hack for me.

I.e. instead of "fbsvcmgr.exe localhost:service_mgr -action_nbackup -dbname test -nbk_level 0 -nbk_file file.nbk" one could use
"fbsvcmgr.exe localhost:test -action_nbackup -nbk_level 0 -nbk_file file.nbk".

Opinions?

I see no benefits, just attempt to make a bit complex thing more complex and
ambiguous.

Vlad

Re: [Firebird-devel] Using ordinary database connection string in isc_service_attach() call

2017-10-11 Thread Vlad Khorsun via Firebird-devel


11.10.2017 16:59, Dimitry Sibiryakov пишет:

10.10.2017 18:00, Dimitry Sibiryakov wrote:

   I'd suggest to offer to the application developer third, more convenient, 
option: subj.
   If database connection string is used this way, server can automatically set the database name to expected_db and dbname tags 
if they are not provided explicitly.


   I.e. instead of "fbsvcmgr.exe localhost:service_mgr -action_nbackup -dbname test -nbk_level 0 -nbk_file file.nbk" one could use 
"fbsvcmgr.exe localhost:test -action_nbackup -nbk_level 0 -nbk_file file.nbk".


   Opinions?


   If nobody objects, I'll create a ticket and PR.


  I object it.

Vlad

PS if this message will pass to the list, i'll explain why i object


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Test - please ignore

2017-09-30 Thread Vlad Khorsun via Firebird-devel


  Test 2

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Lateral derived tables

2017-09-10 Thread Vlad Khorsun via Firebird-devel


08.09.2017 11:03, Dmitry Yemanov wrote:

All,

The key point of this standard feature is to allow sub-queries to reference 
priorly defined contexts (in joins).

While thinking about this, I have a few questions to raise here. The standard defines LATERAL for derived tables only. This sounds 
logical but there are some corner cases to be discussed.


1) LATERAL used when it doesn't make sense, e.g.

-- single context
FROM LATERAL (SELECT ...)

-- first context
FROM LATERAL (SELECT ...) JOIN T ...

In both cases, there's nothing priorly defined to be referenced to.

a) should be prohibited syntactically
b) should raise error at prepare
c) should raise warning at prepare
d) should be silently ignored


1d


2) LATERAL does not reference prior contexts

-- T is not referenced from inside the derived table
FROM T JOIN LATERAL (SELECT ...)

a) should raise error at prepare
b) should be ignored, but join order must be enforced (derived table is 
executed per every row of T)
-- perhaps with a warning?
c) should be ignored, join order can be changed by the optimizer
-- perhaps with a warning?


2b, i think it is useful sometimes to enforce join order in such way



3) LATERAL was historically implied for joined stored procedures, e.g.

FROM T JOIN P(T.ID) ON 1=1

It used to produce invalid plan (and runtime errors) in older versions but FB3 
handles it properly by executing P after reading T.

a) keep status quo, disallow LATERAL syntax for procedures
b) keep status quo, allow LATERAL for procedures as syntax noise (LATERAL is 
implied even if missing)
c) allow LATERAL for procedures, allow external references via parameters only 
if LATERAL is specified (thus breaking legacy behaviour)
d) disallow LATERAL for procedures, require to be rewritten as 
standard-friendly (thus breaking legacy behaviour):

FROM T JOIN LATERAL (SELECT ID FROM P(T.ID))


3b


4) LATERAL in nested parenthesis joins, e.g.

FROM T1 JOIN LATERAL (T2 JOIN T3 ON T1.ID1 = T2.ID2 AND T2.ID3 = T3.ID3) ON TRUE

a) should be allowed
b) should not be allowed


4b


Opinions, please.

My personal votes: 1d, 2c, 3a, 4b

BTW, I see no way to allow LATERAL in RIGHT or FULL outer joins, so it should 
raise error.


  Agree

Regards,
Vlad


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

1 2 >

1 - 100 of 111 matches

Mail list logo