Hi

I've been experimenting with 7.1rc4 for a couple of hours. I was messing with
blobs, and the new toast setup worked quite nicely. One thing I especially
liked was the fact that by having pg_dump create a dumpfile in the custom or
tar format, I could also backup all blobs in one go.

Unfortunately, practice was a bit different. Which is why I would like to know
if these functions are intended for general use.

A small log:

sol2:~$ uname -srm
SunOS 5.8 sun4u
sol2:~$ createdb blaat
CREATE DATABASE
sol2:~$ psql -c 'create table test(a oid)' blaat
CREATE
sol2:~$ psql -c "insert into test values(lo_import('/etc/hosts'))" blaat 
INSERT 18761 1
sol2:~$ pg_dump -b -Fc -f blaat.bk blaat
sol2:~$ pg_restore -l blaat.bk
;
; Archive created at Sat Apr 14 01:03:02 2001
;     dbname: blaat
;     TOC Entries: 4
;     Compression: -1
;     Dump Version: 1.5-2
;     Format: CUSTOM
;
;
; Selected TOC Entries:
;
2; 18749 TABLE test mathijs
3; 18749 TABLE DATA test mathijs
4; 0 BLOBS BLOBS 
sol2:~$ grep serv /etc/hosts
10.1.8.12       serv2.ilse.nl
10.1.8.10       serv0.ilse.nl
sol2:~$ grep serv blaat.bk  
sol2:~$ pg_dump -b -Ft -f blaat.tar blaat
zsh: segmentation fault (core dumped)  pg_dump -b -Ft -f blaat.tar blaat
sol2:~$ psql -c 'select version()' blaat
                              version                              
-------------------------------------------------------------------
 PostgreSQL 7.1rc4 on sparc-sun-solaris2.8, compiled by GCC 2.95.3
(1 row)

A backtrace reveals the following:
#0  0xff132e5c in strlen () from /usr/lib/libc.so.1
#1  0xff181890 in _doprnt () from /usr/lib/libc.so.1
#2  0xff183a04 in vsnprintf () from /usr/lib/libc.so.1
#3  0x2710c in ahprintf (AH=0x56cd0, fmt=0x430a8 "-- File: %s\n")
    at pg_backup_archiver.c:1116
#4  0x2ee90 in _PrintExtraToc (AH=0x56cd0, te=0x5e838) at pg_backup_tar.c:305
#5  0x290e0 in _printTocEntry (AH=0x56cd0, te=0x5e838, ropt=0x681b0)
    at pg_backup_archiver.c:1877
#6  0x25470 in RestoreArchive (AHX=0x56cd0, ropt=0x681b0)
    at pg_backup_archiver.c:269
#7  0x2ffb8 in _CloseArchive (AH=0x56cd0) at pg_backup_tar.c:840
#8  0x24f68 in CloseArchive (AHX=0x56cd0) at pg_backup_archiver.c:136
#9  0x15128 in main (argc=6, argv=0xffbefcac) at pg_dump.c:1114

What happens is that in line 305 of pg_backup_tar.c, ahprintf is handed a NULL
pointer.

300   static void
301   _PrintExtraToc(ArchiveHandle *AH, TocEntry *te)
302   {
303           lclTocEntry *ctx = (lclTocEntry *) te->formatData;
304   
305           ahprintf(AH, "-- File: %s\n", ctx->filename);
306   }

Could this be caused by the fact that IMHO blobs aren't dumped correctly?

Regards,

Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
                                                    Erik Naggum

Sat, 14 Apr 2001 01:14:07 +0200
Mathijs Brands <mathijs@ilse.nl>
pg_dump, formats & blobs
At 01:14 14/04/01 +0200, Mathijs Brands wrote:
...
>sol2:~$ pg_dump -b -Fc -f blaat.bk blaat
>sol2:~$ pg_restore -l blaat.bk
...
>;
>; Archive created at Sat Apr 14 01:03:02 2001
...

This all looks fine.


>sol2:~$ pg_dump -b -Ft -f blaat.tar blaat
>zsh: segmentation fault (core dumped)  pg_dump -b -Ft -f blaat.tar blaat

This is less good. It's caused by the final part of TAR output, which also
dumps a plain SQL script for reference (not actually ever used by
pg_restore). I will fix this in CVS; ctx->filename is set to null for this
script, and my compiler outputs '(null)', which is very forgiving of it. 


>
>Could this be caused by the fact that IMHO blobs aren't dumped correctly?
>

Is there some other problem with BLOBs that you did not mention? AFAICT,
this is only a problem with TAR output (an will be fixed ASAP). 


----------------------------------------------------------------
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, 14 Apr 2001 11:44:18 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump, formats & blobs
On Sat, Apr 14, 2001 at 11:44:18AM +1000, Philip Warner allegedly wrote:
> At 01:14 14/04/01 +0200, Mathijs Brands wrote:
> ...
> >sol2:~$ pg_dump -b -Fc -f blaat.bk blaat
> >sol2:~$ pg_restore -l blaat.bk
> ...
> >;
> >; Archive created at Sat Apr 14 01:03:02 2001
> ...
> 
> This all looks fine.

Hmm, I can only agree.

sol2:~$ cksum postgresql-7.1rc4.tar.gz
61540329        8088934 postgresql-7.1rc4.tar.gz
sol2:~$ dropdb blaat
DROP DATABASE
sol2:~$ createdb blaat
CREATE DATABASE
sol2:~$ psql -c 'create table test(a oid)' blaat
CREATE
sol2:~$ psql -c "insert into test values(lo_import('/export/home/mathijs/postgresql-7.1rc4.tar.gz'))" blaat
INSERT 22753 1
sol2:~$ pg_dump -b -Fc -f blaat.bk blaat
sol2:~$ psql -c 'drop table test ; vacuum' blaat
VACUUM
sol2:~$ pg_restore -d blaat blaat.bk
sol2:~$ psql -c "select lo_export(test.a, '/export/home/mathijs/testfile') from test" blaat
 lo_export
-----------
         1
(1 row)

sol2:~$ cksum testfile
61540329      8088934 testfile
sol2:~$ pg_restore -l blaat.bk
;
; Archive created at Sat Apr 14 03:59:02 2001
;     dbname: blaat
;     TOC Entries: 4
;     Compression: -1
;     Dump Version: 1.5-2
;     Format: CUSTOM
;
;
; Selected TOC Entries:
;
2; 18792 TABLE test mathijs
3; 18792 TABLE DATA test mathijs
4; 0 BLOBS BLOBS

I couldn't get blobs to be restored correctly (must've been doing
something wrong). When something doesn't work, never question your
own methods ;)

> >sol2:~$ pg_dump -b -Ft -f blaat.tar blaat
> >zsh: segmentation fault (core dumped)  pg_dump -b -Ft -f blaat.tar blaat
> 
> This is less good. It's caused by the final part of TAR output, which also
> dumps a plain SQL script for reference (not actually ever used by
> pg_restore). I will fix this in CVS; ctx->filename is set to null for this
> script, and my compiler outputs '(null)', which is very forgiving of it. 

It's more likely that your C library is more forgiving (ie. Open Source OS?).

> >Could this be caused by the fact that IMHO blobs aren't dumped correctly?
> >
> 
> Is there some other problem with BLOBs that you did not mention? AFAICT,
> this is only a problem with TAR output (an will be fixed ASAP). 

Yeah, they're not fool proof ;)

Sorry about the false alarm. I was convinced restoring blobs
didn't work correctly.

Regards,

Mathijs
-- 
$_='while(read+STDIN,$_,2048){$a=29;$c=142;if((@a=unx"C*",$_)[20]&48){$h=5;
$_=unxb24,join"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$&/;$d=
unxV,xb25,$_;$b=73;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=($t=255)&($d
>>12^$d>>4^$d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*8^$q<<6))<<9
,$_=(map{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;$t
^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m&17)),$b^=$_%64?12:0,@z)[$_%8]}(16..271))
[$_]^(($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}print+x"C*",@a}';s/x/pack+/g;eval 

Sat, 14 Apr 2001 04:10:26 +0200
Mathijs Brands <mathijs@ilse.nl>
Re: pg_dump, formats & blobs
At 04:10 14/04/01 +0200, Mathijs Brands wrote:
>
>Sorry about the false alarm. I was convinced restoring blobs
>didn't work correctly.
>

The tar problem is now 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   |/

Sat, 14 Apr 2001 23:13:45 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump, formats & blobs