Horton

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

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Masahiko Sawada <sawada.mshk@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-20T04:07:38Z
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

Attachments

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.

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

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.

Since foo.copy_errors is mainly INSERT operations, if copy_errors grow
too much, that means your source file has many errors, it will take a
very long time to finish the whole COPY. maybe we can capture how many
errors encountered in another client.

I don't know how to deal with logic replication. looking for ideas.