[postgres-xl-bugs] According to ctid to perform delete operation prompt error

Koichi Suzuki koichi.dbms at gmail.com
Tue Jan 9 22:29:36 PST 2018

I'm afraid that success in XC-1.2.1 is just by chance.   XC (and XL) does
not guarantee ctid as a key for operation.  There are two possible cases of
this ctid issue:

1) replicated table: for the same raw, ctid may be different from node to
2) distributed table: because ctid is not distribution key, XL planner may
not be able to locate the target datanode.

The first error's cause may be 2).

The second error's direct cause is planner's limitation but anyway you will
encounter the same restriction.

If your query is just for maintenance purpose, you can perform the query
directly at each datanode, using "execute direct" after "set
xc_maintenance_mode to on".   Be sure that this operation is allowed only
to database superuser and you have to be responsible to any of the side

Good luck;

Koichi Suzuki

2018-01-10 11:25 GMT+09:00 刘攀 <leo.liu at postgresdata.com>:

> Hi PostgreSQL-XL,
> We deployed postgres-xl-9.5r1.6, but we ran into a problem with the delete
> operation:
> psql (PGXL 9.5r1.6, based on PG 9.5.8 (Postgres-XL 9.5r1.6))
> Type "help" for help.
> testdb=# create table trends(a text, itemid int);
> testdb=# delete from trends where ctid = any(array(select ctid from trends
> where itemid=26978 limit 5000));
> ERROR:  could not perform TID scan on remote relation
> testdb=#
> testdb=#
> testdb=# delete from trends where ctid in (select ctid from trends where
> itemid = 26978 limit 5000);
> ERROR:  could not plan this distributed delete
> DETAIL:  correlated or complex DELETE is currently not supported in
> Postgres-XL.
> testdb=#
> However, the same operation, but in PostgresXC-1.2.1 can perform
> successfully.
> These two delete operations are sent by zabbix software, because there is
> a lot of historical data in the environment, so hope to bulk delete by ctid.
> So I would like to ask, is there any way to achieve a similar operation.
> _______________________________________________
> postgres-xl-bugs mailing list
> postgres-xl-bugs at lists.postgres-xl.org
> http://lists.postgres-xl.org/listinfo.cgi/postgres-xl-bugs-postgres-xl.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-bugs-postgres-xl.org/attachments/20180110/d55c7786/attachment.html>

More information about the postgres-xl-bugs mailing list