[GENERAL] Re: SELECT from record-returning function causes function code to be executed multiple times
dbaston wrote I'm wondering if this is expected behavior? Yes. The proper way to handle this is by putting the SRF in the FROM clause. If you must have it in the select clause you should do this: WITH srf_call ( SELECT srf_function() AS srf_result ) SELECT (srf_call.srf_result).* FROM srf_call; Note that if you are using 9.3 you likely can make use of the new LATERAL construct to leave the SRF call in the FROM clause while still pulling parameter values from the same level in the query (which is the main reason for moving the SRF to the select-list). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-from-record-returning-function-causes-function-code-to-be-executed-multiple-times-tp5783495p5783497.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: SELECT FOR UPDATE
On 26 Aug 2001 13:50:16 -0700, Cody [EMAIL PROTECTED] wrote: I just finished reading Bruce M's book, so this thread confuses me, esp. Jan's posts. I take full heed of the need for application level user/thread management, but I was interested in using a parallel set-up in PG (however redundant that might be). Now that Jan has discounted SELECT...FOR UPDATE, is the best alternative using a central locking table (perhaps in conjunction with LISTEN NOTIFY)? Ironically, anyone who suggested using application level transactions would be torn apart at any of the places I've worked at--but that seems to be the gist of this thread. I cannot see a way to avoid deadlocks without an application level transaction component, since the central locking table idea would similarily lock the record forever if the first transaction failed to COMMIT or ROLLBACK. What is the saying: To the beginner, there are many options. To the wise, there are few. It seems to me that SELECT ... FOR UPDATE is not the way to go if it is possible that the selected record may be held for any length of time. The best way around this is going to depend on the application and resolution of conflicts will probably take place in the application. For instance, say you are storing web pages in the database, and you want a number of developers to be able to get pages from the database look at them, and possibly make changes and update the record. Your application might have some kind of sliding time-out scale from a 3-minute I see a typo to a 30-minute This really needs work. It would probably also remind the author before the time-out or possibly automatically save and re-acquire the lock if possible. So, the lock table would need a time-out field, and might also include a queue of people waiting for the record. This is just how I was thinking of it, and again I think it will depend on the application. At some point, you're not making a database, you're creating a cvs. Though a hybrid could be good ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [GENERAL] Re: SELECT FOR UPDATE
On 26 Aug 2001 13:50:16 -0700, Cody [EMAIL PROTECTED] wrote: I just finished reading Bruce M's book, so this thread confuses me, esp. Jan's posts. I take full heed of the need for application level user/thread management, but I was interested in using a parallel set-up in PG (however redundant that might be). Now that Jan has discounted SELECT...FOR UPDATE, is the best alternative using a central locking table (perhaps in conjunction with LISTEN NOTIFY)? It certainly does not discount SELECT...FOR UPDATE (SFU). You need some way to implement a mutex of sorts at the DB level, in order to insert new lock records into the lock table, and this is where SFU comes into play. But ANY long running DB level transaction is generally a bad thing. Ironically, anyone who suggested using application level transactions would be torn apart at any of the places I've worked at--but that This also is definately not app level transactions. I've implemented a lock-table system on a non-transactional database (Paradox) as well, and it's not a pretty thing :-) Generally two DB transactions take place to effect a checkout/checkin cycle, but what happens in between those two operations is completely outside the scope of any kind of transactioning. seems to be the gist of this thread. I cannot see a way to avoid deadlocks without an application level transaction component, since the central locking table idea would similarily lock the record forever if the first transaction failed to COMMIT or ROLLBACK. If the first transaction fails, it is no different than any other transaction failing to end in a timely manor: problems :-) This isn't a special case, the database can't ever completely compensate for a mis-behaved application, since it can't possibly know how the application is *intended* to work. Provided the app(s) are well-behaved, the common problem would be where the second transaction (either an update/unlock or abandon/unlock) never happens. As I and others have mentioned, this can be handled by including some sort of timeout field in the lock table, a periodic process to clean stale lock records from the database, and a tool to explicitly remove locks that can be run by a privileged user. In my experience, with a properly designed timeout system, stale locks rarely get in the way; with reliable client-side software, they don't even occur very often. Glen Parker [EMAIL PROTECTED] ---(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
[GENERAL] Re: SELECT FOR UPDATE
But the question itself tells that you're about to implement a major design error in your application. Holding database locks during user interaction IS A BAD THING. Never, never ever do it that way. And anybody telling you something different is an overpaid idiot. I can see arguments to support this view, but consider this classic scenario: User1: Read data into an interactive program User1: Start to make changes User2: Read data into an interactive program User2: Start to make changes User1: Save changes User2: Save changes With no locks, both users will have the same original data, but User1's changes will not be seen by User2 and will therefore be lost. This is an unavoidable consequence of any multi-user system. If two (or more) users can modify the same record, they have to understand that someone else may be modifying that record. This shouldn't be a problem though because UPDATE statements only affect the fields that the user wants to update. User2 will only overwrite changes made by User1 if they both wanted those changes made. There are a few instances where changes might affect how User2 might edit the record, but the only ones I can think of are cumulative changes (for instance, an account balance). Those *do* need to be updated in some sort of critical section, such as that provided by SELECT FOR UPDATE, but I don't think they require attention from User2 unless some special situation occurs (the account would now be overdrawn). Alternatively, if transactions are used, User2's changes will be rolled back and lost. Why are they lost? The client should check to see if the transaction succeeds. If not, then it handles the situation in whatever manner makes the best sense. I would think handling an error on UPDATE is much more graceful for both the users and the system than locking the record while a user (who could simply step away from his computer for more coffee) holds the lock indefinitely. One way out is to do SELECT when reading and a SELECT FOR UPDATE just before saving; if the row has changed, the user is warned and must redo his changes -- but this could lead to the loss of a lot of editing. Also, a lengthy WHERE clause in the UPDATE can do the same. In other words, don't just use the primary key but all the fields. If the record doesn't EXACTLY match the WHERE statement, it can't be updated. But a failure to update doesn't mean a loss to editing. It is the client's responsibility to keep that data for as long as the user wants it. Let the client say Record couldn't be updated, some information has changed. Here is a summary of the changes: xxx. Press Ok to continue, Cancel to modify your changes. My ideal would be for SELECT FOR UPDATE to timeout with a message: table t primary key k locked by backend with PID p (using oid if there is no primary key). Personally as a user I'd rather handle the conflict resolution than be locked out of records entirely Another user is modifying this record. Please twiddle your thumbs and try again in a few minutes Ugh. I can only imagine how badly my users would badmouth me if they got a message like that above... Greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [GENERAL] Re: SELECT FOR UPDATE
I prefer the way Notes (for example) handles it. All records/documents/views are in read-only mode until the user indicates they actually want to edit. They then exclusively lock that record for editing, with optional timeouts (in case their workstation crashes or whatever). This turns out to work well in many situations where you realise the number of times you want to edit compared to the number of times you want to view, is quite small. Stops users having to worry whether anyone else is editing the record at the same time - the system simply won't let them - no loss of data. Andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: select to combine 2 tables
On Fri, 22 Jun 2001 [EMAIL PROTECTED] wrote: Use a union query: select rec_id, path, '' as link, name from cat_cat UNION select rec_id, path, link, name from cat_alias there is no way to do this in a generic DBI way? i need for this to work across diff kind of DBs. Notice that the two select statements need to have the same number of columns, and the fields should be in the same order. Field names don't have to match as long as the datatypes are compatible. Thomas T. Thai [EMAIL PROTECTED] on 06/22/2001 03:48:49 PM To: PostgreSQL General [EMAIL PROTECTED] cc:(bcc: Wesley Sheldahl/Lex/Lexmark) Subject: [GENERAL] select to combine 2 tables i have two tables: select * from cat_cat; ++--+--+ | rec_id | path | name | ++--+--+ | 1 | 0202 | water crafts | | 2 | 02 | classifieds | | 3 | 0204 | real estate | | 4 | 0201 | auto | | 5 | 0203 | pets | ++--+--+ select * from cat_alias; ++--+--++ | rec_id | path | link | name | ++--+--++ | 1 | 02@@ | 0201 | cars | | 2 | 02@@ | | myLink | ++--+--++ i would like to have a query so that it combines two tables stacked on top of each other instead of side by side: *** totally incorrect query*** SELECT * FROM cat_cat as cc, cat_alias as ca WHERE path like '02%'; so that i'd get this: ++--+--+--+ | rec_id | path | link | name | ++--+--+--+ | 1 | 0202 | | water crafts | | 2 | 02 | | classifieds | | 3 | 0204 | | real estate | | 4 | 0201 | | auto | | 5 | 0203 | | pets | | 1 | 02@@ | 0201 | cars | | 2 | 02@@ | | myLink | ++--+--+--+ what's the correct query to accomplish that task? i could stuff everything in one table to begin with like so: CREATE TABLE cat_alias ( rec_id int(11) NOT NULL PRIMARY KEY, path char(256) NOT NULL, link char(256) NOT NULL, name char(64) NOT NULL ); but since the 'link' column is an alias (symbolic link) pointing to a real path and is not used often, it would be waste of space. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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: [GENERAL] Re: select to combine 2 tables
Thomas T. Thai [EMAIL PROTECTED] writes: Use a union query: select rec_id, path, '' as link, name from cat_cat UNION select rec_id, path, link, name from cat_alias there is no way to do this in a generic DBI way? i need for this to work across diff kind of DBs. Huh? That *is* the generic, fully-SQL-standard way. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Re: select to combine 2 tables
On Fri, 22 Jun 2001, Tom Lane wrote: Thomas T. Thai [EMAIL PROTECTED] writes: Use a union query: select rec_id, path, '' as link, name from cat_cat UNION select rec_id, path, link, name from cat_alias there is no way to do this in a generic DBI way? i need for this to work across diff kind of DBs. Huh? That *is* the generic, fully-SQL-standard way. the project i'm doing this for is opensource and i need it to work in both postgresql and mysql. the above statement wouldn't work in mysql. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Re: select to combine 2 tables
Thomas T. Thai [EMAIL PROTECTED] writes: On Fri, 22 Jun 2001, Tom Lane wrote: Thomas T. Thai [EMAIL PROTECTED] writes: Use a union query: select rec_id, path, '' as link, name from cat_cat UNION select rec_id, path, link, name from cat_alias there is no way to do this in a generic DBI way? i need for this to work across diff kind of DBs. Huh? That *is* the generic, fully-SQL-standard way. the project i'm doing this for is opensource and i need it to work in both postgresql and mysql. the above statement wouldn't work in mysql. MySQL isn't close to SQL compatible. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Select
SELECT * FROM employee WHERE empname LIKE 'P%' the above would work only if you want to search for people w/ names starting w/ P Mike - Original Message - From: "Marcelo Pereira" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 17, 2001 5:23 AM Subject: Select Hi All, I'm in trouble to build a select... I have a table (for example) like this: Table: Employee empCod | empName | empDepth -+---+--- 1 | Anand |any 2 | Oliver |any 3 |Peter |any 4 | Bob |any 5 |David | any 6 | Paul |any Now I would like to select all employees which name begins with the letter "P". Select * from employee where "name-begin-with-letter-P" :-) How can I do it ??? Thanks in advance, Marcelo Pereira Unicamp - Brazil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: SELECT (sometimes) returning Zero Rows?
It's version 7.0.3 Here's the output for the selects using explain below. We had planned to do all of the optimization later. We just haven't had time to get to it yet. Could that be the problem? Anyhow here's the info. Please let me know if you need more information. Appreciatively, Matt Friedman. parent_report_new= explain parent_report_new- SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE parent_report_new- pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new- = 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND parent_report_new- pr_article.release_date 982093578 AND ((pr_article.end_date = 0) or parent_report_new( (pr_article.end_date 982093578 )) ORDER BY pr_article.release_date DESC parent_report_new- LIMIT 2; NOTICE: QUERY PLAN: Sort (cost=4.39..4.39 rows=1 width=24) - Nested Loop (cost=0.00..4.38 rows=1 width=24) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new= explain parent_report_new- SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE parent_report_new- pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new- = 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND parent_report_new- pr_article.release_date 982093578 AND ((pr_article.end_date = 0) or parent_report_new( (pr_article.end_date 982093578 )) ORDER BY pr_article.release_date DESC parent_report_new- LIMIT 3; NOTICE: QUERY PLAN: Sort (cost=4.39..4.39 rows=1 width=24) - Nested Loop (cost=0.00..4.38 rows=1 width=24) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new= explain parent_report_new- SELECT DISTINCT ON (release_date, article_id) pr_article.title, parent_report_new- pr_article.article_id, pr_cat.prog_name FROM pr_article WHERE parent_report_new- pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new- = 1 AND pr_article_cat.cat_id=pr_cat.cat_id AND pr_article.article_id = parent_report_new- pr_article_cat.article_id AND pr_article.is_active = true AND parent_report_new- pr_article.is_approved = true AND pr_article.release_date 982093578 AND parent_report_new- ((pr_article.end_date = 0) or (pr_article.end_date 982093578 )) ORDER BY parent_report_new- pr_article.release_date DESC LIMIT 5; NOTICE: QUERY PLAN: Unique (cost=7.63..7.64 rows=0 width=48) - Sort (cost=7.63..7.63 rows=1 width=48) - Nested Loop (cost=0.00..7.62 rows=1 width=48) - Nested Loop (cost=0.00..5.58 rows=1 width=28) - Nested Loop (cost=0.00..4.36 rows=1 width=12) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_cat_article_id_key on pr_article_cat (cost=0.00..2.01 rows=1 width=8) - Seq Scan on pr_cat (cost=0.00..1.10 rows=10 width=16) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new= explain parent_report_new- SELECT DISTINCT ON (pr_cat.name) pr_cat.name, pr_cat.prog_name FROM parent_report_new- pr_article, pr_article_age, pr_article_cat, pr_cat WHERE parent_report_new- pr_article_age.age_id = 1 AND pr_article_age.article_id = parent_report_new- pr_article.article_id AND pr_article.article_id = pr_article_cat.article_id parent_report_new- AND pr_article_cat.cat_id = pr_cat.cat_id AND pr_article.is_active = true parent_report_new- AND pr_article.is_audio = false AND pr_article.is_approved = true AND parent_report_new- pr_article.release_date 982093578 AND ((pr_article.end_date = 0) or parent_report_new( (pr_article.end_date 982093578 )) parent_report_new- ; NOTICE: QUERY PLAN: Unique (cost=7.64..7.64 rows=0 width=44) - Sort (cost=7.64..7.64 rows=1 width=44) - Nested Loop (cost=0.00..7.63 rows=1 width=44) - Nested Loop (cost=0.00..6.40 rows=1 width=16) - Nested Loop (cost=0.00..4.36 rows=1 width=12) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_cat_article_id_key on pr_article_cat (cost=0.00..2.01 rows=1 width=8) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=4) - Seq Scan on pr_cat (cost=0.00..1.10 rows=10 width=28) EXPLAIN - Original Message - From: "Tom Lane" [EMAIL PROTECTED] To: "Matt Friedman" [EMAIL PROTECTED] Cc: "PgSql General List" [EMAIL PROTECTED] Sent: Tuesday, February 13, 2001 3:46 PM Subject: Re: SELECT (sometimes) returning Zero Rows? "Matt Friedman" [EMAIL
Re: [GENERAL] Re: SELECT (sometimes) returning Zero Rows?
On Wed, 14 Feb 2001 15:03, you wrote: It's version 7.0.3 Here's the output for the selects using explain below. We had planned to do all of the optimization later. We just haven't had time to get to it yet. Could that be the problem? Anyhow here's the info. Please let me know if you need more information. This has the same smell about it as the one a couple of days ago where the CR characters in a file loaded into the database using COPY were upsetting things. So, How did you populate your database? -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -- Please refrain from using HTML or WORD attachments in e-mails to me --
[GENERAL] Re: select question
Tom Lane writes: g [EMAIL PROTECTED] writes: Use the limit clause. SELECT message_text FROM messages ORDER BY creation_date LIMIT $limit, $offset. LIMIT 10, 0 gets you the first batch. LIMIT 10, 10 gets you the second batch. LIMIT 10, 20 gets you the third, etc. BTW, a little tip that a number of people have gotten burnt by not knowing: when you do this you *must* use an ORDER BY clause that's strong enough to order the result rows completely. Otherwise you are asking for slices out of an undefined ordering of the rows. You could get a different ordering on each request, leading to inconsistent slices --- in other words, missing or repeated rows. This does actually happen in Postgres 7.0, because the planner optimizes queries with small limit+offset differently from those without. regards, tom lane Hi, I wonder if one must activate the LIMIT clause somewhere, bacause for me it does nothing. I'm using postgresql Version: 7.0.2 in a Debina potato system. Thanx. -- __ Felipe Alvarez Harnecker. QlSoftware. Tel. 09.874.60.17 e-mail: [EMAIL PROTECTED] Potenciado por Debian GNU/Linux http://www.qlsoft.cl/ __
[GENERAL] Re: select question
Felipe Alvarez Harnecker [EMAIL PROTECTED] writes: Hi, I wonder if one must activate the LIMIT clause somewhere, uh ... no ... bacause for me it does nothing. Details? What query did you issue exactly, and what did you get? regards, tom lane
[GENERAL] Re: select question -- SOLVED
Tom Lane writes: Felipe Alvarez Harnecker [EMAIL PROTECTED] writes: Hi, I wonder if one must activate the LIMIT clause somewhere, uh ... no ... bacause for me it does nothing. Details? What query did you issue exactly, and what did you get? regards, tom lane Hi, before borring you, i've tested the query with psql and it worked. I was testing the query with pgaccess. Maybe i'ts a library bug or something. Regards. -- __ Felipe Alvarez Harnecker. QlSoftware. Tel. 09.874.60.17 e-mail: [EMAIL PROTECTED] Potenciado por Debian GNU/Linux http://www.qlsoft.cl/ __
