Re: Fwd: Re: [SQL] Can I search for an array in csf?
Hi, Achilleus and Josh, I know three ways to store dynamic array in DB: object, xml or csv, and sub-table. It seems to me there are some problems of using the first method in Java. I don't know how the third method work out. That is the reason I use the second method. In my project, the simplest array type is an array of characters or user IDs. The complexest array is an array of data type of userid and another text field. All operations on the arrays are simple: either element look up, add or delete an element. Which method is the most suitable for those different operations. Thanks very much for all your helps. Vernon Thanks for your information. See below. 10/22/2002 1:03:56 AM, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: >On Mon, 21 Oct 2002, Josh Berkus wrote: > >> >> Vernon, >> >> > >> One field of a table stores an array of characters in a string fromat as >> > >> "a,b,c,d". Is anyway to apply a select statement without using stored >> > >> procedure? >> >> > The reason I use this format for an array is that the array is dynamic. I >> have quite few cases of this type of situation. The >> > maximize length in some cases is known, is unknown in others. I have learnt >> the comment separated format is one way >> > to solve the problem. Someone also suggested to store the array as an >> object. I am not sure whether it works or not. >> > The application is written in Java, by the way. >> >> You should store this data in a sub-table linked through a foriegn key. >> Period. Messing with arrays will only lead you to heartache ... > >It depends. >I can tell you of situations that doing it with child tables >will hurt performance really bad. >Its just a matter of complexity. > >One of the apps we run over here, deals with bunker >analysis of the vessels of our fleet. > >For each vessel there are 4 formulas that describe the parameters of >the consumption of fuel oil under some given conditions. > >I have implemented this using arrays. >The app is written in J2EE. > >On a dual xeon 2.2 GHz with 1 GB for postgres, >it takes about 900 miliseconds to compute >some statistics (average, std deviation,etc..) >of the consumption of all vessels (about 20 of them) >for a period of 3 years (the values are stored for each day). > >Before going with the formulas, we had a rather >primitive scheme originated from the previous >cobol application, based on subtable look ups, >(and there was no serious computations involved >just table lookups). > >I can tell you the performance boost was remarkable. > >> >> Try the book "Database Design For Mere Mortals" for a primer on SQL DB design. >> >> -- >> -Josh Berkus >> Aglio Database Solutions >> San Francisco >> >> >> ---(end of broadcast)--- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/users-lounge/docs/faq.html >> > >== >Achilleus Mantzios >S/W Engineer >IT dept >Dynacom Tankers Mngmt >Nikis 4, Glyfada >Athens 16610 >Greece >tel:+30-10-8981112 >fax:+30-10-8981877 >email: [EMAIL PROTECTED] >[EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Fwd: Re: [SQL] Can I search for an array in csf?
On Mon, 21 Oct 2002, Vernon Wu wrote: > > > > > Hi, Richard, > > Thanks for your response and see below. > > 10/21/2002 3:13:57 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > > >On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote: > >> One field of a table stores an array of characters in a string fromat as > >> "a,b,c,d". Is anyway to apply a select statement without using stored > >> procedure? > >> > >> Thanks for your input. > > > >Not really, and I can't think any way of accessing an index on this either. > >Are you sure you wanted the characters stored this way? Even if the overhead > >of a separate table isn't woth it, you might want to look into arrays and the > >intarray stuff in contrib/ > > > > The reason I use this format for an array is that the array is dynamic. I have quite >few cases of this type of situation. The > maximize length in some cases is known, is unknown in others. I have learnt the >comment separated format is one way > to solve the problem. Someone also suggested to store the array as an object. I am >not sure whether it works or not. > The application is written in Java, by the way. > > I have taken a look at intarray by searching on the postgres.org web site as well as >in google. (I use cypwin and unable > to find the contrib directory). My impression is it isn't a standard SQL data type. >And its element is integer only. > > It is my first time doing DB table design. Any helps will be gracfully appreciated. Well some comments, since i have done a lot of work with arrays, postgresql and java. Arrays are supported by the postgresql jdbc driver just fine. Arrays can be of any valid builtin or user defined type. Postgresql Arrays along with intarray package are ideal for doing small set manipulations, where the design fits the natural model of your data (e.g. storing the factors of a polynomial formula). It would be nice if you had yourself a crash course on relational db design. Also i would advise you compiling and running postgres on a unix system. Note that in order to compile the intarray package you need to have the sources installed. > > Thanks, > > Vernon > > > > >-- > > Richard Huxton > > > >---(end of broadcast)--- > >TIP 3: 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 > > > > > End of forwarded message > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Fwd: Re: [SQL] Can I search for an array in csf?
On Mon, 21 Oct 2002, Josh Berkus wrote: > > Vernon, > > > >> One field of a table stores an array of characters in a string fromat as > > >> "a,b,c,d". Is anyway to apply a select statement without using stored > > >> procedure? > > > The reason I use this format for an array is that the array is dynamic. I > have quite few cases of this type of situation. The > > maximize length in some cases is known, is unknown in others. I have learnt > the comment separated format is one way > > to solve the problem. Someone also suggested to store the array as an > object. I am not sure whether it works or not. > > The application is written in Java, by the way. > > You should store this data in a sub-table linked through a foriegn key. > Period. Messing with arrays will only lead you to heartache ... It depends. I can tell you of situations that doing it with child tables will hurt performance really bad. Its just a matter of complexity. One of the apps we run over here, deals with bunker analysis of the vessels of our fleet. For each vessel there are 4 formulas that describe the parameters of the consumption of fuel oil under some given conditions. I have implemented this using arrays. The app is written in J2EE. On a dual xeon 2.2 GHz with 1 GB for postgres, it takes about 900 miliseconds to compute some statistics (average, std deviation,etc..) of the consumption of all vessels (about 20 of them) for a period of 3 years (the values are stored for each day). Before going with the formulas, we had a rather primitive scheme originated from the previous cobol application, based on subtable look ups, (and there was no serious computations involved just table lookups). I can tell you the performance boost was remarkable. > > Try the book "Database Design For Mere Mortals" for a primer on SQL DB design. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] 'next' or similar in plpgsql
Hi, In perl we have 'next' function to skip rest of the statements in the loop and to start with next iteration. In plpgsql, do we have something similar? How do we skip rest of the statements in a loop in plpgsql? TIA. regards, bhuvaneswaran ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Locking that will delayed a SELECT
--- Tom Lane <[EMAIL PROTECTED]> wrote: > A simple answer is to have T1 grab an ACCESS > EXCLUSIVE lock on some > table to block T2's progress. If that locks out > third-party > transactions that you'd rather would go through, you > can probably use > a lesser form of lock --- but then both T1 and T2 > will have to cooperate > since each will need to explicitly take a lock. - Is there a possibility of having a lock that similar to a row level ACCESS EXCLUSIVE (i.e. ROW ACCESS EXCLUSIVE lock) in the future release of PostgreSQL? The ACCESS EXCLUSIVE lock also locks the rows not used in T1, making concurrent transactions almost impossible. regards, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Row Locking?
On Monday 21 Oct 2002 11:09 pm, eric soroos wrote: > I have a long running process that performs outside actions on the content > of a table. The actions could all be done in parallel (if I had n > processors), but I need to ensure that the process is attempted exactly one > time per applicable row. > Extending this to multiple threads if proving problematic. No locking leads > to a race condition between the select and update. If I change the select > to a SELECT ... FOR UPDATE it apparently locks the table against all other > select for updates, then when the update is committed, the second thread > returns nothing, even when there are other rows in the table that could be > returned. > > Is there a single row locking against select? Or can I effeciently do the > equivalent of update set pending, then select the row that I just updated > to get the contents? (perhaps without doing a table scan to find the oid of > the row that I just updated). I can't afford to lock the entire table. I think the issue is that both threads are hanging about for the same row. If the first thread rolled back rather than committing, the second thread might return the same row. You've only locked the one row, but that's the one row everyone wants. How about something like: 1. SELECT FOR UPDATE... 2. IF no id returned, sleep, goto 1 3. UPDATE foo set pending='t' ... A different solution might be to have one thread allocating rows to the others. So - make "pending" something that holds a thread id. That way the processing threads just sit there trying to fetch the first allocated row and sleep if none available. Alternatively, the allocating thread could pass the info directly to the processing threads. Might be worth checking the archives for this list and pgsql-general - someone had a similar question a few weeks/months ago. -- Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] 7.2 date/time format function problems
I have just installed redhat 8.0. It comes with postgresql rpms for 7.2.2. I have been trying to get an application I have written to work, but my queries fail whenever I have queries that use internal date/time formatting functions. example: SELECT a.assignmentid AS "id", u.lastname || ', ' || u.firstname || ' ' || u.middleint AS "assigned", j.name AS "job_name", extract(DOW FROM TIMESTAMP TIMESTAMP(a.startdate)) AS "dow", TO_CHAR(TO_TIMESTAMP(a.starttime, 'HH:MI:SS'), 'HH12:MI am') AS "starttime", TO_CHAR(TO_TIMESTAMP(a.stoptime, 'HH:MI:SS'), 'HH12:MI am') AS "stoptime", ((extract(HOUR FROM TIME (a.stoptime - a.starttime)) + (extract(MINUTE FROM TIME (a.stoptime - a.starttime))/60)) - a.break) AS "hrs", a.break AS "break" FROM assignment a LEFT JOIN users u USING(userid), schedule s, job j, account ac, location l, groups g WHERE s.scheduleid = 1 AND s.scheduleid = a.scheduleid AND s.accountid = 3 AND s.accountid = ac.accountid AND s.locationid = 1 AND s.locationid = l.locationid AND s.groupid = g.groupid AND s.scheduleid = s.scheduleid AND a.jobid = j.jobid ORDER BY j.name ASC, a.starttime ASC, u.lastname ASC; ERROR: parser: parse error at or near "TIMESTAMP" The problem areas are the timestamp() and extract(hour from time) functions. If anyone would be so kind as to help me with this issue, it would be greatly appreciated. I don't know if I have to enable something for these functions to work or if the format changed for 7.2.2. I checked the documentation and it was exactly like 7.1. Thanks for your help, Nicholas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] using deferred on PK/FK relationships
Can deferrable etc be used when deleting primary key records (master table), then reinserting them without losing foreign key records (slave table)? I ask because in our testing we can't; we lose the foreign key records in the slave table. I'm guessing we are trying to abuse the feature. here's a test script we tried: drop table master; CREATE TABLE master ( id integer NOT NULL, Primary Key (id) ); insert into master values (1); insert into master values (2); drop table slave; create table slave ( id int, foreign key (id)references master (id) on update restrict on delete cascade INITIALLY DEFERRED) ; insert into slave values (1); insert into slave values (1); Then: test=# BEGIN; BEGIN test=# SET CONSTRAINTS ALL DEFERRED; SET CONSTRAINTS test=# delete from master; DELETE 2 test=# insert into master values (1); INSERT 20959595 1 test=# insert into master values (2); INSERT 20959596 1 test=# select * from slave; id 1 1 (2 rows) test=# commit; COMMIT test=# select * from slave; id (0 rows) test=# Our hope was that after the commit, slave would retain the original rows. cheers -- Dan Langille ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 7.2 date/time format function problems
On Tue, 22 Oct 2002, Nicholas Barthelemy wrote: > I have just installed redhat 8.0. It comes with postgresql rpms for > 7.2.2. I have been trying to get an > application I have written to work, but my queries fail whenever I have > queries that use internal > date/time formatting functions. > example: > > SELECT a.assignmentid AS "id", > u.lastname || ', ' || u.firstname || ' ' || u.middleint AS "assigned", > j.name AS "job_name", > extract(DOW FROM TIMESTAMP TIMESTAMP(a.startdate)) AS "dow", > TO_CHAR(TO_TIMESTAMP(a.starttime, 'HH:MI:SS'), 'HH12:MI am') AS > "starttime", > TO_CHAR(TO_TIMESTAMP(a.stoptime, 'HH:MI:SS'), 'HH12:MI am') AS "stoptime", > ((extract(HOUR FROM TIME (a.stoptime - a.starttime)) + > (extract(MINUTE FROM TIME (a.stoptime - a.starttime))/60)) - a.break) > AS "hrs", > a.break AS "break" > FROM assignment a LEFT JOIN users u USING(userid), > schedule s, job j, account ac, location l, groups g > WHERE s.scheduleid = 1 AND > s.scheduleid = a.scheduleid AND > s.accountid = 3 AND > s.accountid = ac.accountid AND > s.locationid = 1 AND > s.locationid = l.locationid AND > s.groupid = g.groupid AND > s.scheduleid = s.scheduleid AND > a.jobid = j.jobid > ORDER BY j.name ASC, a.starttime ASC, u.lastname ASC; > > ERROR: parser: parse error at or near "TIMESTAMP" > > The problem areas are the timestamp() and extract(hour from time) > functions. If anyone would > be so kind as to help me with this issue, it would be greatly > appreciated. I don't know if I have to > enable something for these functions to work or if the format changed > for 7.2.2. I checked the > documentation and it was exactly like 7.1. timestamp() and time() became the type specifiers for the type with a particular precision. You can use "timestamp"() or "time"() or it'd probably be better to use SQL standard casts, CAST (expr AS type). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] using deferred on PK/FK relationships
On Tue, 22 Oct 2002, Dan Langille wrote: > Can deferrable etc be used when deleting primary key records (master > table), then reinserting them without losing foreign key records > (slave table)? I ask because in our testing we can't; we lose the > foreign key records in the slave table. I'm guessing we are trying to > abuse the feature. > test=# BEGIN; > BEGIN > test=# SET CONSTRAINTS ALL DEFERRED; > SET CONSTRAINTS > test=# delete from master; > DELETE 2 > test=# insert into master values (1); > INSERT 20959595 1 > test=# insert into master values (2); > INSERT 20959596 1 > test=# select * from slave; > id > > 1 > 1 > (2 rows) > > test=# commit; > COMMIT > test=# select * from slave; > id > > (0 rows) > > test=# > > Our hope was that after the commit, slave would retain the original > rows. As far as I can tell the above is close to right (I'd have said that the select in the transaction should have given you 0 rows as well but that's a matter of argument). In case you're wondering, the spec says for match full/unspecified something to the effect of: when a row is marked for deletion that has not previously been marked for deletion with on delete cascade all matching rows are marked for deletion. So, I don't think you can get the effect you're looking for that way. Someone else mentioned this recently and I was thinking that it might be a useful extension to add another referential action to handle it (and it wouldn't be particularly hard probably). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] 'fake' join and performance ?
OK, I am now confused; postgresql 7.3beta2 on OpenBSD: photos=# select * from metadata WHERE name = 'Make' and value = 'Canon' limit 10; *bang*, 10 values, sub second response. photos=# select * from metadata m, images i WHERE m.name = 'Make' and m.value = 'Canon' limit 10; *yawn* - see you later... Now, 'images' is a new and currently empty table that I intend to do a join on later, but I started building a query to test my join'ing skills and found this; Explain'ing for both: photos=# explain select * from metadata WHERE name = 'Make' and value = 'Canon' limit 10; QUERY PLAN Limit (cost=0.00..27711.98 rows=6 width=92) -> Index Scan using metadata_index_2 on metadata (cost=0.00..31072.94 rows=7 width=92) Index Cond: (name = 'Make'::text) Filter: (value = 'Canon'::text) (4 rows) photos=# explain select * from metadata m, images i WHERE m.name = 'Make' and m.value = 'Canon' limit 10; QUERY PLAN Limit (cost=0.00..27712.04 rows=6 width=816) -> Nested Loop (cost=0.00..31073.00 rows=7 width=816) -> Index Scan using metadata_index_2 on metadata m (cost=0.00..31072.94 rows=7 width=92) Index Cond: (name = 'Make'::text) Filter: (value = 'Canon'::text) -> Seq Scan on images i (cost=0.00..0.00 rows=1 width=724) (6 rows) Er, what's that nested loop. I *know* I have shot myself in the foot somehow, but my initial reaction was that the optimiser should just make the 'fake' (i.e. unreferenced) reference to another table go away... peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Can I search for an array in csf?
Vernon, > Thanks Josh, I will take a look at this book. > > The only problem with the implemention I can see so far is > performance: change format when read/write data. Other > than that, I don't see a big problem. I, however, shall say the > performance is important in my project. I'd think Java would be able to do array --> table and table --> array very quickly. Certainly, PHP and Perl do. Also, you can use a custom aggregate to give yourself a comma-delimited list from a subtable (see the article on custom aggregates at techdocs.postgresql.org) However, custom aggregates are slow and Java's array-handling is probably faster. -Josh Berkus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Can I search for an array in csf?
Hi, Christoph,
Thanks for reminding me regular expression.
The background of my question is about attributes of one element, say hobby for
example. Different people have
different hobbies. Inside of the application, hobbies are denoted in various
characters. The selection I mentioned in the
original mail refers to finding people who have certain hobbies in the case.
I think regular expression is the most effective way for the usage.
Thanks again.
Vernon
10/22/2002 5:50:22 AM, Christoph Haller <[EMAIL PROTECTED]> wrote:
>>
>> I thought I had made my case clear. Let me rephrase it.
>>
>> I have a character array, {'a', 'b', 'c', 'd'} for example. And this
>array is stored in a DB table field as a string
>of "a,b,c,d".
>> Now, I want to find out whether the table field, or array, contains
>any character set of {'c', 'e', 'h'}. My question
> is
>> whether SQL statement is appliable for this selection, or the data has
>been retrieved and process in the application
>> level.
>>
>> I hope I make th case clear this time.
>>
>Ok, now I see.
>Maybe the Postgres POSIX Regular Expressions are what you are looking
>for.
>Suppose your array "a,b,c,d" is stored into a table field of type
>character,
>character varying or text.
>Then, if you are searching for all entries containing a 'c', 'e', or
>'h' character
>your SELECT statement would look like
>
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ '[ceh]' ;
>
>Still not sure if you mean a sequence like "c,e,h" on the other hand.
>Then
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ 'c,e,h' ;
>should do the trick - as long as the order of characters is identical.
>A sequence of "e,c,h" would not show up, of course.
>
>Regular Expressions are far more powerful than these two examples can
>show.
>Refer to the related chapter in the documentation.
>I hope this helps more than the other replies you've received telling
>you
>'Learn about DB-design first'.
>
>Regards, Christoph
>
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] 'fake' join and performance ?
On Tue, 22 Oct 2002, Peter Galbavy wrote: > OK, I am now confused; postgresql 7.3beta2 on OpenBSD: > > > photos=# select * from metadata WHERE name = 'Make' and value = 'Canon' > limit 10; > > *bang*, 10 values, sub second response. > > photos=# select * from metadata m, images i WHERE m.name = 'Make' and > m.value = 'Canon' limit 10; > > *yawn* - see you later... > > > > Limit (cost=0.00..27712.04 rows=6 width=816) >-> Nested Loop (cost=0.00..31073.00 rows=7 width=816) > -> Index Scan using metadata_index_2 on metadata m > (cost=0.00..31072.94 rows=7 width=92) >Index Cond: (name = 'Make'::text) >Filter: (value = 'Canon'::text) > -> Seq Scan on images i (cost=0.00..0.00 rows=1 width=724) > (6 rows) > > > > Er, what's that nested loop. I *know* I have shot myself in the foot > somehow, but my initial reaction was that the optimiser should just make the > 'fake' (i.e. unreferenced) reference to another table go away... It can't do that. The second query would give multiple copies of each row in metadata for each row in images. I'm surprised that it'd be so slow if images is completely empty though. What does explain analyze show for the real times. ---(end of broadcast)--- TIP 3: 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: [SQL] 'fake' join and performance ?
"Peter Galbavy" <[EMAIL PROTECTED]> writes: > photos=# select * from metadata m, images i WHERE m.name = 'Make' and > m.value = 'Canon' limit 10; > Er, what's that nested loop. I *know* I have shot myself in the foot > somehow, Yeah, you didn't restrict the reference to images at all. > but my initial reaction was that the optimiser should just make the > 'fake' (i.e. unreferenced) reference to another table go away... That would be in violation of the SQL spec. The query is defined to return each join row from the cross product of the FROM tables that meets the condition of the WHERE clause. As you wrote the query, each metadata row that meets the WHERE clause will be returned exactly as many times as there are rows in the images table. There is no such thing as an "unreferenced" FROM entry as far as SQL is concerned. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'fake' join and performance ?
> That would be in violation of the SQL spec. The query is defined to > return each join row from the cross product of the FROM tables that > meets the condition of the WHERE clause. As you wrote the query, each > metadata row that meets the WHERE clause will be returned exactly as > many times as there are rows in the images table. There is no such > thing as an "unreferenced" FROM entry as far as SQL is concerned. Sounds about right. Thanks for the clarification. Peter ---(end of broadcast)--- TIP 3: 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
