Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

Unwanted rows modified when using UPDATE on partitioned table : CTID are not unique in partitioned tables

Open
  • Is something wrong?
#271 1 comment 1 reaction 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
cpp, postgresql
Domain
backend, databases

Research direction

Start with src/storage/postgres_update.cpp at lines 49-70, then run the supplied PostgreSQL partitioned-table reproduction against DuckDB 1.2.1. Trace how CTID values are used to target rows during UPDATE. Done means updating category A changes only the two category A rows, including when the table has multiple partitions.

Written by the indexing model from the issue text.

Description

What happens?

When Duckdb updates a Postgres partitioned table, it will also update rows that are not wanted.
After a bit of digging, i found that currently, ctid are used by Duckdb to target the rows to update.

https://github.com/duckdb/postgres_scanner/blob/03eaed75f0ec5500609b7a97aa05468493b229d1/src/storage/postgres_update.cpp#L49-L70

For standard table it works perfectly but not for partionned tables. Unfortunately, ctid are not unique for partitioned table. Instead, ctid are unique for each table that compose the partitioned table.

Here is my uneducated guess : This issue could be solved if the partionned keys are added to the final update statement.
It might be a bit tricky with multi level partition but doable.

To Reproduce

The postgresql partitioned table :

CREATE SCHEMA IF NOT EXISTS ctid_duplicate;

CREATE TABLE IF NOT EXISTS ctid_duplicate.my_table (
    id integer,
    category text,
    data text,
    PRIMARY KEY (id, category)
) PARTITION BY LIST (category);

CREATE TABLE IF NOT EXISTS ctid_duplicate.my_table_category_a
    PARTITION OF ctid_duplicate.my_table
    FOR VALUES IN ('A');

CREATE TABLE IF NOT EXISTS ctid_duplicate.my_table_category_b
    PARTITION OF ctid_duplicate.my_table
    FOR VALUES IN ('B');

CREATE TABLE IF NOT EXISTS ctid_duplicate.my_table_category_c
    PARTITION OF ctid_duplicate.my_table
    FOR VALUES IN ('C');

INSERT INTO ctid_duplicate.my_table (id, category, data)
VALUES
    (1, 'A', 'Data for category A'),
    (2, 'B', 'Data for category B'),
    (3, 'C', 'Data for category C'),
    (4, 'A', 'Another data for category A');

A simple simple select let us see that ctid aren't unique

SELECT ctid, id, category, "data"
FROM ctid_duplicate.my_table;

image

Now to actually test the issue with DuckDB :

ATTACH if not exists 'postgresql://sco:sco@localhost:5432/rece' as postgres_rece (type POSTGRES);

update postgres_rece.ctid_duplicate.my_table 
set data = 'CTID are duplicated'
where category = 'A';

Let's check again the table, only 2 rows should have been updated :

select * from postgres_rece.ctid_duplicate.my_table;

The results :
image

OS:

ubuntu 22.04

PostgreSQL Version:

15.8

DuckDB Version:

1.2.1

DuckDB Client:

Python

Full Name:

Simon Cotte

Affiliation:

AtolCD

Have you tried this on the latest main branch?
  • I agree
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
  • I agree
Dominant language
C++
Stars
373
Forks
112
Avg merge
9h 48m
Merged PRs (30d)
28

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from duckdb/duckdb-postgres

All issues in duckdb/duckdb-postgres

Similar issues

More C++ issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.