Re: [SQL] Chaning locale sorting order for statements
I am not sure about comparation performance. I have big problems with sort performance in pg by comparing varchar. Also I am not sure if it is possible to get this new funktion to replace the standart sort. regards, Ivan. Tomasz Myrta wrote: > Uz.ytkownik Albrecht Berger napisa?: > > In Oracle I would use something like that : > > SELECT * FROM table1 > > ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH'); > > > > Does postgres has something similar ? > I think, you have to create function NLSSORT by your own. > C language would be nice for performance. > It is possible to create operator which compares two strings, but I > don't know how to tell him what the language is used now. > Regards, > Tomasz Myrta > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Deleting in order from a table
Hi , I think the question is stupied, but it is importand for me. I have a table tableA ( ,order_num int). I will to delete some records from tableA but in asc or desc order_num-order. Is it possible to write delete from tableA where (some conditions) order by order_num ? Many thanks, ivan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] update and IN vs. EXISTS
Hi, I have 2 tables Table1 and Table2. The PK for Table1 is declared as name. Table 2 have only 1 field and it is also name ( it is indexed). I will to update all Table1.filedForUpdate for all rows that exists in Table2. In Table1 I have ~ 120 000 rows and in Table2 I have ~ 100 000. If I execute: update Table1 set fieldForUpdate = 1; it takes ~ 28 sec. I test it only to know how much time will I need for all rows. If I try to execute: update Table1 set fieldForUpdate = 1 where ID IN (select T2.ID from Table2); it is running very slow. I do not nkow how many time, but I waited ~ 30 min without to get result. I tested anoder query: update Table1 set fieldForUpdate = 1 where ID IN ( select T1.ID from Table1 T1 where exists (select * select T2.ID from Table2 where T1.IDS=T2.IDS )); and it was running > 30 min ( I do not know how many). And the last query: update Table1 set fieldForUpdate = 1 from Tablet T1 where EXISTS (select * select T2.ID from Table2 where T1.IDS=T2.IDS ); and it was also > 30 min. How can I speed up this update? I have executed vacuum and vacuum full analyze. redards, ivan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] update and IN vs. EXISTS
Bruno Wolff III wrote: > On Sat, Feb 01, 2003 at 12:40:00 +0100, > pginfo <[EMAIL PROTECTED]> wrote: > > > > If I try to execute: > >update Table1 set fieldForUpdate = 1 where ID IN (select T2.ID from > > Table2); > > it is running very slow. > > You might try: > update Table1 set fieldForUpdate = 1 from Table2 where Table1.id = Table2.id; > It is great.It takes 122 sec. With IN it takes 8000 sec. > This uses a nonstandard postgres extension and may not be portable, if that > is a concern. > How to resolve the problem with the standart?regards, iavn. > INs are being speeded up in 7.4, so the original form might work a lot better > in the next release. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL Syntax problem
Hi Doris, In oracle (+) is left outer join or right outer join . You need to write: select... fromauswahlkatalog k, beteiligter b left outer join anspruchkorrektur a on(b.bet_id = a.bet_idemp) left outer join v_betkorr f on (a.ask_id = f.ask_id) where k.awk_id = a.awk_id ; regards, ivan. Doris Bernloehr wrote: > Hello. > > I've got a problem in porting the following select statement from Oracle to > Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the > where clause: (+) > I don't know what these characters mean and how I can transform these into > PostgreSql Syntax. > > select... > fromauswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f > where k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp > and a.ask_id = f.ask_id(+); > > Hoping for help. > Doris > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Interest query plan
Hi all,
I am running pg 7.3.1.
My query is very simple but pg generates not the best possible plan for
me:
analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M
ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060';
The plan is:
---
Nested Loop (cost=1.26..111442.07 rows=6 width=2091) (actual
time=99512.48..101105.48 rows=1 loops=1)
Join Filter: ("outer".ids = "inner".ids_doc)
-> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61 rows=1
width=1344) (actual time=0.13..0.14 rows=1 loops=1)
Index Cond: (ids = 'SOF_700060'::name)
-> Materialize (cost=99981.52..99981.52 rows=916555 width=747)
(actual time=96980.73..99907.73 rows=916555 loops=1)
-> Hash Join (cost=1.26..99981.52 rows=916555 width=747)
(actual time=9.34..86400.88 rows=916555 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Seq Scan on a_sklad s (cost=0.00..83940.55
rows=916555 width=712) (actual time=0.17..45881.02 rows=916555 loops=1)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual
time=8.79..8.79 rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21
width=35) (actual time=8.68..8.75 rows=21 loops=1)
Total runtime: 101563.40 msec
(11 rows)
I think the best olution will be first to left join a_doc and a_sklad
and after it to join a_sklad and a_med.
Can I force pg to execute this query better?
If I do not use left join, the query is very fast:
explain analyze select * from a_doc D,A_SKLAD S,A_MED M where d.IDS=s.
IDS_DOC AND S.IDS_MED=M.IDS AND d.IDS='SOF_700160';
QUERY
PLAN
---
Hash Join (cost=1.26..80.55 rows=6 width=2091) (actual
time=20.41..20.46 rows=1 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Nested Loop (cost=0.00..79.18 rows=6 width=2056) (actual
time=19.23..19.26 rows=1 loops=1)
-> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61
rows=1 width=1344) (actual time=0.59..0.60 rows=1 loops=1)
Index Cond: (ids = 'SOF_700160'::name)
-> Index Scan using i_sklad_ids_doc on a_sklad s
(cost=0.00..75.31 rows=22 width=712) (actual time=18.25..18.26 rows=1
loops=1)
Index Cond: ("outer".ids = s.ids_doc)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.36..0.36
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35)
(actual time=0.22..0.30 rows=21 loops=1)
Total runtime: 21.27 msec
(10 rows)
But I think it is very big penalty for this left join.
regards,
ivan.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Interest query plan
explain analyze select * from a_doc D left outer join A_SKLAD S
ON(D.IDS=S.IDS_DOC) left join A_MED M ON(S.IDS_MED=M.IDS) where
d.IDS='SOF_700060';
QUERY PLAN
-
Hash Join (cost=1.26..80.55 rows=6 width=2091) (actual time=1.09..1.11
rows=1 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Nested Loop (cost=0.00..79.18 rows=6 width=2056) (actual
time=0.40..0.41 rows=1 loops=1)
-> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61 rows=1
width=1344) (actual time=0.14..0.14 rows=1 loops=1)
Index Cond: (ids = 'SOF_700060'::name)
-> Index Scan using i_sklad_ids_doc on a_sklad s (cost=0.00..75.31
rows=22 width=712) (actual time=0.12..0.13 rows=1 loops=1)
Index Cond: ("outer".ids = s.ids_doc)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.19..0.19
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=0.07..0.15 rows=21 loops=1)
Total runtime: 1.82 msec
(10 rows)
I thinked that a_sklad join a_med ... will help, but
Tomasz Myrta wrote:
> > Hi all,
> > I am running pg 7.3.1.
> > My query is very simple but pg generates not the best possible plan for
> > me:
> > analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M
> > ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060';
> What about:
>
> select * from a_doc D
> left join A_SKLAD S on(d.IDS=s.IDS_DOC)
> left join A_MED M ON(S.IDS_MED=M.IDS)
> where d.IDS='SOF_700060'
>
> ?
>
> Regards,
> Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Interest query plan
I have also another good example for a slow left join work.
Can I do it better?
explain analyze select * from a_doc D join A_SKLAD S ON(D.IDS=S.IDS_DOC) join
A_MED M ON(S.IDS_MED=M.IDS) where d
.date_op >= 9600 and d.date_op <= 9700;
QUERY PLAN
--
Hash Join (cost=13174.61..112873.53 rows=67002 width=2091) (actual
time=1439.74..86339.93 rows=50797 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Hash Join (cost=13173.35..111699.74 rows=67002 width=2056) (actual
time=1428.01..78454.80 rows=50797 loops=1)
Hash Cond: ("outer".ids_doc = "inner".ids)
-> Seq Scan on a_sklad s (cost=0.00..83940.55 rows=916555
width=712) (actual time=20.25..61817.66 rows=916555 loops=1)
-> Hash (cost=13145.43..13145.43 rows=11167 width=1344) (actual
time=1399.99..1399.99 rows=0 loops=1)
-> Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167
width=1344) (actual time=0.22..1316.10 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=11.18..11.18
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=11.06..11.14 rows=21 loops=1)
Total runtime: 86409.11 msec
(11 rows)
sklad10=# explain analyze select * from a_doc D left outer join A_SKLAD S
ON(D.IDS=S.IDS_DOC) left outer join A_MED M ON(S.IDS_MED=M.IDS) where
d.date_op >= 9600 and d.date_op <= 9700;
QUERY PLAN
Hash Join (cost=772073.87..778722.53 rows=67002 width=2091) (actual
time=129557.36..142125.53 rows=50797 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Merge Join (cost=772072.61..777548.74 rows=67002 width=2056) (actual
time=129556.40..134598.44 rows=50797 loops=1)
Merge Cond: ("outer".ids = "inner".ids_doc)
-> Sort (cost=13896.25..13924.17 rows=11167 width=1344) (actual
time=1403.35..1409.90 rows=9432 loops=1)
Sort Key: d.ids
-> Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167
width=1344) (actual time=0.19..1343.11 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
-> Sort (cost=758176.36..760467.75 rows=916555 width=712) (actual
time=123981.87..127939.17 rows=896110 loops=1)
Sort Key: s.ids_doc
-> Seq Scan on a_sklad s (cost=0.00..83940.55 rows=916555
width=712) (actual time=16.54..66513.61 rows=916555 loops=1)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.32..0.32
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=0.20..0.28 rows=21 loops=1)
Total runtime: 142598.55 msec
(14 rows)
sklad10=# explain analyze select * from a_doc D where d.date_op >= 9600 and
d.date_op <= 9700;
QUERY PLAN
Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167 width=1344) (actual
time=0.19..1300.47 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
Total runtime: 1309.19 msec
(3 rows)
regards,
ivan.
Tomasz Myrta wrote:
> > Hi all,
> > I am running pg 7.3.1.
> > My query is very simple but pg generates not the best possible plan for
> > me:
> > analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M
> > ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060';
> What about:
>
> select * from a_doc D
> left join A_SKLAD S on(d.IDS=s.IDS_DOC)
> left join A_MED M ON(S.IDS_MED=M.IDS)
> where d.IDS='SOF_700060'
>
> ?
>
> Regards,
> Tomasz Myrta
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Interest IN problem on 7.4
Hi, I am using pg 7.4. Pls, see this test: tt07=# update a_cars set dog_or_free=0 where virtualen=0 and dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND IDS = 'SOF_9989'; UPDATE 0 tt07=# update a_cars set dog_or_free=0 where virtualen=0 and dog_or_free=4 and ids IN ( select oc.ids_car_real from a_oferti _cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND IDS = 'SOF_9989'; UPDATE 0 tt07=# update a_cars set dog_or_free=0 where virtualen=0 and dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964 AND OC.IDS_CAR_REAL IS NOT NULL) AND IDS = 'SOF_9989'; UPDATE 1 I think IN is not working correct in this case. In my case A_CARS.IDS is name and also OC.IDS_CAR_REAL. It is interest that the last update is working well and the first one not. Pls, comment this problem. regards, ivan. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Interest IN problem on 7.4
Hi, thanks a lot. All is ok now. regards, ivan. Stephan Szabo wrote: > On Sat, 13 Dec 2003, pginfo wrote: > > > Hi, > > > > I am using pg 7.4. > > > > Pls, see this test: > > > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND > > IDS = 'SOF_9989'; > > UPDATE 0 > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids IN ( select oc.ids_car_real from a_oferti > > _cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) > > AND IDS = 'SOF_9989'; > > UPDATE 0 > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964 AND > > OC.IDS_CAR_REAL IS NOT NULL) AND IDS = 'SOF_9989'; > > UPDATE 1 > > > > I think IN is not working correct in this case. > > A NOT IN (subselect) when the subselect contains a NULL cannot ever return > true by specification. > > -- > A NOT IN (subselect) -> NOT (A IN (subselect)) > NOT (A IN (subselect)) -> NOT (A = ANY (subselect)) > > The result of A = ANY (subselect) is derived by the application of the > implied comparison predicate, R = RT for every row RT in the subselect. > > If the implied comparison predicate is true for at least one row RT then A > = ANY (subselect) is true. If the subselect is empty or the implied > predicate is false for every row RT then A = ANY (subselect) is false. > Otherwise it is unknown. > > For the one element row RT, A = RT where RT is a NULL returns unknown. > Therefore, we know that it's not an empty subselect (it returns at least > one row containing NULL -- that's our precondition), and that it does not > return false for every row, so A = ANY (subselect) is either true or > unknown depending on whether there's another row which does match, so > NOT(A = ANY(subselect)) is either false or unknown. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] JDBC Batch Updates
Hi, I think yes. I am running 7.4.1 and also using batchUpdate. I am using this from http://jdbc.postgresql.org/download.html. regards, ivan. beyaRecords - The home Urban music wrote: > Hi, > does anybody know whether the latest JDBC driver for postgreSQL 7.4.1 > supports batch updates? > > thanks in advance > > Uzo > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] pg primary key bug?
Hi , I am using pg 7.4.5 on RedHat AS 3.0. I am using it via jdbc and jboss. I have found big problem about the way pg supports primary keys. The bug was reported from my customers ( we are installed pg with our ERP on ~ 500 costomers) and I do not know hot it is happen. sklad21=# \d a_constants_str Table "public.a_constants_str" Column | Type | Modifiers +---+--- constname | character varying(30) | not null fid| integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) sklad21=# select * from a_constants_str ; constname | fid | constvalue ---+-+- AACCGRID | 0 | SOF_3 AKLTYPID | 0 | SOF_3 ADARID| 0 | SOF_2 AOBLASTID | 0 | SOF_6 AUSERID | 0 | SOF_17 ANMGRID | 0 | SOF_21 LOCAID| 0 | SOF_41 DOCID | 0 | SOF_1585254 DOCPLAID | 0 | SOF_1052900 AKLIID| 0 | SOF_18740 DOCRID| 0 | SOF_2268142 DOCPOGPLA | 0 | SOF_324586 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AKLGRID | 0 | SOF_45 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 ASETUPID | 0 | SOF_4605 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 TDOCID| 0 | SOF_337 TDOCRID | 0 | SOF_19450 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AGRADID | 0 | SOF_256 DOCID | 0 | SOF_1585254 ASLUID| 0 | SOF_46 NASTRF| 0 | SOF_88 ANOMID| 0 | SOF_1200 (30 rows) Pls., see the records with 'DOCID' and note we have primary key defined. At this moment we will create a new db and dump and reload the data. The old one will stay and if needet we will be able to study the files (the access to this customer is not very simple, but possible). regards, ivan. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] pg primary key bug?
Hi, It is not spaces problem. I needet to dump the data and I deleted all rows for 'DOCID' with delete from a_constants_str where constname= 'DOCID'; and the pg reported 10 rows deleted. Sorry I can not execute the commend now ( I dropped the data, becaus I needet the uniqu constnames for the restore). regards, ivan. Michael Glaesemann wrote: On Feb 3, 2005, at 21:53, pginfo wrote: I am using pg 7.4.5 on RedHat AS 3.0. sklad21=# \d a_constants_str Table "public.a_constants_str" Column | Type | Modifiers +---+--- constname | character varying(30) | not null fid| integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) sklad21=# select * from a_constants_str ; constname | fid | constvalue ---+-+- AACCGRID | 0 | SOF_3 AKLTYPID | 0 | SOF_3 ADARID| 0 | SOF_2 AOBLASTID | 0 | SOF_6 AUSERID | 0 | SOF_17 ANMGRID | 0 | SOF_21 LOCAID| 0 | SOF_41 DOCID | 0 | SOF_1585254 DOCPLAID | 0 | SOF_1052900 AKLIID| 0 | SOF_18740 DOCRID| 0 | SOF_2268142 DOCPOGPLA | 0 | SOF_324586 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AKLGRID | 0 | SOF_45 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 ASETUPID | 0 | SOF_4605 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 TDOCID| 0 | SOF_337 TDOCRID | 0 | SOF_19450 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AGRADID | 0 | SOF_256 DOCID | 0 | SOF_1585254 ASLUID| 0 | SOF_46 NASTRF| 0 | SOF_88 ANOMID| 0 | SOF_1200 (30 rows) Pls., see the records with 'DOCID' and note we have primary key defined. It's unclear from just this data, but there's a chance that there are varying numbers of spaces after 'DOCID', which would appear as separate values for the index, even though they aren't readily apparent. Could you show us the results of the following query? select constname, length(constname) as constname_length from a_constants_str; Here's another one which would show if PostgreSQL is treating them equally: select constname, count(constname) from a_constants_str; The results of these queries might shed some light on the issue. Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg primary key bug?
Hi all,
Sorry for my post, but I think it is pg primary key bug.
It is secont time in that we found the bug (see the replays for this message).
We have many server with pg and use it over jdbc + jboss.
I am not able to stop this server for long time, but I have dumped the problem
table.
It is very important to know if it is bug, because we have many server running
pg + our ERP and continuing to install new.
01=# select * from a_constants_str order by constname;
constname | fid | constvalue
---+-+
AACCGRID | 0 | SOF_3
ADARID | 0 | SOF_2
AGRADID | 0 | SOF_165
AKLGRID | 0 | SOF_8
AKLIID | 0 | SOF_3513
AKLTYPID | 0 | SOF_3
ANMGRID | 0 | SOF_10
ANOMID | 0 | SOF_747
AOBLASTID | 0 | SOF_3
ASETUPID | 0 | SOF_399
ASLUID | 0 | SOF_17
AUSERID | 0 | SOF_3
DOCID | 0 | SOF_25658
DOCPLAID | 0 | SOF_19738
DOCPLAID | 0 | SOF_19738
DOCPOGPLA | 0 | SOF_24281
DOCRID | 0 | SOF_184547
LOCAID | 0 | SOF_13
NASTRF | 0 | SOF_1
TDOCID | 0 | SOF_47
TDOCRID | 0 | SOF_2439
(21 rows)
01=# select * from a_constants_str where constname='DOCPLAID' ;
constname | fid | constvalue
---+-+
DOCPLAID | 0 | SOF_19738
DOCPLAID | 0 | SOF_19738
(2 rows)
01=# \d a_constants_str;
Table "public.a_constants_str"
Column | Type | Modifiers
+---+---
constname | character varying(30) | not null
fid | integer | not null
constvalue | character varying(30) |
Indexes:
"a_constants_str_pkey" primary key, btree (constname, fid)
regards,
ivan
[EMAIL PROTECTED] wrote:
There are not enough spaces available in the column to allow for the number
of DOC_IDs. There are three spaces, allowing for at most four occurrences
of DOC_ID, but there are eleven. --Rick
Michael Glaesemann
<[EMAIL PROTECTED]To: pginfo <[EMAIL PROTECTED]>
> cc: pgsql-sql@postgresql.org
Sent by: Subject: Re: [SQL] pg primary key bug?
[EMAIL PROTECTED]
tgresql.org
02/03/2005 09:14 AM
On Feb 3, 2005, at 21:53, pginfo wrote:
I am using pg 7.4.5 on RedHat AS 3.0.
sklad21=# \d a_constants_str
Table "public.a_constants_str"
Column | Type | Modifiers
+---+---
constname | character varying(30) | not null
fid| integer | not null
constvalue | character varying(30) |
Indexes:
"a_constants_str_pkey" primary key, btree (constname, fid)
sklad21=# select * from a_constants_str ;
constname | fid | constvalue ---+-+-
AACCGRID | 0 | SOF_3
AKLTYPID | 0 | SOF_3
ADARID| 0 | SOF_2
AOBLASTID | 0 | SOF_6
AUSERID | 0 | SOF_17
ANMGRID | 0 | SOF_21
LOCAID| 0 | SOF_41
DOCID | 0 | SOF_1585254
DOCPLAID | 0 | SOF_1052900
AKLIID| 0 | SOF_18740
DOCRID| 0 | SOF_2268142
DOCPOGPLA | 0 | SOF_324586
DOCID | 0 | SOF_1585254
DOCID | 0 | SOF_1585254
DOCID | 0 | SOF_1585254
AKLGRID | 0 | SOF_45
DOCID | 0 | SOF_1585254
DOCID | 0 | SOF_1585254
ASETUPID | 0 | SOF_4605
DOCID | 0 | SOF_1585254
DOCID | 0 | SOF_1585254
TDOCID| 0 | SOF_337
TDOCRID | 0 | SOF_19450
DOCID | 0 | SOF_1585254
DOCID | 0 | SOF_1585254
AGRADID | 0 | SOF_256
DOCID | 0 | SOF_1585254
ASLUID| 0 | SOF_46
NASTRF|
Re: [SQL] pg primary key bug?
Hi,
No, I do not inherited tables.
The result of this query is:
01=# SELECT tableoid::regclass, *
01-# FROM a_constants_str
01-# WHERE constname = 'DOCPLAID';
tableoid | constname | fid | constvalue
-+---+-+
a_constants_str | DOCPLAID | 0 | SOF_19738
a_constants_str | DOCPLAID | 0 | SOF_19738
(2 rows)
regards,
ivan.
Michael Fuhr wrote:
On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote:
01=# select * from a_constants_str where constname='DOCPLAID' ;
constname | fid | constvalue
---+-+
DOCPLAID | 0 | SOF_19738
DOCPLAID | 0 | SOF_19738
(2 rows)
Do you have any inherited tables? What's the result of the following
query?
SELECT tableoid::regclass, *
FROM a_constants_str
WHERE constname = 'DOCPLAID';
Inherited tables are documented to have deficiencies regarding
constraints. Observe:
CREATE TABLE parent (
constname varchar(30) NOT NULL,
fid integer NOT NULL,
constvalue varchar(30),
PRIMARY KEY (constname, fid)
);
CREATE TABLE child () INHERITS (parent);
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
ERROR: duplicate key violates unique constraint "parent_pkey"
INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738');
SELECT tableoid::regclass, * FROM parent;
tableoid | constname | fid | constvalue
--+---+-+
parent | DOCPLAID | 0 | SOF_19738
child| DOCPLAID | 0 | SOF_19738
(2 rows)
Re: [SQL] pg primary key bug?
Hi, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: 01=# select * from a_constants_str where constname='DOCPLAID' ; constname | fid | constvalue ---+-+ DOCPLAID | 0 | SOF_19738 DOCPLAID | 0 | SOF_19738 (2 rows) Could we see the system columns on these rows? select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where ... regards, tom lane yes, 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue +-+-+-+-+-+---+-+ 17910 | 22331 | 2 | 2 | 26679 | (0,7) | ANMGRID | 0 | SOF_10 17908 | 985 | 6 | 6 | 0 | (0,18) | AKLTYPID | 0 | SOF_3 17907 | 985 | 30 | 30 | 0 | (0,21) | ADARID | 0 | SOF_2 17921 | 985 | 34 | 34 | 0 | (0,22) | AOBLASTID | 0 | SOF_3 17911 | 4640 | 6 | 6 | 26679 | (0,24) | AACCGRID | 0 | SOF_3 17920 | 1220598 | 2 | 2 | 1475630 | (0,47) | ASLUID | 0 | SOF_17 17917 | 643083 | 2 | 2 | 1475630 | (0,49) | LOCAID | 0 | SOF_13 17918 | 762851 | 3 | 3 | 1475630 | (0,50) | AUSERID | 0 | SOF_3 17923 | 35539 | 165 | 165 | 1475630 | (0,51) | ASETUPID | 0 | SOF_399 283686 | 514327 | 3 | 3 | 1475630 | (0,52) | NASTRF | 0 | SOF_1 17909 | 2156667 | 2 | 2 | 2193198 | (0,54) | AKLGRID | 0 | SOF_8 17922 | 2103298 | 2 | 2 | 2193198 | (0,55) | AGRADID | 0 | SOF_165 17913 | 2092705 | 2 | 2 | 2193198 | (0,56) | ANOMID | 0 | SOF_747 63247 | 2226373 | 2 | 2 | 2233003 | (0,126) | TDOCID | 0 | SOF_47 17914 | 2232893 | 2235861 | 2235861 | 3 | (1,125) | DOCID | 0 | SOF_25658 17915 | 2232893 | 2235861 | 2235861 | 19 | (3,38) | DOCRID | 0 | SOF_184547 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | SOF_19738 17919 | 2232893 | 231 | 231 | 2233003 | (5,104) | DOCPOGPLA | 0 | SOF_24281 17912 | 2231139 | 2 | 2 | 0 | (6,1) | AKLIID | 0 | SOF_3513 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | SOF_19738 63249 | 2226373 | 103 | 103 | 0 | (16,31) | TDOCRID | 0 | SOF_2439 (21 rows) and 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where constname='DOCPLAID'; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue ---+-+-+-+--++---+-+ 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | SOF_19738 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | SOF_19738 (2 rows) regards, ivan.
Re: [SQL] pg primary key bug?
Hi, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Tom Lane wrote: Could we see the system columns on these rows? 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where constname='DOCPLAID'; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue ---+-+-+-+--++---+-+ 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | SOF_19738 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | SOF_19738 (2 rows) Given the identical OID and xmin values, it seems certain that these are the "same" row, ie there was only one insertion event. My bet is that the one at (7,62) is the original, and that the one at (4,71) is a copy that was made by VACUUM FULL trying to move the row to compact the table. So the question is how did both copies get to be marked simultaneously valid? That should be impossible, unless a disk write got dropped. Have you had any system crashes during VACUUM FULL operations recently? regards, tom lane I do not know exact, but it is possible. This is customer server and I do not have any info about server/os crash, but I am 100% sure. I will ask as soon as possible and replay. Also I will check the logs. We start pg witl pg_ctl ...-D... -l /logfile and if we can found the needet info in pg log file I can check it ( I do not nkow what to search in this file). We are running vacuum once per 24 h. The OS is RedHat AS 3. PostgreSQL is 7.4.1 (but we have the same problem on 7.4.5). Will upgrade to 8.0 solve this type of problems ? regards, ivan.
Re: [SQL] pg primary key bug?
Hi, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Will upgrade to 8.0 solve this type of problems ? The problem is probably not Postgres' fault at all. I'm wondering about disks with write cacheing enabled. And you didn't turn off fsync, I trust? About fsync (part from postgresql.conf) : #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # 0 is off, in seconds Also part from pg logfile: LOG: statistics collector process (PID 2716) exited with exit code 1 LOG: shutting down LOG: database system is shut down LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was shut down at 2005-02-11 19:58:26 EET LOG: checkpoint record is at 2/BAC39188 LOG: redo record is at 2/BAC39188; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 2221145; next OID: 826607 LOG: database system is ready LOG: recycled transaction log file "000200BA" LOG: recycled transaction log file "000200BB" LOG: recycled transaction log file "000200BC" LOG: recycled transaction log file "000200BD" LOG: recycled transaction log file "000200BE" WARNING: index "a_constants_str_pkey" contains 1449 row versions, but table contains 1422 row versions HINT: Rebuild the index with REINDEX. ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" LOG: received smart shutdown request FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command LOG: statistics collector process (PID 2713) exited with exit code 1 LOG: shutting down LOG: database system is shut down LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was shut down at 2005-02-16 08:32:21 EET
Re: [SQL] pg primary key bug?
Hi Iain,
Iain wrote:
Hi Ivan,
Sorry, I can't remember all you
said in earlier posts, but I was wondering, your log file says:
> HINT: Rebuild the index with REINDEX.
Did you do that, and did it solve
the problem?
No it do not solve the problem.
I sendet the log only to show that we do not have any server crash nor pg
restart.
regards,
ivan.
regards
Iain
-
Original Message -
From:
pginfo
To:
Tom Lane
Cc:
[EMAIL PROTECTED]
; Michael
Glaesemann ; pgsql-sql@postgresql.org ; [EMAIL PROTECTED]
Sent:
Friday, February 18, 20051:53 PM
Subject:
Re: [SQL] pg primary keybug?
Hi,
Tom Lane wrote:
pginfo <[EMAIL PROTECTED]> writes:
Will upgrade to 8.0 solve this type of problems ?
The problem is probably not Postgres' fault at all. I'm wondering about
disks with write cacheing enabled. And you didn't turn off fsync,
I trust?
About fsync (part from postgresql.conf):
#---
#WRITE AHEADLOG
#---
#- Settings -
#fsync =true # turns forced synchronization on or
off
#wal_sync_method =fsync # the default varies acrossplatforms:
# fsync, fdatasync, open_sync, or open_datasync
#wal_buffers =8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments =3 # in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 300 # range30-3600, in seconds
#checkpoint_warning =30 # 0 is off, inseconds
Also part from pg logfile:
LOG: statisticscollector process (PID 2716) exited with exit code 1
LOG: shuttingdown
LOG: database system is shut down
LOG: could not createIPv6 socket: Address family not supported by protocol
LOG: databasesystem was shut down at 2005-02-11 19:58:26 EET
LOG: checkpointrecord is at 2/BAC39188
LOG: redo record is at 2/BAC39188; undorecord is at 0/0; shutdown TRUE
LOG: next transaction ID: 2221145;next OID: 826607
LOG: database system is ready
LOG: recycledtransaction log file "000200BA"
LOG: recycled transaction logfile "000200BB"
LOG: recycled transaction log file"000200BC"
LOG: recycled transaction log file"000200BD"
LOG: recycled transaction log file"000200BE"
WARNING: index "a_constants_str_pkey" contains1449 row versions, but
table contains 1422 row versions
HINT: Rebuildthe index with REINDEX.
ERROR: duplicate key violates uniqueconstraint "a_constants_str_pkey"
ERROR: duplicate key violatesunique constraint "a_constants_str_pkey"
ERROR: duplicate keyviolates unique constraint "a_constants_str_pkey"
ERROR: duplicatekey violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint"a_constants_str_pkey"
ERROR: duplicate key violates uniqueconstraint "a_constants_str_pkey"
ERROR: duplicate key violatesunique constraint "a_constants_str_pkey"
ERROR: duplicate keyviolates unique constraint "a_constants_str_pkey"
ERROR: duplicatekey violates unique constraint "a_constants_str_pkey"
ERROR: duplicate key violates unique constraint"a_constants_str_pkey"
ERROR: duplicate key violates uniqueconstraint "a_constants_str_pkey"
LOG: received smart shutdownrequest
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating connection due to administratorcommand
FATAL: terminating
Re: [SQL] pg primary key bug?
Hi, sorry, but we have the case number 3 in with the same problem. Also this time we do not find any linux box crash nor pg stop or restart. The pg version is 7.4.2 on dual xeon + scsi running also RedHat 3.0 AS. In all the cases we are running RedHat AS 3.0. This system was running for over 12 m. without any problems. I send also the state of the problem table (also the same) and my question is: Need we to stop using vacuum full for now? And can only vacuum analyze make the same problem in pg? As I understand the problem is in OS by making vacuum full analyze (as Tom wrote). We do not found any problems in OS and the ony solution we see is to stop using vacuum full analyze. Also we are using only jdbc to access pg. Is it possible that jdbc to make this problem? regards, ivan. serv117=# select oid, xmin, cmin, xmax, cmax, ctid, * from a_constants_str ; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue ---+-+-+-+-+--+---+-+- 760807304 | 7357839 | 0 | 0 | 0 | (0,1) | PARTID | 0 | SOF_79 760807305 | 7357839 | 0 | 0 | 0 | (0,2) | AACCGRID | 0 | SOF_29 760807306 | 7357839 | 0 | 0 | 0 | (0,3) | AKLTYPID | 0 | SOF_47 760807307 | 7357839 | 0 | 0 | 0 | (0,4) | AOBLASTID | 0 | SOF_41 760807308 | 7357839 | 0 | 0 | 0 | (0,5) | ANMGRID | 0 | SOF_102 760807309 | 7357839 | 0 | 0 | 0 | (0,6) | LOCAID | 0 | SOF_112 760807310 | 7357839 | 0 | 0 | 0 | (0,7) | AKLGRID | 0 | SOF_116 760807311 | 7357839 | 0 | 0 | 0 | (0,8) | ADARID | 0 | SOF_33 760807314 | 7357839 | 0 | 0 | 0 | (0,11) | ASLUID | 0 | SOF_86 760807315 | 7357839 | 0 | 0 | 0 | (0,12) | AUSERID | 0 | SOF_28 760807318 | 7357839 | 0 | 0 | 0 | (0,15) | ANLIZPID | 0 | SOF_100137 760807316 | 7507505 | 3 | 3 | 0 | (0,36) | ASETUPID | 0 | SOF_4618 760807324 | 7750088 | 7766293 | 7766293 | 2 | (0,92) | DOCID | 0 | SOF_836141 760807319 | 7740812 | 2 | 2 | 0 | (4,8) | ANOMID | 0 | SOF_31353 760807325 | 7750088 | 19 | 19 | 0 | (4,111) | DOCRID | 0 | SOF_2067257 760807326 | 7750088 | 41 | 41 | 7750975 | (6,27) | DOCPLAID | 0 | SOF_44261 760807327 | 7750088 | 46 | 46 | 7750975 | (7,106) | DOCPOGPLA | 0 | SOF_58034 760807324 | 7750088 | 7766293 | 7766293 | 1 | (9,107) | DOCID | 0 | SOF_836141 760807313 | 7680519 | 2 | 2 | 0 | (10,3) | NASTRF | 0 | SOF_161 760807312 | 7688072 | 2 | 2 | 0 | (10,92) | AGRADID | 0 | SOF_804 760807324 | 7750088 | 7766293 | 7766293 | 1 | (12,18) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (13,94) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (15,45) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (17,4) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (18,80) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (20,31) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (21,109) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (23,58) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (25,9) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (26,85) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (28,36) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (29,114) | DOCID | 0 | SOF_836141 760807317 | 7702028 | 2 | 2 | 0 | (51,41) | AMITAID | 0 | SOF_345 760807320 | 7702064 | 2 | 2 | 0 | (51,42) | ATRANSID | 0 | SOF_458 760807321 | 7707993 | 2 | 2 | 0 | (57,8) | TDOCID | 0 | SOF_546 760807323 | 7753774 | 3 | 3 | 0 | (59,7) | AKLIID | 0 | SOF_22695 760807322 | 7707993 | 2385 | 2385 | 0 | (59,95) | TDOCRID | 0 | SOF_105930 (37 rows) serv117=# \d a_constants_str Table "public.a_constants_str" Column | Type | Modifiers +---+--- constname | character varying(30) | not null fid | integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Will upgrade to 8.0 solve this type of problems ? The proble
Re: [SQL] pg primary key bug?
Hi Tom, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: sorry, but we have the case number 3 in with the same problem. Also this time we do not find any linux box crash nor pg stop or restart. Hmm, well there's *something* mighty curious about the history of this table. The xmin values span a range of almost 400,000 transactions and yet the oids are twenty-three consecutive values. Is this the only table you've inserted into during the last 400K transactions? No. It's also odd that there's so much empty space (only 37 rows in 60 pages). It's hard to see how that could be unless there were many updates on the table, but judging from the name and contents of the table I can hardly see a reason for lots of updates. How is this table used exactly? In this table we store the last value for the ID of part from other tables. For each table we have one constant in this table. We are using the table as sequence. For Example if we nee to insert the next record in some table we make: select constvalue from a_constants_str where constname ='...' for update; increase the value and make update a_constants_str set constvalue= (new value) where... It is not so easy as I described, but in general this is the case. Al this is connected with replications and data syncronisation and so on. Do you do UPDATEs on it? Yes, see the description. DELETEs? No, never. SELECT FOR UPDATE? Yes. Do you do VACUUM, or VACUUM FULL, or both? Only vacuum full analyze once per day. Also once per 4h pg_dump (for pg 7.x we do not have any chance to restore data without full dump. With 8.0 we will test the ne solution and stop to make pg_dump. We have > 200 relative big installs for this ERP and all are using pg. For the last 3 y. we have one time data corruption and in this 3 cases problem with pkey's. All the time pg is working relative well . regards, ivan. regards, tom lane
Re: [SQL] pg primary key bug?
Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: In this table we store the last value for the ID of part from other tables. For each table we have one constant in this table. We are using the table as sequence. For Example if we nee to insert the next record in some table we make: select constvalue from a_constants_str where constname ='...' for update; increase the value and make update a_constants_str set constvalue= (new value) where... It is not so easy as I described, but in general this is the case. Al this is connected with replications and data syncronisation and so on. "Connected"? What exactly is hiding under that last comment? We are using separate table for sequences and not sequences from pg direct, because this is built in application method for making replication and data syncro. I wish only to clarify the reason of using the table and to describe the groud for so many updates and select for updates. Sorry for my bad english ):. One way I could take your report is that you've found a weird interaction between SELECT FOR UPDATE and VACUUM FULL that no one else has seen before. Another way is that you're using some nonstandard backend extension that has nasty bugs in it. It is interesting that you say this system has been working well for years and only recently have you seen problems. Yes, exact. To me the obvious question is "what have you changed recently?" If I know ! In general we do not make any global changes connected to database access method. We are using jdbc (jdbc driver from pg) + jboss (java based application server) + connection pool (biult in jboss). We are using jdbc with Statement.executeBatch(...) and also direct with Statement.executeUpdate(...) . We are using exact the same ide with oracle without any problem ( oracle have anoder problems and I prefer pg). It might not be a bogus change in itself, but it could have triggered a bug at lower levels. It's certainly possible that you have your finger on a backend bug, but if so there's not nearly enough information here for anyone to find and fix it. I am sure (not 100%) that it is bug. That is the reason to report the problem. You need to be thinking in terms of how to reproduce the problem so that it can be studied and fixed. You idea was that we have "vacuum full" + update or select for update in the same time. I think it is not the case, because we start vacuum full at 1:00 AM and no one is working in this time. Will vacuum full generate this problem if we have locked table in this time? (It is possible to have locked table in theory) At this time we do not have info about how to reproduce the problem. As the first step we will stop using "vacum full" (if needet we will stop using vacuum analyze too) to try to collect more info. "How can I avoid this problem" is exactly the wrong question to be asking, because even if avoiding it is all that concerns you, no one can answer with any confidence until we understand what the failure mechanism is. Can we set some log parameters to collect the needet data? Can you describe more detailed the idea of problem with "vacuum full" + "update" and can some one make patch if this problem exists in theory (if I understand you right)? We can start using this patch and see if the problem will be again . If you have anoder Idea we are ready to collect the needet data. regards, tom lane regards, ivan.
Re: [SQL] pg primary key bug?
Ragnar Hafstað wrote: On Tue, 2005-02-22 at 10:33 +0100, pginfo wrote: We are using jdbc (jdbc driver from pg) + jboss (java based application server) + connection pool (biult in jboss). ... Will vacuum full generate this problem if we have locked table in this time? (It is possible to have locked table in theory) I do not know if this is relevant, but I have seen jboss applications keep sessions in 'Idle in transaction' state, apparently with some locks granted. Would such cases not interfere with vacuum? gnari Only to add, also keeping sme transactions for long time not commited (possible). regards, ivan.
Re: [SQL] pg primary key bug?
Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: You idea was that we have "vacuum full" + update or select for update in the same time. I think it is not the case, because we start vacuum full at 1:00 AM and no one is working in this time. Hmm. AFAICT the duplicate row copies could only be produced by vacuum full, so that's certainly part of the issue. But if vacuum full in isolation were broken, we'd surely know it; so there must be some other contributing factor involved that your setup is exercising but other people are (mostly) not doing. I agree with the plan to use plain vacuum for awhile and see if that makes the problem go away. I think it would have to, but maybe I'm all wet about that. Ok, we can still using vacuum full on some installs (with risk to make problems to customes). I will to ask if it will be possible to start some querys (I do not know the query) exactly before running vacuum full and to save the results in some log file. If it is possible, we will be able to post the results to the list in case of ne problem and to have some start point for reproducing the problem. My idea is some one more familiar with pg to send this querys (if it exists) and we will install it in vacuum scripts. In the meantime I would suggest seeing if you can distill your application down into a test case that other people can run to reproduce the problem. It doesn't matter if the test doesn't make the bug happen very often, but we have to see the problem happening before we have much hope of fixing it. Will vacuum full generate this problem if we have locked table in this time? (It is possible to have locked table in theory) No, that's hardly likely. vacuum full deals with locks all the time. Can you describe more detailed the idea of problem with "vacuum full" + "update" and can some one make patch if this problem exists in theory (if I understand you right)? I have no idea what the actual failure mechanism might be. regards, tom lane regards, ivan.
Re: [SQL] pg primary key bug?
Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: I will to ask if it will be possible to start some querys (I do not know the query) exactly before running vacuum full and to save the results in some log file. If it is possible, we will be able to post the results to the list in case of ne problem and to have some start point for reproducing the problem. Well, you should definitely turn on log_statement across the whole installation so that you have a complete record of all SQL commands being issued. Make sure the log includes timestamps and PIDs. I would suggest adding a simple probe for duplicate records to the vacuum script. Maybe something like set enable_indexscan to off; select constname,fid,count(*) from a_constants_str group by constname,fid having count(*) > 1; (The indexscan off bit is just paranoia --- I think that an indexscan might mask the presence of multiple copies of what's supposedly a unique key.) Do this just before and just after the vacuum full command. That will at least nail down whether vacuum full is creating the dups, and once we see it happen the trace of the day's SQL commands may give some ideas where to look. Ok, it was my idea. We will do it and install the script in ~100 servers and will see the result. regards, tom lane regards, ivan.
[SQL] lower and unicode
Hi , I tested the lower with unicode on 8.0.1 and find it not to work. If I have only latin symbols it is working well, but if I try to use also cyrillic the lower simpli ignore this symbols and all stay as is. I readet that this will work on 8.x . Exists some one using lower/upper + unicode and where can I find info about unicode status with pg? regards, ivan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] lower and unicode
Hi, I am uusing pg 8.0.1 on FreeBSD 5.3 but I am ready t use the version taht supports correct unicode. regards, ivan. Miroslav Šulc wrote: Are you using PostgreSQL on Windows? If so, you should read the FAQ here http://pginstaller.projects.postgresql.org/FAQ_windows.html#2.6. On Windows, UNICODE (UTF8) is not supported because Windows natively supports only UTF16 (I'm just repeating something I have read somewhere). Miroslav pginfo wrote: Hi , I tested the lower with unicode on 8.0.1 and find it not to work. If I have only latin symbols it is working well, but if I try to use also cyrillic the lower simpli ignore this symbols and all stay as is. I readet that this will work on 8.x . Exists some one using lower/upper + unicode and where can I find info about unicode status with pg? regards, ivan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] lower and unicode
Hi, I am using --no-locale by init db. I readet that if I am using some locale the pg will work very slow. Have you noticed some speed penalty by using cs_CZ.utf8. regards, ivan. Miroslav Šulc wrote: pginfo wrote: Hi, I am uusing pg 8.0.1 on FreeBSD 5.3 but I am ready t use the version taht supports correct unicode. I think that should be fine. I use PostgreSQL 8.0.1 on Linux (Gentoo) without these problems (I used cs_CZ.utf8 to init my db). What you write seems to me that you have your database initialized to something else than xy_XY.utf8 which must be used to have databases working correctly with UNICODE. Try 'locale -a' and choose your locale that has *.utf8 to initialize the database. Then things should be O.K. :-) I don't remember I've seen some note in documentation that users must use for databases the same encoding as they used for initdb. If this is true, it would be time saving to mention this in documentation (if it is not already there). regards, ivan. Miroslav ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
