Horton

Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)

Masahiko Sawada <sawada.mshk@gmail.com>

View thread
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: torikoshia <torikoshia@oss.nttdata.com>, Alena Rybakina <lena.ribackina@yandex.ru>, Damir Belyalov <dam.bel07@gmail.com>, zhihuifan1213@163.com, Tom Lane <tgl@sss.pgh.pa.us>, Daniel Gustafsson <daniel@yesql.se>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>, anisimow.d@gmail.com, HukuToc@gmail.com, pgsql-hackers@lists.postgresql.org, Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Date: 2023-12-20T12:26:36Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: Fix COPY ON_ERROR option syntax synopsis.

  2. Disallow specifying ON_ERROR option without value.

  3. Rename COPY option from SAVE_ERROR_TO to ON_ERROR

  4. Fix spelling in notice

  5. Add new COPY option SAVE_ERROR_TO

On Wed, Dec 20, 2023 at 1:07 PM jian he <jian.universality@gmail.com> wrote:
>
> On Tue, Dec 19, 2023 at 9:14 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> >
> > The error table hub idea is still unclear to me. I assume that there
> > are error tables at least on each database. And an error table can
> > have error data that happened during COPY FROM, including malformed
> > lines. Do the error tables grow without bounds and the users have to
> > delete rows at some point? If so, who can do that? How can we achieve
> > that the users can see only errored rows they generated? And the issue
> > with logical replication also needs to be resolved. Anyway, if we go
> > this direction, we need to discuss the overall design.
> >
> > Regards,
> >
> > --
> > Masahiko Sawada
> > Amazon Web Services: https://aws.amazon.com
>
> Please check my latest attached POC.
> Main content is to build spi query, execute the spi query, regress
> test and regress output.

Why do we need to use SPI? I think we can form heap tuples and insert
them to the error table. Creating the error table also doesn't need to
use SPI.

>
> copy_errors one per schema.
> foo.copy_errors will be owned by the schema: foo owner.

It seems that the error table is created when the SAVE_ERROR is used
for the first time. It probably blocks concurrent COPY FROM commands
with SAVE_ERROR option to different tables if the error table is not
created yet.

>
> if you can insert to a table in that specific schema let's say foo,
> then you will get privilege to INSERT/DELETE/SELECT
> to foo.copy_errors.
> If you are not a superuser, you are only allowed to do
> INSERT/DELETE/SELECT on foo.copy_errors rows where USERID =
> current_user::regrole::oid.
> This is done via row level security.

I don't think it works. If the user is dropped, the user's oid could
be reused for a different user.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com