maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

Yes, someday, hopefully, MariaDB can get transactional DDL.

I want to make a good case for a pluggable parser, so I am going to briefly
describe the tools I write and how they could benefit from one.

I need a pluggable parser or at the least, table functions(which can return
more than one column) for Shard-Query, which provides transparent sharding,
parallel query, and window functions. Shard-Query puts a lot of work into
rewriting a query.  I does things like semijoin materialization, after all
how else could a semijoin work on a sharded data set?  Shard-Query is much
more powerful than Spider for reads, though spider is probably better for
writes.  There is no reason they couldn't work together.  Shard-Query can
be called from a stored procedure wrapping gearman UDF, but I would MUCH
prefer if I could implement the logic in the server for parallel query.
This is something that MariaDB really needs and it would set it apart from
MySQL in a major way.  Much of the work on Shard-Query was done in
cooperation with an employee of the Ericsson corporation, which was (and
still is to my knowledge) used for analysis and billing of cell phone
charges.  One of the changes for ericsson was pluggable aggregate functions
like percentile.  Another prerequisite for putting parallel query in the
database is the unified interface for running SQL in the server (there is
an MDEV for it).  Basically I envision a parser plugin which would rewrite
the query internally to each partition, each query being an insert into one
temporary table.  Once all inserts complete the query will be rewritten to
use the temp table and sent through the normal query pipeline where the
results will be sent to the end user, and a deinit function will happen to
drop the temporary table and free up any memory that was allocated, etc.

I also need the pluggable parser for Flexviews, which implements
incrementally refreshable materialized views.  It includes FlexCDC, a
complete change data capture tool that supports ALTER TABLE and that
supports plugins, so you can replicate to other systems or send rows to a
message queue. FlexCDC processes the binary log and inserts data into log
tables.  The log tables are then used to refresh the views.  Joins and all
major aggregate functions are supported and it is in use in production in
major internet properties.  Flexviews needs a pluggable parser to rewrite
queries to access the views transparent.  FlexCDC could be rewritten as a
daemon plugin which acts as a replication slave inside the database.  The
pluggable parser could support CREATE MATERIALIZED VIEW.

I think these are good cases for a pluggable parser.  Let me know if you
have any questions or ideas.

Btw, there are KB articles on both of the tools.  I'm going to work up a
tutorial for both soon.  Let me know if you have questions.

You can find my tools here:
http://github.com/greenlion/swanhart-tools

Also, you might find this UDF useful, or a comical abuse of a plugin, or
both:
http://github.com/greenlion/FastBit_UDF


--Justin

On Tue, Mar 3, 2015 at 7:16 AM, Federico Razzoli <federico_raz@xxxxxxxx>
wrote:

> I totally agree, it's an important feature.
> As a side note, when changing the logic of a database we may need to
> modify more than one object (tables, procedures, views, triggers...). In
> those cases, it would be great to be able to make all the changes
> atomically. But I suppose that this would require a lot of work...
>
> Federico
>
>
>
> --------------------------------------------
> Mar 3/3/15, Justin Swanhart <greenlion@xxxxxxxxx> ha scritto:
>
>  Oggetto: Re: [Maria-discuss] stored programs
>  A: "Peter Laursen" <peter_laursen@xxxxxxxxxx>
>  Cc: "maria-discuss@xxxxxxxxxxxxxxxxxxx" <
> maria-discuss@xxxxxxxxxxxxxxxxxxx>
>  Data: Martedì 3 marzo 2015, 14:30
>
>  After
>  you debug you proc you kinda have to replace the non buggy
>  one.  In production.  While people are using it.  Unless
>  you version your whole code base and push out a new version
>  to use a new name.
>
>  Sent
>  from my iPhone
>  On Mar 3,
>  2015, at 6:22 AM, Peter Laursen <peter_laursen@xxxxxxxxxx>
>  wrote:
>
>  If this "You can't drop a
>  busy proc in production to replace it" was for me,
>   then note the passage from my blog:
>  "you can add a
>  IN-parameter (debug:
>  integer) to a Stored
>  Procedure paramer-list and CALL
>  mysp(….,0|1) what would then
>  control if the stored program should enter or bypass
>  debugging code when executing". This is the best
>  workaround I've found.
>  On Tue, Mar 3, 2015 at 2:17
>  PM, Justin Swanhart <greenlion@xxxxxxxxx>
>  wrote:
>  You
>  can't drop a busy proc in production to replace it.
>  Many users would get an error.  This is the same reason
>  views have had CoR for so long. So yes it is a big deal!
>
>
>
>  Anyway, what I want most are Antony Curtis' stored proc
>  / parser changes (mdev 820 if I'm not mistaken).  I am
>  especially interested in table functions and external stored
>  routines.
>
>
>
>  I'd also like to discuss window functions too.
>  I've implemented them in shard-query and have ideas
>  about how to implement them in the server, but pluggable
>  parser would be really useful here.
>
>
>
>  --Justin
>
>
>
>  Sent from my iPhone
>
>
>
>  > On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@xxxxxxxxxxx>
>  wrote:
>
>  >
>
>  > Hi, Federico!
>
>  >
>
>  >> On Mar 03, Federico Razzoli wrote:
>
>  >> Reading 10.0.3 release notes:
>
>  >>
>
>  >> https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
>
>  >>
>
>  >> I see that IF EXISTS, IF NOT EXISTS and OR REPLACE
>  are now almost
>
>  >> consistent. "Almost" means that... OR
>  REPLACE still doesn't apply to
>
>  >> stored procedures, functions, triggers, events.
>
>  >
>
>  > Support for events is already pushed (albeit after
>  10.1.3).
>
>  > Support for triggers will be pushed any day now
>  (already reviewed and
>
>  > approved, so there's no more work left on it). I
>  suppose that stored
>
>  > procedures and functions will follow soon.
>
>  >
>
>  > This was a GSoC 2014 project that added support for
>  these clauses to
>
>  > *all* objects. It's just being pushed piecewise,
>  object by object.
>
>  >
>
>  >> Recently, during a public session, a PostgreSQL
>  user asked me if
>
>  >> MariaDB supports stored procedures - in his
>  opinion, MySQL doesn't, no
>
>  >> matter what the manual says. Unfortunately my
>  answer was that MariaDB
>
>  >> support for stored procedure is the same as MySQL
>  ("so the answer is
>
>  >> no", he said).
>
>  >
>
>  > I don't understand what exactly missing feature
>  that user had in mind.
>
>  > It couldn't have been "CREATE OR
>  REPLACE", this seems so minor.
>
>  > Or was it?
>
>  >
>
>  > Regards,
>
>  > Sergei
>
>  >
>
>  > _______________________________________________
>
>  > Mailing list: https://launchpad.net/~maria-discuss
>
>  > Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>
>  > Unsubscribe : https://launchpad.net/~maria-discuss
>
>  > More help   : https://help.launchpad.net/ListHelp
>
>
>
>  _______________________________________________
>
>  Mailing list: https://launchpad.net/~maria-discuss
>
>  Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>
>  Unsubscribe : https://launchpad.net/~maria-discuss
>
>  More help   : https://help.launchpad.net/ListHelp
>
>
>
>
>  -----Segue allegato-----
>
>  _______________________________________________
>  Mailing list: https://launchpad.net/~maria-discuss
>  Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>  Unsubscribe : https://launchpad.net/~maria-discuss
>  More help   : https://help.launchpad.net/ListHelp
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>