[SQL] order of rows in update
Hi, is there an (implicit) way to make a multirow update execute on some rows prior to other rows? It is needed in a case where a trigger is defined on the table as FOR EACH ROW, and it is mandatory that the trigger is run for some certain rows before it is run on the rest of the rows. Is there anything reliable to achieve this without making poor assumptions of the future versions, or should i just "SELECT ... ORDER BY ..." and then perform individual UPDATEs? Thanx -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order of rows in update
On Tue, Sep 2, 2008 at 2:58 AM, Achilleas Mantzios <[EMAIL PROTECTED]> wrote: > is there an (implicit) way to make a multirow update execute on some rows > prior to other rows? > It is needed in a case where a trigger is defined on the table as FOR EACH > ROW, and it is mandatory > that the trigger is run for some certain rows before it is run on the rest of > the rows. > > Is there anything reliable to achieve this without making poor assumptions of > the future > versions, or should i just "SELECT ... ORDER BY ..." and then perform > individual UPDATEs? The only way that I know how to do this is to create a named cursor of the rows that you want to update, and then for each record call UPDATE ... FROM ... WHERE CURRENT OF cursorname; But why are you even having this problem to begin with? What you are describing sounds like a database normalization problem. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How do I get min and max from an array of floating point values
Hello,
Is it possible to get the min and max from an array of
floating point values?
The following doesn't return the min of the array values
it simply returns the complete array...(??)
SELECT min(string_to_array('1,2,3,4,5,6,7',',')::float[]);
Thanks
Peter
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] seemingly slow for-loop in plpgsql
Hi.
I have a table with a hashcode-field which is a md5-checksum of a
file. I updated all null-entries using a rails-script by calling
'/sbin/md5 -q' (on FreeBSD). When all null-entries were updated I
found out that '\n' was added to the md5-checksum. :-)
So I wanted to update the table using plpgsql. As I understand it from
the docs
(http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html)
in section 38.6.1.2. RETURN NEXT and RETURN QUERY that ' ... if a
PL/pgSQL function produces a very large result set, performance might
be poor: ...'.
select count(*) from duplicates;
count
--
134673
select count(*) from duplicates where length(hashcode) = 33;
count
31731
\d duplicates
Table "public.duplicates"
Column | Type | Modifiers
--+--+-
id | integer | not null default
nextval('duplicates_id_seq'::regclass)
uid| integer |
filename | text |
hashcode | text |
Indexes:
"duplicates_hashcode_idx" btree (hashcode)
"duplicates_uid_idx" btree (uid)
create or replace function update_hashcode() returns setof duplicates as
$body$
declare
d duplicates%rowtype;
h text;
begin
for d in select * from duplicates where length(hashcode) = 33 loop
h := rtrim(d.hashcode, E'\n');
update duplicates set hashcode = h where id = d.id;
return next d;
end loop;
end
$body$
language 'plpgsql' ;
select count(*) from update_hashcode();
Postgres is 8.3.3 on FreeBSD current on a test-server with an opteron
at 2 GHz and 4 GB ram. The server is not the fastest around but I have
another table with 85 mill. entries where 12 mill. have '\n' as part
of the hashcode. The prod.server is a HP DL360 with a p800-controller
so it's much faster but the script will still be too slow to make this
solution viable.
How can I tune the plpgsql-script? Using cursors? I tried with a
cursor-based script and ended up with this skeleton-script:
create or replace function update_hashcode(refcursor) returns refcursor as '
declare
d duplicates%rowtype;
h text;
begin
open $1 for select * from duplicates;
return $1;
end;
' language plpgsql;
begin;
select update_hashcode('funccursor');
fetch next in funccursor;
commit;
which fetches the next row. But how can I iterate over the rows using cursors?
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentler gamester is the soonest winner.
Shakespeare
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] seemingly slow for-loop in plpgsql
"Claus Guttesen" <[EMAIL PROTECTED]> writes: > create or replace function update_hashcode() returns setof duplicates as > $body$ > declare > d duplicates%rowtype; > h text; > begin > for d in select * from duplicates where length(hashcode) = 33 loop > h := rtrim(d.hashcode, E'\n'); > update duplicates set hashcode = h where id = d.id; > return next d; > end loop; > end > $body$ > language 'plpgsql' ; Why in the world are you using a for-loop for this at all? It would be tremendously faster as a single SQL command: update duplicates set hashcode = rtrim(hashcode, E'\n') where length(hashcode) = 33; regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order of rows in update
Στις Tuesday 02 September 2008 17:24:05 ο/η Richard Broersma έγραψε: > On Tue, Sep 2, 2008 at 2:58 AM, Achilleas Mantzios > <[EMAIL PROTECTED]> wrote: > > is there an (implicit) way to make a multirow update execute on some rows > > prior to other rows? > > It is needed in a case where a trigger is defined on the table as FOR EACH > > ROW, and it is mandatory > > that the trigger is run for some certain rows before it is run on the rest > > of the rows. > > > > Is there anything reliable to achieve this without making poor assumptions > > of the future > > versions, or should i just "SELECT ... ORDER BY ..." and then perform > > individual UPDATEs? > > The only way that I know how to do this is to create a named cursor of > the rows that you want to update, and then for each record call > > UPDATE ... FROM ... WHERE CURRENT OF cursorname; aha Thanks. > > > But why are you even having this problem to begin with? What you are > describing sounds like a database normalization problem. > I am using my version of DB mirror to do some kind of "Conditional row grained + FK dependency oriented lazy replication". (The logic behind it is the cost of comms, because the slaves are servers in vessels in all 7 seas, where communication is done via uucp connections over satellite dilaup, and the costs are really high, so the aim was to minimize the costs. Regarding high costs, It was so in 2003/2004 when we started designing/deploying this system and the prices are still high today.) I have divided my tables into the following categories: 1) never replicated 2) unconditionally replicated to all slaves 3) explicitly conditionally replicated to a *certain* slave and only to this slave, based on the value of one column (smth like "vslid", where vessels denote my slaves) 4) implicitly replicated to slaves, that is they are replicated to some slave *only* if they act as a parent (foreign) table in FK constraint of some child table which is either case 3) or case 4) So what i basically do is a depth first search of the Graph, denoted by the FK constraints. For simplicitly, at some point, in the code i have to find out if some parent table has to be part of the graph search. If that table belongs to case 3), i simply skip this "node", knowing it will be replicated because it is defined as such. The problem arises when a table has a FK to itself, then i have to make sure that some rows will be mirrored before other rows. I could rectify the code to deal correctly with cases like that, but it would add disproportinal complexity in comparison to the problem it solves. Thats why i want to force some rows to be updated before other rows, so that the dbmirror trigger will be called first. > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
