pl/pgsql create table

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

---------------------------------------------------------------------------

Masse Jacques wrote:

I wonder when doing in a pg/pgsql function somewhat like

CREATE TABLE tmp as select foo;

I have an error when tmp don't exist ( "table tmp don't exist" ...)

and all is working well when there is a table tmp (no matter the structure
of this table, the new table has a structure according to foo)

Id with

DROP table tmp_site;
CREATE table tmp_site as select foo;

Cheers
__________________________________________
Jacques Mass?
Tel. 33 (0)5 57 89 08 11 - Fax 33 (0)5 57 89 08 01

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

IMHO, no -- the solution is to automatically invalidate compiled query
plans when a dependant relation is removed. Not exactly sure how to do
it, but I was thinking of tackling this for 7.4 (suggestions are
welcome, of course).

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Neil Conway wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

IMHO, no -- the solution is to automatically invalidate compiled query
plans when a dependant relation is removed. Not exactly sure how to do
it, but I was thinking of tackling this for 7.4 (suggestions are
welcome, of course).

Yes, but how do you handle cases where the table gets create/dropped
inside the transaction. It is clearly tricky.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Masse Jacques wrote:

I find exactly the same with
EXECUTE ''CREATE TABLE tmp as select foo'';

How about:

EXECUTE ''CREATE TABLE tmp as select * FROM foo'';

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I find exactly the same with
EXECUTE ''CREATE TABLE tmp as select foo'';

___________________________________________
Jacques Massé
Tel. 33 (0)5 57 89 08 11 - Fax 33 (0)5 57 89 08 01

-----Message d'origine-----
De : Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Envoyé : mardi 27 août 2002 16:05
À : Masse Jacques
Cc : pgsql-general
Objet : Re: [GENERAL] pl/pgsql create table

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

---------------------------------------------------------------------------

Masse Jacques wrote:

I wonder when doing in a pg/pgsql function somewhat like

CREATE TABLE tmp as select foo;

I have an error when tmp don't exist ( "table tmp don't exist" ...)

and all is working well when there is a table tmp (no matter the structure
of this table, the new table has a structure according to foo)

Id with

DROP table tmp_site;
CREATE table tmp_site as select foo;

Cheers
__________________________________________
Jacques Mass?
Tel. 33 (0)5 57 89 08 11 - Fax 33 (0)5 57 89 08 01

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sorry, I omitted to say that 'foo' was a some complicated select statement
(working alone ...)

___________________________________________
Jacques Massé
Tel. 33 (0)5 57 89 08 11 - Fax 33 (0)5 57 89 08 01

-----Message d'origine-----
De : Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Envoyé : mardi 27 août 2002 17:39
À : Masse Jacques
Cc : pgsql-general
Objet : Re: [GENERAL] pl/pgsql create table

Masse Jacques wrote:

I find exactly the same with
EXECUTE ''CREATE TABLE tmp as select foo'';

How about:

EXECUTE ''CREATE TABLE tmp as select * FROM foo'';

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Masse Jacques <jacques.masse@bordeaux.cemagref.fr> writes:

I find exactly the same with
EXECUTE ''CREATE TABLE tmp as select foo'';

The CREATE is not the problem, it's the subsequent *references* to tmp
that all have to be wrapped with EXECUTE.

regards, tom lane

Neil Conway wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

IMHO, no -- the solution is to automatically invalidate compiled query
plans when a dependant relation is removed. Not exactly sure how to do
it, but I was thinking of tackling this for 7.4 (suggestions are
welcome, of course).

Yes, but how do you handle cases where the table gets create/dropped
inside the transaction. It is clearly tricky.

If I remember right, Oracle does not allow DDL-Statements in PL/SQL
procedures. You have to use a special package (I have forgotten the name)
which prevents the DDL-Statement from being precompiled.

So maybe this is a simple workaround: forbid DDL-Statements without EXECUTE.

Just my 0.2 Euros,

Christoph Dalitz

Christoph Dalitz wrote:

Neil Conway wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

