Re: [HACKERS] I need Help
Yes it is possible for both to be on the same pc. Please send mail to the general or novice list if you need more help. On Mon, 2004-05-03 at 11:05, olivia jurado wrote: > Hi. > > I'm from Panama. > > I don't speak english very well but I'm learning > english. > > I Need help. > > I installed postgresql 7.4 in my computer, I'm using > redhat 9.0 . > I installed pgadmin III but I can't to conecct to the > server. > > The port 5432 is not open. > > I have one computer. If possible to use server and > client in this same machine. > > please help me. > > thanks > > > _ > Do You Yahoo!? > Información de Estados Unidos y América Latina, en Yahoo! Noticias. > VisÃtanos en http://noticias.espanol.yahoo.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(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] Usability, MySQL, Postgresql.org, gborg, contrib,
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Joshua D. Drake") would
write:
> Personally, plpgSQL is only useful to those who are coming from
> Oracle. People are more likely to be comfortable with plPython or
> plPerl than plpgSQL.
I beg to differ.
In order to use pl/Python or pl/Perl to manipulate data in the
database, I have to go and find an SPI module.
With pl/pgsql, all I need to do is to directly present the SQL
queries.
The flip side, of course, is that Perl and Python provide nice idioms
for fiddling with text that are much nicer than clumsy usages of
substr() in pl/pgsql.
I'd rather do complex text validation in Perl, but write code that
does "table stuff" in pl/pgsql.
That is indeed the /exact/ approach I have taken in using stored
procedures for data conversions; a mix of the both, using each to
harness its respective strengths.
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/lsf.html
'Mounten' wird fuer drei Dinge benutzt: 'Aufsitzen' auf Pferde,
'einklinken' von Festplatten in Dateisysteme, und, nun, 'besteigen'
beim Sex.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Hi! Tim Conrad wrote: My favourite part of it is: MySQL uses traditional row-level locking. PostgreSQL uses something called Multi Version Concurrency Control (MVCC) by default. MVCC is a little different from row-level locking in that transactions on the database are performed on a snapshot of the data and then serialized. New versions of PostgreSQL support standard row-level locking as an option, but MVCC is the preferred method. Nice that you point out that incorrectly stated something. Even nicer that you don't tell me what the correct answer would be. Unfortunanatly, that's the best I could come up with with doing research with the documentation I could find on the subject. MVCC does a lot more than can be easily contained in a sentance. The problem is that in MySQL 1) MyISAM does table-level locking; 2) BDB does row-level locking; 3) InnoDB does MVCC (mostly) like PostgreSQL. PostgreSQL does support row-level locking (SELECT ... FOR UPDATE), table-level locking (LOCK TABLE ...), though this does not *replace* MVCC, as one may understand from the quotation. MySQL's roadmap is complete bullshit. Subselects were first promised in 4.0, which was "not that far away" [1] back in 1998! Well, they are in 4.1, which is still alpha in 2004. I realize this. I also realize that having a nicely defined roadmap would give Postgres a hands up in this category. A hands up in *what* category? In bragging? Should PostgreSQL developers write something along the lines of "PostgreSQL 9i (available Really Soon Now) will also be able to make coffee"? Well, as you know about coffee now, why don't you add "make coffee" to your comparison table, with empty space in MySQL's and commercial DBMSs' columns and "in 9i" in PostgreSQL's one? ---(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] [pgsql-advocacy] What can we learn from MySQL?
Bruce wrote:
> Now, if you are asking about marketing, yea, we don't have much in that
> area right now, and we need it. I think your point was that we need a
> single controlling company to provide marketing because if there are
> many, there is little incentive to market PostgreSQL because all the
> other companies are taking advantage of it. That is mostly true.
Yep, this is one of the key issues.
Right now, there isn't a group of people (with a decent budget) who get up
in the morning and say, "what can I do today to make Postgres more widely
adopted?" And that's a big problem. And it's not just marketing: who's
working on partnerships? Who making sure all of the ISVs add Postgres to
their list of supported databases?
> However, I would argue that Red Hat providing support was more important
> than Red Hat marketing, and we do have that with a number of companies
> now, and
I think we may have to "agree to disagree" on this.
> SRA is going to be announcing world-wide support soon (not just
> Japan), and we have other venture capital guys looking a forming
> companies.
This is a good step, but it's not the same as a Postgres-focused effort.
SRA's business (and HP's, and IBM's, and Cap Gemini's, and other companies
which are providing support for open source projects) is not about making
Postgres ubiquitous -- it's about selling services.
If a customer came to {SRA,IBM,etc.} with a large suitcase of cash and said,
"will you support Firebird for me?", you'd say yes!
> My concern about a single company, as all of us are, is that we kill the
> community that created the software, which then burdens the single
> company to steer development, leading to disaster.
Understood, and that's the potential catch-22. This is the problem with
capital: no smart investor is going to fund a company to promote and
support an project like Postgres if there's nothing to prevent 5 other
investors and teams from doing the exact same thing. There MAY be a way to
form something that's supportive and respectful of the community, and I
think it's worth trying to figure that out.
Bottom line: the Postgres project is at a stage where the non-technical
factors (marketing, partnerships) are at least as important as the technical
ones. Postgres may "lose" because of lacking technology (such as win32
support, though coming soon), but will not necessarily "win" with the best
technology.
-andy
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] Hacking postgres backend process
Title: Message Hi All, I posted this subject on General discussion-list but got no takers. I'll restate my query and be as brief as I possible. "What are the issues/dangers involved in putting an external process-execution call in instance of main postgres-backend thread of execution?" The operating context will be a Linux/UNIX OS. Here is a typical SQL statement I'm trying to field: "SELECT * FROM f(a)." Where "f" is a stored-procedure stub to a shared library C function, "a" is a string-parameter. "f" will need to - under the proper circumstances - call an external process "p", parse the process-output, and return a set of structured records. "p" may run for a very long time; may cause SIG_*; may leave heap in an inconsistent state; may spawn child-processes. I've already written a number of stored-procedures backed by shared libraries implemented in C, including set-returning functions, and I know the basics of user-types and arrays (including some custom array extensions). I've written UNIX shell processes in C while in school, so I know a bit about child-process control and signal-handling. It seems that "fork" is clearly out; I'm assuming process execution environment MUST be guaranteed consistent on re-entrance into postgres. Using "exec" is possibly worse with a full image-overlay destroying any hope of reconstructing pre-spawn environment. What are my options here? Thanks for any input, Carl <|};-)>
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Alexey Borzov wrote: I realize this. I also realize that having a nicely defined roadmap would give Postgres a hands up in this category. A hands up in *what* category? In bragging? In telling your boss, "I think we should use Postgresql." It's likely he's not stupid, and it's reasonable for him to say "since I'm tying my own success to this software, I want to have some indication as to where this software is going to go." Something like Josh Berkus' table of features would be very nice. (I've worked with sales teams at my various former employers, and the best things you can provide them are documents (feature descriptions, competitive analyses, white papers, etc) that your customer contact can use as the basis for his own justification to buy your product. All of this can be summarized as "make it easy for people to help you.") Cheers, Mark -- Mark Harrison Pixar Animation Studios ---(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] [pgsql-advocacy] What can we learn from MySQL?
Scott Marlowe wrote: > While Apache is and has been wildly popular for bulk hosing and domain > parking, for serious commercial use, Netscape's enterprise server, now Sun > One, has long been a leader in commercial web sites. Netscrape/SunONE may have been a leader in some sub-market, but this misses the point. Apache + NCSA never had less than 50% market share, overall. http://news.netcraft.com/archives/web_server_survey.html Postgres is in a completely different situation: 95+?% of the world's databases don't run on Postgres, and it's been this way for a long time. Also, Apache never had "MyApache", a more popular version that many believe to be "free" and "open source". My point: Apache was successful in a situation that may not apply here. Does anyone know of an open source project that *has* successfully displaced a market of mature, established products WITHOUT a commercial entity providing marketing, support & direction? -andy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-advocacy] What can we learn from MySQL?
Bruce wrote: > > Does anyone know of an open source project that *has* successfully displaced > > a market of mature, established products WITHOUT a commercial entity > > providing marketing, support & direction? > > Linux. It doesn't have a single company behind it, but several. Uh, no. Linux HAD a commercial entity providing marketing, support, and direction. Red Hat went a long, long way to making Linux real for businesses. They were (are) a well-funded entity, focused on Linux adoption. Their early success, in turn, validated the business (a) so competitors got funded and (b) so established companies (e.g. IBM) started to pay attention. (This is not meant to give all credit to Red Hat: if it wasn't them, it would have been some other similar group). So, does anyone know of an open source project that *has* successfully displaced a market of mature, established products WITHOUT a commercial entity providing marketing, support & direction? If not, where's the Red Hat for Postgres? Good discussion! -andy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nasty security bug with clustering
I'm in the middle of reviewing (read whacking around) Rod Taylor's patch for multiple operations in ALTER TABLE, so I'm afraid that no patch in the same area is likely to apply cleanly after the dust settles :-( OK, Bruce - just ignore the patch I sent in. I'll refactor it after Tom commits. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] EXE and COPY question
Hallo 1/ I have made one program in Access, now I need some tool who can make exe file. What is the easy but good tool for that purpose and where can I get it. 2/ Also I need (Copy protection) tool who can do returning leash between bayer and me (taking his serial number form his hard disk, motherboard etc) Do you know something about that program and where can I get it? Thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
[EMAIL PROTECTED] ("Jim C. Nasby") writes:
> I would still argue that if any language should be installed by
> default it should be plpgsql and not java. As I mentioned, everyone
> using a database already knows SQL; not nearly as many know java.
A vital factor is indeed that pl/pgsql does not require require
importing extra APIs to access the database.
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/sgml.html
Sleep is a poor subsititute for caffeine. -Pat Dughi
---(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] 7.5 features
Bruce Momjian <[EMAIL PROTECTED]> writes: > What we really need is for these folks to start finalizing their patches > and get them submitted. Eggzackle ... my point is that I see the win32 train leaving the station pretty soon, and I don't see anyone else ready to get on board. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] I need Help
I installed postgresql 7.4 in my computer, I'm using redhat 9.0 . I installed pgadmin III but I can't to conecct to the server. The port 5432 is not open. You need to set tcpip_socket = true in your postgresql.conf. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Hi! Tim Conrad wrote: I was researching an article I wrote about a comparison between Postgres and MySQL recently (If you want, you can read the article at http://www.devx.com/dbzone/Article/20743/). I noticed some clear differences between the mysql.com website and the Postgres website. Sorry, couldn't resist: may I suggest doing the research *before* writing an article, not *after*? My favourite part of it is: MySQL uses traditional row-level locking. PostgreSQL uses something called Multi Version Concurrency Control (MVCC) by default. MVCC is a little different from row-level locking in that transactions on the database are performed on a snapshot of the data and then serialized. New versions of PostgreSQL support standard row-level locking as an option, but MVCC is the preferred method. 2) There doesn't seem to be a clear roadmap on Postgres features. When certian things are expected. There's the TODO list that Bruce maintains, but it only outlines 'near' fixes. MySQL has a nice listing of what to expect in certian future versions. I know it's not a perfect list, but it'd be nice to know when full blown replication will be included in PostgreSQL as an example. MySQL's roadmap is complete bullshit. Subselects were first promised in 4.0, which was "not that far away" [1] back in 1998! Well, they are in 4.1, which is still alpha in 2004. Of course, some gullible people actually believe this and compare [2] the existing and working implementations with vaporware (MySQL 5.1, anyone?). On those same lines, there doesn't seem to be anything about the improvements in the minor versions. It seems that in every release (i.e. 7.2,7.3,7.4) there are pretty significant changes, but finding a place that outlines these changes is somewhat difficult. Have you tried looking in the release notes [3]? [1] http://www.geocrawler.com/archives/3/194/1998/8/0/1061364/ [2] http://www.devx.com/dbzone/Article/20743/1763?supportItem=1 [3] http://www.postgresql.org/docs/7.4/interactive/release.html ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Call for 7.5 feature completion
Andrew Dunstan wrote: Marc G. Fournier wrote: Personally, I think there are alot of large features that ppl have been hard at getting complete in time for June 1st that we should stick to it, else we're going to end up with 'yet another release' delayed in hopes that the outstanding bugs in Win32 will get fixed in a reasonable amount of time ... June 1st, let's do beta for 7.5 and then branch onto 8.0, with 8.0 key'd to the Win32 Native port being finished ... If that means 8.0 happens to be September 1st, so be it ... Bruce agreed that this had been vague before today, so if people have had this date in mind and have been working to it, perhaps they have some telepathic abilities I lack ... We missed on PITR *and* Win32 last year. ISTM there's a bit of a credibility issue at stake, so it might well be worth taking a couple of weeks leeway if that's what is required. The other point, especially about Win32, is to see if we can spread the load a bit. Perhaps Claudio, Magnus, Merlin and Bruce should start trying to farm out specific tasks. I for one will be very upset if it misses this release. cheers andrew This is exactly the point ... If you go to a conference you will ALWAYS face the same questions: - when can we have sync. replication and failover - when can we have PITR - when can we have win32 People won't believe us anymore if you keep telling them "in the next release". If a feature freeze is made on August 1st or even later it would be ok because nobody is doing major database changes in summer anyway. Currently I cannot see a major reason why people should upgrade to 7.5 (ARC and so forth are great but they are no killer features). Maybe in this case it is worth waiting for 2 major features to make it into the release (let's say PITR + nested transactions or win32 and pitr or 2pc and nested transactions). This would point out that significant progress is made. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] I need Help
Hi. I'm from Panama. I don't speak english very well but I'm learning english. I Need help. I installed postgresql 7.4 in my computer, I'm using redhat 9.0 . I installed pgadmin III but I can't to conecct to the server. The port 5432 is not open. I have one computer. If possible to use server and client in this same machine. please help me. thanks _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Weird prepared stmt behavior
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Right. But note that Tom wants to distinguish between statements > created via PREPARE (which would rollback) from those created via a > Prepare message (which wouldn't). Actually, no, I'd prefer not to make such a distinction; I'd be happy with SQL-level PREPARE being nontransactional. I'd be willing to put up with that distinction if someone shows it's needed, but so far there's not been a really good argument advanced for it, has there? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Weird prepared stmt behavior
On Mon, May 03, 2004 at 04:15:10PM -0400, Greg Stark wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > I don't see how this collides with the ideas presented so far. The JDBC > > driver wants the same: they want to prepare some statements and be able > > to use them later in the session. They don't want to be paying > > attention to which prepares were committed and which ones weren't. > > Oh I thought the idea was that the statement would only be available within a > transaction. > > You're saying they span transactions but if the transaction rolls back then it > also rolls back the statement "creation". Right. But note that Tom wants to distinguish between statements created via PREPARE (which would rollback) from those created via a Prepare message (which wouldn't). > Incidentally I tried to find documentation on the v3 binary prepare/execute > protocol and failed. I think I ended up looking at libpq calls which is too > high level to understand what the protocol is and isn't capable of. I have > some ideas of what the next step could be. > Where should I be looking? Source code would be fine if the wire protocol > isn't in the documentation. http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52666 -- Alvaro Herrera () "I think my standards have lowered enough that now I think 'good design' is when the page doesn't irritate the living f*ck out of me." (JWZ) ---(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] mingw configure failure workaround
Peter Eisentraut wrote: Andrew Dunstan wrote: However, the problem is that the first line will actually appear to have succeeded, i.e. MSys's ln is lying to us ;-( Then msys needs to be fixed. There is certainly a bunch of autoconfiscated software that gets compiled on mingw/msys every day. I would like to know why we are the only ones having this problem. Has anyone contacted the msys authors about this? I don't know - I recall hearing something, but I have found no trace. I will follow it up, but I do not think this absolves us of all responsibility. We work around all sorts of problems on all sorts of platforms. This comes from the autoconf macro _AC_OUTPUT_LINKS defined in its status.m4, which I guess is what we'd need to override (is that possible?) No I will take your word for it, but see below. if we are going to detect the failure, or maybe there's some more magical way that in my unfamiliarity with autoconf I am unaware of. No "No" is our answer too often. A lot of reading and some experimentation showed that putting this in configure.in: AC_OUTPUT_COMMANDS([ for linktarget in src/backend/port/dynloader.c src/backend/port/pg_sema.c src/backend/port/pg_shmem.c src/include/dynloader.h src/include/pg_config_os.h src/Makefile.port ; do test -e $linktarget || echo " ***" link for $linktarget failed - please fix by hand done ]) yielded results looking like this: config.status: executing default-1 commands *** link for src/backend/port/pg_shmem.c failed - please fix by hand *** link for src/include/dynloader.h failed - please fix by hand Which is more or less what I wanted as a minimum. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL pre-fork speedup
sdv mailer <[EMAIL PROTECTED]> writes: > Forking consumes a large amount of CPU when you have > many simultaneous connections and adds up to the > latency. Particularly MySQL users may think > PostgreSQL's connection time is much slower because > these users tend to perform relatively simple queries. Frankly I think part of the reason you'll get little traction on this front is that some people consider an application that puts building a database connection into a critical path, especially one that does only a single simple query and disconnects, a stupid design. If it's a connection time is critical element it's trivial to move it outside the critical path and reuse connections. You'll get much better performance that way as well since both Postgres and Linux will have more time to observe how the process behaves and adjust cache and schedule behaviour. > In my case, connection pooling and persistent > connection is useless for a large server farm > consisting of hundreds of partitioned and replicated > servers doing only simple queries. Well have you tried either? It would involve having a lot more idle connections but then that's effectively the same as "preforking" anyways. Not only would they be "preforked" but they wouldn't have to be shut down and recreated repeatedly at all, even outside the critical path. If the idle connections consume too much memory to be feasible (like, say, if you have thousands of database servers but only a small unpredictable subset of which are busy at any time) then "preforking" wouldn't really help much either and suffer the same problem. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] mingw configure failure workaround
Andrew Dunstan wrote: Peter Eisentraut wrote: Andrew Dunstan wrote: Even if we don't do that can we *please* put in something that detects the error, and tells the user what they will have to do to fix it? Failing in a situation which we know we can detect and not telling the user is intolerable, IMNSHO. Can you try a more recent version of autoconf and see if that behaves more tolerably? tested with autoconf 2.59. Unfortunately, it does not. It does try to copy if a link fails, unlike what we have now: ln -s $ac_rel_source $ac_dest 2>/dev/null || ln $srcdir/$ac_source $ac_dest 2>/dev/null || cp -p $srcdir/$ac_source $ac_dest || We don't have the last line, which must have been added since autoconf 2.53. I was ahead of myself. It does appear to work, (tested in the platform I was using to get reliable failure, with autoconf 2.56 from the MSysDTK). I'm damned if I know why, though. I still think we should cosider the little error detection macro I just posted. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Weird prepared stmt behavior
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I don't see how this collides with the ideas presented so far. The JDBC > driver wants the same: they want to prepare some statements and be able > to use them later in the session. They don't want to be paying > attention to which prepares were committed and which ones weren't. Oh I thought the idea was that the statement would only be available within a transaction. You're saying they span transactions but if the transaction rolls back then it also rolls back the statement "creation". Off the top of my head that doesn't seem like a problem. Though I wonder how that meshes with other database's views on the point. > Then prepare_cached could send a v3 Prepare and assume the statement > will be available for the rest of the session. Incidentally I tried to find documentation on the v3 binary prepare/execute protocol and failed. I think I ended up looking at libpq calls which is too high level to understand what the protocol is and isn't capable of. I have some ideas of what the next step could be. Where should I be looking? Source code would be fine if the wire protocol isn't in the documentation. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] mingw configure failure workaround
> tested with autoconf 2.59. > > Unfortunately, it does not. It does try to copy if a link > fails, unlike what we have now: > > ln -s $ac_rel_source $ac_dest 2>/dev/null || > ln $srcdir/$ac_source $ac_dest 2>/dev/null || > cp -p $srcdir/$ac_source $ac_dest || > > We don't have the last line, which must have been added since > autoconf 2.53. > > However, the problem is that the first line will actually > appear to have succeeded, i.e. MSys's ln is lying to us ;-( Ok, how's this for a really ugly solution: * Provide our own ln (in the form of a shellscript, even) * Make sure this one gets in ahead of the system supplied one in the path (from the code above it looks like it's not calling it with a specific path, so just force-adding somethign to the path of configure should work?) This ln can then do a cp directly, and not even bother trying the mingw ln function which we know will only do cp anyway if it succeeds. If there is a less ugly solution to be had, by all means stay away from thsi oen :-) //Magnus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Weird prepared stmt behavior
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > If we re-parse from source then we will detect any changes that make the > query visibly incorrect. I don't really see that the user can have any > beef if he continues to use a prepared statement whose source text would > have a valid but incompatible meaning due to changes elsewhere. I see your point about the reparsing. It's what happens anyway for drivers such as DBD::Pg which had to emulate a PREPARE before there actually was one. In effect, the statement is re-parsed every time. So consider me over my initial uneasiness. What about rolling prepares back if they are in a transaction, though? They still have the ability to affect a transaction, despite being partially outside of it: pgf=> begin; BEGIN pgf=> prepare toqualify as select 1 from pg_classs; ERROR: relation "pg_classs" does not exist pgf=> prepare toqualify as select 1 from pg_class; ERROR: current transaction is aborted, commands ignored until end of transaction block pgf=> rollback; ROLLBACK pgf=> begin; BEGIN pgf=> prepare toqualify as select 1 from pg_class; PREPARE pgf=> prepare yourself as select 1 from pg_procc; ERROR: relation "pg_procc" does not exist pgf=> prepare yourself as select 1 from pg_proc; ERROR: current transaction is aborted, commands ignored until end of transaction block pgf=> rollback; ROLLBACK pgf=> deallocate toqualify; DEALLOCATE - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200405032114 -BEGIN PGP SIGNATURE- iD8DBQFAlvCyvJuQZxSWSsgRAq00AJ4tvAseEraeOqz/zG83DvIBX8EPiACeObxW oUPFV5t+dbgsVnFIjh8FgMs= =HVkx -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Weird prepared stmt behavior
On Tue, May 04, 2004 at 01:22:53AM -, Greg Sabino Mullane wrote: > What about rolling prepares back if they are in a transaction, though? > They still have the ability to affect a transaction, despite being > partially outside of it: > [example ripped] IMHO this is an oversight, not a design feature. That's why I pointed it out and planned to correct it. I think with the Prepare message we should _not_ abort the transaction if it fails -- if it's outside transaction control, it shouldn't affect the current transaction (So we would open a subtransaction, process the message, and rollback if it fails.) -- Alvaro Herrera () "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Fixed directory locations in installs
Tom Lane wrote: > > What use could printing the relative path possibly have? > > Debugging. If I can't see it, I *know* I'm going to wish I could. Well, you can just look inside, it's not that big. Supporting extra options might make the script twice as big and thus make it harder to just look at the whole thing. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Weird prepared stmt behavior
On Mon, May 03, 2004 at 03:18:37PM -0400, Greg Stark wrote: > > Tom Lane <[EMAIL PROTECTED]> writes: > > > Before jumping into doing that, though, I'd want to have some > > discussions about the implications for the V3 protocol's notion of > > prepared statements. The protocol spec does not say anything that > > would suggest that prepared statements are lost on transaction rollback, > > and offhand it seems like they shouldn't be because the protocol is > > lower-level than transactions. > > Woah, that would totally defeat the purpose of prepared statements. > > The idea is that an OLTP system can prepare all the statements it will ever > need at startup time. Then simply execute them with various parameters as > needed. I don't see how this collides with the ideas presented so far. The JDBC driver wants the same: they want to prepare some statements and be able to use them later in the session. They don't want to be paying attention to which prepares were committed and which ones weren't. > Using a good driver like Perl's DBI this just means using something like > prepare_cached() instead of prepare(). Then prepare_cached could send a v3 Prepare and assume the statement will be available for the rest of the session. -- Alvaro Herrera () "Thou shalt not follow the NULL pointer, for chaos and madness await thee at its end." (2nd Commandment for C programmers) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] inconsistent owners in newly created databases?
Fabien COELHO <[EMAIL PROTECTED]> writes: > However, I feel that the owner should own the "public" schema and maybe > some other stuff to be carefully selected, without bluring that important > distinction? >From a definitional standpoint I don't have a problem with that. From an implementation standpoint, I fear it would be much more trouble than it is worth. You can't easily connect to another database. Possibly it would work to have this housekeeping done in the first backend to connect to the new database, but I don't think it could be done directly by CREATE DATABASE. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Fixed directory locations in installs
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> We'd also need to give some thought to pg_config output. I think I >> would like to be able to see the relative path computed by configure >> as well as the effective actual path ... maybe a switch to specify >> which way to print it? > What use could printing the relative path possibly have? Debugging. If I can't see it, I *know* I'm going to wish I could. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Weird prepared stmt behavior
Tom Lane <[EMAIL PROTECTED]> writes: > Before jumping into doing that, though, I'd want to have some > discussions about the implications for the V3 protocol's notion of > prepared statements. The protocol spec does not say anything that > would suggest that prepared statements are lost on transaction rollback, > and offhand it seems like they shouldn't be because the protocol is > lower-level than transactions. Woah, that would totally defeat the purpose of prepared statements. The idea is that an OLTP system can prepare all the statements it will ever need at startup time. Then simply execute them with various parameters as needed. For instance, on even a large web site there are often only a few dozen pages with a few hundred SQL queries total. It's entirely feasible to prepare them all on startup then simply execute them as needed. This means the optimizer only ever needs to look at a query once, not every execution which could be hundreds of times per second. Actually, using a web server architected like Apache this turns into "prepare the first time it's seen in a given process or thread". Configure Apache processes to last a few thousand page requests and the query is reparsed and optimized often enough to take advantage of new statistics but infrequently enough to be a negligible hit on performance. Using a good driver like Perl's DBI this just means using something like prepare_cached() instead of prepare(). -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Weird prepared stmt behavior
[ WRT/ automagically re-parsing prepared statement from source when dependency plan changes.] If done, this would have the wonderful side-effect of being able to use regular queries in plpgsql procedures which must currently be done using the EXECUTE form, such as those that just need to manipulate temporary tables. Quite spiffy, reducing the amount of surprise encountered by postgres neophytes. James Robinson Socialserve.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] mingw configure failure workaround
Andrew Dunstan wrote: > However, the problem is that the first line will actually appear to > have succeeded, i.e. MSys's ln is lying to us ;-( Then msys needs to be fixed. There is certainly a bunch of autoconfiscated software that gets compiled on mingw/msys every day. I would like to know why we are the only ones having this problem. Has anyone contacted the msys authors about this? > This comes from the autoconf macro _AC_OUTPUT_LINKS defined in its > status.m4, which I guess is what we'd need to override (is that > possible?) No > if we are going to detect the failure, or maybe there's > some more magical way that in my unfamiliarity with autoconf I am > unaware of. No ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL pre-fork speedup
Forking consumes a large amount of CPU when you have many simultaneous connections and adds up to the latency. Particularly MySQL users may think PostgreSQL's connection time is much slower because these users tend to perform relatively simple queries. In my case, connection pooling and persistent connection is useless for a large server farm consisting of hundreds of partitioned and replicated servers doing only simple queries. Below is a benchmark of MySQL 3.2 and PostgreSQL 7.4 doing multiple connects/disconnects within the same server (AMD 1.2GHz, 512MB, Linux 2.4). If forking is the issue then pre-forking will give a big boost especially for simple queries: MySQL time -- 0.012786865234375 0.011546850204468 0.01167106628418 MySQL time (with simple query) -- 0.015650987625122 0.01443886756897 0.014433860778809 PostgreSQL time --- 0.15319013595581 0.14930582046509 0.14920592308044 PostgreSQL time (with simple query) 0.19016313552856 0.18785095214844 0.18786096572876 __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Fixed directory locations in installs
Tom Lane wrote: > We'd also need to give some thought to pg_config output. I think I > would like to be able to see the relative path computed by configure > as well as the effective actual path ... maybe a switch to specify > which way to print it? What use could printing the relative path possibly have? It would only be relative to the location of pg_config, which is of no interest to the user of the printed information. Let's not make this more complicated than it needs to be. ---(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] inconsistent owners in newly created databases?
> ...
> Without this the db owner cannot drop types that may have been copied
> from the template.
Hmmm. I'm concerned about security, such as enabling the owner to load new
trusted code. You may be right, but I'm afraid it is delicate to decide
what owner fields should be changed. Owning a database does not mean being
a super user in that database. But I may be just pessimistic about
this issue.
> >It is unclear to me at the time when these updates should be performed.
> >After the createdb? Deferred to the first connection to the database?
>
> It seems the logical place is for the createdb routine to connect to the
> new database and make the ownership changes.
Yes, I agree.
However I have not seen a simple api to create a new backend connected to
another database and make it execute some sql commands. The fork/exec
stuff is managed by postmaster (the server frontend) directly. But I've
just given a quick look.
Also, how should it deal with max allowed connections and so on... Hence
deferring the stuff to the first connection may not be that bad, because
it would avoid a lot of system stuff.
Well, anyway someone agree with me that the situation is not appropriate.
Thanks for your comments,
--
Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ [EMAIL PROTECTED]
CRI-ENSMP, 35, rue Saint-Honoré, 77305 Fontainebleau cedex, France
phone: (+33|0) 1 64 69 {voice: 48 52, fax: 47 09, standard: 47 08}
All opinions expressed here are mine _
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Weird prepared stmt behavior
> On Sat, May 01, 2004 at 10:16:56PM -, Greg Sabino Mullane wrote: >> I am very uneasy about this. Statements should stay invalidated, else >> the prepared statement may no longer even do what was originally >> intended when it was first created. I think Greg's concern is overblown, and would result in an effectively unusable facility if we did implement it that way. As an example, adding an index to a table *should* result in flushing of cached plans for that table (maybe the query can make good use of the new index), but people would be quite unhappy if already-prepared statements for the table just stopped working. More generally, the flush mechanism will probably be written in a way that causes plan flushes to occur for events that aren't even user-visible --- such as VACUUM FULL relocating the catalog tuple that describes an object used by the plan. If we re-parse from source then we will detect any changes that make the query visibly incorrect. I don't really see that the user can have any beef if he continues to use a prepared statement whose source text would have a valid but incompatible meaning due to changes elsewhere. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] inconsistent owners in newly created databases?
> A database owner who is not a superuser should *not* be able to fool with > the built-in catalog entries. > > Database owner != superuser, and I don't want us blurring the distinction... Yes sure. I agree, especially if the owner is one of my students;-) However, I feel that the owner should own the "public" schema and maybe some other stuff to be carefully selected, without bluring that important distinction? -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] inconsistent owners in newly created databases?
Tom Lane wrote: Thomas Swan <[EMAIL PROTECTED]> writes: Fabien COELHO wrote: You don't want to update ownership of tables in system schemas. AFAICS, any changes they make are localized to their database not the whole database system. A database owner who is not a superuser should *not* be able to fool with the built-in catalog entries. Database owner != superuser, and I don't want us blurring the distinction... With regards to changing ownership, is there a way to determine what is a "built-in" catalog entry? If the database that was the template was modified, can the createdb routine determine what is core and what was added? ---(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] inconsistent owners in newly created databases?
Thomas Swan <[EMAIL PROTECTED]> writes: > Fabien COELHO wrote: >> You don't want to update ownership of tables in system schemas. >> > AFAICS, any changes they make are localized to their database not the > whole database system. A database owner who is not a superuser should *not* be able to fool with the built-in catalog entries. Database owner != superuser, and I don't want us blurring the distinction... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] cvs: mislinked plpgsql.so ?
At 02:05 AM 29/02/2004, Tom Lane wrote: Your plpgsql.so may be CVS-tip, but your backend isn't... that function was just added a few days ago. I just got this error after upgrading to 7.4.2; I assume it may be because an old library was still present in memory, but wanted to check. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.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])
Re: [HACKERS] Fixed directory locations in installs
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I guess what you are saying is we should have a configure-time option to >> address configured directories via relative paths from the executable's >> directory, rather than absolute paths? Seems reasonable ... > Yep. In fact, why would we not use that by default? Because it'll be slower. Instead of /usr/local/pgsql/lib we'd be using something like /usr/local/pgsql/bin/../lib which is not too bad here but would get worse if the directories are not so close. But perhaps we can arrange for the path to be simplified down to an absolute form when it's constructed at backend startup? You'd need a routine anyway to combine the bindir path (determined by FindExec) with the relative path provided by configure, so maybe this routine could be smart about leading ../ in the configure path. We'd also need to give some thought to pg_config output. I think I would like to be able to see the relative path computed by configure as well as the effective actual path ... maybe a switch to specify which way to print it? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Fixed directory locations in installs
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Not sure how we can do this for Unix except perhaps probe PATH. > > Huh? We have always determined the full path of the executable --- > see FindExec(). Oh, OK. I forgot that. > I guess what you are saying is we should have a configure-time option to > address configured directories via relative paths from the executable's > directory, rather than absolute paths? Seems reasonable ... Yep. In fact, why would we not use that by default? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] inconsistent owners in newly created databases?
Fabien COELHO wrote:
Dear Thomas,
* create the database with the new owner specified.
-- As a superuser in the newly created database
update pg_am set amowner = {userid}
update pg_class set relowner = {userid}
You don't want to update ownership of tables in system schemas.
AFAICS, any changes they make are localized to their database not the
whole database system. In other words, they can change add drop types,
procs, tables as if they were a superuser but only in their database.
A normal account (the db owner in this case) still cannot select against
pg_shadow or add users (without createdb privilege).
update pg_conversion set conowner = {userid}
update pg_namespace set nspowner = {userid}
As for SCHEMAs, I would not do that for system schemas
(pg_%, information_schema)...
It doesn't seem any different than running as the superuser and changing
those. Again, I think it would be restricted to someone frying their
own database, but not the whole system.
update pg_opclass set opcowner = {userid}
update pg_operator set oprowner = {userid}
update pg_proc set proowner = {userid}
I'm not sure system functions owner should be change. Also, call handlers
for languages should not change owner.
Without this the db owner cannot drop types that may have been copied
from the template.
update pg_type set typowner = {userid}
Are there any security problems that this would cause? Perhaps these
should be done by the system automatically.
I think that something along the line you describe should be done by the
system. However database creation does not actually connect to the new
database, the template base directory is simply copied with a "cp -r".
It is unclear to me at the time when these updates should be performed.
After the createdb? Deferred to the first connection to the database?
It seems the logical place is for the createdb routine to connect to the
new database and make the ownership changes.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL pre-fork speedup
Paul Ramsey <[EMAIL PROTECTED]> writes: > ... So the operational benefit of adding the complexity of a > pre-fork system is not very high. In particular, most of the connection startup overhead work cannot be performed until we've identified which database to connect to (since it largely consists of bootstrapping access to the system catalogs in that database). If you want that work to be done in advance of receiving a client connection request, life is much more complicated than it would be for something like Apache. There is considerable discussion of this point in the pgsql-hackers archives. I'd suggest reading the past threads before trying to do anything yourself. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Weird prepared stmt behavior
On Sat, May 01, 2004 at 10:16:56PM -, Greg Sabino Mullane wrote: > > We could imagine that once we add tracking of plan dependencies, > > detection of a change that invalidates a prepared statement's plan > > would just cause the prepared statement to be marked as "needs > > recompilation". The next attempt to use it would have to re-plan > > from source, and could get an error if there is no longer any valid > > interpretation of the original source string. > > I am very uneasy about this. Statements should stay invalidated, else > the prepared statement may no longer even do what was originally > intended when it was first created. OTOH, Oliver Jowett said on the JDBC list that the JDBC driver would like to have a mechanism to non-transactionally create prepared statements (http://archives.postgresql.org/pgsql-jdbc/2004-05/msg0.php) A possible compromise is what Tom said originally: we could just have the PREPARE command statements be discarded at rollback, but the Prepare message's statements should be kept. -- Alvaro Herrera () "Now I have my system running, not a byte was off the shelf; It rarely breaks and when it does I fix the code myself. It's stable, clean and elegant, and lightning fast as well, And it doesn't cost a nickel, so Bill Gates can go to hell." ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL pre-fork speedup
sdv mailer wrote: Instead, there's a big need to create a new connection on every query and with PostgreSQL needing to fork on every incoming connection can be quite slow. Really? My general experience has beent that forking/connection setup times are very good with PgSQL. Do not assume your Oracle experience transfers directly over -- Oracle has very large connection time overheads, PgSQL does not. This could be a big win since even a moderate improvement at the connection level will affect almost every user. Any chance of that happening for 7.5? Only if you do it yourself, probably. The calculation of the developers appears to be that the amount of time spent by the database on fork/connect will generally be dwarfed by the amount of time spent by the database actually doing work (this being a database, the actual workloads required of the backend are much higher than, say, for a web server). So the operational benefit of adding the complexity of a pre-fork system is not very high. And if you have the rare workload where a pre-fork actually *would* speed things up a great deal, you can solve the problem yourself with a connection-pooling middleware. -- __ / | Paul Ramsey | Refractions Research \_ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 01:30 AM 4/05/2004, Tom Lane wrote: can only occur if other transactions running parallel to the ANALYZE perform sufficient catalog updating activity to fill the sinval message queue. And there must also be at least one long-term-idle backend, so that the queue doesn't get drained. Sounds quite likely; usually seems to occur at 'shoulder' load times; lots of updates still happening (several each second) and a server process pool that is larger than necessary to handle the load. I'll replace all: heap_openr(ListenerRelationName, AccessExclusiveLock); with heap_openr(ListenerRelationName, ExclusiveLock); and see how it goes. Thanks for the help. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
> * Is it really a good idea for database-wide ANALYZE to run as a single > transaction? Holding all those locks is a recipe for deadlocks, even > if they're as inoffensive as AccessShareLocks normally are. Wasn't one idea behind that change also to not make the planner create a plan from mixed old and new statistics ? I guess that could later be accomplished with "begin work; analyze; commit work;" (with subtransactions) though. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
I wrote: > 2. As the ANALYZE proceeds, it issues sinval messages due to the updates > it's making in pg_statistic. This is normal. Small correction: actually, backends only send sinval messages at commit, so the ANALYZE will just be accumulating pending messages in its private memory. Your observed symptom therefore can only occur if other transactions running parallel to the ANALYZE perform sufficient catalog updating activity to fill the sinval message queue. And there must also be at least one long-term-idle backend, so that the queue doesn't get drained. I had been wondering why we'd not identified this problem before, but that combination of factors is probably unusual enough to explain why not. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: >> * Is it really a good idea for database-wide ANALYZE to run as a single >> transaction? Holding all those locks is a recipe for deadlocks, even >> if they're as inoffensive as AccessShareLocks normally are. > Wasn't one idea behind that change also to not make the planner create a plan > from mixed old and new statistics ? I don't recall that that was part of the discussion. IIRC all we were after was to let someone invoke ANALYZE from inside a BEGIN/COMMIT block. A possible compromise is to hack ANALYZE so that if it is invoked when *not* within a BEGIN block, it runs a separate transaction for each table. This seems pretty crufty but might satisfy all the requirements. > I guess that could later be accomplished with > "begin work; analyze; commit work;" (with subtransactions) though. AFAICS, locks taken by a (committed) subtransaction can't be released until top-level commit anyhow. Otherwise they fail to perform one of the essential functions of locking in an MVCC environment: to delay another process until the changes you've made are visible to him. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] mingw configure failure workaround
Peter Eisentraut wrote: Andrew Dunstan wrote: Even if we don't do that can we *please* put in something that detects the error, and tells the user what they will have to do to fix it? Failing in a situation which we know we can detect and not telling the user is intolerable, IMNSHO. Can you try a more recent version of autoconf and see if that behaves more tolerably? tested with autoconf 2.59. Unfortunately, it does not. It does try to copy if a link fails, unlike what we have now: ln -s $ac_rel_source $ac_dest 2>/dev/null || ln $srcdir/$ac_source $ac_dest 2>/dev/null || cp -p $srcdir/$ac_source $ac_dest || We don't have the last line, which must have been added since autoconf 2.53. However, the problem is that the first line will actually appear to have succeeded, i.e. MSys's ln is lying to us ;-( This comes from the autoconf macro _AC_OUTPUT_LINKS defined in its status.m4, which I guess is what we'd need to override (is that possible?) if we are going to detect the failure, or maybe there's some more magical way that in my unfamiliarity with autoconf I am unaware of. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Fixed directory locations in installs
Bruce Momjian <[EMAIL PROTECTED]> writes: > Not sure how we can do this for Unix except perhaps probe PATH. Huh? We have always determined the full path of the executable --- see FindExec(). I guess what you are saying is we should have a configure-time option to address configured directories via relative paths from the executable's directory, rather than absolute paths? Seems reasonable ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain wrote: Hi thanx and sorry that I asked such a simple question in postgres-hackers list but the complexity which i feel on that basis please allow me to explain my problem further. As i am working on sorting order , length and substring functions for Hindi text(Indian Language)... Here is the problem which i found in postgresql... after setting collating sequence in proper way(i.e. C) the order was on basis of unicode values...but in Hindi Language some of combined unicode values makes a single character similarly length is not appropriate for these reasons & hence substring operations so i designed a customized data type called IndCharand operations on it in order by statement the only function called is indchar_lt(defined for < operator).. Is your indchar_lt function declared IMMUTABLE? That would allow it's results to be cached instead of re-calculated every time. Now please guide me where is starting(where i can open connection to database) and ending of my programI feel only in indchar_lt function which will be called many times in order by statement causing performance degradation.. Have you created an index on that column? That would be a usual way to speed up an ORDER BY. NB, the function involved must be IMMUTABLE to be used in an index. as i am not much experienced this assumption may be wrong... My professor at University used to always say "measure measure measure". Postgres makes it easy to measure. Try putting EXPLAIN ANALYZE before your SELECT statement. Run the SELECT a couple of times first so that the OS can get stuff cached, then: do an EXPLAIN ANALYZE on the query, save the results then ANALYZE the tables involved and to another EXPLAIN ANALYZE on the query, save the results add the necessary index, ANALYZE then EXPLAIN ANALYZE. so my question remains as it is that is there any such thing which can be called at startup of psql.to make connection to database I'm really not sure what you mean by this. psql connects to the database on startup. regards Vinay Andrew Hammond wrote: Vinay Jain wrote: Hi thank you for such a useful information... but actually in my case if i keep table in disk it significantly degrades performance and even for a table of 10 rows it takes 1-2 minutes I think u r not beliving it ! am i right for example I create a table in which i use my customized data type say student create table student (Name INDCHAR //INDCHAR is customized data type age integer); now i give query like this select * from student order by name; it will search for it's comparator operator (<) and related function... in that function there is one lookup table if that table is in memory no problem! (oh but it can't be) if it is in disk my program makes connection to database and execute query which is just a select statement on a simple where condition of equality. then closes connection There's your problem. Creating database connections is an expensive operation. They are not intended to be opened and closed often or quickly. Open your database connection at the beginning of your program, and close it at the end. You could also throw an index on the column you're using in your order by clause, but that won't make a difference until your table get a little bigger. Please take further questions of this nature to the pgsql-novice list. so every time less than operator(<) is called it does the same task.. what i feel in table of 10 rows how many times the < operator will be called(NO idea but must be > 10 times) is there any solution.. thanks in advance regards vinay ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ERROR: heapgettup: failed ReadBuffer
jihuang <[EMAIL PROTECTED]> writes: > I put 36+ rows in a table , and now any select , update , analyze > ... command fail. > the log shows "ERROR: heapgettup: failed ReadBuffer", What Postgres version is this? AFAICS that error has been impossible for quite some time ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
On Mon, 3 May 2004, Alvaro Herrera wrote: > On Mon, May 03, 2004 at 02:14:18PM +1000, Gavin Sherry wrote: > > > It is implemented using shared memory. I got stuck when I considered the > > situation where we rung out of shared memory. Some emails in the archive > > suggested we just fire all listeners but I didn't like that. > > Can this be kept in backend local memory and then sent to the other > backends at transaction commit? If you run out of local memory you can > just spill to disk. (With shared memory this seems pretty hard to do.) > > I'm not sure how would one "send to the other backends." Maybe write > another file on disk, one for each remote backend? Surely this can be > done somehow. I've heard that on linux-2.6 they are implementing "POSIX > message queues" (not sure what those are anyway); maybe we can do that > on platforms that support it, for performance. What happens in the (unlikely) event that we never find space in shared memory? That's the problem that I am currently trying to solve. We currently just fire all the triggers but is that a great idea? Particularly if we support the passing of a message with a notify. Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
> I'm not sure how would one "send to the other backends." > Maybe write another file on disk, one for each remote > backend? Surely this can be done somehow. I've heard that > on linux-2.6 they are implementing "POSIX message queues" > (not sure what those are anyway); maybe we can do that on > platforms that support it, for performance. Dunno if this is relevant, but if you want to go with message queues, there is also SystemV message queues. Since postgresql already uses sysv semaphores and shared memory, this would perhaps be portable to more systems that pg supports today (though you'd still need some kind of abstraction layer, since e.g. win32 does not have it). (man msgsnd, msgrcv, msgctl, msgget //Magnus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Fixed directory locations in installs
Andrew Dunstan wrote: > Claudio Natoli said: > > > > > > Peter Eisentraut wrote: > >> Claudio Natoli wrote: > >> > I'm yet to see a convincing argument for why we can't adopt the > >> > "binary-location/../share" approach as submitted late March. AFAICS, > >> > it was rejected on the basis that it was not platform independent > >> > (no arguments there) and that we could not rely on the ".." > >> > approach. > >> > >> The only objection was that it hardcodes the layout already in the > >> source, which gives us no flexibility at all to try out different > >> installation layouts. If you want to compute the relative paths from > >> bindir to libdir etc. at build time based on actual configure > >> options, then I see no problem with that. > > > > But we want to resolve the locations at run-time, not build or > > configure time. For win32, I'm yet to see why this approach is > > egregious. > > > > Do you have an alternative solution to propose? > > > > I hope we are at cross purposes here, or else Peter's suggestion won't > fly - we need to be able to decouple some of these things from > configure/build time and defer them to installation/runtime. Any other > result will have us attracting curses from on high from the whole Windows > community, and other binary packagers won't get what I understand some > want. > > How about if we have a configuration flag --enable-relocation which would > require a fixed layout based on an indeterminate root. This would have the > following effects: > > . if prefix did not contain 'postgres' or 'pgsql' then 'postgresql' would > be appended. > . all *dir configure options would be forbidden - they would be based on > the prefix as now, and since it would contain 'postgres' the simple layout > we want would be used. > . binaries would have a DEFINE which would mean they would know they > should look for other binaries and shared files in locations which are > fixed relative to their own location rather than in the hardcoded > locations. Rather than turn off configure options, we can easily make this work for arbitary configure flags. Everything on Win32 drives off of the directory of the binary, which we can probe for at runtime. If they use the configure defaults, /lib is ../lib relative to /bin. If they specify: bin /usr/local/pgsql/bin lib /usr/local/lib/pgsql then lib is ../../lib/pgsql relative to bin. It is easy to write a C function that will give us the relative path from bin to any of the compiled in locations like /lib or /share, and we can use that instead of an abolute path. Not sure how we can do this for Unix except perhaps probe PATH. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Philip Warner <[EMAIL PROTECTED]> writes: > At 11:04 PM 3/05/2004, Tom Lane wrote: >> How confident are you in those "processes"? I don't know of any other >> mechanism for 'tuple concurrently updated' failures in ANALYZE than >> concurrent analyze runs ... > Fairly. In this particular instance the error was probably caused bu a > manually run VACUUM (part of me stressing it to encourage the error). Yeah, I see a process 14295 in your dump that seems to be trying to ANALYZE (at least, it's got write lock on pg_statistic...). 8631 is the incumbent ANALYZE, and it's got locks all over the place :-( I think what we have here is an evil side-effect of the change a couple versions back to allow standalone ANALYZE to run as a single transaction. A database-wide ANALYZE will therefore accumulate AccessShareLock on each table it touches, and it won't release these locks until commit. So the scenario goes like this: 1. Somewhere relatively early in its run, ANALYZE processes pg_statistic. So it's now holding AccessShareLock on pg_statistic. 2. As the ANALYZE proceeds, it issues sinval messages due to the updates it's making in pg_statistic. This is normal. There will be (at least) one such message per column analyzed, and it sounds like your database has many columns. Plus of course other catalog updates could be occurring in other backends. 3. There is at least one other backend sitting idle and therefore not reading sinval messages. So eventually the sinval queue gets 70% full and the SIGUSR2 escape-hatch is triggered. 4. The idle backends (and eventually non-idle ones, too, whenever they next reach the idle loop) try to do the NOTIFY thing, and get blocked trying to acquire AccessExclusiveLock on pg_listener. They will now be stuck until the ANALYZE completes. As a quick-hack fix, I think it would do to reduce the locks taken on pg_listener in async.c from AccessExclusiveLock to ExclusiveLock. This would serve the purpose of serializing async.c processing without creating a conflict against ANALYZE's AccessShareLock. Some other things we ought to think about for the future: * Is it really a good idea for database-wide ANALYZE to run as a single transaction? Holding all those locks is a recipe for deadlocks, even if they're as inoffensive as AccessShareLocks normally are. * Can we use something less heavyweight than ProcessIncomingNotify to deal with the sinval-clearing problem? Not only is that routine expensive, but it is a serialization bottleneck, which is exactly what we *don't* want in something that all the backends are getting told to do at the same time. I think the original motivation for that hack was because we didn't have a spare signal number available to dedicate to sinval response, but SIGUSR1 has been free for a couple releases now. I'm very tempted to commandeer it for sinval. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Fixed directory locations in installs
Andrew Dunstan writes: > How about if we have a configuration flag --enable-relocation which would > require a fixed layout based on an indeterminate root. This would have the > following effects: > > . if prefix did not contain 'postgres' or 'pgsql' then > 'postgresql' would be appended. > . all *dir configure options would be forbidden - they would > be based on the prefix as now, and since it would contain 'postgres' the > simple layout we want would be used. > . binaries would have a DEFINE which would mean they would know they > should look for other binaries and shared files in locations which are > fixed relative to their own location rather than in the hardcoded > locations. Looks good to me. Others? Cheers, Claudio --- Certain disclaimers and policies apply to all email sent from Memetrics. For the full text of these disclaimers and policies see http://www.memetrics.com/emailpolicy.html";>http://www.memetrics.com/em ailpolicy.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
On Mon, May 03, 2004 at 02:14:18PM +1000, Gavin Sherry wrote: > It is implemented using shared memory. I got stuck when I considered the > situation where we rung out of shared memory. Some emails in the archive > suggested we just fire all listeners but I didn't like that. Can this be kept in backend local memory and then sent to the other backends at transaction commit? If you run out of local memory you can just spill to disk. (With shared memory this seems pretty hard to do.) I'm not sure how would one "send to the other backends." Maybe write another file on disk, one for each remote backend? Surely this can be done somehow. I've heard that on linux-2.6 they are implementing "POSIX message queues" (not sure what those are anyway); maybe we can do that on platforms that support it, for performance. -- Alvaro Herrera () "In a specialized industrial society, it would be a disaster to have kids running around loose." (Paul Graham) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 11:04 PM 3/05/2004, Tom Lane wrote: Hm. What seems likely to have happened is that the sinval message queue got full. I agree (our emails crossed). That would have left all the idle backends trying to get exclusive lock on pg_listener, and if the ANALYZE subsequently reached pg_listener, its share lock would queue up behind those requests. What I see is that the ANALYZE job already has it in ACCESS SHARED mode, and keeps the lock until it dies with the 'concurrent update' error. What is not clear yet is why *all* of them are blocked. Seems something else must have some kind of lock already on pg_listener; but who? ANALYZE. Can you get a dump of the pg_locks view while this is happening? Attached. How confident are you in those "processes"? I don't know of any other mechanism for 'tuple concurrently updated' failures in ANALYZE than concurrent analyze runs ... Fairly. In this particular instance the error was probably caused bu a manually run VACUUM (part of me stressing it to encourage the error). Contrary to my other email, we haven't had the 'tuple concurrently updated' error since March (until today, with me messing around). What I do have is minute-by-minute dumps of pg_locks and ps for the day. At each hang there were many processes in 'async_notify waiting' and an ANALYZE job had the lock in shared mode. I do not have minute-by-minute logs for more than today, but there were 3 hangs today, and only one with the concurrent update error. It would be interesting if we could find a piece of backend code that did a 'select * from pg_listener', and hence locked it in ACCESS SHARED. At the moment, it looks like either the ANALYZE is triggering an error that causes it's backend to read pg_listeners, or it is dying while ANALYZING pg_listeners. The latter seems unlikely since it hangs frequently, and pg_listeners is empty. Does ANALYZE rollback if it dies? Could this account for the delay? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ 18-02-hackers.dat.gz Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 07:33 PM 3/05/2004, Philip Warner wrote: I'll try not to send any more emails until someone responds ;-) I also noticed this in SIInsertDataEntry sinvaladt.c: /* * Try to prevent table overflow. When the table is 70% full send a * WAKEN_CHILDREN request to the postmaster. The postmaster will send * a SIGUSR2 signal (ordinarily a NOTIFY signal) to all the backends. * This will force idle backends to execute a transaction to look * through pg_listener for NOTIFY messages, and as a byproduct of the * transaction start they will read SI entries. * * This should never happen if all the backends are actively executing * queries, but if a backend is sitting idle then it won't be starting * transactions and so won't be reading SI entries. * * dz - 27 Jan 1998 */ Would a long-running ANALYZE (or other activity on a busy database) cause the shared buffers to get to the 70% threshold while doing a long-running ANALYZE? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(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] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Philip Warner <[EMAIL PROTECTED]> writes: > I may have found the problem; all the hung processes show 'async_notify > waiting' in ps, and the ANALYZE eventually dies with 'tuple concurrently > updated'. > The routine 'ProcessIncomingNotify' in async.c does indeed try to lock > pg_listener (even if we're not using NOTIFY/LISTEN). Not sure why the > ANALYZE is locking the relation, though...but it is locked in AccessShareLock. Hm. What seems likely to have happened is that the sinval message queue got full. We currently deal with this by sending SIGUSR2 to all backends, which forces them through a NOTIFY-check cycle; a byproduct of the transaction start is to read pending sinval messages. (This is somebody's ugly quick hack from years ago; we really oughta find a less expensive way of doing it.) That would have left all the idle backends trying to get exclusive lock on pg_listener, and if the ANALYZE subsequently reached pg_listener, its share lock would queue up behind those requests. What is not clear yet is why *all* of them are blocked. Seems something else must have some kind of lock already on pg_listener; but who? Can you get a dump of the pg_locks view while this is happening? > And before anyone suggests it, we already have processes in place > to prevent to ANALYZEs running at the same time. How confident are you in those "processes"? I don't know of any other mechanism for 'tuple concurrently updated' failures in ANALYZE than concurrent analyze runs ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Fixed directory locations in installs
Claudio Natoli said: > > > Peter Eisentraut wrote: >> Claudio Natoli wrote: >> > I'm yet to see a convincing argument for why we can't adopt the >> > "binary-location/../share" approach as submitted late March. AFAICS, >> > it was rejected on the basis that it was not platform independent >> > (no arguments there) and that we could not rely on the ".." >> > approach. >> >> The only objection was that it hardcodes the layout already in the >> source, which gives us no flexibility at all to try out different >> installation layouts. If you want to compute the relative paths from >> bindir to libdir etc. at build time based on actual configure >> options, then I see no problem with that. > > But we want to resolve the locations at run-time, not build or > configure time. For win32, I'm yet to see why this approach is > egregious. > > Do you have an alternative solution to propose? > I hope we are at cross purposes here, or else Peter's suggestion won't fly - we need to be able to decouple some of these things from configure/build time and defer them to installation/runtime. Any other result will have us attracting curses from on high from the whole Windows community, and other binary packagers won't get what I understand some want. How about if we have a configuration flag --enable-relocation which would require a fixed layout based on an indeterminate root. This would have the following effects: . if prefix did not contain 'postgres' or 'pgsql' then 'postgresql' would be appended. . all *dir configure options would be forbidden - they would be based on the prefix as now, and since it would contain 'postgres' the simple layout we want would be used. . binaries would have a DEFINE which would mean they would know they should look for other binaries and shared files in locations which are fixed relative to their own location rather than in the hardcoded locations. None of this should need a single #ifdef WIN32 :-) (Would we need to turn off rpath for Unix in such a case? I suspect we would.) cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] ERROR: heapgettup: failed ReadBuffer
I put 36+ rows in a table , and now any select , update , analyze ... command fail. the log shows "ERROR: heapgettup: failed ReadBuffer", but any INSERT sql command success. the table schema is row| type | modifiers ---+-+-- test_id | integer | not null snapshot | timestamp without time zone | ip_client | inet| ip_server | inet| conn_time | integer | response_time | integer | response_head | character varying | Check constraints: "invalid_conn_time" CHECK (conn_time >= 0) "invalid_resp_time" CHECK (response_time >= 0) I didn't create any index, any one know why or suggestion to save the un-readable database? or anything I mis-configuration ?? Thanks for your help. June-Yen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Aclitem "high level description"
Dear committers, dear hackers,
> Subject: Re: [COMMITTERS] pgsql-server/src backend/utils/adt/acl.c ...
> > Ergo, my recommendation is to revert this change altogether. Fabien
> > should figure out the high-level description of what he wants to know
Please find attached as somehow requested a plpgsql implementation for a
"high-level description" (by that, I understand "relationnal", not
"functionnal") of acl in postgres.
The pg_{database,class,namespace,language,proc}_acl views are just
intermediate for the construction of the description from current acl
implementation. I'm not sure the implementation is right about the default
settings, but the spirit is there.
The actual descriptions are pg_{public,group,user}_acl, and pg_granted_acl
and pg_acl are examples of how to use these "high level descriptions".
You may notice that "high level" means two different things. High level
functions from the back-end point of view (has_privileves stuff), and high
level relationnal (something you can query). I need the second stuff.
Also, I must admit that I don't find it really motivating to have to
reimplement all this in C and to have it rejected for some reason such as
"we may change things in this area in some hypothetical future time", as
it was the motivation for rejecting 10 lines of code for 5 aclitem
accessor functions.
A general comment about pg_catalog is that it looks like it was designed
by a C programmer and cast later as an afterthought to a relationnal view.
It makes it quite uneasy to manipulate these tables for any other purpose
that the one that was foreseen by the designer from its internal point of
view, especially as it is not normalized and as opaque types are used.
Anyway, thanks in advance for your comments about this description, and
suggestions about the probability of acceptance it could have (if
implemented properly in C) in the backend, so as to replace quite infamous
aclitem accessors.
Have a nice day,
--
Fabien Coelho - [EMAIL PROTECTED]-- $Id: aclitem_rows.sql,v 1.7 2004/04/12 10:13:12 coelho Exp $
-- composite type for aclitem entries
CREATE TYPE aclitem_row AS
(object OID,-- object id
idtype INTEGER,-- public=0, user=1, group=2
grantee INTEGER, -- user id or group id or nothing
grantor INTEGER, -- user id who gave it
privs INTEGER, -- 15 bits of privileges
goptions INTEGER); -- 15 bits of grant options
CREATE OR REPLACE FUNCTION aclitem_as_rows(TEXT, TEXT, TEXT, TEXT, INTEGER)
RETURNS SETOF aclitem_row AS '
DECLARE
table ALIAS FOR $1;
object ALIAS FOR $2;
owner ALIAS FOR $3;
acl ALIAS FOR $4;
allrights ALIAS FOR $5;
res aclitem_row;
i INTEGER;
r RECORD;
BEGIN
FOR r IN EXECUTE
\'SELECT \'
|| object || \' AS object,\'
|| owner || \' AS owner,\'
|| acl || \' AS acl FROM \' || table
LOOP
res.object := r.object;
IF r.acl IS NOT NULL THEN
-- everything is explicit?
FOR i IN array_lower(r.acl,1) .. array_upper(r.acl, 1) LOOP
res.idtype := aclitem_idtype(r.acl[i]);
res.grantee := aclitem_grantee(r.acl[i]);
IF res.idtype = 0 THEN
res.grantee = NULL;
END IF;
res.grantor := aclitem_grantor(r.acl[i]);
res.privs := aclitem_privs(r.acl[i]);
res.goptions := aclitem_goptions(r.acl[i]);
RETURN NEXT res;
END LOOP;
ELSE
-- owner has all?
res.idtype := 1;
res.grantee := r.owner;
res.grantor := r.owner;
res.privs := allrights;
res.goptions := allrights;
RETURN NEXT res;
-- public has nope? default?
res.idtype := 0;
res.grantee := NULL;
res.grantor := r.owner;
res.privs := 0;
res.privs := 0;
RETURN NEXT res;
END IF;
END LOOP;
RETURN;
END;'
LANGUAGE plpgsql;
-- row acls...
CREATE VIEW pg_database_acl AS
SELECT *
FROM aclitem_as_rows('pg_database', 'oid', 'datdba', 'datacl', 1536);
CREATE VIEW pg_class_acl AS
SELECT *
FROM aclitem_as_rows('pg_class', 'oid', 'relowner', 'relacl', 127);
CREATE VIEW pg_namespace_acl AS
SELECT *
FROM aclitem_as_rows('pg_namespace', 'oid', 'nspowner', 'nspacl', 768);
CREATE VIEW pg_language_acl AS
SELECT *
FROM aclitem_as_rows('pg_language', 'oid', '1', 'lanacl', 256);
CREATE VIEW pg_proc_acl AS
SELECT *
FROM aclitem_as_rows('pg_proc', 'oid', 'proowner', 'proacl', 128);
--
CREATE VIEW pg_public_acl AS
SELECT object, grantor, privs, goptions
FROM pg_database_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_class_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_namespace_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_language_acl
WHERE idtype = 0
UNION ALL
SELECT object, grantor, privs, goptions
FROM pg_proc_acl
WHERE idtype = 0;
--
CREATE VIEW pg_user_acl AS
SELECT object, grantee, grantor, privs, goptions
FROM pg_database_acl
WHERE idtype = 1
UNION ALL
SELECT object, grantee, grantor, pri
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 06:21 PM 3/05/2004, Philip Warner wrote: 'tuple concurrently updated' I lied. The database DO NOT logs show the same error in each case where a long delay has occurred. It happens sometimes; recent process logs do show the 'async_notify waiting' status, however. I'll try not to send any more emails until someone responds ;-) Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 06:21 PM 3/05/2004, Philip Warner wrote: 'tuple concurrently updated' The database logs show the same error in each case where a long delay has occurred. And before anyone suggests it, we already have processes in place to prevent to ANALYZEs running at the same time. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
Further to this, ProcessIncomingNotify seems to hold the lock on the listener relation until it's current transaction exits. If the ANALYZE was not the source of the error, but was just another victim, does that mean it might hold the lock for a very long time if the analyze is lengthy? At 02:54 PM 3/05/2004, Tom Lane wrote: Please dig deeper. I may have found the problem; all the hung processes show 'async_notify waiting' in ps, and the ANALYZE eventually dies with 'tuple concurrently updated'. The routine 'ProcessIncomingNotify' in async.c does indeed try to lock pg_listener (even if we're not using NOTIFY/LISTEN). Not sure why the ANALYZE is locking the relation, though...but it is locked in AccessShareLock. I can send a log of my investigations if necessary. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 02:54 PM 3/05/2004, Tom Lane wrote: Please dig deeper. I may have found the problem; all the hung processes show 'async_notify waiting' in ps, and the ANALYZE eventually dies with 'tuple concurrently updated'. The routine 'ProcessIncomingNotify' in async.c does indeed try to lock pg_listener (even if we're not using NOTIFY/LISTEN). Not sure why the ANALYZE is locking the relation, though...but it is locked in AccessShareLock. I can send a log of my investigations if necessary. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] inconsistent owners in newly created databases?
Dear Thomas,
> * create the database with the new owner specified.
>
> -- As a superuser in the newly created database
> update pg_am set amowner = {userid}
> update pg_class set relowner = {userid}
You don't want to update ownership of tables in system schemas.
> update pg_conversion set conowner = {userid}
> update pg_namespace set nspowner = {userid}
As for SCHEMAs, I would not do that for system schemas
(pg_%, information_schema)...
> update pg_opclass set opcowner = {userid}
> update pg_operator set oprowner = {userid}
> update pg_proc set proowner = {userid}
I'm not sure system functions owner should be change. Also, call handlers
for languages should not change owner.
> update pg_type set typowner = {userid}
>
> Are there any security problems that this would cause? Perhaps these
> should be done by the system automatically.
I think that something along the line you describe should be done by the
system. However database creation does not actually connect to the new
database, the template base directory is simply copied with a "cp -r".
It is unclear to me at the time when these updates should be performed.
After the createdb? Deferred to the first connection to the database?
--
Fabien Coelho - [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] OT: Open source developer survey
Hi guys, I know this is off topic, but if there are any developers with sourceforge accounts here, they might be interested in filling out this query which came throught the phpPgAdmin lists. It seems legit :) Chris Original Message Subject: [ppa-dev] FASD project: Online survey launched Date: Mon, 3 May 2004 09:13:50 +0200 From: Benno Luthiger <[EMAIL PROTECTED]> Reply-To: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Dear Open Source developer I am doing a research project on "Fun and Software Development" in which I kindly invite you to participate. You will find the online survey under http://fasd.ethz.ch/qsf/. The questionnaire consists of 53 questions and you will need about 15 minutes to complete it. With the FASD project (Fun and Software Development) we want to define the motivational significance of fun when software developers decide to engage in Open Source projects. What is special about our research project is that a similar survey is planned with software developers in commercial firms. This procedure allows the immediate comparison between the involved individuals and the conditions of production of these two development models. Thus we hope to obtain substantial new insights to the phenomenon of Open Source Development. With many thanks for your participation, Benno Luthiger PS: The results of the survey will be published under http://www.isu.unizh.ch/fuehrung/blprojects/FASD/. We have set up the mailing list [EMAIL PROTECTED] for this study. Please see http://fasd.ethz.ch/qsf/mailinglist_de.html for registration to this mailing list. ___ Benno Luthiger Swiss Federal Institute of Technology Zurich 8092 Zurich Mail: benno.luthiger(at)id.ethz.ch ___ --- This SF.Net email is sponsored by: Oracle 10g Get certified on the hottest thing ever to hit the market... Oracle 10g. Take an Oracle 10g class now, and we'll give you the exam FREE. http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click ___ phpPgAdmin-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/phppgadmin-devel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Fixed directory locations in installs
Peter Eisentraut wrote: > Claudio Natoli wrote: > > Peter Eisentraut wrote: > > > Claudio Natoli wrote: > > > > I'm yet to see a convincing argument for why we can't adopt the > > > > "binary-location/../share" approach as submitted late March. > > > > AFAICS, it was rejected on the basis that it was not platform > > > > independent (no arguments there) and that we could not rely on > > > > the ".." approach. > > > > > > The only objection was that it hardcodes the layout already in the > > > source, which gives us no flexibility at all to try out different > > > installation layouts. If you want to compute the relative paths > > > from bindir to libdir etc. at build time based on actual configure > > > options, then I see no problem with that. > > > > But we want to resolve the locations at run-time, not build or > > configure time. > > If that is your intention then your original proposal was > wrong to begin with, because it resolves the locations even before build time. Huh? I guess it could be seen like that, as the subdirectory component is fixed. But from a win32/installer POV the only dir that matters IMHO is the install root dir, which certainly is not fixed before build time in the original proposal. I suspect we are talking at cross-purposes, because that seems like exactly what you were asking for in the second paragraph here: http://archives.postgresql.org/pgsql-hackers/2004-05/msg00064.php Got an alternative run-time/win32-install-time solution to offer? Cheers, Claudio --- Certain disclaimers and policies apply to all email sent from Memetrics. For the full text of these disclaimers and policies see http://www.memetrics.com/emailpolicy.html";>http://www.memetrics.com/em ailpolicy.html ---(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] Fixed directory locations in installs
> The only hard facts that we can use are hardcoded/compiled-in > locations and explicit information passed via command-line > arguments or environment variables. None of this seems to be > useful for Windows installations. As far as I recall, the > Windows installation routines only ask you for one > installation directory but not all the individual ones. If > this is true, then we could hardcode relative paths, but > maybe I'm mistaken. Can someone give a couple of full > examples of typical Windows installation layouts? Not sure if this is exactly what you're looking for, but here's a try. Microsoft SQL Server 2000: Base directory: c:\Program Files\Microsoft SQL Server Server root:c:\Program Files\Microsoft SQL Server\MSSQL .EXE & .DLL:c:\Program Files\Microsoft SQL Server\MSSQL\Binn .sql [EMAIL PROTECTED]: c:\Program Files\Microsoft SQL Server\MSSQL\Install Internal .DLLs: c:\Program Files\Microsoft SQL Server\80\Com Client tools: c:\Program Files\Microsoft SQL Server\80\Tools Default datadir:c:\Program Files\Microsoft SQL Server\MSSQL\Data Not an example of a simple layout, certainly. For something a little more "consistent", Exchange Server 2000: Base directory: c:\Program Files\exchsrvr .EXE/.DLL: c:\Program Files\exchsrvr\bin Default Datadir:c:\Program Files\exchsrvr\mdbdata Language DLLs: c:\Program Files\exchsrvr\res Schema @inst: c:\Program Files\exchsrvr\Schema Log files: c:\Program Files\exchsrvr\.log As you can see, this one is more clean. You can typically change the Base directory, as well as the data directory. The other subdirectories are normally locked relative to the base directory. Some will let you split out client side files and server side files into different directories, if both are installed. //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] libpq, binary cursors, & arrays
Hi all,
I'm putting together a small package of macros and functions
to help deal with binary cursor results from libpq, but I've run into a
bit of a stumbling block with regard to array results,
for example:
ArrayType *arr;
uint64_t *lin;
...
res = PQexecParams(conn, "select '{1,2,3}'::bigint[]", 1, NULL,
paramValues, NULL, NULL, 1);
...
arr = (ArrayType *)PQgetvalue(res, 0, 1);
lin = (uint64_t *)ARR_DATA_PTR(lin);
printf("%lld\n", lin[0]);
does not produce the expected results
Any pointers would be much appreciated,
Thanks in advance,
---
Cody Pisto <[EMAIL PROTECTED]>
---(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] Fixed directory locations in installs
Peter Eisentraut wrote: > Claudio Natoli wrote: > > I'm yet to see a convincing argument for why we can't adopt the > > "binary-location/../share" approach as submitted late March. AFAICS, > > it was rejected on the basis that it was not platform independent (no > > arguments there) and that we could not rely on the ".." approach. > > The only objection was that it hardcodes the layout already in the > source, which gives us no flexibility at all to try out different > installation layouts. If you want to compute the relative paths from > bindir to libdir etc. at build time based on actual configure > options, then I see no problem with that. But we want to resolve the locations at run-time, not build or configure time. For win32, I'm yet to see why this approach is egregious. Do you have an alternative solution to propose? Cheers, Claudio --- Certain disclaimers and policies apply to all email sent from Memetrics. For the full text of these disclaimers and policies see http://www.memetrics.com/emailpolicy.html";>http://www.memetrics.com/em ailpolicy.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fixed directory locations in installs
Claudio Natoli wrote: > Peter Eisentraut wrote: > > Claudio Natoli wrote: > > > I'm yet to see a convincing argument for why we can't adopt the > > > "binary-location/../share" approach as submitted late March. > > > AFAICS, it was rejected on the basis that it was not platform > > > independent (no arguments there) and that we could not rely on > > > the ".." approach. > > > > The only objection was that it hardcodes the layout already in the > > source, which gives us no flexibility at all to try out different > > installation layouts. If you want to compute the relative paths > > from bindir to libdir etc. at build time based on actual configure > > options, then I see no problem with that. > > But we want to resolve the locations at run-time, not build or > configure time. If that is your intention then your original proposal was wrong to begin with, because it resolves the locations even before build time. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] inconsistent owners in newly created databases?
Fabien COELHO wrote:
>Dear hackers,
>
>It seems to me that the current default setup for a new database which is
>given to some user is not consistent (createdb -O calvin foo or
>CREATE DATABASE foo WITH OWNER calvin).
>
>Indeed, although the database belongs to the owner, the "public" schema
>still belongs to the database super user, as it was the case in template1.
>As a consequence, the owner of the database CANNOT change the rights of
>the schema, hence he cannot prevent anyone from creating a new table in
>the public schema! However, has he owns the database, he can prevent user
>from creating temporary tables... Not really consistent.
>
>
This is a real problem if that owner wants to drop or create types,
operators, or precreated tables in the template that was copied. It
seems that you would want to go through and give the owner all the
ownership on items that were possible. I've used a database template
with the pg_crypto added in and some other custom routines and found
that the owner of the database couldn't update or access those copied
tables because of the permission on those tables and objects.
* create the database with the new owner specified.
-- As a superuser in the newly created database
update pg_am set amowner = {userid}
update pg_class set relowner = {userid}
update pg_conversion set conowner = {userid}
update pg_namespace set nspowner = {userid}
update pg_opclass set opcowner = {userid}
update pg_operator set oprowner = {userid}
update pg_proc set proowner = {userid}
update pg_type set typowner = {userid}
Are there any security problems that this would cause? Perhaps these
should be done by the system automatically.
>Dropping (the owner of a database can do that) and recreating the schema
>is not a real fix, because all installation performed on template1
>(plpgsql, functions...) would be lost.
>
>So it seems to me that the "public" schema should also belong to the owner
>of the database. I cannot foresee all consequences, but the current
>situation is really inconsistent.
>
>Any comment?
>
>
>
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] Fixed directory locations in installs
Claudio Natoli wrote: > I'm yet to see a convincing argument for why we can't adopt the > "binary-location/../share" approach as submitted late March. AFAICS, > it was rejected on the basis that it was not platform independent (no > arguments there) and that we could not rely on the ".." approach. The only objection was that it hardcodes the layout already in the source, which gives us no flexibility at all to try out different installation layouts. If you want to compute the relative paths from bindir to libdir etc. at build time based on actual configure options, then I see no problem with that. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
