Thread: new aggregate functions v1
Dear patchers,
please find attached a small patch for adding new aggregate functions:
(1) boolean-and and boolean-or aggregates named bool_and and bool_or.
they correspond to standard sql every and some/any aggregates.
they do not have the right name as there is a problem with
the standard and the parser for some/any.
(2) bitwise integer aggregates named bit_and and bit_or for
int2, int4, int8 and bit types. They are not standard,
however they exist in other db (eg mysql), and I needed them
for some other stuff.
The patch adds:
- 3 new functions for boolean aggregates in src/backed/utils/adt/bool.c,
src/include/utils/builtins.h and src/include/catalog/pg_proc.h
- new aggregates declared in src/include/catalog/pg_proc.h and
src/include/catalog/pg_aggregate.h
- some documentation and validation about these new aggregates.
It also updates the catalog version. It validates for me.
Have a nice day,
--
Fabien Coelho - coelho@cri.ensmp.fr
Attachment
On Sat, May 01, 2004 at 04:21:21PM +0200, Fabien COELHO wrote: > (2) bitwise integer aggregates named bit_and and bit_or for > int2, int4, int8 and bit types. They are not standard, > however they exist in other db (eg mysql), and I needed them > for some other stuff. I'm sure people won't like to add functions just because "some other DB has them". Maybe we could take this kind of "compatibility functions" all together into some gborg module and let people install that if they want closer compatibility. So we could have "Mysql compatible functions", "Oracle compatible functions", etc. Thus the backend would be free from this stuff, and people who needs it just installs the whole package. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La realidad se compone de muchos sueños, todos ellos diferentes, pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)
Dear Alvaro, > > (2) bitwise integer aggregates named bit_and and bit_or for > > int2, int4, int8 and bit types. They are not standard, > > however they exist in other db (eg mysql), and I needed them > > for some other stuff. > > I'm sure people won't like to add functions just because "some other DB > has them". I develop them because I NEEDED them, NOT because they are available in mysql and I would want compatibility. I needed them for some queries over pg_catalog, as acl are encoded as bitfields. So the addition is about functionnality, not compatibility. The argument about mysql is rather to show that other people in the world found these functions also useful, and to justify the name I chose. You may also notice that the impact in close to void, there are two lines added for each of these bit_* functions. I'm not going to develop an external package for 16 lines of code. -- Fabien Coelho - coelho@cri.ensmp.fr
> (1) boolean-and and boolean-or aggregates named bool_and and bool_or. > they correspond to standard sql every and some/any aggregates. They do not, sorry. I'll resubmit. -- Fabien Coelho
Fabien COELHO wrote: > I develop them because I NEEDED them, NOT because they are available > in mysql and I would want compatibility. I needed them for some > queries over pg_catalog, as acl are encoded as bitfields. We are not going to accept any functions that are primarily intended to access the internals aclitem data. You need to think of a high-level description of your problem and implement functions for that.
Dear Peter, > We are not going to accept any functions that are primarily intended to > access the internals aclitem data. You need to think of a high-level > description of your problem and implement functions for that. I already have what I juge a "high level description" of the problem, thanks. It nevertheless still use the acl bitfield for representings granted rights. I don't think the bitfield is a bad idea to represent a set of rights, as they are easy to combine (say, with a bit and aggregate to compute effective rights for instance). My problem rather is to deal with arrays in a relationnal query. It is not easy to query things when data are not normalized. So I will still need bit_* functions. I can implement them outside for sure, but I really think that it does cost nothing to propose them as a default (only the declaration is needed), and I know I'm not the first one to look for them and being desappointed not to find them (there was a discussion about these very functions last month on some other pg list). Although It is very interesting to learn about how to add aggregates in pg, maybe all users around don't have the time and the motivation for that. -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO wrote: > Dear Alvaro, > >> > (2) bitwise integer aggregates named bit_and and bit_or for >> > int2, int4, int8 and bit types. They are not standard, >> > however they exist in other db (eg mysql), and I needed them >> > for some other stuff. >> >> I'm sure people won't like to add functions just because "some other DB >> has them". > > I develop them because I NEEDED them, NOT because they are available > in mysql and I would want compatibility. I needed them for some queries > over pg_catalog, as acl are encoded as bitfields. > > So the addition is about functionnality, not compatibility. > > The argument about mysql is rather to show that other people in the world > found these functions also useful, and to justify the name I chose. The argument about MySQL (tm) is IMHO a good one, because in contrast to MySQL (tm), PostgreSQL is designed and implemented as a catalog based system. We carry the performance burden we always got blamed for (unjustified because MySQL (tm) is slow as hell anyway) for a reason, and the reason is flexibility. In MySQL (tm) you don't have a chance, the functionality you want must be the functionality linked into the server. With PostgreSQL you can add the functionality you need where and when you need it. > > You may also notice that the impact in close to void, there are two lines > added for each of these bit_* functions. I'm not going to develop an > external package for 16 lines of code. > Maybe I'm missing something, but what is wrong with installing these functions on demand as user defined functions? After all, even PL/pgSQL is still an external package ... sort of at least. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Dear Jan, > > You may also notice that the impact in close to void, there are two lines > > added for each of these bit_* functions. I'm not going to develop an > > external package for 16 lines of code. > > Maybe I'm missing something, but what is wrong with installing these > functions on demand as user defined functions? After all, even PL/pgSQL > is still an external package ... sort of at least. By external, I mean with a separate distribution, which is the result if the addition is rejected. I could try to submit to contrib, but the current status is that part is that it should be "reduced" or even disappear, so I'm no that confident that it would be accepted either. If you are a user and you need a bit_*, then you look in the doc. In the aggregate function documentation, you'll find no such function, so then you turn to the "how to add an aggregate function". Things there are very interesting, but it is a little bit overshot for a simple basic aggregate. Have a nice day, -- Fabien Coelho - coelho@cri.ensmp.fr
Where are on this? Without a user-visible way of seeing the acl values, is this still useful? --------------------------------------------------------------------------- Fabien COELHO wrote: > > Dear patchers, > > please find attached a small patch for adding new aggregate functions: > > (1) boolean-and and boolean-or aggregates named bool_and and bool_or. > they correspond to standard sql every and some/any aggregates. > they do not have the right name as there is a problem with > the standard and the parser for some/any. > > (2) bitwise integer aggregates named bit_and and bit_or for > int2, int4, int8 and bit types. They are not standard, > however they exist in other db (eg mysql), and I needed them > for some other stuff. > > > The patch adds: > > - 3 new functions for boolean aggregates in src/backed/utils/adt/bool.c, > src/include/utils/builtins.h and src/include/catalog/pg_proc.h > > - new aggregates declared in src/include/catalog/pg_proc.h and > src/include/catalog/pg_aggregate.h > > - some documentation and validation about these new aggregates. > > It also updates the catalog version. It validates for me. > > Have a nice day, > > -- > Fabien Coelho - coelho@cri.ensmp.fr Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
What does this patch have to do with ACLs? Wasn't that Fabien's previous patch? Chris Bruce Momjian wrote: > Where are on this? Without a user-visible way of seeing the acl values, > is this still useful? > > --------------------------------------------------------------------------- > > Fabien COELHO wrote: > >>Dear patchers, >> >>please find attached a small patch for adding new aggregate functions: >> >>(1) boolean-and and boolean-or aggregates named bool_and and bool_or. >> they correspond to standard sql every and some/any aggregates. >> they do not have the right name as there is a problem with >> the standard and the parser for some/any. >> >>(2) bitwise integer aggregates named bit_and and bit_or for >> int2, int4, int8 and bit types. They are not standard, >> however they exist in other db (eg mysql), and I needed them >> for some other stuff. >> >> >>The patch adds: >> >>- 3 new functions for boolean aggregates in src/backed/utils/adt/bool.c, >> src/include/utils/builtins.h and src/include/catalog/pg_proc.h >> >>- new aggregates declared in src/include/catalog/pg_proc.h and >> src/include/catalog/pg_aggregate.h >> >>- some documentation and validation about these new aggregates. >> >>It also updates the catalog version. It validates for me. >> >>Have a nice day, >> >>-- >>Fabien Coelho - coelho@cri.ensmp.fr > > > Content-Description: > > [ Attachment, skipping... ] > > >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html > >
Christopher Kings-Lynne wrote: > What does this patch have to do with ACLs? Wasn't that Fabien's > previous patch? I thought it was used to read them somehow. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Dear Bruce, > Christopher Kings-Lynne wrote: > > What does this patch have to do with ACLs? Wasn't that Fabien's > > previous patch? > > I thought it was used to read them somehow. Yes, as for bit_* aggregates are concerned. However I still need them, and they also have been asked for in the past. This functionnality cost very little. Rejecting aclitem accessors just means that I'll have to install them with my package, hence the current discussion about providing all includes files and so by default. As for bool_and and bool_or are to provide standard functionnalities. I did not need them, but I saw them in the standard (every/any/some), thus I developed them next to the one I needed. -- Fabien Coelho - coelho@cri.ensmp.fr
Agreed, these seem to be of general interest and have been requested in
the past. I will clean up the docs a little.
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Fabien COELHO wrote:
>
> Dear patchers,
>
> please find attached a small patch for adding new aggregate functions:
>
> (1) boolean-and and boolean-or aggregates named bool_and and bool_or.
> they correspond to standard sql every and some/any aggregates.
> they do not have the right name as there is a problem with
> the standard and the parser for some/any.
>
> (2) bitwise integer aggregates named bit_and and bit_or for
> int2, int4, int8 and bit types. They are not standard,
> however they exist in other db (eg mysql), and I needed them
> for some other stuff.
>
>
> The patch adds:
>
> - 3 new functions for boolean aggregates in src/backed/utils/adt/bool.c,
> src/include/utils/builtins.h and src/include/catalog/pg_proc.h
>
> - new aggregates declared in src/include/catalog/pg_proc.h and
> src/include/catalog/pg_aggregate.h
>
> - some documentation and validation about these new aggregates.
>
> It also updates the catalog version. It validates for me.
>
> Have a nice day,
>
> --
> Fabien Coelho - coelho@cri.ensmp.fr
Content-Description:
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Dear Bruce,
> Agreed, these seem to be of general interest and have been requested in
> the past. I will clean up the docs a little.
Please find attached a new version to address Neil's comments.
- add "every" anyway, next to "bool_and".
- cleaner source and comments.
I also added more regression tests, including the added "EVERY" aggregate.
It DOES NOT validate for me, as errors and rules are broken in current
head:
undefined symbol: pg_strcasecmp
However the aggregate part works fine.
Have a nice day,
--
Fabien Coelho - coelho@cri.ensmp.fr
Attachment
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I will try to apply it within the next 48 hours.
This patch seems to still be under active discussion. Would you refrain
from giving the impression that it's going to be applied as-is?
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I will try to apply it within the next 48 hours. > > This patch seems to still be under active discussion. Would you refrain > from giving the impression that it's going to be applied as-is? He submitted a new version based on Neil's comments, and barring any other comments, it will be applied in 24-48 hours. I haven't seen anything that would meke that conclusion invalid. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Fabien COELHO wrote: > > Dear Bruce, > > > Agreed, these seem to be of general interest and have been requested in > > the past. I will clean up the docs a little. > > Please find attached a new version to address Neil's comments. > - add "every" anyway, next to "bool_and". > - cleaner source and comments. > > I also added more regression tests, including the added "EVERY" aggregate. > > It DOES NOT validate for me, as errors and rules are broken in current > head: > undefined symbol: pg_strcasecmp > > However the aggregate part works fine. > > Have a nice day, > > -- > Fabien Coelho - coelho@cri.ensmp.fr Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073