Thread

pg_dump -a strangeness/bug? (7.1beta5)

Antoine Reid <antoiner@hansonpublications.com> — 2001-03-01T22:52:42Z

Hi everybody!

I believe I have found a little buglet in pg_dump utility, as distributed
in 7.1beta5.  Let me describe the environment first:


This is an x86 machine (AMD K6-2) with 128MB RAM.

Running Debian 'unstable', updated almost every day.

The following (relevant?) packages are installed:

	openssl		0.9.6-1
	libssl096	0.9.6-1
	libssl096-dev	0.9.6-1
	tcl83		8.3.2-6
	tcl83-dev	8.3.2-6
	tk83		8.3.2-3
	tk83-dev	8.3.2-6

PostgreSQL 7.1 beta 5, compiled from sources.  Here is the exact build 
incantation:

	./configure  --prefix=/usr/local/pgsql --with-tcl --with-perl \
	--with-CXX --with-openssl=/usr/include/openssl --with-gnu-ld \
	--with-odbc --enable-syslog --with-tclconfig=/usr/lib/tcl8.3 \
	--with-tkconfig=/usr/lib/tk8.3 --with-includes=/usr/include/tcl8.3

The options --with-tclconfig, --with-tkconfig and --with-includes is 
necessary because those tcl and tk packages now have their own subdirs in
/usr/include and /usr/lib to allow concurrent versions to exist on the 
system.  Please note I am not using any .deb packages for PostgreSQL.

Now, the bug report itself...
I have tried to cover all bases here.
See my comments after the "-- END BUG REPORT --" line..

-- START BUG REPORT --

pgsql@wumpus(pts/3)(0):~% echo $PGDATA
/usr/local/pgsql/data
pgsql@wumpus(pts/3)(0):~% echo $PGHOST
localhost
pgsql@wumpus(pts/3)(0):~% echo $PGLIB 
/usr/local/pgsql/lib
pgsql@wumpus(pts/3)(0):~% which psql
/usr/local/pgsql/bin/psql
pgsql@wumpus(pts/3)(0):~% grep 127.0.0.1 /usr/local/pgsql/data/pg_hba.conf
host         all         127.0.0.1     255.255.255.255     ident sameuser
pgsql@wumpus(pts/3)(0):~% psql -l
 List of databases
 Database  | Owner 
-----------+-------
 template0 | pgsql
 template1 | pgsql
(2 rows)

pgsql@wumpus(pts/3)(0):~% createdb testbug
CREATE DATABASE
pgsql@wumpus(pts/3)(0):~% psql testbug
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

testbug=# SELECT version(); 
                             version                              
------------------------------------------------------------------
 PostgreSQL 7.1beta5 on i586-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

testbug=# CREATE TABLE table1 (
testbug(# attribute1 SERIAL NOT NULL PRIMARY KEY,
testbug(# attribute2 TEXT
testbug(# );
NOTICE:  CREATE TABLE will create implicit sequence 'table1_attribute1_seq' for SERIAL column 'table1.attribute1'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'table1_pkey' for table 'table1'
CREATE
testbug=# \q
pgsql@wumpus(pts/3)(0):~% which pg_dump
/usr/local/pgsql/bin/pg_dump
pgsql@wumpus(pts/3)(0):~% pg_dump -a testbug
--
-- Selected TOC Entries:
--
\connect - pgsql
--
pgsql@wumpus(pts/3)(0):~% pg_dump -a testbug
--
-- Selected TOC Entries:
--
\connect - pgsql
--
-- TOC Entry ID 1 (OID 18772)
--
-- Name: "table1_attribute1_seq" Type: SEQUENCE Owner: pgsql
--

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

--
-- TOC Entry ID 3 (OID 18772)
--
-- Name: "table1_attribute1_seq" Type: SEQUENCE Owner: pgsql
--

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

--
-- Data for TOC Entry ID 5 (OID 18791) TABLE DATA table1
--

-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'table1';
COPY "table1"  FROM stdin;
\.
-- 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" ~* 'table1'  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 18772)
--
-- Name: "table1_attribute1_seq" Type: SEQUENCE SET Owner: 
--

SELECT setval ('"table1_attribute1_seq"', 1, 'f');

--
-- TOC Entry ID 4 (OID 18772)
--
-- Name: "table1_attribute1_seq" Type: SEQUENCE SET Owner: 
--

SELECT setval ('"table1_attribute1_seq"', 1, 'f');

pgsql@wumpus(pts/3)(0):~%

-- END BUG REPORT --



OK.. Here we go:

I create a brand new database.
I create a new (simple) table, that will implicitly create a sequence.

I don't insert any data.

The problem is within the output of "pg_dump -a testbug"

Note: calling "pg_dump testbug" does not show the problem.

The problem exists only when the "-a" option is given.  Taken from
pg_dump --help, -a should give me only the data, not the schema creation
queries.

However, if you notice above, it CREATEs the SEQUENCE, and twice!
It also sets the sequence value twice!

Now, I would expect it to only COPY the data in the different tables, and
set the values of the different sequences.  Maybe my expectations are
wrong, but at least, it ought not to do it TWICE... :-/

Can someone confirm whether this is a bug? I have tried on many different
systems here, and it looks like this bug was there in beta4 and beta3 as
well. I haven't checked earlier versions (in particular, I didn't check
whether 7.0.3 had this behavior or not).

If someone goes there and fixes the code, maybe the code could be optimized
not to put any COPY at all for a table if it is empty?  Either issue a
SELECT COUNT(*) FROM foo  before, or just checking if it got an empty
recordset when fetching the data? It seems to me we could spare disabling
triggers, creating a temp table, doing an empty copy and re-enabling
triggers.. Comments?

Any more details available upon request.

Thanks
Antoine Reid 

-- 
 O          Antoine Reid             O>  Any sufficiently perverted  <O>
<|>    System and Network Admin     <|         perl script is         |
 >\ antoiner@hansonpublications.com  >\ indistinguishable from Magic /<

Re: pg_dump -a strangeness/bug? (7.1beta5)

Philip Warner <pjw@rhyme.com.au> — 2001-03-06T05:09:24Z

At 17:52 1/03/01 -0500, Antoine Reid wrote:
>
>Can someone confirm whether this is a bug? 

It is a bug. It's fixed in CVS.


>If someone goes there and fixes the code, maybe the code could be optimized
>not to put any COPY at all for a table if it is empty? 

Maybe for 7.2.


> Either issue a
>SELECT COUNT(*) FROM foo  before, or just checking if it got an empty
>recordset when fetching the data? 

Probably use 'select Exists(Select * from foo)', since count is very slow -
it does a sequential scan.

>It seems to me we could spare disabling
>triggers, creating a temp table, doing an empty copy and re-enabling
>triggers.. Comments?

This may be less of a problem for normal dumps, since the triggers are now
only disabled for data-only dumps.

Hope this helps,

Philip Warner.

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

Re: pg_dump -a strangeness/bug? (7.1beta5)

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-06T15:35:25Z

Philip Warner <pjw@rhyme.com.au> writes:
>> If someone goes there and fixes the code, maybe the code could be optimized
>> not to put any COPY at all for a table if it is empty? 

> Maybe for 7.2.

A copy supplying no data isn't going to take a significant amount of
time.  I don't think this is worth worrying about.

			regards, tom lane