Hello -
I am observing odd behavior that I am wondering if anyone here may have an
idea of how better to debug. I am suspecting a bug in the pgsql code, but
would be happy to find it is my error.
My specific situation is that I am using version 8.1.4 on a FreeBSD 6.1
AMD-64 system. I have a table with about 15000 records in it, which I
would like to add a new column to. The alter command shows success.
However, testing inserts reveals that the data for the new column is never
stored. Running the same exercise against the identical schema but with
only a few records finds that the test succeeds, and hence the problem is
not easily recreated. Experimentation has shown that the type of the
column doesn't matter. If the column has NOT NULL DEFAULT {value} added,
then it magically works.
A trivial example of the exercise is shown here:
create table foo (id bigserial);
insert into foo (id) values (8);
alter table foo add source_record bigint;
insert into foo (id,source_record) values (10,20);
select * from foo;
id | source_record
----+---------------
8 |
10 | 20
If I populate the table foo above with 15000 records, the exercise still
works OK. The only difference that I and others staring at this problem
see is that the real-world table contains a more complex definition,
included here for reference:
Column | Type | Modifiers
--------------------+-----------------------------+---------------------------------------------------------
id | bigint | not null default nextval('audit_logs_id_seq'::regclass)
timestamp | timestamp without time zone | not null default now()
notify_at | timestamp without time zone |
audit_log_type_id | bigint | not null
sdp_id | bigint |
customer_id | bigint |
customer_region_id | integer |
audit_format_id | bigint | not null
msg_args | text[] |
arg_names | text[] |
source_record | bigint |
Indexes:
"audit_logs_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"audit_logs_audit_format_id_fkey" FOREIGN KEY (audit_format_id) REFERENCES audit_formats(id) ON DELETE RESTRICT
"audit_logs_audit_log_type_id_fkey" FOREIGN KEY (audit_log_type_id) REFERENCES audit_log_types(id) ON DELETE RESTRICT
"audit_logs_audit_log_type_id_fkey1" FOREIGN KEY (audit_log_type_id) REFERENCES audit_log_types(id) ON DELETE RESTRICT
"audit_logs_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
"audit_logs_customer_region_id_fkey" FOREIGN KEY (customer_region_id) REFERENCES customer_regions(id) ON DELETE RESTRICT
"audit_logs_sdp_id_fkey" FOREIGN KEY (sdp_id) REFERENCES sdps(id) ON DELETE RESTRICT
Triggers:
audit_log_delete_trigger BEFORE DELETE ON audit_logs FOR EACH ROW EXECUTE PROCEDURE audit_log_delete_restrict()
Does anybody have a suggestion about how to debug this?
Thanks in advance - Marc
