[firebird-support] Re: Negative impact on system performance querying monitoring tables.
04.10.2019 15:50, Mark Rotteveel wrote: > The problem is inherent to the monitoring tables. If you query one > monitoring table, Firebird needs to collect and prepare a snapshot of > the data for all other monitoring tables to be able to provide a stable > and consistent view of the monitoring tables in your current > transaction. If you do this a lot, then it will need to repeatedly spend > that time, which can have an impact on performance, because the cost of > that data collection is non-zero (. > > So, the problem can't really be fixed, because that data collection will > need to happen and doing the collection takes time (CPU) and memory. Just an addition: In v2.5, pending MON$ requests could also [temporarily] block other connections' operations, thus decreasing the overall performance. This was improved in v3 to reduce the cross-locking with other connections. But generally, your message is absolutely correct. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Negative impact on system performance querying monitoring tables.
04.10.2019 15:01, Marcelo Guimaraes wrote: > > Reading about FB performance, I hit this page: > https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-appx05-montables.html > > My surprise on the note was the warning: "In a highly loaded > environment, collecting information via the monitoring tables could have > a negative impact on system performance." It should be re-phrased as: *frequent* collecting This operation is costly and should be used with care. > To administer a database and monitore his health usually we need to > query monitoring tables (dictionary views).. On FB, according to the > doc, we have a negative impact on system performance, so how can we by > pass that? Common recommendation is: 1) Do not query MON$ tables inside your SPs and triggers (ON CONNECT/DISCONNECT triggers could be an exception) 2) Do not query MON$ tables very often. Depending on your load (read: avg MON$ access time) it could be every >1min, >5min, etc. Dmitry
[firebird-support] Re: using parameters with EVENT firebird 2.5+
20.09.2019 13:16, Rustam rusta...@ukr.net wrote: > I don't now API details, but my idea: what if we can specify 'mask' in > Event name on clients, e.g. "ORDER_CHANGED*"? > > Then client subscribes to event "ORDER_CHANGED*", and his code receive > all events, who name is starting with "ORDER_CHANGED". This cannot work in the current API. Dmitry
[firebird-support] Re: using parameters with EVENT firebird 2.5+
19.09.2019 16:31, 'P-Soft - Fabio Codebue' wrote: > Is there a way to fires event with some parameters? Nope. > Now I see that parameter-aware events should use a "pull" > approach (parameter values are fetched by the client) instead of the > current "push" one (data is unconditionally sent to the client). > > Ref: http://tracker.firebirdsql.org/browse/CORE-642 > > Is it planned to be done? Or this kind of features are too expensive to > implement? It's hardly compatible with the existing events architecture and API, so I'm still somewhat sceptical about its implementation. Dmitry
[firebird-support] Re: Unsuccessful execution on delete FB 3 SP source code
13.06.2019 13:43, 'Mr. John' mr_joh...@yahoo.com wrote: > Using FB 3.0.4/Windows 10x64 ,I'm trying to delete SP source code as > worked in 2.5: > UPDATE RDB$PROCEDURES SET RDB$PROCEDURE_SOURCE = NULL WHERE > RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG=0; > > bun now on FB 3 tried: > > update RDB$PROCEDURES set rdb$procedure_source = NULL where > rdb$procedure_name > STARTING WITH 'MYPR_' > > and got > Unsuccessful execution caused by a system error that precludes > successful execution of subsequent statement s. > UPDATE operation is not allowed for system table RDB$PROCEDURES. Try adding one more condition to your WHERE clause: (...) and RDB$PROCEDURE_SOURCE is not null Dmitry
[firebird-support] Re: How to specify manual plan for this kind of query?
30.05.2019 9:35, liviuslivius wrote:
>
> i need to modify plan generated by Firebird and specify manual plan.
> I use Firebird 3.
> example on employee.fdb (sample from taken
> from README.common_table_expressions.txt)
>
> WITH RECURSIVE
> DEPT_YEAR_BUDGET AS
> (
> SELECT B.FISCAL_YEAR, B.DEPT_NO, SUM(B.PROJECTED_BUDGET) AS BUDGET
> FROM PROJ_DEPT_BUDGET B
> GROUP BY B.FISCAL_YEAR, B.DEPT_NO
> ),
> DEPT_TREE AS
> (
> SELECT D1.DEPT_NO, D1.HEAD_DEPT, D1.DEPARTMENT, CAST('' AS
> VARCHAR(255)) AS INDENT
> FROM DEPARTMENT D1
> WHERE HEAD_DEPT IS NULL
> UNION ALL
> SELECT D2.DEPT_NO, D2.HEAD_DEPT, D2.DEPARTMENT, H.INDENT || ' '
> FROM DEPARTMENT D2 JOIN DEPT_TREE H
> ON D2.HEAD_DEPT = H.DEPT_NO
> )
> SELECT D3.DEPT_NO,
> D3.INDENT || D3.DEPARTMENT AS DEPARTMENT,
> B_1993.BUDGET AS B_1993
> FROM DEPT_TREE D3
> LEFT JOIN DEPT_YEAR_BUDGET B_1993
> ON D3.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993
>
> it generate plan
> PLAN JOIN (DEPT_TREE D1 INDEX (RDB$FOREIGN6), DEPT_TREE D2 INDEX
> (RDB$FOREIGN6), SORT (B_1993 B INDEX (RDB$FOREIGN18, RDB$PRIMARY17)))
> but if i put this plan witgout any modification to the query
> it say:
> --
> Dynamic SQL Error
> SQL error code = -104
> Invalid command
> there is no alias or table named D1 at this scope level.
> --
Manual planning is the remainder of old times linear queries. It doesn't
work for nested and/or complex statements.
> How to put manual plan for this query?
You may try putting a sub-plan into particular select expression you
need to optimize. Or better use +0 hints, if possible.
> In my real situation query plan looks like
> PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))),
> JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
> and i need to change it to
> PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))),
> HASH (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
Really? I'd suppose the changed part should be:
HASH (CTE T T NATURAL, CTE K NATURAL))
Dmitry
[firebird-support] Re: TempCacheLimit - Integer?
13.05.2019 15:31, liviuslivius wrote:>> is TempCacheLimit is really Integer or Int64? int32 in v2.5, int64 in v3 and beyond. Dmitry
[firebird-support] Re: ODP: Question about index use
07.03.2019 14:23, Carsten Schäfer wrote: > > With the left join FB does a full scan on Table_A and uses the Foreign > Key index on Table_B. The index on table_B.field_B is not used at all. > > So you get millions of reads if the table contains millions of data How many rows in table_B exist for every row in table_A? > With the inner join the index on table_B.field_B is used and you only > get 1 read (if It's only 1 row that matches the condition) Yes, this is possible because the join starts with table_B! When INNER JOIN is used, the optimizer chooses the most effective join order. But this is impossible for LEFT JOIN, because of its nature. table_A is always read first and then joined to table_B. And it does not make sense to use an index on field_B when the foreign key index is already used. > Is it possible to force the optimizer tu use the index on table_B.field_B? Disable usage of foreign key index, e.g. via +0 hint. But I doubt it will make the timing better. Or specify an explicit plan with both indices used for table_B. Dmitry
[firebird-support] Re: multiple embedded versions
28.02.2019 23:17, Issam Boughanmi wrote: > > i have managed to use the firebird embedded on the shared hosting by setting > the FIREBIRD_LOCK variable Why? This is not necessary. > but i dont know if it safe to use this setup that way It's safe as long as FIREBIRD_LOCK is defined as system variable and nobody overrides it at the user level. But tweaking FIREBIRD_LOCK is a dangerous practice that is usually not recommended. > another option is to use a single fbembed.dll library for all the > website but i don't know if it's possible . It should be possible. Dmitry
[firebird-support] Re: How to "shape" indexes to avoid index depth > 3
22.01.2019 20:54, Sean Leyne wrote: > > In my database I have some indexes with depth = 4 (db page size is > already 16KB). > The indexes are multi-segment, with the fields sequenced from least to > most selective, and gstat reports a very low selectivity for the index > (less than 0.0001). > The indexed tables have between 100 million and 1500 million (yes, 1.5 > Billion) rows. > All the FB articles/docs/postings that I have seen have said for > performance, index depth should be no more than 3. This is not a hard requirement, 4 levels may be also OK. Are those multi-segment indices primary/unique keys or just for lookup purposes? Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Firebird 3: Not able to UPDATE RDB$Procedures
01.06.2018 14:49, Mark Rotteveel wrote: > Out of curiosity: why? Why not just allow any updates to NULL, even if > the existing value already is NULL? Setting NULL to NULL means the record wasn't changed. From the engine POV it's the same as e.g. SET RDB$PROCEDURE_ID = RDB$PROCEDURE_ID, i.e. all kinds of dummy updates would be also allowed. Too much for a temporary workaround. Dmitry
[firebird-support] Re: Firebird 3: Not able to UPDATE RDB$Procedures
01.06.2018 10:30, Chuck Belanger wrote: > > 2. The difference turned out to be that for one there was actually some > source code in the fields. While the other that threw an error message, > already had NULL fields in all records. > > 3. When limiting the UPDATE to a specific record rather than the entire > table and even then setting RDB$Procedure_Source = NULL only when there > is some source code to null, then it works fine. Only changes from non-NULL to NULL are allowed while updating. You need to filter out records already without sources. Dmitry
[firebird-support] Re: Doubt about TempCacheLimit parameter...
16.05.2018 07:05, Javier Cintron wrote: > Firebird ver 2.5.7.27050 64 bits > Superserver > Windows Server 2012R2 > > What is the unit of TempCacheLimit? Bytes. Settings > 4GB are not supported. Dmitry
[firebird-support] Re: Firebird temp files
09.05.2018 20:02, liviuslivius wrote: > Ok, i understand temp file caching but why Firebird create this temp > file at all and how to prevent this? Either some GTT was used or temporary blob storage was requested. Why do you want it to be prevented? > And second question, why file handle is still active? In SS, it will be closed/deleted as soon as all connections are gone. Dmitry
[firebird-support] Re: Firebird temp files
09.05.2018 17:15, liviuslivius wrote: > >> No, caching is left up to OS. > > i do not understand above. I have dissabled os caching in firebird.conf. > Also file is created by Firebird not OS. Can you explain this more? You've disabled file-level caching for the database file, but this setting doesn't affect temporary files. While they're created by Firebird, Windows is asked to aggressively cache them if possible: Quoting MSDN: "Specifying the FILE_ATTRIBUTE_TEMPORARY attribute causes file systems to avoid writing data back to mass storage if sufficient cache memory is available, because an application deletes a temporary file after a handle is closed. In that case, the system can entirely avoid writing the data. Although it does not directly control data caching in the same way as the previously mentioned flags, the FILE_ATTRIBUTE_TEMPORARY attribute does tell the system to hold as much as possible in the system cache without writing and therefore may be of concern for certain applications." There's no such a hint on Linux though, the default file-caching policy is used there. To ensure in-memory storage, TempDirectories may be tuned to redirect writes to tmpfs, for example. Dmitry
[firebird-support] Re: Firebird temp files
09.05.2018 11:41, liviuslivius wrote: > Is there particular settings to go always into RAM or some buffer > settings which i can increase to avoid going to disc? No, caching is left up to OS. > I have now > TempCacheLimit = 800M It doesn't affect GTTs. Dmitry
[firebird-support] Re: Limit of the page cache buffer in Firebird 2.5?
08.05.2018 20:50, Javier Cintron wrote: > Firebird ver 2.5.7.27050 64 bits > Superserver > Windows Server 2012R2 > > This link > says that the maximum size of the page cache buffer is 131,072 pages. > > This limit also applies to Firebird 2.5 x64 superserver in Windows? 131,072 pages is the limit for 32-bit builds. There's no explicit limit for 64-bit builds. Dmitry
[firebird-support] Re: Firebird temp files
09.05.2018 10:25, liviuslivius wrote: > > what is the purpose of temp file named e.g. > fb_table_yfz4x5? Storage for global temporary tables. Dmitry
[firebird-support] Re: order by 1, 2 - fb 32990
13.04.2018 13:30, acgomes2...@yahoo.com.br wrote:
>
> select N.DTEMISSAONOTA as DATA,
> 'DINHEIRO' as MEIO_PAGAMENTO1,
> cast('DINHEIRO' as char(10)) as MEIO_PAGAMENTO2,
> '00' as MEIO_PAGAMENTO,
> N.VLRTOTALNOTA as VALOR,
> 0 as AUTENT_01,
> 0 as AUTENT_01_CALC,
> N.AUTENTICACAO_NOTA as AUTENT_02,
> 3 as TIPO
> from NOTA N
> where (N.EMPRESANOTA = 1)
> and (N.DTEMISSAONOTA between '05/11/2018' and '04/11/2018')
> and (N.MODELONOTA = '55')
> order by 1, 2
http://tracker.firebirdsql.org/browse/CORE-5743
Dmitry
[firebird-support] Re: Are Firebird 3 performance improvement available in Firebird 3 dialect 1 database?
08.03.2018 12:23, jonatan.laurit...@yahoo.dk wrote: > > I have Firebird 2.1 dialect 1/charset NONE database and I am moving it > to Firebird to get all the performance improvements (64 bit, > multi-process capabilities, etc.). Move to dialect 3/charset UTF8 is > quite involved, I am trying to do this, of course, but as fallback > option I would like to move my Firebird 2.1 database to Firebird 3 > dialect 1/charset NONE. Will I get any performance improvements? Or > maybe Firebird 3 performance improvements are available for dialect 3 > databases only? No limits, dialect doesn't matter. Dmitry
[firebird-support] Re: Space used null values
27.02.2018 06:42, hugo.lar...@yahoo.com [firebird-support] wrote: > > Does null values take up disk space? Yes, but much less than the declared column size. > If I have a table with 4 VARCHAR columns and I add 4 more does the table > need double space even if the new columns are null? Already existing records will not be extended at all, the occupied space will remain the same. New record versions may slightly increase in size, but not double. Dmitry
[firebird-support] Re: SecurityDatabase in FB3
21.02.2018 15:46, Alexander Tabakov wrote: > > What happens in case 3 (stored in the same database) when we move the > database on a different server. Shall credentials stored in place take > precedence over the other server's configuration? It depends on the databases.conf on the other server. If you move it too, in-place credentials will be used. > In case of a server running in /classic /mode and case 2 (credentials in > a separate database), shall FB start a different process for each and > every connection just to verify user credentials? Nope, authentication uses the embedded connection, so new processes are not launched. Dmitry
[firebird-support] Re: Firebird 3 - Auto Garbage collection with Sweep interval = 0
16.02.2018 22:23, Helen Borrie wrote: > > A gbak backup does a sweep by default, unless you run it with the -g > switch ( [no_]g[arbage-collection] ). Small correction: gbak performs a global cooperative GC pass, not the sweep (which main purpose is moving OIT). Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: server 2.5.8 deadlocked
13.02.2018 05:07, Hamish Moffatt wrote: > I attached gdb and dumped the state of all threads, which shows every > one of them is waiting on a mutex or futex, ie it's dead locked. I've > attached the trace. You need to download the debug symbols (Firebird-debuginfo-* package) and copy them into your FB installation directory. Then take the backtrace again. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Adding a field with NOT NULL constraint
10.02.2018 22:33, Aldo Caruso wrote: > > A strange behavior is seen in the combination not null and no default > value. It is returned as a 0 for selects but treated as a NULL when > comparing. In fact, the engine returns NULL. But query prepare describes the output descriptor as NOT NULL. Some connectivity layers (including ISQL, IIRC) get fooled, as NULL is not expected from a NOT NULL descriptor, and zero / empty string is returned. I recall that IBExpert is able to return NULL in this case. Dmitry
[firebird-support] Re: Set custom value on connection system info
03.02.2018 23:14, marco andreolli wrote: > > but RDB$SET_CONTEXT() it's not right for me, because it store > information in actual connection (or transaction) and they can not be > read out of this (with a monitor program for example). It can be, via MON$CONTEXT_VARIABLES. Dmitry
[firebird-support] Re: Problem with CONTAINING and COLLATE
08.12.2017 13:56, Mark Rotteveel wrote: >> Because you are sending queries in connection charset. And string >> literals in them are >> in connection charset. Because of that your 'x' cannot have COLLATE >> PXW_HUNDC. > > I don't agree with this. The string literal is in the connection > character set, but the value should be converted to the target character > set (of the column) for the comparison, and that target character set > does know PXW_HUNDC. > > Right now, it looks like instead Firebird only tries to apply the target > collation, and not the target character set. In the Firebird grammar, COLLATE has a higher priority than comparison operators. So it's parsed as "A CONTAINING (B COLLATE C)". B has charset UTF8 and collation C doesn't belong here. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: query with no result
24.11.2017 10:53, alauren...@gmail.com wrote: > > I have a query with no results and I doo't understand what I do wrong: > > SELECT DENSUB, CODSUB from SUBANS,TEMPSUB > > WHERE SUBANS.CODSUB=TEMPSUB.CODSBB AND SUBANS.CODSUB NOT IN (SELECT > CODSBB FROM TEMPSUB) AND SUBANS.CODPF='FL1' order by SUBANS.DENSUB asc > > The query must return some values but, I don't get any errors nor results. Does TEMPSUB.CODSBB contain any NULLs? If so, it forces NOT IN to always return false. Perhaps you need to use NOT EXISTS instead. Dmitry
[firebird-support] Re: Firebird performance vs PostgreSQL
29.09.2017 09:50, Ertan Küçükoğlu wrote: > > Answer to your question is yes. KAYNAGAGORE and SEBEBEGORE are not > indexed. Please note that they're not indexed on PostgreSQL, too. > > Question remains as how PostgreSQL can cope with identical data only > with primary key indexes faster than FirebirdSQL? PG can do sort/merge for outer joins, whilst FB cannot (it can only for inner joins). When there are no indices for the joined columns, this makes a huge difference. PG uses the fast sort/merge algorithm, FB uses the slow (only without indices!) nested loop algorithm because it cannot do any better. This is a known subject of improvement. Dmitry
[firebird-support] Re: Firebird performance vs PostgreSQL
29.09.2017 01:08, Ertan Küçükoğlu wrote: Answers can be found in the plans: > PLAN JOIN (RAPOR_EK ORDER rapor_ek_pkey, SORT (DT M NATURAL)) This join plan is terrible for FB. Are KAYNAGAGORE and SEBEBEGORE indexed? They seem not. > Merge Left Join (cost=6600.19..6610.98 rows=560 width=340) FB cannot perform merge- or hash-join for outer joins (yet). When important indices are missing, this becomes a problem. Dmitry
[firebird-support] Re: Firebird Alpha 4 Release Notes
10.09.2017 02:13, Daniel Miller wrote: > The misunderstanding is probably mine - but I've never heard of 34-bit > values/precision before. Is that a typo for either 32 or 64? Or "34" > is correct? 34 *digit* precision. If "34-bit" is mentioned there, then it's a mistake. Dmitry
[firebird-support] Re: nbackup : Error assessing database due a missing delta file
03.08.2017 20:33, Jesus Garcia wrote: > > I only see three items pending, but anyway, why not remove Nbackup from > firebird until it is safe to use? > > Why is there a tool that cannot be used in a safe way? AFAIK, there are no known critical issues with nbackup in latest FB releases (2.5.6-2.5.7). Dmitry
[firebird-support] Re: Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error
28.03.2017 10:06, Martijn Tonies wrote: > > So the first 'fetch' operation on a position that's invalid returns > "rowcount=0", > any additional fetch (which includes a move) returns an exception? Yes. Moving cursor from "last" to EOF is perfectly valid, "no data" condition is not an error (as per SQL spec). After that, you may fetch prior and get the last record back. Moving cursor from EOF further can be theoretically processed in two ways: (1) silent no-op (cursor remains its position at EOF) and (2) raise an error. The former is somewhat error prone (can lead to infinite loops, for example), perhaps this is the reason why the engine raises an error instead. This is the logic existing since InterBase for DSQL cursors, and it was applied to PSQL cursors for consistency. Dmitry
[firebird-support] Re: Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error
27.03.2017 11:23, 'Martijn Tonies wrote: > > The FOR SELECT loop returns 1..10 as the values. > > The fetch/suspend lines return 1..10, with a row count of 1, and another > ‘10’ with a row count of 0. > > If I add another FETCH, I get this error while executing the routine: >>>attempt to fetch past the last record in a record stream At procedure > 'DEBUG_CURSOR_TESTS' line: 42, col: 3 > > And here’s my question: the last fetch has a row_count of 0, another > fetch returns an error. However, if I make this cursor a scrollable > cursor, and my first ‘fetch’ is a ‘fetch prior’, I immediately get this > error: >>>attempt to fetch before the first record in a record stream At > procedure 'DEBUG_CURSOR_TESTS' line: 9, col: 3 > > I get the ‘past last record’ error when I use: > fetch absolute 11 from c into :p_out1; > > I wonder, should the first fetch next that returns no data, return > row_count=0, or also the ‘fetch past record stream’ error? AFAIU, this is expected. When the cursor moves past the last record, "no data" condition is returned. In API, this means fetch() returning 100 (EOF marker). Inside PSQL, row_count is set to 0 that also indicates EOF. However, moving past EOF is not supported and it throws an error. It may look counter-intuitive to someone, but this is how our engine always worked (internally or via API). Dmitry
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
14.03.2017 20:54, 'Leyne, Sean' wrote: > > The -o -w switches generated details such as: > > OWNER BLOCK 732776 > Owner id: 114332029419524, type: 1, pending: 0 > Process id: 26620 (Alive), thread id: 2348 > Flags: 0x08 wake > Requests (431): forward: 732888, backward: 10111616 > Blocks: *empty* > 732776 waits on nothing. > > Most of the entries show: >Flags: 0x08 wake > > Oddly, all of the entries show: >Blocks: *empty* Nobody is waiting at the moment. This is why I suggested getting it periodically, hoping that some of the runs could step on somebody's wait. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
12.03.2017 00:12, 'Leyne, Sean' wrote: > > Am I right to think that I need to create a process to run the command on a > regular basis (every 5 secs?) to find what objects locks are waiting for? Yes. But we're still guessing in the dark, the reason of the slowdown could be completely unrelated to the lock manager (e.g. bad plans, undesired access to MON$ tables inside triggers, whatever else). Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
11.03.2017 23:56, Dmitry Yemanov wrote: > > I asked for -w, not -iw. Or maybe -o -w, if just -w prints nothing. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
11.03.2017 22:40, 'Leyne, Sean' wrote: > > Here are some initial results > > C:\FIREBIRD\bin>fb_lock_print.exe -d x -iw 1 5000 I asked for -w, not -iw. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
11.03.2017 21:03, 'Leyne, Sean' wrote: > >>> We have a client with 320GB database (running FB CS v2.5) >> >> Is it really so? FB does not support LockHashSlots more than 64K, it would >> truncate your 90001 down to 65521. > > Is the engine that smart to trim to an exact prime number? Nope, 65521 is just hardcoded as the max supported value, everything bigger gets silently reset to 65521. > Curious, how would the number of owners (even free owners) impact the lock > manager? It depends on what those owners (read: connections) do inside the database. > Today's numbers are much worse, again. But the activity load is > substantially lower (based on the "Enqs" and "Acquires" values). > > LOCK_HEADER BLOCK > Version: 145, Active owner: 0, Length: 67108864, Used: 5269256 > Flags: 0x0001 > Enqs: 202113295, Converts: 934229, Rejects: 96674, Blocks: 615455 > Deadlock scans:103, Deadlocks: 0, Scan interval: 10 > Acquires: 326947224, Acquire blocks: 186433223, Spin count: 0 > Mutex wait: 57.0% Too many deadlock scans, it means long (> 10 sec) waiting for some locks. Regular (every few minutes) fb_lock_print -w output could probably shed some light... Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
10.03.2017 23:58, 'Leyne, Sean' wrote: > > We have a client with 320GB database (running FB CS v2.5) Is it really so? FB does not support LockHashSlots more than 64K, it would truncate your 90001 down to 65521. > Yesterday, I ran fb_lock_print to check on the database and found a > “Mutex wait” value of 20.9%, which I knew that a “bad thing”. So, I > increased (by 50%) the “Hash slots” value from 60011 to 90001. These value are not directly related. Most probably, you didn't need to change that setting. > Now, today, I checked the lock print values again, and got even worse > numbers!!! The change didn't help, the reason is elsewhere. 21% vs 23% could be explained by e.g. printing the stats at 3PM and 5PM. And we don't know what was the mutex wait ratio before they reported the performance issues. > Hash slots: 90001, Hash lengths (min/avg/max):0/ > 0/ 5 Hash stats looks good. > Owners (288): forward: 732776, backward: 29775680 > Free owners (400): forward: 9817904, backward: 9548560 688 peak connections. Is this expected? Could the load be growing recently? > Any suggestions on how I can improve the numbers? Nothing based on this lock print. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Bloated security2.fdb
06.03.2017 21:48, 'Neil Pickles' wrote: > One thing I don’t understand is why there would be over 744 million txns > on this file? Is a new txn started each time a connection to the > database is made? Of course, the server needs to validate your login/password against the security database. > I find when it gets to this kind of size I see intermittent errors > ‘Unable to attach to password database’ from our applications and I > have found the only way to ‘fix’ that is to reinstall a clean version of > Firebird which resets the security2.fdb file back to 760K and everything > works again without errors. I had to do this today in this installation > for this very reason. I'd say the error is not expected, as I don't see any limits reached. But there could be something related but not so obvious, especially in old versions. firebird.log should contain a record about such failures, at least in FB 2.5. What does it say? > Is there something wrong with this or is this supposed to happen, should > security2.fdb be growing to over 190 Meg and beyond while containing > just a single record? This is expected. > Is there some kind of housekeeping that should be done to keep > security2.fdb in shape? Backup/restore? Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: How to check and (possibly) repair new authorization system on Firebird 3.0.1
01.03.2017 11:37, jonatan.laurit...@yahoo.dk wrote: > Actually, I don't understand this thing about old and new authorization > system. Does it mean that there are two SYSDBA and other user accounts - > one for old and other for new authorization system and does it mean that > there are two sets of passwords? Exactly. Dmitry
[firebird-support] Re: How to check and (possibly) repair new authorization system on Firebird 3.0.1
01.03.2017 11:58, jonatan.laurit...@yahoo.dk wrote: > OK, I have working Firebird 3.0.1 installation and I have copied > security3.fdb from the broken Firebird installation and now I am also > receiving the error message. So - security3.fdb is somehow broken, > problem is in security3.fdb It's not broken, it just misses SYSDBA account. Dmitry
[firebird-support] Re: Error registered in Windows event Viewer
21.02.2017 15:53, michael.vilhelm...@microcom.dk wrote: > > ANS4005E Error processing > '\\db07\c$\ProgramData\firebird\fb_lock_068acd221200a400': file > not found > > What does this mean? > > Should I be worried? Nothing to worry about. Some MS or 3rd party software (backup? antivirus?) attempts to scan files in the FB directory but fails to do that for the lock-file being removed by the FB engine in the meantime. Dmitry
[firebird-support] Re: Firebird 3 execution plan
16.02.2017 21:20, liviuslivius wrote: > > can this plan be selected by Firebird in somehow automatically without > going to left join or +0 solution? In this particular case, I'm afraid you don't have any other choice. But I will think a little bit more, maybe some clever idea will come. Dmitry
[firebird-support] Re: FB3 - max length of varchar fields and slow queries with blobs
17.02.2017 15:29, André Litfin a.lit...@xsigns.de wrote: > > I thought FB3 should remove the limitation to max. length of for > varchars (32765 bytes). I tried today to create a longer varchar field > without luck. Is the limitation still active? Yes, there were no plans to remove it. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Firebird 3 execution plan
16.02.2017 21:20, liviuslivius wrote: > > Firebird3 There's no such a version. > plan is "expected" > PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID, S INDEX (PK_SENSOR)) What's definition of IXDU_SENSOR_DATA__ID? Dmitry
[firebird-support] Re: detect duplicate blobs, how to?
09.02.2017 17:39, hamacker wrote: > Well, the man page says fully supports text BLOBs, I assume that binary not. For blobs, it works with raw binary data. Dmitry
[firebird-support] Re: detect duplicate blobs, how to?
09.02.2017 14:48, hamacker wrote: > > There is a HASH function but only for string. It works for blobs as well. Dmitry
[firebird-support] Re: Character set of parameter strings
06.02.2017 22:08, Dimitry Sibiryakov wrote: > > When a string is sent to server via a query parameter, how is its character > set > determined: from XSQLVAR.sqlsubtype field or from connection's lc_ctype > parameter? From the XSQLVAR.sqlsubtype, which in turn is determined based on the lc_ctype during prepare (but can be overridden by API user). Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Full join and indices
23.01.2017 15:05, Kjell Rilbe wrote: > > I'm still on FB 2.5.2, so... has this been improved in 3.0? Yes, it was (to some extent). Dmitry
[firebird-support] Re: FB3: Default create database location
29.12.2016 23:05, Helen Borrie hele...@iinet.net.au wrote: > >> On windows systems, if you create a database without path specification, >> firebird tries to create it in the windows/system32 directory. > > No; it will use the current directory By default, for any Windows service (including Firebird running as a service) the current directory is %system%. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: rdb$get_context('SYSTEM', 'CLIENT_ADDRESS') sometimes returns NULL
22.12.2016 10:43, Geoff Worboys wrote: > > I do have a couple of EXECUTE STATEMENT ON EXTERNAL calls in > the database code. I hadn't given them a thought. In theory > none of them should happen during the startup, but it does > seem the most likely explanation. They don't need to run during the startup. They just cause a separate connection to be established and in turn this triggers the ON CONNECT event. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: rdb$get_context('SYSTEM', 'CLIENT_ADDRESS') sometimes returns NULL
21.12.2016 03:45, Geoff Worboys wrote:
>
> Firebird v2.5.6 32bit "Classic"
> on Windows 64bit 2012 R2 Standard
>
> I have an ON CONNECT database trigger in which I try to collect
> the client address and network protocol using:
>
> rdb$get_context('SYSTEM', 'CLIENT_ADDRESS')
> and rdb$get_context('SYSTEM', 'NETWORK_PROTOCOL')
>
> Most of the time they work, but sometimes they return NULL.
Usually this means an embedded connection to the database. While
CLIENT_ADDRESS theoretically could be NULL for network connections, I
can hardly believe this is possible for NETWORK_PROTOCOL which is filled
by the remote server and does not depend on the client in any way.
Even if you don't use fbembed to access that database, it could happen
implicitly on the server side, e.g. if you use EXECUTE STATEMENT ON
EXTERNAL and use a hostless connection string, or if you run
gbak/gstat/gfix via the Services API (they connect embeddedly in this case).
Dmitry
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: NEXT VALUE FOR on Firebird 3.0.1 32bit Windows 7 32bit
21.12.2016 12:39, Roland Turcan wrote: > > But still, why this problem occurred? Why didn't some generators have > it =1? Could that database be migrated from some pre-release (e.g. Beta) v3 build without backup/restore? Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: NEXT VALUE FOR on Firebird 3.0.1 32bit Windows 7 32bit
06.12.2016 11:50, Mark Rotteveel wrote: > I noticed something interesting, when restoring a Firebird 2.5 database > to Firebird 3.0, only user generators (RDB$SYSTEM_FLAG = 0) get a value > of 1 in RDB$GENERATOR_INCREMENT, system generators get 0. Is that > intentional? System generators are not transferred in backup, they're recreated from scratch when a database is being created during restore. And you cannot explicitly issue either GEN_ID or NEXT VALUE for them, the engine itself increments them at the low level, without looking at RDB$GENERATOR_INCREMENT. So I believe it's absolutely OK, although possibly looking weird. Dmitry
[firebird-support] Re: NEXT VALUE FOR on Firebird 3.0.1 32bit Windows 7 32bit
06.12.2016 11:05, Roland Turcan wrote: > > I have installed new clean database to new customer having FB3.0.1 > 32bit running on Windows7Pro 32bit. He was complaining that he is not > able to add new record into some parts of our software. I have > connected to his computer and made backup/restore and aligned all > generators to MAX(ID) to be sure they are correct. But he still had > some troubles then I started to investigate what the hell is going on. > I have found, that on his "server" is NEXT VALUE FOR not incrementing > generator value, but places where GEN_ID is used instead are working > fine. > I have tried to reproduce this on 32bit XP with the same version of > FB, but in my case it was running fine. > > Does anybody have this kind of trouble? Check RDB$GENERATOR_INCREMENT inside RDB$GENERATORS. NEXT VALUE FOR uses the increment stored inside the system table. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: RES: VIEW CREATES ON FB 2.5 BUT DO NOT CREATE ON FB 1.5
29.11.2016 13:45, 'Israel Pinheiro' wrote: > > Invalid Token. Dynamic SQL Error. Invalid Command Data Type unknown Check datatypes of all union parts, i.e. select A1, B1, C1 union select A2, B2, C2 union select A3, B3, C3 Data types for (A1, A2, A3) should match exactly. The same for B and C. Dmitry
[firebird-support] Re: FB3 Memory usage
04.11.2016 13:30, Si Carter wrote: > The memory usage crept up to just under 3gb again Once again, where do you get that number from? Task manager? Perfmon? top? Screenshot proof would be appreciated. > The memory usage crept up to just under 3gb again, and I took a snapshot > of the data using the previous sql, from all 4 databases. Between the 4 > databases there are over 1500 orphaned records, and between the 4 > databases there are 30 identifiable connections. There are no "orphaned" records, it's just a result of your outer join -- you also get non-identified transaction- and statement-level counters (mon$stat_group > 1). And because of that, you shouldn't sum all the values in the column - the counters are already aggregated statement->transaction->attachment->database, so you just get wrongly multiplied results. > I have placed the raw data in a csv file (http://tinyurl.com/zkqf9bo) The current memory usage is just 350MB but there was a peak of 3.4GB (3GB of them were used for db1.fdb). What is your TempCacheLimit setting in firebird.conf? If it's set too high, the peaks could be explained by heavy sorts. Dmitry
[firebird-support] Re: max rows par table in FB3
04.11.2016 13:48, Norbert Saint Georges wrote: > > the recording limit for FB3 is the same as FB2.5? Yes. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: FB3 Memory usage
03.11.2016 09:37, Si Carter wrote: > > I recently installed FB v3.0.1.32609 on Windows server 2008. > > This is a dual installation as I still have 2.5.3 installed. > > I noticed that my fb3 service is using in excess of 4gb memory How did you get that number? > Given this sql > > SELECT ma.MON$ATTACHMENT_NAME, ma.MON$USER, a.MON$STAT_ID, > a.MON$STAT_GROUP, a.MON$MEMORY_USED, a.MON$MEMORY_ALLOCATED, > a.MON$MAX_MEMORY_USED, a.MON$MAX_MEMORY_ALLOCATED > FROM MON$MEMORY_USAGE a > LEFT JOIN MON$ATTACHMENTS ma ON (ma.MON$STAT_ID = a.MON$STAT_ID) > ORDER BY ma.MON$ATTACHMENT_NAME > > it produces the following results, STAT_ID 1 is the one I am more > concerned about at this point. > > [null] [null] 138 2 3248 0 4272 0 > [null] [null] 115 2 44048 0 440480 > [null] [null] 218 3 22104 8112 87984 73688 > [null] [null] 116 3 22384 8112 91760 73688 > [null] [null] 95 3 21192 24336 2121800 2121568 > [null] [null] 1 0 58128176 81557240 1685336336 2771125200 I see that the database utilizes only 81MB of memory (with a 2.8GB peak somewhen in the past). > Previously it was using in excess of 5gb of ram, a restart bought it > back to normal, but it soon escalated to 3gb (within seconds) and now to > 4gb ram. I don't see 4GB here, sorry. > The database has only 4 normal connections, plus Cache Writer and > Garbage Collector, so it's not under unusual usage at the mo. Number of connections does not directly affect memory usage. Dmitry
[firebird-support] Re: Calculating Transaction Inventory Pages (TIP)
24.10.2016 18:08, 'Gregor Kobler' wrote: > > Is it also valid for Server 3.0? Are the roules 4 transaction per byte > also valid at FB3 ? Yes. Dmitry
[firebird-support] Re: AW: Problem querying MON$STATEMENTS table
16.09.2016 09:02, 'Gregor Kobler'wrote: > > When you querying the MON$STATEMENTS table, the active statements are > not shows a transaction-id and not a timestamp, These columns are allwas > Zero / NULL! Transaction ID and timestamp are reported only for active statements, they're NULL for idle ones. You've said that MON$STATE = 0, so your statements are idle, not active. Dmitry
[firebird-support] Re: Problems after FB3 upgrade
06.09.2016 08:55, Tony Christiansen wrote: > > My client needed a server rebuild after a ransomware attack so I took > the opportunity to upgrade from FB2.5 to FB3. My client application is > Delphi using IBX components. > > Now they are experiencing repeated crashes with an array of exceptions > - the most common being "Error writing data to the connection" and > "invalid transaction handle (expecting explicit transaction start)" > mostly on select queries. It's worth trying a v3.0.1 snapshot before considering downgrade. It may have these issue fixed already. Dmitry
[firebird-support] Re: fb_get_master_interface, cursor fetchPrior, fetchLast =feature is not supported
13.07.2016 12:01, Norbert Saint Georges wrote: > it's normal ? Yes, scrollable navigation is not yet supported by the remote protocol, only by the embedded engine. Maybe support will be added in some point release. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Firebird 3.0 - invalid transaction handle (expecting explicit transaction start).
16.06.2016 09:02, Roland Turcan wrote: > > I am trying to deploy Firebird 3.0 to our customers. Only three small > customers are running on FB3, but all of them have the same problem: > > invalid transaction handle (expecting explicit transaction start). > > This exception happens on different places which are not reproducible. > It doesn't even depend if software built on Delphi XE6 IBX or > FirebirdSQL.NET provider. Both drivers are having the same problem. > I have got it on my test machine only once, but I can't get it again. > > The fact is, that I have checked logs of all customers running FB2.5 > and no one has this problem on FB2.5 for years. This appears to be a bug in Firebird 3, being researched right now. I hope to have the fix committed in a day or two. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Clarification about 64bit and 32bit Firebird 3.0 software
07.07.2016 10:54, jonatan.laurit...@yahoo.dk wrote: > Are the following statements correct? > > - Most Firebird client software still is 32bit (Flamerobin, IBExpert, > Delphi 2009 and older editions) and therefore it requires 32bit Firebird > 3.0 client dlls (fbclient.dll or gds32.dll) even if it connects to the > 64bit edition of server. > > - 64bit Firebird server can receive connections both from 32bit and > 64bit clients. > > - Both 32bit and 64bit Firebird server uses the same ODS (physical) file > structure therefore DB.FDB created with 32bit server can be simply > copied and used on 64bit server and vice versa. Correct (given that "simply copied" implies stopping the FB server). Dmitry
[firebird-support] Re: FB 3 - Per database configurations (Providers and Security Database vs Legacy Auth)
07.07.2016 07:25, Alexandre Benson Smith wrote:
>
> I am looking at the per-database configuration (FB 3) and have two doubts:
>
> I defined my databases.conf like:
> demo_naolocal=c:\bd\demo_naolocal.fdb
> {
> Providers = Remote,Loopback
> }
>
> after that on the same machine I tryed:
>
> C:\fb3>isql demo_naolocal -user teste -password abc
> Statement failed, SQLSTATE = 08001
> I/O error during "CreateFile (open)" operation for file "demo_naolocal"
> -Error while trying to open file
> -O sistema não pode encontrar o arquivo especificado. (Translates to:
> The system could not find the specified file)
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> quit;
>
> C:\fb3>isql localhost/3053:demo_naolocal -user teste -password abc
> Statement failed, SQLSTATE = 08001
> unavailable database
> Use CONNECT or CREATE DATABASE to specify a database
> SQL>
>
> I expected the first one to fail and the second to succeed.
Your expectation is correct from the client POV. But you miss the fact
that the network server (namely: firebird.exe) also connects to the
database through the y-valve so the same configuration is used again.
And now the engine provider is strictly required.
I.e. if c:\fb3 contains a default config but isql runs from a different
directory (with modified configuration), then it would work.
Dmitry
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: It works on FB 2.5 but does not work in FB 1.5
23.06.2016 19:39, 'Israel Pinheiro' wrote: > > I created a view that works perfectly on FB 2.5, but now need to create the > same view on FB 1.5, but appears error in creation. Can help me? http://www.firebirdfaq.org/faq92/ Double-check that both union parts have exactly the same datatypes for all elements in the select lists. Dmitry
[firebird-support] Re: gbak with -se switch?
22.06.2016 13:44, Martijn Tonies wrote: > >>> When connected to the database from another application, here: >>> C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t -v >>> -user sysdba -pas masterkey >>> "e:\temp\2014 FB Conference.fdb" "e:\temp\2014 FB Conference.bak" >> >>Don't you forget "martijn-ws-dev/3054:" in database connection string?.. > > This doesn't use the service manager. You connect to the database using the embedded engine. Of course, it refuses connection because some other application is already connected. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: gbak with -se switch?
22.06.2016 13:40, Dmitry Yemanov wrote: > Worth registering in the tracker. It seems it's already there: http://tracker.firebirdsql.org/browse/CORE-5263 Dmitry
[firebird-support] Re: gbak with -se switch?
22.06.2016 13:14, Martijn Tonies wrote: > > When connected to the database from another application, here: > C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t -v >-user sysdba -pas masterkey >"e:\temp\2014 FB Conference.fdb" "e:\temp\2014 FB Conference.bak" Hostless connection string, as I expected. >> InterBase API Guide, page 202. > > I have to look in the >>API guide<< for using a command line tool? As an end user, you should not. GBAK reference surely deserves fixing. As a tool/component developer, I'm pretty surprised you don't know how to connect to the service manager remotely. > OK, tried that. Connected to the renamed database from another application, > ran this statement: > > C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t >-se martijn-ws-dev/3054:service_mgr >-v -user sysdba -pas masterkey >"e:\temp\2014_FB_Conference.fdb" >"e:\temp\2014_FB_Conference.bak" > > (still used quotes, changed spaces into underscores) > > It ran just fine. So the problem seems to be with embedded blanks, not with quotes. > See above. Strangely enough, it fails when using -se, but not without > using -se. Because supposedly the problem is inside the service manager. GBAK uses a somewhat original way of interacting with it. Worth registering in the tracker. Dmitry
[firebird-support] Re: gbak with -se switch?
22.06.2016 12:49, 'Martijn Tonies wrote: > >> I understood you as that regular gbak does not work when the database is >> in use, so now you try the -se switch. > > That is correct, but as you can see, using -se resulted in an error. So what was the error using gbak without -se? And what was the command line you used? > where does the docs say to use the host before 'service_mgr' instead of > the (source) database string? InterBase API Guide, page 202. >> Did you try using filenames without embedded spaces (and thus without >> quotes)? > > No, cause the database name contains spaces ;) Is it too hard to rename it? I'm trying to reduce the possible reasons but you insist. > Could that be the cause of the multiple sources/destinations error? If so, > I'll take it this is a parsing error in gbak? It could be, but it should be proved. Dmitry
[firebird-support] Re: gbak with -se switch?
22.06.2016 12:35, 'Martijn Tonies wrote: > > As per original example: > Here’s the command: > C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t -se service_mgr –v > -user sysdba -pas masterkey >"MARTIJN-WS-DEV/3054:e:\temp\2014 FB Conference.fdb" >"e:\temp\2014 FB Conference.bak" > > In my book, this -looks- like a connection string with host. I understood you as that regular gbak does not work when the database is in use, so now you try the -se switch. > So I tried: > > C:\Program Files\Firebird\Firebird_3_0>gbak -b -m -t >-se "martijn-ws-dev/3054:service_mgr" Quotes are not needed here. >-v -user sysdba -pas masterkey >"e:\temp\2014 FB Conference.fdb" "e:\temp\2014 FB Conference.bak" Did you try using filenames without embedded spaces (and thus without quotes)? Dmitry
[firebird-support] Re: gbak with -se switch?
22.06.2016 11:25, 'Martijn Tonies wrote: > > Firebird 3 fails to back up when the database is in use It does not, as long as you don't use a "hostless" connection string. Dmitry
[firebird-support] Re: Not a valid database
21.06.2016 03:52, Seyeong Jeong wrote: > > I have a firebird database with ODS version 10.1. While I can open this > with server version WI-V2.1.7.18553 Firebird 2.1 on Windows without any > errors, the same operation fails on Ubuntu. I suspect your Windows version (or Firebird version running there) is 32-bit, correct? > These are the versions on Firebird I tried on Ubuntu: > > - Firebird/linux AMD64 (access method), version "LI-V2.5.1.26351 > Firebird 2.5" > - Firebird/linux AMD64 (access method), version "LI-V2.1.7.18553 > Firebird 2.1" > - Firebird/linux AMD64 (access method), version "LI-V2.1.4.18393 > Firebird 2.1" > - Firebird TCP/IP server version LI-V1.5.4.4910 Firebird 1.5 > > All versions of Firebird fails to open the database with isql-fb with > the following message: > > SQL> connect "/home/user/a.fdb" user '***' password '**'; > > Statement failed, SQLSTATE = HY000 > file /home/user/a.fdb is not a valid database This error is raised when the database is not understandable to the engine. Prior to ODS 11, databases were not binary compatible between 32-bit and 64-bit architectures. You cannot just copy a ODS 10.1 database to a host with different bitness and use it there. Backup/restore is required instead. Dmitry
[firebird-support] Re: Column Name Size
17.06.2016 10:56, Dimitry Sibiryakov s...@ibphoenix.com wrote: > >>> Is there the limitation of 31 bytes for column names in the new FB 3? >> >> 31 CHARACTERS - as before > > No, it is 31 byte. Correct, the limit is 31 *bytes*. The legacy UNICODE_FSS charset is multi-byte (up to three bytes per character) in user-defined tables, but it's treated specially for system tables, limiting the length to 31 bytes, not characters (like it was a single-byte charset). The reasons are mainly historical (thanks to Borland) and related to backward compatibility of API and GPRE-generated code. v3 preserves this legacy behaviour. v4 resolves this issue from multiple angles: - switching to UTF8 instead of UNICODE_FSS - counting the length in characters instead of bytes - increasing the limit to 63 characters (at the moment, may change before the final release) Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Schema pluses?
15.06.2016 22:48, 'livius' wrote: > > we can write sql like > select * from table_name but really this is > select * from schema.table_name > and if i write this in stored procedure – i suppose that “object_id” > will be stored in blr(when schemas will be avaiable) BLR will contain schema.table_name. > and also some task – user1 tell user2 “send me sql to retrive something” In this case, user1 does not know about user2. They live in two parallel universes. > I see only complication over benefits You just don't see the point from the integration (read: enterprise) POV. For simple setups (single database), schemas are just two-level namespace, with the benefit of "A.B" being longer than "A_B". Dmitry
[firebird-support] Re: Schema pluses?
15.06.2016 22:49, 'livius' wrote: > > but this feature is considered for FB4 No, it's not. At least not for v4. Dmitry
[firebird-support] Re: Schema pluses?
13.06.2016 00:18, 'livius' wrote: > > what are + and – with working with schema? > What benefits are between > schema_name__table_name and real schema implementation? > schema_name.table_name Ability to have a default schema (per user, per connection). So that you may have multiple completely unrelated databases inside single physical database, and set up multiple "schema-less" users/applications of these different logical databases. E.g. one app works with USERS but actually it's CRM.USERS and a different app also works with USERS but actually it's HR.USERS. From the maintenance/administration POV it's a single database (configuration, backups, etc). Dmitry
[firebird-support] Re: FB3 - Is RemoteBindAddress a Serverwide variable or a Database linked variable?
14.06.2016 15:52, fabia...@itbizolutions.com.au wrote: > 1) The variable "RemoteBindAddress" in firebird.conf is a Server Wide > value, or it can be set per database in databases.conf? Server wide. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Uneven results from Round(x, 3) function
08.06.2016 11:36, m.djo...@yahoo.com wrote: > > First I thought it's because of the results' type after the different > operations, but even in cases when the result before ROUND is a DOUBLE > PRECISION (according IBExpert) the round gives different results if the > NULL in the COALESCE function comes from a table's field or if I write > it manually. COALESCE derives the resulting datatype based on input arguments: - COALESCE(NULL, 0.285) returns numeric - COALESCE(TABLE1.QTY, 0.285) returns double precision In the second case, 0.285 gets implicitly converted to double precision and may cause insignificant digits appearing in intermediate calculations: coalesce(TABLE1.QTY, 0.285) - coalesce(null, 0.285) SUBTRACT === 0.000 coalesce(col, 0.285) * 12.5 - coalesce(null, 0.285) * 12.5 SUBTRACT === -4.440892098500626e-16 This difference obviously affects the ROUND results. Dmitry
[firebird-support] Re: Where's my logic flawed?
03.06.2016 17:59, Svein Erling Tysvær wrote: > > I simplified the query to > > WITH TMP( TJENESTEENHETRESHID, TJENESTEENHETLOKAL, IMPORT_DATO ) AS > ( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO ) > FROM NPR > WHERE TJENESTEENHETRESHID > 0 > AND TJENESTEENHETLOKAL > '' > GROUP BY 1, 2 ) > select t1.tjenesteenhetreshid, t1.tjenesteenhetlokal > from tmp t1 > left join tmp t2 > on t1.tjenesteenhetreshid = t2.tjenesteenhetreshid >and t1.tjenesteenhetlokal <> t2.tjenesteenhetlokal >and t1.IMPORT_DATO <= t2.IMPORT_DATO > where t2.tjenesteenhetreshid is null No difference: the same nested loop join. > So my question was more: "Why not execute the CTE (against the large > table) once, and do the further processing against the (very small) > result of the CTE, rather than expand the CTE twice (and thus increase > the execution time from 16 minutes to about four weeks)?" Prior to v3, the engine didn't support materialized subqueries (except in sorts and merge joins). I hoped to implement such a "caching" for nested loop joins in v3, but didn't have time to succeed. Maybe in some point release. > I think I've found a completely different way to solve the particular > problem I was trying to solve, so this was more a case of me believing > Firebird behaving differently than it does. I can think of at least two options to execute this query fast, but none of them implemented in Firebird yet. It's in my personal roadmap. Dmitry
[firebird-support] Re: Where's my logic flawed?
03.06.2016 11:27, Svein Erling Tysvær wrote: > This query: > WITH TMP( TJENESTEENHETRESHID, TJENESTEENHETLOKAL, IMPORT_DATO ) AS > ( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO ) > FROM NPR > WHERE TJENESTEENHETRESHID > 0 > AND TJENESTEENHETLOKAL > '' > GROUP BY 1, 2 ), > TMP2(TJENESTEENHETRESHID, TJENESTEENHETLOKAL ) AS > ( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL >FROM TMP T >WHERE NOT EXISTS( SELECT * FROM TMP T2 > WHERE T.TJENESTEENHETRESHID = T2.TJENESTEENHETRESHID >AND T.IMPORT_DATO < T2.IMPORT_DATO ) ) > SELECT TJENESTEENHETRESHID, LIST( TJENESTEENHETLOKAL ) TJENESTEENHETLOKAL > FROM TMP2 > GROUP BY 1 > HAVING COUNT( DISTINCT TJENESTEENHETLOKAL ) = 1 > > generates this plan: > PLAN SORT ((TMP2 T2 NPR NATURAL)) > PLAN SORT ((TMP2 T NPR NATURAL)) > > NPR is a medium sized table (I'm guessing 20 million rows), but the > result of the first CTE is only 2200 rows. None of the fields in this > particular query are indexed. I tried to run the query, but decided to > terminate it after about 2 hours 40 minutes. > If I run the first CTE separately > > ( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO ) > FROM NPR > WHERE TJENESTEENHETRESHID > 0 > AND TJENESTEENHETLOKAL > '' > GROUP BY 1, 2 ) > > it takes just over 16 minutes. Putting the result of this CTE in a > temporary table and running the rest of the query against this temporary > table only takes a couple of seconds (reducing the 2200 rows to 1600). > The way I normally read plans seems to fail me this time - I don't quite > understand why NPR is mentioned twice in the plan (I assume it has to do > with my "two-level" GROUP BYs). Can anyone explain why? Or better: Does > anyone understand why the query takes so long to execute? It's slow because T2 is accessed naturally (i.e. a fullscan) for the every record in T. NOT EXISTS is kinda nested loop join (anti-join, actually), it's good only if the subquery is indexed. Otherwise, it takes 16 minutes + (2200 * 16 minutes) to execute. The question is why T2 does not use an index inside CTE. Condition T.IMPORT_DATO < T2.IMPORT_DATO cannot use an index because it's evaluated against an aggregate: IMPORT_DATO is actually MAX(NPR.IMPORT_DATO). However, condition T.TJENESTEENHETRESHID = T2.TJENESTEENHETRESHID should use an index on NPR.TJENESTEENHETRESHID, if available. But I doubt it exists, otherwise the local condition TJENESTEENHETRESHID > 0 would also be indexed in this case. So the only way to run this query fast is to ensure NPR.TJENESTEENHETRESHID is indexed (and it's the first segment, if the index is compound). Dmitry
[firebird-support] Re: FB 3.0, a few questions on a new future
03.06.2016 05:24, trsk...@yahoo.com wrote:
>
> 1) Since FB 3.0 support WireCompression, should I still use Zebedee?
No point anymore.
> 2) Is that possible to hide stored procedure/trigger codes in FB 3.0 ?
> Usually I used this statement :
>
> UPDATE RDB$TRIGGERS a set a.RDB$TRIGGER_SOURCE = NULL
> where a.RDB$TRIGGER_NAME in ('BD_1','BD_2')
Yes, this is still allowed.
Dmitry
[firebird-support] Re: FB 3.0, got problem when add field to Table with existing data
02.06.2016 07:42, trsk...@yahoo.com wrote: > > I have a table with existing data & got a problem to add field that must > have a value -> alter table Tbl1 add Fld1 int not null. What value must it have? Add a default clause to this statement. Or add the field as nullable, populate with data and then alter to NOT NULL. Dmitry
[firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?
02.06.2016 03:44, trsk...@yahoo.com wrote: > > Am I correct that maximum allowed RAM for database cache on SS FB 3.0 in > Win64 is only 2 GB? Calculated from max DefaultDbCachePages (131072) x > max Page Size (16384) = 2 GB. Correct for 32-bit builds. 64-bit builds don't have such a limit. > Setting DefaultDbCachePages more than allowed caused a crashed. Try a v3.0.1 snapshot build, IIRC it was fixed recently. Dmitry
[firebird-support] Re: Issue with Database Cache Size on FB 3.0
01.06.2016 04:16, Fabian Ernesto Chocron wrote:
>
> We are having trouble setting up the database cache size on FB 3.0
> running on Windows 2008 R2 64 bits with 32 GB ram.
>
> The problem we have is we cannot get the server to allocate the ram for
> the cache as we intend. With FB 2.54 we had the DB cache set very high,
> close to 1 GB per database, all running in RAM memory. With FB 3.0 we
> read it can allocate much more RAM to the cache, but it appears the
> server is allocating very small amount of Ram when the first user
> connects to the DB, and as we connect more users to the DB the ram
> consumption increases slowly.
What memory counters do you look at? Virtual (reserved) memory usage
should grow high with the first connect, physical (committed) memory
usage should grow together with your load, while pages are being read
from disk.
> On firebird.conf
>
> FileSystemCacheThreshold = 0
> FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB
> ram.)
These changes conflict with each other. You disable filesystem cache
with the first line and still want to use 16GB with the second line.
> On databases.conf
>
> MyTestDB = c:\Temp\MyDb.fdb
> {
> DefaultDbCachePages = 458752
> }
First of all, I'd suggest to reset FileSystemCacheSize back to zero and
set FileSystemCacheThreshold to something higher than 458752 pages (e.g.
50). Then re-test your memory usage and only then start tweaking the
filesystem cache.
BTW, don't you occasionally have page cache override at the database
level? What is gstat -h output?
Dmitry
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Searching for FB3 backup suggestions
26.05.2016 14:50, dondondonje...@yahoo.com wrote: > > "C:\Program Files\Firebird\Firebird_3_0\gbak.exe" -b -v -t -se > service_mgr employee e:\xxx.bak -user SYSDBA > works if no one is connected else i always get error "file is used by > another process". gbak.exe -b -v -t -se localhost:service_mgr employee e:\xxx.bak -user SYSDBA And all other connections should also be through localhost (or host name, or IP address). Dmitry
[firebird-support] Re: Translating Firebird.MSG - pt_BR
23.05.2016 17:22, 'Heron Vieira' wrote: > > I'd like to translate firebird.msg, but I think I hit a wall with the > size of the messages. > > So far I translated some messages by editing the file in a HEX editor, > but I think the correct way would be modifying the source code, but I've > no idea where to start. > > I searched the files on github and found nothing about firebird.msg. > > So I need some advice on how it works and what should I do. Look here: https://github.com/FirebirdSQL/firebird/tree/master/src/msgs messages2.sql is the file you need to translate. transmsgs.de_DE2.sql and transmsgs.fr_FR2.sql are older (now outdated) translation attempts. Dmitry
[firebird-support] Re: Question about few Firebird details
23.05.2016 22:32, Alexey Kovyazin wrote: > > 44. This option turn off individual savepoints. Individual savepoints are never turned off. This option disables the root (transaction-level) savepoint used to transform a rollback into a commit (by undoing all the transaction changes). Dmitry
[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
01.05.2016 19:49, trsk...@yahoo.com wrote: > > This is the Query plan from SS FB 3.0 > > PLAN (GET_STCK NATURAL) This is just a different output for select from procedure GET_STCK, v3.0 does not show all its internals as v2.5 did. You'd better compare per table performance counters (indexed/non-indexed reads) after execution. > This could be a big problem, all queries/stored procedures/triggers must > be test again for its performance. This is always required when migrating to the new major version. Dmitry
[firebird-support] Re: SELECT WITH LOCK, WAIT transaction, deadlock
29.04.2016 17:57, Dmitry Yemanov wrote: > > Did you see the same issue on v2.5 or only on v3.0? Maybe it's a > regression introduced in the new version. I'm checking the code now, > will report back if anything useful is found. I see a possible reason in the code, it needs testing. Can you send me a test case or do you prefer to get a trial build to test yourself? Please contact me off-list (firebird2 at yandex dot ru). Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: SELECT WITH LOCK, WAIT transaction, deadlock
29.04.2016 12:38, Gabor Boros wrote: >> This is true for two concurrent transactions but may fail for three or >> more concurrent transactions. > > Your words shocked me. Firebird cannot handle more than two concurrent > transactions? What is the purpose of WITH LOCK if see deadlock with and > without it? Apparently, I was wrong. My statement is correct for updates inside no-record-version + wait transactions, but WITH LOCK is implemented in a more clever way and works around this issue by refetching the last committed version and posting its dummy update on top. So update conflicts should not be possible there. Did you see the same issue on v2.5 or only on v3.0? Maybe it's a regression introduced in the new version. I'm checking the code now, will report back if anything useful is found. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: SELECT WITH LOCK, WAIT transaction, deadlock
28.04.2016 16:19, Gabor Boros wrote: > In the language reference for "isc_tpb_read_committed + isc_tpb_wait" I > see this: "Update conflict exceptions can never be raised by an explicit > lock statement in this TPB mode." This is true for two concurrent transactions but may fail for three or more concurrent transactions. Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: User access by database level in 3.0?
25.04.2016 10:40, Behlül Şimşek wrote: > > is it possible to protect our database with a user/password in FB 3.0. > if db move out the another pc, won't open without knowing correct > user/password. You may protect it with encryption. Usre/password cannot protect a publicly distributed database, but an encryption key can. Dmitry
[firebird-support] Re: Composite index - issue or not existing feature?
14.03.2016 10:41, liviuslivius wrote:
> Why?
> Index is a Tree? And if i found VALUE 2 in A key then i can fast find
> value 5 in sub key (leaf)
> You scan throught keys in A, and then in finded nodes you look for leafs
> in B
Compound index key is a single concatenated value, not two separate
ones. You may search for exact match {2, 5} or for its "substring" {2}.
You cannot get the second segment from the key as independent value.
Firebird indices are imprecise and generally you may convert value->key
but cannot key->value.
Dmitry
[firebird-support] Re: Composite index - issue or not existing feature?
14.03.2016 10:32, liviuslivius wrote: > > simple table > CREATE TABLE XXX( > A INTEGER > , B INTEGER > , CONSTRAINT UK_XXX__A__B UNIQUE(A, B) > ) > commit; > SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 > AND 60 > Select Expression > -> Filter > -> Table "XXX" as "X" Access By ID > -> Bitmap > -> Index "UK_XXX__A__B" Range Scan (lower bound: *1/2*, > upper bound: *1/2*) > As you can see only A key is used but B key should be also used. > I am missing something? Yes, you do. If the first segment is matched for non-equality, then the following segments cannot be used. Dmitry
[firebird-support] Re: FB3 and not null
11.03.2016 13:39, liviuslivius wrote: > > I see that in FB3 this was changed and before apply not null to column - > all existed data are validated. Correct. > Is three some possibility to disable this behavior? Nope. > On my big table with 910 468 710 records this operation take 72 minutes. > I am sure that all records contain value in this field and this check is > then "useless". But Firebird is not that sure. You may forget something and get inconsistent database as a result. Out of curiosity, how often do you change NULL <-> NOT NULL on such big tables? ;-) Dmitry
[firebird-support] Re: UDF install in FB3.0 RC2
09.03.2016 05:11, russ...@belding.co.nz [firebird-support] wrote: > > Adding UDFs to databases in FB 3.0 RC2 is giving an error when using any > function in the added UDF. The UDFs packaged with FB3 are OK. > > To the EMPLOYEE db packaged with FB3.0 RC2 I can add UDF fbudf.dll, > which is packaged with FB3 and its functions select values OK. I have > also installed ib_udf.dll. > > I have also installed two UDFs not packaged with FB3 into the UDF folder > and when testing (eg. "select extractyear(current_date from rdb$database > ") the engine gives error "function extractyear is not defined. module > name or entrypoint could not be found." > > Extractyear is in rfunc.dll. The other DLL with the same problem is > freeUDFLib.dll. It looks like some system problem. IIRC, rfunc.dll depends on fbclient.dll, it must be available (maybe deployed to %system%). Also, it could be a MS runtime issue, FB3 is dynamically linked to msvcr10.dll while those UDFs may have different requirements. Do you test FB3 on the same system where FB2.5 runs successfully with these UDFs or on a different system? Dmitry
