inconsistent tableoid handling in COPY WHERE clause - Mailing list pgsql-hackers

I think the COPY FROM WHERE clause is handling the tableoid column in a 
way that is inconsistent with its usual definition.

Consider a partitioning hierarchy like

DROP TABLE IF EXISTS xp;

CREATE TABLE xp (a int, b int) PARTITION BY LIST (a);
CREATE TABLE xp1 PARTITION OF xp FOR VALUES IN (1);
CREATE TABLE xp2 PARTITION OF xp FOR VALUES IN (2);
CREATE TABLE xp3 PARTITION OF xp FOR VALUES IN (3);

Then you can use tableoid in a trigger to reveal the actual partition 
that an inserted row ended up in:

CREATE OR REPLACE FUNCTION tf() RETURNS TRIGGER LANGUAGE plpgsql
AS
$$
BEGIN
   RAISE NOTICE 'new.tableoid = %', NEW.tableoid;
   RETURN NEW;
END
$$;

CREATE TRIGGER tg1 AFTER INSERT ON xp FOR EACH ROW EXECUTE FUNCTION tf();

INSERT INTO xp VALUES (1, 11), (2, 22);

You can also write a check constraint that references tableoid to check 
what partition a row ends up in:

ALTER TABLE xp ADD CONSTRAINT xpc
   CHECK (tableoid IN ('xp1'::regclass, 'xp2'::regclass));

INSERT INTO xp VALUES (3, 33);  -- error: violates check constraint

So far so good.

You can also refer to tableoid in the WHERE clause of a COPY command, 
but that doesn't work correctly:

COPY xp FROM STDIN WHERE tableoid = 'xp2'::regclass;
1    111
2    222
\.

I would have expected that to copy only rows that are targeted for the 
xp2 partition, but in fact it does not copy anything.

This works:

COPY xp FROM STDIN WHERE tableoid = 'xp'::regclass;
1    111
2    222
\.

because tableoid in fact refers to the table named in the command, not 
the actual target partition.

That seems incorrect to me.

I don't see this documented one way or another, but there is a code 
comment in copyfrom.c at least mentioning tableoid in the context of the 
COPY WHERE clause:

     /*
      * Constraints and where clause might reference the tableoid column,
      * so (re-)initialize tts_tableOid before evaluating them.
      */
     myslot->tts_tableOid = 
RelationGetRelid(target_resultRelInfo->ri_RelationDesc);

This comment appeared in a not-quite-related refactoring (commit 
86b85044e82), it only said "Constraints might ..." beforehand.

Even the "Constraints might ..." variant of this is dubious, since as 
shown above, check constraints do have partition awareness, and there 
are places in nodeModifyTable.c that initialize tts_tableOid for that 
purpose.  Are there other constraints where tableoid can be used (and 
where this way of initializing it doesn't result in wrong behavior)?

I suggest that we should prohibit using tableoid in COPY WHERE clauses 
for the time being.  I don't know if there would be a way to make them 
work correctly at all, but most likely not in a backpatchable way anyway.

I also suggest that the above piece of code assigning tts_tableOid 
should be changed somehow.  Maybe just delete it, or set it to 
InvalidOid, because as it is it's misleading and probably wrong.