The following bug has been logged online:

Bug reference: 1808
Logged by: Michael Afanasiev
Email address: wbear@wbear.ru
PostgreSQL version: 8.0.0 & 7.4.6
Operating system: 8.0.0 - WIN XP 7.4.6 FreeBSD
Description: bug: plan bugs on alert /drop table
Details:

/*
Postgres must recreate all plans when table recreated or alerted
*/


show server_version; --8.0.0 & 7.4.6
CREATE TABLE public.a ( a int4);
CREATE FUNCTION public.aa_func() RETURNS int4 AS
'BEGIN

insert into a (a) values (1);
RETURN 1;
END;'
LANGUAGE 'plpgsql' VOLATILE;
SELECT * FROM aa_func(); -- OK 1 row inserted

DROP TABLE a;
CREATE TABLE public.a ( a int4);
SELECT * FROM a;
SELECT * FROM aa_func();-- !!! ERROR: relation with OID XXXX does not
exist


DROP FUNCTION public.aa_func();
CREATE FUNCTION public.aa_func() RETURNS int4 AS
'BEGIN

insert into a (a) values (1);
RETURN 1;
END;'
LANGUAGE 'plpgsql' VOLATILE;
-- DROP function ,CREATE function or reconnect

SELECT * FROM aa_func(); -- OK 1 row inserted
SELECT * FROM a;
DELETE FROM a;

ALTER TABLE public.a ADD COLUMN b int4;
ALTER TABLE public.a ALTER COLUMN b SET NOT NULL;
ALTER TABLE public.a ALTER COLUMN b SET DEFAULT 0;

SELECT * FROM aa_func(); -- !!! ERROR: .. null value in column "b" violates
not-null constraint

--DROP function ,CREATE function or reconnect
SELECT * FROM aa_func(); --- OK 1 row inserted

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

It is a well known bug?

""Michael Afanasiev"" <wbear@wbear.ru> Wrote
news:20050805142530.30E6EF0B06@svr2.postgresql.org ...
>
> The following bug has been logged online:
>
> Bug reference: 1808
> Logged by: Michael Afanasiev
> Email address: wbear@wbear.ru
> PostgreSQL version: 8.0.0 & 7.4.6
> Operating system: 8.0.0 - WIN XP 7.4.6 FreeBSD
> Description: bug: plan bugs on alert /drop table
> Details:
>
> /*
> Postgres must recreate all plans when table recreated or alerted
> */
>
>
> show server_version; --8.0.0 & 7.4.6
> CREATE TABLE public.a ( a int4);
> CREATE FUNCTION public.aa_func() RETURNS int4 AS
> 'BEGIN
>
> insert into a (a) values (1);
> RETURN 1;
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
> SELECT * FROM aa_func(); -- OK 1 row inserted
>
> DROP TABLE a;
> CREATE TABLE public.a ( a int4);
> SELECT * FROM a;
> SELECT * FROM aa_func();-- !!! ERROR: relation with OID XXXX does not
> exist
>
>
> DROP FUNCTION public.aa_func();
> CREATE FUNCTION public.aa_func() RETURNS int4 AS
> 'BEGIN
>
> insert into a (a) values (1);
> RETURN 1;
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
> -- DROP function ,CREATE function or reconnect
>
> SELECT * FROM aa_func(); -- OK 1 row inserted
> SELECT * FROM a;
> DELETE FROM a;
>
> ALTER TABLE public.a ADD COLUMN b int4;
> ALTER TABLE public.a ALTER COLUMN b SET NOT NULL;
> ALTER TABLE public.a ALTER COLUMN b SET DEFAULT 0;
>
> SELECT * FROM aa_func(); -- !!! ERROR: .. null value in column "b"
violates
> not-null constraint
>
> --DROP function ,CREATE function or reconnect
> SELECT * FROM aa_func(); --- OK 1 row inserted
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Please see the FAQ. This is a known problem.

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

Michael Afanasiev wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1808
> Logged by: Michael Afanasiev
> Email address: wbear@wbear.ru
> PostgreSQL version: 8.0.0 & 7.4.6
> Operating system: 8.0.0 - WIN XP 7.4.6 FreeBSD
> Description: bug: plan bugs on alert /drop table
> Details:
>
> /*
> Postgres must recreate all plans when table recreated or alerted
> */
>
>
> show server_version; --8.0.0 & 7.4.6
> CREATE TABLE public.a ( a int4);
> CREATE FUNCTION public.aa_func() RETURNS int4 AS
> 'BEGIN
>
> insert into a (a) values (1);
> RETURN 1;
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
> SELECT * FROM aa_func(); -- OK 1 row inserted
>
> DROP TABLE a;
> CREATE TABLE public.a ( a int4);
> SELECT * FROM a;
> SELECT * FROM aa_func();-- !!! ERROR: relation with OID XXXX does not
> exist
>
>
> DROP FUNCTION public.aa_func();
> CREATE FUNCTION public.aa_func() RETURNS int4 AS
> 'BEGIN
>
> insert into a (a) values (1);
> RETURN 1;
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
> -- DROP function ,CREATE function or reconnect
>
> SELECT * FROM aa_func(); -- OK 1 row inserted
> SELECT * FROM a;
> DELETE FROM a;
>
> ALTER TABLE public.a ADD COLUMN b int4;
> ALTER TABLE public.a ALTER COLUMN b SET NOT NULL;
> ALTER TABLE public.a ALTER COLUMN b SET DEFAULT 0;
>
> SELECT * FROM aa_func(); -- !!! ERROR: .. null value in column "b" violates
> not-null constraint
>
> --DROP function ,CREATE function or reconnect
> SELECT * FROM aa_func(); --- OK 1 row inserted
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend