pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedweird duplicate data problem
5+ messages / 5 participants
[nested] [flat]
* weird duplicate data problem
@ 2001-12-14 07:29 Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-12-14 18:42 ` Re: weird duplicate data problem Jeff Self <jocknerd@home.com>
0 siblings, 1 reply; 5+ messages in thread
From: Carolyn Lu Wong @ 2001-12-14 07:29 UTC (permalink / raw)
To: pgsql-sql
a table was created with:
create table bills (
id serial not null,
account_no int4 not null,
date_issued date not null,
.....
);
The following SQL script returns only 1 row of data:
select oid, * from bills where id = xxxxx' order by oid;
However with the following SQL:
select oid, * from bills where date_issued = '01/01/2001'
order by oid;
some of the data is duplicated, including the OID.
When trying to 'vaccum' the database, get
NOTICE: Index bills_id_key: number of index tupples (4755) is not the
same as heap (7800).
What is happening? What could have caused this?
Database version used is V6.50. I know it's old, but i hope it's not the
version that's causing this problem.
Thanks in advance for any suggestions.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: weird duplicate data problem
2001-12-14 07:29 weird duplicate data problem Carolyn Lu Wong <carolyn@greatpacific.com.au>
@ 2001-12-14 18:42 ` Jeff Self <jocknerd@home.com>
2001-12-14 19:22 ` Re: weird duplicate data problem Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jeff Self @ 2001-12-14 18:42 UTC (permalink / raw)
To: Carolyn Lu Wong <carolyn@greatpacific.com.au>; +Cc: pgsql-sql
On Fri, 14 Dec 2001, Carolyn Lu Wong wrote:
> a table was created with:
>
> create table bills (
> id serial not null,
> account_no int4 not null,
> date_issued date not null,
> .....
> );
There is no reason to set id to not null. It is a serial field. It will
automatically increase. Your account_no should probably be set to a char
field. My old rule of thumb is not to make something int unless you plan
to perform some type of math on it.
>
> The following SQL script returns only 1 row of data:
>
> select oid, * from bills where id = xxxxx' order by oid;
>
If your id field is serial, all the records will have unique id's in them.
Therefore you are only going to get one row of data.
>
>
> However with the following SQL:
>
> select oid, * from bills where date_issued = '01/01/2001'
> order by oid;
>
Can't help you here. I'm running 7.1.3 and the query works fine.
> some of the data is duplicated, including the OID.
>
> When trying to 'vaccum' the database, get
>
> NOTICE: Index bills_id_key: number of index tupples (4755) is not the
> same as heap (7800).
>
> What is happening? What could have caused this?
>
> Database version used is V6.50. I know it's old, but i hope it's not the
> version that's causing this problem.
>
> Thanks in advance for any suggestions.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Jeff Self
Powered by Debian GNU/Linux
http://members.home.net/jocknerd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: weird duplicate data problem
2001-12-14 07:29 weird duplicate data problem Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-12-14 18:42 ` Re: weird duplicate data problem Jeff Self <jocknerd@home.com>
@ 2001-12-14 19:22 ` Josh Berkus <josh@agliodbs.com>
2001-12-14 20:54 ` Re: weird duplicate data problem Jan Wieck <janwieck@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2001-12-14 19:22 UTC (permalink / raw)
To: Jeff Self <jocknerd@home.com>; Carolyn Lu Wong <carolyn@greatpacific.com.au>; +Cc: pgsql-sql
Carolyn, Jeff,
> > a table was created with:
> >
> > create table bills (
> > id serial not null,
> > account_no int4 not null,
> > date_issued date not null,
> > .....
> > );
> There is no reason to set id to not null. It is a serial field. It
> will
> automatically increase. Your account_no should probably be set to a
> char
> field. My old rule of thumb is not to make something int unless you
> plan
> to perform some type of math on it.
I have to disagree with this, Jeff. A six-digit integer takes up only
20% of the storage space of a 6-character string. As such, it will be
faster to index and faster to retrieve, and faster to perform JOINs and
sorts. So if Carolyn will never want to store anything but numbers in
the field, integer is ideal.
> > The following SQL script returns only 1 row of data:
> >
> > select oid, * from bills where id = xxxxx' order by oid;
> >
> If your id field is serial, all the records will have unique id's in
> them.
> Therefore you are only going to get one row of data.
Yup. Why, BTW, is Carloyn interested in the OID? It is not a useful
piece of information for query purposes ... the OID is a tool for the
Postgres DB engine and should not be used as data, since it may change
unexpectedly.
> > However with the following SQL:
> >
> > select oid, * from bills where date_issued = '01/01/2001'
> > order by oid;
> >
>
> Can't help you here. I'm running 7.1.3 and the query works fine.
Try date_issued = '2001-01-01'. Some platforms (Unix for one) get fussy
about using universal date format.
> > some of the data is duplicated, including the OID.
> >
> > When trying to 'vaccum' the database, get
> >
> > NOTICE: Index bills_id_key: number of index tupples (4755) is not
> the
> > same as heap (7800).
> >
> > What is happening? What could have caused this?
> >
> > Database version used is V6.50. I know it's old, but i hope it's
> not the
> > version that's causing this problem.
Sorry, Carolyn, can't help you on Vacuum problems unless you upgrade.
6.5 does indeed have several major problems with Vacuum; time to get
7.1.3. The core team ain't releasing new versions for the fun alone,
y'know!
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: weird duplicate data problem
2001-12-14 07:29 weird duplicate data problem Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-12-14 18:42 ` Re: weird duplicate data problem Jeff Self <jocknerd@home.com>
2001-12-14 19:22 ` Re: weird duplicate data problem Josh Berkus <josh@agliodbs.com>
@ 2001-12-14 20:54 ` Jan Wieck <janwieck@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jan Wieck @ 2001-12-14 20:54 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jeff Self <jocknerd@home.com>; Carolyn Lu Wong <carolyn@greatpacific.com.au>; pgsql-sql
Josh Berkus wrote:
> Carolyn, Jeff,
>
> I have to disagree with this, Jeff. A six-digit integer takes up only
> 20% of the storage space of a 6-character string. As such, it will be
> faster to index and faster to retrieve, and faster to perform JOINs and
> sorts. So if Carolyn will never want to store anything but numbers in
> the field, integer is ideal.
That is all true, and still you should not restrict the
possible content to something that got nothing to do with the
nature of the data.
Things like account numbers, zip codes, phone numbers,
whatnot ARE CHARACTER SEQUENCES - period. How sure can
Carolyn be that there will never be an account number where
leading zeroes are significant? That true already for zip
codes (and by the way, european zipcodes are alphanumeric).
And what is the cosmic significance of my phone number plus
your creditcard number divided by Carolyn's shoesize? :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: weird duplicate data problem
@ 2001-12-17 15:20 Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Henshall, Stuart - WCP @ 2001-12-17 15:20 UTC (permalink / raw)
To: 'Carolyn Lu Wong' <carolyn@greatpacific.com.au>; pgsql-sql
-----Original Message-----
From: Carolyn Lu Wong [mailto:carolyn@greatpacific.com.au]
Sent: 14 December 2001 07:29
To: pgsql-sql@postgresql.org
Subject: weird duplicate data problem
a table was created with:
create table bills (
id serial not null,
account_no int4 not null,
date_issued date not null,
.....
);
The following SQL script returns only 1 row of data:
select oid, * from bills where id = xxxxx' order by oid;
--
Seems reasonable
--
However with the following SQL:
select oid, * from bills where date_issued = '01/01/2001'
order by oid;
some of the data is duplicated, including the OID.
--
OIDs are not guaranteed unique and can wraparound (unlesss there's a unique
index). However this seems a little unlikely in the same day so the only
thing I can think of is that there is a corrupt index on date_issued. Try
dropping and recreating any indexes.However I havn't really used 6.5 so
couldn't be sure.
--
When trying to 'vaccum' the database, get
NOTICE: Index bills_id_key: number of index tupples (4755) is not the
same as heap (7800).
What is happening? What could have caused this?
Database version used is V6.50. I know it's old, but i hope it's not the
version that's causing this problem.
Thanks in advance for any suggestions.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-12-17 15:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-14 07:29 weird duplicate data problem Carolyn Lu Wong <carolyn@greatpacific.com.au>
2001-12-14 18:42 ` Jeff Self <jocknerd@home.com>
2001-12-14 19:22 ` Josh Berkus <josh@agliodbs.com>
2001-12-14 20:54 ` Jan Wieck <janwieck@yahoo.com>
2001-12-17 15:20 Re: weird duplicate data problem Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox