Thread: pg_dump 7.1beta4 misses a table

Tara Piorkowski (tara@vilaj.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
pg_dump 7.1beta4 misses a table

Long Description
I noticed in attempting to dump a database I was developing that one of the tables failed to get recreated from the
dumpfile. Upon examining the dump file I noticed that there's a placeholder for the table but the actual "create"
commandis missing. All of the other tables in the system were recreated and repopulated without error. 

Below I have included the table definition and also the dump file created by using "pg_dump -t user_profiles tsar",
whichexhibits the same behavior as when the whole database is dumped. 

Note that this database started out as a 7.0.3 database, was successfully dumped, then successfully loaded into pg_dump
7.1beta4.

Here's the system info...

SuSE Linux 6.4 running on a PowerPC (iMac 233/96MB memory)
PostgreSQL 7.1beta4
Compiled using gcc version 2.95.2 19991024 (release)

Thanks for your help on fixing this.

Sample Code
tsar=# \d user_profiles
                                             Table "user_profiles"
        Attribute         |         Type          |                          Modifier
--------------------------+-----------------------+-------------------------------------------------------------
 user_id                  | integer               | not null default nextval('user_profiles_user_id_seq'::text)
 employee_id              | integer               | not null
 first_name               | character varying(25) | not null
 last_name                | character varying(30) | not null
 email_address            | character varying(40) | not null
 timesheet_approver_id    | integer               | not null
 normal_cycle_type_id     | integer               | not null
 normal_start_time        | time                  | not null
 normal_end_time          | time                  | not null
 approver_privileges_flag | boolean               | not null
 admin_privileges_flag    | boolean               | not null
 password                 | character varying(25) | not null
Indices: user_profiles_email_address_und,
         user_profiles_pkey

tsar=# \q
[tara@boreas exports]$ ls -l
total 20
-rw-r-----   1 tara   users       16808 Feb 21 17:24 tsar_20010222.dmp
[tara@boreas exports]$ pg_dump tsar -t user_profiles > tsar_user_profiles_20010222.dmp
[tara@boreas exports]$ cat tsar_user_profiles_20010222.dmp
--
-- Selected TOC Entries:
--
\connect - tara
--
-- TOC Entry ID 2 (OID 73294)
--
-- Name: user_profiles Type: TABLE Owner: tara
--


--
-- Data for TOC Entry ID 4 (OID 73294) TABLE DATA user_profiles
--

-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'user_profiles';
COPY "user_profiles"  FROM stdin;
1       6150    Tara    Piorkowski   tara@wildwood.edu     2       1       09:00:00        05:00:00        f       t
 nopass 
2       3640    Gary    Milford gmilford@wildwood.edu   2       1       09:00:00        05:00:00        t       t
nopass
\.
-- 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" ~* 'user_profiles'  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 3 (OID 73294)
--
-- Name: "user_profiles_email_address_und" Type: INDEX Owner: tara
--

CREATE UNIQUE INDEX "user_profiles_email_address_und" on "user_profiles" using btree ( "email_address" "varchar_ops" );

--
-- TOC Entry ID 5 (OID 73415)
--
-- Name: "RI_ConstraintTrigger_73414" Type: TRIGGER Owner: tara
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "user_profiles"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR
EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'user_profiles', 'cycle_types', 'UNSPECIFIED',
'normal_cycl
e_type_id', 'cycle_type_id');

--
-- TOC Entry ID 6 (OID 73417)
--
-- Name: "RI_ConstraintTrigger_73416" Type: TRIGGER Owner: tara
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "user_profiles"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
E
XECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'timesheets', 'user_profiles', 'UNSPECIFIED', 'user_id',
'user_id'
);

--
-- TOC Entry ID 7 (OID 73419)
--
-- Name: "RI_ConstraintTrigger_73418" Type: TRIGGER Owner: tara
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "user_profiles"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
E
XECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'timesheets', 'user_profiles', 'UNSPECIFIED', 'user_id',
'user_id'
);


No file was uploaded with this report

Tara Piorkowski (tara@vilaj.com) writes:
> I noticed in attempting to dump a database I was developing that one
> of the tables failed to get recreated from the dump file. Upon
> examining the dump file I noticed that there's a placeholder for the
> table but the actual "create" command is missing. All of the other
> tables in the system were recreated and repopulated without error.

> Note that this database started out as a 7.0.3 database, was
> successfully dumped, then successfully loaded into pg_dump 7.1beta4.

Bizarre.  Do you still have the dump file from the 7.0.3 version?
Could we see the part of it that creates this table?  Don't need the
data I suppose, but the other commands related to the table.

            regards, tom lane

Hi all,

    I already reported this directly to Peter Mount but since I have since
joined this list I thought I should make it a more wide-spread report.

    The issue is that if you use getDate() on a field that is a timestamp type
then you will get an SQLException that wraps a NumberFormatException. While
this behavior is arguably correct, our main problem is that PG 7.0 did not
have this problem and I think it would be nice if 7.1 didn't break our old
code.
    To that end, the following is as replacement to the getDate() method it
ResultSet and fixes the problem, at least for me. I think it is harmless to
provide the ability to get a date from a timestamp. If you do not agree to
add this to the JDBC driver please let me know ASAP so that I can begin
fixing our code in preparation for 7.1.


     /**
      * Get the value of a column in the given row as a java.sql.Date
     * object. This handles both <code>Timestamp</code> and <code>
     * Date</code> fields, truncating the time portion in the case of a
     * Timestamp.
     *
     * @param columnIndex the first column is 1, the second is 2...
     * @return the column value; null if SQL NULL
     * @exception SQLException if a database access error occurs
     */
    public java.sql.Date getDate( int columnIndex ) throws SQLException
    {
        String s = getString( columnIndex );
        if( s == null )
            return null;

        java.sql.Date date = null;
        try {
            date = java.sql.Date.valueOf( s );
        } catch( NumberFormatException e ) {
            date = new java.sql.Date( getTimestamp( columnIndex ).getTime() );
        }

        return date;
    }




Best regards,

--Rainer