Re: [Firebird-devel] RFC: External Connections Pool
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? 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. From reading documentation I have a feeling that currently external connection is reused only within transaction. When local transaction ends, connection is disconnected. Am I wrong? 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. -- WBR, SD. -- 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
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? 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. 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 -- 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
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). 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. 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. 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 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. 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. It will behave the same as older versions, and we prevent annoying bugs by inconsistent behaviour caused by retained session state, or wrong expectations. 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. -- 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
On 2018-05-24 11:01, Mark Rotteveel wrote: On 2018-05-24 01:08, Vlad Khorsun via Firebird-devel wrote: 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. Ignore above, I was thinking to much about the autonomous transaction case. -- 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
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? 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. -- WBR, SD. -- 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] [FB-Tracker] Created: (CORE-5833) DDL triggers for some object types (views, exceptions, roles, indexes, domains) are lost in backup-restore process
DDL triggers for some object types (views, exceptions, roles, indexes, domains) are lost in backup-restore process -- Key: CORE-5833 URL: http://tracker.firebirdsql.org/browse/CORE-5833 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.3 Environment: Linux Mint 18.3, 64-bit Reporter: Zoran Vučenović For example: create trigger tr_bc_view active before create view position 0 as begin -- end Now, see the trigger's type: select rdb$trigger_type from rdb$triggers where rdb$trigger_name = 'TR_BC_VIEW' You get 540672 (HEX 84000). Backup the database. Restore the database. Now, query the trigger type again (same select as above), you get 16384 (HEX 4000). So, the byte which says that this is "create view" trigger is lost during backup-restore and this trigger is unusable. This happens with triggers defined to fire on views, as well as with triggers defined for exceptions, roles, indexes and domains. The triggers which fire on other objects do survive (triggers on tables, procedures, etc.). -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- 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
