Thread: array_agg for 8.3

Hi,

Can anyone comment on the practicality of using the code for array_agg
from the dev repos, file src/backend/utils/adt/array_userfuncs.c in 8.3 as
a user defined function? It looks like this code was recently added, Nov
13th/14th. There are two functions, array_agg_transfn and
array_agg_finalfn, but I'm not clear how to use them. The idea would be to
compile and load them like as any other user-defined C function in 8.3. I
was informed that directly trying to use these functions in 8.3 would not
work. I realise this funcionality will be available in 8.4, but I don't
want to wait.

If anyone has working code along these lines, please send it to me. Also,
please CC me on any reply. Thanks.

                                                     Regards, Faheem Mitha.

On Sat, 2009-01-17 at 02:09 -0500, Faheem Mitha wrote:
> Hi,
>
> Can anyone comment on the practicality of using the code for array_agg
> from the dev repos, file src/backend/utils/adt/array_userfuncs.c in 8.3 as
> a user defined function? It looks like this code was recently added, Nov
> 13th/14th. There are two functions, array_agg_transfn and
> array_agg_finalfn, but I'm not clear how to use them. The idea would be to
> compile and load them like as any other user-defined C function in 8.3. I
> was informed that directly trying to use these functions in 8.3 would not
> work. I realise this funcionality will be available in 8.4, but I don't
> want to wait.
>
> If anyone has working code along these lines, please send it to me. Also,
> please CC me on any reply. Thanks.


Hi,

I wrote a module that will include these functions in 8.3.

The reason it's not easy to just make them a normal UDF is because the
state type is "internal", and that's not allowed using regular SQL. I
got around this by updating the catalogs directly. I believe "internal"
is safe here because the state still uses one palloc() chunk (and it
matches the commit to 8.4, anyway).

Code review welcome.

Regards,
    Jeff Davis

Hi Jeff,

On Sun, 18 Jan 2009, Jeff Davis wrote:

> On Sun, 2009-01-18 at 16:52 -0500, Faheem Mitha wrote:
>> Hi Jeff,
>>
>> When I try to run array_agg.sql inside psql I get
>>
>> btsnp_test=# \i '/tmp/array_agg/array_agg.sql'
>> BEGIN
>> psql:/tmp/array_agg/array_agg.sql:5: ERROR:  could not access file
>> "$libdir/array_agg": No such file or directory
>
> What that means is that it's not really installed into the global
> postgresql instance. What did you do to install it?

I haven't installed it anywhere. It is trying to install to the system,
which is a no-no.

