Re: Request for Comments: ALTER [OBJECT] SET SCHEMA

--On Mittwoch, Juni 08, 2005 14:48:55 -0400 Alvaro Herrera
<alvherre(at)surnet(dot)cl> wrote:

> On Wed, Jun 08, 2005 at 08:25:12PM +0200, Bernd Helmle wrote:
>
>> One issue that comes to my mind is what to do when dealing with tables
>> that have assigned triggers and sequences (serials). Do we want to move
>> them as well or leave them in the source namespace?
>
> I'd think it's important that the ALTER TABLE leaves things just like
> what you'd end up with if you created the table in the new schema in the
> first place. i.e., indexes, triggers, sequences should be moved too.
>

That leads me to the question what gets attached to a table:

SEQUENCE, INDEX, TRIGGER (function), CONSTRAINT, .... ?

> One issue to check is what happens if you move the table and trigger but
> the function remains in the original namespace. Is this a problem if
> the new namespace is not in the search path?

Hmm have triggers an own namespace? I can see in pg_trigger that they are
attached to pg_proc, but can't see an own namespace specification...

However, lets have a look at this example:

bernd(at)[local]:bernd #= CREATE SCHEMA B;
CREATE SCHEMA
bernd(at)[local]:bernd #= set search_path TO b;
SET
bernd(at)[local]:bernd #= CREATE TABLE test ( id integer not null primary key
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE
bernd(at)[local]:bernd #= CREATE TABLE log_test ( usr text, log_time timestamp
default NOW() );
CREATE TABLE ^
bernd(at)[local]:bernd #= CREATE OR REPLACE FUNCTION trigger_log_update()
RETURNS TRIGGER AS $$ BEGIN INSERT INTO log_test VALUES( current_user );
RETURN new; END; $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
bernd(at)[local]:bernd #= CREATE TRIGGER t_log_update AFTER UPDATE OR DELETE
OR INSERT ON test FOR STATEMENT EXECUTE PROCEDURE trigger_log_update();
CREATE TRIGGER
bernd(at)[local]:bernd #= INSERT INTO test VALUES (2);
INSERT 0 1
bernd(at)[local]:bernd #= CREATE SCHEMA C;
CREATE SCHEMA
bernd(at)[local]:bernd #= ALTER TABLE test SET SCHEMA C;
NOTICE: changed dependency to new schema "c"
ALTER TABLE
bernd(at)[local]:bernd #= SET search_path TO C;
SET
bernd(at)[local]:bernd #= INSERT INTO test VALUES (4);
INSERT 0 1

So that works, but let's move the trigger function as well:

bernd(at)[local]:bernd #= ALTER FUNCTION B.trigger_log_update() SET SCHEMA C;
NOTICE: changed dependency to new schema "c"
ALTER TABLE
bernd(at)[local]:bernd #= INSERT INTO test VALUES (5);
ERROR: relation "log_test" does not exist
CONTEXT: SQL statement "INSERT INTO log_test VALUES( current_user )"
PL/pgSQL function "trigger_log_update" line 1 at SQL statement

So that doesn't work and it's likely that someone can mess up his schema
with this, because the trigger function no longer finds its "log table".
Don't know how to deal with that.....

--

Bernd