Thread: Single-Transaction Utility options

Single-Transaction Utility options

From
Simon Riggs
Date:
The following patches add a -N option to psql and pgrestore.

This option adds a BEGIN at the start and a COMMIT at the end of all
commands, causing all statements to be executed as a single transaction.

In pgrestore the -N option also forces the -e option: exit on error.

Passes make check on cvstip, plus some other basic testing.
No docs as yet.

Patches are completely independent of each other, the only connection
between them is at the conceptual level.

[Why have I done this? This is a precursor to the introduction of
WAL-bypass for COPY, when run in the same transaction in which a table
was created. Together, they will allow faster upgrades and restores.]

Best Regards, Simon Riggs

Attachment

Re: Single-Transaction Utility options

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> The following patches add a -N option to psql and pgrestore.

-N seems an entirely random name for the switch ... can't we do better?
I see that -t, -T, -s, -S, -x and -X are all taken, which lets out the
obvious choices ... but I'd rather have no single-letter abbreviation at
all than one that has zero relationship to the function of the switch.
Would -1 work, or just confuse people?

Also, I don't actually see any point to this in psql, as you can
always do
    begin;
    \i file
    end;
It's only pg_restore that you really need it for.  Dropping the psql
part of the patch might give us a little more maneuvering room as far
as the switch name goes.

            regards, tom lane

Re: Single-Transaction Utility options

From
Alvaro Herrera
Date:
Simon Riggs wrote:
> The following patches add a -N option to psql and pgrestore.
>
> This option adds a BEGIN at the start and a COMMIT at the end of all
> commands, causing all statements to be executed as a single transaction.

Why use it around the whole file and not only around that particular
table's operations?  Also why force it to activate the abort-on-error
mode?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Single-Transaction Utility options

From
Jaime Casanova
Date:
> Also, I don't actually see any point to this in psql, as you can
> always do
>        begin;
>        \i file
>        end;
> It's only pg_restore that you really need it for.  Dropping the psql
> part of the patch might give us a little more maneuvering room as far
> as the switch name goes.
>
>                        regards, tom lane
>

maybe it could be handy if you do this to migrate from one version to another...
pg_dumpall base | psql

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re: Single-Transaction Utility options

From
Simon Riggs
Date:
On Fri, 2005-12-16 at 13:59 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > The following patches add a -N option to psql and pgrestore.
>
> -N seems an entirely random name for the switch ... can't we do better?
> I see that -t, -T, -s, -S, -x and -X are all taken, which lets out the
> obvious choices ... but I'd rather have no single-letter abbreviation at
> all than one that has zero relationship to the function of the switch.

Almost. Stands for traNsaction....

> Would -1 work, or just confuse people?

That was my preference, I just thought it wouldn't be popular...

So I'll happily change that.

> Also, I don't actually see any point to this in psql, as you can
> always do
>     begin;
>     \i file
>     end;
> It's only pg_restore that you really need it for.  Dropping the psql
> part of the patch might give us a little more maneuvering room as far
> as the switch name goes.

Of course, you're right... and that is all the patch does in fact.

It seemed easier to do -1 than the SQL above, especially when doing it
from a single command line. But the main reason was to make all the
utilities work the same, if possible. Having different options on each
utility makes it easier to make mistakes, so I'd rather have the same
option everywhere that it could apply. I couldn't see any reason not to
do it, either.

Best Regards, Simon Riggs




Re: Single-Transaction Utility options

From
Simon Riggs
Date:
On Fri, 2005-12-16 at 16:04 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> > The following patches add a -N option to psql and pgrestore.
> >
> > This option adds a BEGIN at the start and a COMMIT at the end of all
> > commands, causing all statements to be executed as a single transaction.
>
> Why use it around the whole file and not only around that particular
> table's operations?

You could. That just behaves slightly differently.

Maybe we should have options for both?

> Also why force it to activate the abort-on-error
> mode?

For what reason would you want it to keep running?

Best Regards, Simon Riggs



Re: Single-Transaction Utility options

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> On Fri, 2005-12-16 at 13:59 -0500, Tom Lane wrote:
>> Would -1 work, or just confuse people?

> That was my preference, I just thought it wouldn't be popular...
> So I'll happily change that.

OK.  While you're at it, I didn't like the long name either ;-).
We do not use the abbrevation txn anywhere, so I think it's bad to
introduce it here.  I'd vote for spelling out --single-transaction,
or maybe just --single.  I believe you can abbreviate long switch names
to any unique prefix, so there's not really any more typing here.

            regards, tom lane

Re: Single-Transaction Utility options

From
Alvaro Herrera
Date:
Simon Riggs wrote:
> On Fri, 2005-12-16 at 16:04 -0300, Alvaro Herrera wrote:
> > Simon Riggs wrote:
> > > The following patches add a -N option to psql and pgrestore.
> > >
> > > This option adds a BEGIN at the start and a COMMIT at the end of all
> > > commands, causing all statements to be executed as a single transaction.
> >
> > Why use it around the whole file and not only around that particular
> > table's operations?
>
> You could. That just behaves slightly differently.
>
> Maybe we should have options for both?

Are they different enough to warrant having two switches?  IIRC the
point was to have the COPY in the same transaction as the CREATE TABLE,
right?  In what way is it worse to have each table in its own
transaction?

> > Also why force it to activate the abort-on-error
> > mode?
>
> For what reason would you want it to keep running?

To restore the rest of the tables in the dump I presume ... I mean, the
behaviors are orthogonal really (_if_ you take the stance that this
should be used on a per table basis rather than a file basis, that is.
Because if you abort the transaction then clearly there's no point in
keep running, as everything will be rejected by the server anyway.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Single-Transaction Utility options

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
>> Why use it around the whole file and not only around that particular
>> table's operations?

> You could. That just behaves slightly differently.

pg_dump does not always produce all the commands affecting a single
table together, so I don't think you can actually get the desired
results --- certainly it would be a nontrivial amount of work to get
any useful behavior like that.

            regards, tom lane

Re: Single-Transaction Utility options

From
Alvaro Herrera
Date:
Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> >> Why use it around the whole file and not only around that particular
> >> table's operations?
>
> > You could. That just behaves slightly differently.
>
> pg_dump does not always produce all the commands affecting a single
> table together, so I don't think you can actually get the desired
> results --- certainly it would be a nontrivial amount of work to get
> any useful behavior like that.

Ah, quite true.  I withdraw my comments then.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Single-Transaction Utility options

From
Simon Riggs
Date:
On Fri, 2005-12-16 at 15:56 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Fri, 2005-12-16 at 13:59 -0500, Tom Lane wrote:
> >> Would -1 work, or just confuse people?
>
> > That was my preference, I just thought it wouldn't be popular...
> > So I'll happily change that.
>
> OK.  While you're at it, I didn't like the long name either ;-).
> We do not use the abbrevation txn anywhere, so I think it's bad to
> introduce it here.  I'd vote for spelling out --single-transaction,
> or maybe just --single.  I believe you can abbreviate long switch names
> to any unique prefix, so there's not really any more typing here.

Changes as discussed. "singletransaction.patch" attached.

options:
-1 or --single-transaction

Functions work as described.

Best Regards, Simon Riggs

Attachment

Re: Single-Transaction Utility options

From
Peter Eisentraut
Date:
Simon Riggs wrote:
> Changes as discussed. "singletransaction.patch" attached.

I meant to ask, why is this not the default or only behavior?  Your
patch does not contain a documentation update, and so the user has no
information about why to use this option or not.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Single-Transaction Utility options

From
Simon Riggs
Date:
On Sat, 2005-12-17 at 20:03 +0100, Peter Eisentraut wrote:
> Simon Riggs wrote:
> > Changes as discussed. "singletransaction.patch" attached.
>
> I meant to ask, why is this not the default or only behavior?

Historically, it didn't work that way, so I hadn't thought to change
that behaviour. We could I suppose... but I'm happy with just an option
to do --single-transaction.

> Your
> patch does not contain a documentation update, and so the user has no
> information about why to use this option or not.

I was waiting for tech approval of the patch before writing the docs. A
doc patch is enclosed.

Best Regards, Simon Riggs

Attachment

Re: Single-Transaction Utility options

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> On Sat, 2005-12-17 at 20:03 +0100, Peter Eisentraut wrote:
>> I meant to ask, why is this not the default or only behavior?

> Historically, it didn't work that way, so I hadn't thought to change
> that behaviour. We could I suppose... but I'm happy with just an option
> to do --single-transaction.

I believe Peter's question was rhetorical: what he meant to point out
is that the documentation needs to explain what is the reason for having
this switch, ie, in what cases would you use it or not use it?
Just saying what it does isn't really adequate docs.

            regards, tom lane

Re: Single-Transaction Utility options

From
Peter Eisentraut
Date:
Tom Lane wrote:
> I believe Peter's question was rhetorical: what he meant to point out
> is that the documentation needs to explain what is the reason for
> having this switch, ie, in what cases would you use it or not use it?
> Just saying what it does isn't really adequate docs.

