Re: [GENERAL] filter duplicates by priority
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Clark Slater > Sent: Tuesday, July 14, 2009 10:04 AM > Subject: [GENERAL] filter duplicates by priority Maybe I'm missing something, but why not something like (incoming pseudo-SQL): Select part_number, max(priority) From( Select part_number, priority from TableAndCriteria Union all Select part_number, priority from TableAndCriteria Union all Select part_number, priority from TableAndCriteria ) as allTables Group by part_number Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital .now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UNION question
> > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > > ow...@postgresql.org] On Behalf Of Brandon Metcalf > > Sent: Friday, July 10, 2009 12:16 PM > > Change it to this: Sorry, I forgot that you need to split the GROUP BY clause as well in a similar manner to the WHERE clause. And unless you have duplicate rows to eliminate, use UNION ALL rather than UNION for a speed increase. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital .now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UNION question
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Brandon Metcalf > Sent: Friday, July 10, 2009 12:16 PM Change it to this: > SELECT t.name AS machine_type_name, > j.workorder, > round(sum(EXTRACT(epoch FROM(j.clockout- > j.clockin))/3600/w.quantity_made)::numeric,2) > AS avgtime > NULLAS employees > FROM jobclock j > JOIN employee e ON e.employee_id=j.employee_id > JOIN machine m ON m.machine_id=j.machine_id > JOIN machine_type t ON t.machine_type_id=m.machine_type_id > JOIN workorder wON w.workorder=j.workorder > JOIN part p ON p.part_id=w.part_id > WHERE p.part_id=379 > UNION > SELECT t.name AS machine_type_name, > NULLAS workorder, > h.time AS avgtime, > employees > FROM part_time_historical h > JOIN machine_type t ON > t.machine_type_id=h.machine_type_id > WHERE h.part_id=379 AND h.machine_type_id=1 > WHERE t.machine_type_id=1 > GROUP BY t.name,j.workorder > ORDER BY avgtime Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital.now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] singletons per row in table AND locking response
> Dennis Gearon > Sent: Tuesday, July 07, 2009 9:46 PM > > When locking is involved, does a transaction wait for access to a row or > table, or does it just fail back to the calling code? Would it be up to my > PHP code to keep hammeing for access to a row/table, or could a user > defined function do that? I do not know the answer to your question off hand, but be wary of pausing or hammering the database to establish a lock. Consider the implications of what happens when the application or thread with the lock crashes. Say for example that User A establishes a lock on a table and crashes. The lock persists. User B tries for a lock, is denied, and enters a loop of constantly trying. He'll be stuck in limbo until the first lock is cleared. I think the model of denying the lock and perhaps retrying with a set limit on attempts would be a better approach. For what it's worth, in my own PHP/PostgreSQL application I handle locking through the application and database. The database has a "locks" table. The application requests a lock by looking for a non-deleted lock in that table for whatever object (table or a row within a table) it wants. If no such lock exists, one is created and returned to the application. The lock is released at the end of the current task. The advantage is that if anything crashes, there is a page in the application that an administrator can delete any lock from, or see who holds a lock on what from when. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital > > I'd like to have a certain object in my PHP application have essentially > individual SERIAL rows per object created site wide. So only one script > instance at a time in apache can have access to a row to read and > increment a value in a row. > > Example, (totally random idea, example only), any user on site can create > a group. Each group assigns group_user_ids per site member in his group, > starting at zero for each new user joining a group, no matter their > site_id. > > My choices so far seem to be: > IN PHP, Use a system file for locking only one instance of the class > gets access to the table. > IN PHP, Use the transaction failure to hammer the database for one > instance of the class. > IN PHP, Use the transaction failure to hammer the database for each > ROW's instance of a class. > IN POSTGRESQL, use the transaction failure to hammer the database for > each ROW's instance of a class. > > But maybe there's more to the locking than failed transactions for UPDATE, > some kind of sequential queueing of access to tables or rows for > transactions? > > I'm trying to minimize the interfaces, cpu time, etc involved in getting > access to the table. > > > extremely basic SQL for this idea. > > CREATE TABLE group ( > group_id SERIAL NOT NULL, > CONSTRAINT PK_group PRIMARY KEY (group_id) > ); > > CREATE TABLE singletons_for_last_grp_mbr_id_issued ( > group_id INTEGER NOT NULL, > last_grp_mbr_id_issued INTEGER DEFAULT 0 NOT NULL, > CONSTRAINT PK_singletons PRIMARY KEY (counts_per_main, main_id) > ); > > CREATE UNIQUE INDEX IDX_One_Group_Row_Only ON > singletons_for_last_grp_mbr_id_issued (group_id); > > ALTER TABLE singletons_for_last_grp_mbr_id_issued >ADD CONSTRAINT group_singletons_for_last_grp_mbr_id_issued >FOREIGN KEY (group_id) REFERENCES group (group_id) > > Dennis Gearon > > Signature Warning > > EARTH has a Right To Life > > I agree with Bolivian President Evo Morales > > # The right to life: "The right for no ecosystem to be eliminated by the > irresponsible acts of human beings." > > # The right of biosystems to regenerate themselves: "Development cannot be > infinite. There's a limit on everything." > > # The right to a clean life: "The right for Mother Earth to live without > contamination, pollution. Fish and animals and trees have rights." > > # The right to harmony and balance between everyone and everything: "We > are all interdependent." > > > See the movie - 'Inconvenient Truth' > See the movie - 'Syriana' > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combine multiple row values in to one row
Try this. select idn, array_to_string(array(select code from tbl t2 where t2.idn = t1.idn order by code), ', ') as codes fromtbl t1 group byidn order byidn Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Lee Harr > Sent: Monday, July 06, 2009 5:30 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] combine multiple row values in to one row > > > Hi; > > I'm looking for a way to do this: > > > # \d tbl > Table "public.tbl" > Column | Type | Modifiers > +-+--- > idn| integer | > code | text| > # SELECT * FROM tbl; > idn | code > -+-- >1 | A >2 | B >2 | C >3 | A >3 | C >3 | E > (6 rows) > # select idn, magic() as codes FROM tbl; > idn | codes > -+-- >1 | A >2 | B, C >3 | A, C, E > (3 rows) > > > Right now, I use plpgsql functions, but each time I do it > I have to rewrite the function to customize it. > > Is there a generic way to do this? An aggregate maybe? > > > Thanks for any help. > > > _ > Invite your mail contacts to join your friends list with Windows Live > Spaces. It's easy! > http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.a sp > x&mkt=en-us > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general .now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Store derived data or use view?
If it's static (i.e. the planets don't move too much, hah), calculate and store. No sense in re-calculating it each and every time. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of gvimrc > Sent: Friday, July 03, 2009 1:08 PM > To: pgsql > Subject: [GENERAL] Store derived data or use view? > > I have a table which stores the absolute longitude of a planetary > position, eg: > > MERCURY > --- > 157.65 > > SATURN > - > 247.65 > > When 2 planets are a certain distance apart there is an 'aspect', eg. 90 > degrees is a "square" aspect > > I wish to record these aspects for different user profiles and eventually > do searches for users who have the same aspect(s). Would it be better, in > terms of search speed/efficiency, to calculate and store the aspect data, > eg. Mercury/Saturn square, or should I just store the longitude data and > create a view with the calculated aspects? I anticipate a large dataset of > users so search speed/efficiency is very important. > > gvim > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general.now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simulate multiple primary keys
Just create a unique constraint on all of the columns.
Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549- x4294
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Brandon Metcalf
> Sent: Thursday, July 02, 2009 1:28 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] simulate multiple primary keys
>
> I have the following table:
>
> gms=> \d jobclock
> Table "public.jobclock"
> Column| Type |
> Modifiers
>
-++-
> ---
>jobclock_id | integer| not null default
> nextval('jobclock_jobclock_id_seq'::regclass)
>employee_id | integer| not null
>machine_id | character varying(4) | not null
>workorder | character varying(8) | not null
>operation | integer| not null
>bartype | character varying(10) | not null
>clockin | timestamp(0) without time zone | not null
>clockout| timestamp(0) without time zone | default
NULL::timestamp
> without time zone
>comments| character varying(255) | default
NULL::character
> varying
> Indexes:
> "jobclock_pkey" PRIMARY KEY, btree (jobclock_id)
> ...
>
> I need to keep jobclock_id unique and not null, but I also need to
> ensure that no row is duplicated. Is my best bet to drop the current
> primary key and make a primary key out of the columns that I want to
> ensure remain unique from row to row?
>
> Thanks.
>
>
> --
> Brandon
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
.now.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upgrading 8.3 to 8.4 on Windows.
Good morning. I am itching to upgrade my 8.3 development database to 8.4 before I move to production. Pg_migrator is listed as beta so I'd like to avoid that. Has anyone made the leap yet? Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294
Re: [GENERAL] Vacuum on the database versus individual tables.
I do have autovacuum on (as of yesterday). This was discovered when I ran vacuum on a whim. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Thursday, June 25, 2009 12:10 PM To: Hartman, Matthew Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Vacuum on the database versus individual tables. On Thu, Jun 25, 2009 at 10:40 AM, Hartman, Matthew wrote: > Good morning. > > > > On occasion I'll perform a full vacuum on a database but will still receive > a suggestion to vacuum an individual table immediately after. Does the full > database vacuum not handle each individual table? As of 8.3, for the most part you can trust autovacuum to do its thing if it's enabled...there are exceptions to this but I'm curious why you are doing 'vacuum full'. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum on the database versus individual tables.
But it'll do so immediately after I run a full vacuum on the entire database? Nothing has changed. This is a development box. You know, I bet it doesn't refresh the view of the database after having run the maintenance script.. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 -Original Message- From: Dave Page [mailto:dp...@pgadmin.org] Sent: Thursday, June 25, 2009 12:00 PM To: Grzegorz Jaśkiewicz Cc: Hartman, Matthew; pgsql-general@postgresql.org Subject: Re: [GENERAL] Vacuum on the database versus individual tables. 2009/6/25 Grzegorz Jaśkiewicz : > 2009/6/25 Hartman, Matthew : >> Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical >> "Running vacuum on this table is recommended" dialog box. > > Well, it really has nothing to do with postgresql it self. Either it > is a bug or property of PgAdmin, but I don't know. > Either someone who knows more about pgadmin is going to respond here, > or you have to ask on pgadmin list. pgAdmin will advise vacuuming a table if there is a significant discrepancy between the number of rows in the table and the value in pg_class.reltuples. i forget the exact algorithm off-hand, but it takes the size of the table into account, and is looking for a %age difference between the value, not a set number of rows. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum on the database versus individual tables.
Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical "Running vacuum on this table is recommended" dialog box. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 -Original Message- From: Grzegorz Jaśkiewicz [mailto:gryz...@gmail.com] Sent: Thursday, June 25, 2009 11:25 AM To: Hartman, Matthew Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Vacuum on the database versus individual tables. On Thu, Jun 25, 2009 at 3:40 PM, Hartman, Matthew wrote: > On occasion I'll perform a full vacuum on a database but will still receive > a suggestion to vacuum an individual table immediately after. Does the full > database vacuum not handle each individual table? What's the exact message, what OS, what pg version, how do you call vacuum. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Vacuum on the database versus individual tables.
Good morning. On occasion I'll perform a full vacuum on a database but will still receive a suggestion to vacuum an individual table immediately after. Does the full database vacuum not handle each individual table? Thanks, Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294
Re: [GENERAL] Explaining functions.
Thanks! That'll reduce the amount of copy/pasting I have to do to figure
out the differences in times.
Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549- x4294
-Original Message-
From: Chris Spotts [mailto:rfu...@gmail.com]
Sent: Tuesday, June 23, 2009 10:48 AM
To: Hartman, Matthew; 'Merlin Moncure'
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Explaining functions.
> > is around 250 lines.
>
> What I normally do for benchmarking of complex functions is to
> sprinkle the source with "raise notice '%', timeofday();" to figure
> out where the bottlenecks are. Following that, I micro-optimize
> problem queries or expressions outside of the function body in psql.
>
[Spotts, Christopher]
I use this set of functions towards this end, sprinkled about...
I'm sure there are better ways to write it,but it works.
CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS
$$
if ($_SHARED{$_[0]} = $_[1]) {
return 'ok';
} else {
return "cannot set shared variable $_[0] to $_[1]";
}
$$ LANGUAGE plperl;
CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
return $_SHARED{$_[0]};
$$ LANGUAGE plperl;
CREATE OR REPLACE FUNCTION time_between_calls() RETURNS interval AS $$
DECLARE
ot text;
BEGIN
ot := get_var('calltime');
PERFORM set_var('calltime',timeofday());
RETURN timeofday():: timestamp - ot :: timestamp;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION start_time_between_calls() RETURNS void AS $$
BEGIN
PERFORM set_var('calltime',timeofday());
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION test() RETURNS void AS $$
BEGIN
PERFORM start_time_between_calls();
raise notice '%',time_between_calls();
PERFORM pg_sleep(3);
raise notice '%',time_between_calls();
END
$$ LANGUAGE plpgsql;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Explaining functions.
Hy the raise notice is a good idea, thanks. I use raise notice already for other uses, may as well go with it. Thanks. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Tuesday, June 23, 2009 9:20 AM To: Hartman, Matthew Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Explaining functions. On Tue, Jun 23, 2009 at 8:03 AM, Hartman, Matthew wrote: > Is there a recommended approach when trying to use EXPLAIN on a > function? Specifically, a function that is more than the typical SELECT > statement or tiny loop. The one in question that I'm hoping to optimize > is around 250 lines. What I normally do for benchmarking of complex functions is to sprinkle the source with "raise notice '%', timeofday();" to figure out where the bottlenecks are. Following that, I micro-optimize problem queries or expressions outside of the function body in psql. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Explaining functions.
Is there a recommended approach when trying to use EXPLAIN on a function? Specifically, a function that is more than the typical SELECT statement or tiny loop. The one in question that I'm hoping to optimize is around 250 lines. Thanks, Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dynamic table
Isn't a many-to-one relationship the classic example of a child table? Have one parent table that assigns a primary key. "PARENT" with "PARENT_ID". Have a child table that has a name and value column. "CHILD" with "PARENT_ID", "COLUMN_NAME", and "COLUMN_VALUE". Perform joins as you see fit to build up the structure at times. Use arrays if you'd like or perform multiple joins, which ever. Matthew Hartman Programmer/Analyst Information Management Kingston General Hospital, ICP (613) 544-2631 x4294 From: pgsql-general-ow...@postgresql.org on behalf of Jasen Betts Sent: Sat 20-Jun-09 12:14 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Dynamic table On 2009-06-17, A B wrote: >> Your problem is currently sounding very much like an exam question; you >> seem to be arbitrarily making decisions without showing any real data. >> When you deal with real problems in the real world you're normally >> making compromises when you model things and hence the decisions >> wouldn't be as forced as you're making it. Design is about picking and >> choosing between compromises and without knowing what the choices are >> you can't design anything, this is one of the problems with tests. > > I wish it was just an exam question > > Show real data? > Well here it is: > > customer | value1 | value2 | value3| > 1 5 3 4 > 2 8 2 10 > > I hope you can believe me when I say that the names > value1,value2,value3 really are impossible to relate. I will not > decide upon those. Someone else is going to do that in the future. I > just have to make a structure that can handle all cases from "shoe > size" to "number of atoms in persons body" (well, perhaps I can say > that the values will be in the 0...100 range, but that's about it. > There is really nothing else I know about how the system will be used > by other people. :-( I just know that I have to make them enter data > like this since it is a third party that need this kind of data. > >yes I took some random values for the "real data" since I > don't know anything else about the data. I like the table-per-column approach And also the array approach, arrays of numbers of the sizes you discuss are much faster than hard disks what sorts of querys will be most common -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
