pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cannot COPY xmin?
3+ messages / 2 participants
[nested] [flat]

* Cannot COPY xmin?
@ 2024-02-27 18:20  Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Dominique Devienne @ 2024-02-27 18:20 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi. I just tried adding the xmin pseudo-column to my COPY,
and I get an error. Using a regular SELECT works fine.

Is that normal? that's a `COPY tab(xmin, cols...) ...`

Do I need to switch to a `COPY (select xmin, cols... from tab) ...` to
access xmin?

And if I do that, any performance implications for the COPY?
The reason I'm using COPY is performance and its "streaming" behavior.
(modulo the "big row" non-streaming issue from my as-yet unanswered post
from today).
I'm thus interested in how a `COPY tab(cols...)` differs from a `COPY
(select cols from tab ...)` in terms of implementation and performance.

Thanks, --DD

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Cannot COPY xmin?
@ 2024-02-27 18:26  Alvaro Herrera <alvherre@alvh.no-ip.org>
  parent: Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Alvaro Herrera @ 2024-02-27 18:26 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On 2024-Feb-27, Dominique Devienne wrote:

> Hi. I just tried adding the xmin pseudo-column to my COPY,
> and I get an error.

I suggest you find some other way to achieve whatever it is you think
you'll achieve with this.  It's not reliable and you'll regret it
eventually.  Maybe take a step back and let us know why you want to copy
xmin in the first place?

> Do I need to switch to a `COPY (select xmin, cols... from tab) ...` to
> access xmin?

If your ultimate objective is to shoot your feet, then yes, this will
work.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Cannot COPY xmin?
@ 2024-02-27 19:01  Dominique Devienne <ddevienne@gmail.com>
  parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Dominique Devienne @ 2024-02-27 19:01 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-general@lists.postgresql.org

On Tue, Feb 27, 2024 at 7:26 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2024-Feb-27, Dominique Devienne wrote:
>
> > Hi. I just tried adding the xmin pseudo-column to my COPY,
> > and I get an error.
>
> I suggest you find some other way to achieve whatever it is you think
> you'll achieve with this.  It's not reliable and you'll regret it
> eventually.  Maybe take a step back and let us know why you want to copy
> xmin in the first place?
>

OK, fair enough. I've read about xmin many times, in the doc and some
articles,
and I've aware at a high-level about PostgreSQL's MVCC and the possibility
of
wrap-around of xmin, but I'm definitely not an expert. So here goes, you
asked for it.

I'm replicating custom backups from our old architecture / backend, in our
new
PostgreSQL-based "backend". Our "backups" are a mix of disk files and DB
rows.
Sometimes we need whole schema tables/rows; other times we need subset of
tables
*and* subset of rows in those tables. I've looked at pg_dump and
pg_dumpbin, but for various
reasons I won't get into, I prefer to roll my own using COPY (to each his
own, right).
So far, so good. I'm getting good perf. It's flexible. My backups are
semi-structured,
semi-opaque, which allows to have easy visibility inside the backups w/o
restoring them.

One feature the old backups had, was incremental backups, after a base full
one.
There's where xmin comes in. I already have a hash of each row, as returned
by COPY,
so I can always do a content-based, exact / accurate comparison of the old
and new rows,
to know whether it needs to be part of the incremental backup or not. But
that's expensive,
because I still need to COPY TO the whole table, and compare rows
client-side. So I was
thinking of using the xmin of the old (as recorded in the backup) and new
(DB side) rows,
as an alternative way to determine what needs to be transferred across to
the client.

The idea being that xmin will change on UPDATEs.
I'd also need logic for INSERTs and DELETEs, of course.

If I can somehow record a global XID Epoch at backup time, I can detect
wrap-arounds.
But instead of testing inequality of the old and new xmin, I was thinking
of using strict equality,
accepting the false positive (and thus excess transfers), and relying of
the hash-based comparison
to avoid only the writing-to-disk part. (the chances of same xmin after a
wrap-around is just so low...)

So there you go. This is why I want to capture xmin of each row I backup.
If this is a bad idea, as you wrote, please explain why in details.
I'm very willing to learn where/how that scheme would go wrong.
But I'd need to understand the technical details. TIA.


> > Do I need to switch to a `COPY (select xmin, cols... from tab) ...` to
> > access xmin?
>
> If your ultimate objective is to shoot your feet, then yes, this will work.
>

Yes, I see that now. It does indeed work.

But I'd still like more details on the differences between the two forms of
COPY, please.
There are people here who know implementations details of COPY I'm sure.
And I'd very interesting to know whether it matters or not.
Yes, I can test and bench myself too. Still, I'd like insights into the
impl(s) please.

Thanks, --DD

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2024-02-27 19:01 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-27 18:20 Cannot COPY xmin? Dominique Devienne <ddevienne@gmail.com>
2024-02-27 18:26 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-02-27 19:01   ` Dominique Devienne <ddevienne@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox