Workaround solution for GP CTAS problem

Details

Type: Task
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: v0.2beta
Fix Version/s: v0.2.1beta
Component/s: Design

DB Port:
Greenplum

Description

Make a list of problematic UDFs: driver UDFs returning output set.

Possible solutions:

create one function for each problematic UDF which takes another argument for output table and runs the old UDF in a cursor,
make just one extra wrapper UDF, say: madlib.run_to_table( 'logistic_regression(...)', 'some_schema.some_table') which will do the same thing as option #1 but with dynamic SQL

Issue Links

relates to

MADLIB-363 Kmeans: "source relation does not exist or can not be accessed " when creating table as select from kmeans

MADLIB-265 Implement a workaround for INSERT INTO on Greenplum in the same way as the existing CTAS workaround

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Florian Schoppmann added a comment - 17/Aug/11 6:37 PM

I vote for option #2, using a memorable name. What about "create_table_as()"?

Show

Florian Schoppmann added a comment - 17/Aug/11 6:37 PM I vote for option #2, using a memorable name. What about "create_table_as()"?

Hide

Permalink

Florian Schoppmann added a comment - 17/Aug/11 6:39 PM

So a complete usage would be:

SELECT create_table_as('new_table_name', $$
    SELECT * FROM madlib.logregr('data', 'y', 'x')
$$);

Show

Florian Schoppmann added a comment - 17/Aug/11 6:39 PM So a complete usage would be: SELECT create_table_as('new_table_name', $$ SELECT * FROM madlib.logregr('data', 'y', 'x') $$);

Hide

Permalink

Florian Schoppmann added a comment - 24/Aug/11 6:59 PM - edited

The greatest workaround mankind has ever come up with, © Aleks Gorajek and Florian Schoppmann.

DROP FUNCTION IF EXISTS create_table_as(inTableName VARCHAR, inSQL VARCHAR,
    inDistributed VARCHAR);

/**
 * @brief Mimick CREATE TABLE AS. Create a table and fill it with data computed
 *     by a SELECT command.
 * @param inTableName The name (optionally schema-qualified) of the table to be
 *     created.
 * @param inSQL A SELECT command.
 * @param inDistributed The Greenplum Database distribution policy for the
 *     table. This can be either \c RANDOMLY or
 *     <tt>BY (<em>column</em>, [...])</tt>.
 */
CREATE FUNCTION create_table_as(inTableName VARCHAR, inSQL VARCHAR,
    inDistributed VARCHAR)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
DECLARE
    quotedTableName TEXT;
    oldClientMinMessages TEXT;
BEGIN
    EXECUTE
        $execute$
        SELECT setting FROM pg_settings WHERE name = 'client_min_messages'
        $execute$
        INTO oldClientMinMessages;
    EXECUTE 'SET client_min_messages TO warning';
    quotedTableName := quote_ident(inTableName);
    EXECUTE
        $execute$
        CREATE TABLE $execute$ || quotedTableName || $execute$
            AS SELECT * FROM ($execute$ || inSQL || $execute$) AS _madlib_ignore
            WHERE FALSE
            DISTRIBUTED $execute$ || inDistributed || $execute$;
        DROP FUNCTION IF EXISTS pg_temp._madlib_temp_function();
        CREATE FUNCTION pg_temp._madlib_temp_function()
            RETURNS VOID AS $BODY_madlib_temp_function$
            DECLARE
                result $execute$ || quotedTableName || $execute$%ROWTYPE;
            BEGIN
	        FOR result IN EXECUTE
	            $_madlib_temp_function_string$
	            $execute$ || inSQL || $execute$
	            $_madlib_temp_function_string$
	            LOOP

	            INSERT INTO $execute$ || quotedTableName || $execute$
	                VALUES (result.*);
	        END LOOP;
            END;
            $BODY_madlib_temp_function$ LANGUAGE plpgsql;
        $execute$;
    -- We call _madlib_temp_function() in a separate
    -- EXECUTE statement because it is not yet visible before
    -- (we would see "ERROR: function _madlib_temp_function() does not exist")
    EXECUTE 'SELECT pg_temp._madlib_temp_function();';
    EXECUTE 'SET client_min_messages TO ' || oldClientMinMessages;
END;
$$;

DROP FUNCTION IF EXISTS create_table_as(inTableName VARCHAR, inSQL VARCHAR);

/**
 * @brief Mimick CREATE TABLE AS. Create a table and fill it with data computed
 *     by a SELECT command.
 *
 * The table will be distributed randomly.
 *
 * @param inTableName The name (optionally schema-qualified) of the table to be
 *     created.
 * @param inSQL A SELECT command.
 */
CREATE FUNCTION create_table_as(inTableName VARCHAR, inSQL VARCHAR)
RETURNS VOID
LANGUAGE sql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT create_table_as($1, $2, 'RANDOMLY');
$$;

Show

