Re: Ask ctid - Mailing list pgsql-general

I'm currently using Postgre 8.2. Are there something wrong with that
version?

Below is the DDL for my table:

CREATE TABLE absensi (
    id_absensi integer NOT NULL,
    id_user integer,
    tanggal date,
    keterangan character varying,
    id_tipe_absensi integer
);

ALTER TABLE ONLY absensi
    ADD CONSTRAINT absensi_pkey PRIMARY KEY (id_absensi);

ALTER TABLE ONLY absensi
    ADD CONSTRAINT unique_absen UNIQUE (id_user, tanggal);


below are examples of instance of two records:
R1: 1001, 1, 02-25-2008, Present, 1
R2: 1001, 1, 02-25-2008, Present, 1

The two records are identical. But if i display the ctid, the two
records differ as they
have different ctid (suppose i display the ctid in the first column):
R1: 8888, 1001, 1, 02-25-2008, Present, 1
R2: 9999, 1001, 1, 02-25-2008, Present, 1

Many thanks for your help.

Nizar
=====


Alban Hertroys wrote:

> On Mar 7, 2008, at 9:28 AM, Achmad Nizar Hidayanto wrote:
>
>> Thank you for the comment,
>>
>> I just wonder, how come i have two identic rows. I have set the
>> primary key and set it as a unique. That's why i take
>> a look at ctid (in real, i don't use this id. I just tried to trace
>> why i have two identic rows. After examining the physical
>> id using ctid, i found that the two identic rows differ in their  ctid).
>>
>> Having this case, can i conclude that postgre cannot guarantee the
>> uniqueness of primary key? or is it just a bug of old
>> version of postgre?
>
>
> Well, it's certainly surprising your Postgres intallation is doing
> that - it's one of the things Postgres is good at, but we lack
> information to see what's really going on here.
>
> You're mentioning you're using an old version, which one? The output
> of "select version();" should do.
>
> Can you show us the table definition with the primary key and the two
> identical records you mentioned?
>
>> Many thanks for your help.
>>
>>
>> Nizar
>> =====
>>
>> Tom Lane wrote:
>>
>>> Achmad Nizar Hidayanto <nizar@cs.ui.ac.id> writes:
>>>
>>>>
>>>> I implement database in my faculty using Postgre. I have a  problem
>>>> with ctid in my tables. Let say, i have table STUDENT  with #STU as
>>>> the primary key. I don't know what happend in this  table, some
>>>> rows have exactly the same value ( i also have set  the #STU as
>>>> unique). After tracing the table, i found that the  two rows differ
>>>> in ctid value. As the impact, my application  cannot operate well.
>>>
>>> There are some known bugs in older PG releases that could lead to
>>> duplicate rows (actually, to multiple versions of a row all being
>>> seen as live). If you're not on the latest minor version of your
>>> release series, update. regards, tom lane
>>
>>
>>
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:789,47d19036233095581217154!
>
>