Re: Integration with Access Method interface - Mailing list pgsql-hackers

Alice Lottini wrote:
> 
> Our task is to implement FPGrowth (an algorithm for
> extracting association rules for data mining purposes)
> as a C programme and to integrate it at low level into
> Postgres. We are strictly required not to pass through
> the SQL layer and to bypass even the optimiser layer,
> getting the data out of tables directly with the
> Access Methods.

You have no way to tell the database system what you currently access.
So the data you read from the disk is allowed to be invalid and even
corrupted as the DB system sees fit (it needs to know how to return into
a consistent state, but it doesn not need to tell you how it thinks to
accomplish that task or when it will be in the mood to do so).

The only way you can ensure consistent reads from PostgreSQL data files
is by shutting down the postmaster first. So your "digger" could be some
sort of standalone backend that can only work while the database system
is down.

> 
> The reason for this is that all the existing tools for
> data mining obtain data either from flat files or from
> dbms, through SQL queries; since the amount of data
> involved is usually extremely huge, this high level
> integration results in rather poor performances.
> Furthermore, FPGrowth is a recursive algorithm and the
> data structures it needs (FPTree's) are likely not to
> fit into memory.

But the single nodes of that FPTree still fit, no?

> In order to partly solve such problems, we've studied
> an optimised version of the algorithm as well as a
> partitioning technique for the data structures so that
> they can be stored on the disk instead of having to be
> held into memory.
> 
> Now we must enable our programme to access the data
> directly from the table so that the FPtree can be
> built and, after having partitioned it according to
> our strategy, stored on the disk blocks (each node of
> our tree should be a tuple).
> 
> We'd like to know which is the most suitable way for
> integrating our algorithm into the server at the
> access method level. If it is not possible simply to
> invoke the access methods from an external programme,
> what could be an alternative? Maybe making the whole
> procedure a user defined function such as the ones in
> contrib is the most viable way...
> Any suggestion would be greatly appreciated.
> Thanks in advance!
> Best regards, alice and lorena

I think we know much too little about your algorithms to give you any
advice yet. What it looks like to me is that you might have wanted very
substantial, in depth, relational database knowledge and experience a
little earlier in your project. Just that everyone else using SQL failed
so far doesn't mean that these guy's had the brightest database hotshots
on their teams. Also, PostgreSQL's extensibility might offer a few
possible paths inside the "supported" boundaries of the backends
streetmap. We just need to know more.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #