Hello,
On 2/14/2006 9:06 PM, Magnus Hagander wrote:
When I show the processlist several times in a row it is nearly
always SELECTing some filename in the Filename table.
That's expected - it selects to find out if the filename has
to be inserted. By the way - is there a portable way of
having unique file names, and, in case of inserting a
duplicate one, failing in a way that allows Bacula to process
the FilenameId? This might save some time.
Unfortunately, I suspect stored procedures won't work because
of major differences between SQLite, PostgreSQL and MySQL.
If there was a way to do it in a stored proc, that would probably be
fine even if the syntax of the proc differed - because the proc would be
part of the schema, just like table and index definitions. As long as
the call syntax would be the same.
However, I don't beleive sqlite does stored procs, and I doubt it's in
the plans - doesn't really make sense for an embedded db.
Yes, that's about what I meant with "major differences" between the RDBMs...
I think the standard way of doing it is using a SAVEPOINT. This is
supported by postgresql and mysql 4 (with innodb, of course). Sqlite
doesn't, it seems.
So, even without knowing about SAVEPOINTs, we're stuck in the same
situation.
If you're thinking of doing the INSERT->SELECT (try insert, do select if
it fails) for filenames, I think you're on a bad track though. That's
only good if you will in most cases do INSERT and just in some cases to
SELECT. Looking at my own database, it certainly looks different - once
a full backup is through, most files never change their names so they
wouldn't need INSERTs.
Right, and that's the reason I wouldn't suggest this as a solution to
performance problems.
The best scheme would probably be SELECT->INSERT->SELECT (second select
if the insert failed). That you can do with SAVEPOINTs (around the
INSERT).
Why would you need anything - SAVEPOINTs, in this case - around inserts,
or rather what *is* a savepoint?
Another thought - does the filename *HAVE* to be unique?
Difficult question, at least for me. This would be something best
discussed with Kern and perhaps Dan, I guess...
The basic idea is appealing, though.
You can gain a
lot of concurrency if you say "I want to use the existing one in 99.99%
of the cases, but I don't care about reaching 100%". Then you can do
SELECT->INSERT and the INSERT will *always* succeed. In case a
concurrent proces sinserted the same name, you get two ids for the same
name. You could either live with that, or clean it up at the end of the
job, perhaps?
Might be some task for dbcheck.
(or is this perhaps already done today? Since there is no unique
constraint on it?)
I suppose the reason for not having constarints wherever possible is
that older versions of MySQL, AFAIK, didn't support those.
(Sorry if I'm ranting about things that are completely out of the scope
you were talking about ;-))
Nothing to worry about - you gave just the answers to my questions :-)
Arno
//Magnus
--
IT-Service Lehmann [EMAIL PROTECTED]
Arno Lehmann http://www.its-lehmann.de
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