faheem@orwell:/tmp/array_agg$ make install
mkdir -p -- /usr/share/postgresql/8.3/contrib
mkdir: cannot create directory `/usr/share/postgresql/8.3/contrib':
Permission denied
make: *** [installdirs] Error 1

In any case, I don't have admin permissions on the machine I'm trying to
install it to.

I replaced '$libdir/array_agg' in the following text by the current
location of the shared library on the machine, namely
'/tmp/array_agg/array_agg', since the shared library file is
/tmp/array_agg/array_agg.so. From the documentation, it sounds like
'/tmp/array_agg/array_agg.so' would also work.

"CREATE OR REPLACE FUNCTION UNNEST(ANYARRAY) RETURNS SETOF anyelement
   AS '$libdir/array_agg','array_unnest' LANGUAGE 'C' IMMUTABLE;"

This appears to work. I get

btsnp_test=# \i array_agg.sql
BEGIN
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE AGGREGATE
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
COMMIT

The test code you have in README now runs. Is that Ok? If there is a
better approach to this, please let me know.

> Make sure you have pg_config in your PATH environment variable, change
> directory to /tmp/array_agg, and then run "make install". That should
> install it in the global postgresql instance, and then you can run the
> SQL file to install it in the specific database.

Thanks very much for your help.
                                                          Regards, Faheem.

************************************************************************
array_agg.sql
************************************************************************

BEGIN;

CREATE OR REPLACE FUNCTION UNNEST(ANYARRAY) RETURNS SETOF anyelement
   AS '$libdir/array_agg','array_unnest' LANGUAGE 'C' IMMUTABLE;
CREATE OR REPLACE FUNCTION ARRAY_AGG_TRANSFN(INT, ANYELEMENT) RETURNS INT
   AS '$libdir/array_agg' LANGUAGE 'C' IMMUTABLE;
CREATE OR REPLACE FUNCTION ARRAY_AGG_FINALFN(ANYELEMENT) RETURNS ANYARRAY
   AS '$libdir/array_agg' LANGUAGE 'C' IMMUTABLE;

CREATE AGGREGATE ARRAY_AGG(anyelement) (
   SFUNC     = ARRAY_AGG_TRANSFN,
   STYPE     = INT,
   FINALFUNC = ARRAY_AGG_FINALFN
);

--
-- We need to properly set the state type for array_agg to be
--   "internal", but that's impossible with regular SQL. So, we make the
--   changes in the catalog directly.
--

UPDATE pg_aggregate SET aggtranstype = 2281 WHERE aggfnoid = 'array_agg'::regproc;
UPDATE pg_proc SET prorettype = 2277 WHERE oid = 'array_agg'::regproc;
UPDATE pg_proc SET prorettype = 2281, proargtypes = '2281 2283' WHERE oid = 'array_agg_transfn'::regproc;
UPDATE pg_proc SET proargtypes = '2281' WHERE oid = 'array_agg_finalfn'::regproc;

COMMIT;

On Mon, 19 Jan 2009, Jeff Davis wrote:

> On Mon, 2009-01-19 at 13:40 -0500, Faheem Mitha wrote:
>> In any case, I don't have admin permissions on the machine I'm trying to
>> install it to.
>
> At absolute minimum, you need PostgreSQL superuser privileges. If you
> don't, you need to set up a new PostgreSQL instance (which you can do as
> a normal non-root user), and then you will have superuser privileges for
> your own instance.
>
> If you do have PostgreSQL superuser privileges, but not root on the
> machine, you can install the module to some other location by changing
> some paths around, although that might be slightly ugly.

Hi Jeff,

Yes, I have pg superuser privileges. I *think* this is the same as having
sudo rights for postgres, right? Are you suggesting adding another entry
to the library path, or whatever this is called? Like ~/.postgresql/ or
something like that?

To be clear, even if I have admin on the machine, it is generally
undesirable to install unpackaged software to the machine, so I'd have to
package it first. I guess that is an option if I find I am using it a lot.

Is there any major downside to the way I'm doing it, as described in my
last message?

Thanks again for your help.
                                                          Regards, Faheem.

On Mon, 2009-01-19 at 13:40 -0500, Faheem Mitha wrote:
> In any case, I don't have admin permissions on the machine I'm trying to
> install it to.

At absolute minimum, you need PostgreSQL superuser privileges. If you
don't, you need to set up a new PostgreSQL instance (which you can do as
a normal non-root user), and then you will have superuser privileges for
your own instance.

If you do have PostgreSQL superuser privileges, but not root on the
machine, you can install the module to some other location by changing
some paths around, although that might be slightly ugly.

Regards,
    Jeff Davis

On Mon, 2009-01-19 at 14:09 -0500, Faheem Mitha wrote:
> Yes, I have pg superuser privileges. I *think* this is the same as having
> sudo rights for postgres, right? Are you suggesting adding another entry
> to the library path, or whatever this is called? Like ~/.postgresql/ or
> something like that?

If you have sudo rights for postgres, that should be fine. All you need
to do is "make" in the module, and it will produce a file array_agg.so.
Copy the .so file to someplace (wherever makes the most sense for you),
and then change all the paths in the .sql file to point to that .so
file. Then run the .sql file as the postgresql superuser.

Regards,
    Jeff Davis