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.
A few details are still not clear, but let's see, what we can do now.
First, having about 100M rows and 231 partitions sounds not that good. The resulting tables will be too small, in turn their number too high - I cannot tell the threshold, but at some point the query planning migt get too expensive. I think it is quite possible that yearly partitions would be enough. Alternatively, if you really want to fetch a whole month at a time, create monthly partitions.
Now to the actual problem.
It is not quite clear to me why you have rows in the parent table. The usual way of partitioning is that the parent is empty, and every row is redirected to one of the children.
At the same time, is you have an index on posted_at
of the parent table (as you have it on the children), finding rows in the parent based on the timestamp is easy.
On the other hand, while I'm not sure which column shared_parent_id
refers to, you can define an index on it, too - looking rows up based on this will be easy, too.
The only thing still has to be added is tell your query to look for parents in the parent table only. Let's have a look at a possible query:
WITH child_messages AS (
SELECT shared_parent_id, {other interesting columns}
FROM messages
WHERE posted_at {matches your needs}
)
SELECT *
FROM child_messages
UNION ALL
SELECT shared_parent_id, {other interesting columns}
FROM ONLY messages -- this way it does not go to the children
WHERE {unclear column} IN (SELECT shared_parent_id FROM child_messages);
The WITH
query may pick up rows from the parent, too - this you may or may not want, adjust the query accordingly.
Furthermore, the performance might not be ideal, in this case there is room for tweaking the query (eg. a JOIN
instead of the IN()
, pushing the query in the WITH
clause into a (sub)query and so on).
And a final notice: varchar(255)
is usually a sign of a value of unknown-before length - if you really want to constrain it, you may want to choose a meaningful limit. Otherwise, an unlimited varchar
(or text
) has a slight performance advantage in PostgreSQL over the limited ones. Furthermore, from your example it seems that shared_parent_id
is a number (integer) - use the best fitting type.
Best Answer
The following queries evaluate the expensive predicate just once for each
parent
row. To achieve this, the predicate is evaluated in a separate subquery. The first version uses a lateral join, the second an inner join. This meets the requirements from the question.