Re: [HACKERS] select order by for update
От | Mark Wright |
---|---|
Тема | Re: [HACKERS] select order by for update |
Дата | |
Msg-id | 001e01beb353$1541e4d0$c62812ac@markw_compaq обсуждение исходный текст |
Список | pgsql-hackers |
From: Vadim Mikheev <vadim@krs.ru> |> There is another problem in subj - sometimes application gets |> ERROR: EvalPlanQual: t_xmin is uncommitted ?! |> I'll try to find why. Mark (Wright), could you avoid |> order by in PL function? If you really need in ordered |> updates then try to create index on id_number and add |> id_number >= 0 to WHERE in select for update. | |Ops, this will not work in all cases. Try to rewrite select: | |SELECT * FROM test_attendees |WHERE print_status = 'R' |AND id_number = (select min(id_number) from test_attendees) |FOR UPDATE OF test_attendees | |and run it in loop. That would only work the first time, since after updating print_status to 'C', the record where id_number = (select min(id_number) from test_attendees) would no longer have print_status = 'R', so no records would match the query. The solution would appear to be to replace the clause '(select min(id_number) from test_attendees)' with '(select min(id_number) from test_attendees where print_status = 'R')'. However, that would not work, since the subselect doesn't block (see the pgsql mailing list for an explanation from Jan Wieck - http://www.postgresql.org/mhonarc/pgsql-sql/1999-06/msg00049.html - my current solution is from his suggestion). I need the ordered select, since I'm trying to create a FIFO. I have one set of clients who are entering records into the table, and another set of clients who are taking those records and sending them to a printer. I need the printers to output records in more or less the same order that they were entered. --- Mark Wright mwright@pro-ns.net mark_wright@datacard.com
В списке pgsql-hackers по дате отправления: