At 10:17 26/06/00 +0200, Zeugswetter Andreas SB wrote:
>
>A problem I see with an index at file end is, that you will need to read the
>file twice, and that may be very undesireable if e.g the backup is on tape
>or a compressed file.

The proposal has actually come a fairly long way after extensive
discussions with Tom Lane, and I have added the current plans at the end of
this message. The TOC-at-end problem is an issue that I am trying to deal
with; I am planning a 'custom' format that has the large parts (data dumps)
compressed, to avoid the need of compressing the entire file. This means
that you would not need to uncompress the entire file to get to the TOC, or
to restore just the schema. It also allows good random access to defns and
data. I'm also considering putting the dumped data at the end of the file,
but this has issues when you want to restore table data before defining
indexes, for example.

I must admit that I've been working on the assumption that people using
PostgreSQL don't have multi-GB (compressed) database dumps, so that (in
theory) a restore can be loaded onto disk from tape before being used. I
know this is pretty evil, but it will cover 95% of users. For those people
with huge backups, they will have to suffer tapes that go backward and
forwards a bit. From the details below, you will see that this is unavoidable.

Sanity Check: does fseek work on tapes? If not, what is the correct way to
read a particular block/byte from a file on a tape?

-----------------------------------------------------------
Updated Proposal:
-------------------------

For the sake of argument, call the new utilities pg_backup and pg_restore.

pg_backup
---------

Dump schema [and data] in OID order (to try to make restores sequential,
for when tar/tape storage is used). Each dumped item has a TOC entry which
includes the OID and description, and for those items for which we know
some dependencies (functions for types & aggregates; types for tables;
superclasses for classes; - any more?), it will also dump the dependency OIDs.

Each object (table defn, table data, function defn, type defn etc) is
dumped to a separate file/thing in the output file. The TOC entries go into
a separate file/thing (probably only one file/thing for the whole TOC).

The output scheme will be encapsulated, and in the initial version will be
a custom format (since I can't see an API for tar files), and a
dump-to-a-directory format. Future use of tar, DB, PostgreSQL or even a
Make file should not be excluded in the IO design. This last goal *may* not
be achieved, but I don't see why it can't be at this stage. Hopefully
someone with appropriate skills & motivation can do a tar archive 8-}.

The result of a pg_backup should be a single file with metadata and
optional data, along with whatever dependency and extra data is available
pg_backup, or provided by the DBA.


pg_restore
----------

Reads a backup file and dumps SQL suitable for sending to psql.

Options will include:

- No Data (--no-data? -nd? -s?)
- No metadata (--no-schema? -ns? -d?)
- Specification of items to dump from an input file; this allows custom
ordering AND custom selection of multiple items. Basically, I will allow
the user to dump part of the TOC, edit it, and tell pg_restore to use the
edited partial TOC. (--item-list=<file>? -l=<file>?)
- Dump TOC (--toc-only? -c?)

[Wish List]
- Data For a single table (--table=<name>? -t=<name>)
- Defn/Data for a single OID; (--oid=<oid>? -o=<oid>?)
- User definied dependencies. Allow the DB developer to specify once for
thier DB what the dependencies are, then use that files as a guide to the
restore process. (--deps=<file> -D=<file>)

pg_restore will use the same custom IO routines to allow IO to
tar/directory/custom files. In the first pass, I will do custom file IO.

If a user selects to restore the entire metadata, then it will be dumped
according to the defaul policy (OID order). If they select to specify the
items from an input file, then the file ordering is used.


-------

Typical backup procedure:

    pg_backup mydb mydb.bkp

or *maybe* 

    pg_backup mydb > mydb.bkp

BUT AFAIK, fseek does not work on STDOUT, and at the current time pg_backup
will use fseek.


Typical restore procedure:

    pg_restore mydb mydb.bkp | psql 

A user will be able to extract only the schema (-s), only the data (-d), a
specific table (-t=name), or even edit the object order and selection via:

    pg_restore --dump-toc mydb.bkp > mytoc.txt
    vi mytoc.txt   {ie. reorder TOC elements as per known dependency problems}
    pg_restore --item-list=mytoc.txt mydb.bkp | psql

FWIW, I envisage the ---dump-toc output to look like:

ID; FUNCTION FRED(INT4)
ID; TYPE MY_TYPE
ID; TABLE MY_TABLE
ID; DATA MY_TABLE
ID; INDEX MY_TABLE_IX1
...etc.

so editing and reordering the dump plan should not be too onerous.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Tue, 27 Jun 2000 00:30:30 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Proposal: More flexible backup/restore via
  pg_dump

> I must admit that I've been working on the assumption that people using
> PostgreSQL don't have multi-GB (compressed) database dumps, so that (in
> theory) a restore can be loaded onto disk from tape before being
> used.

Are you are also assuming that a backup fits in a single file,
i.e. that anyone with >2GB of backup has some sort of large file
support?

> Sanity Check: does fseek work on tapes? If not, what is the correct way to
> read a particular block/byte from a file on a tape?

As someone else answered: no.  You can't portably assume random access
to tape blocks.

> The output scheme will be encapsulated, and in the initial version will be
> a custom format (since I can't see an API for tar files)

You can use a standard format without there being a standard API.

Using either tar or cpio format as defined for POSIX would allow a lot
of us to understand your on-tape format with a very low burden on you
for documentation.  (If you do go this route you might want to think
about cpio format; it is less restrictive about filename length than
tar.)

There is also plenty of code lying around for reading and writing tar
and cpio formats that you could steal^H^H^H^H^H reuse.  The BSD pax
code should have a suitable license.

> pg_restore will use the same custom IO routines to allow IO to
> tar/directory/custom files. In the first pass, I will do custom file
> IO.

Presumably you'd expect this file I/O to be through some standard API
that other backends would also use?  I'd be interested to see this;
I've got code for an experimental libtar somewhere around here, so I
could offer comments at least.

> BUT AFAIK, fseek does not work on STDOUT, and at the current time pg_backup
> will use fseek.

It depends what fseek is whether it works on standard output or not.
If it's a pipe, no.  If it's a file, yes.  If it's a tape, no.  If
it's a ...

Not using fseek() would be a win if you can see a way to do it.

Regards,

Giles

Tue, 27 Jun 2000 07:00:41 +1000
Giles Lean <giles@nemeton.com.au>
Re: Proposal: More flexible backup/restore via pg_dump 
At 07:00 27/06/00 +1000, Giles Lean wrote:
>
>Are you are also assuming that a backup fits in a single file,
>i.e. that anyone with >2GB of backup has some sort of large file
>support?

That's up to the format used to save the database; in the case of the
'custom' format, yes. But that is the size after compression. This is not
substantially different to pg_dump's behaviour, except that pg_dump can be
piped to a tape drive...

The objective of the API components are to (a) make it very easy to add new
metadata to dump (eg. tablespaces), and (b) make it easy to add new output
formats (eg. tar archives). Basically the metadata dumping side makes one
call to register the thing to be saved, passing an optional function
pointer to dump data (eg. table contents) - this *could* even be used to
implement dumping of BLOBs.

The 'archiver' format provider must have some basic IO routines:
Read/WriteBuf and Read/WriteByte and has a number of hook functions which
it can use to output the data. It needs to provide at least one function
that actually writes data somewhere. It also has to provide the associated
function to read the data.

>
>As someone else answered: no.  You can't portably assume random access
>to tape blocks.

This is probably an issue. One of the motivations for this utility it to
allow partial restores (eg. table data for one table only), and
arbitrarilly ordered restores. But I may have a solution:

write the schema and TOC out at the start of the file/tape, then compressed
data with headers for each indicating which TOC item they correspond to.
This metadata can be loaded into /tmp, so fseek is possible. The actual
data restoration (assuming constraints are not defined [THIS IS A PROBLEM])
can be done by scanning the rest of the tape in it's own order since RI
will not be an issue. I think I'm happy with this.

But the problem is the constraints: AFAIK there is no 'ALTER TABLE ADD
CONSTRAINT...' so PK, FK, Not Null constraints have to be applied before
data load (*please* tell me I'm wrong). This also means that for large
databases, I should apply indexes to make PK/FK checks fast, but they will
slow data load.

Any ideas?


>> The output scheme will be encapsulated, and in the initial version will be
>> a custom format (since I can't see an API for tar files)
>
>You can use a standard format without there being a standard API.

Being a relatively lazy person, I was hoping to leave that as an excercise
for the reader...


>Using either tar or cpio format as defined for POSIX would allow a lot
>of us to understand your on-tape format with a very low burden on you
>for documentation.  (If you do go this route you might want to think
>about cpio format; it is less restrictive about filename length than
>tar.)

Tom Lane was also very favorably disposed to tar format. As I said above,
the archive interfaces should be pretty amenable to adding tar support -
it's just I'd like to get a version working with custom and directory based
formats to ensure the flexibility is there. As I see it, the 'backup to
directory' format should be easy to use as a basis for the 'backup to tar'
code.

The problem I have with tar is that it does not support random access to
the associated data. For reordering large backups, or (ultimately) single
BLOB extraction, this is a performance problem.

If you have a tar spec (or suitably licenced code), please mail it to me,
and I'll be able to make more informed comments.


>Presumably you'd expect this file I/O to be through some standard API
>that other backends would also use?  I'd be interested to see this;
>I've got code for an experimental libtar somewhere around here, so I
>could offer comments at least.

No problem: I should have a working version pretty soon. The API is
strictly purpose-built; it would be adaptable to a more general archibe
format, but as you say, tar is fine for most purposes.


>> BUT AFAIK, fseek does not work on STDOUT, and at the current time pg_backup
>> will use fseek.
>
>Not using fseek() would be a win if you can see a way to do it.

I think I probably can if I can work my way around RI problems.
Unfortunately the most effective solution will be to allow reording of the
table data restoration order, but that requires multiple passes through the
file to find the table data...


Bye for now,

Philip



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Tue, 27 Jun 2000 19:07:03 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Proposal: More flexible backup/restore via
  pg_dump

> If you have a tar spec (or suitably licenced code), please mail it to me,
> and I'll be able to make more informed comments.

The POSIX tar format is documented as part of the "Single Unix
Specification, version 2" which is available from:

http://www.opengroup.org
http://www.opengroup.org/publications/catalog/t912.htm

You can download the standard as HTML.  They keep moving the location
around so if the second URL breaks start from the top. They do want an
email address from you and they will spam this address with
invitations to conferences.  There's no such thing as a free lunch, I
guess.

For source code, any FreeBSD, NetBSD, or OpenBSD mirror will have pax
which understands both cpio and tar format and is BSD licensed:

ftp://ftp.au.netbsd.org/pub/NetBSD/NetBSD-current/src/bin/pax/

Regards,

Giles


Tue, 27 Jun 2000 19:38:35 +1000
Giles Lean <giles@nemeton.com.au>
Re: Proposal: More flexible backup/restore via pg_dump 
At 10:55 27/06/00 +0100, Peter Mount wrote:
>comments prefixed with PM...
>
>PM: So can most other Unix based formats. On the intranet server here, I
>pg_dump into /tmp, then include them in a tar piped to the tape drive.

Since you are using an intermediate file, there would be no change. fseek
is only an issue on tape drives and pipes, not files on disk. I suspect
that most people can afford the overhead of restoring the backup file to
disk before restoring the database, but it'd be nice to start out as
flexible as possible. In the back of my mind is the fact that when the WAL
and storage manager are going, raw data backups should be possible (and
fast) - but then again, maybe it's a pipe dream.


>PM: The problem with blobs hasn't been with dumping them (I have some Java
>code that does it into a compressed zip file), but restoring them - you
>can't create a blob with a specific OID, so any references in existing
>tables will break. I currently get round it by updating the tables after the
>restore - but it's ugly and easy to break :(

I assumed this would have to happen - hence why it will not be in the first
version. With all the TOAST stuff coming, and talk about the storage
manager, I still live in hope of a better BLOB system...


>PM: Having a set of api's (either accessible directly into the backend,
>and/or via some fastpath call) would be useful indeed.

By this I assume you mean APIs to get to database data, not backup data.


>
>This is probably an issue. One of the motivations for this utility it to
>allow partial restores (eg. table data for one table only), and
>arbitrarilly ordered restores. But I may have a solution:
>
>PM: That would be useful. I don't know about CPIO, but tar stores the TOC at
>the start of each file (so you can actually join two tar files together and
>still read all the files). In this way, you could put the table name as the
>"filename" in the header, so partial restores could be done.

Well, the way my plans work, I'll use either a munged OID, or a arbitrary
unique ID as the file name. All meaningful access has to go via the TOC.
But that's just a detail; the basic idea is what I'm implementing. 

It's very tempting to say tape restores are only possible in the order in
which the backup file was written ('pg_restore --stream' ?), and that
reordering/selection is only possible if you put the file on disk.


>
>PM: How about IOCTL's? I know that ArcServe on both NT & Unixware can seek
>through the tape, so there must be a way of doing it.

Maybe; I know BackupExec also does some kind of seek to update the TOC at
end of a backup (which is what I need to do). Then again, maybe that's just
a rewind. I don't want to get into custom tape formats...

Do we have any tape experts out there?


>PM: Tar can do this sequentially, which I've had to do many times over the
>years - restoring just one file from a tape, sequential access is probably
>the only way.

It's just nasty when the user reorders the restoration of tables and
metadata. In the worst cast it might be hundreds of scans of the tape. I'd
hate to have my name associated with something so unfriendly (even if it is
the operators fault).


>PM: The tar spec should be around somewhere - just be careful, the obvious
>source I was thinking of would be GPL'd, and we don't want to be poluted :-)

That was my problem. I've got some references now, and I'll look at them.
At least everything I've written so far can be put in PG.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Tue, 27 Jun 2000 20:30:58 +1000
Philip Warner <pjw@rhyme.com.au>
RE: Proposal: More flexible backup/restore via
  pg_dump

> Do we have any tape experts out there?

Don't even try.  The guaranteed portable subset of tape drive,
interface, device driver, and OS semantics is pretty limited.

I'm confident to stream one (tape) file of less than one tape capacity
to a drive and read it back sequentially.  These days you can probably
expect reliable end of media handling as well, but don't be too sure
what value errno will have when you do hit the end of a tape.

As soon as you start looking to deal with more advanced facilities you
will discover portability problems:

- autochanger interface differences
- head positioning on tapes with multiple files  (SysV v. BSD, anyone?)
- random access (yup, supported on some drives  ... probably all obsolete)
- "fast search marks" and similar

Some of these things can vary on the one OS if a tape drive is
connected to different interfaces, since different drivers may be
used.

BTW I'm not a tape expert.  The problems in practice may be greater or
lesser than I've suggested.

I would be trying really hard to work out a backup format that allows
a one pass restore.  Rummaging around in the database when making the
backup and using some scratch space at that time isn't too bad.  Using
scratch space upon restore is more problematic; restore problems are
traditionally found at the worst possible moment!

Regards,

Giles





Tue, 27 Jun 2000 23:40:37 +1000
Giles Lean <giles@nemeton.com.au>
Re: Proposal: More flexible backup/restore via pg_dump 

> Maybe; I know BackupExec also does some kind of seek to 
> update the TOC at
> end of a backup (which is what I need to do). Then again, 
> maybe that's just
> a rewind. I don't want to get into custom tape formats...
> 
> Do we have any tape experts out there?

Dont lock yourself in on the tape issue, it is the pipes that 
actually add value to the utility, and those can't rewind, seek
or whatever.

pipes can:
	compress, split output, write to storage managers, stripe output,
.....

I guess we would want two formats, one for pipe, and one for a standard
directory.

Andreas

Tue, 27 Jun 2000 15:52:01 +0200
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: Proposal: More flexible backup/restore via pg_dump 
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> pipes can:
> 	compress, split output, write to storage managers, stripe output,
> .....

Right, the thing we *really* want is to preserve the fact that pg_dump
can write its output to a pipeline ... and that a restore can read from
one.  If you can improve performance when you find you do have a
seekable source/destination file, fine, but the utilities must NOT
require it.

> I guess we would want two formats, one for pipe, and one for a standard
> directory.

At the risk of becoming tiresome, "tar" format is eminently pipeable...

			regards, tom lane

Tue, 27 Jun 2000 10:48:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
At 10:48 27/06/00 -0400, Tom Lane wrote:
>
>Right, the thing we *really* want is to preserve the fact that pg_dump
>can write its output to a pipeline ... and that a restore can read from
>one.  If you can improve performance when you find you do have a
>seekable source/destination file, fine, but the utilities must NOT
>require it.

OK, the limitation will have to be that reordering of *data* loads (as
opposed to metadata) will not be possible in piped data. This is only a
problem if RI constraints are loaded.

I *could* dump the compressed data to /tmp, but I would guess that in most
cases when the archive file is being piped it's because the file won't fit
on a local disk.

Does this sound reasonable?


>> I guess we would want two formats, one for pipe, and one for a standard
>> directory.
>
>At the risk of becoming tiresome, "tar" format is eminently pipeable...
>

No, it's good...I'll never feel guilty about asking for optimizer hints again.

More seriously, though, if I pipe a tar file, I still can't reorder the
*data* files without saving them to disk, which is what I want to avoid.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 28 Jun 2000 01:08:03 +1000
Philip Warner <pjw@rhyme.com.au>
Re: AW: Proposal: More flexible backup/restore via
  pg_dump
Philip Warner <pjw@rhyme.com.au> writes:
> More seriously, though, if I pipe a tar file, I still can't reorder the
> *data* files without saving them to disk, which is what I want to avoid.

True.  This is not an issue on the dump side, of course, since you can
choose what order you're going to write the tables in.  On the restore
side, you have no alternative but to restore the tables in the order
they appear on tape.  Of course the DBA can run the restore utility
more than once and extract a subset of tables each time, but I don't
see how the restore utility can be expected to do that for him.
(Except if it finds it does have the ability to seek in its input file,
but I dunno if it's a good idea to use that case for anything except
under-the-hood performance improvement, ie quickly skipping over the
data you don't need.  Features that don't work all the time are not
good in my book.)

Basically I think we want to assume that pg_dump will write the tables
in an order that's OK for restoring.  If we can arrange for RI checks
not to be installed until after all the data is loaded, this shouldn't
be a big problem, seems like.

			regards, tom lane

Tue, 27 Jun 2000 11:23:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
At 11:23 27/06/00 -0400, Tom Lane wrote:
>Of course the DBA can run the restore utility
>more than once and extract a subset of tables each time, but I don't
>see how the restore utility can be expected to do that for him.

Only works with seek (ie. a file).


>Features that don't work all the time are not
>good in my book.)

The *only* bit that won't work is being able to select the table data load
order, and I can fix that by writing tables that are wanted later to /tmp
if seek is unavailable. This *may* not be a problem, and probably should be
presented as an option to the user if restoring from non-seekable media.
Assuming that the backup was originally written to seekable media, I will
be able to tell the user how much space will be required, which should help.

I don't suppose anyone knows of a way of telling if a file handle supports
seek?


>Basically I think we want to assume that pg_dump will write the tables
>in an order that's OK for restoring.  If we can arrange for RI checks
>not to be installed until after all the data is loaded, this shouldn't
>be a big problem, seems like.

Part of the motivation for this utility was to allow DBAs to fix the
ordering at restore time, but otherwise I totally agree. Unfortunately I
don't think the RI checks can be delayed at this stage - can they? 

I don't suppose there is a 'disable constraints' command? Or the ability to
set all constraints as deferrred until commit-time?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 28 Jun 2000 03:40:25 +1000
Philip Warner <pjw@rhyme.com.au>
Re: AW: Proposal: More flexible backup/restore via
  pg_dump
On Tue, 27 Jun 2000, Philip Warner wrote:

> But the problem is the constraints: AFAIK there is no 'ALTER TABLE ADD
> CONSTRAINT...' so PK, FK, Not Null constraints have to be applied before
> data load (*please* tell me I'm wrong). This also means that for large
> databases, I should apply indexes to make PK/FK checks fast, but they will
> slow data load.

Actually, there is an ALTER TABLE ADD CONSTRAINT for foreign key
constraints.  Of course, if the existing data fails the constraint the
constraint doesn't get made. and if you're in a transaction, it'll force
a rollback.
In fact, you really can't always apply foreign key constraints at
schema reload time because you can have tables with circular
dependencies.  Those would have to be created after data load.


Tue, 27 Jun 2000 13:10:00 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Proposal: More flexible backup/restore via  pg_dump 

> I don't suppose anyone knows of a way of telling if a file handle supports
> seek?

The traditional method is to call lseek() and see what happens.

> Part of the motivation for this utility was to allow DBAs to fix the
> ordering at restore time, but otherwise I totally agree. Unfortunately I
> don't think the RI checks can be delayed at this stage - can they? 

The current pg_dump handles the data and then adds the constraints.

Otherwise there are "chicken and egg" problems where two tables have
mutual RI constraints.  Even at the tuple level two tuples can be
mutually dependent.

Regards,

Giles



Wed, 28 Jun 2000 08:48:52 +1000
Giles Lean <giles@nemeton.com.au>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
At 13:10 27/06/00 -0700, Stephan Szabo wrote:
>
>Actually, there is an ALTER TABLE ADD CONSTRAINT for foreign key
>constraints.
>

This is good to know; presumably at some stage in the future the rest will
be added, and the backup/restore can be amended to apply constraints after
data load. In the mean time, I suppose people with tape drives who need to
reorder the data load will have to make multiple passes (or copy the file
locally).

 


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 28 Jun 2000 13:05:21 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Proposal: More flexible backup/restore via
  pg_dump
At 08:48 28/06/00 +1000, Giles Lean wrote:
>
>> Part of the motivation for this utility was to allow DBAs to fix the
>> ordering at restore time, but otherwise I totally agree. Unfortunately I
>> don't think the RI checks can be delayed at this stage - can they? 
>
>The current pg_dump handles the data and then adds the constraints.

Not as far as I can see; that's what I want to do, bu there is no
implemented syntax for doing it. pg_dump simply dumps the table definition
with constraints (at least on 7.0.2).

>Otherwise there are "chicken and egg" problems where two tables have
>mutual RI constraints.  Even at the tuple level two tuples can be
>mutually dependent.

Absolutely. And AFAICT, these happen with pg_dump.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 28 Jun 2000 13:10:39 +1000
Philip Warner <pjw@rhyme.com.au>
Re: AW: Proposal: More flexible backup/restore via
  pg_dump
> At 08:48 28/06/00 +1000, Giles Lean wrote:
> >Otherwise there are "chicken and egg" problems where two tables have
> >mutual RI constraints.  Even at the tuple level two tuples can be
> >mutually dependent.
>
> Absolutely. And AFAICT, these happen with pg_dump.

This will happen for check constraints, but not for foreign key
constraints...
It actually adds the fk constraints later with CREATE CONSTRAINT TRIGGER
after the data dump is finished. And, if you do separate schema and data
dumps, the wierd statements at the top and bottom of the data dump turn
off triggers and then turn them on again (in the most painful way possible).
However those cases do not actually guarantee the validity of the data in
between.


Wed, 28 Jun 2000 12:06:33 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
At 12:06 28/06/00 -0700, Stephan Szabo wrote:
>
>This will happen for check constraints, but not for foreign key
>constraints...
>It actually adds the fk constraints later with CREATE CONSTRAINT TRIGGER
>after the data dump is finished. And, if you do separate schema and data
>dumps, the wierd statements at the top and bottom of the data dump turn
>off triggers and then turn them on again (in the most painful way possible).

Thanks for this information!

I had not seen those statements before; I have been mistakenly modifying
6.5.3 sources, not 7.0.2. I will incorporate them in my work. Is there any
way of also disabling all constraint checking while loading the data?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 29 Jun 2000 20:33:03 +1000
Philip Warner <pjw@rhyme.com.au>
Re: AW: Proposal: More flexible backup/restore via
  pg_dump
I think my previous message went dead...

> >This will happen for check constraints, but not for foreign key
> >constraints...
> >It actually adds the fk constraints later with CREATE CONSTRAINT TRIGGER
> >after the data dump is finished. And, if you do separate schema and data
> >dumps, the wierd statements at the top and bottom of the data dump turn
> >off triggers and then turn them on again (in the most painful way
possible).
>
> Thanks for this information!
>
> I had not seen those statements before; I have been mistakenly modifying
> 6.5.3 sources, not 7.0.2. I will incorporate them in my work. Is there any
> way of also disabling all constraint checking while loading the data?

Well, for unique you could remove/recreate the unique index.  NOT NULL is
probably
not worth bothering with.  Check constraints might be able to be turned off
for a table
by setting relchecks to 0 in the pg_class row and the resetting it after
data is loaded (sort
of like what we do on data only dumps for triggers).

The problem is that the create constraint trigger, playing with reltriggers
and playing with
relchecks doesn't guarantee that the data being loaded is correct.  And if
you remove
and recreate a unique index, you might not get the index back at the end,
and then
you've lost the information that there was supposed to be a unique or
primary key on
the table.

It might be a good idea to have some sort of pg_constraint (or whatever)
that holds
this data, since that would also make it easier to make the constraint
naming SQL compliant
(no duplicate constraint names within schema - that includes automatically
generated ones),
and it might help if we ever try to make deferrable check/primary key/etc...


Thu, 29 Jun 2000 11:40:13 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
"Stephan Szabo" <sszabo@kick.com> writes:
>> I had not seen those statements before; I have been mistakenly modifying
>> 6.5.3 sources, not 7.0.2. I will incorporate them in my work. Is there any
>> way of also disabling all constraint checking while loading the data?

> Well, for unique you could remove/recreate the unique index.  NOT NULL
> is probably not worth bothering with.  Check constraints might be able
> to be turned off for a table by setting relchecks to 0 in the pg_class
> row and the resetting it after data is loaded (sort of like what we do
> on data only dumps for triggers).

There's no need to disable NOT NULL, nor unique constraints either,
since those are purely local to a table --- if they're going to fail,
altering load order doesn't prevent it.  The things you need to worry
about are constraint expressions that cause references to other tables
(perhaps indirectly via a function).

If we had ALTER TABLE ADD CONSTRAINT then the problem would be largely
solved, I believe.  This should be a minor exercise --- the heavy
lifting is already done, because heap.c's AddRelationRawConstraints()
is already set up to be invokable on a pre-existing relation.  Also
the parser knows how to parse ALTER TABLE ADD CONSTRAINT ... I think
all that's missing is a few lines of glue code in command.c.

			regards, tom lane

Thu, 29 Jun 2000 15:24:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
> There's no need to disable NOT NULL, nor unique constraints either,
> since those are purely local to a table --- if they're going to fail,
> altering load order doesn't prevent it.  The things you need to worry
Is there a speed difference with doing a copy on a table with an index
versus creating
the index at the end?  I've been assuming that the latter was faster (and
that that was
part of what he wanted)

> about are constraint expressions that cause references to other tables
> (perhaps indirectly via a function).
Yeah, that's actually a big problem, since that's actually also a constraint
on the other table
as well, and as far as I know, we aren't yet constraining the other table.

> If we had ALTER TABLE ADD CONSTRAINT then the problem would be largely
> solved, I believe.  This should be a minor exercise --- the heavy
> lifting is already done, because heap.c's AddRelationRawConstraints()
> is already set up to be invokable on a pre-existing relation.  Also
> the parser knows how to parse ALTER TABLE ADD CONSTRAINT ... I think
> all that's missing is a few lines of glue code in command.c.

Does the AddRelationRawConstraints() check that the constraint is satisified
as well when
you add it?   It didn't look like it did, but I could be missing something.
That's another requirement of ALTER TABLE ADD CONSTRAINT.  That was the
bit I wasn't sure how to do for other generic constraints when I added the
foreign key one.



Thu, 29 Jun 2000 12:50:02 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: AW: Proposal: More flexible backup/restore via pg_dump
"Stephan Szabo" <sszabo@kick.com> writes:
>> If we had ALTER TABLE ADD CONSTRAINT then the problem would be largely
>> solved, I believe.  This should be a minor exercise --- the heavy
>> lifting is already done, because heap.c's AddRelationRawConstraints()
>> is already set up to be invokable on a pre-existing relation.

> Does the AddRelationRawConstraints() check that the constraint is
> satisified as well when you add it?  It didn't look like it did, but I
> could be missing something.

Oh, you're right, it does not.  So you'd first have to run through the
table and verify that the constraint holds for each existing tuple.
Doesn't seem like a big deal though...

			regards, tom lane

Thu, 29 Jun 2000 20:30:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
At 20:30 29/06/00 -0400, Tom Lane wrote:
>
>Oh, you're right, it does not.  So you'd first have to run through the
>table and verify that the constraint holds for each existing tuple.
>Doesn't seem like a big deal though...
>

Does this mean somebody is likely to do it? It'd certainly make
backup/restore more reliable.

I'm almost at the point of asking for testers with the revised
pg_dump/pg_restore, so I'll go with what I have for now, but it would make
life a lot less messy. Since the new version *allows* table restoration
intermixed with metadata, and in any order, I need to update pg_class
repeatedly (I assume there may be system triggers that need to execute when
metadata is changed).


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Fri, 30 Jun 2000 11:46:58 +1000
Philip Warner <pjw@rhyme.com.au>
Re: AW: Proposal: More flexible backup/restore via
  pg_dump
> At 20:30 29/06/00 -0400, Tom Lane wrote:
> >
> >Oh, you're right, it does not.  So you'd first have to run through the
> >table and verify that the constraint holds for each existing tuple.
> >Doesn't seem like a big deal though...
> >
> 
> Does this mean somebody is likely to do it? It'd certainly make
> backup/restore more reliable.

I'll take a stab at it. It might take me a while to get stuff working
but it shouldn't take too long before the beginnings are there.



Thu, 29 Jun 2000 19:15:21 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: AW: Proposal: More flexible backup/restore via pg_dump 
At 15:25 12/07/00 +0100, Peter Mount wrote:
>No he didn't, just I've been sort of lurking on this subject ;-)
>
>Actually, tar files are simply a small header, followed by the file's
>contents. To add another file, you simply write another header, and contents
>(which is why you can cat two tar files together and get a working file).
>
>http://www.goice.co.jp/member/mo/formats/tar.html has a nice brief
>description of the header.
>

Damn! I knew someone would call my bluff.

As you say, it looks remarkably simple.

A couple of questions:


    136     12 bytes  Modify time (in octal ascii)

    ...do you know the format of the date (seconds since 1970?).


    157    100 bytes  Linkname ('\0' terminated, 99 maxmum length)

    ...what's this? Is it the target for symlinks?


    329      8 bytes  Major device ID (in octal ascii)
    337      8 bytes  Minor device ID (in octal ascii)345    167 bytes
Padding

    ...and what should I set these to?

>As for a C api with a compatible licence, if needs must I'll write one to
>your spec (maidast should be back online in a couple of days, so I'll be
>back in business development wise).

If you're serious about the offer, I'd be happy. But, given how simple the
format is, I can probably tack in into place myself. 

There is a minor problem. Currently I compress the output stream as I
receive it from PG, and send it to the archive. I don't know how big it
will be until it is written. The custom output format can handle this, but
in streaming a tar file to tape, I have to know the file size first. This
means writing to /tmp. I supose that's OK, but I've been trying to avoid it.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 13 Jul 2000 01:11:04 +1000
Philip Warner <pjw@rhyme.com.au>
RE: RE: [HACKERS] pg_dump & blobs - editable dump?

> >http://www.goice.co.jp/member/mo/formats/tar.html has a nice brief

Best is to look at one of the actual standards, accessible via:

http://www.opengroup.org

The tar and cpio formats are in the pax specification.

>     136     12 bytes  Modify time (in octal ascii)
> 
>     ...do you know the format of the date (seconds since 1970?).

It's just 11 bytes plus \0 in tar's usual encode-this-as-octal format:

encode_octal(unsigned char *p, size_t n, unsigned long value)
{
    const unsigned char octal[] = "01234567";
    while (n) {
        *(p + --n) = octal[value & 07];
        value >>= 3;
    }
}

Warning: some values allowed by tar exceed the size of 'long' on a 32
bit platform.

>     157    100 bytes  Linkname ('\0' terminated, 99 maxmum length)
> 
>     ...what's this? Is it the target for symlinks?

Long pathnames get split into two pieces on a '/' as I recall.

The code I offered you previously has code to do this too; I
appreciate that the code is quite likely not what you want, but you
might consider looking at it or other tar/pax code to help you
interpret the standard.

>     329      8 bytes  Major device ID (in octal ascii)
>     337      8 bytes  Minor device ID (in octal ascii)
>     345    167 bytes  Padding
> 
>     ...and what should I set these to?

Zero.

> If you're serious about the offer, I'd be happy. But, given how simple the
> format is, I can probably tack in into place myself. 

For the very limited formats you want to create, that's probably
the easiest way.  You don't care about unpacking, GNU v. POSIX format,
device files, etc etc.

> There is a minor problem. Currently I compress the output stream as I
> receive it from PG, and send it to the archive. I don't know how big it
> will be until it is written. The custom output format can handle this, but
> in streaming a tar file to tape, I have to know the file size first. This
> means writing to /tmp. I supose that's OK, but I've been trying to
> avoid it.

I recommend you compress the whole stream, not the pieces.  Presumably
you can determine the size of the pieces you're backing up, and ending
with a .tar.gz (or whatever) file is more convenient to manage than a
.tar file of compressed pieces unless you really expect people to be
extracting individual files from the backup very often.

Having to pass everything through /tmp would be really unfortunate.

Regards,

Giles

Thu, 13 Jul 2000 07:58:43 +1000
Giles Lean <giles@nemeton.com.au>
Re: RE: [HACKERS] pg_dump & blobs - editable dump? 
At 07:58 13/07/00 +1000, Giles Lean wrote:
>
>I recommend you compress the whole stream, not the pieces.  Presumably
>you can determine the size of the pieces you're backing up, and ending
>with a .tar.gz (or whatever) file is more convenient to manage than a
>.tar file of compressed pieces unless you really expect people to be
>extracting individual files from the backup very often.
>
>Having to pass everything through /tmp would be really unfortunate.
>

The only things I compress are the table data and the blobs (ie. the big
things); unfortunately, the table data is of unknown uncompressed size. I
*could* do two 'COPY TO STDOUT' calls, just to get the size, but that seems
like a very bad idea.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 13 Jul 2000 10:58:06 +1000
Philip Warner <pjw@rhyme.com.au>
Re: RE: [HACKERS] pg_dump & blobs - editable dump? 

The reason this isn't working is because there is no concept of an inherent order of rows
in SQL. The only time things are ordered are when you explicitly request them to be,
according to a particular field. Thus, inserting a bunch of rows is exactly the same no
matter what order you insert them in, and you shouldn't assume anything about the
underlying mechanism of insertion and oids in your application.

What is the purpose you're trying to accomplish with this order by? No matter what, all the
rows where done='f' will be inserted, and you will not be left with any indication of that
order once the rows are in the todolist table.

-Ben

Andrew Selle wrote:

> Alright.  My situation is this.  I have a list of things that need to be done
> in a table called tasks.  I have a list of users who will complete these tasks.
> I want these users to be able to come in and "claim" the top 2 most recent tasks
> that have been added.  These tasks then get stored in a table called todolist
> which stores who claimed the task, the taskid, and when the task was claimed.
> For each time someone wants to claim some number of tasks, I want to do something
> like
>
> INSERT INTO todolist
>         SELECT taskid,'1',now()
>         FROM tasks
>         WHERE done='f'
>         ORDER BY submit DESC
>         LIMIT 2;
>
> Unfortunately, when I do this I get
> ERROR:  ORDER BY is not allowed in INSERT/SELECT
>
> The select works fine
>
> aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;
>  taskid | ?column? |          now
>  --------+----------+------------------------
>        4 | 1        | 2000-08-17 12:56:00-05
>        3 | 1        | 2000-08-17 12:56:00-05
> (2 rows)
>
> It seems to me, this is something I should do.  I was wondering if there
> is any reason why I can't do this?  I've thought of a couple of workarounds
> but they don't seem to be very clean:
>
> 1. Read the results of the select at the application level and reinsert into the
>     todolist table
>
> 2. Add two fields to the task table that keep track of userid and claimed.
>    This unfortunately clutters the main task table, and it loses the ability
>     to assign multiple people to the same task. It also requires looping at the
>     application level I think
>
> 3. use a temporary table with a SELECT INTO statement and then copy the contents
>    of the temporary table into the table I want it in todolist
>
> Below are the table creation statements for this sample...
>
> -Andy
>
> CREATE TABLE tasks (
>         taskid  int4,
>         title   varchar(64),
>         descr   text,
>         submit  datetime,
>         done    boolean
> );
>
> CREATE TABLE users (
>         userid  int4,
>         name    varchar(32)
> );
>
> CREATE TABLE todolist (
>         taskid  int4,
>         userid  int4,
>         claimed datetime
> );


Thu, 17 Aug 2000 10:23:17 -0400
Ben Adida <ben@openforce.net>
Re: Inserting a select statement result into another table
Alright.  My situation is this.  I have a list of things that need to be done
in a table called tasks.  I have a list of users who will complete these tasks.
I want these users to be able to come in and "claim" the top 2 most recent tasks
that have been added.  These tasks then get stored in a table called todolist
which stores who claimed the task, the taskid, and when the task was claimed.
For each time someone wants to claim some number of tasks, I want to do something
like

INSERT INTO todolist 
	SELECT taskid,'1',now() 
	FROM tasks 
	WHERE done='f' 
	ORDER BY submit DESC 
	LIMIT 2;

Unfortunately, when I do this I get 
ERROR:  ORDER BY is not allowed in INSERT/SELECT

The select works fine

aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;
 taskid | ?column? |          now           
 --------+----------+------------------------
       4 | 1        | 2000-08-17 12:56:00-05
       3 | 1        | 2000-08-17 12:56:00-05
(2 rows)

It seems to me, this is something I should do.  I was wondering if there
is any reason why I can't do this?  I've thought of a couple of workarounds
but they don't seem to be very clean:

1. Read the results of the select at the application level and reinsert into the
    todolist table

2. Add two fields to the task table that keep track of userid and claimed.
   This unfortunately clutters the main task table, and it loses the ability
    to assign multiple people to the same task. It also requires looping at the
    application level I think

3. use a temporary table with a SELECT INTO statement and then copy the contents
   of the temporary table into the table I want it in todolist

Below are the table creation statements for this sample...

-Andy


CREATE TABLE tasks (
	taskid	int4,
	title	varchar(64),
	descr	text,
	submit	datetime,
	done	boolean
);

CREATE TABLE users (
	userid	int4,
	name	varchar(32)
);

CREATE TABLE todolist (
	taskid	int4,
	userid	int4,
	claimed	datetime
);




Thu, 17 Aug 2000 13:05:17 -0500
Andrew Selle <aselle@upl.cs.wisc.edu>
Inserting a select statement result into another table

He does ask a legitimate question though. If you are going to have a
LIMIT feature (which of course is not pure SQL), there seems no reason
you shouldn't be able to insert the result into a table.

Ben Adida wrote:
> 
> The reason this isn't working is because there is no concept of an inherent order of rows
> in SQL. The only time things are ordered are when you explicitly request them to be,
> according to a particular field. Thus, inserting a bunch of rows is exactly the same no
> matter what order you insert them in, and you shouldn't assume anything about the
> underlying mechanism of insertion and oids in your application.
> 
> What is the purpose you're trying to accomplish with this order by? No matter what, all the
> rows where done='f' will be inserted, and you will not be left with any indication of that
> order once the rows are in the todolist table.
> 
> -Ben
> 
> Andrew Selle wrote:
> 
> > Alright.  My situation is this.  I have a list of things that need to be done
> > in a table called tasks.  I have a list of users who will complete these tasks.
> > I want these users to be able to come in and "claim" the top 2 most recent tasks
> > that have been added.  These tasks then get stored in a table called todolist
> > which stores who claimed the task, the taskid, and when the task was claimed.
> > For each time someone wants to claim some number of tasks, I want to do something
> > like
> >
> > INSERT INTO todolist
> >         SELECT taskid,'1',now()
> >         FROM tasks
> >         WHERE done='f'
> >         ORDER BY submit DESC
> >         LIMIT 2;
> >
> > Unfortunately, when I do this I get
> > ERROR:  ORDER BY is not allowed in INSERT/SELECT
> >
> > The select works fine
> >
> > aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;
> >  taskid | ?column? |          now
> >  --------+----------+------------------------
> >        4 | 1        | 2000-08-17 12:56:00-05
> >        3 | 1        | 2000-08-17 12:56:00-05
> > (2 rows)
> >
> > It seems to me, this is something I should do.  I was wondering if there
> > is any reason why I can't do this?  I've thought of a couple of workarounds
> > but they don't seem to be very clean:
> >
> > 1. Read the results of the select at the application level and reinsert into the
> >     todolist table
> >
> > 2. Add two fields to the task table that keep track of userid and claimed.
> >    This unfortunately clutters the main task table, and it loses the ability
> >     to assign multiple people to the same task. It also requires looping at the
> >     application level I think
> >
> > 3. use a temporary table with a SELECT INTO statement and then copy the contents
> >    of the temporary table into the table I want it in todolist
> >
> > Below are the table creation statements for this sample...
> >
> > -Andy
> >
> > CREATE TABLE tasks (
> >         taskid  int4,
> >         title   varchar(64),
> >         descr   text,
> >         submit  datetime,
> >         done    boolean
> > );
> >
> > CREATE TABLE users (
> >         userid  int4,
> >         name    varchar(32)
> > );
> >
> > CREATE TABLE todolist (
> >         taskid  int4,
> >         userid  int4,
> >         claimed datetime
> > );

Fri, 18 Aug 2000 09:34:33 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: Inserting a select statement result into another table

Well, If I'm reading the spec correctly,
INSERT INTO references a query expression 
which doesn't include ORDER BY as an option, so this
is even less SQL since we're actually not just changing
it to allow our non-standard bit, but we're changing
a piece that is explicitly not allowed in the spec.

That being said, I also think it's probably a useful extension
given the LIMIT clause.

On Fri, 18 Aug 2000, Chris Bitmead wrote:

> 
> He does ask a legitimate question though. If you are going to have a
> LIMIT feature (which of course is not pure SQL), there seems no reason
> you shouldn't be able to insert the result into a table.


Thu, 17 Aug 2000 17:27:08 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Inserting a select statement result into another table
Chris Bitmead wrote:

> He does ask a legitimate question though. If you are going to have a
> LIMIT feature (which of course is not pure SQL), there seems no reason
> you shouldn't be able to insert the result into a table.

Yes, that's true, I had missed that the first time around.

-Ben


Thu, 17 Aug 2000 20:53:08 -0400
Ben Adida <ben@openforce.net>
Re: Inserting a select statement result into another table
At 09:34 18/08/00 +1000, Chris Bitmead wrote:
>
>He does ask a legitimate question though. If you are going to have a
>LIMIT feature (which of course is not pure SQL), there seems no reason
>you shouldn't be able to insert the result into a table.

This feature is supported by two commercial DBs: Dec/RDB and SQL/Server. I
have no idea if Oracle supports it, but it is such a *useful* feature that
I would be very surprised if it didn't.


>Ben Adida wrote:
>> 
>> What is the purpose you're trying to accomplish with this order by? No
matter what, all the
>> rows where done='f' will be inserted, and you will not be left with any
indication of that
>> order once the rows are in the todolist table.

I don't know what his *purpose* was, but the query should only insert the
first two rows from the select bacause of the limit).

>> Andrew Selle wrote:
>> 
>> > Alright.  My situation is this.  I have a list of things that need to
be done
>> > in a table called tasks.  I have a list of users who will complete
these tasks.
>> > I want these users to be able to come in and "claim" the top 2 most
recent tasks
>> > that have been added.  These tasks then get stored in a table called
todolist
>> > which stores who claimed the task, the taskid, and when the task was
claimed.
>> > For each time someone wants to claim some number of tasks, I want to
do something
>> > like
>> >
>> > INSERT INTO todolist
>> >         SELECT taskid,'1',now()
>> >         FROM tasks
>> >         WHERE done='f'
>> >         ORDER BY submit DESC
>> >         LIMIT 2;

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Fri, 18 Aug 2000 10:58:35 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Inserting a select statement result into another
  table
Can I ask on a status?

> At 10:17 26/06/00 +0200, Zeugswetter Andreas SB wrote:
> >
> >A problem I see with an index at file end is, that you will need to read the
> >file twice, and that may be very undesireable if e.g the backup is on tape
> >or a compressed file.
> 
> The proposal has actually come a fairly long way after extensive
> discussions with Tom Lane, and I have added the current plans at the end of
> this message. The TOC-at-end problem is an issue that I am trying to deal
> with; I am planning a 'custom' format that has the large parts (data dumps)
> compressed, to avoid the need of compressing the entire file. This means
> that you would not need to uncompress the entire file to get to the TOC, or
> to restore just the schema. It also allows good random access to defns and
> data. I'm also considering putting the dumped data at the end of the file,
> but this has issues when you want to restore table data before defining
> indexes, for example.
> 
> I must admit that I've been working on the assumption that people using
> PostgreSQL don't have multi-GB (compressed) database dumps, so that (in
> theory) a restore can be loaded onto disk from tape before being used. I
> know this is pretty evil, but it will cover 95% of users. For those people
> with huge backups, they will have to suffer tapes that go backward and
> forwards a bit. From the details below, you will see that this is unavoidable.
> 
> Sanity Check: does fseek work on tapes? If not, what is the correct way to
> read a particular block/byte from a file on a tape?
> 
> -----------------------------------------------------------
> Updated Proposal:
> -------------------------
> 
> For the sake of argument, call the new utilities pg_backup and pg_restore.
> 
> pg_backup
> ---------
> 
> Dump schema [and data] in OID order (to try to make restores sequential,
> for when tar/tape storage is used). Each dumped item has a TOC entry which
> includes the OID and description, and for those items for which we know
> some dependencies (functions for types & aggregates; types for tables;
> superclasses for classes; - any more?), it will also dump the dependency OIDs.
> 
> Each object (table defn, table data, function defn, type defn etc) is
> dumped to a separate file/thing in the output file. The TOC entries go into
> a separate file/thing (probably only one file/thing for the whole TOC).
> 
> The output scheme will be encapsulated, and in the initial version will be
> a custom format (since I can't see an API for tar files), and a
> dump-to-a-directory format. Future use of tar, DB, PostgreSQL or even a
> Make file should not be excluded in the IO design. This last goal *may* not
> be achieved, but I don't see why it can't be at this stage. Hopefully
> someone with appropriate skills & motivation can do a tar archive 8-}.
> 
> The result of a pg_backup should be a single file with metadata and
> optional data, along with whatever dependency and extra data is available
> pg_backup, or provided by the DBA.
> 
> 
> pg_restore
> ----------
> 
> Reads a backup file and dumps SQL suitable for sending to psql.
> 
> Options will include:
> 
> - No Data (--no-data? -nd? -s?)
> - No metadata (--no-schema? -ns? -d?)
> - Specification of items to dump from an input file; this allows custom
> ordering AND custom selection of multiple items. Basically, I will allow
> the user to dump part of the TOC, edit it, and tell pg_restore to use the
> edited partial TOC. (--item-list=<file>? -l=<file>?)
> - Dump TOC (--toc-only? -c?)
> 
> [Wish List]
> - Data For a single table (--table=<name>? -t=<name>)
> - Defn/Data for a single OID; (--oid=<oid>? -o=<oid>?)
> - User definied dependencies. Allow the DB developer to specify once for
> thier DB what the dependencies are, then use that files as a guide to the
> restore process. (--deps=<file> -D=<file>)
> 
> pg_restore will use the same custom IO routines to allow IO to
> tar/directory/custom files. In the first pass, I will do custom file IO.
> 
> If a user selects to restore the entire metadata, then it will be dumped
> according to the defaul policy (OID order). If they select to specify the
> items from an input file, then the file ordering is used.
> 
> 
> -------
> 
> Typical backup procedure:
> 
>     pg_backup mydb mydb.bkp
> 
> or *maybe* 
> 
>     pg_backup mydb > mydb.bkp
> 
> BUT AFAIK, fseek does not work on STDOUT, and at the current time pg_backup
> will use fseek.
> 
> 
> Typical restore procedure:
> 
>     pg_restore mydb mydb.bkp | psql 
> 
> A user will be able to extract only the schema (-s), only the data (-d), a
> specific table (-t=name), or even edit the object order and selection via:
> 
>     pg_restore --dump-toc mydb.bkp > mytoc.txt
>     vi mytoc.txt   {ie. reorder TOC elements as per known dependency problems}
>     pg_restore --item-list=mytoc.txt mydb.bkp | psql
> 
> FWIW, I envisage the ---dump-toc output to look like:
> 
> ID; FUNCTION FRED(INT4)
> ID; TYPE MY_TYPE
> ID; TABLE MY_TABLE
> ID; DATA MY_TABLE
> ID; INDEX MY_TABLE_IX1
> ...etc.
> 
> so editing and reordering the dump plan should not be too onerous.
> 
> 
> ----------------------------------------------------------------
> Philip Warner                    |     __---_____
> Albatross Consulting Pty. Ltd.   |----/       -  \
> (A.C.N. 008 659 498)             |          /(@)   ______---_
> Tel: (+61) 0500 83 82 81         |                 _________  \
> Fax: (+61) 0500 83 82 82         |                 ___________ |
> Http://www.rhyme.com.au          |                /           \|
>                                  |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 10 Oct 2000 20:49:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal: More flexible backup/restore via  pg_dump
At 20:49 10/10/00 -0400, Bruce Momjian wrote:
>Can I ask on a status?

Done.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 11 Oct 2000 12:09:08 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Proposal: More flexible backup/restore via
  pg_dump
> 
> Well, If I'm reading the spec correctly,
> INSERT INTO references a query expression 
> which doesn't include ORDER BY as an option, so this
> is even less SQL since we're actually not just changing
> it to allow our non-standard bit, but we're changing
> a piece that is explicitly not allowed in the spec.
> 
> That being said, I also think it's probably a useful extension
> given the LIMIT clause.
> 

> On Fri, 18 Aug 2000, Chris Bitmead wrote:
> 
> > 
> > He does ask a legitimate question though. If you are going to have a
> > LIMIT feature (which of course is not pure SQL), there seems no reason
> > you shouldn't be able to insert the result into a table.
> 
> 

This is an interesting idea.  We don't allow ORDER BY in INSERT INTO ...
SELECT because it doesn't make any sense, but it does make sense if
LIMIT is used:

	ctest=> create table x (Y oid);
	CREATE
	test=> insert into x 
	test-> select oid from pg_class order by oid limit 1;
	ERROR:  LIMIT is not supported in subselects

Added to TODO:

	Allow ORDER BY...LIMIT in INSERT INTO ... SELECT

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 12 Oct 2000 13:23:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Inserting a select statement result into another table
Hello,

just my $0.02...
If I do
insert into x
  select * from y limit 10;

I will get all of rows in x inserted, not just 10...
I already wrote about this... But did not get any useful reply.

> This is an interesting idea.  We don't allow ORDER BY in INSERT INTO ...
> SELECT because it doesn't make any sense, but it does make sense if
> LIMIT is used:
>
> 	ctest=> create table x (Y oid);
> 	CREATE
> 	test=> insert into x
> 	test-> select oid from pg_class order by oid limit 1;
> 	ERROR:  LIMIT is not supported in subselects
>
> Added to TODO:
>
> 	Allow ORDER BY...LIMIT in INSERT INTO ... SELECT

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Fri, 13 Oct 2000 11:02:29 +0700
Denis Perchine <dyp@perchine.com>
Re: Inserting a select statement result into another table
I reported this problem about 3 weeks ago or even more. The problem hasn't
disappeared yet. In 7.1beta4 if I use pg_dump with -a switch together, I
get each CREATE SEQUENCE twice. I suspected if this is an installation
problem at my place but now I think it maybe isn't.

You answered that noone experienced anything like this. Here I get this
behaviour with the most simple table as well.

Could you please help? TIA, Zoltan

------------------------------------------------------------------------
Zoltan Kovacs
system designing leader at Trend Ltd, J\'aszber\'eny
assistant teacher in mathematics at Bolyai Institute, Szeged

http://www.trendkft.hu
http://www.math.u-szeged.hu/~kovzol



Tue, 6 Mar 2001 16:07:36 +0100 (CET)
kovacsz <zoli@pc10.radnoti-szeged.sulinet.hu>
pg_dump writes SEQUENCEs twice with -a 
At 16:07 6/03/01 +0100, kovacsz wrote:
>The problem hasn't
>disappeared yet. In 7.1beta4...

As per an earlier message today, the problem is fixed in CVS


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 07 Mar 2001 03:00:01 +1100
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump writes SEQUENCEs twice with -a 
kovacsz wrote:
  >I reported this problem about 3 weeks ago or even more. The problem hasn't
  >disappeared yet. In 7.1beta4 if I use pg_dump with -a switch together, I
  >get each CREATE SEQUENCE twice. I suspected if this is an installation
  >problem at my place but now I think it maybe isn't.
  >
  >You answered that noone experienced anything like this. Here I get this
  >behaviour with the most simple table as well.

I get the same error using 7.1beta4.  See this example for a 1 table database:

olly@linda$ pg_dump -a junk
--
-- Selected TOC Entries:
--
\connect - olly
--
-- TOC Entry ID 1 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE Owner: olly
--

CREATE SEQUENCE "basket_id_seq" start 1 increment 1 maxvalue 2147483647 
minvalue 1  cache 1 ;

--
-- TOC Entry ID 3 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE Owner: olly
--

CREATE SEQUENCE "basket_id_seq" start 1 increment 1 maxvalue 2147483647 
minvalue 1  cache 1 ;

--
-- Data for TOC Entry ID 5 (OID 2091639) TABLE DATA basket
--

-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'basket';
COPY "basket"  FROM stdin;
1	2001-03-04 19:59:58+00
\.
-- Enable triggers
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, 
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'basket'  GROUP 
BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP 
WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;

--
-- TOC Entry ID 2 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE SET Owner: 
--

SELECT setval ('"basket_id_seq"', 1, 't');

--
-- TOC Entry ID 4 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE SET Owner: 
--

SELECT setval ('"basket_id_seq"', 1, 't');

olly@linda$ 

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Go ye therefore, and teach all nations, baptizing them
      in the name of the Father, and of the Son, and of the 
      Holy Ghost; Teaching them to observe all things  
      whatsoever I have commanded you; and, lo, I am with 
      you alway, even unto the end of the world. Amen."     
                     Matthew 28:19,20 



Wed, 07 Mar 2001 20:48:32 +0000
"Oliver Elphick" <olly@lfix.co.uk>
Re: pg_dump writes SEQUENCEs twice with -a 
At 20:48 7/03/01 +0000, Oliver Elphick wrote:
>kovacsz wrote:
>  >
>  >You answered that noone experienced anything like this. Here I get this
>  >behaviour with the most simple table as well.
>

Is there a problem with the lists? I reveived Zoltan's message twice, and
now this one that seems to indicate my earlier reply has not been seen.

FWIW, this is fixed in CVS.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 08 Mar 2001 10:10:04 +1100
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump writes SEQUENCEs twice with -a 
Philip Warner wrote:
  >At 20:48 7/03/01 +0000, Oliver Elphick wrote:
  >>kovacsz wrote:
  >>  >
  >>  >You answered that noone experienced anything like this. Here I get this
  >>  >behaviour with the most simple table as well.
  >>
  >
  >Is there a problem with the lists? I reveived Zoltan's message twice, and
  >now this one that seems to indicate my earlier reply has not been seen.

No I hadn't (and still haven't) seen your earlier reply.


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Go ye therefore, and teach all nations, baptizing them
      in the name of the Father, and of the Son, and of the 
      Holy Ghost; Teaching them to observe all things  
      whatsoever I have commanded you; and, lo, I am with 
      you alway, even unto the end of the world. Amen."     
                     Matthew 28:19,20 



Wed, 07 Mar 2001 23:21:56 +0000
"Oliver Elphick" <olly@lfix.co.uk>
Re: pg_dump writes SEQUENCEs twice with -a 
On Thu, 8 Mar 2001, Philip Warner wrote:

> At 20:48 7/03/01 +0000, Oliver Elphick wrote:
> >kovacsz wrote:
> >  >
> >  >You answered that noone experienced anything like this. Here I get this
> >  >behaviour with the most simple table as well.
> >
> 
> Is there a problem with the lists? I reveived Zoltan's message twice, and
> now this one that seems to indicate my earlier reply has not been seen.
> 
> FWIW, this is fixed in CVS.
Thank you, I checked the CVS (and I downloaded the new sources and tried 
to compile -- without success, I should download the whole stuff IMHO,
e.g. postgres_fe.h is quite new to 7.1beta4 and the old sources may be
incompatible with the new ones).

Zoltan 


Thu, 8 Mar 2001 08:17:31 +0100 (CET)
Kovacs Zoltan <kovzol@server.math.u-szeged.hu>
Re: pg_dump writes SEQUENCEs twice with -a 
At 11:06 AM 2/10/2002 -0400, Tom Lane wrote:
>It needs to get done; AFAIK no one has stepped up to do it.  Do you want
>to?

My limited reading of off_t stuff now suggests that it would be brave to 
assume it is even a simple 64 bit number (or even 3 32 bit numbers). One 
alternative, which I am not terribly fond of, is to have pg_dump write 
multiple files - when we get to 1 or 2GB, we just open another file, and 
record our file positions as a (file number, file position) pair. Low tech, 
but at least we know it would work.

Unless anyone knows of a documented way to get 64 bit uint/int file 
offsets, I don't see we have mush choice.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Thu, 03 Oct 2002 23:10:48 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump and large files - is this a problem? 

Philip Warner writes:

> My limited reading of off_t stuff now suggests that it would be brave to 
> assume it is even a simple 64 bit number (or even 3 32 bit numbers).

What are you reading??  If you find a platform with 64 bit file
offsets that doesn't support 64 bit integral types I will not just be
surprised but amazed.

> One alternative, which I am not terribly fond of, is to have pg_dump
> write multiple files - when we get to 1 or 2GB, we just open another
> file, and record our file positions as a (file number, file
> position) pair. Low tech, but at least we know it would work.

That does avoid the issue completely, of course, and also avoids
problems where a platform might have large file support but a
particular filesystem might or might not.

> Unless anyone knows of a documented way to get 64 bit uint/int file 
> offsets, I don't see we have mush choice.

If you're on a platform that supports large files it will either have
a straightforward 64 bit off_t or else will support the "large files
API" that is common on Unix-like operating systems.

What are you trying to do, exactly?

Regards,

Giles




Fri, 04 Oct 2002 07:15:29 +1000
Giles Lean <giles@nemeton.com.au>
Re: pg_dump and large files - is this a problem? 
At 07:15 AM 4/10/2002 +1000, Giles Lean wrote:

> > My limited reading of off_t stuff now suggests that it would be brave to
> > assume it is even a simple 64 bit number (or even 3 32 bit numbers).
>
>What are you reading??  If you find a platform with 64 bit file
>offsets that doesn't support 64 bit integral types I will not just be
>surprised but amazed.

Yes, but there is no guarantee that off_t is implemented as such, nor would 
we be wise to assume so (most docs say explicitly not to do so).


> > Unless anyone knows of a documented way to get 64 bit uint/int file
> > offsets, I don't see we have mush choice.
>
>If you're on a platform that supports large files it will either have
>a straightforward 64 bit off_t or else will support the "large files
>API" that is common on Unix-like operating systems.
>
>What are you trying to do, exactly?

Again yes, but the problem is the same: we need a way of making the *value* 
of an off_t portable (not just assuming it's a int64). In general that 
involves knowing how to turn it into a more universal data type (eg. int64, 
or even a string). Does the large file API have functions for representing 
the off_t values that is portable across architectures? And is the API also 
portable?



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Fri, 04 Oct 2002 09:42:35 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump and large files - is this a problem? 

Philip Warner writes:

> Yes, but there is no guarantee that off_t is implemented as such, nor would 
> we be wise to assume so (most docs say explicitly not to do so).

I suspect you're reading old documents, which is why I asked what you
were referring to.  In the '80s what you are saying would have been
best practice, no question: 64 bit type support was not common.

When talking of near-current systems with 64 bit off_t you are not
going to find one without support for 64 bit integral types.

> Again yes, but the problem is the same: we need a way of making the *value* 
> of an off_t portable (not just assuming it's a int64). In general that 
> involves knowing how to turn it into a more universal data type (eg. int64, 
> or even a string).

So you need to know the size of off_t, which will be 32 bit or 64 bit,
and then you need routines to convert that to a portable representation.
The canonical solution is XDR, but I'm not sure that you want to  bother
with it or if it has been extended universally to support 64 bit types.

If you limit the file sizes to 1GB (your less preferred option, I
know;-) then like the rest of the PostgreSQL code you can safely
assume that off_t fits into 32 bits and have a choice of functions
(XDR or ntohl() etc) to deal with them and ignore 64 bit off_t
issues altogether.

If you intend pg_dump files to be portable avoiding the use of large
files will be best.  It also avoids issues on platforms such as HP-UX
where large file support is available, but it has to be enabled on a
per-filesystem basis. :-(

> Does the large file API have functions for representing 
> the off_t values that is portable across architectures? And is the API also 
> portable?

The large files API is a way to access large files from 32 bit
processes.  It is reasonably portable, but is a red herring for
what you are wanting to do.  (I'm not convinced I am understanding
what you're trying to do, but I have 'flu which is not helping. :-)

Regards,

Giles


Fri, 04 Oct 2002 10:50:21 +1000
Giles Lean <giles@nemeton.com.au>
Re: pg_dump and large files - is this a problem? 
Giles Lean <giles@nemeton.com.au> writes:
> When talking of near-current systems with 64 bit off_t you are not
> going to find one without support for 64 bit integral types.

I tend to agree with Giles on this point.  A non-integral representation
of off_t is theoretically possible but I don't believe it exists in
practice.  Before going far out of our way to allow it, we should first
require some evidence that it's needed on a supported or
likely-to-be-supported platform.

time_t isn't guaranteed to be an integral type either if you read the
oldest docs about it ... but no one believes that in practice ...

			regards, tom lane

Thu, 03 Oct 2002 23:07:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump and large files - is this a problem? 
Tom Lane wrote:
> Giles Lean <giles@nemeton.com.au> writes:
> > When talking of near-current systems with 64 bit off_t you are not
> > going to find one without support for 64 bit integral types.
> 
> I tend to agree with Giles on this point.  A non-integral representation
> of off_t is theoretically possible but I don't believe it exists in
> practice.  Before going far out of our way to allow it, we should first
> require some evidence that it's needed on a supported or
> likely-to-be-supported platform.
> 
> time_t isn't guaranteed to be an integral type either if you read the
> oldest docs about it ... but no one believes that in practice ...

I think fpos_t is the non-integral one.  I thought off_t almost always
was integral.

-- 
  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

Thu, 3 Oct 2002 23:10:19 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump and large files - is this a problem?
At 11:07 PM 3/10/2002 -0400, Tom Lane wrote:
>A non-integral representation
>of off_t is theoretically possible but I don't believe it exists in
>practice.

Excellent. So I can just read/write the bytes in an appropriate order and 
expect whatever size it is to be a single intXX.

Fine with me, unless anybody voices another opinion in the next day, I will 
proceed. I just have this vague recollection of seeing a header file with a 
more complex structure for off_t. I'm probably dreaming.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Fri, 04 Oct 2002 13:15:29 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump and large files - is this a problem? 

I have made the changes to pg_dump and verified that (a) it reads old 
files, (b) it handles 8 byte offsets, and (c) it dumps & seems to restore 
(at least to /dev/null).

I don't have a lot of options for testing it - should I just apply the 
changes and wait for the problems, or can someone offer a bigendian machine 
and/or a 4 byte off_t machine?


>was integral.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Fri, 18 Oct 2002 13:42:24 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump and large files - is this a problem?
Philip Warner <pjw@rhyme.com.au> writes:
> I don't have a lot of options for testing it - should I just apply the 
> changes and wait for the problems, or can someone offer a bigendian machine 
> and/or a 4 byte off_t machine?

My HP is big-endian; send in the patch and I'll check it here...

			regards, tom lane

Fri, 18 Oct 2002 09:25:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump and large files - is this a problem? 
Philip Warner writes:

>
> I have made the changes to pg_dump and verified that (a) it reads old
> files, (b) it handles 8 byte offsets, and (c) it dumps & seems to restore
> (at least to /dev/null).
>
> I don't have a lot of options for testing it - should I just apply the
> changes and wait for the problems, or can someone offer a bigendian machine
> and/or a 4 byte off_t machine?

Any old machine has a 4-byte off_t if you configure with
--disable-largefile.  This could be a neat way to test:  Make two
installations configured different ways and move data back and forth
between them until it changes.  ;-)

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 19 Oct 2002 00:07:07 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: pg_dump and large files - is this a problem?
At 12:07 AM 19/10/2002 +0200, Peter Eisentraut wrote:
>Any old machine has a 4-byte off_t if you configure with
>--disable-largefile.

Thanks - done. I just dumped to a custom backup file, then dumped it do 
SQL, and compared each version (V7.2.1, 8 byte & 4 byte offsets), and they 
all looked OK. Also, the 4 byte version reads the 8 byte offset version 
correctly - although I have not checked reading > 4GB files with 4 byte 
offset, but it's not a priority for obvious reasons.

So once Giles gets back to me (Monday), I'll commit the changes.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Sat, 19 Oct 2002 14:15:54 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump and large files - is this a problem?