Re: [HACKERS] someone working to add merge?
On Fri, 2005-11-11 at 20:22, Bruno Wolff III wrote: On Fri, Nov 11, 2005 at 18:48:33 +0100, Csaba Nagy [EMAIL PROTECTED] wrote: OK, I'm relatively new on this list, and I might have missed a few discussions on this topic. I wonder if doing it this way would not be better than using a table lock: - set a save point; - insert the row; - on error: - roll back to the save point; - update the row; - on success release the save point; This would provide less contention while paying the prise for the save point. In low contention scenarios the table lock would be better, and I wonder for high contention scenarios which is better, the table lock, or the save point version... You may not be able to update the row after the insert fails. If there is insert occurring in another transaction, the row may not be visible to the current transaction. In which case you can neither insert or update the row. You need to wait for the other transaction to commit or rollback. Are you sure ? From what I understand, the insert will only fail when the other transaction commits, and actively wait for the commit or rollback. Look at this: session_1= create table test (col smallint primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index test_pkey for table test CREATE TABLE session_1= begin; BEGIN cnagy= insert into test values (1); INSERT 165068987 1 session_2= begin; BEGIN session_2= insert into test values (1); [session_2 is now waiting] session_1= commit; COMMIT [session_2 wakes up] ERROR: duplicate key violates unique constraint test_pkey So it looks like predicate locking is already in place for primary key conditions... Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Supporting NULL elements in arrays
On trying to recompile things, I find that contrib/intarray is broken by this change, because it's using the flags field for its own purposes: /* * flags for gist__int_ops, use ArrayType-flags * which is unused (see array.h) */ #define LEAFKEY (131) #define ISLEAFKEY(x)( ((ArrayType*)(x))-flags LEAFKEY ) It seems likely that intarray is going to need some rather significant work anyway to deal with null elements, so this seems to me to be not necessarily a fatal objection. But why exactly does intarray need to play games with the contents of an array value? Sorry, intarray was first our module for PgSQL. I'll remove usage of ArrayType-flags soon. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] outer joins and for update
Hi all, A colleague pointed out to me today that the following is actually possible on Oracle, MySQL, et al: template1=# create table a (i int); CREATE TABLE template1=# create table b (i int); CREATE TABLE template1=# insert into a values(1); INSERT 0 1 template1=# select * from a left outer join b on (a.i=b.i); i | i ---+--- 1 | (1 row) template1=# select * from a left outer join b on (a.i=b.i) for update of b; ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join The comment in initplan.c around line 325 is: /* * Presently the executor cannot support FOR UPDATE/SHARE marking of * rels appearing on the nullable side of an outer join. (It's * somewhat unclear what that would mean, anyway: what should we * mark when a result row is generated from no element of the * nullable relation?) So, complain if target rel is FOR UPDATE/SHARE. * It's sufficient to make this check once per rel, so do it only * if rel wasn't already known nullable. */ As I said, it seems that this is actually possible on other databases. (MySQL might not be the best example: they seem to take a write lock on the tables, not a row lock -- tested with Innodb [MyISAM silently ignores the lock instructions]). I looked to the spec for instruction on this matter and could find nothing. I think we could, in fact, lock rows on the nullable side of the join if we say that locking the NULL rows is not necessary. The rows do not physical exist and I could see an argument which says that those rows do not match any other rows which a concurrent transactions if attempting to modify -- since they don't exist. Does anyone have any thoughts on this matter? Thanks, Gavin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] syntax for drop if exists
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: The MySQL syntax is actually drop table if exists foo Implementing this unfortunately generates a shift/reduce conflict, What did you try exactly? I don't see any fundamental reason for a conflict here. You may just need to rearrange the grammar to postpone the reduction a bit. You're right, as usual. I had factored out the IF EXISTS bit into a seperate rule. When I undid that and instead used 2 rules for DropStmt, the problem disappeared. (This is because it gives bison more info about the context of each IF - this has often caught me with bison - I should have known better). cheers andrew ---(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: [HACKERS] outer joins and for update
Gavin Sherry [EMAIL PROTECTED] writes: I think we could, in fact, lock rows on the nullable side of the join if we say that locking the NULL rows is not necessary. The rows do not physical exist and I could see an argument which says that those rows do not match any other rows which a concurrent transactions if attempting to modify -- since they don't exist. The point of the comment really is that this is a predicate locking problem. I should think that a minimum expectation of SELECT FOR UPDATE is that you have exclusive hold on the selected rows and they won't change underneath you before the end of your transaction. In the case of an outer join where the left-side row joined to nothing on the right-side, we can't guarantee that: repeating the SELECT might find a matching right-side row, thereby changing the allegedly-locked join row. To guarantee a stable view of the data, we'd need a predicate lock that prevents a matching right-side row from being inserted. The fact that MySQL doesn't care about consistency or sane semantics is no news, of course, but I'm slightly more interested by your claim that Oracle allows this. What do they do about the locking issue? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
Gavin Sherry: Grouping sets Recursive queries The recursive queries is a long-awaited feature. Does the fact that the feature is listed for Gavin Sherry mean that Gavin is actually working with the feature at the moment? Does anybody know the current state of this feature or know when it will be public available? ---(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
[HACKERS] functions marked STABLE not allowed to do INSERT
New in 8.1 it seems functions marked STABLE are not allowed to have any INSERT statement in them. However in this particular case, the insert does not violate the rule: STABLE indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements. it does basically lookup a value by a foreign key and builds a surrogate key on demand. I know I could make it volatile but otoh I really want the optimizer to optimize calls away as possible. Now, what to do beside a private revert to the patch? Regards Tino ---(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: [HACKERS] functions marked STABLE not allowed to do INSERT
On 11/14/05, Tino Wildenhain [EMAIL PROTECTED] wrote: New in 8.1 it seems functions marked STABLE are not allowed to have any INSERT statement in them. this is not new, always was said that SATBLE and IMMUTABLE functions must not modify the database. But beginning with 8.0.0 these kind of thing are checked at compile time. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Should a plan node's result tuple slot be read-only to caller?
I looked into Frank van Vugt's recent report of bizarre behavior in 8.1: http://archives.postgresql.org/pgsql-bugs/2005-11/msg00121.php The problem occurs because execMain.c's ExecInsert() replaces the contents of the TupleTableSlot passed to it with whatever the trigger hands back. This slot is the result slot of the top-level plan node, which in the case at hand is a Unique node. In 8.0 and before, this does not result in a failure, because Unique is keeping a separate copy of its last output tuple to compare to; the 8.0 code comments: /* * We have a new tuple different from the previous saved tuple (if * any). Save it and return it. We must copy it because the source * subplan won't guarantee that this source tuple is still accessible * after fetching the next source tuple. * * Note that we manage the copy ourselves.We can't rely on the result * tuple slot to maintain the tuple reference because our caller may * replace the slot contents with a different tuple. We assume that * the caller will no longer be interested in the current tuple after * he next calls us. * * tgl 3/2004: the above concern is no longer valid; junkfilters used to * modify their input's return slot but don't anymore, and I don't * think anyplace else does either. Not worth changing this code * though. */ In connection with the virtual tuple slot optimization added for 8.1, I rewrote this code and got rid of the supposedly-redundant extra tuple copy. nodeUnique is now comparing the next input tuple directly to the contents of its result slot, and so it gets fooled when the caller changes that slot. (IOW, the comment I added in 3/2004 was wrong...) The minimum-change way to fix the bug would be to revert the logic change in nodeUnique.c and go back to maintaining a separate tuple copy. But I'm thinking that this sort of thing could happen again. ISTM it's not intuitive to allow a plan node's caller to scribble on the plan node's result slot. An alternative solution would be to require execMain.c to keep an extra tuple table slot that has no other purpose than to temporarily hold replacement tuples during ExecInsert and ExecUpdate. I'm leaning towards the extra-slot approach, but wondered if anyone has comments or better ideas. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] outer joins and for update
On Mon, 14 Nov 2005, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: I think we could, in fact, lock rows on the nullable side of the join if we say that locking the NULL rows is not necessary. The rows do not physical exist and I could see an argument which says that those rows do not match any other rows which a concurrent transactions if attempting to modify -- since they don't exist. The point of the comment really is that this is a predicate locking problem. I should think that a minimum expectation of SELECT FOR UPDATE I thought you might say that. I'm yet to do much reading on predicate locking -- do you think it is an area we will even pursue? is that you have exclusive hold on the selected rows and they won't change underneath you before the end of your transaction. In the case of an outer join where the left-side row joined to nothing on the right-side, we can't guarantee that: repeating the SELECT might find a matching right-side row, thereby changing the allegedly-locked join row. To guarantee a stable view of the data, we'd need a predicate lock that prevents a matching right-side row from being inserted. Well we can guarantee that we wont see rows added by concurrent transactions if we're in serializable isolation level :-). The fact that MySQL doesn't care about consistency or sane semantics is no news, of course, but I'm slightly more interested by your claim that Oracle allows this. What do they do about the locking issue? I wont be able to actually test to see what they do until Thursday at the earliest. Their manual offers no detail. Gavin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond
Hi, On Tue, 14 Nov 2005 [EMAIL PROTECTED] wrote: Gavin Sherry: Grouping sets Recursive queries The recursive queries is a long-awaited feature. Does the fact that the feature is listed for Gavin Sherry mean that Gavin is actually working with the feature at the moment? Does anybody know the current state of this feature or know when it will be public available? I recall suggesting these features as being amongst those in demand. I don't remember saying that I'd actually do them... Gavin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
That's pretty bizarre. What's the datatype of the key column(s)? Can you reduce it to a smaller test case, or perhaps send me the full dump off-list? (270m is a bit much for email, but web or ftp would work ... also, presumably only the pkey column is needed to generate the error ...) I just confirmed that there are duplicate p-keys in the source table :(. Three currently but last week there were six. Just FYI I am not 100% sure pg rebuilt the p-key with dups in it...I need to double check this. esp=# select * from esp-# ( esp(# select prl_combined_key, prl_seq_no, count(*) as c from parts_order_line_file group by 1,2 esp(# ) q where q.c 1; prl_combined_key | prl_seq_no | c --++--- 00136860| 20 | 2 00136860| 23 | 2 00137050| 1 | 2 (3 rows) esp=# \d parts_order_line_file Table data1.parts_order_line_file Column | Type | Modifiers --+-+--- [...] Indexes: parts_order_line_file_pkey PRIMARY KEY, btree (prl_combined_key, prl_seq_no) parts_order_line_file_prl_exchange_part_key UNIQUE, btree (prl_exchange_part, id) parts_order_line_file_prl_item_no_key UNIQUE, btree (prl_item_no, id) parts_order_line_file_prl_trx_type_2_key UNIQUE, btree (prl_combined_key_2, prl_item_no, id) I keep a timestamp on every row for last modified date: esp=# select lastmod from parts_order_line_file where prl_combined_key = ' 00136860' and prl_seq_no in (20, 23); lastmod - 2005-09-15 11:17:17.062 2005-09-15 11:17:17.187 (2 rows) There have been no schema changes since 9/15... Merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Should a plan node's result tuple slot be read-only to caller?
On Mon, Nov 14, 2005 at 10:14:53AM -0500, Tom Lane wrote: The minimum-change way to fix the bug would be to revert the logic change in nodeUnique.c and go back to maintaining a separate tuple copy. But I'm thinking that this sort of thing could happen again. ISTM it's not intuitive to allow a plan node's caller to scribble on the plan node's result slot. An alternative solution would be to require execMain.c to keep an extra tuple table slot that has no other purpose than to temporarily hold replacement tuples during ExecInsert and ExecUpdate. I agree that execMain should play with its own memory. The rule that a node's result slot is valid until the next call is good because it solves the memory management issue. By allowing other people to scribble over your slot may cause issues w.r.t. knowing when you can safely free it. From a modularity point of view, nodes own their tupleslots and should be able to rely on them not changing... Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpVDJ3DozbGh.pgp Description: PGP signature
Re: [HACKERS] outer joins and for update
Gavin Sherry [EMAIL PROTECTED] writes: On Mon, 14 Nov 2005, Tom Lane wrote: The point of the comment really is that this is a predicate locking problem. I thought you might say that. I'm yet to do much reading on predicate locking -- do you think it is an area we will even pursue? Don't hold your breath ;-) ... AFAICS it's a hard problem and would have horrid repercussions for performance. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond
[EMAIL PROTECTED] (Gavin Sherry) writes: Hi, On Tue, 14 Nov 2005 [EMAIL PROTECTED] wrote: Gavin Sherry: Grouping sets Recursive queries The recursive queries is a long-awaited feature. Does the fact that the feature is listed for Gavin Sherry mean that Gavin is actually working with the feature at the moment? Does anybody know the current state of this feature or know when it will be public available? I recall suggesting these features as being amongst those in demand. I don't remember saying that I'd actually do them... Jonah Harris appears to be working on the Recursive Queries side of it... -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://www.ntlug.org/~cbbrowne/sap.html I visited a company that was doing programming in BASIC in Panama City and I asked them if they resented that the BASIC keywords were in English. The answer was: ``Do you resent that the keywords for control of actions in music are in Italian?'' -- Kent M Pitman ---(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
[HACKERS] Running PostGre on DVD
Hi everybody, My questions may seem kind of odd. I would like to run PostGreSQL on a DVD (database on the DVD and if possible executable on DVD too) on windows. I want no installation at all, so I took the no install package. The problem is the need of creating a non-admin user to run PostGre, I would like to know if there is an option to parameter PostGre to accept WILLINGLY that an administrator user can run it. If there isn't, it would be a great idea to add such a parameter. Secondly, I would like to run PostGre having only read permission on the data directory (which would be on the DVD...). Is it possible? If not, can it be added (add of a 'read-only' option). Thanks in advance for your help. Regards, Eric LEGUILLIER ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] MERGE vs REPLACE
On 11/13/05, Petr Jelinek wrote: I am really not db expert and I don't have copy of sql standard but you don't need to use 2 tables I think - USING part can also be subquery (some SELECT) and if I am right then you could simulate what REPLACE does because in PostgreSQL you are not forced to specify FROM clause in SELECT. So you could in theory do MERGE INTO tablename USING (SELECT 1 AS myid) ON (tablename.id = myid) ... I think the MySQL statement: REPLACE INTO table (pk, col1, col2, col3) VALUES (2, '-00-00', NULL, 3) would translate into the following MERGE statement: MERGE INTO table target USING (2 as pknew , NULL as col1new, NULL as col2new, 3 as col3new) source ON target.pknew = source.pk WHEN MATCHED THEN UPDATE SET col1 = col1new, col2 = col2new, col3 = col3new WHEN NOT MATCHED THEN INSERT (pk, col1, col2, col3) VALUES (pknew, col1new, col2new, col3new) It might not be the most elegant solution, but I don't see why it won't work. Jochem ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MERGE vs REPLACE
I think you translated it correctly, MySQL has another way of specifying this which is INSERT ... ON DUPLICATE KEY UPDATE ... (http://dev.mysql.com/doc/refman/5.0/en/insert.html) Regards Paolo Jochem van Dieten [EMAIL PROTECTED] ha scritto On 11/13/05, Petr Jelinek wrote: I am really not db expert and I don't have copy of sql standard but you don't need to use 2 tables I think - USING part can also be subquery (some SELECT) and if I am right then you could simulate what REPLACE does because in PostgreSQL you are not forced to specify FROM clause in SELECT. So you could in theory do MERGE INTO tablename USING (SELECT 1 AS myid) ON (tablename.id = myid) ... I think the MySQL statement: REPLACE INTO table (pk, col1, col2, col3) VALUES (2, '-00-00', NULL, 3) would translate into the following MERGE statement: MERGE INTO table target USING (2 as pknew , NULL as col1new, NULL as col2new, 3 as col3new) source ON target.pknew = source.pk WHEN MATCHED THEN UPDATE SET col1 = col1new, col2 = col2new, col3 = col3new WHEN NOT MATCHED THEN INSERT (pk, col1, col2, col3) VALUES (pknew, col1new, col2new, col3new) It might not be the most elegant solution, but I don't see why it won't work. Jochem ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
On Monday 14 November 2005 10:02, Tino Wildenhain wrote:
New in 8.1 it seems functions marked STABLE are
not allowed to have any INSERT statement in them.
Try hiding your inserts in seperate volitle sql function that you can select
inside your stable function. I think the planner won't be smart enough to
realize what your doing to it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
On 11/14/05, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: I just confirmed that there are duplicate p-keys in the source table :(. Well, that's not very good either, but at least it narrows down the problem. Do the duplicate rows appear to be independent insertions, or successive states of the same logical row? Looking at their xmin/xmax might help determine this, and if the table has OIDs then looking at the oid would be pretty conclusive. no oid. esp=# select xmin, xmax, lastmod from parts_order_line_file where prl_combined_key = esp-# ' 00136860' and prl_seq_no in (20, 23); xmin| xmax | lastmod ---+--+- 584527952 |0 | 2005-09-15 11:17:17.062 584527961 |0 | 2005-09-15 11:17:17.187 (2 rows) the nature of the file, unfortunately is that a record may be rewritten several times over it's lifespan, due to ISAM style resequencing on the table. the file is taken directly from user entry app so high speec race condition type behavior is unlikely, save for a order duplication aspect I need to check into. Being a ported COBOL app, tranasactions are one-record wonders, save for places where parts have been rewritten in pl/pgsql (does not apply here). Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
Merlin Moncure [EMAIL PROTECTED] writes: On 11/14/05, Tom Lane [EMAIL PROTECTED] wrote: Do the duplicate rows appear to be independent insertions, or successive states of the same logical row? esp=# select xmin, xmax, lastmod from parts_order_line_file where prl_combined_key = esp-# ' 00136860' and prl_seq_no in (20, 23); xmin| xmax | lastmod ---+--+- 584527952 |0 | 2005-09-15 11:17:17.062 584527961 |0 | 2005-09-15 11:17:17.187 (2 rows) I think you need to try this with enable_indexscan = 0; it should be showing us 4 rows according to your prior result, and it's only showing 2, which suggests that the indexscan is short-circuiting because it knows there can only be 1 result row. Also, since you're probing for more than one primary key value, please include the pkey columns in the query so we can tell what's what... regards, tom lane ---(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: [HACKERS] functions marked STABLE not allowed to do INSERT
Am Montag, den 14.11.2005, 13:29 -0500 schrieb Robert Treat: On Monday 14 November 2005 10:02, Tino Wildenhain wrote: New in 8.1 it seems functions marked STABLE are not allowed to have any INSERT statement in them. Try hiding your inserts in seperate volitle sql function that you can select inside your stable function. I think the planner won't be smart enough to realize what your doing to it. Now this is really a bug: =# CREATE OR REPLACE function foo(int) RETURNS int as $$ $# DECLARE f ALIAS FOR $1; $# BEGIN $# RETURN (random()*f)::int; $# END; $# $$ LANGUAGE plpgsql STABLE; =# SELECT foo(10); foo - 6 (1 row) Instead of screaming here, where I use a VOLATILE function in my STABLE function which could really be dangerous, it just works. And the other example, where I do my insert on purpose and fully knowing what I do gets refused. Is this a shortcoming of the function compiler? I dont think so - it retrieves the OID of used functions anyway so the lookup on stableness would be easy - and lets skip the silly scan for INSERT instead. Regards Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] How to find a number of connections
It would be better to ask this on -general, but SELECT count(*) FROM pg_stat_activity; is what you want. On Fri, Nov 11, 2005 at 12:35:28PM -0500, Brusser, Michael wrote: Is there a way to find a number of current connections on Postgres 7.3.x ? I looked at some system tables and views, but did not see anything obvious. Thank you, Mike -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
On 11/14/05, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: esp=# select xmin, xmax, lastmod from parts_order_line_file where prl_combined_key = esp-# ' 00136860' and prl_seq_no in (20, 23); xmin| xmax | lastmod ---+--+- 584527952 |0 | 2005-09-15 11:17:17.062 584527961 |0 | 2005-09-15 11:17:17.187 (2 rows) I think you need to try this with enable_indexscan = 0; it should be right, I missed that! esp=# select prl_combined_key, prl_seq_no, xmin, xmax, lastmod from parts_order_line_file where prl_combined_key = ' 00136860' and prl_seq_no in (20, 23); prl_combined_key | prl_seq_no | xmin| xmax | lastmod --++---+--+- 00136860| 20 | 584527952 |0 | 2005-09-15 11:17:17.062 00136860| 20 | 584412245 |0 | 2005-09-15 09:31:35.381 00136860| 23 | 584527961 |0 | 2005-09-15 11:17:17.187 00136860| 23 | 584415243 |0 | 2005-09-15 09:32:18.898 merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
On 11/14/05, Tom Lane [EMAIL PROTECTED] wrote: I think you need to try this with enable_indexscan = 0; it should be showing us 4 rows according to your prior result, and it's only showing one thing I forgot to mentionthere is a sequence on the table. Sequence is global for all tables hooked via default on a domain..highly unlikely records were inserted back to back. esp=# select id, prl_combined_key, prl_seq_no, xmin, xmax, lastmod from parts_order_line_file where prl_combined_key = ' 00136860' and prl_seq_no in (20, 23); id| prl_combined_key | prl_seq_no | xmin| xmax | lastmod --+--++---+--+- 15077227 | 00136860| 20 | 584412245 |0 | 2005-09-15 09:31:35.381 15077260 | 00136860| 23 | 584415243 |0 | 2005-09-15 09:32:18.898 15082475 | 00136860| 20 | 584527952 |0 | 2005-09-15 11:17:17.062 15082522 | 00136860| 23 | 584527961 |0 | 2005-09-15 11:17:17.187 (4 rows) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
Merlin Moncure [EMAIL PROTECTED] writes: esp=# select prl_combined_key, prl_seq_no, xmin, xmax, lastmod from parts_order_line_file where prl_combined_key = ' 00136860' and prl_seq_no in (20, 23); prl_combined_key | prl_seq_no | xmin| xmax | lastmod --++---+--+- 00136860| 20 | 584527952 |0 | 2005-09-15 11:17:17.062 00136860| 20 | 584412245 |0 | 2005-09-15 09:31:35.381 00136860| 23 | 584527961 |0 | 2005-09-15 11:17:17.187 00136860| 23 | 584415243 |0 | 2005-09-15 09:32:18.898 OK, so the fact that they all have xmax=0 proves that none are UPDATEd versions of others, which leaves us with the presumption that there was an outright failure of duplicate-key detection during INSERT :-( The pairing seems very odd though: judging from the proximity of xmin and lastmod, the first and third rows were inserted at almost the same time, and they do *not* have equal keys; the rows they should have conflicted with were inserted some time earlier. Can you think of anything special about the client-side logic that might have created unusual behavior in this situation? Not that it's not clearly a backend-side bug, I'm just looking for a handle to attack it with. (If you can determine the exact SQL commands that were issued to create these records, it'd be great.) One question that seems interesting is whether there might have been a VACUUM working on the pkey index concurrently with the later insertions. If you can either positively confirm or positively deny that, it'd be useful information. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
On 11/14/05, Tino Wildenhain [EMAIL PROTECTED] wrote: Am Montag, den 14.11.2005, 13:29 -0500 schrieb Robert Treat: On Monday 14 November 2005 10:02, Tino Wildenhain wrote: New in 8.1 it seems functions marked STABLE are not allowed to have any INSERT statement in them. Try hiding your inserts in seperate volitle sql function that you can select inside your stable function. I think the planner won't be smart enough to realize what your doing to it. Now this is really a bug: =# CREATE OR REPLACE function foo(int) RETURNS int as $$ $# DECLARE f ALIAS FOR $1; $# BEGIN $# RETURN (random()*f)::int; $# END; $# $$ LANGUAGE plpgsql STABLE; =# SELECT foo(10); foo - 6 (1 row) Instead of screaming here, where I use a VOLATILE function in my STABLE function which could really be dangerous, it just works. stable functions must show an stable image of the database, but if you start to do insertions, deletions and so how stable the image is? now, i don't like the behaviour of letting call volatile functions inside immutable/stable ones... but some people use it to do what they think is good... if you know you can call volatile functions from stable ones maybe you asked enough or read enough to actually know what you are doing... but if you simply put inserts in your stable functions and expect to work, maybe you are not reading enough... you can ask to yourself, am i reading enough to actually know what am i doing? conclusion: think in it as a netsafe for novices, if you think you are expert enough take the net off (calling the volatile functions) -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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: [HACKERS] functions marked STABLE not allowed to do INSERT
Am Montag, den 14.11.2005, 14:45 -0500 schrieb Jaime Casanova: On 11/14/05, Tino Wildenhain [EMAIL PROTECTED] wrote: Am Montag, den 14.11.2005, 13:29 -0500 schrieb Robert Treat: On Monday 14 November 2005 10:02, Tino Wildenhain wrote: New in 8.1 it seems functions marked STABLE are not allowed to have any INSERT statement in them. Try hiding your inserts in seperate volitle sql function that you can select inside your stable function. I think the planner won't be smart enough to realize what your doing to it. Now this is really a bug: =# CREATE OR REPLACE function foo(int) RETURNS int as $$ $# DECLARE f ALIAS FOR $1; $# BEGIN $# RETURN (random()*f)::int; $# END; $# $$ LANGUAGE plpgsql STABLE; =# SELECT foo(10); foo - 6 (1 row) Instead of screaming here, where I use a VOLATILE function in my STABLE function which could really be dangerous, it just works. stable functions must show an stable image of the database, but if you start to do insertions, deletions and so how stable the image is? No, the definiton is: STABLE indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements. And I'm not speaking of delete. My common usecase is lookup of key in surrogate-key table and generating one if not found. If it would break on DELETE I'd understand it, but it breaks on INSERT which isnt acceptable imho. now, i don't like the behaviour of letting call volatile functions inside immutable/stable ones... but some people use it to do what they think is good... Now, we are forcing people to not use INSERT in a STABLE function but we happily allow them to use VOLATILE functions where the real danger lives. Doesnt sound very logical to me. if you know you can call volatile functions from stable ones maybe you asked enough or read enough to actually know what you are doing... Thats the point. I know what I'm doing with my INSERT but am not allowed, but if I didnt know what I do and use a volatile function, I can happily do that. but if you simply put inserts in your stable functions and expect to work, maybe you are not reading enough... you can ask to yourself, am i reading enough to actually know what am i doing? Yes I do. conclusion: think in it as a netsafe for novices, if you think you are expert enough take the net off (calling the volatile functions) Yes sure, but since the change does not really prevent noobs from doing bad things [tm], it should be reverted or at least kept consequence - which would be to ban volatile funtions too. (IMHO only calling volatile functions should be banned) ---(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: [HACKERS] functions marked STABLE not allowed to do INSERT
Tino Wildenhain [EMAIL PROTECTED] writes: Now this is really a bug: That's in the eye of the beholder (and one who wasn't paying attention to previous discussion of this point, evidently). The reason why the no-data-change rule is now enforced, not only recommended, is that a stable/immutable function now actually would not see any changes it did make. Consider code like INSERT INTO foo VALUES (42, ...); SELECT * INTO rec FROM foo WHERE key = 42; IF NOT FOUND THEN RAISE EXCEPTION 'where did my row go?'; If this were allowed in stable/immutable functions, the RAISE would in fact be reached in 8.1, because the SELECT will be done with the snapshot of the query that called the function. This is a feature, not a bug, because it makes it possible to write a stable function that selects from the database and be sure that it really is stable in the face of concurrent changes. Calling a volatile function that itself makes some database changes isn't necessarily a wrong thing to do; the rule is just that the calling stable function isn't going to see those changes, just as the outer query won't (and never has). In a larger sense, maybe we ought to forbid stable/immutable functions calling volatiles, but it's not clear that there are no cases where it makes sense. As Robert notes, the lack of this check does provide an out for people who want to do what you want to do. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CONNECT BY PRIOR
On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote: I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Sounds interesting. What approach are you taking to the plan shape? The current approach would be to have additional plan nodes for each join. Coping with a dynamic number of operations will do interesting things in the planner. I face a similar dynamic problem with joins to partitioned tables. Do you have any thoughts about this area? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
ISTM that instead of comming up with clever ways to fool the parser it would be better to allow users to force a function to be marked as STABLE, etc., even though it's contents indicate that it shouldn't be. Since the standard IMMUTABLE | STABLE | VOLATILE is obviously a bad choice, I suggest adding [FORCE] as an option, so you could do FORCE STABLE. On Mon, Nov 14, 2005 at 08:55:03PM +0100, Tino Wildenhain wrote: stable functions must show an stable image of the database, but if you start to do insertions, deletions and so how stable the image is? No, the definiton is: STABLE indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements. And I'm not speaking of delete. My common usecase is lookup of key in surrogate-key table and generating one if not found. If it would break on DELETE I'd understand it, but it breaks on INSERT which isnt acceptable imho. now, i don't like the behaviour of letting call volatile functions inside immutable/stable ones... but some people use it to do what they think is good... Now, we are forcing people to not use INSERT in a STABLE function but we happily allow them to use VOLATILE functions where the real danger lives. Doesnt sound very logical to me. if you know you can call volatile functions from stable ones maybe you asked enough or read enough to actually know what you are doing... Thats the point. I know what I'm doing with my INSERT but am not allowed, but if I didnt know what I do and use a volatile function, I can happily do that. but if you simply put inserts in your stable functions and expect to work, maybe you are not reading enough... you can ask to yourself, am i reading enough to actually know what am i doing? Yes I do. conclusion: think in it as a netsafe for novices, if you think you are expert enough take the net off (calling the volatile functions) Yes sure, but since the change does not really prevent noobs from doing bad things [tm], it should be reverted or at least kept consequence - which would be to ban volatile funtions too. (IMHO only calling volatile functions should be banned) ---(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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
On Mon, 2005-11-14 at 14:51 -0500, Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: esp=# select prl_combined_key, prl_seq_no, xmin, xmax, lastmod from parts_order_line_file where prl_combined_key = ' 00136860' and prl_seq_no in (20, 23); prl_combined_key | prl_seq_no | xmin| xmax | lastmod --++---+--+- 00136860| 20 | 584527952 |0 | 2005-09-15 11:17:17.062 00136860| 20 | 584412245 |0 | 2005-09-15 09:31:35.381 00136860| 23 | 584527961 |0 | 2005-09-15 11:17:17.187 00136860| 23 | 584415243 |0 | 2005-09-15 09:32:18.898 OK, so the fact that they all have xmax=0 proves that none are UPDATEd versions of others, which leaves us with the presumption that there was an outright failure of duplicate-key detection during INSERT :-( Is there any chance it is related to the 8.0 problem I reported on Wednesday? http://groups.google.ca/group/pgsql.hackers/browse_frm/thread/a832e9919fb53f92/83e8f0bd272b8597?lnk=stq=%22possible+savepoint+bug%22rnum=1hl=en#83e8f0bd272b8597 -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
Rod Taylor [EMAIL PROTECTED] writes: Is there any chance it is related to the 8.0 problem I reported on Wednesday? Too soon to tell ... though one would like to think we don't have more than one bug in that area ;-). If either of you can come up with even a low-probability test case, it'd be a great help. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
See 'merge_db' in http://lnk.nu/postgresql.org/5sl.html On Fri, Nov 11, 2005 at 10:07:07PM -0500, Rod Taylor wrote: On Fri, 2005-11-11 at 18:36 -0500, [EMAIL PROTECTED] wrote: On Fri, Nov 11, 2005 at 06:00:32PM -0500, Rod Taylor wrote: So? That is what save points are for. You can even skip the select for update if you don't mind dead tuples from the attempted insert. SELECT ... FOR UPDATE; IF not exists THEN SAVEPOINT; INSERT ; IF UNIQUE VIOLATION THEN /* Someone else inserted between the SELECT and our INSERT */ ROLLBACK TO SAVEPOINT; UPDATE; ELSE RELEASE SAVEPOINT; FI ELSE UPDATE; FI Isn't there still a race between INSERT and UPDATE? I suppose there is although I hadn't noticed before. I've never run into it and always check to ensure the expected number of tuples were touched by the update or delete. Within the PostgreSQL backend you might get away with having your insert hold a lock on the index page and follow it up with a FOR UPDATE lock on the offending tuple thus ensuring that your update will succeed. If you hack index mechanisms for the support you don't need the SAVEPOINT either -- just don't throw an error when you run across the existing entry. For client side code one possibility is to repeat until successful. WHILE SELECT FOR UPDATE; IF NOT EXISTS THEN SAVEPOINT INSERT; IF UNIQUE VIOLATION THEN ROLLBACK TO SAVEPOINT; ELSE RELEASE SAVEPOINT EXIT; FI ELSE UPDATE; EXIT; END -- Check for infinite loop END -- ---(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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MERGE vs REPLACE
On Fri, Nov 11, 2005 at 03:42:38PM -0500, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: It seems to me that it has always been implicitly assumed around here that the MERGE command would be a substitute for a MySQL-like REPLACE functionality. After rereading the spec it seems that this is not the case. MERGE always operates on two different tables, which REPLACE doesn't do. Normally I'd plump for following the standard ... but AFAIR, we have had bucketloads of requests for REPLACE functionality, and not one request for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a whole lot harder and slower than REPLACE, it seems that we could do worse than to concentrate on doing REPLACE for now. (We can always come back to MERGE some other day.) I suspect a lot of those requests are from people who actually want merge and don't realize that mysql has a replace. On another note, is there any reason we can't put an equivalent to example 36-1 (http://lnk.nu/postgresql.org/617.html) into the backend? Presumably it wouldn't be as fast as a more elegant solution, but OTOH it'd probably be faster than plpgsql... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MERGE vs REPLACE
On Sun, 2005-11-13 at 23:56 +0100, Martijn van Oosterhout wrote: On Sun, Nov 13, 2005 at 11:32:47PM +0100, Petr Jelinek wrote: I am really not db expert and I don't have copy of sql standard but you don't need to use 2 tables I think - USING part can also be subquery (some SELECT) and if I am right then you could simulate what REPLACE does because in PostgreSQL you are not forced to specify FROM clause in SELECT. So you could in theory do MERGE INTO tablename USING (SELECT 1 AS myid) ON (tablename.id = myid) ... But I am not sure if this is what you want. Well, the obvious extension to this is that the extire USING clause is in fact optional: MERGE INTO tablename ON id = 1 ... Which starts looking a lot simpler. ...choosing a place to jump in is a little hard in this thread...so I'll jump in with some general info and thoughts on topics so far: MERGE is useful both for OLTP systems and for Data Warehousing, where it is sometimes known as the UPSERT. The MERGE statement in SQL:2003 requires a target table and a table statement. I don't see anything in that to always require two separate tables - this is just the same as a self-referencing INSERT SELECT statement. The USING clause is also a compulsory part of SQL:2003. One of the more interesting ways to use MERGE is with Oracle external tables. The same idea for us would be to have MERGE become a variant of the PostgreSQL COPY FROM command. That would be very cool. The above is the reason why MERGE doesn't seem to provide for external data being passed, as does INSERT or MySQL REPLACE. Neither DB2 or Oracle perform predicate locking. DB2 is more cautious, and some would say more efficient, thats all. PostgreSQL's locking features are just fine for pragmatic implementation of MERGE, AFAICS. Where there is doubt, we should fall back to table locking just like the rest of the world, IMHO. Making this work with partitioning will be hard enough without overusing the predicate solving logic. The UPSERT concept is also supported by Teradata, who simply append an ELSE INSERT clause onto the standard UPDATE syntax. MySQL REPLACE seems to me to be a fairly small subset of MERGE functionality and we ought to be able to offer that functionality as a side branch of the main work. I've been looking at ways of doing INSERT then UPDATE, but it doesn't seem very easy to avoid unique index violations in that case. So doing the UPDATE first then INSERTs later seems like the way to go. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
On 11/14/05, Tom Lane [EMAIL PROTECTED] wrote:
The pairing seems very odd though: judging from the proximity of xmin
and lastmod, the first and third rows were inserted at almost the same
time, and they do *not* have equal keys; the rows they should have
conflicted with were inserted some time earlier.
Can you think of anything special about the client-side logic that might
have created unusual behavior in this situation? Not that it's not
clearly a backend-side bug, I'm just looking for a handle to attack it
with. (If you can determine the exact SQL commands that were issued to
create these records, it'd be great.)
One question that seems interesting is whether there might have been a
VACUUM working on the pkey index concurrently with the later insertions.
If you can either positively confirm or positively deny that, it'd be
useful information.
VACUUM is scheuled to run weekly, so we can rul that out. Can rule
out exotic transaction behavor (savepoint) and long running
transactions generally, save for pg_dump. In my app, all I/O save for
rare pgadmin edit is with PQExecParams/PQExecPrepared through libpq.
Inserts and updates are currently not ever prepared. Following is an
example of the insert logged from the ISAM drver logging utility.
Transaction load on the table is moderate to heavy but is 10:1
read/write ratio at least.
This is of course running windows...reading Rod's mail it seems we
might have similar problem (running 8.0.2). The update would be
virtually the same and matching on the p-key.
0.0181023 sec: insert into parts_order_line_file (prl_combined_key,
prl_seq_no, prl_combined_key_2, prl_item_no, prl_comment_desc,
prl_location, prl_workstation, prl_stock_loc, prl_qty, prl_adj_price,
prl_cost, prl_weight, prl_uom, prl_vendor_no, prl_vendor_part_no,
prl_track_this_part, prl_warranty_period, prl_comments_1,
prl_comments_2, prl_qty_shipped, prl_qty_still_on_bo,
prl_qty_credited, prl_credit_reason, prl_credit_reason_type,
prl_cancel_ship, prl_exchange_part, prl_authorization_code,
prl_item_status, prl_item_status_alpha, prl_cancel_flag,
prl_charge_type_flag, prl_ct_taxable_flag, prl_account_cat_code,
prl_retail_price, prl_line_needs_serials, prl_chrg_type_ship_indx,
prl_claim_type_flag, prl_attached_wc_seq_no, prl_attached_claim_type,
prl_already_issued, prl_returned_part_flag, prl_prev_qty_shipped,
prl_prev_qty_still_on_bo, prl_prev_qty_credited) values ($1, $2, $3,
$4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,
$19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32,
$33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44) params:
$1= 1126 $2=001 $3= 1126 $4=BAR-06 $5=BAR $6=PL-1 $7= $8=
$9= 0001.000 $10= 16.50 $11= 15.0 $12=00050.00 $13=EA
$14=06 $15=BAR-06 $16=N $17= $18= $19= $20={ 0001.00,
.00, .00, .00, .00, .00, .00,
.00, .00} $21={ .00, .00, .00,
.00, .00, .00, .00, .00, .00}
$22={ .00, .00, .00, .00, .00,
.00, .00, .00, .00}
$23={} $24={}
$25={} $26=N $27= $28=2 $29=A $30= $31= $32=
$33= $34= 19.80 $35= $36=0 $37= $38=000 $39= $40= $41= $42={
.00, .00, .00, .00, .00, .00,
.00, .00, .00} $43={ .00, .00,
.00, .00, .00, .00, .00, .00,
.00} $44={ .00, .00, .00, .00,
.00, .00, .00, .00, .00}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
Am Montag, den 14.11.2005, 15:06 -0500 schrieb Tom Lane: Tino Wildenhain [EMAIL PROTECTED] writes: Now this is really a bug: That's in the eye of the beholder (and one who wasn't paying attention to previous discussion of this point, evidently). Yes I was, but only to the fact it is not useable for caching and there are some cases (like random) for which STABLE would be bad thing [tm]. The reason why the no-data-change rule is now enforced, not only recommended, is that a stable/immutable function now actually would not see any changes it did make. Consider code like INSERT INTO foo VALUES (42, ...); SELECT * INTO rec FROM foo WHERE key = 42; IF NOT FOUND THEN RAISE EXCEPTION 'where did my row go?'; If this were allowed in stable/immutable functions, the RAISE would in fact be reached in 8.1, because the SELECT will be done with the snapshot of the query that called the function. This is a feature, Ah this was the missing bit. I though this would only be true for IMMUTABLE. Thanks for the explanation. I'm not fine w/ it. Regards Tino ---(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: [HACKERS] functions marked STABLE not allowed to do INSERT
The previous discussion/complaints really revolved around how volatility
effected the planner. There are some scenarios (most revolving around a
surrogate key lookup type scenario) where 99% of function calls do not
generate DML changes and because of that we need the planner to treat these
functions as stable functions rather than volatile functions (and we're aware
of the tradeoffs of the other 1% case, but willing to take the hit). At the
time the check was instituted inside plpgsql, istr some of us saying that we
needed a 4th volatility that meant treat my as stable for purposes of the
planner, but treat me as volatile for other purposes but the proposals never
gathered much steam.
Robert Treat
On Monday 14 November 2005 15:09, Jim C. Nasby wrote:
ISTM that instead of comming up with clever ways to fool the parser it
would be better to allow users to force a function to be marked as
STABLE, etc., even though it's contents indicate that it shouldn't be.
Since the standard IMMUTABLE | STABLE | VOLATILE is obviously a bad
choice, I suggest adding [FORCE] as an option, so you could do FORCE
STABLE.
On Mon, Nov 14, 2005 at 08:55:03PM +0100, Tino Wildenhain wrote:
stable functions must show an stable image of the database, but if you
start to do insertions, deletions and so how stable the image is?
No, the definiton is:
STABLE indicates that within a single table scan the function will
consistently return the same result for the same argument values, but
that its result could change across SQL statements.
And I'm not speaking of delete. My common usecase is
lookup of key in surrogate-key table and generating
one if not found. If it would break on DELETE
I'd understand it, but it breaks on INSERT which isnt
acceptable imho.
now, i don't like the behaviour of letting call volatile functions
inside immutable/stable ones... but some people use it to do what they
think is good...
Now, we are forcing people to not use INSERT in a STABLE
function but we happily allow them to use VOLATILE
functions where the real danger lives. Doesnt sound
very logical to me.
if you know you can call volatile functions from stable ones maybe you
asked enough or read enough to actually know what you are doing...
Thats the point. I know what I'm doing with my INSERT
but am not allowed, but if I didnt know what I do and
use a volatile function, I can happily do that.
but if you simply put inserts in your stable functions and expect to
work, maybe you are not reading enough... you can ask to yourself, am
i reading enough to actually know what am i doing?
Yes I do.
conclusion: think in it as a netsafe for novices, if you think you are
expert enough take the net off (calling the volatile functions)
Yes sure, but since the change does not really prevent noobs
from doing bad things [tm], it should be reverted or at least
kept consequence - which would be to ban volatile
funtions too.
(IMHO only calling volatile functions should be banned)
---(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
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Translation typo fix
Devrim GUNDUZ wrote: On Mon, 14 Nov 2005, Alvaro Herrera wrote: Log Message: --- Translation typo fix Shouldn't they go to the translation project @Â pgfoundry ? Good question. Peter, is pgtranslation supposed to be the primary source of translations? Or is it an optional mechanism designed to ease the interactions with outside translators? Finding typos is extremely annoying to me and I want to fix them right away. If I have to commit the fix to pgtranslation, and commit from there to the main CVS, it's going to impose an extra cost on me. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Running PostGre on DVD
Why do you need to run PostgreSQL as admin? There shouldn't be any need for this. Someone has done a PostgreSQL demo CD, I believe based on Knoppix. The list archives will probably have more info. On Mon, Nov 14, 2005 at 11:29:10AM +0100, [EMAIL PROTECTED] wrote: Hi everybody, My questions may seem kind of odd. I would like to run PostGreSQL on a DVD (database on the DVD and if possible executable on DVD too) on windows. I want no installation at all, so I took the no install package. The problem is the need of creating a non-admin user to run PostGre, I would like to know if there is an option to parameter PostGre to accept WILLINGLY that an administrator user can run it. If there isn't, it would be a great idea to add such a parameter. Secondly, I would like to run PostGre having only read permission on the data directory (which would be on the DVD...). Is it possible? If not, can it be added (add of a 'read-only' option). Thanks in advance for your help. Regards, Eric LEGUILLIER ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
Robert Treat [EMAIL PROTECTED] writes: The previous discussion/complaints really revolved around how volatility effected the planner. There are some scenarios (most revolving around a surrogate key lookup type scenario) where 99% of function calls do not generate DML changes and because of that we need the planner to treat these functions as stable functions rather than volatile functions (and we're aware of the tradeoffs of the other 1% case, but willing to take the hit). At the time the check was instituted inside plpgsql, istr some of us saying that we needed a 4th volatility that meant treat my as stable for purposes of the planner, but treat me as volatile for other purposes but the proposals never gathered much steam. Probably because you never provided a convincing use-case. As far as the planner is concerned, the only real differences between stable and volatile functions are: 1. A stable function is safe to use in an indexscan qualification (which implies it will be evaluated only once per scan, not once per row, but *only* if the relevant index actually gets used). 2. Stable functions are OK to evaluate speculatively when trying to estimate WHERE-clause selectivities. It's tough to believe that a function with side-effects is reasonable to use in either of those ways (and no, it only changes the database 1% of the time doesn't make it more reasonable). In fact, I'd go so far as to say that you're a fool if you use a function with side-effects in a WHERE clause, ever --- but doubly so if you then want to claim to the planner that it hasn't got any side-effects. Now, the current discussion about stable functions really has to do with semantics of SQL-command evaluation within the function itself, which is only weakly related to what the planner thinks about it. So it's not a-prior impossible that we've overloaded the meaning of stable too much and should split the concepts somehow. But it's not clear to me why or how, which is why I'm wanting a plausible use-case. regards, tom lane ---(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: [HACKERS] outer joins and for update
On Tue, Nov 15, 2005 at 02:22:15AM +1100, Gavin Sherry wrote:
On Mon, 14 Nov 2005, Tom Lane wrote:
is that you have exclusive hold on the selected rows and they won't
change underneath you before the end of your transaction. In the case
of an outer join where the left-side row joined to nothing on the
right-side, we can't guarantee that: repeating the SELECT might find a
matching right-side row, thereby changing the allegedly-locked join row.
To guarantee a stable view of the data, we'd need a predicate lock that
prevents a matching right-side row from being inserted.
Well we can guarantee that we wont see rows added by concurrent
transactions if we're in serializable isolation level :-).
Do we really need to prevent inserts from happening under a SELECT FOR
UPDATE? ISTM that's trying to apply serializable concurrency to SELECT
FOR UPDATE even if it's running in a read committed transaction. In the
single table case we don't prevent someone from inserting a value...
# session 1
decibel=# insert into t values('1');
INSERT 633175 1
# session 2
decibel=# begin;
BEGIN
decibel=# select * from t where t='1' for update;
t
---
1
(1 row)
# session 1
decibel=# insert into t values('1');
INSERT 633176 1
decibel=# select * from t;
t
---
1
1
(2 rows)
decibel=# update t set t='2';
# Blocks on session 2
Am I missing something here?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(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: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
[EMAIL PROTECTED] wrote: Gavin Sherry: Grouping sets Recursive queries The recursive queries is a long-awaited feature. Does the fact that the feature is listed for Gavin Sherry mean that Gavin is actually working with the feature at the moment? Does anybody know the current state of this feature or know when it will be public available? No, it just means he has worked on it in the past. However, I no longer see his name on the item in the current TODO. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
Would the PG Dev group be working on update-able views for 8.2 ? I know that there is a work-around using rules, the SAMS book does claim that 8.0 has readonly views. I don't think that this has changed in 8.1 no ? Cheers, Aly. Bruce Momjian wrote: [EMAIL PROTECTED] wrote: Gavin Sherry: Grouping sets Recursive queries The recursive queries is a long-awaited feature. Does the fact that the feature is listed for Gavin Sherry mean that Gavin is actually working with the feature at the moment? Does anybody know the current state of this feature or know when it will be public available? No, it just means he has worked on it in the past. However, I no longer see his name on the item in the current TODO. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
Hi all, karen hill wrote: What do you see for 8.2 and beyond? What type of features are you devs planning for 9.0? It would be good if you could put up a place on your site so we mortals can drool over up-coming postgresql features. I'm wishing - more audit facilities - pluggable/loadable storage manager (and bufmgr?) - in-memory table How do you think? -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(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
[HACKERS] Long-time 7.4 contrib failure Mac OS X 10.3.8
The 7.4 tree has never built cleanly on Wallaroo, a Mac OS X 10.3.8 member of the build farm. Currently it's failing in the make contrib stage. I'd like to get it to build properly, but I don't know enough to be able to make sense of the log output. I'd be grateful if someone could spare a few minutes to take a look at the log and give me an idea of what it would take to fix it. http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=wallaroodt=2005-11-10% 2021:15:00 It looks to me like there's a linking error with GIST. Does this mean it's looking for libraries in the wrong place? Perhaps the libraries it's looking for aren't installed on this box? Perhaps it's a configure problem? Thanks for any insight! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] outer joins and for update
Jim C. Nasby [EMAIL PROTECTED] writes: Do we really need to prevent inserts from happening under a SELECT FOR UPDATE? ISTM that's trying to apply serializable concurrency to SELECT FOR UPDATE even if it's running in a read committed transaction. In the single table case we don't prevent someone from inserting a value... You're missing the point entirely, Jim. In the first place, SELECT FOR UPDATE has little or nothing to do with serializable mode: it's guaranteed to lock and return the latest committed version of the row. In the second case, inserting additional tuples does not invalidate your lock on the tuples you selected to begin with. SELECT FOR UPDATE doesn't try to guarantee that if you were to select again with the same WHERE condition, there might not be more rows matching the same condition. It does try to guarantee that the rows you selected before are still there and unchanged. In the case being discussed here, you're trying to lock rows of an outer-join. IMHO, if that means anything at all, it means that if you read those rows again they will still look the same. Having the righthand side go from NULL to not-NULL does not qualify as looking the same in my book. Perhaps this could be clarified if someone has an actual use case of wanting to SELECT FOR UPDATE from an outer join, and can explain what semantics they think they need for that. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Fixes for 8.1 run of pgindent
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: My guess is that there is a one-off bug in there. At least a two-off ... but I think it's more likely some sort of wrong-state error, given the narrow places where it happens. I have not observed any non-comment code being mis-justified, for instance. OK, I have spent some unpleasant time tracking down the pgindent problems from 8.1. Some were my fault, and some the BSD indent code. First, my fault was not updating the typedefs for both /bin and /lib --- I did only /bin. This was documented, but not clearly. I have improved the docs on this. Second, Tom found that when we took the margin to 79, BSD indent had a bug that the first line after a blank comment line could go to 80 or 81 columns. I tracked down this bug and applied a fix to my version, the patch in our CVS, and the indent tarball on our ftp server. Third, I found that if more then 150 'else if' are used in the same statement, the comments are shifted to start on column 100. I have found the cause for this (stack of 150 hardcoded) and fixed that too everywhere. I have rerun pgindent on psql/tab-complete.c and committed it to CVS for both branches. I have tested the new pgindent on our existing CVS and the only changes are for comments to fix the bad wrapping, and to fix the missing /lib typedefs. I think we should rerun pgindent on 8.1.X and HEAD to correct the reported problems. I am betting 90% of our patches either come from CVS head or 8.1.X branches greater than 8.1.0. I have on my TODO list to test GNU indent again during 8.2 to see how it does. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Fixes for 8.1 run of pgindent
Bruce Momjian pgman@candle.pha.pa.us writes: I think we should rerun pgindent on 8.1.X and HEAD to correct the reported problems. I am betting 90% of our patches either come from CVS head or 8.1.X branches greater than 8.1.0. Can you post a diff showing what would change exactly? I'd like to hold off for at least a little bit on reindenting HEAD, because I've got a fair size set of changes for nulls-in-arrays that I'm still a day or two away from committing. Reindenting right now is likely to cause a rather painful merge problem because I updated a lot of comments. I'm not sure who else has major patches in progress (anyone out there?), but I may not be the only one with a problem. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Fixes for 8.1 run of pgindent
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I think we should rerun pgindent on 8.1.X and HEAD to correct the reported problems. I am betting 90% of our patches either come from CVS head or 8.1.X branches greater than 8.1.0. Can you post a diff showing what would change exactly? I'd like to hold off for at least a little bit on reindenting HEAD, because I've got a fair size set of changes for nulls-in-arrays that I'm still a day or two away from committing. Reindenting right now is likely to cause a rather painful merge problem because I updated a lot of comments. I'm not sure who else has major patches in progress (anyone out there?), but I may not be the only one with a problem. I have two moderate-size patches that I'm planning to submit shortly. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
On Monday 14 November 2005 18:36, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
The previous discussion/complaints really revolved around how volatility
effected the planner. There are some scenarios (most revolving around a
surrogate key lookup type scenario) where 99% of function calls do not
generate DML changes and because of that we need the planner to treat
these functions as stable functions rather than volatile functions (and
we're aware of the tradeoffs of the other 1% case, but willing to take
the hit). At the time the check was instituted inside plpgsql, istr some
of us saying that we needed a 4th volatility that meant treat my as
stable for purposes of the planner, but treat me as volatile for other
purposes but the proposals never gathered much steam.
Probably because you never provided a convincing use-case.
It's hard to be convincing when you start out thinking the other side to be
fools.
As far as the planner is concerned, the only real differences between
stable and volatile functions are:
1. A stable function is safe to use in an indexscan qualification
(which implies it will be evaluated only once per scan, not once
per row, but *only* if the relevant index actually gets used).
2. Stable functions are OK to evaluate speculatively when trying to
estimate WHERE-clause selectivities.
It's tough to believe that a function with side-effects is reasonable to
use in either of those ways (and no, it only changes the database 1% of
the time doesn't make it more reasonable). In fact, I'd go so far as
to say that you're a fool if you use a function with side-effects in a
WHERE clause, ever --- but doubly so if you then want to claim to the
planner that it hasn't got any side-effects.
The basic scenario is one of a function that, given input, looks up
corresponding information in a cache table. If it can't find the
information, it goes through a more complicated (and slower) search to obtain
the information, inserts that information into the cache, and returns the
result. Note it always returns the same result whether the cache contains
the information or not, which means you really do only need to evaluate it
once per scan. The problem is that when you mark such functions as volatile
the performance you get is horrendous, so you're forced to mark them as
stable so the planner will make use of index scans and such and give decent
performance. Now maybe that's not a convincing use-case, but it is a common
one.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
On Mon, Nov 14, 2005 at 08:31:50PM -0500, Robert Treat wrote: The basic scenario is one of a function that, given input, looks up corresponding information in a cache table. If it can't find the information, it goes through a more complicated (and slower) search to obtain the information, inserts that information into the cache, and returns the result. Note it always returns the same result whether the cache contains the information or not, which means you really do only need to evaluate it once per scan. The problem is that when you mark such functions as volatile the performance you get is horrendous, so you're forced to mark them as stable so the planner will make use of index scans and such and give decent performance. Now maybe that's not a convincing use-case, but it is a common one. Isn't this the sort of case that Tom just explained as not functioning in 8.1, as the STABLE functions, and all functions called by the STABLE functions will use the snapshot that is used at the time it was called? As in, you do the INSERT, but within the same SELECT statement invoking this 'STABLE' function, it never sees the inserted cached value? Also - what does it do with parallel inserts of the same cache values? Three or four clients all require the data at the same time - they execute the cache table lookup, to fail to find a row, they then all resolve the query the slow way, and each try to insert a cache row. The case seems problematic to me. Isn't it better served by a caching daemon, such as memcached? It has similar problems - not transaction safe, and so on, but I would suspect that this caching table that you describe above cannot ever be truly transaction safe, unless you store full row dependencies for each of the cache records, and validate against the dependencies before returning any data. Who is to say the cache data is up-to-date? Invalidation of the cache data rows may not solve this either. I'd say why bother? Personally, I'm more in favour of PostgreSQL doing cheap caching of query to results, making those very common slow queries you mention faster where possible. For example, keeping the query results in a LRU cache, with an identifier that would allow it to quickly determine if all dependent tables have changed or not, allowing it to return the results as is, if all of the tables are unchanged since the last execution. To make it faster, and to minimize caching of less frequent queries, perhaps the first few times a query is executed, it should only remember the number of times it has been executed, and only after some threshhold has passed, start to cache the results, and the dependency information. If a query rarely keeps the same dependency information, keep it in a cache of queries to never cache results or dependency information for? I'm sure this has been talked about at length, before I joined this mailing list. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Long-time 7.4 contrib failure Mac OS X 10.3.8
Michael Glaesemann [EMAIL PROTECTED] writes: The 7.4 tree has never built cleanly on Wallaroo, a Mac OS X 10.3.8 member of the build farm. Currently it's failing in the make contrib stage. I'm afraid 7.4 will probably never build completely cleanly on OS X. The failure you're showing is related to the dblink - libpq - libssl dependency chain. It works OK on PG 8.0 and later, and AFAICT the only relevant difference is that 8.0 builds libpq as a dynamiclib (.dylib) instead of a bundle (.so). Apparently the linker follows libpq's dependency on libssl in one case but not the other. Unfortunately, changing this in 7.4 would be an ABI breakage and so is not likely to happen. I believe you could get 7.4 contrib to build if you take out --with-openssl in that branch's configure arguments. You may find you have to back off on --with-python and/or --with-tcl as well :-( I don't recall when we first got those PLs to work on OS X, but it wasn't all that long ago. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
Aly Dharshi wrote: Would the PG Dev group be working on update-able views for 8.2 ? I know that there is a work-around using rules, the SAMS book does claim that 8.0 has readonly views. I don't think that this has changed in 8.1 no ? We don't know what will be in 8.2 until a volunteer does the work. --- Cheers, Aly. Bruce Momjian wrote: [EMAIL PROTECTED] wrote: Gavin Sherry: Grouping sets Recursive queries The recursive queries is a long-awaited feature. Does the fact that the feature is listed for Gavin Sherry mean that Gavin is actually working with the feature at the moment? Does anybody know the current state of this feature or know when it will be public available? No, it just means he has worked on it in the past. However, I no longer see his name on the item in the current TODO. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Long-time 7.4 contrib failure Mac OS X 10.3.8
On Nov 15, 2005, at 11:34 , Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: The 7.4 tree has never built cleanly on Wallaroo, a Mac OS X 10.3.8 member of the build farm. Currently it's failing in the make contrib stage. snip / I believe you could get 7.4 contrib to build if you take out --with-openssl in that branch's configure arguments. You may find you have to back off on --with-python and/or --with-tcl as well :-( I don't recall when we first got those PLs to work on OS X, but it wasn't all that long ago. Thanks for the explanation. Is making this change to the build farm machine's config worth doing? Is it more useful on the build farm to see what works, or to see what fails? I'm thinking the latter. It'd be nice to see green, but green for green's sake isn't the point. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
On Monday 14 November 2005 20:59, [EMAIL PROTECTED] wrote:
On Mon, Nov 14, 2005 at 08:31:50PM -0500, Robert Treat wrote:
The basic scenario is one of a function that, given input, looks up
corresponding information in a cache table. If it can't find the
information, it goes through a more complicated (and slower) search
to obtain the information, inserts that information into the cache,
and returns the result. Note it always returns the same result
whether the cache contains the information or not, which means you
really do only need to evaluate it once per scan. The problem is
that when you mark such functions as volatile the performance you
get is horrendous, so you're forced to mark them as stable so the
planner will make use of index scans and such and give decent
performance. Now maybe that's not a convincing use-case, but it is a
common one.
Isn't this the sort of case that Tom just explained as not functioning
in 8.1, as the STABLE functions, and all functions called by the
STABLE functions will use the snapshot that is used at the time it
was called? As in, you do the INSERT, but within the same SELECT
statement invoking this 'STABLE' function, it never sees the inserted
cached value?
That's the whole point, it doesn't need to see the cached value as it has
already done the look-up the expensive way. But all subsequent queries will
get the value from the cache table, thereby avoiding the expensive query.
Also - what does it do with parallel inserts of the same cache values?
Three or four clients all require the data at the same time - they execute
the cache table lookup, to fail to find a row, they then all resolve the
query the slow way, and each try to insert a cache row.
The case seems problematic to me. Isn't it better served by a caching
daemon, such as memcached? It has similar problems - not transaction
safe, and so on, but I would suspect that this caching table that you
describe above cannot ever be truly transaction safe, unless you store
full row dependencies for each of the cache records, and validate
against the dependencies before returning any data. Who is to say the
cache data is up-to-date? Invalidation of the cache data rows may not
solve this either.
These are all business logic decsions and as such would be implementation
dependent. Generally the idea is that once the expensive query is done, it's
value is unlikely to change. If this were something that would change a lot
then it wouldn't exactly be non-volatle would it?
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Interval aggregate regression failure (expected seems
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Would you mind reporting this to RedHat Bugzilla? I believe a bug report from you would have more weight then mine, because you actually understand what's going on here. :-) Actually, given the thought that this may be an artifact of keeping an intermediate value in a wider-than-normal register rather than genuinely rearranging the computation, I'm not certain it is a compiler bug. We'd have to study it a lot more closely before filing it as one, anyway. If you accept the idea that the pentium4 answer is the right one, then what we really need to do is focus on a better rounding rule than strict truncation. I was toying with the notion of adding the equivalent of half a microsecond to the fractional-day value before truncating it to integer. But I'm not certain that that wouldn't have some bad effects in other cases. Looking at the code, do we need additional rint() calls in there, or rint(x + 0.5)? Frankly, I am confused why interval_div() has caused such problems for us? Are we going at this the right way? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [HACKERS] Long-time 7.4 contrib failure Mac OS X 10.3.8
Michael Glaesemann [EMAIL PROTECTED] writes: Thanks for the explanation. Is making this change to the build farm machine's config worth doing? Is it more useful on the build farm to see what works, or to see what fails? I'm thinking the latter. It'd be nice to see green, but green for green's sake isn't the point. True, but if we're writing off openssl-on-OSX-on-7.4 as never will happen, as I think we must, it's probably better to configure the buildfarm to avoid the problem. Otherwise the failure may simply mask breakages that we *would* like to know about. In particular, we presently have no idea whether wallaroo would expose any other contrib problems if it were able to get past building dblink; and the fact that it's red and always has been red discourages people from noticing if the failure changes to something else. In general, I like build machines that usually are green. I've learned to totally tune out failures from tuna and carp, for instance, because they fail every couple of days due to local problems such as lack of SHMMAX space. If they ever show a real problem, it's unlikely anyone would notice for a long while --- ye old cry wolf too much problem. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] functions marked STABLE not allowed to do INSERT
On Mon, Nov 14, 2005 at 10:02:32PM -0500, Robert Treat wrote: Isn't this the sort of case that Tom just explained as not functioning in 8.1, as the STABLE functions, and all functions called by the STABLE functions will use the snapshot that is used at the time it was called? As in, you do the INSERT, but within the same SELECT statement invoking this 'STABLE' function, it never sees the inserted cached value? That's the whole point, it doesn't need to see the cached value as it has already done the look-up the expensive way. But all subsequent queries will get the value from the cache table, thereby avoiding the expensive query. Ok. I think I get it. But -- isn't the STABLE definition itself enough to benefit the same query, without INSERT, assuming appropriate optimization of STABLE? The INSERT is only for caching across multiple statements, then, correct? Or is it to get around a deficiency in the implementation of STABLE? [ application side caching? ] These are all business logic decsions and as such would be implementation dependent. Generally the idea is that once the expensive query is done, it's value is unlikely to change. If this were something that would change a lot then it wouldn't exactly be non-volatle would it? I think that's the point. Whether the data changes or not in the table, isn't restricted by the definition of the functions that access the data. I believe I see your argument, and given a suitable definition of STABLE (such as only table snapshots being used for the STABLE function, and all functions invoked by the STABLE function), I can see INSERT being safe (although perhaps difficult to understand). I predict wierd scenarios, including a VOLATILE function that normally expects to be able to update a table, and view the updates immediately, failing in unexpected ways when called from a STABLE function. Yuck. It really sounds like something is wrong. Or missing. I'm scared of it. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] Major Problem, need help! Can't run our website!
ITS ONT Alcazar, Jose Aguedo C wrote: Anyone! Before anything else, I have no background in PostgreSQL. But I have a little knowledge in Linux. We used postgreSQL to run one of our website. It runs in Redhat Linux 7.3. Our System Administrator, who used to maintain this server, had resigned and didn't have a proper documentation on how to maintain this server. Right now, our NEW System Administrator is clearing some logs in /var/lib/pgsql/data/pg_xlog in able to free some space in the /var file system. It used to work before, but now, its not working anymore. Information below is the message we are encountering when we are trying to connect to the website. Please, ANYONE, help us! We've seen reports of people firing this particular foot-gun before, haven't we? Would it make sense to rename pg_xlog to something that doesn't sound like it's just full of log files? Eg pg_wal - something where the half-educated will have no idea what it is, and therefore not think they know what they can do with it. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] Major Problem, need help! Can't run our website!
Tim Allen [EMAIL PROTECTED] writes: We've seen reports of people firing this particular foot-gun before, haven't we? Would it make sense to rename pg_xlog to something that doesn't sound like it's just full of log files? Eg pg_wal - something where the half-educated will have no idea what it is, and therefore not think they know what they can do with it. There's something in what you say. We'd have to rename pg_clog as well, since that's even more critical than pg_xlog ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [ADMIN] Major Problem, need help! Can't run our website!
We've seen reports of people firing this particular foot-gun before, haven't we? Would it make sense to rename pg_xlog to something that doesn't sound like it's just full of log files? Eg pg_wal - something where the half-educated will have no idea what it is, and therefore not think they know what they can do with it. Would it be wise or insane for us to to mention in the startup error a HINT that if you've removed such files, only hope is full restore from backup or pg_resetxlog with data loss? Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [ADMIN] Major Problem, need help! Can't run our website!
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Would it be wise or insane for us to to mention in the startup error a HINT that if you've removed such files, only hope is full restore from backup or pg_resetxlog with data loss? Not sure that we should have a HINT recommending a worst-case-scenario course of action as the first resort. We'll have people blowing away their data for what might be relatively fixable problems (eg, bogus permissions on the pg_xlog directory, which I think was an issue that just came up a day or two ago ...) (We're all really jumping to conclusions here anyway. The guy may have been foolish to remove xlog files, but that doesn't explain why his postmaster isn't running. There's some facts missing in that report.) regards, tom lane ---(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: [HACKERS] [ADMIN] Major Problem, need help! Can't run our
On Mon, 2005-11-14 at 23:02 -0500, Tom Lane wrote: Tim Allen [EMAIL PROTECTED] writes: We've seen reports of people firing this particular foot-gun before, haven't we? Would it make sense to rename pg_xlog to something that doesn't sound like it's just full of log files? Eg pg_wal - something where the half-educated will have no idea what it is, and therefore not think they know what they can do with it. There's something in what you say. We'd have to rename pg_clog as well, since that's even more critical than pg_xlog ... Rename them to pg_donttouchthis and pg_youneedthis. -- ---(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: [HACKERS] [ADMIN] Major Problem, need help! Can't run our
Rod Taylor [EMAIL PROTECTED] writes: On Mon, 2005-11-14 at 23:02 -0500, Tom Lane wrote: There's something in what you say. We'd have to rename pg_clog as well, since that's even more critical than pg_xlog ... Rename them to pg_donttouchthis and pg_youneedthis. :-) On a more serious level: Tim's suggestion of pg_wal for pg_xlog sounds fine to me. How about pg_trans for pg_clog, by analogy to the existing pg_subtrans? Nothing else in the standard layout looks like it's got a name that a newbie would think means discardable data. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
On Mon, Nov 14, 2005 at 17:10:58 -0700, Aly Dharshi [EMAIL PROTECTED] wrote: Would the PG Dev group be working on update-able views for 8.2 ? I know that there is a work-around using rules, the SAMS book does claim that 8.0 has readonly views. I don't think that this has changed in 8.1 no ? It's not really a work around. Updateable views will likely be built on top of rules and just provide a more convenient way to do things for the cases it is clear what updating means. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] Major Problem, need help! Can't run our
I agree. (sorry again Tom... dang GMAIL should default reply to all g!) On 11/14/05, Tom Lane [EMAIL PROTECTED] wrote: Rod Taylor [EMAIL PROTECTED] writes: On Mon, 2005-11-14 at 23:02 -0500, Tom Lane wrote: There's something in what you say.We'd have to rename pg_clog as well, since that's even more critical than pg_xlog ... Rename them to pg_donttouchthis and pg_youneedthis.:-) On a more serious level: Tim's suggestion of pg_wal for pg_xlog soundsfine to me.How about pg_trans for pg_clog, by analogy to theexisting pg_subtrans?Nothing else in the standard layout looks like it's got a name that a newbie would think means discardable data. regards, tom lane---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CONNECT BY PRIOR
Hey Simon, I'm doing some research into recursive query planning in terms of theory as-well-as actual implementation in other RDBMS. Let me get back to you when I have some more definitive info. On 11/14/05, Simon Riggs [EMAIL PROTECTED] wrote: On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote: I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2.Sounds interesting.What approach are you taking to the plan shape? The current approachwould be to have additional plan nodes for each join. Coping with adynamic number of operations will do interesting things in the planner. I face a similar dynamic problem with joins to partitioned tables.Do you have any thoughts about this area?Best Regards, Simon Riggs
Re: [HACKERS] Long-time 7.4 contrib failure Mac OS X 10.3.8
On Nov 15, 2005, at 12:20 , Tom Lane wrote: In particular, we presently have no idea whether wallaroo would expose any other contrib problems if it were able to get past building dblink; and the fact that it's red and always has been red discourages people from noticing if the failure changes to something else. Makes sense. I've stripped --with-openssl, and --with-tcl and --with- python for good measure. It still fails, and to my eye, with the same failure. http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=wallaroodt=2005-11-15% 2006:29:06 Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ANNOUNCE] PostgreSQL Weekly News - November 13 2005
David Fetter writes in PostgreSQL Weekly News - November 13 2005: Teodor Sigaev has been making lots of improvements to tsearch2, a full-text search engine. I can't find them. Am I blind? Can someone help? ---(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
