Re: [GENERAL] serial type (text instead of integer) and duplicate
Carlos Costa wrote:
Hello all!
There is an extrange error in my logfile:
update articles set online='t' where id = 391 ;
ERROR: duplicate key violates unique constraint articles_pkey
(the error exists only with this id)
I've checked (well, almost imposible) if there was more than one
article with this id:
select count(*) from articles where id=391 ;
count
---
1
(1 row)
Then, I checked the table:
Table public.articles
Column | Type | Modifiers
---++-
id| integer| not null default
nextval('articles_id_seq'::text)
Here is the origin of my problem, I think: text. text?. The
serial type generate text instead of integer. Really extrange.
No - it's saying that 'articles_id_seq' is text. The sequence is
returning a number.
So, my next query:
select id from articles where id like '%391%' ;
id
-
391
391
(2 rows)
The problem is easy to solve: delete and re-create the rows. But I
would like to know the origin of this error. Any tip?
You're seeing two copies here because this query doesn't use the index
(you're forcing PG to convert id to text). You should be able to
recreate it using:
SET enable_indexscan=false;
SELECT * FORM articles WHERE id = 391;
In fact, you should do:
SET enable_indexscan=false;
SELECT oid,xmin,xmax,ctid,id FROM articles WEHRE id=391;
This will show some system columns too. If you post the results of this
query, I'm sure one of the developers will be able to identify the issue.
I'm guessing the unique index has been corrupted somehow. Two questions:
1. What version of PostgreSQL are you running?
2. Have you had any crashes?
If it is the index, a reindex or drop/recreate will solve it, but let's
see what's in the system columns first.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] serial type (text instead of integer) and duplicate keys
Thank you very much.
Here is the output:
SELECT oid,xmin,xmax,ctid,id FROM articles WHERE id=391;
oid | xmin| xmax| ctid | id
-+---+---+--+-
3032469 | 152691945 | 152886684 | (104,6) | 391
3032469 | 152886687 | 156995994 | (104,13) | 391
(2 rows)
My version is 7.4.1
And no, I haven't had crashes. And I am using this database for 4-5 years.
Some days ago, the partition where my data is was full (just a few
seconds), but all the system continued running fine. (This is the only
possible cause of corruption I've detected).
On Apr 12, 2005 2:00 PM, Richard Huxton dev@archonet.com wrote:
Carlos Costa wrote:
Hello all!
There is an extrange error in my logfile:
update articles set online='t' where id = 391 ;
ERROR: duplicate key violates unique constraint articles_pkey
(the error exists only with this id)
I've checked (well, almost imposible) if there was more than one
article with this id:
select count(*) from articles where id=391 ;
count
---
1
(1 row)
Then, I checked the table:
Table public.articles
Column | Type | Modifiers
---++-
id| integer| not null default
nextval('articles_id_seq'::text)
Here is the origin of my problem, I think: text. text?. The
serial type generate text instead of integer. Really extrange.
No - it's saying that 'articles_id_seq' is text. The sequence is
returning a number.
So, my next query:
select id from articles where id like '%391%' ;
id
-
391
391
(2 rows)
The problem is easy to solve: delete and re-create the rows. But I
would like to know the origin of this error. Any tip?
You're seeing two copies here because this query doesn't use the index
(you're forcing PG to convert id to text). You should be able to
recreate it using:
SET enable_indexscan=false;
SELECT * FORM articles WHERE id = 391;
In fact, you should do:
SET enable_indexscan=false;
SELECT oid,xmin,xmax,ctid,id FROM articles WEHRE id=391;
This will show some system columns too. If you post the results of this
query, I'm sure one of the developers will be able to identify the issue.
I'm guessing the unique index has been corrupted somehow. Two questions:
1. What version of PostgreSQL are you running?
2. Have you had any crashes?
If it is the index, a reindex or drop/recreate will solve it, but let's
see what's in the system columns first.
--
Richard Huxton
Archonet Ltd
--
[ http://www.improveyourweb.com/ ]
web.log.about.web.development
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] serial type (text instead of integer) and duplicate keys
Carlos Costa [EMAIL PROTECTED] writes: SELECT oid,xmin,xmax,ctid,id FROM articles WHERE id=391; oid | xmin| xmax| ctid | id -+---+---+--+- 3032469 | 152691945 | 152886684 | (104,6) | 391 3032469 | 152886687 | 156995994 | (104,13) | 391 (2 rows) Could we see cmin,cmax as well? Since the OID is the same, these are evidently two versions of the same row; somehow one of them didn't get marked dead when the other one was created. What patterns of updating do you use on this table? Any SELECT FOR UPDATE? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SERIAL type - auto-increment grouped by other field
:(( sorry At server ther is istalled pgsql 7.1.2 but creatlang works from 7.2.X and upper version :( do you have any other ideas?? elein wrote: You just need to define 'plpgsql' as a language in your database. At the shell, to define the language use: createlang 'plpgsql' dbname; After you do the createlang, you can write functions using plpgsql. Let me know if you are still having trouble. --elein On Mon, Mar 01, 2004 at 09:52:33PM +0100, Paulovi?? Michal wrote: tnx a lot, but i am using PostgreSQL 7.1.2 and your script result errors: --- ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal, and created procedural languages. --- elein wrote: One possible implementation of a two level numbering outline will be published in PostgreSQL GeneralBits Issue #64 due out Monday morning, 3/1. http://www.varlena.com/GeneralBits/ elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com 1-866-VARLENA PostgreSQL Consulting, Support Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = Its a doggy dog world out there. On Sun, Feb 29, 2004 at 02:44:01AM +0100, Paulovi? Michal wrote: hi all, I have problem with SERIAL field type (or sequence functionality). I have table with three columns - ID, IDS, NAME. I want auto-increment IDS grouped by ID. Example: 1, 1, Ferdo 1, 2, John 2, 1, Martin 1, 3, Elvira 2, 2, Georgia but... when I use IDS - SERIAL and when I create UNIQUE (ID, IDS) result is: 1, 1, Ferdo 1, 2, John 2, 3, Martin 1, 4, Elvira 2, 5, Georgia where i make misstake??? how can i do it??? in documentation there is description only for one auto-increment column. I didn't find auto increment as I described upper. Do you have any idea how can I do it??? tnx a lot Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Serial Type
Hi Chris, If you do an INSERT straight into the table, AND also make sure you give it the value you want in the serial column, it will accept it and not increment the serial column's counter. There are also a few function which are useful with the serial type : currval() setval() nextval() See http://odbc.postgresql.org/docs/index.php?sql-createsequence.html for more info. Regards and best wishes, Justin Clift Christian Marschalek wrote: The serial data type automaticaly takes the next higher value, but what if there's a drop and I want to assing 3 even if there are 20 records and the next higher number would be 21 not 3? Tia Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Serial Type
Christian Marschalek writes: The serial data type automaticaly takes the next higher value, but what if there's a drop and I want to assing 3 even if there are 20 records and the next higher number would be 21 not 3? Then you can't use the serial type. Coding hole-filling serial numbers can be quite tricky and slow. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