I once considered implementing this myself but found it infeasible for
some reason I don't remember.  Nevertheless I always thought that
having an atomic restore ought to be a non-optional feature.  Are there
situations where one would not want to use it?  (And if so, which one
is the more normal case?)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Single-Transaction Utility options

From
Simon Riggs
Date:
On Sun, 2005-12-18 at 14:04 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Sat, 2005-12-17 at 20:03 +0100, Peter Eisentraut wrote:
> >> I meant to ask, why is this not the default or only behavior?
>
> > Historically, it didn't work that way, so I hadn't thought to change
> > that behaviour. We could I suppose... but I'm happy with just an option
> > to do --single-transaction.
>
> I believe Peter's question was rhetorical: what he meant to point out
> is that the documentation needs to explain what is the reason for having
> this switch, ie, in what cases would you use it or not use it?
> Just saying what it does isn't really adequate docs.

Well, you know the reason: to allow pg_restore and psql take advantage
of the COPY optimization I'm just about to submit. When that patch is
accepted, I'll update these docs to explain that. But the two patches
are separable, since the -1 still has value anyway.

Best Regards, Simon Riggs


Re: Single-Transaction Utility options

From
Simon Riggs
Date:
On Sun, 2005-12-18 at 21:51 +0100, Peter Eisentraut wrote:
> Tom Lane wrote:
> > I believe Peter's question was rhetorical: what he meant to point out
> > is that the documentation needs to explain what is the reason for
> > having this switch, ie, in what cases would you use it or not use it?
> > Just saying what it does isn't really adequate docs.
>
> I once considered implementing this myself but found it infeasible for
> some reason I don't remember.  Nevertheless I always thought that
> having an atomic restore ought to be a non-optional feature.  Are there
> situations where one would not want to use it?  (And if so, which one
> is the more normal case?)

You're thinking is good. I guess if restores never failed, I'd be
inclined to agree 100%, but I'm at about 80% right now.

I'd say: if the patch is accepted technically, lets debate this point
more widely on -hackers.

Best Regards, Simon Riggs


Re: Single-Transaction Utility options

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> I once considered implementing this myself but found it infeasible for
> some reason I don't remember.  Nevertheless I always thought that
> having an atomic restore ought to be a non-optional feature.  Are there
> situations where one would not want to use it?

Absolutely.  As a nontrivial example, I *very* often load dumps sent to
me by other people which are full of GRANT/REVOKE commands referencing
users that don't exist in my installation.  Since, most of the time,
I don't particularly care about the ownership/privileges of the tables
involved, having to create those users would just be a PITA.

More generally, the pg_dump output has always been designed around the
assumption that failed commands are non-fatal.  Look at all those
unportable SET commands that we don't give you an option to omit.

            regards, tom lane

Re: Single-Transaction Utility options

From
Bruce Momjian
Date:
Patch applied.  Thanks.

---------------------------------------------------------------------------


Simon Riggs wrote:
> On Fri, 2005-12-16 at 15:56 -0500, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > On Fri, 2005-12-16 at 13:59 -0500, Tom Lane wrote:
> > >> Would -1 work, or just confuse people?
> >
> > > That was my preference, I just thought it wouldn't be popular...
> > > So I'll happily change that.
> >
> > OK.  While you're at it, I didn't like the long name either ;-).
> > We do not use the abbrevation txn anywhere, so I think it's bad to
> > introduce it here.  I'd vote for spelling out --single-transaction,
> > or maybe just --single.  I believe you can abbreviate long switch names
> > to any unique prefix, so there's not really any more typing here.
>
> Changes as discussed. "singletransaction.patch" attached.
>
> options:
> -1 or --single-transaction
>
> Functions work as described.
>
> Best Regards, Simon Riggs

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Single-Transaction Utility options

From
Bruce Momjian
Date:
Patch applied.  Thanks.

---------------------------------------------------------------------------


Simon Riggs wrote:
> On Sat, 2005-12-17 at 20:03 +0100, Peter Eisentraut wrote:
> > Simon Riggs wrote:
> > > Changes as discussed. "singletransaction.patch" attached.
> >
> > I meant to ask, why is this not the default or only behavior?
>
> Historically, it didn't work that way, so I hadn't thought to change
> that behaviour. We could I suppose... but I'm happy with just an option
> to do --single-transaction.
>
> > Your
> > patch does not contain a documentation update, and so the user has no
> > information about why to use this option or not.
>
> I was waiting for tech approval of the patch before writing the docs. A
> doc patch is enclosed.
>
> Best Regards, Simon Riggs

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073