Re: Odp: [firebird-support] Readcomitted ReadOnly Transaction - Commit or Rollback?

[Carlos H. Cantu]

IMHO, RC+RO should be used mostly for long running transactions, since
its major benefit is to not block garbage collection. I usually use
them for "lookup" data.

I agree with Dmitry that constantly commiting such transactions is
kind of nonsense or misuse of its purpose, unless there is some
specific situation that could justify such behavior (and I'm not
seeing it).

Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br

DY> 01.03.2014 00:14, liviusliv...@poczta.onet.pl wrote:
>>
>>  >> IIRC, TIP is written only when RC RO txn starts, at this point it
>> records its state as committed. Dmitry Nice, but it is written to disk
>> and maybe it shouldn't?

DY> Maybe. Although I'm not 100% sure this is doable.

DY> And anyway, I feel a big problem for applications committing RC RO txns
DY> so much often that it could possibly affect the performance. This sounds
DY> like a feature misuse.


DY> Dmitry

Re: Odp: [firebird-support] Readcomitted ReadOnly Transaction - Commit or Rollback?

[Dmitry Yemanov]

01.03.2014 00:14, liviusliv...@poczta.onet.pl wrote:
>
>  >> IIRC, TIP is written only when RC RO txn starts, at this point it
> records its state as committed. Dmitry Nice, but it is written to disk
> and maybe it shouldn't?

Maybe. Although I'm not 100% sure this is doable.

And anyway, I feel a big problem for applications committing RC RO txns 
so much often that it could possibly affect the performance. This sounds 
like a feature misuse.


Dmitry

Odp: [firebird-support] Readcomitted ReadOnly Transaction - Commit or Rollback?

[liviusliv...@poczta.onet.pl]

>> IIRC, TIP is written only when RC RO txn starts, at this point it 
records its state as committed.
Dmitry

Nice, but it is written to disk and maybe it shouldn't?

Regards,
Karol Bieniaszewski

Re: [firebird-support] Find all records on all tables that violate constraints?

[Kjell Rilbe]

Den 2014-02-28 09:41 skrev Kjell Rilbe såhär:
>
> Den 2014-02-28 08:42 skrev Svein Erling Tysvær såhär:
> >
> > Reinventing the wheel can sometimes be fun, Kjell. I would expect this
> > statement
> >
> > SELECT list('SELECT ''' || TRIM(RDB$RELATION_NAME) ||'''
> > RELATION_NAME, ''' || TRIM(RDB$FIELD_NAME) ||
> > ''' FIELD_NAME, COUNT(*) FROM ' || TRIM(RDB$RELATION_NAME) ||
> > ' WHERE ' || TRIM(RDB$FIELD_NAME) || '|| IS NULL GROUP BY 1, 2 ', '
> > UNION ')
> > FROM RDB$RELATION_FIELDS
> > WHERE RDB$NULL_FLAG = 1
> >
> > to return an SQL statement that you can execute to find which
> > combination of tables and fields contains NULL values (though it will
> > take time since it has to avoid using indexes, at least I don't think
> > you'll find NULLs if you use indexes).
> >
>

This is what I ended up with, issuing one select per table to reduce 
table scans:

select 'select ''' || trim(RDB$RELATION_NAME) || ''' "Table", count(*) 
"Count" from "' || trim(RDB$RELATION_NAME) ||
'" where ' || list('"' || trim(RDB$FIELD_NAME) || '" ||  is null', ' 
or ') || ';'
from RDB$RELATION_FIELDS
where RDB$NULL_FLAG = 1
group by trim(RDB$RELATION_NAME);

With over 200 tables I manually grouped them in groups of 25 with union 
and an outer select to only return rows with count <> 0.

Thanks for the great starting point!
Kjell

[firebird-support] isql connect database using TCP/IP protocol, After run a long time SQL Statement can't go back to SQL command prompt

[deke_xie]

I have a database 40GB, a table Name is T1 with 70 columns and 100 records, 
I run isql command:
connect database 'localhost:/db/db.fdb' user 'sysdba' password 'masterkey';
update set T1 set Column1=0;
the 2nd Statement run with IBExpert is ok, waste time 2 minute. In this case, 
after 2 minute(and more time), isql can't get back to SQL> prompt.
I change 1st Statement to "connect database '/db/db.fdb' user 'sysdba' password 
'masterkey';" everything is ok. I test the db without automatic garbage 
collection, this problem same: gfix -sweep /db/db.fdb
 gfix -h 0 /db/db.fdb isql SQL>connect database 'localhost:/db/db.fdb' user 
'sysdba' password 'masterkey'; SQL>update set T1 set Column1=0; then can't back 
to "SQL>" 

Re: [firebird-support] Find all records on all tables that violate constraints?

[Kjell Rilbe]

Den 2014-02-28 08:42 skrev Svein Erling Tysvær såhär:
>
> Reinventing the wheel can sometimes be fun, Kjell. I would expect this 
> statement
>
> SELECT list('SELECT ''' || TRIM(RDB$RELATION_NAME) ||''' 
> RELATION_NAME, ''' || TRIM(RDB$FIELD_NAME) ||
> ''' FIELD_NAME, COUNT(*) FROM ' || TRIM(RDB$RELATION_NAME) ||
> ' WHERE ' || TRIM(RDB$FIELD_NAME) || '|| IS NULL GROUP BY 1, 2 ', '
> UNION ')
> FROM RDB$RELATION_FIELDS
> WHERE RDB$NULL_FLAG = 1
>
> to return an SQL statement that you can execute to find which 
> combination of tables and fields contains NULL values (though it will 
> take time since it has to avoid using indexes, at least I don't think 
> you'll find NULLs if you use indexes).
>

Nice one! Thanks! After fixing some quotes (mixed case table names etc) 
it seems to work nicely. Got 910 "cases"... Will have to split them up I 
think - too many unions otherwise.

Regards,
Kjell