Re: [HACKERS] Accessing schema data in information schema
Ühel kenal päeval, K, 2006-03-22 kell 21:50, kirjutas Andrew Dunstan: Tom Lane said: Darcy Buskermolen [EMAIL PROTECTED] writes: On Wednesday 22 March 2006 13:11, Tom Lane wrote: (Thinks a bit...) Maybe it would work for pg_sequence to be a real catalog with a row per sequence, and we also create a view named after the sequence that simply selects from pg_sequence with an appropriate WHERE condition. I'd think that would be a workable solution, with documentation notes that this will be deprecated in favor of information_schema in an upcoming release ? Yeah, we could consider the views a transitional thing, and get rid of them after a release or two. Tell people to change over to either look in the pg_sequence catalog, or use the information_schema view. Does that view expose everything that there is, though, or will we have proprietary extensions that are not in SQL2003? What happens to sequence ACLs? perhaps we can keep pg_class part of seqs and just make the pg_class.relfilenode to point to row oid in pg_sequence table ? - Hannu ---(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] Where does the time go?
On Wed, Mar 22, 2006 at 8:59 pm, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: You didn't show us the explain analyze results, The below is cut paste directly from a psql run without editing. bigbird= UPDATE User SET isActive = TRUE WHERE countyNo = 13 and isActive = FALSE; UPDATE 0 bigbird= cluster User; CLUSTER bigbird= vacuum analyze User; VACUUM bigbird= select current_timestamp; now 2006-03-22 16:30:55.875-06 (1 row) bigbird= explain analyze bigbird- UPDATE User bigbird- SET isActive = FALSE bigbird- WHERE countyNo = 13 bigbird- AND (userId, countyNo) NOT IN (SELECT userId, countyNo FROM UserAuthority) bigbird- AND (userId, countyNo) NOT IN (SELECT userId, countyNo FROM UserDivision) bigbird- AND (userId, countyNo) NOT IN (SELECT userId, countyNo FROM UserCtofcAuthority); QUERY PLAN --- Index Scan using User_pkey on User (cost=2365.43..2499.34 rows=44 width=126) (actual time=145.968..147.061 rows=153 loops=1) Index Cond: ((countyNo)::smallint = 13) Filter: ((NOT (hashed subplan)) AND (NOT (hashed subplan)) AND (NOT (hashed subplan))) SubPlan - Seq Scan on UserCtofcAuthority (cost=0.00..332.65 rows=15565 width=14) (actual time=0.003..10.105 rows=15565 loops=1) - Seq Scan on UserDivision (cost=0.00..326.65 rows=15765 width=14) (actual time=0.003..10.409 rows=15764 loops=1) - Seq Scan on UserAuthority (cost=0.00..1451.24 rows=70624 width=14) (actual time=0.007..45.823 rows=70626 loops=1) Total runtime: 150.340 ms (8 rows) bigbird= select current_timestamp; now 2006-03-22 16:30:56.046-06 (1 row) bigbird= UPDATE User SET isActive = TRUE WHERE countyNo = 13 and isActive = FALSE; UPDATE 153 bigbird= cluster User; CLUSTER bigbird= vacuum analyze User; VACUUM bigbird= select current_timestamp; now 2006-03-22 16:30:56.203-06 (1 row) bigbird= UPDATE User bigbird- SET isActive = FALSE bigbird- WHERE countyNo = 13 bigbird- AND (userId, countyNo) NOT IN (SELECT userId, countyNo FROM UserAuthority) bigbird- AND (userId, countyNo) NOT IN (SELECT userId, countyNo FROM UserDivision) bigbird- AND (userId, countyNo) NOT IN (SELECT userId, countyNo FROM UserCtofcAuthority); UPDATE 153 bigbird= select current_timestamp; now 2006-03-22 16:30:56.343-06 (1 row) bigbird= UPDATE User SET isActive = TRUE WHERE countyNo = 13 and isActive = FALSE; UPDATE 153 bigbird= cluster User; CLUSTER bigbird= vacuum analyze User; VACUUM bigbird= select current_timestamp; now 2006-03-22 16:30:56.484-06 (1 row) bigbird= explain analyze bigbird- UPDATE User bigbird- SET isActive = FALSE bigbird- WHERE countyNo = 13 bigbird- AND NOT EXISTS (SELECT * FROM UserAuthority a where a.countyNo = User.countyNo and a.userId = User.userId) bigbird- AND NOT EXISTS (SELECT * FROM UserDivision b where b.countyNo = User.countyNo and b.userId = User.userId) bigbird- AND NOT EXISTS (SELECT * FROM UserCtofcAuthority c where c.countyNo = User.countyNo and c.userId = User.userId); QUERY PLAN - Index Scan using User_pkey on User (cost=0.00..3650.67 rows=42 width=111) (actual time=0.057..5.722 rows=153 loops=1) Index Cond: ((countyNo)::smallint = 13) Filter: ((NOT (subplan)) AND (NOT (subplan)) AND (NOT (subplan))) SubPlan - Index Scan using UserCtofcAuthority_pkey on UserCtofcAuthority c (cost=0.00..3.48 rows=1 width=50) (actual time=0.004..0.004 rows=0 loops=153) Index Cond: (((countyNo)::smallint = ($0)::smallint) AND ((userId)::bpchar = ($1)::bpchar)) - Index Scan using UserDivision_pkey on UserDivision b (cost=0.00..3.53 rows=1 width=42) (actual time=0.006..0.006 rows=0 loops=156) Index Cond: (((countyNo)::smallint = ($0)::smallint) AND ((userId)::bpchar = ($1)::bpchar)) - Index Scan using UserAuthority_pkey on UserAuthority a (cost=0.00..3.60 rows=1 width=42) (actual time=0.007..0.007 rows=1 loops=341) Index Cond: (((countyNo)::smallint = ($0)::smallint) AND ((userId)::bpchar = ($1)::bpchar)) Total runtime: 9.136 ms (11 rows) bigbird= select current_timestamp; now 2006-03-22 16:30:56.546-06 (1 row) bigbird= UPDATE User SET isActive = TRUE WHERE countyNo = 13 and isActive = FALSE; UPDATE 153 bigbird= cluster User;
Re: [HACKERS] Accessing schema data in information schema
On Thu, Mar 23, 2006 at 12:10:54AM +0200, Hannu Krosing wrote: ??hel kenal p??eval, K, 2006-03-22 kell 16:11, kirjutas Tom Lane: Peter Eisentraut [EMAIL PROTECTED] writes: How does one get at the missing fields. The only way I know is selecting from the sequence, but how does one work this into this query? Somehow it seems that these things should be stored in a real system catalog. Yeah. I've occasionally toyed with the idea that sequences should be rows in a single catalog instead of independent tables as they are now. This would make for a much smaller disk footprint (with consequent I/O savings) and would solve problems like the one you have. Would it not make page locking problems much worse with all get_next()'s competeing to update the same page? What about bumping up the default cache setting a bit? Even going to a fairly conservative value, like 10 or 25 would probably make a huge difference. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] On vacation
On Wed, Mar 22, 2006 at 09:43:24PM -0500, Bruce Momjian wrote: I am heading on vacation starting tomorrow/Thursday, and return the following Thursday, March 30th. I will be in Florida with my family. Speaking of trips, I'm currently in Brussels, and will be here until next Wednesday. Anyone want to get together for beers? I'm willing to travel outside the city as well, so long as it doesn't cost a fortune. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
On Thu, Mar 23, 2006 at 12:39:52AM -0500, Tom Lane wrote: Akshat Nair [EMAIL PROTECTED] writes: Can I get the grammar for the explain output? There isn't one, it's just text and subject to change at a moment's notice :-(. The past proposals that we format it a bit more rigidly have so far foundered for lack of a workable definition of what the structure should be. It's still an open problem to devise that definition. Structure for the human-consumable output or for something that would be machine-parsed? ISTM it would be best to keep the current output as-is, and provide some other means for producing machine-friendly output, presumably in a table format. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Where does the time go?
Kevin Grittner [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: You didn't show us the explain analyze results, The below is cut paste directly from a psql run without editing. OK, so the two plans do indeed have much different node execution counts. The EXPLAIN ANALYZE instrumentation overhead is basically proportional to (rows+1)*loops summed over all the nodes in the plan, so I count about 102112 node executions in the NOT IN plan versus 1145 in the NOT EXISTS plan --- in other words, 100x more overhead for the former. The run time of the NOT IN query, as measured by elapsed time between SELECT CURRENT_TIMESTAMP executions, increased by 31 ms. Works out to about 30 microsec per node execution, which seems a bit high for modern machines ... and the coarse quantization of the CURRENT_TIMESTAMP results is odd too. What platform is this on exactly? That leaves an unaccounted difference between the time reported by EXPLAIN ANALYZE and the timestamp elapsed time of (on average) 9 ms for the NOT IN form of the query, and 41 ms for the NOT EXISTS for of the query. (In the run shown above, it's higher.) I'm guessing that this is the time spent in parsing and planning the query. Parse/plan time is one component, and another is the time spent by EXPLAIN preparing its output display, which is not an area we've spent any time at all optimizing --- I wouldn't be surprised if it's kinda slow. However, these plans are relatively similar in terms of the complexity of the display, so it is odd that there'd be so much difference. What is the best way to see where this time is going? Profiling with gprof or some such tool might be educational. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Accessing schema data in information schema
Plan C would be to say that we don't need to preserve SELECT * FROM seqname, but I'll bet there would be some hollering. I'd like to hear this hollering first, before we create tons of views :-) Imho it is not a problem to remove it, I am for Plan C. (Those with need for the select can still create their view by hand. A release note would be sufficient imho.) Of course if we still need one row in pg_class for the ACL's, that row might as well be a view. Andreas ---(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] Accessing schema data in information schema
Zeugswetter Andreas DCP SD [EMAIL PROTECTED] writes: Of course if we still need one row in pg_class for the ACL's, that row might as well be a view. Yeah, view or view-like thingie. Given the thought that we need both transactional and nontransactional state for a sequence, I'm kind of inclined to leave the transactional data in pg_class. We could still imagine putting the nontransactional state into a new pg_sequence catalog indexed by, say, the pg_class OID of the sequences. OTOH I'm not sure how much that buys for Peter's problem --- it might be better for him just to invent some functions that can grab the required data given the sequence OID. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Accessing schema data in information schema
Tom Lane wrote: Given the thought that we need both transactional and nontransactional state for a sequence, I'm kind of inclined to leave the transactional data in pg_class. We could still imagine putting the nontransactional state into a new pg_sequence catalog indexed by, say, the pg_class OID of the sequences. OTOH I'm not sure how much that buys for Peter's problem --- it might be better for him just to invent some functions that can grab the required data given the sequence OID. Yes, this seems a lot of lifting for a fairly small need. If there aren't other advantages, a simple function or two seems a better way to go, and then there are no legacy problems. cheers andrew ---(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] Static build of psql with readline support
To the GP, adding -lncurses (or rather the static equivalent) to your
link line should solve it. But if you include any other libraries like
ssl or kerberos be prepared to add a lot more.
With -lncurses or -lcurses I still can't get this to work. I add it to
the ${CC} line, right?
Chris
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Where does the time go?
On Thu, Mar 23, 2006 at 11:27 am, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: The run time of the NOT IN query, as measured by elapsed time between SELECT CURRENT_TIMESTAMP executions, increased by 31 ms. Works out to about 30 microsec per node execution, which seems a bit high for modern machines ... and the coarse quantization of the CURRENT_TIMESTAMP results is odd too. What platform is this on exactly? This is a smaller machine with a copy of the full production database. A single 3.6 GHz Xeon with 4 GB RAM running Windows Server 2003. It was being used to test update scripts before applying them to the production machines. I stumbled across a costing issue I thought worth posting, and in the course of gathering data noticed this time difference I didn't understand. What is the best way to see where this time is going? Profiling with gprof or some such tool might be educational. Our builds are all done with --enable-debug, but this machine doesn't even have msys installed. I'll try to put together some way to profile it on this machine or some other. (It might be easier to move it to a Linux machine and confirm the problem there, then profile.) Thanks, -Kevin ---(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] Where does the time go?
On Thu, Mar 23, 2006 at 11:27 am, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Profiling with gprof or some such tool might be educational. I've never used gprof before, and from a quick scan of the info, it appears that I need to compile and link a special version of the software to generate the file that gprof needs. Is this correct? Does it work on a Windows build, or will I need to use Linux? Any tips? Thanks, all. -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Where does the time go?
On Thu, Mar 23, 2006 at 12:29:27PM -0600, Kevin Grittner wrote: Works out to about 30 microsec per node execution, which seems a bit high for modern machines ... and the coarse quantization of the CURRENT_TIMESTAMP results is odd too. What platform is this on exactly? This is a smaller machine with a copy of the full production database. A single 3.6 GHz Xeon with 4 GB RAM running Windows Server 2003. It was being used to test update scripts before applying them to the production machines. I stumbled across a costing issue I thought worth posting, and in the course of gathering data noticed this time difference I didn't understand. This may be the cause of the problem (windows). On UNIX platforms the gettimeofday() call is used to calculate the timings in both cases. On Windows the EXPLAIN ANALYZE measures time in a different way using the CPU counters. It uses the interface but it will run into issues if the CPU speed is not properly calculated or there is drift between the different CPUs. Here's one person who claims that the performance counter frequency is often wrong: http://archives.postgresql.org/pgsql-hackers-win32/2005-03/msg00063.php It's also been pointed out before that the code actually divides by the wrong number (it uses GetTimerFrequency() rather than QueryPerformenceFrequency()). If you can find the values of these two functions on your machine, see how it compares to your actual clock speed. 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. signature.asc Description: Digital signature
Re: [HACKERS] Static build of psql with readline support
On Thu, Mar 23, 2006 at 10:31:24AM +0800, Christopher Kings-Lynne wrote:
To the GP, adding -lncurses (or rather the static equivalent) to your
link line should solve it. But if you include any other libraries like
ssl or kerberos be prepared to add a lot more.
With -lncurses or -lcurses I still can't get this to work. I add it to
the ${CC} line, right?
I'm not sure what controls it, but it's quite possible -lcurses tries
to do a dynamic link again, you may need to specify the path to the .a
file.
Note, make sure you actually have the static version installed, not all
distributions come with static versions these days...
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.
signature.asc
Description: Digital signature
Re: [HACKERS] Where does the time go?
Kevin Grittner [EMAIL PROTECTED] writes: I've never used gprof before, and from a quick scan of the info, it appears that I need to compile and link a special version of the software to generate the file that gprof needs. Is this correct? Does it work on a Windows build, or will I need to use Linux? Any tips? I dunno anything about profiling on Windows. If you don't mind moving the test case to Linux it's pretty easy: ./configure --enable-debug --whatever-other-options make PROFILE=-pg -DLINUX_PROFILE install postgres executable (On non-Linux Unixen you can omit that -D flag, but otherwise the recipe is the same.) Run the test case (you'll want a test script that does the same thing over and over, enough times to build up a decent set of statistics; I like to have about a minute's worth of accumulated CPU time in a profile run). Exit the session --- the gmon.out file will only be dumped at backend process exit. Then do gprof /path/to/postgres-executable $PGDATA/gmon.out outfile BTW, in 8.1 you want to be sure to do this with autovacuum off, else exit of the autovacuum process might clobber the gmon.out file before you can run gprof. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] [SUGGESTION] CVSync
Hello, I've been trying to get a local mirror of the cvs repository, but my connection attempts using rsync to cvsup.postgresql.org are always refused when I try to actually retrieve the pgsql-cvs collection. I tried to use rsync because cvsup is simply not an option for me. I'd like to suggest that CVSync (www.cvsync.org) be added as a means for the public to obtain a local mirror of the cvs repository. It builds on most every platform (it's written in C, no modula-3 required) and has the advantage of caching directory scan info to reduce server load -- The cached scan info is sent to the client, which compares it to its own cached scan info to decide what to download (You can set up a cron job to periodically scan the repository directories, or even set up a sophisticated system that would only scan after commits are made.). It also sends only the new diffs (optionally compressed) from the ,v files that are needed to bring the clients' repository mirror up to date. Thanks! Joel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SUGGESTION] CVSync
Joel Miller wrote: Hello, I've been trying to get a local mirror of the cvs repository, but my connection attempts using rsync to cvsup.postgresql.org are always refused when I try to actually retrieve the pgsql-cvs collection. I tried to use rsync because cvsup is simply not an option for me. I'd like to suggest that CVSync (www.cvsync.org) be added as a means for the public to obtain a local mirror of the cvs repository. It builds on most every platform (it's written in C, no modula-3 required) and has the advantage of caching directory scan info to reduce server load -- The cached scan info is sent to the client, which compares it to its own cached scan info to decide what to download (You can set up a cron job to periodically scan the repository directories, or even set up a sophisticated system that would only scan after commits are made.). It also sends only the new diffs (optionally compressed) from the ,v files that are needed to bring the clients' repository mirror up to date. As a complete out of nowhere suggestion, you might also look at csup in FreeBSD's CVS (or the project page), which is a cvsup clone in C. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SUGGESTION] CVSync
On Thu, Mar 23, 2006 at 01:32:34PM -0800, Joel Miller wrote: Hello, I've been trying to get a local mirror of the cvs repository, but my connection attempts using rsync to cvsup.postgresql.org are always refused when I try to actually retrieve the pgsql-cvs collection. I tried to use rsync because cvsup is simply not an option for me. Any particular reason why straight CVS doesn't work for you? Are you that interested in having the log comment locally? 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. signature.asc Description: Digital signature
[HACKERS] Role incompatibilities
Trying to work in the new role features into the information schema, I noticed that there might be a few incompatibilities between the implementation and what the SQL standard would like to see. The way I understand this is that, according to the SQL standard, there should be a current user and optionally a current role. A current role can be set by running SET ROLE, and that is only permissible if that role has been granted to the current user. (It seems that this must have been a direct grant, but that is less important.) The set of applicable privileges (used for permission checking) is now the privileges held by the current user, the current role, and all roles that have been granted to the current role. It seems that the inherit functionality was invented to simulate something like this but it doesn't quite do it. What we'd really need is a system where roles granted to the current user are not automatically activated but roles granted to the current role are. The inherit functionality is then only to simulate traditional groups that activate all their privileges automatically depending on who is the current user. The other problem is that using SET ROLE activates the privileges of a role but loses the privileges of the current user. In practice this may mean that it reduces your privileges while you might want to use it to augment your privileges. What both of these observations come down to is that in my estimation current user and current role should be separated. It's quite possible that I'm reading this wrong in a hurry or can't quite simulate it right, so please enlighten me. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Get explain output of postgresql in Tables
Jim C. Nasby wrote: Structure for the human-consumable output or for something that would be machine-parsed? ISTM it would be best to keep the current output as-is, and provide some other means for producing machine-friendly output, presumably in a table format. How about (well-formed) XML format? Anyone menthioned in the past threads? I guess XML is good for the explain structure. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
Satoshi Nagayasu wrote: Jim C. Nasby wrote: Structure for the human-consumable output or for something that would be machine-parsed? ISTM it would be best to keep the current output as-is, and provide some other means for producing machine-friendly output, presumably in a table format. How about (well-formed) XML format? A friend developed a patch for this. He offered to post it but I don't think there was any reaction at all. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
Alvaro Herrera wrote: Satoshi Nagayasu wrote: Jim C. Nasby wrote: Structure for the human-consumable output or for something that would be machine-parsed? ISTM it would be best to keep the current output as-is, and provide some other means for producing machine-friendly output, presumably in a table format. How about (well-formed) XML format? A friend developed a patch for this. He offered to post it but I don't think there was any reaction at all. Very interesting. I guess the machine-friendly expalin format is important for query tools, such as Visual Explain, pgAdminIII Query and so on. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SUGGESTION] CVSync
Martijn van Oosterhout kleptog@svana.org writes: Any particular reason why straight CVS doesn't work for you? Are you that interested in having the log comment locally? Personally, I'd really like to have a local repository copy, because I spend a *lot* of time with cvsweb etc --- but I'm sure my needs are several standard deviations away from the mean. So far I've been discouraged from setting up a repository by the unreasonable infrastructure needs of cvsup. So these alternatives do sound pretty interesting. Is csup protocol-compatible with cvsup? If so people could use it without Marc having to do anything. Has anyone got experience with it --- reliability, performance, etc? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Did this work in earlier version of Postgres?
Tony Caduto wrote: I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; Now it gives a error that type double does not exist. From the docs: http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-FLOAT the type is double precision. J During the summer of 2004 I ported a large Firebird database to 7.x and firebird uses the term double which in PG is a float8 I believe. Anyway i was able to just paste the Firebird ddl in to the query editor and the server would substitute the correct PG native type. varchar works, how come double does not? Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Did this work in earlier version of Postgres?
On Thu, 2006-03-23 at 17:31 -0600, Tony Caduto wrote: I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; Now it gives a error that type double does not exist. CREATE DOMAIN double AS float8; There, now the type exists ;) -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Did this work in earlier version of Postgres?
Tony Caduto [EMAIL PROTECTED] writes: I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; Now it gives a error that type double does not exist. [ tries it... ] Sorry, fails in everything back to 7.0, which is the oldest branch I have running. The error message varies a bit. varchar works, how come double does not? The SQL spec has varchar, it does not have double. character string type ::= CHARACTER [ left paren length right paren ] | CHAR [ left paren length right paren ] | CHARACTER VARYING left paren length right paren | CHAR VARYING left paren length right paren | VARCHAR left paren length right paren approximate numeric type ::= FLOAT [ left paren precision right paren ] | REAL | DOUBLE PRECISION regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Did this work in earlier version of Postgres?
Tony Caduto wrote: I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; There has never been a type named double in PostgreSQL. The type name mandated by the SQL standard is double precision, and PostgreSQL supports that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] [SUGGESTION] CVSync
Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: Any particular reason why straight CVS doesn't work for you? Are you that interested in having the log comment locally? Personally, I'd really like to have a local repository copy, because I spend a *lot* of time with cvsweb etc --- but I'm sure my needs are several standard deviations away from the mean. So far I've been discouraged from setting up a repository by the unreasonable infrastructure needs of cvsup. So these alternatives do sound pretty interesting. Is csup protocol-compatible with cvsup? If so people could use it without Marc having to do anything. Has anyone got experience with it --- reliability, performance, etc? Tom, I don't know what unreasonable infrastructure you are referring to. Building cvsup is a major pain, but installing and running it isn't, in my experience. There's a package in Fedora Extras. Setting up cvsweb against my cvsup repo is a fine idea - I wonder why I didn't think of that. That's not to say that supporting cvsync isn't a good idea too. TIMTOWTDI as we perl people like to say. cheers andrew ---(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] Did this work in earlier version of Postgres?
Peter Eisentraut wrote: There has never been a type named double in PostgreSQL. The type name mandated by the SQL standard is double precision, and PostgreSQL supports that. Ok, Thanks for clearing that up for me :-) Maybe it was pgAdmin that did the substitution. Thanks, Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Did this work in earlier version of Postgres?
Which is actually a float8 :) CREATE TABLE public.test ( junk double precision, ); alter table public.test add column foo float8; Table public.test Column | Type | +--+-- junk | double precision | punk | double precision | Regards, Guido Barosio On 3/23/06, Peter Eisentraut [EMAIL PROTECTED] wrote: Tony Caduto wrote: I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; There has never been a type named double in PostgreSQL. The type name mandated by the SQL standard is double precision, and PostgreSQL supports that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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 -- Guido Barosio --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Did this work in earlier version of Postgres?
Rod Taylor wrote: On Thu, 2006-03-23 at 17:31 -0600, Tony Caduto wrote: I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; Now it gives a error that type double does not exist. CREATE DOMAIN double AS float8; There, now the type exists ;) That's a little too perl for me ;) Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SUGGESTION] CVSync
It's only partially compatible: Like I mentioned earlier, csup currently only acts as an alternative cvs checkout client. Joel On Thu, Mar 23, 2006 at 3:15 PM, Tom Lane ([EMAIL PROTECTED]) wrote: Is csup protocol-compatible with cvsup? If so people could use it without Marc having to do anything. Has anyone got experience with it --- reliability, performance, etc? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Did this work in earlier version of Postgres?
On Thu, 2006-03-23 at 16:05 -0800, Joshua D. Drake wrote: Rod Taylor wrote: On Thu, 2006-03-23 at 17:31 -0600, Tony Caduto wrote: I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; Now it gives a error that type double does not exist. CREATE DOMAIN double AS float8; There, now the type exists ;) That's a little too perl for me ;) I suppose it depends on the goal. If it is an application that is to be supported on more than one database, defining types and other things for a given DB type (PostgreSQL) is easier than injecting a large number of SWITCH statements into the code. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SUGGESTION] CVSync
Yes, that's one way to solve the problem. I was just trying to suggest something that would benefit everyone in general while not opening up a can of worms with regard to increased server load in the long term. Joel On Fri, Mar 24, 2006 at 4:34 PM, Kurt Roeckx ([EMAIL PROTECTED]) wrote: On Thu, Mar 23, 2006 at 01:32:34PM -0800, Joel Miller wrote: Hello, I've been trying to get a local mirror of the cvs repository, but my connection attempts using rsync to cvsup.postgresql.org are always refused when I try to actually retrieve the pgsql-cvs collection. I tried to use rsync because cvsup is simply not an option for me. Which seems to be a permission problem to me. Maybe the permissions should just get changed so that you can use it using rsync? I also find it very handy to have a local mirror of the repository. Kurt ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Did this work in earlier version of Postgres?
Now it gives a error that type double does not exist. CREATE DOMAIN double AS float8; There, now the type exists ;) That's a little too perl for me ;) I suppose it depends on the goal. If it is an application that is to be supported on more than one database, defining types and other things for a given DB type (PostgreSQL) is easier than injecting a large number of SWITCH statements into the code. \ Why in the world would you build an application for anything except PostgreSQL? ;) ---(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] pgNixInstaller: Making PostgreSQL relocatable
On Mar 23, 2006, at 12:15 AM, Tom Lane wrote: OK ... it's supposed to work to shift the whole installation tree to a new root, ie, paths to places like the /share and /lib directories are determined relative to where the backend executable actually is. If this is not working on Solaris then for sure we want to know ... It is not relocatable on OS X. The full prefix path seems to be used instead. For example: [M:bin/postgresql/bin] % otool -L pg_ctl pg_ctl: /sw/bin/postgresql-8.1.2/lib/libpq.4.dylib (compatibility version 4.0.0, current version 4.1.0) /usr/lib/libssl.0.9.7.dylib (compatibility version 0.9.7, current version 0.9.7) /usr/lib/libcrypto.0.9.7.dylib (compatibility version 0.9.7, current version 0.9.7) /usr/lib/libz.1.dylib (compatibility version 1.0.0, current version 1.2.3) /usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 369.1.5) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 88.1.2) /usr/lib/libmx.A.dylib (compatibility version 1.0.0, current version 92.0.0) It would be really nice to have relative references. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SUGGESTION] CVSync
On Thu, Mar 23, 2006 at 01:32:34PM -0800, Joel Miller wrote: Hello, I've been trying to get a local mirror of the cvs repository, but my connection attempts using rsync to cvsup.postgresql.org are always refused when I try to actually retrieve the pgsql-cvs collection. I tried to use rsync because cvsup is simply not an option for me. Which seems to be a permission problem to me. Maybe the permissions should just get changed so that you can use it using rsync? I also find it very handy to have a local mirror of the repository. Kurt ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Static build of psql with readline support
What is the virtue of this in any case? I can see considerable use for a statically linked pg_dump, to help with upgrading, but not too much for statically linked anything else, especially since we are now pretty relocatable on most platforms at least. Upgraded db server to 8.1, but don't want to upgrade client library on 3 webservers to 8.1. Reason being I'll have to end up rebuilding PHP and more downtime and then new version of libtool, autoconf, etc. and anything else FreeBSD ports decides it needs. So, I just put static versions of pg_dump, pg_dumpall and psql on the webservers in /usr/local/bin so that those machines can still usefully talk to the db server from the CLI. In particular, I can restore dumps containing dollar quotes, plus get new psql features and 8.1 dumps. Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Did this work in earlier version of Postgres?
On Thu, 2006-03-23 at 16:41 -0800, Joshua D. Drake wrote: Now it gives a error that type double does not exist. CREATE DOMAIN double AS float8; There, now the type exists ;) That's a little too perl for me ;) I suppose it depends on the goal. If it is an application that is to be supported on more than one database, defining types and other things for a given DB type (PostgreSQL) is easier than injecting a large number of SWITCH statements into the code. \ Why in the world would you build an application for anything except PostgreSQL? To prove that, as unbelievable as it sounds, it is possible to do such a thing? Don't worry, such a thing would not get into a production environment. -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SUGGESTION] CVSync
[I thought this had gone out to the list when I first sent it, but now I see that it didn't] Actually, I've already looked at it very closely (spent a whole day browsing the latest source code, in fact). The problem is csup still doesn't support mirroring of the repository ,v files. It only acts as an alternative cvs checkout client. It doesn't handle ,v files at all. The main developer of csup is looking for help in implementing support for dealing with the RCS ,v files but so far it looks like no one is offering. And I don't think anyone is going to, since cvsync is already up and running, and so easy to set up and use. Joel On Thu, Mar 23, 2006 at 1:59 PM, Larry Rosenman (ler@lerctr.org) wrote: Joel Miller wrote: Hello, I've been trying to get a local mirror of the cvs repository, but my connection attempts using rsync to cvsup.postgresql.org are always refused when I try to actually retrieve the pgsql-cvs collection. I tried to use rsync because cvsup is simply not an option for me. I'd like to suggest that CVSync (www.cvsync.org) be added as a means for the public to obtain a local mirror of the cvs repository. It builds on most every platform (it's written in C, no modula-3 required) and has the advantage of caching directory scan info to reduce server load -- The cached scan info is sent to the client, which compares it to its own cached scan info to decide what to download (You can set up a cron job to periodically scan the repository directories, or even set up a sophisticated system that would only scan after commits are made.). It also sends only the new diffs (optionally compressed) from the ,v files that are needed to bring the clients' repository mirror up to date. As a complete out of nowhere suggestion, you might also look at csup in FreeBSD's CVS (or the project page), which is a cvsup clone in C. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Worthwhile optimisation of position()?
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
Chris
---(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] Worthwhile optimisation of position()?
Christopher Kings-Lynne wrote:
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
You must compare position('ch' in username) to '%ch%' instead of 'ch%' in this
respect.
The position function must look for 'ch' everywhere in the string so there's no way it can
use an index.
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Worthwhile optimisation of position()?
Thomas Hallgren wrote:
Christopher Kings-Lynne wrote:
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
You must compare position('ch' in username) to '%ch%' instead of 'ch%'
in this respect.
The position function must look for 'ch' everywhere in the string so
there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Tim
--
---
Tim Allen [EMAIL PROTECTED]
Proximity Pty Ltd http://www.proximity.com.au/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Worthwhile optimisation of position()?
Tim Allen [EMAIL PROTECTED] writes: Thomas Hallgren wrote: The position function must look for 'ch' everywhere in the string so there's no way it can use an index. I think the '= 0' bit is what Chris was suggesting could be the basis for an optimisation. Yeah. AFAICS the transformation Chris suggested is valid. I'm really dubious that it's worth expending planner cycles to look for it though. LIKE is something that everybody and his brother uses, but who uses this position()=0 locution? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Worthwhile optimisation of position()?
Tom Lane wrote:
Tim Allen [EMAIL PROTECTED] writes:
Thomas Hallgren wrote:
The position function must look for 'ch' everywhere in the string so
there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Yeah. AFAICS the transformation Chris suggested is valid. I'm really
dubious that it's worth expending planner cycles to look for it though.
LIKE is something that everybody and his brother uses, but who uses this
position()=0 locution?
regards, tom lane
The documentation says: position('om' in 'Thomas') == 3 so i assumed
that the returned index was 1-based and that a zero meant 'not found'.
If I'm wrong ,perhaps the docs need to be updated?
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Worthwhile optimisation of position()?
Yeah. AFAICS the transformation Chris suggested is valid. I'm really dubious that it's worth expending planner cycles to look for it though. LIKE is something that everybody and his brother uses, but who uses this position()=0 locution? One of our junior developers :) Which is why I noticed it. Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Worthwhile optimisation of position()?
Thomas Hallgren wrote:
Tom Lane wrote:
Tim Allen [EMAIL PROTECTED] writes:
Thomas Hallgren wrote:
The position function must look for 'ch' everywhere in the string so
there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Yeah. AFAICS the transformation Chris suggested is valid. I'm really
dubious that it's worth expending planner cycles to look for it though.
LIKE is something that everybody and his brother uses, but who uses this
position()=0 locution?
regards, tom lane
The documentation says: position('om' in 'Thomas') == 3 so i assumed
that the returned index was 1-based and that a zero meant 'not found'.
If I'm wrong ,perhaps the docs need to be updated?
The docs are correct so my initial point was correct. position('ch' in user) = 0 is
equivalent to user NOT LIKE '%ch%' and there's no way you can index that.
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgNixInstaller: Making PostgreSQL relocatable
Greg Stark wrote: I'm sure this isn't the only possible gotcha but I do seem to recall that on Solaris there's no such thing as a default LD_LIBRARY_PATH. Every binary stores absolute paths to every shared library it's linked against. On Solaris you can actually use relative library paths by writing $ORIGIN in the path. We don't do that right now, but it's possible. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Worthwhile optimisation of position()?
The docs are correct so my initial point was correct. position('ch' in
user) = 0 is equivalent to user NOT LIKE '%ch%' and there's no way
you can index that.
Well = 1 then.
Chris
---(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
