From: | "Mark Wright" <mwright(at)pro-ns(dot)net> |
---|---|
To: | "Vadim Mikheev" <vadim(at)krs(dot)ru>, "PostgreSQL Developers List" <hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] select order by for update |
Date: | 1999-06-10 15:08:17 |
Message-ID: | 001e01beb353$1541e4d0$c62812ac@markw_compaq |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
From: Vadim Mikheev <vadim(at)krs(dot)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(at)pro-ns(dot)net
mark_wright(at)datacard(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 1999-06-10 15:14:43 | Re: [HACKERS] Postgres 6.5 beta2 and beta3 problem |
Previous Message | Tom Lane | 1999-06-10 15:06:22 | Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance |