Re: inconsistent tableoid handling in COPY WHERE clause

On Wed, 5 Nov 2025 at 16:06, Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> 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.
>
>
>

Hi!
Looks like this issue is currently discussed at [0]. Should we
continue here or there?

[0] https://www.postgresql.org/message-id/CACJufxHGGc25a2m%2B3Dezfctuykn51n5k3FJK6w3KSqfSFc5gvQ%40mail.gmail.com

--
Best regards,
Kirill Reshke