Your problem is that when you add a new column to the_person
, its child, the_person_two
will have this field appended at the end of columns list (4th position), so after has_default
column. See:
db=> \d temp_person
Column | Type | Modifiers
-----------+-------------------+-----------------------------------------------------------------
person_id | integer | not null default nextval('temp_person_person_id_seq'::regclass)
name | character varying |
foo | text |
db=> \d temp_person_two
Column | Type | Modifiers
-------------+----------------------+-----------------------------------------------------------------
person_id | integer | not null default nextval('temp_person_person_id_seq'::regclass)
name | character varying |
has_default | character varying(4) | not null default 'en'::character varying
foo | text |
So, when you execute this:
INSERT INTO temp_person_two VALUES ( NEW.* );
PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two
(as NEW.*
will expand to three values), generating something similar to this:
INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );
So, temp_person_two.has_default
will get the value of NEW.foo
, which is NULL
in your case.
The solution is to simply expand the column names:
INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );
or, you could also use this:
INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );
But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.
EDIT:
So the conclusion and the lesson learned here is:
Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D
This will save you a lot of time solving problems like that in future.
There are many open ends in your question, but partitioning by customer could to be the way to go - especially if:
- you expect many customers,
- each of them could have tons of data ("tons" means much more than RAM cache size),
- most of their datasets will be mutually exclusive (each customer sees different subset of data).
RULEs or triggers are a performance overhead, and can be avoided.
Consider something along these lines:
BEGIN;
CREATE USER tenant1;
CREATE USER tenant2;
CREATE SCHEMA app;
CREATE SCHEMA tenant1;
CREATE SCHEMA tenant2;
CREATE TABLE app.objects_nonphysical(id int);
CREATE TABLE app.objects_physical(id int);
CREATE TABLE app.objects_mapping(id int);
CREATE TABLE tenant1.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant1.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant1.objects_mapping() INHERITS(app.objects_mapping);
CREATE TABLE tenant2.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant2.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant2.objects_mapping() INHERITS(app.objects_mapping);
GRANT USAGE ON SCHEMA tenant1 TO tenant1;
GRANT USAGE ON SCHEMA tenant2 TO tenant2;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant1 TO tenant1;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant2 TO tenant2;
/* TEST: simulate login as customer */
SET SESSION AUTHORIZATION tenant2;
/* No schema needed - default search_path works */
SELECT count(*) FROM objects_nonphysical;
ROLLBACK;
You do not need any triggers / rules to maintain it.
There are open ends here - that's just a draft... Some issues:
- PK, FK and indexes are not "inherited".
- even if you create them, the PK is not enforced on master table
- you can overcome this by using same sequence for all tenants
- obviously, application must be adjusted for this model
Best Answer
I was recently affected by this bug. I was able to work around by detaching the partitions, re-defining the parent table to enable logging, and then re-attaching the partitions. Continuing with the OP example:
Enable
LOGGED
:Notice that the parent table remains
UNLOGGED
:Manually, detach/attach partitions:
Verify that change worked: