Thread
-
pg_dump 7.1beta4 misses a table
PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-02-21T23:47:10Z
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 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. Below I have included the table definition and also the dump file created by using "pg_dump -t user_profiles tsar", which exhibits 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 -
Re: pg_dump 7.1beta4 misses a table
Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-22T00:16:33Z
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
-
JDBC bug in 7.1b4
Rainer Mager <rmager@vgkk.com> — 2001-02-22T00:28:59Z
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