[HACKERS] A problem with new pg_dump
I've tried the pg_dump bundled in the new 7.1.1 release. I wanted to test its feature of dumping a 7.0.X database. Let's say I have database A running 7.1.1, B running 7.0.2. Both servers have the same database 'test', 'myview' is a view defined on both of them. I want to dump data only, being a VIEW I expect zero rows. From host A: pg_dump -da -t myview test OK pg_dump -h B -a -t myview test OK pg_dump -h B -da -t myview test An INSERT for each row This last behaviour is obviously wrong because you cannot re-INSERT into the VIEW (no rules are defined). From host B: pg_dump -da -t myview test OK Seems that there is a problem dumping 'INSERT-style' from a 7.0.X database. Running PostgreSQL 7.1.1 on alphaev67-dec-osf4.0f, compiled by cc -std -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 It is more complicated than you think -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] A problem with new pg_dump
At 13:19 7/05/01 +0300, Alessio Bragadini wrote: Seems that there is a problem dumping 'INSERT-style' from a 7.0.X database. It's actually a more general problem - it looks like dumping views in 7.0 does not work with the 7.1.1 pg_dump (it thinks they are tables because the 7.1 check of pg_relkind='v' is not valid). 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Wanted Sydney Australia, someone to explain PostgreSQL to a bunch of programmers
PHP users tend to start with MySQL and stick there. PostgreSQL from release 7 is getting rave reviews for being equivalent in performance to MySQL in medium size web sites. Perhaps it is time for PHP programmers to dive straight in to postgreSQL. Wanted: PostgreSQL expert to rave about PostgreSQL advantages (an explain them) to a bunch of PHP programmers at PHP Sydney User Group. Meeting details at http://phpsydney.com/ plus a contact page there for a brave volunteer. I would love to use PostgreSQL all the time but my 2.7 remaining brain cells re insufficient to install PostgreSQL on NT and my Linux workstation always refuses to talk to either the video card or the mouse. If I find a Linux workstation setup that does not require a screen or a mouse.. ---(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] A problem with new pg_dump
At 23:04 7/05/01 +1000, Philip Warner wrote: It's actually a more general problem - it looks like dumping views in 7.0 does not work with the 7.1.1 pg_dump (it thinks they are tables because the 7.1 check of pg_relkind='v' is not valid). The attached patch should fix the problem. Assuming it tests out OK, can this be back-patched, since 7.1.1 is already out? pg_dump_70_views.gz 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: XFS File systems and PostgreSQL
I have run a simple PostgreSQL benchmark on my SGI system which uses XFS for its file system on all disks to compare the effect of fsync. The benchmark was the loading of a database from 157 MB of pg_dump data including the construction of 11 Btree indexes covering nearly all of the data. The second column was just for the data load, and the third column is for the index creation. The system is an SGI Indigo2 R1 running Irix 6.5.7 with 384 MB RAM writing to Seagate 18GB 7200RPM narrow SCSI disks. Fsync enabled Elapsed load time Elapsed indexing time Yes 15:53 9:16 No10:33 8:40 The CPU is not fully utilized for loading, and thus the system is I/O bound and the use of fsync has an impact. By contrast, the indexing process is CPU bound, and fsync is less important. The performance penalty for using fsync is modest, and therefore, I do not believe that we should discourage people from using XFS because it is a journaling file system. The note advising against installing Postgres on XFS should be removed from the installation guide. Instead, we need to explore how to use XFS's features to improve PostgreSQL's performance. For example, the XFS filesystem journal can be placed on a drive different from the data drive. This would substantially improve write performance. +--++ | Robert E. Bruccoleri, Ph.D. | Phone: 609 737 6383| | President, Congenomics, Inc. | Fax: 609 737 7528| | 114 W Franklin Ave, Suite K1,4,5 | email: [EMAIL PROTECTED]| | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 || +--++ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
AW: [HACKERS] Re: New Linux xfs/reiser file systems
I don't have a machine with XFS installed and it will be at least a week before I could get around to a build. Any volunteers? I think I could do that... any useful benchmarks to run? Looks like we have expert help here :-) One very interesting question would imho be, how do we best preallocate the log files ? The current method is to prewrite 8k pages to the whole file, since only writing 1 byte to the end of file triggered the sparse file handling. This, although usually during off peak times, effectively doubles the writes for WAL. Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] A problem with new pg_dump
Philip Warner [EMAIL PROTECTED] writes: The attached patch should fix the problem. Assuming it tests out OK, can this be back-patched, since 7.1.1 is already out? Yes, it should be back-patched into the REL7_1_STABLE branch once you're confident of it. Probably there will be a 7.1.2 by and by ... Do you need a quick lecture on CVS branch management? 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] Isn't pg_statistic a security hole?
Tom Lane wrote: Serguei Mokhov [EMAIL PROTECTED] writes: Being a simple user, I still want to view the stats from the table, but it should be limited only to the stuff I own. I don't wanna let others see any of my info, however. The SU's, of course, should be able to read all the stats. This is infeasible since we don't have a concept of per-row permissions. It's all or nothing. Can't we provide a view that shows those rows from pg_statistics that belong to the tables owned by the current user? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] incorrect query result using complex structures (views?)
Hi, I cannot decide if this is a serious bug or not --- some queries from complex views may give strange results. The next few days I will try to find the point where the problem is but now I can only include the data structure and the SELECT statements which don't give the correct result. A lot of rows (contained by the database) should be downloaded from http://www.math.u-szeged.hu/~kovzol/rows.pgsql.gz (25K, uncompressed 305K) if you want to check this error. Here are the definitions (rels-views.pgsql) and a RUNME.pgsql file (which must be loaded with \i in psql), it contains the SELECTs. I tried it with 7.1beta4 and 7.1. There ARE workarounds. I am using SQL functions instead of subSELECTs now. Regards, Zoltan CREATE TABLE cikk ( az SERIAL PRIMARY KEY, nev varchar(80) NOT NULL, mennyisegi_egyseg int4 NOT NULL, szin int4 NOT NULL, tipus int4 NOT NULL DEFAULT 1, megjegyzes varchar(250), felvitel_allapot int4 NOT NULL DEFAULT 1); CREATE TABLE keretrendeles ( az SERIAL PRIMARY KEY, szallito int4 NOT NULL, megrendelo int4 NOT NULL, CHECK (szallito megrendelo), az_jel varchar(20) NOT NULL, megjegyzes varchar(250), kezdes date NOT NULL, befejezes date NOT NULL DEFAULT '3000-01-01', CHECK (kezdes=befejezes) ); CREATE TABLE megrendeles ( az SERIAL PRIMARY KEY, szallito int4 NOT NULL, megrendelo int4 NOT NULL, az_jel varchar(10), keretrendeles int4 REFERENCES keretrendeles(az), teljesites date NOT NULL, teljesites_kezdete date, allapot int4 NOT NULL, utolso_lezart_modositas int4 ); CREATE TABLE megrendeles_modositasa ( megrendeles int4 NOT NULL REFERENCES megrendeles(az), sorszam int4 check (sorszam = 0) DEFAULT 0, PRIMARY KEY (megrendeles, sorszam), kelt date check ( (not kelt is null) or (not lezarva) ), beerkezett date NOT NULL DEFAULT now(), kezdemenyezo int4, leiras text, lezarva bool DEFAULT 'f', lezarta int4, megnyito int4, lezaras_idopontja timestamp); CREATE TABLE megrendeles_tetele ( megrendeles int4 NOT NULL REFERENCES megrendeles(az), modositas int4 NOT NULL, FOREIGN KEY (megrendeles, modositas) REFERENCES megrendeles_modositasa(megrendeles, sorszam), tetelszam int4 NOT NULL, archiv bool default 'f', PRIMARY KEY (megrendeles, tetelszam, modositas, archiv), sorrend int4 NOT NULL, kulso_cikk int4 NOT NULL, cikk int4 NOT NULL, minoseg int4 DEFAULT 1 NOT NULL, teljesites date NOT NULL, mennyiseg numeric(14,4) NOT NULL, mettol int4 ); create view megrendeles_tetele_eddigi as select mt.megrendeles, mt.tetelszam, mt.sorrend, mt.kulso_cikk, mt.cikk, mt.minoseg, mt.teljesites, mt.mennyiseg, mm.sorszam from megrendeles_tetele as mt, megrendeles_modositasa as mm where (mm.sorszam mettol and mm.sorszam = modositas and archiv) or (mm.sorszam = modositas and not archiv) and mt.megrendeles = mm.megrendeles group by mt.megrendeles, tetelszam, sorrend, kulso_cikk, cikk, minoseg, teljesites, mennyiseg, sorszam; create view megrendeles_tetele_ervenyes as select mr.az, mr.az_jel, mr.allapot, mr.megrendelo, mr.szallito, (select keretrendeles.az_jel from keretrendeles where az=mr.keretrendeles) as keretrendeles, mr.keretrendeles as keretrendeles_az, mt.teljesites, mt.cikk, mt.kulso_cikk, mt.minoseg, mt.mennyiseg from megrendeles mr, megrendeles_tetele_eddigi mt where mr.az = mt.megrendeles and mt.sorszam = mr.utolso_lezart_modositas; \i rels-views.pgsql \i rows.pgsql select az, (select mennyiseg from megrendeles_tetele_ervenyes where cikk=cikk.az) from cikk; select az, (select az from megrendeles_tetele_ervenyes where cikk=cikk.az) from cikk; select az, (select cikk.az from megrendeles_tetele_ervenyes where cikk=cikk.az) from cikk; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: New Linux xfs/reiser file systems
[EMAIL PROTECTED] (Trond Eivind Glomsrød) writes: Ken Hirsch [EMAIL PROTECTED] writes: I don't have a machine with XFS installed and it will be at least a week before I could get around to a build. Any volunteers? I think I could do that... any useful benchmarks to run? In lack of bigger benchmarks, I tried postgresql 7.1 on a Red Hat Linux 7.1 system with the SGI XFS modifications. The differences were very small. DB connection startup: 0.01user 0inputs+0outputs (208major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE (1 xact): real0m5.871s : user0m0.990s : sys 0m0.460s : 8192 INSERTs INTO SIMPLE (8192 xacts): real0m13.347s : user0m1.420s : sys 0m0.530s : Create INDEX on SIMPLE: 0.01user 0inputs+0outputs (209major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE with INDEX (1 xact): real0m6.938s : user0m1.160s : sys 0m0.420s : 8192 random INDEX scans on SIMPLE (1 xact): real0m12.831s : user0m1.700s : sys 0m0.970s : ORDER BY SIMPLE: 0.07user 0inputs+0outputs (214major+69minor)pagefaults 0swaps : DB connection startup: 0.02user 0inputs+0outputs (208major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE (1 xact): real0m5.652s : user0m1.080s : sys 0m0.530s : 8192 INSERTs INTO SIMPLE (8192 xacts): real0m13.021s : user0m1.280s : sys 0m0.380s : Create INDEX on SIMPLE: 0.01user 0inputs+0outputs (209major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE with INDEX (1 xact): real0m6.912s : user0m1.100s : sys 0m0.530s : 8192 random INDEX scans on SIMPLE (1 xact): real0m12.745s : user0m1.660s : sys 0m0.940s : ORDER BY SIMPLE: 0.04user 0inputs+0outputs (214major+70minor)pagefaults 0swaps : DB connection startup: 0.01user 0inputs+0outputs (208major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE (1 xact): real0m5.649s : user0m1.000s : sys 0m0.540s : 8192 INSERTs INTO SIMPLE (8192 xacts): real0m13.115s : user0m1.390s : sys 0m0.490s : Create INDEX on SIMPLE: 0.01user 0inputs+0outputs (209major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE with INDEX (1 xact): real0m6.827s : user0m1.240s : sys 0m0.560s : 8192 random INDEX scans on SIMPLE (1 xact): real0m12.653s : user0m1.790s : sys 0m0.830s : ORDER BY SIMPLE: 0.01user 0inputs+0outputs (214major+69minor)pagefaults 0swaps : -- Trond Eivind Glomsrød Red Hat, Inc. ---(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] Denying user to create new tables
Hello, All How I prevent a new user to create new tables in a Data Base ? The Data Base is owned by postgres and I need that only the postgres user can create new tables ... --- Where are the default messages thats appears when the Referentian Integrity is violated ? I need change this message to portuguese... regards, tulio oliveira -- Um velho homem sábio disse uma vez: Quando você atualiza um exploit, você é bom. Quando você é o primeiro a hackear cada sucessiva versão de um produto que roda em milhões de computadores pela Internet, você cria uma Dinastia. ---(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] A problem with new pg_dump
At 11:22 7/05/01 -0400, Tom Lane wrote: Do you need a quick lecture on CVS branch management? That would be sensible. 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
CVS branch management (was Re: [HACKERS] A problem with new pg_dump)
Philip Warner [EMAIL PROTECTED] writes: At 11:22 7/05/01 -0400, Tom Lane wrote: Do you need a quick lecture on CVS branch management? That would be sensible. OK, some quick notes for those with commit privileges: If you just do basic cvs checkout, cvs update, cvs commit, then you'll always be dealing with the HEAD version of the files in CVS. That's what you want for development, but if you need to patch past stable releases then you have to be able to access and update the branch portions of our CVS repository. We normally fork off a branch for a stable release just before starting the development cycle for the next release. The first thing you have to know is the branch name for the branch you are interested in getting at. Unfortunately Marc has been less than 100% consistent in naming the things. One way to check is to apply cvs log to any file that goes back a long time, for example HISTORY in the top directory: $ cvs log HISTORY | more RCS file: /home/projects/pgsql/cvsroot/pgsql/HISTORY,v Working file: HISTORY head: 1.106 branch: locks: strict access list: 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 keyword substitution: kv total revisions: 129; selected revisions: 129 More---q Unfortunately cvs log isn't all that great about distinguishing branches from tags --- it calls 'em all symbolic names. (A tag just marks a specific timepoint across all files --- it's essentially a snapshot whereas a branch is a changeable fileset.) Rule of thumb is that names attached to four-number versions where the third number is zero represent branches, the others are just tags. Here we can see that the extant branches are REL7_1_STABLE REL7_0_PATCHES REL6_5_PATCHES The next commit to the head will be revision 1.107, whereas any changes committed into the REL7_1_STABLE branch will have revision numbers like 1.106.2.*, corresponding to the branch number 1.106.0.2 (don't ask where the zero went...). OK, so how do you do work on a branch? By far the best way is to create a separate checkout tree for the branch and do your work in that. Not only is that the easiest way to deal with CVS, but you really need to have the whole past tree available anyway to test your work. (And you *better* test your work. Never forget that dot-releases tend to go out with very little beta testing --- so whenever you commit an update to a stable branch, you'd better be doubly sure that it's correct.) Normally, to checkout the head branch, you just cd to the place you want to contain the toplevel pgsql directory and say cvs ... checkout pgsql To get a past branch, you cd to whereever you want it and say cvs ... checkout -r BRANCHNAME pgsql For example, just a couple days ago I did mkdir ~postgres/REL7_1 cd ~postgres/REL7_1 cvs ... checkout -r REL7_1_STABLE pgsql and now I have a maintenance copy of 7.1.*. When you've done a checkout in this way, the branch name is sticky: CVS automatically knows that this directory tree is for the branch, and whenever you do cvs update or cvs commit in this tree, you'll fetch or store the latest version in the branch, not the head version. Easy as can be. So, if you have a patch that needs to apply to both the head and a recent stable branch, you have to make the edits and do the commit twice, once in your development tree and once in your stable branch tree. This is kind of a pain, which is why we don't normally fork the tree right away after a major release --- we wait for a dot-release or two, so that we won't have to double-patch the first wave of fixes. Any questions? (See the CVS manual for details on these commands, of course.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] create database name with location = 'path';
mlw [EMAIL PROTECTED] writes: template1=# create database fubar with location = '/tmp' ; ERROR: CREATE DATABASE: could not link '/postgres/data/base/12523613' to '/tmp/base/12523613': Operation not permitted Try using a filesystem that supports symbolic links ... 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: CVS branch management (was Re: [HACKERS] A problem with new pg_dump)
Tom Lane [EMAIL PROTECTED] writes: Unfortunately cvs log isn't all that great about distinguishing branches from tags --- it calls 'em all symbolic names. Minor addition to this: you can distinguish branches and tags by using `cvs status -v'. (Historical note: CVS was originally implemented as shell scripts on top of RCS. The .0 syntax was magic which CVS used to indicate a branch as opposed to a revision tag. The output of `cvs log' is simply the output of `rlog' on the underlying RCS file. `cvs status' is not based on an existing RCS command.) Ian ---(end of broadcast)--- TIP 734: Often statistics are used as a drunken man uses lampposts -- for support rather than illumination. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] Duplicate constraint names in 7.0.3
OK, I have modifed heap.c so that it won't automatically generate duplicate constraint names. I have _not_ compiled this yet, as it's a bit of a pain for me cos I don't have bison, etc. However, it looks good to me, and if someone else wants to test it and then maybe think about if the patch is necessary that's fine by me. If no-one wants to test it, I will eventually get around to testing it myself. Given that this is my first code patch for Postgres - I should treat it with caution! Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher Kings-Lynne Sent: Friday, 4 May 2001 12:33 PM To: Hackers Subject: RE: [HACKERS] Duplicate constraint names in 7.0.3 A reasonable interpretation of DROP CONSTRAINT foo is to drop *all* constraints named foo on the target table. Then it should probably be a good thing to avoid the automatic generation of duplicate names? I might take a look at that, actually... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl heap.diff ---(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
I don't mind contributing the script and schema that I used, but one thing I failed to mention in my first post is that the first thing the script does is open connections to 256 databases (all on this same machine), and the transactions are relatively evenly dispersed among the 256 connections. The test was originally written to try out an idea to allow scalability by partitioning the data into seperate databases (which could eventually each live on its own server). If you are interested I can modify the test to use only one database and rerun the same tests this weekend. I modified my test script to use just one (instead of 256) databases to be more representative of a common installation. Then I ran more tests under both ext2 and reiserfs. The summary follows. Short answer is that the differences are much smaller than under the first test, but ext2 is still faster. -- Joe case rfs_fdatasyncext_fdatasyncrfs_fdatasync ext_fdatasyncrfs_fdatasyncext_fdatasync fstabsync,noatime sync,noatime noatime noatime defaults defaults starting # tup 70k 70k 70k 70k 70k 70k total time (min) 12.1011.7711.8311.43 11.8811.42 cpu util % 90-94% 95-98% 90-95% 95-99% 90-95% 95-99% ram - stable cpu 42M 42M 42M 42M 42M 42M ram - final 52M 52M 52M 52M 52M 52M avg trans/sec 1 tup13.7714.1614.0814.58 14.0314.60 5000 tup 13.7014.0813.9714.71 13.9314.75 1000 tup 11.3611.6311.6313.33 11.6313.51 Notes: 1. rfs_fdatasync: data and wal on rieserfs with wal_sync_method = fdatasync 2. ext_fdatasync: data and wal on ext2 with wal_sync_method = fdatasync 3. starting # tup: the database was pre-seeded with 70k tuples. I made a tarball of the starting database and refreshed the pgsql/data filestructure before each test to ensure a good comparison. 4. cpu utilization + ram - stable cpu + ram - final: I eyeballed top while the test was running. In general cpu % increased steadily through the first 1500 or so transactions, along with ram usage. At the point when cpu utilization stabilized, ram was pretty consistently at 42M. From there, cpu util % varied in the ranges noted, while ram usage slowly increased to 52M. It seemed pretty linear in that I could estimate the number of transactions already processes based on ram usage. 5. avg trans/sec: These represent the total transactions/total elapsed time at the given number of transactions (as opposed to some instantaneous value at that point in time). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
AW: [HACKERS] Re: New Linux xfs/reiser file systems
I think it's worth noting that Oracle has been petitioning the kernel developers for better raw device support: in other words, the ability to write directly to the hard disk and bypassing the filesystem all together. But there could be other reasons why Oracle would want to do raw stuff. The reasons are: 1. Most Unixen now have shared (between several machines) raw devices Oracle needs this for their shared everything Parallel Server. Only 2 Unixen that I know of have shared filesystems (IBM gpfs and Sun Veritas) (both are rather new) 2. The allocation time for raw devices is by far better (near instantaneous) than creating preallocated files in a fs. Providing 1 Tb of raw devices is a task of minutes, creating 1 Tb filsystems with preallocated 2 Gb files is a task of hours at best. 3. absolute control over writes and page location (you don't want interleaved pages) 4. Efficient use of buffer memory. Usual use of filesystems buffers the disk pages twice, one copy in the db buffer pool, one in the OS file cache. 5. async raw IO (most Unixes provide async raw IO on raw devices, only some provide raw IO on filesystem files). (async IO has 2 advantages: CPU work can be done while waiting for IO and IO can complete within one OS timeslice (20 us). This is possible with modern disk systems, that have large caches) Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: [GENERAL] v7.1.1 Branched, Packaged and Released ...
Does this mean that we have officially released 7.1.1? I could not
find any statements regarding 7.1.1 on the web pages...
--
Tatsuo Ishii
This is just a quick announcement that we have now branched off v7.1.x
from the main development tree, and are starting to dive into development
of v7.2 ...
There have been several changes since v7.1 was released, including:
Fix for numeric MODULO operator (Tom)
pg_dump fixes (Philip)
pg_dump can dump 7.0 databases (Philip)
readline 4.2 fixes (Peter E)
JOIN fixes (Tom)
AIX, MSWIN, VAX,N32K fixes (Tom)
Multibytes fixes (Tom)
Unicode fixes (Tatsuo)
Optimizer improvements (Tom)
Fix for whole tuples in functions (Tom)
Fix for pg_ctl and option strings with spaces (Peter E)
ODBC fixes (Hiroshi)
EXTRACT can now take string argument (Thomas)
Python fixes (Darcy)
With more details available in the ChangeLog file ...
This release does not require a dump/restore from v7.1, it is purely a
maintaince release ...
Any bugs please report them to [EMAIL PROTECTED] ...
RPMs and DEBs should be available soon ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[HACKERS] Re: [GENERAL] v7.1.1 Branched, Packaged and Released ...
takes Vince a day or two to catch up ... yes, we are officially released,
and Tom just dump'd some major stats changes into HEAD ...
On Mon, 7 May 2001, Tatsuo Ishii wrote:
Does this mean that we have officially released 7.1.1? I could not
find any statements regarding 7.1.1 on the web pages...
--
Tatsuo Ishii
This is just a quick announcement that we have now branched off v7.1.x
from the main development tree, and are starting to dive into development
of v7.2 ...
There have been several changes since v7.1 was released, including:
Fix for numeric MODULO operator (Tom)
pg_dump fixes (Philip)
pg_dump can dump 7.0 databases (Philip)
readline 4.2 fixes (Peter E)
JOIN fixes (Tom)
AIX, MSWIN, VAX,N32K fixes (Tom)
Multibytes fixes (Tom)
Unicode fixes (Tatsuo)
Optimizer improvements (Tom)
Fix for whole tuples in functions (Tom)
Fix for pg_ctl and option strings with spaces (Peter E)
ODBC fixes (Hiroshi)
EXTRACT can now take string argument (Thomas)
Python fixes (Darcy)
With more details available in the ChangeLog file ...
This release does not require a dump/restore from v7.1, it is purely a
maintaince release ...
Any bugs please report them to [EMAIL PROTECTED] ...
RPMs and DEBs should be available soon ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
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] \c connects as another user instead I want in psql
Really? We are removing usesysid? Seems the admin will no longer be able to choose the users id, right? Not that this was ever useful. Except for re-adding users. Yes. In theory, the correct answer to that is to add referential integrity checks that prevent you from dropping a user that still owns any objects. In practice, this is impractical because users span a whole database installation. We have no reasonable way to check whether the user owns objects in other databases that cannot be seen from the DB where we are issuing the DROP USER command. Therefore, for the foreseeable future it will be important to be able to reverse a DROP USER command --- ie, recreate a user with the same user identifier previously used. After thinking about that for awhile, I am inclined to change my previous position: we should not switch over to using the OIDs of pg_shadow rows as user identifiers. usesysid should continue to exist. Ditto for groups --- grosysid can't go away either. I think the original motivation for wanting to eliminate these columns was that we need usesysid and grosysid to be distinct (can't use the same ID for both a user and a group). Using OIDs as IDs would fix that, but it's overkill. Wouldn't it be sufficient to use an installation-wide sequence object to assign new IDs for new users and groups? We have no such animals at the present, but I see no reason why we couldn't make one. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] File system performance and pg_xlog
Marko Kreen [EMAIL PROTECTED] writes: On Sat, May 05, 2001 at 10:10:33PM -0400, mlw wrote: I think it is simpler problem than that. Postgres, with fsync enabled, does a lot of work trying to maintain data integrity. It is logical to conclude that a file system that does as little as possible would almost always perform better. Regardless of what the file system does, eventually it writes blocks of data to sectors on a disk. But there's more, when PostgreSQL today 'uses a fs' it also get all the caching/optimizing algorithms in os kernel 'for free'. Many databases use their own data volume management. I am not suggesting that anyone create a new file system, but after performing some tests, I am really starting to see why products like oracle manage their own table spaces. If one looks at the FAT file system with an open mind and a clear understanding of how it will be used, some small modifications may make it the functional equivalent of a managed table space volume, at least under Linux. Are you talking about new in-kernel fs? Lets see, how many os'es PostgreSQL today supports? If you're using raw devices on Linux and get a win there, it's a win for Postgresql on Linux. This is important for everyone using it on this platform (probably a big chunk of the users). And who uses all the new features and performance enhancements done in other ways? It all comes down to if it actually would give a performance boost, how much work it is and if someone wants to do it. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: [GENERAL] v7.1.1 Branched, Packaged and Released ...
On Mon, 7 May 2001, The Hermit Hacker wrote:
takes Vince a day or two to catch up ... yes, we are officially released,
and Tom just dump'd some major stats changes into HEAD ...
But this time Vince had all the info online in a matter of minutes
after receiving Marc's announcement. It does take a while for the
mirrors to get the updates tho. www.ca.postgresql.org *always* has
the most current info.
On Mon, 7 May 2001, Tatsuo Ishii wrote:
Does this mean that we have officially released 7.1.1? I could not
find any statements regarding 7.1.1 on the web pages...
--
Tatsuo Ishii
This is just a quick announcement that we have now branched off v7.1.x
from the main development tree, and are starting to dive into development
of v7.2 ...
There have been several changes since v7.1 was released, including:
Fix for numeric MODULO operator (Tom)
pg_dump fixes (Philip)
pg_dump can dump 7.0 databases (Philip)
readline 4.2 fixes (Peter E)
JOIN fixes (Tom)
AIX, MSWIN, VAX,N32K fixes (Tom)
Multibytes fixes (Tom)
Unicode fixes (Tatsuo)
Optimizer improvements (Tom)
Fix for whole tuples in functions (Tom)
Fix for pg_ctl and option strings with spaces (Peter E)
ODBC fixes (Hiroshi)
EXTRACT can now take string argument (Thomas)
Python fixes (Darcy)
With more details available in the ChangeLog file ...
This release does not require a dump/restore from v7.1, it is purely a
maintaince release ...
Any bugs please report them to [EMAIL PROTECTED] ...
RPMs and DEBs should be available soon ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
--
==
Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directoryhttp://www.camping-usa.com
Online Giftshop Superstorehttp://www.cloudninegifts.com
==
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] File system performance and pg_xlog
[EMAIL PROTECTED] (Trond Eivind =?iso-8859-1?q?Glomsr=F8d?=) writes: If you're using raw devices on Linux and get a win there, it's a win for Postgresql on Linux. ... It all comes down to if it actually would give a performance boost, how much work it is and if someone wants to do it. No, those are not the only considerations. If the feature is not portable then we also have to consider how much of a headache it'll be to maintain in parallel with a more portable approach. We might reject such a feature even if it's a clear win for Linux, if it creates enough problems elsewhere. Postgres is *not* a Linux-only application, and I trust it never will be. regards, tom lane PS: that's not meant to reject the idea out-of-hand; perhaps the benefits will prove to be so large that we will want to do it anyway. I'm just trying to counter what appears to be a narrowly platform-centric view of the issues. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] typo in psql's help
On Mon, 7 May 2001, Bruce Momjian wrote: postgresql= \h create table Command: CREATE TABLE Description: Creates a new table Syntax: CREATE [ TEMPORARY | TEMP ] TABLE table ( column type [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ] ] ^ This should be a ] | Vince, I can't find this anywhere. What version is this? I bet we already fixed it. In fact, I think I remember seeing the fix a while ago. Did someone delete this one from the database? I just went to close it out and it's gone. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] typo in psql's help
On Mon, 7 May 2001, Bruce Momjian wrote: postgresql= \h create table Command: CREATE TABLE Description: Creates a new table Syntax: CREATE [ TEMPORARY | TEMP ] TABLE table ( column type [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ] ] ^ This should be a ] | Vince, I can't find this anywhere. What version is this? I bet we already fixed it. In fact, I think I remember seeing the fix a while ago. Yeah, I got a note from Peter saying it was fixed in 7.1. Silly me, I thought hub was running 7.1, psql must be 7.0.x. --- postgresql= select version(); version --- PostgreSQL 7.1 on i386-unknown-freebsd4.2, compiled by GCC 2.95.2 (1 row) postgresql= \h create table Command: CREATE TABLE Description: Creates a new table Syntax: CREATE [ TEMPORARY | TEMP ] TABLE table ( column type [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ] ] [, ... ] [, PRIMARY KEY ( column [, ...] ) ] [, CHECK ( condition ) ] [, table_constraint_clause ] ) [ INHERITS ( inherited_table [, ...] ) ] --- as just seen on hub a few minutes ago. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] File system performance and pg_xlog
Bruce Momjian [EMAIL PROTECTED] writes: That is a major issue for people running performance tests. For example, XFS may be slow on 2.2 kernels but not 2.4 kernels. XFS is 2.4 only, AFAIK - even the installer modifications SGI did to Red Hat Linux 7 (which is shipped with a 2.2 kernel) includes installing a 2.4pre kernel, AFAIR. -- Trond Eivind Glomsrød Red Hat, Inc. ---(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] Lisp as procedural language
Bruce Momjian [EMAIL PROTECTED] writes: Can someone explain why we have a lisp.sgml file in our docs? Seems it descripes a 3rd party Emacs interface. I don't think we should start distributing docs for software we don't distribute. Can I remove it? Only if you move the pointer to someplace more appropriate (don't we have somewhere on the website with links to outside software?) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] typo in psql's help
Vince Vielhaber [EMAIL PROTECTED] writes: Yeah, I got a note from Peter saying it was fixed in 7.1. Silly me, I thought hub was running 7.1, psql must be 7.0.x. Looks like there's an older psql in your PATH. You could make sure with psql -V. 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
[HACKERS] Re: [GENERAL] v7.1.1 Branched, Packaged and Released ...
Lieven Van Acker [EMAIL PROTECTED] writes: Are the nested views permission problems fixed in this release? If so, a dump IS necessary because of a change rule creation routines. If you're running into that issue, you might want to drop and recreate the affected views/rules. That's a far cry from a database dump and reload, though. At least for them as has gigabytes of data ;-) 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] NOCREATETABLE patch (was: Re: Please, help!(about Postgres))
Bruce Momjian [EMAIL PROTECTED] writes: Can someone remind me what we are going to do with this? I'd like to see some effort put into implementing the SQL-standard privilege model, rather than adding yet more ad-hoc user properties. The more of these we make, the more painful it's going to be to meet the spec later. Possibly, after we have the SQL semantics we'll still feel that we need some additional features ... but how about spec first and extensions afterwards? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: [GENERAL] v7.1.1 Branched, Packaged and Released ...
takes Vince a day or two to catch up ... yes, we are officially released, and Tom just dump'd some major stats changes into HEAD ... On Mon, 7 May 2001, Tatsuo Ishii wrote: Does this mean that we have officially released 7.1.1? I could not find any statements regarding 7.1.1 on the web pages... Thanks. I'm just wondering which version should be targeted in my new book. I think I could write based on 7.1.1. -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Isn't pg_statistic a security hole?
Bruce Momjian [EMAIL PROTECTED] writes: It seems to me that only superusers should be allowed to read the pg_statistic table. Or am I overreacting? Comments? You are not overreacting. Imagine a salary column. I can imagine max/min being quite interesting. A fine example, indeed ;-) I doubt it is worth letting non-super users see values in that table. Their only value is in debugging the optimizer, which seems like a super-user job anyway. Well, mumble. I routinely ask people who're complaining of bad plans for extracts from their pg_statistic table. I don't foresee that need vanishing any time soon :-(. The idea of a view seemed nice, in part because it could be set up to give all the useful info with a simple select * from pg_statview where relname = 'foo'; rather than the messy three-way join you have to type now. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] typo in psql's help
On Mon, 7 May 2001, Tom Lane wrote: Vince Vielhaber [EMAIL PROTECTED] writes: Yeah, I got a note from Peter saying it was fixed in 7.1. Silly me, I thought hub was running 7.1, psql must be 7.0.x. Looks like there's an older psql in your PATH. You could make sure with psql -V. Yup. 7.0.3. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.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
Re: [HACKERS] MULTIBYTE and SQL_ASCII (was Re: [JDBC] Re: A bug with pgsql 7.1/jdbc and non-ascii (8-bit) chars?)
Thus I would be happy if getdatabaseencoding() returned 'UNKNOWN' or something similar when in fact it doesn't know what the encoding is (i.e. when not compiled with multibyte). Is that ok for Java? I thought Java needs to know the encoding beforehand so that it could convert to/from Unicode. That is actually the original issue that started this thread. If you want the full thread see the jdbc mail archive list. A user was complaining that when running on a database without multibyte enabled, that through psql he could insert and retrieve 8bit characters, but in jdbc the 8bit characters were converted to ?'s. Still I don't see what you are wanting in the JDBC driver if PostgreSQL would return UNKNOWN indicating that the backend is not compiled with MULTIBYTE. Do you want exact the same behavior as prior 7.1 driver? i.e. reading data from the PostgreSQL backend, assume its encoding default to the Java client (that is set by locale or something else) and convert it to UTF-8. If so, that would make sense to me... -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
