Re: [HACKERS] Converting PL/SQL to PL/PGSQL
Am Donnerstag, 10. Mai 2001 19:23 schrieb Roberto Mello: On Thu, May 10, 2001 at 03:33:27PM +0200, Klaus Reger wrote: Have you looked at the Porting From Oracle PL/SQL chapter of the PostgreSQL Programmer's Guide? I am expanding that guide to include more things, like queries. The goas is for it to become a Porting From Oracle guide. Yes I did, and it was very helpful for me. Thank you for this stuff. I made a list of the differences I found for me too. If you want it, I cam send it to you. Writing PS/PGSQL tools seems to be a bit hard, because of the existing tool-infrastructure on linux. Are there are tools I have overseen? Heh? What do you mean by this? There are zillions of editors, both console and graphical, where you can do this. Ah! That is right, I use emacs too. I have found pgaccess to be vey useful in testing. In the OpenACS project (www.openacs.org) we port thousands of lines of Oracle code to PostgreSQL, mostly using vim or Emacs. For testing, I use pgaccess because it lets me drop/recreate a function easily, plus it escapes quotes. One thing I don't like about it is that it's hard to keep things indented. The problem for me seems, that the code is in the database. When you want to edit it, you do this in three steps: 1. Get source from the database 2. Edit the source 3. Put it back to the database When there are no syntax-problems in the proc-declarations, or any wrong nested things step 3 is no problem. But often, when I ram my procedures I get runtime-errors (without konowing, where the problem exactly is). So here some type of compilation would be very useful. First, I used pgacess too. because it is very helpful to develop pl/pgsql-procedures. But as the maintainer of my own Web-database-frontend I decided to write my own tool, which is very similar to pgaccess. - A WWWdb-Application for editing and testing of SQL-Procedures over a WEB-frontend Cool. Anywhere we can see this in action? WWWdb of course. Point your browser to http://WWWdb.org. The procedure part is very sensible (because I don't want everybody to change my procedures :-), so it is not testable on my site. I may send you some screenshots, or you could install WWWdb at your computer and I send you the code separately, because it is not released as OpenSource yet. - A perl-script, that does basic conversions between PL/SQL - XML - PL/PGSQL (The Procedure-definition is converted completely, the code-block a little bit) Hmmm. *Very* interesting. Link? Source for this anywhere? We could probably use this at OpenACS. I asked my boss, if he allows me to give out the sources, I will start a project at sourceforge. Stay tuned. In this way it is called: -- work@pc01:SqlProc$ ConvertPlsql.pl -h Call: ConvertPlsql.pl [-DVw] [-o file] [file ...] Switches: -D Debugging-mode -V show version -o file file is the file where the output should be directed to. If file is a directory, one source-file will be generated for every procedure. When file is a normal file, all output will be generated into this single file. Default is STDOUT, which can be passed explicitly as '-' -s Sort functions alphabetically at output (Default is unsorted) -S Source-language This is the language of the existing script-file(s). Valid values are (Default is PL_SQL): - pl_sql -T Target-language This is the language of the generated script-file(s). Valid values are (Default is PL_PGSQL): - xml - pl_pgsql -w Display warnings, that are found in conversion-process Description: ConvertPlsql.pl scans PL/SQL-Procedure-definitions and tries to convert them to PL/PGSQL. Here is an example of the conversion between Oracle, Postgres and XML: -- ?xml version=1.0 encoding=iso-8859-1? !DOCTYPE SOURCE SYSTEM ./SqlProc.dtd SOURCE FUNCTION NAME = chk_ip TYPE = FUNCTION RESULTTYPE = NUMBER PARAMETER NAME = IPADRESSp INOUT = IN TYPE = VARCHAR,/ PARAMETER NAME = N_uid INOUT = IN TYPE = NUMBER,/ VARIABLE NAME = N_tmp TYPE = NUMBER/ CODE SELECT test.NEXTVAL INTO N_uid /* FROM DUAL */ ; N_tmp := 'That''s my quoted text!'; RETURN N_tmp; EXCEPTION WHEN others THEN return -100; /CODE /FUNCTION /SOURCE -- DROP FUNCTION chk_ip (VARCHAR, NUMBER,); CREATE FUNCTION chk_ip (VARCHAR, NUMBER,) RETURNS INTEGER AS ' DECLARE IPADRESSpALIAS FOR $1; N_uid
Re: [HACKERS] 7.1.2 release
At 01:28 11/05/01 -0400, Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Yes - it's waiting on the problem Zoltan reported (the select from pg_rewrite etc). I can't reproduce the problem on any of my DBs. I've just realized that the problem is a lot simpler than it appears. The given string is too long for a NAME: Ung. That's a bit nasty for views: pjw=# create view szallitolevel_tetele_erveny01 as select * from t1; CREATE pjw=# create view szallitolevel_tetele_erveny02 as select * from t1; ERROR: Attempt to insert rule _RETszallitolevel_tetele_erveny failed: already exists But at least I can fix pg_dump now. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] AW: WAL and raw devices (was: volume management)
As an aside, I do however think, that optimizing the O_SYNC path of the WAL code to block writes to larger blocks (doesn't need to be more than 256k) would lead to nearly the same performance as a raw device on most filesystems. (Maybe also add code to reuse backed up logfiles to avoid the need to preallocate space) Imho this is the part of the code where the brainwork should first be put into. It is also a prerequisite to make raw devices fast, since if you write 8k blocks to a raw device, that is slow (not faster than a fs). You cannot block writes to the WAL without blocking transactions waiting on the write, because completion of that write is necessary for the transaction to complete. Yes, this is obvious, but: You *can* block writes into larger blocks as long as no commit comes inbetween. This essentially increases performance e.g. for bulk loads where single transactions are 8k of WAL. A typical example is even in the regression test, the copy ... from statements. They really suffer from the O_SYNC mode. This mode is essentially what you would have now for a raw device WAL. Moving the WAL volume's disk head into position is the major investment you are amortizing with your large blocks. If the head is already in position, it is about as efficient to write a little as to write a lot. This is only half of the story for large transactions. For large transactions you need to write more than the current 8k in one call (only in the raw device, or O_SYNC mode of course). Writing in large blocks also helps the fs to reduce head movement. After every write call the OS suspends the current process, and makes room for another backend to e.g read a block on the same drive, thus forcing head movement. I suggest you do some tests with raw devices, which I already did, to see what happens if you only write 8k blocks (you only get 50-60% performance compared to 256k). The IO performance gain you can achieve on a raw device compared to a preallocated filesystem file is imho neglectible. e.g. on AIX it is due to a global kernel parameter, that defaults to a max 32k block size for read ahead and write behind. I noted the advantages in a previous thread about why Oracle wants raw devices, and I don't think they are worth it at the current state of PostgreSQL. Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Converting PL/SQL to PL/PGSQL
Hi all! I have to convert functions and procedures from Oracle to PostgreSQL. I looked at all the stuff of the Pg-Homepage and I ask me if there are any tools, that support the conversion. Writing PS/PGSQL tools seems to be a bit hard, because of the existing tool-infrastructure on linux. Are there are tools I have overseen? I have implemented the following tools for my use yet: - A WWWdb-Application for editing and testing of SQL-Procedures over a WEB-frontend - A perl-script, that does basic conversions between PL/SQL - XML - PL/PGSQL (The Procedure-definition is converted completely, the code-block a little bit) Who else is working in this area? Any tips? Regards, Klaus Visit WWWdb at http://wwwdb.org ---(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] Odd results in SELECT
On Thu, 10 May 2001, Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Can anyone suggest why this might be happening (I think it's in 7.1b4): Can't duplicate in current sources: regression=# SELECT definition as viewdef, regression-# (select oid from pg_rewrite where regression(#rulename='_RETstreet') as view_oid regression-# from pg_views where viewname = 'street'; viewdef | view_oid -+-- SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); | 4001276 (1 row) regression=# SELECT definition as viewdef, regression-# (select oid from pg_rewrite where regression(#rulename='_RET' || 'street') as view_oid regression-# from pg_views where viewname = 'street'; viewdef | view_oid -+-- SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); | 4001276 (1 row) What does EXPLAIN show for your two queries? (Maybe you'd better make it EXPLAIN VERBOSE.) I attached both. Kov\'acs, Zolt\'an [EMAIL PROTECTED] http://www.math.u-szeged.hu/~kovzol ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz exp-bad.out exp-good.out ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Problem with a rule on upgrade to v7.1.1
On Thu, May 10, 2001 at 06:44:39PM -0400, Tom Lane wrote: Next question: do you still have your 7.0.* DB up? Can you get an EXPLAIN that shows how it did it (on the real tables)? Tom- Okay. I started from a clean slate, by recompiling both Pgv7.1.1 and Pgv7.1RC1, initdb'ing each (after appropriately changing /etc/ld.so.conf, running ldconfig, etc, etc), and restoring my real DB from a previously created dump file. I didn't do Pgv7.0.3 b/c I think it may be unnecessary since 7.1RC1 doesn't show this problem, while 7.1.1 does. But, if you really think it necessary, I will repeat his using 7.0.3. Notes: 1) As usual, the 7.1RC1 returns from the UPDATE ... command as fast as I press enter. The 7.1.1 returns from the UPDATE ... command in about 10 minutes. 2) The two explains are identical. 3) Both updates succeed, it is only the time difference that is the problem 4) Running UPDATE tplantorgan SET active='t' WHERE sampleid=100430; (setting the boolean to true, instead of false) is instantaneous for both 7.1RC1 and 7.1.1 5) There are 8664 and 3680 tuples in the tplantorgan and tplant tables respectively. So this is a relatively small DB. -Jon The actual results: -- Pg v7.1RC1 (restored from 2001-05-10 db dump): main_v0_8=# vacuum ANALYZE ; VACUUM main_v0_8=# explain update tplantorgan set active='f' where sampleid=100430; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..2243933.76 rows=1 width=239) - Seq Scan on tplantorgan (cost=0.00..2243931.72 rows=1 width=4) SubPlan - Aggregate (cost=258.96..258.96 rows=1 width=0) - Seq Scan on tplantorgan (cost=0.00..258.96 rows=1 width=0) - Index Scan using tplant_pkey on tplant (cost=0.00..2.03 rows=1 width=235) NOTICE: QUERY PLAN: Result (cost=0.00..1112558.20 rows=31883520 width=235) - Nested Loop (cost=0.00..1112558.20 rows=31883520 width=235) - Seq Scan on tplant (cost=0.00..167.80 rows=3680 width=235) - Seq Scan on tplantorgan (cost=0.00..215.64 rows=8664 width=0) NOTICE: QUERY PLAN: Seq Scan on tplantorgan (cost=0.00..237.30 rows=1 width=103) EXPLAIN main_v0_8=# update tplantorgan set active='f' where sampleid=100430; UPDATE 1 -- Pg v7.1.1 (restored from 2001-05-10 db dump): main_v0_8=# VACUUM ANALYZE ; VACUUM main_v0_8=# explain update tplantorgan set active='f' where sampleid=100430; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..2243933.76 rows=1 width=239) - Seq Scan on tplantorgan (cost=0.00..2243931.72 rows=1 width=4) SubPlan - Aggregate (cost=258.96..258.96 rows=1 width=0) - Seq Scan on tplantorgan (cost=0.00..258.96 rows=1 width=0) - Index Scan using tplant_pkey on tplant (cost=0.00..2.03 rows=1 width=235) NOTICE: QUERY PLAN: Result (cost=0.00..1112558.20 rows=31883520 width=235) - Nested Loop (cost=0.00..1112558.20 rows=31883520 width=235) - Seq Scan on tplant (cost=0.00..167.80 rows=3680 width=235) - Seq Scan on tplantorgan (cost=0.00..215.64 rows=8664 width=0) NOTICE: QUERY PLAN: Seq Scan on tplantorgan (cost=0.00..237.30 rows=1 width=103) EXPLAIN main_v0_8=# update tplantorgan set active='f' where sampleid=100430; UPDATE 1 main_v0_8=# select active from tplantorgan where sampleid=100430; active f (1 row) -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham Extracta Moléculas Naturais, Rio de Janeiro, Brasil email: [EMAIL PROTECTED] web: http://www.extracta.com.br/ ***-*--**---***--- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostgreSQL HOWTO Version 42.0 is available for public...
Hello: I can bet that in about a year's time, PostgreSQL user base will explode. (there is broadband connection everywhere in USA and Europe!!! 63% of american households have internet connection compare that to India, where 0.002% of Indian homes have internet connection and there are 1.1 billion indians!!) Please read the PostgreSQL HOWTO v42.0 is at - http://aldev.8m.com Mirrors: http://aldev.webjump.com http://www.angelfire.com/nv/aldev http://www.geocities.com/alavoor/index.html http://aldev.virtualave.net http://aldev.50megs.com http://aldev.bizland.com http://members.theglobe.com/aldev1/index.html http://members.nbci.com/alavoor http://aldev.terrashare.com http://members.fortunecity.com/aldev http://aldev.freewebsites.com http://members.tripod.lycos.com/aldev http://members.spree.com/technology/aldev http://homepages.infoseek.com/~aldev1/index.html http://www3.bcity.com/aldev See also the Benchmarks of Postgresql. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Problems in porting from Oracle to Postgres
Hi! I am facing two problems in porting from oracle to Postgres SQL. 1 There is a code in Oracle like Type Tstate is table of number(9) index by binary_integer; . To define a runtime table, basically it works like a array, How can it be possible in Postgres SQL, I have tried create temp table But it not works.. Is there any way to use arrays. 2 There is one function in Oracle Executesql '...' to execute and what i got in Postgres is Execute immediate '.' But it is giving error at Execute. I will be very thankful if any one help me. Amit ( India ) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: AW: AW: Re: New Linux xfs/reiser file systems
On Tue, 8 May 2001 09:09:08 + (UTC), [EMAIL PROTECTED] (Giles Lean) wrote: Good performance on such storage systems might depend on keeping as much work up to it as possible, to let the device determine what order to service the requests. Attempts to minimise head movement may hurt, not help. Letting the device determine the sequence of IO increases throughput and reduces performance. If you want the maximum throughput, so you can reduce the money you spend on storage, you que the requests and sort the ques based on the minimum work required to complete the aggregated requests. If you want performance, you put your request first and make the que wait. Some storage systems allow the specification of two or more priorities so your IO can go first and everyone else goes second. lazy page writes and all the other tricks used to keep IO in memory have the effect of reducing writes at the expense of data lost during a power failure. Some storage devices were built with batteries to allow writes after power loss. If the batteries could maintain writes for 5 seconds after poser loss, writes could be held up for nearly 5 seconds in the hope that many duplicate writes to the same location could be dropped. I know a lot of storage systems from the hardware up and few outperform an equivalent system where the money was focused on more memory in the computer. Most add on storage systems offering spectacular performance have make most financial sense when they are attached to a computer that is at a physical limit of expansion. If you have 4 Gb on a 32 bit computer, adding a storage system with 2 Gb of cache can be a sound investment. Adding the same 2 Gb cache to a 32 bit system expanded to just 2 Gb usually costs more than adding the extra 2 Gb to the computer. Once 64 bit computers with 32, 64 or 128 Gb of DDR become available, the best approach will go back to heaps of RAM on the computer and none on disk. If you are looking at one of the 64 bit replacements x86 style processor and equivalents, the best disk arrangement would be to have no file system or operating system intervention and have the whole disk allocated to the processor page function, similar to the theory behind AS/400s and equivalents. Each disk would be on a single fibre, service 64 Gb gigabyte and be mirrored on an adjacent disk. The only processing in the CPU would be ECC, the disk controller would perform the RAID 1 processing and perform the IO in a pendulum sweep pattern with just enough cache to handle one sweep. You would, of course, need power supplies big enough to cover a few extra sweeps and something to tell the page processing to flush everything when the power is dropping. When you have multiple computers in a cluster, you could build an intermediate device to handle the page flow much the same as a network switch. All these technologies were tried and proves several times in the last 30 years and work perfectly when the computer's maximum address space is larger than the total size of all open files. They worked perfectly when people had 100Mb databases on 200Mb disks in systems that could address 4Gb. Doubling the number of bits in the address range puts 64 bit systems out in front of both disks and memory again. There are already 128 bit and 256 bit processors in use so systems could be planned to stay ahead of disk design so you never have to worry about a file system again. The AMD slot A and Intel slot 1 could be sold the way you buy Turkish pizza, by the foot. Just walk up to the hardware shop and ask for 300 bits of address space. Shops could have specials, like an extra 100 bits of address space for all orders over $20. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re: New Linux xfs/reiser file systems
Hello ! I am forwarding the following from lkml It seems that the only case when XFS is slow is the 'rm -rf linux' [which can be considered as a good sign for linux]. For all other operation XFS is the winner. YAS MessageFromLKML From: Ricardo Galli ([EMAIL PROTECTED]) Date: Wed May 09 2001 - 20:45:46 EDT * Next message: [EMAIL PROTECTED]: USB broken in 2.4.4? Serial Ricochet works, USB performance sucks. * Previous message: AmigaLinux A2232 Driver Project : New Amiga Driver * Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] It would be great to see a table of ReiserFS/XFS/Ext2+index performance results. Well, to make it really fair it should be Ext3+index so I'd better add 'backport the patch to 2.2' or 'bug Stephen and friends to hurry up' to my to-do list. You can find a simple benchmark (an average of three samples) among reiser, ext2, xfs and fat32 under Linux: http://bulma.lug.net/body.phtml?nIdNoticia=626 Although is Spanish, the tables are easy to understand. The benchmark was carried up by Guillem Cantallops, student of the University of Balearics Islands and member or the local LUG... BASIC WORDS ;-) Escritura: Writing Lectura: Reading Borrado: Deletion Copia: Copy Extracción: Extraction Regards, --ricardo http://m3d.uib.es/~gallir/ - To unsubscribe from this list: send the line unsubscribe linux-kernel in the body of a message to [EMAIL PROTECTED] More majordomo info at http://vger.kernel.org/majordomo-info.html Please read the FAQ at http://www.tux.org/lkml/ /MessageFromLKML Bruce Momjian wrote: I'm concearned about this because we are going to switch our fist server to a Journaling FS (on Linux). Searching and asking I found out that for our short term work we need ReiserFS (it's for a proxy server). Put the interesting thing was that for large (very large) files, everybody recomends XFS. The drawback of XFS is that it's very, very slw when deleting files. Why do all these file systems seem to have one major negative? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Odd results in SELECT
See my prior reply to Philip: the problem is that the given string is
longer than NAMEDATALEN. When you write
rulename = 'foo'
(rulename is of type NAME) the untyped literal string 'foo' gets coerced
to NAME, ie truncated to fit, and all is well. When you write
rulename = ('foo' || 'bar')
the result of the || operator is type TEXT, so instead rulename is
converted to TEXT and a text comparison is performed. In this case the
righthand value is not truncated and so the match will always fail.
regards, tom lane
---(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] Problem with a rule on upgrade to v7.1.1
Jon Lapham [EMAIL PROTECTED] writes: I didn't do Pgv7.0.3 b/c I think it may be unnecessary since 7.1RC1 doesn't show this problem, while 7.1.1 does. But, if you really think it necessary, I will repeat his using 7.0.3. No, that seems like useless work. 1) As usual, the 7.1RC1 returns from the UPDATE ... command as fast as I press enter. The 7.1.1 returns from the UPDATE ... command in about 10 minutes. 2) The two explains are identical. Oh, *that's* interesting. 5) There are 8664 and 3680 tuples in the tplantorgan and tplant tables respectively. So this is a relatively small DB. Would you be willing to send me a dump of the whole DB (or at least the tables needed for this query)? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.2 items
From: [EMAIL PROTECTED] Date: Fri, 11 May 2001 10:41:57 +1200 Here is a small list of big TODO items. I was wondering which ones people were thinking about for 7.2? The need for stored procedures that return a record set. This is required to migrate from MSSQL, Interbase and others. This is a commonly requested item. This would be very useful, as well as the RETURNING clause that is supported elsewhere with inserts. -- Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: 7.2 items
Bruce Momjian wrote: Here is a small list of big TODO items. I was wondering which ones people were thinking about for 7.2? * Allow inherited tables to inherit index, UNIQUE constraint, and primary key [inheritance] i was wondering if there was any thought still being given to Oliver Elphick's post from a while back that is still in TODO.detail [inheritance]: http://candle.pha.pa.us/mhonarc/todo.detail/inheritance/msg00010.html i kind of feel as though the inheritance semantics for postgres at the moment are not fully fleshed out, and including further features without having a full plan for the semantics doesn't seem to advance the effort of making postgres a true Object-Relational DBMS. for my part, as a user, i am excited that inheritance is available even in a limited fashion, but where i use it, i have basically had to invent my own semantics for referential integrity based on a suite of triggers. this issue is addressed in Oliver's post, but i was wondering if such issues were still a part of the development dialogue since Oliver's post was the last in TODO.detail [inheritance] and seemed to merit no response (or any that i could find in the mailing list archives). -tfo ---(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: 7.1.2 schedule (was Re: [HACKERS] Posted 7.1 RPMs for Mandrake 7.2)
On Tuesday 08 May 2001 10:16, Tom Lane wrote: Thomas Lockhart [EMAIL PROTECTED] writes: I've built RPMs for 7.1.1, but perhaps we should wait until 7.1.2 to post them given the pgtcl problem? Lamar, what are you planning for 7.1.1? Given my plpgsql screwup, and the dump-7.0-views thing that Philip wants to fix in pg_dump, I'd say there certainly will be a 7.1.2 pretty soon. But I think we should wait a couple more days and see if any other bug reports turn up. Maybe we should plan for the end of the week? Given a quick 7.1.2, I would rather go through the release pain once. Is it just me, or do we have terrible luck with .1 subreleases? IIRC, 6.2.1 was the last good x.y.1 release. I'm not going to beat a dead horse, here, though. :-) HOWEVER, I _do_ have 7.1.1 RPMs built (minus some minor modifications) for RedHat 7.1. Thomas, would you mind e-mailing me any changes you made to anything (other than the version diff)? I have another patch from Trond to apply to the initscript, and more testing would be nice. If 7.1.2 is over a week away, I'll go ahead and release 7.1.1 RPMs -- but I would really like to incorporate any patch to the plpgsql code, Tom, being that I am a member of the OpenACS team :-O. I can easily patch and release 7.1.1 RPMs that don't have the bug -- not that that is the best idea, by any means, but it is for me just about a showstopper. Or I need to release a 7.1-2 set that includes RPM-specific bugfixes to the initscript and files list. Thomas, which pgtcl problem are you referring to? FWIW, my extant CHANGELOG entry for the 7.1.1 RPMs currently reads: * Mon May 07 2001 Lamar Owen [EMAIL PROTECTED] [EMAIL PROTECTED] - 7.1.1 - 7.1.1-1 RPM release - Changes to initscript courtesy Karl DeBisschop - pg_restore was not in 7.1-1 - pl's back into /usr/lib/pgsql - use groupadd's -o and -r switches. As to the group write permissions, Thomas.. The perms on the RPMS subdir now set g+w. Sorry. I'll need to set my umask a little more appropriately. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: 7.1.2 schedule (was Re: [HACKERS] Posted 7.1 RPMs for Mandrake 7.2)
On Tuesday 08 May 2001 11:14, Thomas Lockhart wrote: As to the group write permissions, Thomas.. The perms on the RPMS subdir now set g+w. Sorry. I'll need to set my umask a little more appropriately. Great. I'll move things around. btw, I've found that things like scp don't respect a .cshrc umask setting, so you will likely need to check permissions when you are working in those directories anyway. Ah. Of course, Idon't use the csh :-). But I _do_ use scp exclusively to copy stuff. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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] Problem with a rule on upgrade to v7.1.1
Jon Lapham [EMAIL PROTECTED] writes: But, there is definitely something wrong here, b/c the rule that is causing this *should* only need to run the subselect [SELECT count(*) FROM tplantorgan WHERE tplantid=NEW.tplantid AND sampleidNEW.sampleid AND active='t'] one time! My understanding is that the first conditional in the statement (WHERE tplantid=NEW.tplantid) would be evaluated before the subselect, and there is only 1 tuple in which tplantid=NEW.tplantid. Well, I've figured it out. The problem is that the expensive subselect is actually being evaluated *first* among the several available WHERE clauses, and thus it's run at basically every row of tplantorgan. The tplantid=NEW.tplantid clause does not help because it's a join clause and is not evaluated until we do the join between tplant and tplantorgan. The subselect is a restriction clause and so is evaluated lower down in the plan tree. There are other restriction clauses available, however: OLD.active='t' from the rule qual and sampleid=100430 from the original query both end up in the same list of restriction clauses for the tplantorgan scan. But in 7.1.1 they get put behind the subselect clause. Had they come first, the subselect would get evaluated at very few tplantorgan rows. Postgres has never been particularly careful about the ordering of WHERE clauses, and depending on release version, phase of the moon, etc etc it's perfectly possible that the subselect would have ended up last in the other versions you happened to try. I was able to make current sources run quickly by backing out the rev 1.59 change seen at http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/optimizer/plan/initsplan.c This explains why you saw different results between 7.1RC1 and 7.1.1. There was probably some other change between 7.0.2 and 7.0.3 that caused 7.0.3 to put the clauses in the right order whereas 7.0.2 didn't, but I don't feel like trawling the revision history right now to find it. The long-term solution to this is for the planner to pay attention to the execution cost of WHERE clauses and try to put the expensive ones last in whatever list they end up in. Meanwhile, I don't really recommend that you hack up the code to reverse the ordering yet again. The query is a mess anyway, and rewriting it seems the better pathway. I'm beginning to suspect that my rule is just simply designed poorly... Yes. Why not replace both of those rules with ON UPDATE to tplantorgan DO UPDATE tplant SET active = EXISTS (SELECT 1 FROM tplantorgan WHERE tplantid=NEW.tplantid AND active) WHERE tplantid=NEW.tplantid; which seems a lot more obvious as well as quicker. BTW, an index on tplantorgan(tplantid) would likely help too... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Bug or feature?
Olivier PRENANT [EMAIL PROTECTED] writes: I've just noticed that (after a upgrade from 7.0.3 to 7.1) the following did'nt work anymore: create tabla a (n1 serial, n2 int); grant all on a to nobody; reconnect as user nobody insert into a (n2) value (1); n1.nextval: you don't have permission to set sequence n1 It worked on 7.0.3 You'll have to grant update rights on the sequence object to nobody ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Rtree on custom data types; type conversion stops index use.
Dave Blasby [EMAIL PROTECTED] writes: gives the correct results. Postgres automatically uses the agg_points3d() function to convert the BOX3D into an AGG_POINTS3D. Unfortunately, it doesn't use the index scan anymore; it does a sequence scan. First question: what Postgres version? Next question (if PG = 7.0): did you mark your type conversion routine as cachable? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Rtree on custom data types; type conversion stops index use.
I'm using 7.1.1, and your suggestion WORKED! Thanks for your prompt reply! Refractions Research will be releasing (open source) postGIS very soon (as soon as its in a releasable state). It will contain GIS data types (box3d, multi-point3d, multi-polyline3d, multi-complex-polygon3d) and GIS operations. Tom Lane wrote: ... First question: what Postgres version? Next question (if PG = 7.0): did you mark your type conversion routine as cachable? ---(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] Converting PL/SQL to PL/PGSQL
Hello, PgAdmin http://www.greatbridge.org/project/pgadmin/projdisplay.php is the windows administration interface of PostgreSQL. The new upcoming version features a function, trigger and view IDE. When functions are modified, it is possible to rebuild dependencies. It is the perfect tool for writing PL/PgSQL Wait a few days before it is ready... Greetings from Jean-Michel POURE, Paris, France At 12:24 09/05/01 +0200, you wrote: Hi all! I have to convert functions and procedures from Oracle to PostgreSQL. I looked at all the stuff of the Pg-Homepage and I ask me if there are any tools, that support the conversion. Writing PS/PGSQL tools seems to be a bit hard, because of the existing tool-infrastructure on linux. Are there are tools I have overseen? I have implemented the following tools for my use yet: - A WWWdb-Application for editing and testing of SQL-Procedures over a WEB-frontend - A perl-script, that does basic conversions between PL/SQL - XML - PL/PGSQL (The Procedure-definition is converted completely, the code-block a little bit) Who else is working in this area? Any tips? Regards, Klaus Visit WWWdb at http://wwwdb.org ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of 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] Trouble with initdb when the #define NAMEDATALEN = 51
I'm not sure if this is still needed in postgres to define the length of a variable/table name. In postgres_ext.h, I changed: #define NAMEDATALEN 32 to #define NAMEDATALEN 51 Everything compiled and installed. However, the initdb started up but then just said that it failed. I did a gmake clean and changed the 51 back to 32 and everything went through correctly (make, install, and initdb). Can anyone else verify if this is correct or even makes sense? Thanks. -Tony ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Installation on SGI IRIX 6.5.10
In addition to my RedHat 6.2 server, I'm installing Postgres 7.1.1 on an SGI O2 (IRIX 6.5.10). The configure works, but the 'gmake all' fails when it tries to compile 'xact.c': cc-1521 cc: WARNING File = /usr/include/setjmp.h, Line = 26 A nonstandard preprocessing directive is used. #ident $Revision: 1.36 $ ^ cc-1070 cc: ERROR File = xact.c, Line = 696 The indicated type is incomplete. struct timeval delay; ^ 1 error detected in the compilation of xact.c. gmake[4]: *** [xact.o] Error 2 gmake[4]: Leaving directory `/usr/src/postgresql-7.1.1/src/backend/access/transam' gmake[3]: *** [transam-recursive] Error 2 gmake[3]: Leaving directory `/usr/src/postgresql-7.1.1/src/backend/access' gmake[2]: *** [access-recursive] Error 2 gmake[2]: Leaving directory `/usr/src/postgresql-7.1.1/src/backend' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/usr/src/postgresql-7.1.1/src' gmake: *** [all] Error 2 o21.nsi.edu:postgres::/usr/src/postgresql-7.1.1 I'm using the SGI MIPSPro 7.1 C compiler. I haven't had any problems like this when compiling previous versions of Postgres. If necessary, I could try to get gcc instead of the MIPSPro compiler, but I wonder if the xact.c definition for timeval could be modified to pass on my machine. Thanks. -Tony ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Addition to: Trouble with initdb when the #define NAMEDATALEN = 51
Sorry, I forgot to include that I'm compiling this on RedHat 6.2, Pentium III with Postgres 7.1.1. -Tony I'm not sure if this is still needed in postgres to define the length of a variable/table name. In postgres_ext.h, I changed: #define NAMEDATALEN 32 to #define NAMEDATALEN 51 Everything compiled and installed. However, the initdb started up but then just said that it failed. I did a gmake clean and changed the 51 back to 32 and everything went through correctly (make, install, and initdb). Can anyone else verify if this is correct or even makes sense? Thanks. -Tony ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] inserts on a transaction blocking other inserts
I am having problems with transactions and foreign key constraints in postgres 7.0-3 (RPM distribution). . The foreign key constraints were blocking concurrent transactions. Here is an example where something blocked but shouldn't have blocked: create table hello10 (myid serial primary key, myvalue int4); create table hello11(myvalue int4, foreign key (myvalue) references hello10); insert into hello10 (myvalue) values (1); ok, now everything is set up for the blocking problem. Now have two logins to psql: psql1# begin; psql1# insert into hello11 (myvalue) values (1) psql1# switch to the other login psql2# begin; psql2# insert into hello11 (myvalue) values (1) *** block *** It shouldn't block there. Basically it happens when two transactions try to insert something into tables (doesn't have to be the same one) which both have a foreign key constraint to a common key. I did some poking around and luckily did find something in the archives that was similar here: http://fts.postgresql.org/db/mw/msg.html?mid=30149 It was mentioned that it was a problem, and there was a workaround (add INITIALLY DEFFERED to the constraint). The workaround works. My question is, is this fixed in Postgres 7.1 (i don't have a spare machine to test, sorry)? -rchit ---(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] Addition to: Trouble with initdb when the #define NAMEDATALEN = 51
G. Anthony Reina [EMAIL PROTECTED] writes: In postgres_ext.h, I changed: #define NAMEDATALEN 32 to #define NAMEDATALEN 51 Everything compiled and installed. However, the initdb started up but then just said that it failed. I have not tried that in awhile, but the last time I did, it worked fine. Are you sure you did a *complete* rebuild? I'd suggest make distclean at the top level, configure, make all, install, initdb. BTW, 51 is a gratuitously wasteful setting --- given alignment considerations, any value that's not a multiple of 4 is pointless. (It should work ... but it's pointless.) regards, tom lane ---(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] REL7_1_STABLE tag/branch
it is a branch ... for lack of a better way to work it:
symbolic names:
REL7_1_STABLE: 1.106.0.2
REL7_1_BETA: 1.79
REL7_1_BETA3: 1.86
REL7_1_BETA2: 1.86
REL7_1: 1.102
REL7_0_PATCHES: 1.70.0.2
REL7_0: 1.70
REL6_5_PATCHES: 1.52.0.2
REL6_5: 1.52
REL6_4: 1.44.0.2
release-6-3: 1.33
SUPPORT: 1.1.1.1
PG95-DIST: 1.1.1
the big long numbers (1.106.0.2) denote branches ... the shorter ones
(1.79) simple tags along the main trunk ...
On Fri, 11 May 2001, Bruce Momjian wrote:
Can someone confirm that REL7_1_STABLE is a branch and not a tag? I am
having trouble doing 'cvs log -rREL7_1_STABLE' and wanted to make sure
everything was set up properly. I can 'cvs update/commit' fine.
It is my understanding that it should be a branch.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(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] 7.1.2 release
On Thu, 10 May 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Isn't this only critical for those that are using it? Does it affect those that don't use plpgsql? No, but I think it's pretty critical for those that do ... So, why not create a quick patch for those that need it, and let those with the capability pull from CVS/CVSup ... that is why we have them setup ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Converting PL/SQL to PL/PGSQL
Bruce Momjian [EMAIL PROTECTED] writes: With our new /contrib policy, we could put it right in our PostgreSQL CVS contrib. ?? What new contrib policy? I didn't notice any discussion of policy changes ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] REL7_1_STABLE tag/branch
Bruce Momjian [EMAIL PROTECTED] writes: Can someone confirm that REL7_1_STABLE is a branch and not a tag? Seems to work for committing stuff into the branch, so it must be a branch ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
