Re: PostgreSQL question
От | Mridul Mathew |
---|---|
Тема | Re: PostgreSQL question |
Дата | |
Msg-id | CAFm5QJzj7j1ZsE0oWt_2o5XaA3ZX_siJ1r0jz91FeFDDKtF9TQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: PostgreSQL question (Guillaume Lelarge <guillaume@lelarge.info>) |
Список | pgsql-admin |
Okay, thanks!
-Mridul.
-Mridul.
On Tue, Sep 27, 2011 at 12:14 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
This isn't a PostgreSQL question, this is an EnterpriseDB one. PackagesOn 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).
>
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
В списке pgsql-admin по дате отправления:
Следующее
От: Adam TistlerДата:
Сообщение: timeline X of the primary does not match recovery target timeline Y