Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOCkO-00010L-Nw for pgsql-sql@arkaria.postgresql.org; Fri, 05 Apr 2013 19:56:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UOCkO-0006ZU-7s for pgsql-sql@arkaria.postgresql.org; Fri, 05 Apr 2013 19:56:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOCkN-0006ZP-Fj for pgsql-sql@postgresql.org; Fri, 05 Apr 2013 19:56:27 +0000 Received: from dub0-omc2-s26.dub0.hotmail.com ([157.55.1.165]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOCkH-0006ut-In for pgsql-sql@postgresql.org; Fri, 05 Apr 2013 19:56:27 +0000 Received: from DUB116-W89 ([157.55.1.137]) by dub0-omc2-s26.dub0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Fri, 5 Apr 2013 12:56:16 -0700 X-EIP: [IK77ybytAEc3h33+HQm5SOsWJnZYJXOw] X-Originating-Email: [kong_mansatiansin@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_17f9fa20-b4ba-4bfe-b3be-cc53b91aa3a3_" From: Kong Man To: "pgsql-sql@postgresql.org" Subject: Data Loss from SQL SELECT (vs. COPY/pg_dump) Date: Fri, 5 Apr 2013 12:56:16 -0700 Importance: Normal In-Reply-To: References: MIME-Version: 1.0 X-OriginalArrivalTime: 05 Apr 2013 19:56:16.0498 (UTC) FILETIME=[A220ED20:01CE3237] X-Pg-Spam-Score: -2.4 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_17f9fa20-b4ba-4bfe-b3be-cc53b91aa3a3_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am troubled to find out that a SELECT statement produces fewer rows than = the actual row count and have not been able to answer myself as to why. I = hope someone could help shedding some light to this. I attempted to generate a set of INSERT statements=2C using a the following= SELECT statement=2C against my translations data to reuse elsewhere=2C but= then realized the row count was 8 rows fewer than the source of 2=2C178. = COPY and pg_dump don't seem to lose any data. So=2C I compare the results = to identify the missing data as follows. I don't even see any strange enco= ding in those missing data. What scenario could have caused my SELECT query to dump out the 8 blank row= s=2C instead of the expected data? Here is how I find the discrepancy: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D $ psql -c "CREATE TABLE new_translation AS SELECT display_name=2C name=2C type=2C translation FROM translations t JOIN lang l USING (langid) WHERE display_name =3D 'SPANISH_CORP' ORDER BY display_name=2C name" SELECT 2178 $ psql -tAc "SELECT 'INSERT INTO new_translation VALUES (' ||quote_literal(display_name)|| '=2C '||quote_literal(name)|| '=2C '||quote_literal(type)|| '=2C '||quote_literal(translation)||')=3B' FROM new_translation ORDER BY display_name=2C name" >/tmp/new_translation-select.sql=20 $ pg_dump --data-only --inserts --table=3Dnew_translation clubpremier | sed -n '/^INSERT/=2C/^$/p' >/tmp/new_translation-pg_dump.sql $ grep ^INSERT /tmp/new_translation-pg_dump.sql | wc -l 2178 $ grep ^INSERT /tmp/new_translation-select.sql | wc -l 2170 $ diff /tmp/new_translation-select.sql /tmp/new_translation-pg_dump.sql 27c27 <=20 --- > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'AGENCY_IN_USE_BY_C= OBRAND'=2C NULL=2C 'La cuenta no puede ser eliminada porque est=E1 siendo u= tilizada actualmente por la co-marca #cobrand#')=3B 506c506 <=20 --- > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'CAR_DISTANCE_UNIT'= =2C NULL=2C 'MILLAS')=3B 1115c1115 <=20 --- > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'HOTEL_PROMO_TEXT'= =2C 'label'=2C NULL)=3B 1131=2C1134c1131=2C1134 <=20 <=20 <=20 <=20 --- > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'INSURANCE_SEARCH_A= DVERTISEMENT_SECTION_ONE'=2C 'checkout'=2C NULL)=3B > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'INSURANCE_SEARCH_A= DVERTISEMENT_SECTION_THREE'=2C 'checkout'=2C NULL)=3B > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'INSURANCE_SEARCH_A= DVERTISEMENT_SECTION_TWO'=2C 'checkout'=2C NULL)=3B > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'INSURANCE_SEARCH_F= OOTER'=2C 'checkout'=2C NULL)=3B 1615c1615 <=20 --- > INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'PAGE_FORGOT_PASSWO= RD'=2C 'page_titles'=2C NULL)=3B 2215a2216 >=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D Thank you in advance for your help=2C -Kong = --_17f9fa20-b4ba-4bfe-b3be-cc53b91aa3a3_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I am troubled to find out that a= SELECT statement produces fewer rows than the actual row count and have no= t been able to answer myself as to why. =3B I hope someone could help s= hedding some light to this.

I attempted to generate a set of INSERT = statements=2C using a the following SELECT statement=2C against my translat= ions data to reuse elsewhere=2C but then realized the row count was 8 rows = fewer than the source of 2=2C178. =3B COPY and pg_dump don't seem to lo= se any data. =3B So=2C I compare the results to identify the missing da= ta as follows. =3B I don't even see any strange encoding in those missi= ng data.

What scenario could have caused my SELECT query to dump out= the 8 blank rows=2C instead of the expected data?

Here is how I fin= d the discrepancy:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
$ psql -c "CREATE TABLE new_transla= tion AS
 =3B SELECT display_name=2C name=2C type=2C translation
&= nbsp=3B FROM translations t JOIN lang l USING (langid)
 =3B WHERE di= splay_name =3D 'SPANISH_CORP'
 =3B ORDER BY display_name=2C name"SELECT 2178

$ psql -tAc "SELECT
 =3B'INSERT INTO new_transla= tion VALUES ('
 =3B =3B =3B =3B ||quote_literal(display_= name)||
 =3B'=2C '||quote_literal(name)||
 =3B'=2C '||quote_l= iteral(type)||
 =3B'=2C '||quote_literal(translation)||')=3B'
FRO= M new_translation
ORDER BY display_name=2C name" >=3B/tmp/new_translat= ion-select.sql

$ pg_dump --data-only --inserts --table=3Dnew_transl= ation clubpremier |
 =3B sed -n '/^INSERT/=2C/^$/p' >=3B/tmp/new_t= ranslation-pg_dump.sql

$ grep ^INSERT /tmp/new_translation-pg_dump.s= ql | wc -l
2178

$ grep ^INSERT /tmp/new_translation-select.sql | = wc -l
2170

$ diff /tmp/new_translation-select.sql /tmp/new_transl= ation-pg_dump.sql
27c27
<=3B
---
>=3B INSERT INTO new_tran= slation VALUES ('SPANISH_CORP'=2C 'AGENCY_IN_USE_BY_COBRAND'=2C NULL=2C 'La= cuenta no puede ser eliminada porque est=E1 siendo utilizada actualmente p= or la co-marca #cobrand#')=3B
506c506
<=3B
---
>=3B INSERT= INTO new_translation VALUES ('SPANISH_CORP'=2C 'CAR_DISTANCE_UNIT'=2C NULL= =2C 'MILLAS')=3B
1115c1115
<=3B
---
>=3B INSERT INTO new_t= ranslation VALUES ('SPANISH_CORP'=2C 'HOTEL_PROMO_TEXT'=2C 'label'=2C NULL)= =3B
1131=2C1134c1131=2C1134
<=3B
<=3B
<=3B
<=3B <= br>---
>=3B INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'INS= URANCE_SEARCH_ADVERTISEMENT_SECTION_ONE'=2C 'checkout'=2C NULL)=3B
>= =3B INSERT INTO new_translation VALUES ('SPANISH_CORP'=2C 'INSURANCE_SEARCH= _ADVERTISEMENT_SECTION_THREE'=2C 'checkout'=2C NULL)=3B
>=3B INSERT IN= TO new_translation VALUES ('SPANISH_CORP'=2C 'INSURANCE_SEARCH_ADVERTISEMEN= T_SECTION_TWO'=2C 'checkout'=2C NULL)=3B
>=3B INSERT INTO new_translat= ion VALUES ('SPANISH_CORP'=2C 'INSURANCE_SEARCH_FOOTER'=2C 'checkout'=2C NU= LL)=3B
1615c1615
<=3B
---
>=3B INSERT INTO new_translation= VALUES ('SPANISH_CORP'=2C 'PAGE_FORGOT_PASSWORD'=2C 'page_titles'=2C NULL)= =3B
2215a2216
>=3B
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Thank you in advance f= or your help=2C
-Kong
= --_17f9fa20-b4ba-4bfe-b3be-cc53b91aa3a3_--