ALTERed DEFAULTS not visible to PL/pgSQL cached plans

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: ALTERed DEFAULTS not visible to PL/pgSQL cached plans
Date: 2006-03-12 01:31:45
Message-ID: 200603111731.45809.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Folks,

Version: 8.1.3
Platform: SuSE Linux, GCC
Severity: mild data corruption
Reproducability: 100%

Steps to Reproduce:
(sample code attached)
1) Create a table.
2) Create a function which inserts a row into that table.
3) Run the function once.
4) ALTER the table with a new column and SET DEFAULT for that column.
5) Run the function again.
6) Re-load the function (via REPLACE)
7) Insert one more row using the function.
8) The table will have NULL values in the first TWO rows, not the first ONE
row as it should. This is because the DEFAULT value is not being "seen"
by the cached plan of the function. As an example, the attached code
produces:

ltreetest=# select * from bugtest;
id | name | is_true
----+----------------+---------
1 | Before ALTER |
2 | Look, its null |
3 | Now its true. | t

When it should produce:

ltreetest=# select * from bugtest;
id | name | is_true
----+----------------+---------
1 | Before ALTER |
2 | Look, its null | t
3 | Now its true. | t

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Attachment Content-Type Size
alter_bug.sql text/x-objcsrc 749 bytes

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Francisco de Assis Carvalho da Silva Neto 2006-03-12 01:41:53 BUG #2313: Installation on a FAT32 partition
Previous Message inner 2006-03-11 11:07:27 BUG #2312: Software caused connection abort (0x00002745/10053) with long Bytea (>4.5mb) in the query