Re: [GENERAL] [ADMIN] [HACKERS] retrieve statement from catalogs
you could put a view on every table that called a function? Maybe, but how can i retrieve the select statement _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [ADMIN] [HACKERS] retrieve statement from catalogs
Hi everybody, can anyone tell me if there's a way to retrieve the select instruction executed from the catalogs, or maybe via some structure in a trigger? The reason is that i have some selects constructed on-the-fly (just part of it) and i want to save that in a table in order to know what are the most used for optimizing them. Another reason for doing that is that i don't know all the selects in the program nor the page and this is an easier solution than examine all the code. thanx in advance, el_vigia _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] xeon processors
Hi all, Can anyone tell me if postgresql has problems with xeon processors? If so, there is any fix or project of fix it? Thanx in advance, Jaime Casanova Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias.
Re: [HACKERS] xeon processors
thanx"Joshua D. Drake" [EMAIL PROTECTED] wrote: Hello,I seem to recall that HyperThreading and PostgreSQL != good stuff...There was a whole bunch of stuff recently on this... google the archives.Sincerely,Joshua D. DrakeJaime Casanova wrote: Hi all, Can anyone tell me if postgresql has problems with xeon processors? If so, there is any fix or project of fix it? Thanx in advance, Jaime Casanova *Do You Yahoo!?* Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias .-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBCPostgresql support, programming shared hosting and dedicated hosting.+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.comMammoth PostgreSQL Replicator. Integrated Replication for PostgreSQLbegin:vcardfn:Joshua D. Draken:Drake;Joshua D.org:Command Prompt, Inc.adr:;;PO Box 215;Cascade Locks;Oregon;97014;USAemail;internet:[EMAIL PROTECTED]title:Consultanttel;work:503-667-4564tel;fax:503-210-0034note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.x-mozilla-html:FALSEurl:http://www.commandprompt.com/version:2.1end:vcardDo You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias.
[HACKERS] A wanna be
Hi all, I'm a young developer with some knowledge in various programming languages including C. Nowadays, i'm not capable to contribute to any part of the postgresql project but i want seriously learn what i need in order to contribute. Can you guys tell me where can i start? Where can i find usefull information about Databases programming techniques? Thanx in advance, Jaime CasanovaTom Lane [EMAIL PROTECTED] wrote: "Dave Page" <[EMAIL PROTECTED]>writes: As many of you will know, I recently placed a news item on www.postgresql.org to try to get some new developers working on psqlODBC. I was pleseantly surprised to find that after just a few days I had received eight responses from people interested in joining the project. This message has been BCC'd to them.This is really, really good news. With you and Hiroshi both pretty muchwithdrawn from development, I was afraid psqlODBC would die on the vine.(I have no time to contribute to it either :-()One comment on your todo list: 3) An audit of the code for possible buffer overrun problems should be undertaken.I think this is really critical and should be done ASAP. We alreadyhave at least one known issue of this kind. The bad news of courseis that it is boring, tedious work ... but on the other hand it's agreat way to learn one's way around the code. I hope that several ofthe new developers will perform such reviews.regards, tom lane---(end of broadcast)---TIP 6: Have you searched our list archives?http://archives.postgresql.orgDo You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias.
[HACKERS] Developers page is down
Hi guys i was looking for the http://developer.postgresql.org/todo.php in order to view what things are you posponing for later versions but the entire developer.postgresql.org site is down. By the way, will be a way in postgresql 8 to add a column in a middle of a table. just curious. thanx in advance, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Adding columns in the middle of tables
--- Gaetano Mendola [EMAIL PROTECTED] escribió: Jaime Casanova wrote: Hi guys i was looking for the http://developer.postgresql.org/todo.php in order to view what things are you posponing for later versions but the entire developer.postgresql.org site is down. By the way, will be a way in postgresql 8 to add a column in a middle of a table. just curious. No IIRC. The core doesn't think this is a valid feature. I had in the past my reasons to ask for it too. If you have yours may be... Hi Gaetano, I want to clarify this. The point of the core is they won't do it because they have things more important to do, or, the feature will never be part of the postgresql even if someone else contribute it. Maybe i am not capable of contribute it but i will try if the feature will be become part of postgresql. thanx in advance, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Adding columns in the middle of tables
--- Tom Lane [EMAIL PROTECTED] escribió: We looked at this and decided that it would be vastly more trouble than it's worth --- not only in terms of effort to implement the feature originally, but in ongoing maintenance and risk of bug creation. For instance, the original proposals about it required separating logical and physical column numbers, so that a new column could be added physically at the end but logically be earlier in the sequence. This would be a huge amount of work to get done in the first place: you'd have to look at essentially every single use of column numbers in both the backend and every application and decide which flavor you wanted to use at that place. And mistakenly using the wrong flavor would be a permanent gotcha that could be expected to introduce new bugs in future. Given the 8.0 ALTER TABLE feature of being able to rewrite the whole table, one could now think about doing it without decoupling logical and physical numbers: just rewrite the table with the new column inserted in the proper place. This moves the problem to a different area, which is being sure you have updated every place in the system catalogs and backend caches that references the old column numbers of the renumbered columns. Again, doable in theory but a lot of work, and it introduces a bug hazard every time someone changes these data structures. So, no we're not likely to do it ourselves, and we'd probably reject as unmaintainable any patch to do it in either of the above ways. What's needed to get the idea off the forget it list is a different implementation plan that isn't going to create a maintenance headache. regards, tom lane Got it. Obviously this is not a necesary feature and is a feature that good design can do a very rare need. I will think if there is another implementation plan that can be used (but if *the core* didn't find it i hardly will). thanx a lot for the explanation, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] get_rel_* functions in lsyscache.c
Hi, i have a doubt... it seems to me that the get_rel_* functions in lsyscache do the same as doing heap_open(); Calling the appropiate macro Relation* heap_close(); is there any difference between them? in wich situation is one better than the other? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] #ifdef NOT_USED
Hi, i have found several #ifdef NOT_USED marked code... i guess this is dead code... is safe to remove it? there is some reason you just hide it and not remove the code? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] lookup fail at DROP USER
Hi, i have installed the latest CVS, 8.1beta1. and get an error executing at DROP USER. these are the commands i execute and the error i get: in: psql -U postgres template1 CREATE USER deimos; CREATE TABLESPACE sgis_dat OWNER deimos LOCATION '/data/postgres/sgis_dat'; CREATE TABLESPACE sgis_idx OWNER deimos LOCATION '/data/postgres/sgis_idx'; CREATE DATABASE sgis WITH OWNER deimos ENCODING 'latin1' TABLESPACE sgis_dat; the i drop the objects: DROP DATABASE sgis; DROP TABLESPACE sgis_dat; DROP TABLESPACE sgis_idx; DROP USER deimos; ERROR: cache lookup failed for tablespace 16396 i get these lines from the log file: LOG: statement: drop database sgis; LOG: transaction ID wrap limit is 2147484144, limited by database postgres LOG: statement: drop tablespace sgis_idx; LOG: statement: drop tablespace sgis_dat; LOG: statement: drop user deimos; ERROR: cache lookup failed for tablespace 16396 i hope it helps... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] TODO item: set proper permissions on non-system schemas
Hi, I see this TODO item: * %Set proper permissions on non-system schemas during db creation I think a quetion here is wich are non-system schemas? i guess public is one of these... but in pg_namespace i have no way to know that. Is there another way? or maybe we need an attribute to know that? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] sequences TODO items
Hi, i'm looking for some item i can do and that have enough consensus to make it worth the effort... :) * Have sequence dependency track use of DEFAULT sequences, seqname.nextval? what this means? i don't understand it... * %Disallow changing default expression of a SERIAL column? why? a SERIAL is not really a datatype but a short-hand to make an integer with a nextval's sequence as default... so why making them both (the integer type and the nextval's sequence as default) act as if it were a single unit? Actually, i have dropped sequences created with SERIAL because i found that was better to me to make it manually... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] avoid pulling up subquerys that contain volatile functions?
the comments fot contain_volatile_functions in clauses.c says... src/backend/optimizer/util/clauses.c: * * XXX we do not examine sub-selects to see if they contain uses of * volatile functions. It's not real clear if that is correct or not... */ but this example seems to clarify (or at least i think) that we have to avoid pulling up subquerys containing volatile functions: --- BEGIN SQL --- create view vfoo_random as select alu_codigo, is_true from (select alu_codigo, (random() * 5) as is_true from rec_m_alumno) as t_tmp where is_true 1; select count(*) from vfoo_random where is_true 1; drop view vfoo_random; --- END SQL --- i thought it was just calling contain_volatile_function from is_simple_subquery() in src/backend/optimizer/prep/prepjointree.c but it doesn't work for me. what i miss? -- 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] avoid pulling up subquerys that contain volatile functions?
On 10/8/05, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: but this example seems to clarify (or at least i think) that we have to avoid pulling up subquerys containing volatile functions: This is exactly the same example discussed in previous threads on this issue. Do you think it will change anyone's mind? regards, tom lane you are right, i haven't internet all day this week so i'm reading mails for parts... in any case, i still think that is better to get bad performance because i forgot to correctly mark a function that to get incorrect data from a correct query because a gotcha... there is a precedent for this in postgres??? BTW, i still wanna get a patch for my postgres... so i will keep trying... but i don't understand why when i add the function contain_volatile_functions in the is_simple_subquery function i got the same results... :) -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] avoid pulling up subquerys that contain volatile functions?
On 10/9/05, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: On 10/8/05, Tom Lane [EMAIL PROTECTED] wrote: This is exactly the same example discussed in previous threads on this issue. Do you think it will change anyone's mind? in any case, i still think that is better to get bad performance because i forgot to correctly mark a function that to get incorrect data from a correct query because a gotcha... there is a precedent for this in postgres??? Just to be clear, I'm in favor of changing it; but the majority opinion in the previous discussion seemed to be against. [snipped some interesting explanation about this] regards, tom lane Maybe, document it? even with an example? and the workaround of course -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] plperl error when making 8.2dev CVS
Hi, i was trying to compile CVS using --with-plperl (perl installed is 5.6.1) and i get this error when make go inside plperl: make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. make[2]: *** [all] Error 1 make[1]: *** [all] Error 2 make: *** [all] Error 2 -- 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] plperl error when making 8.2dev CVS
On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: Hi, i was trying to compile CVS using --with-plperl (perl installed is 5.6.1) and i get this error when make go inside plperl: fixing a bad typo in the message i try --with-perl (no --with-plperl like said above)... Just in case, someone tell me that's the error... the above was an error of typo in the message... make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. Did you delete that file? It's part of the sources. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. No... i didn't... and the file *is* in the plperl dir -- Atentamente, 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] plperl error when making 8.2dev CVS
On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. Did you delete that file? It's part of the sources. No... i didn't... and the file *is* in the plperl dir Weird. So if you cd src/pl/plperl and ls, the file is there, yet it tries to make it? That's weird. Did you try a make distclean after changing your configure arguments? I've never seen this problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support i think i did... but it was 1:30 am maybe i dream it, i will try 'make distclean' and configure again at night... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl error when making 8.2dev CVS
On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. Did you delete that file? It's part of the sources. No... i didn't... and the file *is* in the plperl dir Weird. So if you cd src/pl/plperl and ls, the file is there, yet it tries to make it? That's weird. Did you try a make distclean after changing your configure arguments? I've never seen this problem. ok, i execute 'make distclean' and then get the same error when making after configuring with --with-perl -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] plperl error when making 8.2dev CVS
On 11/9/05, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Jaime Casanova wrote: ok, i execute 'make distclean' and then get the same error when making after configuring with --with-perl Is this a clean source tree? Try cvs update -C (beware it'll destroy all your local changes) I'm wondering about a clock skew problem (if machine's clock is in the past then SPI.xs might appear newer than derived files). If not that, it sounds like it would have to be a bug in gmake. regards, tom lane mmm... sorry for the noise... i download the CVS from my windows box (because i can't get my internal modem to work with linux :) for some reason i don't know sometimes when moving from windows to a linux dir shared with smb (in the same machine)... it converts file names from uppercase to lowercase so the file in linux was named spi.xs not SPI.xs... i realize that this was the problem just when replacing the entire source tree with same i downloaded in windows... this time the names got right and everything is fine... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] someone working to add merge?
Hi, there is someone working in add the MERGE statement? i don't find much about what a good implementation of merge must have... i think what it needs to do is something like: - try to lock the rows for update - if the lock cannot be immediatly acquire ask why - if the rows are already locked, wait and try again? - if no rows were found try de insert part - if there was any other error, abort - else update so i suppose we can reuse many of the code breaking the merge in 3 pieces... for now they are just thougths, i will think more in this and try to implement it... comments? ideas? suggestions? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] someone working to add merge?
On 11/11/05, Josh Berkus josh@agliodbs.com wrote: Jaime, so i suppose we can reuse many of the code breaking the merge in 3 pieces... for now they are just thougths, i will think more in this and try to implement it... comments? ideas? suggestions? Funny, we were just discussing this at OpenDBCon. Seems that you can't do a full implementation of MERGE without Predicate Locking (the ability to say lock this table against inserts or updates of any row with key=5). it isn't what select for update does? However, Peter suggested that we could do a proof-of-concept implementation, working out syntax and trigger issues, based on a full table lock and do the hard work once it was proved to be feasable. Peter? -- Josh Berkus Aglio Database Solutions San Francisco -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] someone working to add merge?
On 11/11/05, Peter Eisentraut [EMAIL PROTECTED] wrote: Tom Lane wrote: If you don't have any better idea how to do it than a full table lock, you might as well not do it at all. A proof of concept that doesn't solve the hard part of the problem is no proof :-( But the problem here is not to break any kind of performance barrier, but to give people migrating from MySQL and alternative for REPLACE command. But MERGE isn't REPLACE... REPLACE will delete old records to insert new ones; MERGE try to insert and if the record exists then can UPDATE just a few values, maybe incrementing them with a value (all the calculation are doing by the MERGE) My first guess about a real implementation would involve extending the index AM API to offer a function insert this key, or return the existing match if there already is one. This assumes that there are indexes defined for the columns involved in the merge condition, which is not required anywhere. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MERGE vs REPLACE
On 11/11/05, Peter Eisentraut [EMAIL PROTECTED] wrote: 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. That said, what kind of support for insert-or-update-this-row do we want to provide, if any? Should it be a REPLACE command, an extension of the INSERT command, a modication of the MERGE syntax, or something else? -- Peter Eisentraut http://developer.postgresql.org/~petere/ MERGE seems to me the better option... not just because is standard but at least i can see some use cases for it... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MERGE vs REPLACE
On 11/11/05, Peter Eisentraut [EMAIL PROTECTED] wrote: Jaime Casanova wrote: MERGE seems to me the better option... not just because is standard but at least i can see some use cases for it... I don't think you understand my message: MERGE does not do what REPLACE does. -- Peter Eisentraut http://developer.postgresql.org/~petere/ I understand you well... what i was trying to say is that i prefer MERGE (standard SQL command) to be done because the functionally it has (basically a merge of two tables) seems to me to be more usefull than REPLACE (MySql Command)... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MERGE vs REPLACE
On 11/11/05, Josh Berkus josh@agliodbs.com wrote: Guys, I understand you well... what i was trying to say is that i prefer MERGE (standard SQL command) to be done because the functionally it has (basically a merge of two tables) seems to me to be more usefull than REPLACE (MySql Command)... But even REPLACE requires predicate locking. There's no real way to get around it. --Josh why? seems that REPLACE only work if there are at least one row matching... -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] REPLACE implementation (was: Re: MERGE vs REPLACE)
On 11/12/05, Matteo Beccati [EMAIL PROTECTED] wrote: 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 would also like to add that MySQL's REPLACE is not exactly an INSERT OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the fields not specified in the query are set to their defaults: This sounds a lot like postgres implementation of UPDATE... delete tuple (actually, mark it as dead and insert)... Maybe we can use this? or maybe some kind of merge between ExecDelete and ExecInsert? Also, the MySQL implementation require DELETE and INSERT permission. What about triggers? run before/after delete and insert? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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
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] someone working to add merge?
On 11/15/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Csaba Nagy wrote: 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] This only happens because of the unique index. There's no predicate locking involved. The btree code goes some lengths to make this work; it would be probably simple to modify this to support MERGE or REPLACE on the limited cases where there's a UNIQUE index. Tom has already said this twice (on this thread only; he has already said it before IIRC.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support And the only type of predicate locking we need for MySQL REPLACE because it needs a pk or unique index to know it has to replace otherwise it inserts the row... that's the way it works as mysql spec said... -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] MERGE vs REPLACE
On 11/15/05, Josh Berkus josh@agliodbs.com wrote: Simon, 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. Yes, I guess my hesitation on the full-table-lock strategy is that it doesn't really fulfill the mandate for why people want REPLACE-like statements ... to give them an INSERT-or-UPDATE with *higher* efficiency and concurrency than doing two statements. That being said, I've personally designed more than a dozen web applications and have not yet been faced with a single circumstance of not knowing whether I wanted to INSERT or UPDATE. I've even ported MySQL apps and found it easy to re-code them to do if $id = 0, then insert ... without even needing to use a pl/pgsql hack. Actually REPLACE is not INSERT or UPDATE... REPLACE means INSERT if already exists DELETE then INSERT can be used as an UPDATE if you use the SET clause but, it is optional -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] MERGE vs REPLACE
You could also just add something to the merge syntax like ALLOW TABLE LOCK or something. The idea is just that the user can explicitly allow the table lock and thus the more complicated merge. The problem here is that many people will see that option and think it's safe to do it... i mean, many people will shoot themselves in the foot and the culprit will be PostgreSQL because he let a ready to shoot gun in a visible place when are kids around -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MERGE vs REPLACE
On 11/16/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Interesting approach. Actually, we could tell the user they have to use BEGIN;LOCK tab before doing MERGE, and throw an error if we don't already have a table lock. If the lock will be required, what's the problem in doing it internally? without user interaction? -- 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] Optional postgres database not so optional in 8.1
On 11/17/05, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Nov 18, 2005 at 12:01:31AM +1100, John Hansen wrote: On a fresh installation of postgrsql 8.1 if you drop the 'postgres' database, psql, createdb, etc. no longer works. psql -l; ignores -dtemplate1, and createdb doesn't have such an option. What distribution? I've never seen this postgres database you speak of. It certainly not on any systems I've used. Do you have a PGDATABASE variable in your environment? Have a nice day, -- 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. That database exists beginning with 8.1... I don't think it's optional... i remember a comments from Tom telling that this database will be used to connect to for CREATE DATABASE porpouses: http://archives.postgresql.org/pgsql-committers/2005-06/msg00302.php -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
And yes merge CAN be used to do REPLACE (oracle uses their dummy table for this, we can use the fact that FROM clause isn't required in postgres). the FROM clause is required by default (starting with 8.1) unless you change a postgresql.conf parameter. and i don't think that idea will have any fan... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MERGE vs REPLACE
On 11/22/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Jaime Casanova wrote: And yes merge CAN be used to do REPLACE (oracle uses their dummy table for this, we can use the fact that FROM clause isn't required in postgres). the FROM clause is required by default (starting with 8.1) unless you change a postgresql.conf parameter. and i don't think that idea will have any fan... No, it is not, try SELECT 1. Oracle requires SELECT 1 FROM dual. The change in 8.1 is that SELECT pg_class.relname no longer works. You have to do SELECT relname FROM pg_class. -- 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 touche... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] multi-key index
Hi everyone, From the TODO items: Use index to restrict rows returned by multi-key index when used with non-consecutive keys to reduce heap accesses. For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and col3 = 9, spin though the index checking for col1 and col3 matches, rather than just col1; also called skip-scanning. I was looking in the archives something about this but i found nothing. Where can i found the thread (i suppose should be one) about this issue? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Thoughts about updateable views
In a galaxy far, far away Bernd wrote: The context: http://archives.postgresql.org/pgsql-hackers/2004-03/msg00999.php so joined views are even not updateable, too. I don't find the why of this on the specs and the threads about this issue ignore the comment. Is this right? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Updateable views
Hi,
I'm currently working with Bernd in an implementation
of updateable views and want to know the hacker's
opinion on this issue.
What features have to be implemented in a first
extension in order to the patch to be accepted? What
features can wait until a second extension?
This are my first thought on this (i start working on
this just two weeks ago).
* thoughts ***
- What if we cannot create one of the three rules?
Make the rule not updateable at all?
Or create the rules we can? (i think this is the
correct)
General Restrictions!!!
---
- The column target list holds column fields only,
that are retrieved from one base relation / view
only. (NO joined views).
- UNION [ALL]/EXCEPT, DISTINCT and GROUP BY query
expressions aren't updateable at all.
- HAVING, Aggregates, function expressions and
Subqueries aren't allowed to be updateable, too
NOTE: one option is add a catalog that contains info
about updateability of the view attributes, just
like ORACLE's user_updateable_column view
(actually pg_attribute says what columns has a
view, can it be extended?).
That way we can have views in which some columns
are updateable and other are not. Views with
more complicated querys (even joined ones) can
be allowed this way.
Insertable???
--
We need to provide, at least, a value for every column
in the underlaying table that is NOT NULL and do not
have a DEFAULT value.
- If primary key of the table is a serial we can
manage it
CREATE RULE ins_people_full as ON
INSERT TO people_full DO INSTEAD
(
INSERT INTO people (person_id, inits, fname)
VALUES (nextval('people_person_id_seq'),NEW.inits,
NEW.fname);
INSERT INTO addresses (person_id,city, state, zip)
VALUES (currval('people_person_id_seq'), NEW.city,
NEW.state, NEW.zip);
);
- What if we add a new not null column without a
default value to the underlaying table? The insert
rule must be deleted?
Updateable???
--
Deleteable???
--
- Can we delete a row from the underlaying table if
the view where i execute the delete stmnt does not
view all the columns in that table?
- What about joined views? What is deleted?
Consider:
CREATE VIEW people_full AS
SELECT p.*, a.city, a.state, s.state_long,
a.country, a.zip
FROM people p JOIN addresses a USING (person_id)
JOIN states s USING (state);
The a.city, a.state, s.state_long, a.country, a.zip
columns must be deleted as well as the p.* columns
***
- Other point is: some people will not be happy
with updateable views, they will want their views to
be read-only. Should we have an extension to the sql
specs for this? Something like a READONLY keyword?
The patch Bernd did, actually covers some of this
points but is just for *very, very* simple views. We
want improve it.
These of course are just general ideas, and we really
want to know your opinion.
regards,
Jaime Casanova
_
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Updateable views
--- Greg Stark [EMAIL PROTECTED] escribió: - What if we cannot create one of the three rules? Make the rule not updateable at all? Or create the rules we can? (i think this is the correct) I seem to be in the minority here. But I think creating complex rules to fiddle with the updates to translate them to the underlying tables is the wrong approach. I think you want to extend the SQL syntax to allow updating views, and implement plan nodes and executor functionality to handle them. What if someone want his views to be readonly? with rules he can just drop rule. In the approach you mention he cannot. So things like this works: UPDATE (SELECT id,val FROM t) SET val=0 where id 100 You really do things like that??? For what?? I'm asking because i do not know any situation when it becomes usefull. Views, conceptually, should have the same behavior a table has, because you can use it to let some people view part of your info without letting them touch the table. Sometimes you need they can update the fields they can see, but then how u can prevent them touching other fields they have no rights to? Updateable views are handy for that. In your example is obvious that you can access to the t table, why not do the update directly?? Besides, this enforce to create privileges per columns rather than per table. Then the rules you create on the views are just like the rules for SELECT, they simply mechanically replace the view with the view definition. I think this is the right approach because: a) I think creating the general rules to transform an update into an update on the underlying table will be extremely complex, and you'll only ever be able to handle the simplest cases. By handling the view at planning time you'll be able to handle arbitrarily complex cases limited only by whether you can come up with reasonable semantics. I don't think is *extremely complex* to create the rules; but yes, there will be limitations. b) I think it's aesthetically weird to have functionality that's only accessible via creating DDL objects and then using them, and not accessible directly in a single SQL DML command. Ie, it would be strange to have to create a temporary view just in order to execute an update because there's no equivalent syntax available for use directly. alter table (SELECT id,val FROM t) alter column val set default 3; General Restrictions!!! --- - The column target list holds column fields only, that are retrieved from one base relation / view only. (NO joined views). I know there are other uses for updatable views (eg implementing column-based security policies) but the _only_ reason I ever found them useful in Oracle was precisely for joined views. The NOTE i included in my last post says that oracle do that with user_updateable_columns view and i suggest the creation (or the extension of pg_attribute) of a catalog to implement this. And i state that can be useful to create joined updateable views. They're the Oracle blessed method for achieving the same performance win as Postgres's FROM clause. So in Oracle you can do: UPDATE (select a.val as newval, b.b_id, b.val from a,b where a.b_id = b.b_id) SET val = newval I think Postgres's UPDATE ... FROM is a lot more clear to understand. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] displaying contents
Hi, there is way to display all the values (fields) in a tree node like this? for debug purpouses. Query *query; regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] displaying contents
--- Tom Lane [EMAIL PROTECTED] escribió: Jaime Casanova [EMAIL PROTECTED] writes: there is way to display all the values (fields) in a tree node like this? for debug purpouses. Query *query; Look at elog_node_display(). regards, tom lane Ok. Thanks. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] rule system oddity
Hi, i was doing some tests and found out this: create table foo ( id serial not null primary key, nametextnot null ); NOTICE: CREATE TABLE will create implicit sequence foo_id_seq for serial column foo.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo i want the view show the oid of the row it shows: create view vfoo as select oid, * from foo; so far, so good... -- then i create this rule create rule insrule as on insert to vfoo do instead insert into foo(id, name) values (new.id, new.name); and try an insert insert into vfoo values(1, 'test1'); ERROR: invalid input syntax for integer: test1 it seems like it's trying to insert into the oid column is that the intended behaviour? or is it a bug? (i think is the latter). if it's a bug? where (in the code) is the rule expanded? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] rule system oddity
Hi, i forgot to mention the version it's pgsql-8.0.0rc3. freshly installed. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] rule system oddity
--- Michael Fuhr [EMAIL PROTECTED] escribió: On Tue, Jan 04, 2005 at 11:51:54PM -0600, Jaime Casanova wrote: create view vfoo as select oid, * from foo; ... create rule insrule as on insert to vfoo do instead insert into foo(id, name) values (new.id, new.name); ... insert into vfoo values(1, 'test1'); ERROR: invalid input syntax for integer: test1 it seems like it's trying to insert into the oid column is that the intended behaviour? or is it a bug? (i think is the latter). vfoo has three fields: oid, id, and name. If you INSERT without a column list, the values are assigned to the columns in order: oid=1, id='test1', name=NULL. Are you suggesting that the insert should ignore oid since its source is a system column? I'm not sure such behavior would be desirable because of the inconsistency it would introduce: sometimes values would be assigned to the displayed columns in order, but other times one or more of those columns might be implicitly skipped. ahhh... you are right i have to use named column list to do that insert. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] subqueries in check
Hi, i was looking at the unsuported features in the RC4 docs and found this: F671| Enhanced integrity management| Subqueries in CHECK| intentionally omitted Why is it *intentionally omitted*? Is it to hard? or has some side-effects? just a question! regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] US Patents vs Non-US software ...
--- Alvaro Herrera [EMAIL PROTECTED] escribió: On Mon, Jan 17, 2005 at 07:31:48PM -0400, Marc G. Fournier wrote: Just curious here, but are patents global? PostgreSQL is not US software, but it is run within the US ... so, would this patent, if it goes through, only affect those using PostgreSQL in the US, or do patents somehow transcend international borders? No, they are limited to the territory they are registered in. It depends. Every country is independant so their laws are independants but if they sign a covenant in that way or if there are any commercial covenants to force with, countries like US can do their will. But i think like Tom's. There is nothing to worry about there are no penalty for violate a non-existing patent. And when (if) the patent become a reality i'm sure the core (you geniuses of programming) have been eliminated that algorithm. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] TIP9
TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Hi, sorry for using this list, but is not time to change this TIP for something more suitable to the new PG8 capabilities? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] TIP9
--- Joshua D. Drake [EMAIL PROTECTED] escribió: Jaime Casanova wrote: TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Hi, sorry for using this list, but is not time to change this TIP for something more suitable to the new PG8 capabilities? Is this still not the case for pg8? I know it is better about casting in general for use with int8 etc... but don't the column datatypes still have to match? http://archives.postgresql.org/pgsql-hackers/2004-11/msg00497.php ??? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema
Josh Berkus josh@agliodbs.com writes: And overall, I'd think it would make the feature a *lot* less useful; basically it would encourage a lot of DBAs to organize their schemas by security level, which is not really what schemas are for. that's not the way Oracle do things? one schema per user and the objects of the user in its own schema? at least i was tought that way. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] prev main developer page
Hi, --- a little off topic --- I want to do something so informative like the map that was in the prev main developer page. Can anyone point me about what tool to use? and maybe some guidance on this? thanks, Jaime Casanova ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Data loss, vacuum, transaction wrap-around
On Fri, 18 Feb 2005 22:35:31 -0500, Tom Lane [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: I think there should be a 100% no data loss fail safe. Possibly we need to recalibrate our expectations here. The current situation is that PostgreSQL will not lose data if: 1. Your disk drive doesn't screw up (eg, lie about write complete, or just plain die on you). 2. Your kernel and filesystem don't screw up. 3. You follow the instructions about routine vacuuming. 4. You don't hit any bugs that we don't know about. I'm not an expert but a happy user. My opinion is: 1) there is nothing to do with #1 and #2. 2) #4 is not a big problem because of the velocity developers fix those when a bug is found. 3) All databases has some type of maintenance routine, in informix for example we have (update statistics, and there are others for oracle) of course they are for performance reasons, but vacuum is too for that and additionally give us the XID wraparound. So, to have a maintenance routine in PostgreSQL is not bad. *Bad* is to have a DBA(1) with no clue about the tool is using. Tools that do to much are an incentive in hire *no clue* people. (1) DBA: DataBase Administrator or DataBase Aniquilator??? regards, Jaime Casanova ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
On Thu, 10 Mar 2005 12:44:50 -0500, Tom Lane [EMAIL PROTECTED] wrote: Would those of you with access to other DBMSes try this: On informix 9.21.UC4 create table tab (col integer); select 1 from tab having 1=0; returns no rows select 1 from tab having 1=1; returns no rows insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; returns no rows select 1 from tab having 1=1; returns 2 rows regards, Jaime Casanova ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Raw size
On Thu, 10 Mar 2005 20:07:13 +0200 (EET), Ioannis Theoharis [EMAIL PROTECTED] wrote: Hi, i have a table: create table triples( att0 varchar(1000), att1 int4, att2 varchar(20), att3 varchar(1000) ) My table has 990 raws. The (possibly wrong) way, with wich i compute the size of the table is: att0: 1000 * 1 Byte + 4 = 1004 Bytes i don't know what the varchar size is in byte but i think is not 1 per character. IIRC, it varies on diferent encodings. regards, Jaime Casanova ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] rewriter in updateable views
Hi,
Bernd and myself are working in updateable views, one thing we find is
that when we have something like:
create table foo (
col1 serial,
col2 text default 'default'
);
create view vfoo as select * from foo;
then we create the appropiate rules for allow INSERT /UPDATE /DELETE on the view
but if we do INSERT INTO vfoo(col2) values ('some_string) the rewriter
cann resolv the value for col1. the reason is that views does not
inherit the defaults of the parent table. That is the reason you add
the ALTER TABLE ALTER COLUMN ADD/DROP DEFAULT for views.
Ok, this is a problem for us, so we want to improve the rewriter to
see the default in the base table an add it as appropiate.
Can you comment on this? Are there any issues here we have not seen
yet? performance? possible?
regards,
Jaime Casanova
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] rewriter in updateable views
On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Jaime Casanova [EMAIL PROTECTED] writes:
... but if we do INSERT INTO vfoo(col2) values ('some_string) the
rewriter
cann resolv the value for col1. the reason is that views does not
inherit the defaults of the parent table. That is the reason you add
the ALTER TABLE ALTER COLUMN ADD/DROP DEFAULT for views.
Ok, this is a problem for us, so we want to improve the rewriter to
see the default in the base table an add it as appropiate.
Why do you not define the problem as when we decide a view is
updateable and create the needed rules for it, also create default
values for it by copying up from the base tables?
regards, tom lane
Well, that was our first thought. but what if the default value is
changed in the base table? then we have a problem, can we found in
what views we have to alter the default value in order to keep
consistency.
regards,
Jaime Casanova
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] rewriter in updateable views
On Sat, 19 Mar 2005 11:42:18 +, Simon Riggs [EMAIL PROTECTED] wrote:
On Sat, 2005-03-19 at 01:10 -0500, Jaime Casanova wrote:
On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Jaime Casanova [EMAIL PROTECTED] writes:
... but if we do INSERT INTO vfoo(col2) values ('some_string) the
rewriter
cann resolv the value for col1. the reason is that views does not
inherit the defaults of the parent table. That is the reason you add
the ALTER TABLE ALTER COLUMN ADD/DROP DEFAULT for views.
Ok, this is a problem for us, so we want to improve the rewriter to
see the default in the base table an add it as appropiate.
Why do you not define the problem as when we decide a view is
updateable and create the needed rules for it, also create default
values for it by copying up from the base tables?
Well, that was our first thought. but what if the default value is
changed in the base table? then we have a problem, can we found in
what views we have to alter the default value in order to keep
consistency.
I can see that I might want the view to have a different default value
from that of the underlying table. I can see a reason to have multiple
updateable views on the same table, all with different columns, column
defaults and row selection clauses. (Multiple classes all held within
the same physical table, for example).
I'd suggest - if the default value for a column on a view IS NOT set,
then use the default value from the underlying table. If it IS set, then
it should stay set, even if the underlying table changes. That might
need some dependency logic in there...
And here is were we thought we have to improve the rewriter, if the
rewriter find a default value for a view it will use it if not it must
look for a default value in the base table.
regards,
Jaime Casanova
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] rewriter in updateable views
On Sat, 19 Mar 2005 11:05:39 -0500, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane [EMAIL PROTECTED] wrote: Why do you not define the problem as when we decide a view is updateable and create the needed rules for it, also create default values for it by copying up from the base tables? Well, that was our first thought. but what if the default value is changed in the base table? So? Being able to have a different default for the view could be construed as a feature, not a bug. regards, tom lane We are not against this. As you say this is a feature, but if the view doesn't have a default value we have to assign something in the appropiate col in the insert. ALTER TABLE view_name ALTER COLUMN ADD/DROP DEFAULT is your friend ;) regards, Jaime Casanova ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] rewriter in updateable views
On Sat, 19 Mar 2005 11:42:18 +, Simon Riggs [EMAIL PROTECTED] wrote: I can see that I might want the view to have a different default value from that of the underlying table. I can see a reason to have multiple updateable views on the same table, all with different columns, column defaults and row selection clauses. (Multiple classes all held within the same physical table, for example). I'd suggest - if the default value for a column on a view IS NOT set, then use the default value from the underlying table. If it IS set, then it should stay set, even if the underlying table changes. That might need some dependency logic in there... Best Regards, Simon Riggs I think i can do this within rewriterHandle.c:build_column_default immediatly after the first try i can ask if expr == NULL and if is it a view if so my code start looking for defaults on base tables maybe using something like the parse_relation.c:colNameToVar function to identify the column. there is a better way to do it? also the ParseState parameter is extensively used in the colNameToVar function but i can't find what is it. regards, Jaime Casanova ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] when using a bound cursor, error found..
On Tue, 29 Mar 2005 20:46:44 +0900, [EMAIL PROTECTED] wrote: I 'll use a bound cursor with parameters. But when I use such a cursor, I found a error. I don't know error message. How do you know there is an error. Postgres should send you a message or a log entry. without it i doubt can help you. regards, Jaime Casanova ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Feature freeze date for 8.1
On 5/1/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Sun, 1 May 2005 14:35:37 -0500 Bruno Wolff III [EMAIL PROTECTED] wrote: On Sun, May 01, 2005 at 19:57:37 +0300, [EMAIL PROTECTED] wrote: Listen Tom, write a client software that releases the resources / locks that was hold before client power is down or client connection was lost. If Postgres can tell the connection has been lost then it should roll back the connection. Yes, but, Can PostgreSQL know which connection is lost or live or dead ? The problem is that you can't always tell if a connection has been lost. All you can do is timeout, either when TCP times out or some other timeout (such as a statment timeout) that you set. You are right, a timeout parameter must be used for that on the backend. a client application never find the previous instance before it crashed. However more than one connection was able to be established to PostgreSQL backend.. Statement_timeout is just a escape mechanism for active transaction. Imagine; you've started a process to update the rows in a table then your PC power was down but you have not sent commit or rollback yet..What will happen now If you send the update outside a transaction and... Option 1) ...the client crashes then the update will commit, i think. If you don't want that send the update inside a begin/commit block. Option 2) ...the server crashes the update will rollback. If you send the update inside a transaction and... Option 1) ...the client crashes then the update will rollback. Option 2) ...the server crashes the update will rollback. Actually, i can't see what's the problem. :) -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Can we get patents?
On 5/9/05, Dann Corbit [EMAIL PROTECTED] wrote: If the idea originates in PostgreSQL, then nobody else can patent it, because there will be pre-existing art (the PostgreSQL engine) that already demonstrated the idea. A patent must have a novel idea in it. I do not think a good thing can come from creation of software patents. Here is a link of interest: http://swpat.ffii.org/index.en.html Of course, it is IMO-YMMV. But you need to probe that there is pre-existing art, and that implies a trial and costs involved. Who will do the representation? who will pay the bills? The same argument can be used (and in fact, was used) against the idea of patent software. :( -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Understanding Rule System
On 5/11/05, Juan Pablo Espino [EMAIL PROTECTED] wrote: Hello all I have been studying the rule system in Postgres. I understand that the original query tree is the input at the rewrite, and then this query tree is modified by the rewrite in case that there is a rule. SQL query Parser Rewrite Planner Executor Query New Query treetree I would like to know how the rewrite detects that a rule in a table exists, and if it possible you indicate me where in the source code it's implemented. Any comments are welcome, thanks in advance. src/backend/rewrite/*.c The entry point i think is rewriteHandler.c IIRC, there is a catalog that is used to know if there are any rules for a table i think it is pg_rewrite, but i can be wrong. -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views update ?
On 5/18/05, LAMBEAU Bernard [EMAIL PROTECTED] wrote: Hi, On the TODO list, if found the following information : Automatically create rules on views so they are updateable, per SQL99 Does anyone already work on such a tool inside PostgreSQL ? I'm interessed by giving some contribution to this task ... if you're interessed also ? Hi Bernd and i, specialy Bernd, are working on this. Any help would be appreciated. This is the patch against current HEAD. Have you talked with Bernd already? if not, i am emailing to tell him about you. -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) pgsql-view_update_8.1dev.tar.bz2 Description: BZip2 compressed data ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] adding a function to pg_proc.h
I have added a function in sources and added appropiate lines in pg_proc.h
DATA(insert OID = 2560 ( get_view_column_defaultPGNSP PGUID 12 f f
t f i 1 2283 25 25 21 _null_ _null_ _null_ get_view_column_default -
_null_));
DESCR(get default value for view column);
then make; make install; and initdb the data directory.
When i start postgres i look for the function using:
\df get_view*
List of functions
Schema | Name | Result data type | Argument data types
+-+--+--
pg_catalog | get_view_column_default | anyelement | text, text, smallint
(1 row)
So far, so good... I will do a test
create table foo (
col1serial,
col2int2
);
NOTICE: CREATE TABLE will create implicit sequence foo_col1_seq for
serial column foo.col1
create view v_foo as select * from foo;
NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules
alter table v_foo alter col1 set default
get_view_column_default('public'::text, 'foo'::text, 1::smallint);
ERROR: function get_view_column_default(text, text, smallint) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
So the question is why ERROR message tell the function doesn't exist,
as you can see the function actually exists.
I change the return data type to bigint and got the same error so it
seems is not a problem of returning anyelement.
any comments will be appreciated.
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] adding a function to pg_proc.h
On 5/25/05, Tom Lane [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: I have added a function in sources and added appropiate lines in pg_proc.h DATA(insert OID = 2560 ( get_view_column_defaultPGNSP PGUID 12 f f t f i 1 2283 25 25 21 _null_ _null_ _null_ get_view_column_default - _null_)); DESCR(get default value for view column); Try putting the right value for pronargs ;-) jeje... fooly of me Also, it seems highly unlikely that this function should be marked as immutable. Stable sounds more likely. i don't know when i change this i marked it volatile, but well.. again it's my fault Also, defining the return type as ANYELEMENT will definitely NOT work, since none of the input arguments are polymorphic. mmm... This is a problem, there is a way to make a function that can be used for returning different datatypes depending on the columns regards, tom lane -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] adding a function to pg_proc.h
On 5/25/05, Jaime Casanova [EMAIL PROTECTED] wrote: Also, defining the return type as ANYELEMENT will definitely NOT work, since none of the input arguments are polymorphic. mmm... This is a problem, there is a way to make a function that can be used for returning different datatypes depending on the columns Ok, i have read in the manual about returning polymorphic types. why we can't cast the function to the appropiate type to avoid the extra parameter? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] thw rewriter and default values, again
Hi, here we go again... As you know there is a problem executing something like: create table foo ( col1 serial, col2 int ); create view v_foo as select * from foo; create rule ins_rule as on insert to v_foo do instead insert into foo(col1, col2) values (new.col1, new.col2); insert into v_foo(col2) values (1); this give an error like: psql:f:/views.sql:13: ERROR: null value in column col1 violates not-null constraint There is a workaround about this creating default values to the view. Now, for updateable views we need this happen automatically, attached there is a solution to this. The only problem i have found until now is that update v_foo set col1 = DEFAULT; execute nextval twice per every record. so there will be a gasp between numbers, but AFAIK nextval has no guarantee of returning sequential numbers. Any comments on this? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) *** ../pgsql_head/src/backend/rewrite/rewriteHandler.c 2005-05-27 02:38:08.0 -0500 --- src/backend/rewrite/rewriteHandler.c2005-05-27 14:36:58.0 -0500 *** *** 376,381 --- 376,389 new_expr = build_column_default(target_relation, attrno); /* +* I will do this only in case of relkind == RELKIND_VIEW. +* This is the last attempt to get a value for new_expr before we +* consider that new_expr must be NULL. +*/ + if (!new_expr target_relation-rd_rel-relkind == RELKIND_VIEW) + new_expr = (Expr *) makeNode(SetToDefault); + + /* * If there is no default (ie, default is effectively NULL), * we can omit the tlist entry in the INSERT case, since the * planner can insert a NULL for itself, and there's no point ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] thw rewriter and default values, again
On 5/28/05, Tom Lane [EMAIL PROTECTED] wrote:
Jaime Casanova [EMAIL PROTECTED] writes:
create rule ins_rule as on insert to v_foo do instead
insert into foo(col1, col2) values (new.col1, new.col2);
insert into v_foo(col2) values (1);
this give an error like:
psql:f:/views.sql:13: ERROR: null value in column col1 violates
not-null constraint
That's not a bug, and fixing it isn't acceptable.
The correct solution to the problem you are looking at is
to attach default expressions to the view itself. Adding
alter table v_foo alter col1 set default nextval('public.foo_col1_seq');
to your example makes it work as you wish.
I know you're right, but -when dealing with updateable views- doing
that implies to add a lot of time altering views when base table
change, and of course we maybe don't want all views get that values.
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] thw rewriter and default values, again
On 28 May 2005 10:25:48 -0400, Greg Stark [EMAIL PROTECTED] wrote: Jaime Casanova [EMAIL PROTECTED] writes: The only problem i have found until now is that update v_foo set col1 = DEFAULT; execute nextval twice per every record. so there will be a gasp between numbers, but AFAIK nextval has no guarantee of returning sequential numbers. While there's no guarantee that strong there's still an expected behaviour. sequences generate sequential numbers and only skip in specific cases. I think this would still surprise and bother most users. Certainly, i will look deeper in it. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ddl triggers
Could we keep track of NOT TODO items also? The question is where do you put this idea if you reject it? :-) And of course a link to the thread, or an explanation on why it was rejected. That way if someone can solve the reason for reject it maybe a new discussion can be made. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] thw rewriter and default values, again
On 6/6/05, Richard Huxton dev@archonet.com wrote:
Jaime Casanova wrote:
I know you're right, but -when dealing with updateable views- doing
that implies to add a lot of time altering views when base table
change, and of course we maybe don't want all views get that values.
Sorry for the late posting on this thread.
Might there be any way to have something like ...SET DEFAULT
pg_same_as('public','foo','col1').
Hmm - you'd need pg_same_as_int/text/etc. depending on the return-type -
still not ideal.
Actually, i try to do that but i need the function to be polymorphic
and because polymorphic functions needs to receive at least one
polymorphic argument that teach about the return type of the function
i can't go ahead with this idea.
About the code i talk earler in this thread i solve the problem with
the update to serial columns.
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Issue with adding ORDER BY to EXCEPT.
SELECT encounter.encounter_id, encounter_d.encounter_d_id FROM encounter JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id EXCEPT SELECT encounter.encounter_id, encounter_d.encounter_d_id FROM encounter JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id JOIN p_l_d ON p_l_d.patient_id = encounter.patient_mpi WHERE encounter_d.encounter_id = encounter.encounter_id AND ((p_l_d.start_date = encounter_d.from_date OR p_l_d.start_date IS NULL) AND (p_l_d.end_date = encounter_d.from_date OR p_l_d.end_date IS NULL)) ORDER BY encounter.encounter_id, encounter_d.encounter_d_id With the ORDER BY NOTICE: adding missing FROM-clause entry for table encounter NOTICE: adding missing FROM-clause entry for table encounter_d ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns I suppose this is because the columns in the except are the same that the ones in the main select and the order by get confused. i'm redirecting to hackers to know if this is a known bug or there is something wrong in the select? i don't see anything wrong!! -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] In RULEs, INSERT does not use DEFAULTs
On 6/12/05, Tom Lane [EMAIL PROTECTED] wrote: David Fetter [EMAIL PROTECTED] writes: I believe this isn't just my problem. Without access to a the underlying column's DEFAULT, how can people implement the automated WRITEable VIEWs? That's a reasonable question, but translating insert null to insert the default is not a reasonable answer. There was some speculation just a couple days ago about inventing a function that would compute the default associated with some other table's column, but it's not clear how to make that work (in particular, how to declare the result type of such a function). I discarded the idea because i couldn't fight with the polymorphic function to return the correct value in any case. But i successfully found that hacking rewriteHandler.c can do the trick. I am using that in updateable views project. Another possibility is a command along the lines of ALTER view ALTER col LINK DEFAULT TO othertable.col; (syntax open to argument of course) which accomplishes the same thing without having to figure a way to avoid the constraints of a specific function result type. That's sounds like a good idea too -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Graphics in postgress using GTK
On 12/2/05, Anuj Tripathi [EMAIL PROTECTED] wrote: Hi We are trying to implement a progress estiamator for long queries. We are trying to make the display graphical using GTK but we get an error saying Xlib : Connection to :0.0 refused by server xlib : no protocol specified GTK-warning ** : Cannot open display Can someone suggest a method to integrate this GTK code into postgres and solve this problem. Thanks and Regards Anuj Tripathi What about to return to your application a cursor or a set of rows and while looping let your progress bar advance? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] generalizing the planner knobs
BTW, there's another end to the 'enable_seqscan=false' problem... it sometimes doesn't work! Last I looked, enable_seqscan=false only added a fixed overhead cost to a seqscan (100 IIRC). The problem is, some queries will produce estimates for other methodes that are more expensive than a seqscan even with the added burden. If instead of adding a fixed amount enable_seqscan=false multiplied by some amount then this would probably be impossible to occur. (And before someone asks, no, I don't remember which query was actually faster...) -- 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 I have often considered that this is an indication that seq scan is actually the better plan... although, i have to admit that is a little confusing that enable_seqscan = false actually let you use a seqscan if the other plans are bad enough -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Server Crash, possible security exploit, where to send security report?
Hi all, while playing with Npgsql I faced an strange behavior of Postgresql server. I have all the details of it and I thought it could be a severe security exploit, so I don't send it in clear to this mailing list directly as, I think, anybody with this information could Dos postgresql servers. Please, send me information to where/who I should send the details in order this can be fixed as soon as possible. http://www.postgresql.org/support/security.html -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] second begin transaction emits a warning
Hi, recently someone show us this code in the spanish list... BEGIN WORK; INSERT INTO mitabla VALUES (1); BEGIN TRANSACTION; INSERT INTO mitabla VALUES (2); INSERT INTO mitabla VALUES (3); COMMIT TRANSACTION; INSERT INTO mitabla VALUES (4); ROLLBACK WORK; this is clearly bad you can't use a begin transaction inside a transaction... but the user was expecting other results and because he receives no error (actually was a warning but he is sending the commands via an external application)... he was expecting an empty table but instead he gets this: mitabla 1 2 3 (3 rows) so, why BeginTransactionBlock emits just a warning and not an error? this is not the same as in the case of the one who was closing and already closed cursor? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
On 12/21/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Andreas Pflug wrote: Martijn van Oosterhout wrote: So it's only an issue if you have a policy of removing old versions of libpq on upgrades... I'm not sure what's best practice on windows in this area. When removing the application (in this case: pgsql), you'd remove that old lib as well if it's the only app using it. If you have another application installed, the deinstaller should observe this, and keep the version. I'm voting +1 for lib name versions. If you add a version number to the Win32 libpq name, you have to update any command-line compile tools that mention libpq after an upgrade. The Unix linker knows about version numbers, but the Win32 linker doesn't, so adding version numbers does add quite a bit of chaos to the Win32 compile world. win32 compile world *is* a chaos... it's very frustating when you try to run a program and it fails because a library (when you actually has the library, at least _a_ version of the library)... IMHO, adding version numbers to the name of library for windows is a the cleanest thing you can do... -- 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 -- 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] [COMMITTERS] A question about index internals
On 1/10/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I've been working with SQL Server for a long time and many times I had to change how an index page is filled with fillfactor clause. I've noticed that PostgreSQL doesn't have anything like that, am I right? I'd like to implement something that could change the index page filling. I've found a struct called IndexOptInfo. Do I have to change it? Also, I followed the creation index path and I reached a fuction called heap_create. Am I in a right way, or completely lost? Best regards Eduardo Morelli lost... at least in the list you select to post ;) redirecting to pgsql-hackers@postgresql.org -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] message for constraint
Hi, From time to time people ask me if there is a way to customize messages for constraints so they could be more informative to the user... Imagine something like: create table foo (fld int4 check (fld 0)); message for constraint foo_fld_check on foo is 'fld field must contain possitive numbers only.'; so i can let this message go directly to my users, and they can respond without knowing waht a check constraint is... we can use the pg_description catalog with a column added to indicate if it is a comment or a message for constraint... what do you think, it's worth the effort? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] message for constraint
On 1/14/06, Peter Eisentraut [EMAIL PROTECTED] wrote: Jaime Casanova wrote: From time to time people ask me if there is a way to customize messages for constraints so they could be more informative to the user... What about this? = create table foo (fld int4 constraint fld must contain positive numbers check (fld 0)); CREATE TABLE = insert into foo values (-5); ERROR: new row for relation foo violates check constraint fld must contain positive numbers -- Peter Eisentraut http://developer.postgresql.org/~petere/ ok, i didn't know you can use such names... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
On 1/17/06, Daniel Schuchardt [EMAIL PROTECTED] wrote:
Hi,
here is a testcase:
CREATE OR REPLACE FUNCTION testseq()
RETURNS void AS
$BODY$
BEGIN
CREATE TEMP SEQUENCE test;
PERFORM testseq1();
DROP SEQUENCE test;
RETURN;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq() OWNER TO postgres;
CREATE OR REPLACE FUNCTION testseq1()
RETURNS void AS
$BODY$
DECLARE I INTEGER;
BEGIN
I:= nextval('test');
RETURN;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq1() OWNER TO postgres;
SELECT testseq();
-- this works fine.
SELECT testseq();
ERROR: could not open relation with OID 21152
CONTEXT: PL/pgSQL function testseq1 line 3 at assignment
SQL statement SELECT testseq1()
PL/pgSQL function testseq line 3 at perform
Greetings,
Daniel.
try this way:
CREATE OR REPLACE FUNCTION testseq() RETURNS void AS
$BODY$
BEGIN
EXECUTE 'CREATE TEMP SEQUENCE test';
PERFORM testseq1();
DROP SEQUENCE test;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq() OWNER TO postgres;
CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS
$BODY$
DECLARE I INTEGER;
BEGIN
EXECUTE 'select nextval(''test'')' INTO I;
raise notice '%', I;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq1() OWNER TO postgres;
SELECT testseq();
SELECT testseq();
is the same problem as with temp tables, you must put their creation,
and in this case even the nextval in an execute...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] log_min_messages and debug levels
Hi, in my machine (win xp) i was trying to start psql (8.1.1) with log_min_messages to debug5 (just to see the messages :) but even the service start i cannot use psql nor pgadmin i receive an error of server closed the connection unexpectedly postgres=# select version(); version -- PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) (1 fila) Sorry, my postgres is in spanish but maybe you can recognize the message... ;) C:\Archivos de programa\PostgreSQL\8.1\binpsql -U postgres pruebas psql: el servidor ha cerrado la conexión inesperadamente, probablemente porque terminó de manera anormal antes o durante el procesamiento de la petición. is this expected on windows platforms? -- 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] un-vacuum?
On 1/19/06, uwcssa [EMAIL PROTECTED] wrote: I have a simple question here, not sure if i should posted here but if you have the quick answer, it helps a lot i have a table that is already vacuumed. for some reason i want to un-vacuum it instead of dropping the table and recreate the table and indexes on it. is there a existing command to do so? can you explain yourself a bit better? vacuum is good, why do you think you want to undo it? why do you think that drop and create will undo vacuum? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] textToQualifiedNameList second parameter
On 2/24/06, Rafael Martinez Guerrero [EMAIL PROTECTED] wrote: Hello In version 7.4.x, the function textToQualifiedNameList was defined with two parameters. Some months ago, the second parameter was removed [1] so I had to modify my program to work after this change. Now with 8.0.7, the second parameter is back again and I get this error when I try to compile: so, your progam rely on internal functions from someone else's software? bad idea Is the second parameter back again? [1]: http://archives.postgresql.org/pgsql-patches/2005-05/msg00307.php -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug in 7.3.2
On 2/28/06, Suvarna [EMAIL PROTECTED] wrote: we are using postgresql 7.3.2 version. As somebody pointed out, that's not a bug... but i think you must upgrade at least to 7.3.14 even if you really found a bug nobody will fix it for 7.3.2 -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problemas with gram.y
On 3/3/06, Tom Lane [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: I'm trying to extend the CREATE INDEX statement with a fillfactor clause. Um, are you aware that a patch for that was already submitted? http://momjian.postgresql.org/cgi-bin/pgpatches I find the whole idea pretty ugly myself. regards, tom lane why? if i can ask? you didn't seem upset with that in the thread -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(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] Updateable views was:(Re: [HACKERS] Proposal for SYNONYMS)
On 3/9/06, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus josh@agliodbs.com writes: Eh? I thought that it was just syntatic sugar that was missing. I've built lots of updatable views manually; I don't see what's difficult about it. I think you'll find that corner cases like inserts involving nextval() don't work real well with a rule-based updatable view. But perhaps I'm just scarred by the many complaints we've had about rules. With a plain unconditional DO INSTEAD rule it might be OK ... regards, tom lane the last time i talk with Bernd Helmle, he was preparing the code to send to patches for discussion... that was two months ago... the current code had problems with casts and i think with domains too... i will contact with Bernd to know if he did some more work, if not i can send to patches the latest path he sent me... -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] There is a problem with the download site?
Hi, i am trying to download the windows version since 3 hours ago and just get an error page no matters if i try the FTP browser, ftp mirrors or bittorrent -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] There is a problem with the download site?
fixing some typos, sorry is late... i am trying to download the windows version since 3 hours ago and just get an error page no matters if i try the FTP browser, ftp mirrors or bittorrent MOre on this, i was able to download from bittorrent when i manually paste the url and remove the http://wwwmaster.postgresql.org/redir? part and in the rest of the url i change %2F for / (without ) so it becomes from: http://bt.postgresql.org/binary%2Fv8.1.3%2Fwin32%2Fpostgresql-8.1.3-1.zip.torrent to http://bt.postgresql.org/binary/v8.1.3/win32/postgresql-8.1.3-1.zip.torrent -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(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] Function's final statement must not be a SELECT
On 3/10/06, Pavel Stehule [EMAIL PROTECTED] wrote: Hello, I want do sql wrap for woid plpgsql function. But void SQL function must not finish SELECT cmd. I don't know any others command which I can use. Can You help me? Thank You Pavel Stehule perform * from your_table; -- Atentamente, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for updatable views
On 3/13/06, Bernd Helmle [EMAIL PROTECTED] wrote: --On Sonntag, März 12, 2006 23:52:12 -0500 Neil Conway [EMAIL PROTECTED] wrote: On Fri, 2006-03-10 at 11:21 +0100, Bernd Helmle wrote: Please find attached a patch that implements SQL92-compatible updatable views. I'm currently reviewing this. Comments later... ok Please note that the patch isn't complete yet Do you have a list of known TODO items? There's a problem with CASTed expressions because it thinks (and with reason) that they are functions expressions (and those are not allowed) but with CAST you have to be flexible... i was working on that but at the time i am very busy... -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(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] for statement, adding a STEP clause?
Hi, there is a chance to add a STEP clause to the FOR statement in plpgsql? something like FOR i IN 1..100 STEP 2 LOOP END LOOP the STEP value must be a positive value because of the effect of the REVERSE clause... i think it's just a matter of fixing gram.y, plpgsql.h (to add another PLpgSQL_expr to the stmt_fori) and pl_exec.c (the way the internal variable is incremented here makes me think that i wasn't the first one with this idea) i'm missing something? is STEP a good name for this? i will make a try tomorrow any ideas and suggestions are welcome... -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] BEGIN inside transaction should be an error
On 5/10/06, Tom Lane [EMAIL PROTECTED] wrote: Dennis Bjorklund [EMAIL PROTECTED] writes: Yesterday I helped a guy on irc with a locking problem, he thought that locking in postgresql was broken. It turned out that he had a PHP function that he called inside his transaction and the function did BEGIN and COMMIT. Since BEGIN inside a transaction is just a warning what happend was that the inner COMMIT ended the transaction and released the locks. The rest of his commands ran with autocommit and no locks and he got broken data into the database. Could we make BEGIN fail when we already are in a transaction? We could, but it'd probably break about as many apps as it fixed. I wonder whether php shouldn't be complaining about this, instead --- doesn't php have its own ideas about controlling where the transaction commit points are? regards, tom lane AFAIK php doesn't care about that... it just see for success or failure conditions, so if postgres said everything is ok it will continue... -- Atentamente, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] BEGIN inside transaction should be an error
On 5/10/06, Tom Lane [EMAIL PROTECTED] wrote: Martijn van Oosterhout kleptog@svana.org writes: How do other database deal with this? Either they nest BEGIN/COMMIT or they probably throw an error without aborting the transaction, which is pretty much what we do. Is there a database that actually aborts a whole transaction just for an extraneous begin? Probably not. The SQL99 spec does say (in describing START TRANSACTION, which is the standard spelling of BEGIN) 1) If a start transaction statement statement is executed when an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state - active SQL-transaction. *However*, they are almost certainly expecting that that condition only causes the START command to be ignored; not that it should bounce the whole transaction. So I think the argument that this is required by the spec is a bit off base. regards, tom lane Well, actually informix throw an error... at least, my 4gl programs always abort when a second begin work is found inside a transaction... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] BEGIN inside transaction should be an error
On 5/12/06, Mario Weilguni [EMAIL PROTECTED] wrote: Am Donnerstag, 11. Mai 2006 22:16 schrieb Simon Riggs: On Wed, 2006-05-10 at 21:24 -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: How do other database deal with this? Either they nest BEGIN/COMMIT or they probably throw an error without aborting the transaction, which is pretty much what we do. Is there a database that actually aborts a whole transaction just for an extraneous begin? Probably not. The SQL99 spec does say (in describing START TRANSACTION, which is the standard spelling of BEGIN) 1) If a start transaction statement statement is executed when an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state - active SQL-transaction. *However*, they are almost certainly expecting that that condition only causes the START command to be ignored; not that it should bounce the whole transaction. So I think the argument that this is required by the spec is a bit off base. If you interpret the standard that way then the correct behaviour in the face of *any* exception condition should be *not* abort the transaction. In PostgreSQL, all exception conditions do abort the transaction, so why not this one? Why would we special-case this? IMO it's ok to raise an exception - if this is configurable for at least one releasy cycle - giving developers time to fix applications. It's no good behaviour to change something like this without any (at least time-limited ) backward compatible option. if an option to change it is put in place, maybe it will be there forever (with a different default behavior)... i am all in favor of a second begin to throw an exception already in transaction or something else (http://archives.postgresql.org/pgsql-hackers/2005-12/msg00813.php), but if we do it we should do it the only behavior... i don't think it's good to introduce a new GUC for that things (we will finish with GUCs to turn off every fix) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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] PL/pgSQL 'i = i + 1' Syntax
On 5/16/06, David Wheeler [EMAIL PROTECTED] wrote: On May 16, 2006, at 16:30, Andrew Dunstan wrote: It ought to be illegal to modify the loop control variable anyway, IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. I agree, but I must say that it's incredibly useful to be able to increment by two as I go through a loop: FOR i IN 1 + offset .. 11 + offset LOOP total := total + substring(ean, i, 1)::INTEGER; i = i + 1; END LOOP; Best, David i have posted a patch to add a BY clause to the for statement (integer version), with the BY clause you can specify an increment value... it's in the unapplied patches list waiting for review... http://candle.pha.pa.us/mhonarc/patches/msg3.html -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
(No, I'm not particularly in favor of the BY feature mentioned upthread, either.) regards, tom lane mmm... and why is that? i mean, many languages have some way to increment the for variable by different values... call it STEP, BY or even i+=number -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
