Re: [GENERAL] [OT] "advanced" database design (long)
I"m not a database expert, but wouldn't create table attribute ( attribute_id int attribute text ) create table value ( value_id int value text ) create table attribute_value ( entity_id int attribute_id int value_id int ) give you a lot less pages to load than building a table with say 90 columns in it that are all null, which would result in better rather than worse performance? Alex On Feb 2, 2008 9:15 AM, Lewis Cunningham <[EMAIL PROTECTED]> wrote: > > --- vladimir konrad <[EMAIL PROTECTED]> wrote: > > > I think that I understand basic relational theory but then I had an > > idea. > > Basically, instead of adding field to a table every time there is a > > need for it, have a table split in two: one holds identity (id) and > > one holds the attributes (linked to this id). > > Basically, if in the future user decides that the subject should > > have a new attribute, he can simply add "attribute definition" and > > attribute_definition_set (if any) and the application would handle > > Basically, you would be creating your own data dictionary (i.e. > system catalog) on top of the db data dictionary. The database > already comes with a way to easily add columns: ddl. I have seen > newbie database designers reinvent this method a hundred times. The > performance hits and complexity of querying data would far out weigh > any perceived maintenance gain. > > My .02. > > LewisC > > > > > Lewis R Cunningham > > An Expert's Guide to Oracle Technology > http://blogs.ittoolbox.com/oracle/guide/ > > LewisC's Random Thoughts > http://lewiscsrandomthoughts.blogspot.com/ > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
On Sun, 03 Feb 2008 11:28:24 -0800 Ron Mayer <[EMAIL PROTECTED]> wrote: > Josh Berkus wrote: > >> Id really prefer my company be certified by the community rather > >> than by a company, despite the full respect I have in SRA's > >> engagement in PostgreSQL and that we all know their contributions. > > What would it mean for a company to be certified? > I'd hope it'd mean that I can have some degree of confidence > hiring that organization for Postgresql support. No? > > It seems to have very similar benefits as certifying individuals. > > Microsoft seems to have something like that for their > partners in their "Database Management competency" > https://partner.microsoft.com/global/40012911 > Guys, with respect this thread does nothing for us unless it is on the certification list. Sincerely, Joshua D. Drake > > > ---(end of > broadcast)--- TIP 9: In versions below 8.0, > the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > signature.asc Description: PGP signature
Re: [GENERAL] [pgsql-advocacy] PostgreSQL professionals group at LinkedIn.com
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Alvaro Herrera asked: > Is there an existing Postgres group? Yes, this one: http://www.linkedin.com/e/gis/41621/0F3C7A53CCD6 - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200802032251 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHpovLvJuQZxSWSsgRA3GGAJ9wAWha41Qx78Ut74BvJyWfR7WX4ACgsYxo C/ChaabuhKDtwTqsUnNP1mA= =wVjX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL/PHP Application Server
Frameworks are over-rated. PHP makes most common tasks simple (not that I'm really a big PHP fan, but it works pretty well most of the time). Just follow a few basic XSS protection rules, and you will have few problems. Filter input elements for HTML, don't put stupid things in cookies that can be sniffed or forged, same for URL parameters, don't make database queries by concatenating strings made up from $_GET or $_POST values, use prepared statements instead, write a basic authentication framework that is mostly just calling auth($expected_role), and going to a auth failed page if it returns false (if thats appropriate, or just set a flag so the rest of the page knows this is not a logged in user). I've found that I seem to spend as much time programming for the exceptions to the frameworks inbuilt mechanisms as I would have if I just built it myself as a basic page in raw PHP/SQL, not only that, but abstraction layers just add overhead which makes you scale less, and cost more $$s. I've seen a J2EE app that fell over with just 32 users (and it wasn't anything to do with data mining) because the entity bean layer was thrashing the crap out of the database server. Do yourself a favour - put sessions in the DB not on the file system, and let the DB do what it's good at - being fast at data access (now if I could just figure out how to make certain table stay in RAM...) (for goodness sake, database professionals have spent 30 years making sure that databases are fast, can any framework programmer claim the same thing?) As for security - Monolithic security is obvious because you can't forge a new database connection for every user that connects to your system - it doesn't scale, you have to use connection pooling, which means one master-user profile, and web users get authenticated at the application level, not the DB level. PHP supports LDAP just fine, you can auth against LDAP no problem (even Windows AD though the schema is scary), but ultimately roles for your application will be in the DB with all your other data. You can lazy initialize users in the DB when you auth them for the first time from the LDAP server. I really don't know what row-level security even means, but most security has to function not just on what data a user has access to, but rather what function a user has access to combined with the ownership of the data, which is mostly application level. Many functions access the same data, but in different ways, trying to enforce authorization by data is wrong-headed in my mind. PHP/Apache is your shared foundation, that IS the app server. Your 'modules' are just directories with files (not that different from tomcat or JBoss really). Use version control (preferably one that has good branching support like git), and a makefile to copy/FTP your files to your 'live' directory from your source directory so that multiple people can do deployments, and you can deploy to one of multiple environments easily (think personal development server, nightly build environment, staging environment and live environment) by simply changing an environment variable. The only thing I've seen that's worth adding to this mix is content management, and I've yet to see someone do that well in the FOSS community. Drupal is a mess (and is basically MySQL), Joomla isn't that great and is also MySQL, and Plone is over complicated with lousy documentation, and is sadly based on Zope, which uses ZopeDB, which is also really poorly documented (as I write this, looking for an example I had in mind, http://wiki.zope.org/zope3/ is actually down right now). If you want to do the world a favour, write a CMS that doesn't suck, preferably in Java, but PHP would do in a pinch. If you keep it simple, you will be amazed at how many requests a single web-server can sustain if you just configure it with the right hardware (and no I don't mean 128CPUs and 1TB of RAM and 40HDs) and sit back and watch it fly. If you need it, make a hot spare. If your app can do 50 requests/second (which isn't that unreasonable with simple PHP/PostgreSQL), that's 4.3 million requests per day, which is a shit ton of traffic. Chances are your network will max out before you hit the server's CPU/IO throughput ceiling. Alex P.S. Don't even think about working with PHP and not running into a serious bug somewhere along the way that's been ignored by the devs, and lets not even start about interface inconsistencies that make remembering the APIs a nightmare. At least the website has good docs, which is a big plus for PHP against other 'frameworks' P.P.S. Do the world another favour and run away from mod_perl screaming in terror if you ever want to debug your application or hire someone else who can understand it. On Jan 24, 2008 12:15 PM, Brian A. Seklecki <[EMAIL PROTECTED]> wrote: > All: > > Are there any frameworks / toolkits available, possibly as established > F/OSS projects, for web applications using PHP+PostgreSQL? > > sf.net/goo
[GENERAL] Reverse key index
Hi All,
I have wanted to create a reverse key index for some time now, and it
seems that an evening of reading and half a day of efforts finally paid off.
This is just a proof of concept, and sure, the bit-reversing technique can
use a native language's power for better results.
I started with the following posts:
http://archives.postgresql.org/pgsql-hackers/2002-01/msg01201.php
http://archives.postgresql.org/pgsql-hackers/2002-01/msg01225.php
The operator class that is created at the end uses one function to
populate the index in almost a random manner (reverse binary
representation). And this op-class provides just one operator to compare the
values, as opposed to Tom's suggestion ("all the other members would be
byte-reversed-comparison operators"); this is because if we allow the index
to use any of these other operators (custom or the built-in ones) for range
scans, the range's starting value will be found for sure, but because the
btree index follows the leaf nodes from there on, the results will be
totally what we never asked for!
The result at the end, INDEX del_i, is an index that helps disperse
heavy sequential INSERTs from different sessions over to different index
blocks, reducing index block contention hence improving performance. Also,
this index can be used of equality operator (but no other operator).
Hackers, of course, comments please. Let me know if I have missed
something, and if this is not exactly what a user would want!
For fun: If you wish to see how a BTree index performs the comparisons
and populates the index, just uncomment the 'raise notice' statement in
rev_int_cmp(). And to compare the bit-reversed mode to the normal mode of
index population, just replace the contents of declare section with 'rev_a
int = a; rev_b int = b;' in the declare section. :) have fun.
I have uploaded my original, unedited file from the efforts here. It
goes to lengths to create functions and operators and what not; may be
helpful for other noobs chasing operators.
http://www.geocities.com/gurjeet79/reverse_key_index.sql.txt
Best regards,
PS: I think my signature should be:
'Do I LOVE Postgres or what!!'
OR 'I am in LOVE with Postgres'
OR 'Postgres is _is_ *is* BEAutiful!'
OR
--- CODE ---
--- Support
create or replace function public.reverse_string( str varchar )
returns varchar
strict
immutable
language plpgsql
as $$
declare reversed varchar = '';
begin
for i in reverse char_length( str ) .. 1 loop
reversed = reversed || substring( str from i for 1 );
end loop;
return reversed;
end;
$$;
create or replace function public.rev_int_cmp( a int, b int )
returns int
strict
immutable
language plpgsql
as $$
declare
rev_a int = reverse_string( a::bit(32)::varchar )::bit(32)::int;
rev_b int = reverse_string( b::bit(32)::varchar )::bit(32)::int;
begin
-- raise notice 'rev_int_cmp( %, % ) called', a, b;
if( rev_a < rev_b ) then
return -1;
elsif( rev_a > rev_b ) then
return +1;
else
return 0;
end if;
end;
$$;
--- Operator class
drop operator class if exists public.rev_int_ops using btree cascade;
create operator class public.rev_int_ops for type int using btree as
operator 3 pg_catalog.=,
function 1 public.rev_int_cmp( int, int );
--- example
drop table if exists del;
create table del( a int, b char(128) );
create index del_i on del( a rev_int_ops );
insert into del select s, s+1 from generate_series( 1, 1000 ) as s; -- rev
vacuum full analyze del;
explain
select * from del;
explain
select * from del order by a;
explain
select * from del where a = 2; -- should use the reverse index
explain
select * from del where a < 200; -- should NOT use the reverse index
truncate del;
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
Re: [GENERAL] Function returning subset of columns from table (return type)
On Sun, 03 Feb 2008 18:23:47 -0500 brian <[EMAIL PROTECTED]> wrote: > Myk wrote: > > Hi > > > > I'm pretty new to PostgreSQL, and have encountered a bit of trouble > > with functions, namely the return type. Version is 8.0.15. > > > > I have the following table: > > > > note ( id int, added date, updated date, text varchar(1000) ) > > > > and want to define a function that just returns the dates and text by > > id. I initially just did: > > > > create function note_get (id int) returns setof note as 'select * > > from note where id=$1' language sql; > > > > which was fine. Then later I thought I'd try formatting the columns > > (they're only intended for display): > > > > create function note_get ( id int ) returns setof record as ' select > > to_char (added, ''Mon D ''), to_char (updated, ''Mon D ''), > > text from note where id=$1 ' language sql; > > > > but this gives me ERROR: a column definition list is required for > > functions returning "record" > > > > You could create a rowtype for this: > > CREATE TYPE your_type > AS ( >added CHAR(11) NOT NULL, >updated CHAR(11) NOT NULL, >text_col TEXT > ); After my refreshing walk, I created a view that did the pretty printing, and then just used that: create function note_get(id int) returns setof as ' select * from where note_id=$1' language sql; although I then get a redundant note_id, and it may affect performance as the view gets all the 'pretty print' results, which is then filtered afterwards, I'm not sure... (explain analyze is my friend!) Defining a type as you suggest seems like the proper way - but it's a bit high-maintenance, especially for big queries with lots of joins... Anyway, thanks for your help. - Mike ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
On Thu, 31 Jan 2008, [EMAIL PROTECTED] wrote: I haven't done any tuning as of yet. I'm running with the default settings produced by initdb. The default settings are junk and the disk pattern will change once they're set correctly, so tuning ZFS first and then PostgreSQL is probably backwards. You may return to tuning the database again after ZFS, but for the first shot I'd start with a somewhat tuned DB server and then play with the filesystem. Put the major postgresql.conf parameters in the right ballpark--shared_buffers, effective_cache_size, and a large setting for checkpoint_segments since I think you mentioned a write-heavy benchmark. You should do your own experiments with wal_sync_method, I haven't seen any tests that are really definitive on the best setting there for S10+ZFS and it kind of depends on the underlying hardware--try both open_datasync and fdatasync. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Function returning subset of columns from table (return type)
Gurjeet Singh wrote: All's okay, except you should not have declared it IMMUTABLE, because the results depend on a database query. From the docs: IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list Yes, my bad. Make that STABLE. b ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql book - practical or something newer?
On Jan 31, 2008 4:40 PM, Guy Rouillier <[EMAIL PROTECTED]> wrote: > Robert Treat wrote: > > > Just so you know, I approached OReally about writing a PostgreSQL Cookbook, > > and they turned it down. They did offer me some other titles, but those > > don't > > seem to have gone anywhere. > > As someone else pointed out in this thread, very much of what you need > to know has been previously discussed at one point; the hard part is > finding it. > > What we need is for some of the people with the big brains ;) to come up > with some new kind of "hyperbook". That would be the documentation in > some form similar to what it is today, but somehow connected to the > discussions that happen in the mailing lists. That way, when something > really insightful or helpful gets said in the mailing lists, it can get > connected to a particular place in the documentation. Then over time, > the doc maintainers can take the best of those and incorporate them > directly into the docs at the appropriate place. The trouble is that this is nearly as much trouble as actually writing a book, and doesn't provide a clear incentive for people to put in the effort of making it happen. There's the problem (and it is, to a degree, truly a problem) that the "postgreSQL book" market hasn't been lucrative enough to draw people into writing books. And honestly, it *needs* to be more lucrative. If I'm thinking about alternative uses for my spare time, writing does not appear to be a particularly profitable use. Finding a "poor man's way" to generate a "hyperbook" actually needs much the same sorts of skills and efforts, even though it probably provides those that provide the effort with *less* benefits. -- http://linuxfinances.info/info/linuxdistributions.html "The definition of insanity is doing the same thing over and over and expecting different results." -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Function returning subset of columns from table (return type)
On Feb 3, 2008 3:23 PM, brian <[EMAIL PROTECTED]> wrote: > CREATE TYPE your_type > AS ( > added CHAR(11) NOT NULL, > updated CHAR(11) NOT NULL, > text_col TEXT > ); > > CREATE FUNCTION get_note(id INT) > RETURNS SETOF your_type IMMUTABLE > AS $$ > > DECLARE > your_row your_type%rowtype; > > BEGIN > SELECT INTO your_row > to_char(added, 'Mon D '), > to_char(updated, 'Mon D '), > text_col > FROM note > WHERE id = $1; > > RETURN your_row; > END; > $$ LANGUAGE sql; > > All's okay, except you should not have declared it IMMUTABLE, because the results depend on a database query. >From the docs: IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] Function returning subset of columns from table (return type)
Myk wrote: Hi I'm pretty new to PostgreSQL, and have encountered a bit of trouble with functions, namely the return type. Version is 8.0.15. I have the following table: note ( id int, added date, updated date, text varchar(1000) ) and want to define a function that just returns the dates and text by id. I initially just did: create function note_get (id int) returns setof note as 'select * from note where id=$1' language sql; which was fine. Then later I thought I'd try formatting the columns (they're only intended for display): create function note_get ( id int ) returns setof record as ' select to_char (added, ''Mon D ''), to_char (updated, ''Mon D ''), text from note where id=$1 ' language sql; but this gives me ERROR: a column definition list is required for functions returning "record" You could create a rowtype for this: CREATE TYPE your_type AS ( added CHAR(11) NOT NULL, updated CHAR(11) NOT NULL, text_col TEXT ); CREATE FUNCTION get_note(id INT) RETURNS SETOF your_type IMMUTABLE AS $$ DECLARE your_row your_type%rowtype; BEGIN SELECT INTO your_row to_char(added, 'Mon D '), to_char(updated, 'Mon D '), text_col FROM note WHERE id = $1; RETURN your_row; END; $$ LANGUAGE sql; b ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: R: [GENERAL] how to add array of objects to a record
On Sun, Feb 03, 2008 at 10:00:04PM +0100, dfx wrote: > Yes, this is the "normal" way, but I was tempted to investigate the > possibility to use array (of string) or composite types to avoid to > increase the number of tables That is an extremely bad thing to "optimize" for. Add tables as needed for your data. > and to simplify stored procedures reducing the number of join. That's a bad thing to "optimize" for, too. Just do your JOINs, and *if* you discover a performance problem, come back here and get help on it. > Thi idea was born following the discussion concerning EAV. EAV is just a mistake. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [OT] "advanced" database design (long)
vladimir konrad wrote: Worst case would be another column flagging the test type. Why do you think this is the worst case? Bad choice of words - just referring to using the where clause to extract one particular test - if that is insufficient you can use a test column to track what test it refers to. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 1: 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
R: [GENERAL] how to add array of objects to a record
Yes, this is the "normal" way, but I was tempted to investigate the possibility to use array (of string) or composite types to avoid to increase the number of tables and to simplify stored procedures reducing the number of join. Thi idea was born following the discussion concerning EAV. Thank you for contribute. Domenico -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] conto di Joris Dobbelsteen Inviato: domenica 3 febbraio 2008 12:16 A: [EMAIL PROTECTED]; pgsql-general@postgresql.org Oggetto: Re: [GENERAL] how to add array of objects to a record >-Original Message- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of dfx >Sent: Sunday, 3 February 2008 10:38 >To: pgsql-general@postgresql.org >Subject: [GENERAL] how to add array of objects to a record > >Hi list, > >Can I add an array of object to a record? > >For example if I have a class (or type) phone_number: > >create type phone_number as( >name char(20), >caption char(50), >ph_num char(25)); > >and I would like associate several (unknown number, a priori) >phone numbers to a record "persons" >can I create a table like this: > >create table persons( >id integer, >first_name char(50), >family_name char(50), >phone_numbers phone_number[]) > >and how I have to write the insert and the select queries to >put and get the array of phone numbers? Its an SQL database, do not fall back to paradigms from imperative programming languages (like C, Java, PHP, Perl, ...). Postgres is an implementation of the relational model, which works very nice on sets. It does not have a good concept of pointers and alike (they can be represented, but inefficiently). Transform you type into a table and add an "id" attribute that references the persons table (using a foreign key). At this point you can use regular SQL statements. - Joris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Function returning subset of columns from table (return type)
Hi I'm pretty new to PostgreSQL, and have encountered a bit of trouble with functions, namely the return type. Version is 8.0.15. I have the following table: note ( id int, added date, updated date, text varchar(1000) ) and want to define a function that just returns the dates and text by id. I initially just did: create function note_get (id int) returns setof note as 'select * from note where id=$1' language sql; which was fine. Then later I thought I'd try formatting the columns (they're only intended for display): create function note_get ( id int ) returns setof record as ' select to_char (added, ''Mon D ''), to_char (updated, ''Mon D ''), text from note where id=$1 ' language sql; but this gives me ERROR: a column definition list is required for functions returning "record" Further reading led me to: create function note_get ( id int, out added varchar(12), out updated varchar(12), out text varchar(1000) ) returns setof record ... which got me ERROR: CREATE FUNCTION / OUT parameters are not implemented at which point I thought it best to go out for a walk :) How do I create a function that returns a number of columns like this? Thanks -- Mike ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Josh Berkus wrote: Id really prefer my company be certified by the community rather than by a company, despite the full respect I have in SRA's engagement in PostgreSQL and that we all know their contributions. What would it mean for a company to be certified? I'd hope it'd mean that I can have some degree of confidence hiring that organization for Postgresql support. No? It seems to have very similar benefits as certifying individuals. Microsoft seems to have something like that for their partners in their "Database Management competency" https://partner.microsoft.com/global/40012911 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
JPA, Id really prefer my company be certified by the community rather than by a company, despite the full respect I have in SRA's engagement in PostgreSQL and that we all know their contributions. What would it mean for a company to be certified? --Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] temp sequence
On 03/02/2008 06:39, Sim Zacks wrote: I call the function once it works, when I call it a second time, it gives me an error that the sequence already exists. When I restart the application, I can call the function again. Are you by any chance connecting via ODBC with connection pooling? If so, then the pooled connections remain open, which - as someone else has explained - causes sessions to be maintained. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [OT] "advanced" database design (long)
Hello, and thanks > Are the tests that different that you need to segregate the data? > I see them both as being the time taken to travel a distance. The > only difference is whether the time or distance is used to end the > measurement. Good point (I have realised this after posting, when I dug deeper into the design). What I got now looks like this (linking fields omitted): [subject] 1 | n [ergo: distance(float), time(interval), taken_on(date)] n n | | 1 1 [ergo_rate] [ergo_set] The same test can be taken at different rates (e.g. 20 strokes per minute), therefore the [ergo_rate] (there are only few rates they always use). The [ergo_set] determines what value was "set" before the test (bad name but maybe i think of something better). So, it the distance is set, it points to (1, 'distance') in [ergo_set]; if time, then to (2, 'time'). User chooses what is "set" before recording the test. Also it is possible to ask "give me all 2000m ergo test results done at this rate". > Worst case would be another column flagging the test type. Why do you think this is the worst case? > I also see multiple samples for a single run. Time at 1km, 2km, 3km, > 4km, 5km (or at 2min, 4 min, 6min...) - you could see whether they > can maintain the speed over the distance or at what distance/time > they wear out and slow down. (maybe they give too much in the first > 2km so that they just crawl in the last 2) > Maybe sub-times can be a second table. They call it splits (have to check if it is done for ergos but it is definitely done for racing on the water). In ergo case, I would have extra table [ergo_split]: [ergo] <-- [ergo_split: distance(float), clock(time)] (they record the time a watch shows them, therefore time and not interval) Vlad ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [OT] "advanced" database design (long)
vladimir konrad wrote: The system I am developing has to handle "tests" (for rowing athletes): 1. how many meters athlete did in 10 minutes (result is float) 2. how long it took the athlete to do 5 kilo-meters (the result is time) So it looks that I need table for each of 1 and 2 (because of different data types describing the test). Are the tests that different that you need to segregate the data? I see them both as being the time taken to travel a distance. The only difference is whether the time or distance is used to end the measurement. Personally I would think that one table that has the athlete's id and a date (or timestamp to allow more than one a day) of the event as well as a time interval and distance would suffice. For 1. the time interval would always be 10mins, for 2. the distance would always be 5km. To get individual test stats you can use WHERE time=10mins or WHERE distance=5.0 You could even create test_views with the where clause pre-determined. Worst case would be another column flagging the test type. From there you can also add in a 5, 15, 20, 30, 40 minutes or even 2, 2.5, 7.5, 10 km tests as well without changing your structure. I also see multiple samples for a single run. Time at 1km, 2km, 3km, 4km, 5km (or at 2min, 4 min, 6min...) - you could see whether they can maintain the speed over the distance or at what distance/time they wear out and slow down. (maybe they give too much in the first 2km so that they just crawl in the last 2) Maybe sub-times can be a second table. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] arrays of floating point numbers / linear algebra operations into the DB
I respond myself: Enrico Sirola ha scritto: [...] seems to work). The problem for the code above is that it doesn't work for vectors longer than 1000 elements or so (try it with 2000 and it doesn't work). I guess I should manage the "toasting" machinery in some ways - any suggestion is appreciated wrong. it was just that I forgot to add ARR_OVERHEAD_NONULLS(ndims1) to the mem allocation for rv: rv = (ArrayType *) palloc(nbytes); becomes rv = (ArrayType *) palloc(nbytes) + ARR_OVERHEAD_NONULLS(ndims1); and now it seems to work :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] temp sequence
On Saturday 02 February 2008 10:39 pm, Sim Zacks wrote: > "PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC > i386-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1)" > > I am creating a temporary sequence in a function and it seems like it is > not going away after the function finishes. > The front end is in MS Access 2000 and I have a single connection. When I > call the function once it works, when I call it a second time, it gives me > an error that the sequence already exists. When I restart the application, > I can call the function again. > > I solved the problem by using: alter sequence seq_linenum restart with 1; > > The manual states: > If specified, the sequence object is created only for this session, and is > automatically dropped on session exit. Existing permanent sequences with > the same name are not visible (in this session) while the temporary > sequence exists, unless they are referenced with schema-qualified names. > > I thought that a function would be considered its own session, is that > incorrect? The connection is the session. The function is the transaction. If you are going to maintain the connection you will need to drop the sequence inside the function after using it. > > Thank you > Sim > > ---(end of broadcast)--- > TIP 1: 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 -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Argentina presente ;-) Regards, gb.- On Feb 3, 2008 6:49 AM, Jean-Paul Argudo <[EMAIL PROTECTED]> wrote: > Hi all, > > First of all, thanks to Josuah to start this usefull and long time > waited project :-) > > Oleg Bartunov wrote : > > Can you show us the goals of the PostgreSQL Certification ? > > To me, there are two things Id like to be "PostgreSQL Certified": > > - individuals > - companies > > Id really prefer my company be certified by the community rather than by > a company, despite the full respect I have in SRA's engagement in > PostgreSQL and that we all know their contributions. > > > I always voted for the united PostgreSQL Certification program (amin, > > developer) we could promote with the help of commercial companies. > > Count on us (Dalibo) and us (PostgreSQLFr non-profit). > > > In my opinion, common certificate, valid in all countries will be much more > > useful than buttons. > > Definitely. We discussed the topic at Prato. We were talking there about > it could be a project inside PostgreSQL-Europe. > > I'd be more than happy if this could be a Worldwide project instead. > > > We have several good authors who can be sponsored > > to write certification courses with the help of developers. > > Yes, I think so. Dalibo could contribute too, on its own. I know some of > the non-profit that can contribute too. > > > On Wed, 30 Jan 2008, Joshua D. Drake wrote: > > Hey guys, > > > > Myself and a small team of PostgreSQL contributors have started a new > > community project for PostgreSQL Certification. It is just launching > > but we wanted to get it out there so that people can join in on the > > discussion now :). > > > > For more information please visit: > > http://www.postgresqlcertification.org/ > > Joshua D. Drake > > Thanks for such a good initiative, Josuah: > > «Your subscription request has been received..»: let's talk about this > in the mailing-list :) > > Cheers, > > -- > Jean-Paul Argudo > www.PostgreSQLFr.org > www.Dalibo.com > > > > ---(end of broadcast)--- > > TIP 1: 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 > -- Guido Barosio --- http://www.globant.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] arrays of floating point numbers / linear algebra operations into the DB
Hi Webb,
Webb Sprague ha scritto:
I'm quite proud, this is my first C extension function ;-)
I'd gladly post the code if it's ok for the list users. It's more or
less 100 lines of code. This approach seems promising...
I would definitely like to see it.
here it goes:
-->linalg.h<--
#ifndef linalg_h
#define linalg_h
#include "postgres.h"
#include "utils/array.h"
Datum scale(PG_FUNCTION_ARGS);
#endif /* linalg_h */
-->linalg.c<--
#include "linalg.h"
#include "fmgr.h"
#include
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1( scale);
Datum
scale(PG_FUNCTION_ARGS)
{
float8x = PG_GETARG_FLOAT8(0);
ArrayType *v1 = PG_GETARG_ARRAYTYPE_P(1);
int *dims1,
*lbs1,
ndims1,
nitems1,
ndatabytes1;
int *arrlbound1, *arrlbound2;
char *arrdatap1, *arrdatap2;
ArrayType *rv;
/* get argument array details */
lbs1 = ARR_LBOUND(v1);
dims1 = ARR_DIMS(v1);
ndims1 = v1->ndim;
nitems1 = ArrayGetNItems(ndims1, dims1);
ndatabytes1 = ARR_SIZE(v1) - ARR_DATA_OFFSET(v1);
if ( ndims1 != 1 )
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("Multi dimensional array given"),
errdetail("Array have %d dimensions", ndims1)));
if (ARR_HASNULL(v1))
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("Null in array"),
errdetail("array should not contain null elements")));
/* allcate new vector */
rv = (ArrayType *) palloc(ndatabytes1);
SET_VARSIZE(rv, ndatabytes1);
rv->ndim = v1->ndim;
rv->dataoffset = v1->dataoffset; // no nulls (0)
rv->elemtype = v1->elemtype;
memcpy(ARR_DIMS(rv), ARR_DIMS(v1), sizeof(int));
arrlbound2 = ARR_LBOUND(rv);
arrlbound1 = ARR_LBOUND(v1);
memcpy(arrlbound2, arrlbound1, sizeof(int));
arrdatap1 = ARR_DATA_PTR(v1);
arrdatap2 = ARR_DATA_PTR(rv);
memcpy(arrdatap2, arrdatap1, nitems1 * sizeof(float8));
/* scale vector a la blas */
cblas_dscal(nitems1, x, (float8*) arrdatap2, 1);
PG_RETURN_ARRAYTYPE_P(rv);
}
--->linalg.sql<-
/* -*-sql-*- */
create or replace function scale(float8, float8[])
returns float8[]
as '$libdir/linalg', 'scale'
language 'C' immutable strict;
create aggregate array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
create operator * (leftarg=float8, rightarg=float8[], procedure=scale);
>end<
GSL licensing is GNU ish, so may be that is a deal breaker, too.
well, I don't know. This is just a proof of concept. Anyway, yes, there
could be problems with GPL.
On the above code: coupling cblas functions with PG float8[] seems easy,
you just have to know which black-magic-macros to use in order to access
the data structure. It took me a while to figure out how it works (I'm
not actually sure I understood everything, but at least the above code
seems to work). The problem for the code above is that it doesn't work
for vectors longer than 1000 elements or so (try it with 2000 and it
doesn't work). I guess I should manage the "toasting" machinery in some
ways - any suggestion is appreciated
Bye,
e.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] [OT] "advanced" database design (long)
Hello, >> vladimir konrad wrote: >>> I think that I understand basic relational theory but > Clearly, you'll have to revisit that thought. Usually I have one table per "entity" modelled (and the table holds fields describing that entity). E.g. subject would have name fields and date of birth field (fields related directly to subject), postal_address would be separate table and subject_postal_address would be linking table between postal_address and subject: subject <-- subject_postal_address --> postal_address This way, the postal_address can be made unique (with constrains) and linked to other entities where the postal_address is needed. The system I am developing has to handle "tests" (for rowing athletes): 1. how many meters athlete did in 10 minutes (result is float) 2. how long it took the athlete to do 5 kilo-meters (the result is time) So it looks that I need table for each of 1 and 2 (because of different data types describing the test). > The math beneath this is that query complexity goes up like O(E!A!V!) > for Entity, Attribute and Value. Well, when I thought about it, the gut feeling was that I am opening a can of worms - it would push the complexity into code (the bad place to have it in). > The first price, though, and by far the biggest, is that it's > impossible to maintain any kind of data integrity in such a system, as > such constraints, by their nature, are application-dependent. Two > applications means you're violating the SPOT (Single Point of Truth) > Rule, and that in turn means your data turns quickly into > incomprehensible gibberish. It could be implemented inside of the database server (stored procedures, views and such), but it would still be complex, hard, long and as you said badly performing (your point about complexity made that clear). Vlad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to add array of objects to a record
>-Original Message- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of dfx >Sent: Sunday, 3 February 2008 10:38 >To: pgsql-general@postgresql.org >Subject: [GENERAL] how to add array of objects to a record > >Hi list, > >Can I add an array of object to a record? > >For example if I have a class (or type) phone_number: > >create type phone_number as( >name char(20), >caption char(50), >ph_num char(25)); > >and I would like associate several (unknown number, a priori) >phone numbers to a record "persons" >can I create a table like this: > >create table persons( >id integer, >first_name char(50), >family_name char(50), >phone_numbers phone_number[]) > >and how I have to write the insert and the select queries to >put and get the array of phone numbers? Its an SQL database, do not fall back to paradigms from imperative programming languages (like C, Java, PHP, Perl, ...). Postgres is an implementation of the relational model, which works very nice on sets. It does not have a good concept of pointers and alike (they can be represented, but inefficiently). Transform you type into a table and add an "id" attribute that references the persons table (using a foreign key). At this point you can use regular SQL statements. - Joris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] how to add array of objects to a record
Hi list, Can I add an array of object to a record? For example if I have a class (or type) phone_number: create type phone_number as( name char(20), caption char(50), ph_num char(25)); and I would like associate several (unknown number, a priori) phone numbers to a record "persons" can I create a table like this: create table persons( id integer, first_name char(50), family_name char(50), phone_numbers phone_number[]) and how I have to write the insert and the select queries to put and get the array of phone numbers? thank you Domenico ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Hi all, First of all, thanks to Josuah to start this usefull and long time waited project :-) Oleg Bartunov wrote : > Can you show us the goals of the PostgreSQL Certification ? To me, there are two things Id like to be "PostgreSQL Certified": - individuals - companies Id really prefer my company be certified by the community rather than by a company, despite the full respect I have in SRA's engagement in PostgreSQL and that we all know their contributions. > I always voted for the united PostgreSQL Certification program (amin, > developer) we could promote with the help of commercial companies. Count on us (Dalibo) and us (PostgreSQLFr non-profit). > In my opinion, common certificate, valid in all countries will be much more > useful than buttons. Definitely. We discussed the topic at Prato. We were talking there about it could be a project inside PostgreSQL-Europe. I'd be more than happy if this could be a Worldwide project instead. > We have several good authors who can be sponsored > to write certification courses with the help of developers. Yes, I think so. Dalibo could contribute too, on its own. I know some of the non-profit that can contribute too. > On Wed, 30 Jan 2008, Joshua D. Drake wrote: > Hey guys, > > Myself and a small team of PostgreSQL contributors have started a new > community project for PostgreSQL Certification. It is just launching > but we wanted to get it out there so that people can join in on the > discussion now :). > > For more information please visit: > http://www.postgresqlcertification.org/ > Joshua D. Drake Thanks for such a good initiative, Josuah: «Your subscription request has been received..»: let's talk about this in the mailing-list :) Cheers, -- Jean-Paul Argudo www.PostgreSQLFr.org www.Dalibo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
