Thread: Ask ctid

Ask ctid

From
Achmad Nizar Hidayanto
Date:
Dear all,

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.

It happened after restarting my server. Anyone can help me how to
solve this problem. Many thanks for your help.


Regards,

Nizar
=====

Re: Ask ctid

From
Martijn van Oosterhout
Date:
On Thu, Mar 06, 2008 at 05:40:00PM +0700, Achmad Nizar Hidayanto wrote:
> Dear all,
>
> 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.

You need to provide way more information. Your description is kinda
vague. Why are youusing the ctid any, it's not a stable identifier for
a row...

Please show us actual output from psql showing your problem and the
definitions of your tables.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Attachment

Re: Ask ctid

From
Tom Lane
Date:
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

Re: Ask ctid

From
Achmad Nizar Hidayanto
Date:
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?

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
 

Re: Ask ctid

From
Martijn van Oosterhout
Date:
On Fri, Mar 07, 2008 at 03:28:12PM +0700, Achmad Nizar Hidayanto wrote:
> 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?

There were some older versions that had some bugs in this area. Whether
this applies to you I don't know. You did not tell us what version you
are running...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Attachment

Re: Ask ctid

From
Alban Hertroys
Date:
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:737,47d19038233091286811304!



Re: Ask ctid

From
Achmad Nizar Hidayanto
Date:
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!
>
>


Re: Ask ctid

From
Alvaro Herrera
Date:
Achmad Nizar Hidayanto wrote:

> 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

This would be a bug.  Please post the unmodified output of

select ctid, xmin, xmax, cmin, cmax, id_absensi, id_user, tanggal
from absensi

The ctid column must differ because it is the physical address of the
tuple.


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.