Re: [Bacula-devel] PostgreSQL issues - GROUP BY issues

[Dan L. <da...@la...>]

On 30 Dec 2003 at 14:55, Kern Sibbald wrote:

> As long as it returns the right answer, I'm happy. They do, however, do
> a number of things differently. I am particularly annoyed at their
> non-standard concatenation (concat(x,y) instead of ||). It makes my life
> much more difficult especially in files such as query.sql where it is
> not possible to #ifdef.


|| in PostgreSQL?  That works

# select 'a' || 'a';
 ?column?
----------
 aa
(1 row)


> > > > Similarly, for src/dird/sql_cmds.c.  See the diff at 
> > > > http://cvs.sourceforge.net/viewcvs.py/bacula/bacula/src/dird/sql_cmds.
> > > > c?r1=1.27&r2=1.28
> > > > 
> > > > The query is:
> > > > 
> > > > INSERT INTO temp1
> > > >   SELECT Job.JobId, JobTdate
> > > >     FROM Client,
> > > >          Job,
> > > >          JobMedia,
> > > >          Media
> > > >    WHERE Client.ClientId  = %u
> > > >      AND Job.ClientId     = %u
> > > >      AND Job.StartTime    < '%s'
> > > >      AND Level            = 'F'
> > > >      AND JobStatus        = 'T'
> > > >      AND JobMedia.JobId   = Job.JobId
> > > >      AND JobMedia.MediaId = Media.MediaId
> > > >      AND Job.FileSetId    = %u
> > > > ORDER BY Job.JobTDate DESC 
> > > >    LIMIT 1
> > > > 
> > > > Given that this query has a limit 1, it can only every return 1 row.  
> > > > I think the objective is to obtain the date of the last full backup.  
> > > > Please confirm.
> > > 
> > > Yes, this is correct.  Unless you can "optimize" it, the above is
> > > probably OK as is.
> > 
> > OK, next question: If the above puts only one row into temp1, why do 
> > we need temp1?  Oh, I think I see..  A look at src/dird/sql_cmds.c 
> > for references to temp1 shows that "uar_full" uses it:
> 
> Well, it gets the first full backup. Then a wee bit later in the same
> code, I run a query based on what is returned in that row, in particular
> the JobTdate (my own format guaranteed compatible across all SQLs), and
> insert all the following Differential and Incremental backups into
> temp1. Then I pull them out one at a time and load them into the memory
> tree.
> 
> If you have any magic way to eliminate the temp1 table, great, but I
> prefer not to read all the info directly into memory.

I'd rather not change it actually.  Just digging for answers.  I'll 
leave it alone now.

> > char *uar_full =
> >    "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,"
> >    " Job.ClientId,Job.Level,Job.JobFiles,"
> >    " 
> > StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime "
> >    "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId "
> >    "AND Level='F' AND JobStatus='T' "
> >    "AND JobMedia.JobId=Job.JobId "
> >    "AND JobMedia.MediaId=Media.MediaId";
> > 
> > 
> > It is joining temp1 with Job and JobMedia.  But if there's only one 
> > row in temp1, we can supply the JobId directly into the query and 
> > eliminate the join.
> > 
> > Did I miss something?
> 
> As noted above, I add to the table.
> 
> > 
> > 
> > > > This function is used by select_backups_before_date() in 
> > > > src/dird/ua_restore.c (line 718).
> > 
> > Function?  What function.  I should have said query.
> 
> Oh, OK I should have guessed.
> 
> Thanks again for digging into this. 
> 
> By the way, do you want to take a crack at re-writing 
> <bacula-source>/doc/html-manual/postgresql.wml?  It is a copy of the
> mysql version with MySQL changed to PostgreSQL everywhere -- pretty ugly
> for the moment, but at least it is a base for what needs to be done. 
> 
> In addition, even mysql.wml needs updating to add the new database
> creation scripts, ...

I can have a look at that later.

> I'm really pleased with the results of your PostgreSQL addition. It has
> helped improve the SQL and made a fantastic addition to Bacula. I
> suspect that within a short time, PostgreSQL will be the most widely
> used Bacula database.

I hope so.  There are optmiziations we may be able to make with the 
PostgreSQL version, but that would involve the use of Stored 
Procedures and triggers. That is some way down the track.  We'll need 
to change the Bacula structure first.  That will make it easier to 
have different SQL for different databases.
-- 
Dan Langille : http://www.langille.org/