Florian Schoppmann added a comment - 24/Aug/11 6:59 PM - edited The greatest workaround mankind has ever come up with, © Aleks Gorajek and Florian Schoppmann. DROP FUNCTION IF EXISTS create_table_as(inTableName VARCHAR, inSQL VARCHAR, inDistributed VARCHAR); /** * @brief Mimick CREATE TABLE AS. Create a table and fill it with data computed * by a SELECT command. * @param inTableName The name (optionally schema-qualified) of the table to be * created. * @param inSQL A SELECT command. * @param inDistributed The Greenplum Database distribution policy for the * table. This can be either \c RANDOMLY or * <tt>BY (<em>column</em>, [...])</tt>. */ CREATE FUNCTION create_table_as(inTableName VARCHAR, inSQL VARCHAR, inDistributed VARCHAR) RETURNS VOID LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT AS $$ DECLARE quotedTableName TEXT; oldClientMinMessages TEXT; BEGIN EXECUTE $execute$ SELECT setting FROM pg_settings WHERE name = 'client_min_messages' $execute$ INTO oldClientMinMessages; EXECUTE 'SET client_min_messages TO warning'; quotedTableName := quote_ident(inTableName); EXECUTE $execute$ CREATE TABLE $execute$ || quotedTableName || $execute$ AS SELECT * FROM ($execute$ || inSQL || $execute$) AS _madlib_ignore WHERE FALSE DISTRIBUTED $execute$ || inDistributed || $execute$; DROP FUNCTION IF EXISTS pg_temp._madlib_temp_function(); CREATE FUNCTION pg_temp._madlib_temp_function() RETURNS VOID AS $BODY_madlib_temp_function$ DECLARE result $execute$ || quotedTableName || $execute$%ROWTYPE; BEGIN FOR result IN EXECUTE $_madlib_temp_function_string$ $execute$ || inSQL || $execute$ $_madlib_temp_function_string$ LOOP INSERT INTO $execute$ || quotedTableName || $execute$ VALUES (result.*); END LOOP; END; $BODY_madlib_temp_function$ LANGUAGE plpgsql; $execute$; -- We call _madlib_temp_function() in a separate -- EXECUTE statement because it is not yet visible before -- (we would see "ERROR: function _madlib_temp_function() does not exist") EXECUTE 'SELECT pg_temp._madlib_temp_function();'; EXECUTE 'SET client_min_messages TO ' || oldClientMinMessages; END; $$; DROP FUNCTION IF EXISTS create_table_as(inTableName VARCHAR, inSQL VARCHAR); /** * @brief Mimick CREATE TABLE AS. Create a table and fill it with data computed * by a SELECT command. * * The table will be distributed randomly. * * @param inTableName The name (optionally schema-qualified) of the table to be * created. * @param inSQL A SELECT command. */ CREATE FUNCTION create_table_as(inTableName VARCHAR, inSQL VARCHAR) RETURNS VOID LANGUAGE sql VOLATILE RETURNS NULL ON NULL INPUT AS $$ SELECT create_table_as($1, $2, 'RANDOMLY'); $$;

Hide

Permalink

Aleksander Gorajek (Inactive) added a comment - 24/Aug/11 11:17 PM - edited

This is fantastic! GP hacking gem!

Now, let's think of a good place to put it...

Show

Aleksander Gorajek (Inactive) added a comment - 24/Aug/11 11:17 PM - edited This is fantastic! GP hacking gem! Now, let's think of a good place to put it...

Hide

Permalink

Florian Schoppmann added a comment - 25/Aug/11 7:39 PM - edited

Created pull request. Aleks, please merge into main code line after a review. Thanks!
The link: https://github.com/madlib/madlib/pull/75

Show

Florian Schoppmann added a comment - 25/Aug/11 7:39 PM - edited Created pull request. Aleks, please merge into main code line after a review. Thanks! The link: https://github.com/madlib/madlib/pull/75

Hide

Permalink

Florian Schoppmann added a comment - 29/Aug/11 1:12 PM

We should also add the two functions create_temp_table_as() and create_temporary_table_as().

Show

Florian Schoppmann added a comment - 29/Aug/11 1:12 PM We should also add the two functions create_temp_table_as() and create_temporary_table_as() .

Hide

Permalink

Aleksander Gorajek (Inactive) added a comment - 29/Aug/11 1:39 PM

Why two? Would they differ in any way?

Show

Aleksander Gorajek (Inactive) added a comment - 29/Aug/11 1:39 PM Why two? Would they differ in any way?

Hide

Permalink

Florian Schoppmann added a comment - 29/Aug/11 7:26 PM

They are exactly the same. I added both because there are two originals: CREATE TEMP TABLE AS and CREATE TEMPORARY TABLE AS.

Show

Florian Schoppmann added a comment - 29/Aug/11 7:26 PM They are exactly the same. I added both because there are two originals: CREATE TEMP TABLE AS and CREATE TEMPORARY TABLE AS.

Hide

Permalink

Aleksander Gorajek (Inactive) added a comment - 30/Aug/11 5:47 PM

Reviewed, added a test case (https://github.com/madlib/madlib/commit/4bbf94e1832e055839a08ad8e1199c6a8a8c1846) and merged into madlib master.

Show

Aleksander Gorajek (Inactive) added a comment - 30/Aug/11 5:47 PM Reviewed, added a test case ( https://github.com/madlib/madlib/commit/4bbf94e1832e055839a08ad8e1199c6a8a8c1846 ) and merged into madlib master.

Hide

Permalink

Florian Schoppmann added a comment - 31/Aug/11 2:07 PM

We need to document that this is a workaround, and only be used as such. Reopening.

Show

Florian Schoppmann added a comment - 31/Aug/11 2:07 PM We need to document that this is a workaround, and only be used as such. Reopening.

Hide

Permalink

Florian Schoppmann added a comment - 02/Sep/11 3:18 PM

Added to documentation that workaround should only be used when necessary. See discussion at: https://github.com/madlib/madlib/pull/75

Show

Florian Schoppmann added a comment - 02/Sep/11 3:18 PM Added to documentation that workaround should only be used when necessary. See discussion at: https://github.com/madlib/madlib/pull/75

People

Assignee:

Florian Schoppmann

Reporter:

Aleksander Gorajek (Inactive)

Vote (0)

Watch (0)

Dates

Created:

16/Aug/11 6:27 PM

Updated:

31/Jan/12 8:57 PM

Resolved:

02/Sep/11 3:18 PM

Agile

View on Board