Обсуждение: pl/pgsql create table
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
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
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
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
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