Thread: PostgreSQL question

PostgreSQL question

From
Mridul Mathew
Date:
Hello,

I wanted to take a moment here and reach out to the community with a question. How does postgreSQL handle schema switching in the database? I have a test scenario.

##################

vpn2=> show search_path;
 search_path
-------------
 mridul
(1 row)

CREATE OR REPLACE PACKAGE mridul.TEST_PKG
IS
PROCEDURE execute_for_all_schema();
end TEST_PKG;

CREATE OR REPLACE PACKAGE BODY mridul.TEST_PKG
IS

    PROCEDURE execute_for_all_schema() IS

        CURSOR c_schema_names IS
        SELECT name
        FROM mridul.schema_config
        WHERE status = 'ACTIVE';

    BEGIN

        FOR dat_schema_names IN c_schema_names LOOP
-----    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '|| dat_schema_names.name;
        EXECUTE IMMEDIATE 'SET SEARCH_PATH = '|| dat_schema_names.name;
        EXECUTE IMMEDIATE 'insert into A values (1)';
        commit;
        END LOOP;
        EXCEPTION
        WHEN OTHERS THEN
            RAISE;

    END;
end    TEST_PKG;

vpn2=> select * from mridul.schema_config;
 id | name  | description | status | last_device_sync_date
----+-------+-------------+--------+-----------------------
  1 | TEST  | TESTING     | ACTIVE | 22-SEP-11 18:28:11
  6 | TEST1 | TESTING5    | ACTIVE | 22-SEP-11 11:46:09
  2 | TEST2 | TESTING2    | ACTIVE | 23-SEP-11 11:54:35
(3 rows)

vpn2=> show search_path;
 search_path
-------------
 mridul
(1 row)

vpn2=> select count(*) from test.a;
 count
-------
     0
(1 row)


vpn2=> select count(*) from test1.a;
 count
-------
     0
(1 row)


vpn2=> select count(*) from test2.a;
 count
-------
     0
(1 row)

vpn2=> exec mridul.TEST_PKG.execute_for_all_schema;

EDB-SPL Procedure successfully completed
vpn2=> select count(*) from test.a;
 count
-------
     1
(1 row)


vpn2=> select count(*) from test1.a;
 count
-------
     1
(1 row)


vpn2=> select count(*) from test2.a;
 count
-------
     1
(1 row)

If I convert the 'insert into A ...' part from a dynamic dml to a normal insert, and then execute the procedure, there are 3 rows inserted into test.a and none go into test1.a nor test2.a. However, if I echo the search_path after executing the proc, it displays the last schema's name (meaning, it has switched the schemas, but the insert has gone into only the first schema).

Thanks in advance!

-Mridul.


Re: PostgreSQL question

From
Guillaume Lelarge
Date:
On Mon, 2011-09-26 at 16:06 +0530, Mridul Mathew wrote:
> Hello,
>
> I wanted to take a moment here and reach out to the community with a
> question. How does postgreSQL handle schema switching in the database? I
> have a test scenario.
>
> ##################
>
> vpn2=> show search_path;
>  search_path
> -------------
>  mridul
> (1 row)
>
> CREATE OR REPLACE PACKAGE mridul.TEST_PKG
> IS
> PROCEDURE execute_for_all_schema();
> end TEST_PKG;
>
> CREATE OR REPLACE PACKAGE BODY mridul.TEST_PKG
> IS
>
>     PROCEDURE execute_for_all_schema() IS
>
>         CURSOR c_schema_names IS
>         SELECT name
>         FROM mridul.schema_config
>         WHERE status = 'ACTIVE';
>
>     BEGIN
>
>         FOR dat_schema_names IN c_schema_names LOOP
> -----    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '||
> dat_schema_names.name;
>         EXECUTE IMMEDIATE 'SET SEARCH_PATH = '|| dat_schema_names.name;
>         EXECUTE IMMEDIATE 'insert into A values (1)';
>         commit;
>         END LOOP;
>         EXCEPTION
>         WHEN OTHERS THEN
>             RAISE;
>
>     END;
> end    TEST_PKG;
>
> vpn2=> select * from mridul.schema_config;
>  id | name  | description | status | last_device_sync_date
> ----+-------+-------------+--------+-----------------------
>   1 | TEST  | TESTING     | ACTIVE | 22-SEP-11 18:28:11
>   6 | TEST1 | TESTING5    | ACTIVE | 22-SEP-11 11:46:09
>   2 | TEST2 | TESTING2    | ACTIVE | 23-SEP-11 11:54:35
> (3 rows)
>
> vpn2=> show search_path;
>  search_path
> -------------
>  mridul
> (1 row)
>
> vpn2=> select count(*) from test.a;
>  count
> -------
>      0
> (1 row)
>
>
> vpn2=> select count(*) from test1.a;
>  count
> -------
>      0
> (1 row)
>
>
> vpn2=> select count(*) from test2.a;
>  count
> -------
>      0
> (1 row)
>
> vpn2=> exec mridul.TEST_PKG.execute_for_all_schema;
>
> EDB-SPL Procedure successfully completed
> vpn2=> select count(*) from test.a;
>  count
> -------
>      1
> (1 row)
>
>
> vpn2=> select count(*) from test1.a;
>  count
> -------
>      1
> (1 row)
>
>
> vpn2=> select count(*) from test2.a;
>  count
> -------
>      1
> (1 row)
>
> If I convert the 'insert into A ...' part from a dynamic dml to a normal
> insert, and then execute the procedure, there are 3 rows inserted into
> test.a and none go into test1.a nor test2.a. However, if I echo the
> search_path after executing the proc, it displays the last schema's name
> (meaning, it has switched the schemas, but the insert has gone into only the
> first schema).
>

This isn't a PostgreSQL question, this is an EnterpriseDB one. Packages
are not part of the community PostgreSQL distribution. "EXECUTE
IMMEDIATE" is also not part of the community PostgreSQL distribution.

So I guess you need to ask them.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


Re: PostgreSQL question

From
Mridul Mathew
Date:
Okay, thanks!

-Mridul.

On Tue, Sep 27, 2011 at 12:14 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
On Mon, 2011-09-26 at 16:06 +0530, Mridul Mathew wrote:
> Hello,
>
> I wanted to take a moment here and reach out to the community with a
> question. How does postgreSQL handle schema switching in the database? I
> have a test scenario.
>
> ##################
>
> vpn2=> show search_path;
>  search_path
> -------------
>  mridul
> (1 row)
>
> CREATE OR REPLACE PACKAGE mridul.TEST_PKG
> IS
> PROCEDURE execute_for_all_schema();
> end TEST_PKG;
>
> CREATE OR REPLACE PACKAGE BODY mridul.TEST_PKG
> IS
>
>     PROCEDURE execute_for_all_schema() IS
>
>         CURSOR c_schema_names IS
>         SELECT name
>         FROM mridul.schema_config
>         WHERE status = 'ACTIVE';
>
>     BEGIN
>
>         FOR dat_schema_names IN c_schema_names LOOP
> -----    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '||
> dat_schema_names.name;
>         EXECUTE IMMEDIATE 'SET SEARCH_PATH = '|| dat_schema_names.name;
>         EXECUTE IMMEDIATE 'insert into A values (1)';
>         commit;
>         END LOOP;
>         EXCEPTION
>         WHEN OTHERS THEN
>             RAISE;
>
>     END;
> end    TEST_PKG;
>
> vpn2=> select * from mridul.schema_config;
>  id | name  | description | status | last_device_sync_date
> ----+-------+-------------+--------+-----------------------
>   1 | TEST  | TESTING     | ACTIVE | 22-SEP-11 18:28:11
>   6 | TEST1 | TESTING5    | ACTIVE | 22-SEP-11 11:46:09
>   2 | TEST2 | TESTING2    | ACTIVE | 23-SEP-11 11:54:35
> (3 rows)
>
> vpn2=> show search_path;
>  search_path
> -------------
>  mridul
> (1 row)
>
> vpn2=> select count(*) from test.a;
>  count
> -------
>      0
> (1 row)
>
>
> vpn2=> select count(*) from test1.a;
>  count
> -------
>      0
> (1 row)
>
>
> vpn2=> select count(*) from test2.a;
>  count
> -------
>      0
> (1 row)
>
> vpn2=> exec mridul.TEST_PKG.execute_for_all_schema;
>
> EDB-SPL Procedure successfully completed
> vpn2=> select count(*) from test.a;
>  count
> -------
>      1
> (1 row)
>
>
> vpn2=> select count(*) from test1.a;
>  count
> -------
>      1
> (1 row)
>
>
> vpn2=> select count(*) from test2.a;
>  count
> -------
>      1
> (1 row)
>
> If I convert the 'insert into A ...' part from a dynamic dml to a normal
> insert, and then execute the procedure, there are 3 rows inserted into
> test.a and none go into test1.a nor test2.a. However, if I echo the
> search_path after executing the proc, it displays the last schema's name
> (meaning, it has switched the schemas, but the insert has gone into only the
> first schema).
>

This isn't a PostgreSQL question, this is an EnterpriseDB one. Packages
are not part of the community PostgreSQL distribution. "EXECUTE
IMMEDIATE" is also not part of the community PostgreSQL distribution.

So I guess you need to ask them.


--
Guillaume
 http://blog.guillaume.lelarge.info
 http://www.dalibo.com