IMHO, no -- the solution is to automatically invalidate compiled query
plans when a dependant relation is removed. Not exactly sure how to do
it, but I was thinking of tackling this for 7.4 (suggestions are
welcome, of course).

Yes, but how do you handle cases where the table gets create/dropped
inside the transaction. It is clearly tricky.

If I remember right, Oracle does not allow DDL-Statements in PL/SQL
procedures. You have to use a special package (I have forgotten the name)
which prevents the DDL-Statement from being precompiled.

So maybe this is a simple workaround: forbid DDL-Statements without EXECUTE.

I wish it was that simple. You could create a temp table, execute the
function, then drop/recreate the table, and when you reexecute the
function, the temp table with the precompiled oid is gone.

It can get even worse because you could create a permanent table, run
the function that accesses it, then create a temp table with the same
name that masks the premanent table, but when you execute the function,
it will not see the temp table properly. Clearly, it is a mess.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian dijo:

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

I don't understand. I think he is referring to the fact that the
function aborts midway because the DROP TABLE fails and marks the
transaction as failed. (but I didn't test before posting)

If that's the case, the solution would be to test for existance of the
table before the DROP TABLE statement.

---------------------------------------------------------------------------

Masse Jacques wrote:

I have an error when tmp don't exist ( "table tmp don't exist" ...)

and all is working well when there is a table tmp (no matter the structure
of this table, the new table has a structure according to foo)

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

I was merely saying that we have multiple problems with compiled-in oids
in plpgsql functions.

---------------------------------------------------------------------------

Alvaro Herrera wrote:

Bruce Momjian dijo:

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

I don't understand. I think he is referring to the fact that the
function aborts midway because the DROP TABLE fails and marks the
transaction as failed. (but I didn't test before posting)

If that's the case, the solution would be to test for existance of the
table before the DROP TABLE statement.

---------------------------------------------------------------------------

Masse Jacques wrote:

I have an error when tmp don't exist ( "table tmp don't exist" ...)

and all is working well when there is a table tmp (no matter the structure
of this table, the new table has a structure according to foo)

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian dijo:

I was merely saying that we have multiple problems with compiled-in oids
in plpgsql functions.

Oh, yes, I agree with you on that.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"

Beginner in pgpsql, I have to work a bit to understand all these postings :)

For my own case,
CREATE TABLE mytable AS SELECT * FROM something
works with or without EXECUTE when mytable exists before launching the
function. Both don't work when mytable don't exist.

So, I
CREATE TABLE wk_table (foo int4)
which lives permanently in the database and the function
DROP wk_table
CREATE wk_table with a new structure given by select * from
something

Question : Is it better with EXECUTE and can I use safely this function or
are there some hidden OIDaemons parasiting my database ?

Thanks for all

__________________________________________
Jacques Massé
Tel. 33 (0)5 57 89 08 11 - Fax 33 (0)5 57 89 08 01

-----Message d'origine-----
De : Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Envoyé : mardi 27 août 2002 22:01
À : Alvaro Herrera
Cc : Masse Jacques; pgsql-general
Objet : Re: [GENERAL] pl/pgsql create table

I was merely saying that we have multiple problems with compiled-in oids
in plpgsql functions.

---------------------------------------------------------------------------

Alvaro Herrera wrote:

Bruce Momjian dijo:

When referencing created/dropped tables in pl/pgsql, use EXECUTE to
prevent the table oid from being stored in function as precompiled. It
is mentioned in the current FAQ. The solution is for us to
automatically add EXECUTE somehow.

I don't understand. I think he is referring to the fact that the
function aborts midway because the DROP TABLE fails and marks the
transaction as failed. (but I didn't test before posting)

If that's the case, the solution would be to test for existance of the
table before the DROP TABLE statement.

---------------------------------------------------------------------------

Masse Jacques wrote:

I have an error when tmp don't exist ( "table tmp don't exist" ...)

and all is working well when there is a table tmp (no matter the

structure

of this table, the new table has a structure according to foo)

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073