Search Postgresql Archives

Odd behavior observed

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux