By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,192 Members | 1,783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,192 IT Pros & Developers. It's quick & easy.

error when executing dynamic commands with transaction

P: n/a
(another try with a different subject as it doesn't seem to work with
"EXECUTE + transaction = unexpected error -8" :-/)
Hi,

It seems that there is a problem when executing a dynamic commands
containing a transaction...

Here's a simple example...

CREATE OR REPLACE FUNCTION fct_trig2()
RETURNS TRIGGER AS '
BEGIN
EXECUTE ''BEGIN;''
|| ''CREATE TABLE t2_'' || NEW.id::TEXT || ''(''
|| '' CONSTRAINT pkt2_'' || NEW.id::TEXT || '' PRIMARY KEY (id)''
|| '') INHERITS (t1);''
|| ''COMMIT;'';
RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE TABLE t1 (id INT PRIMARY KEY);

CREATE TABLE t2 (id INT PRIMARY KEY);
CREATE TRIGGER trig2 AFTER INSERT ON t2 FOR EACH ROW EXECUTE PROCEDURE
fct_trig2();

test=# INSERT INTO t2 VALUES (1);
ERROR: unexpected error -8 in EXECUTE of query "BEGIN;CREATE TABLE
t2_1( CONSTRAINT pkt2_1 PRIMARY KEY (id)) INHERITS (t1);COMMIT;"
CONTEXT: PL/pgSQL function "fct_trig2" line 2 at execute statement
If I don't use a transaction in the executed statement, there is no problem.
Remark: In this example a transaction is not needed, but in my real
usage of this I'm creating a table and granting permissions, and maybe
also creating a view. So a transaction is preferable.

I've tested this on "PostgreSQL 7.4.1 on i386-pc-linux-gnu, compiled by
GCC i386-linux-gcc (GCC) 3.3.2 (Debian)", Debian Sarge.

And also on "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
2.95.4", Debian Woody.
On that system, the function fct_trig2() needs to return OPAQUE instead
of TRIGGER.
Thanks,
Pascal

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Pascal Polleunus wrote:
If I don't use a transaction in the executed statement, there is no
problem.
Remark: In this example a transaction is not needed, but in my real
usage of this I'm creating a table and granting permissions, and maybe
also creating a view. So a transaction is preferable.

I've tested this on "PostgreSQL 7.4.1 on i386-pc-linux-gnu, compiled by
GCC i386-linux-gcc (GCC) 3.3.2 (Debian)", Debian Sarge.


AFAIK, functions reside in transaction scope created by caller. So you can not
call begin/commit in a transaction.

Correct me if I am wrong..

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #2

P: n/a
Shridhar Daithankar wrote:
Pascal Polleunus wrote:
If I don't use a transaction in the executed statement, there is no
problem.
Remark: In this example a transaction is not needed, but in my real
usage of this I'm creating a table and granting permissions, and maybe
also creating a view. So a transaction is preferable.

I've tested this on "PostgreSQL 7.4.1 on i386-pc-linux-gnu, compiled by
GCC i386-linux-gcc (GCC) 3.3.2 (Debian)", Debian Sarge.

AFAIK, functions reside in transaction scope created by caller. So you
can not call begin/commit in a transaction.


Argh.. read that as 'can not call begin/commit in a function'.

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.