Re: [HACKERS] adding operators
On Mon, 1 Oct 2007 12:38:07 +1000 Brendan Jurd [EMAIL PROTECTED] wrote: On 10/1/07, Islam Hegazy [EMAIL PROTECTED] wrote: I am a graduate student in the University of Calgary. I want to add some new operators to PostgreSQL to perform some specific tasks in a project I am working in. My problem is that I cannot find my way into the code, where should I start and where to find the documentation for the code. There's no need to hack Postgres to add operators. You can do so by defining functions using CREATE FUNCTION, and then hooking operators up to them using CREATE OPERATOR. And if you need to add C code you can do that too. Check some examples in contrib such as my chkpass module for examples. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] ERROR: invalid byte sequence from psql - Works perfectly from pgAdmin III query window
Why the Fun_ABC1 is created and Fun_ABC12 is raising the following error, while run through psql, ( I Could create both the functions from PgAdmin III query ) ERROR: invalid byte sequence for encoding UTF8: 0x93 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. CREATE OR REPLACE FUNCTION Fun_ABC1() RETURNS VOID AS $BODY$ /***Description : Desc1 - Description contains character ***/ CREATE OR REPLACE FUNCTION Fun_ABC12() RETURNS VOID AS $BODY$ /***Description : Desc1 - Description contains character ***/ Visit our Website at http://www.rmesi.co.in This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications. Freedom of Information Act 2000 This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests. This email has been scanned for viruses by Trend ScanMail.
Re: [HACKERS] Latest ecpg patch broke MSVC build
On Sun, Sep 30, 2007 at 11:30:35PM -0400, Andrew Dunstan wrote: Tom Lane wrote: This morning's ecpg patch certainly seems to have been snake-bit. Although the Windows gcc buildfarm members seem happy, the MSVC ones are all failing with Linking... Creating library Release\libecpg\libecpg.lib and object Release\libecpg\libecpg.exp libecpg.exp : error LNK2001: unresolved external symbol [EMAIL PROTECTED] .\Release\libecpg\libecpg.dll : fatal error LNK1120: 1 unresolved externals I see that DllMain() got added to misc.c, so it's not obvious what's wrong here. Some adjustment needed in the MSVC build scripts maybe? It is building with thread.c but it should not be unless I am misreading It's been building with thread.c before this patch. And the problem doesn't go away if you ermove thread.c. The problem seems to be that it tries to export a decorated DllMain ([EMAIL PROTECTED]) which is listed in the object file, but it's unable to export it. Will need to dig further. The reason it doesn't happen on mingw is likely the horrible kludge that is export-all-symbols-in-all-files that we've only partially been able to emulate. Since this is an actual API library, perhaps a proper fix is to create a .def file listing the exports in it, the same way we do for libpq? And then we could (should!) also filter the exports the same ways as we do for libpq these days. (see the exports.txt file in libpq) I'll try to find time to look forther at this meanwhile, but if someone can confirm that donig an explicit export list is a good way to go, I can confirm that donig that fixes the build problem :-) //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Latest ecpg patch broke MSVC build
On Sun, Sep 30, 2007 at 11:46:00PM -0400, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: It is building with thread.c but it should not be unless I am misreading the Makefile. The makefile processing in Project.pm doesn't look nearly powerful enough to handle this: # thread.c is needed only for non-WIN32 implementation of path.c ifneq ($(PORTNAME), win32) OBJS += thread.o endif Hmm, sounds like a problem, but why was it not a problem before? It's not realliy a problem since the stuff in thread.c is #ifdefed away on Windows in most cases anyway. All we do is import a small piece of code we'll never use.. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] msvc = VC7 understands __FUNCTION__
On Fri, Sep 28, 2007 at 10:43:17PM +0200, Hannes Eder wrote: Hi, Starting from version VC7 msvc supports __FUNCTION__, so I think this could be enabled in pg_config.h.win32, see attached diff. Applied, thanks. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] IDE
Hello, I am having problems of productivity with IDE that I am using. Exists some IDE that recommended to develop postgresql? I am thankful, -- Pedro Belmino.
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On 9/22/07, Tom Lane [EMAIL PROTECTED] wrote: Please try that experiment with all three configurations on both versions: * autovacuum off * autovacuum on, autovacuum_vacuum_cost_delay = 0 * autovacuum on, autovacuum_vacuum_cost_delay = 20 I've finally found some time to spend on these tests. Here are the results: * 8.2.5 * - autovacuum off + ANALYZE: less than 16 minutes (figures previously posted in this thread) - default configuration of 8.2 - autovacuum on, delay 0: 16m29 - autovacuum on, delay 20: 16m13 (I didn't repeat the run but we can see that autovacuum doesn't introduce too much slowdown during the restore operation) * 8.3devel freshly compiled * - autovacuum off: 14m39 - autovacuum on, delay 0: 15m32 - autovacuum on, delay 20: 51m37 (the box is idle during a large amount of this time) - default configuration of 8.3devel -- Guillaume ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] IDE
On 10/1/07, Pedro Belmino [EMAIL PROTECTED] wrote: Hello, I am having problems of productivity with IDE that I am using. Exists some IDE that recommended to develop postgresql? Hello Pedro, You are probably looking for a tool like pgAdmin (http://www.pgadmin.org) or PhpPgAdmin (http://phppgadmin.sourceforge.net). There are also some commercial applications : http://www.postgresql.org/download/commercial However, please take notice that your question is not suited for the pgsql-hackers mailing list : this list is for discussions about developing PostgreSQL itself. Cheers, Adrian Maier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Guillaume Smet wrote: On 9/22/07, Tom Lane [EMAIL PROTECTED] wrote: Please try that experiment with all three configurations on both versions: * autovacuum off * autovacuum on, autovacuum_vacuum_cost_delay = 0 * autovacuum on, autovacuum_vacuum_cost_delay = 20 I've finally found some time to spend on these tests. Here are the results: * 8.2.5 * - autovacuum off + ANALYZE: less than 16 minutes (figures previously posted in this thread) - default configuration of 8.2 - autovacuum on, delay 0: 16m29 - autovacuum on, delay 20: 16m13 (I didn't repeat the run but we can see that autovacuum doesn't introduce too much slowdown during the restore operation) * 8.3devel freshly compiled * - autovacuum off: 14m39 - autovacuum on, delay 0: 15m32 - autovacuum on, delay 20: 51m37 (the box is idle during a large amount of this time) - default configuration of 8.3devel some additional datapoints: autovacuum on, delay 20: 8h 40min autovacuum on, delay 0: 4h 23min for restoring a database of around 120GB (on disk size) ... In the delay 20 case the restore is more or less waiting hours for grabbing locks during PK creation held by autovacuum (which tries to analyze the tables). Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] IDE
On Oct 1, 2007, at 10:27 , Adrian Maier wrote: On 10/1/07, Pedro Belmino [EMAIL PROTECTED] wrote: Hello, I am having problems of productivity with IDE that I am using. Exists some IDE that recommended to develop postgresql? Hello Pedro, You are probably looking for a tool like pgAdmin (http:// www.pgadmin.org) or PhpPgAdmin (http://phppgadmin.sourceforge.net). There are also some commercial applications : http://www.postgresql.org/download/ commercial However, please take notice that your question is not suited for the pgsql-hackers mailing list : this list is for discussions about developing PostgreSQL itself. That may be what he means. Unfortunately develop PostgreSQL can be taken both ways. In case he's working on internals, I believe Emacs is used by a number of PostgreSQL hackers. And as for developing PostgreSQL-backed applications, I find $EDITOR + psql to work quite well. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On 10/1/07, Guillaume Smet [EMAIL PROTECTED] wrote: * 8.3devel freshly compiled * - autovacuum off: 14m39 - autovacuum on, delay 0: 15m32 - autovacuum on, delay 20: 51m37 (the box is idle during a large amount of this time) - default configuration of 8.3devel I made a few more tests with 8.3devel and different autovacuum_vacuum_cost_delay: - autovacuum off: 14m39 - autovacuum on, delay 0: 15m32 - autovacuum on, delay 10: 17m19 (*new*) - autovacuum on, delay 20: 51m37 - default - autovacuum on, delay 40: 90m46 (*new*) -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] IDE
Le lundi 01 octobre 2007, Michael Glaesemann a écrit : That may be what he means. Unfortunately develop PostgreSQL can be taken both ways. In case he's working on internals, I believe Emacs is used by a number of PostgreSQL hackers. And as for developing PostgreSQL-backed applications, I find $EDITOR + psql to work quite well. In this case the slides from Neil Gavin presentation about PostgreSQL hacking may be a good starting read: http://neilconway.org/talks/hacking/ IIRC, they also mention cscope (there's a user friendly kscope GUI of it) to easily browse code structures and call graphs. http://cscope.sourceforge.net/ http://kscope.sourceforge.net/ Hope this helps, regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Enforcing database encoding and locale match
Tom Lane wrote: I tried this program on Mac OS X 10.4.10 (the current release) and found out that what that OS mostly returns is the encoding portion of the locale name, for instance FWIW I tried this program here, and I get C ... ANSI_X3.4-1968 - NO MATCH POSIX ... ANSI_X3.4-1968 - NO MATCH Note the funny name. Trying initdb with LC_ALL=C correctly uses SQL_ASCII (I saw the special case in chklocale.c), but I'm wondering if we should list those names explicitely. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG on NFS may be just a bad idea
Tom, Maybe we need to actively discourage people from running Postgres against NFS-mounted data directories. Shane Kerr's paper cited above mentions some other rather scary properties, including O_EXCL file creation not really working properly. Wouldn't you be describing a Linux-specific issue, though? And possibly kernel-specific? It's hard to reconcile this with the real-world performance of PostgreSQL on NFS, which is happening all over the place. Most notably, Joe Conway's 20,000 txn/sec. I *do* think it's an accurate statement that if you're going to use Postgres, or any other OLTP database, on NFS you'd better have access to a NAS expert. But to say that it's a bad idea even if you have expert help is probably going to far. --Josh Berkus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: some additional datapoints: autovacuum on, delay 20: 8h 40min autovacuum on, delay 0: 4h 23min I realize this isn't directly addressing the problem but perhaps part of the solution would be to start advocating the use of pg_restore -1 ? That would solve the problem for the narrow case of pg_restore. In the long run we could think about exposing some kind of command for pg_restore to use which would disable autovacuum from touching a table. (Or take a session-level lock on the table -- shudder) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] PG on NFS may be just a bad idea
Josh Berkus [EMAIL PROTECTED] writes: Maybe we need to actively discourage people from running Postgres against NFS-mounted data directories. It's hard to reconcile this with the real-world performance of PostgreSQL on NFS, which is happening all over the place. Most notably, Joe Conway's 20,000 txn/sec. This is not a question of performance, it is a question of whether you are willing to tolerate corner-case misbehaviors. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Enforcing database encoding and locale match
Alvaro Herrera [EMAIL PROTECTED] writes: FWIW I tried this program here, and I get C ... ANSI_X3.4-1968 - NO MATCH POSIX ... ANSI_X3.4-1968 - NO MATCH Note the funny name. Trying initdb with LC_ALL=C correctly uses SQL_ASCII (I saw the special case in chklocale.c), but I'm wondering if we should list those names explicitely. Since we're already special-casing C/POSIX, I don't see a need. It looks a bit hopeless to keep up with all the possibilities anyway --- by my count we've tested four different platforms so far and gotten four different answers for the CODESET name for C :-( Linux ANSI_X3.4-1968 Darwin (empty) Solaris 646 HP-UX roman8 regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] adding operators
Thanks for this information. It was really helpful. Another problem that is facing me is altering existing functions. For example, what if I want to change the execution of the SUM function to work as follows: select sum(a) from mytable w(5); which means to sum only 5 records or records that arrived in the last 5 minutes. Do I need to change the core code of PostgreSQL to implement such thing? Regards Islam Hegazy - Original Message - From: Brendan Jurd [EMAIL PROTECTED] To: Islam Hegazy [EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.org Sent: Sunday, September 30, 2007 8:38 PM Subject: Re: [HACKERS] adding operators On 10/1/07, Islam Hegazy [EMAIL PROTECTED] wrote: I am a graduate student in the University of Calgary. I want to add some new operators to PostgreSQL to perform some specific tasks in a project I am working in. My problem is that I cannot find my way into the code, where should I start and where to find the documentation for the code. There's no need to hack Postgres to add operators. You can do so by defining functions using CREATE FUNCTION, and then hooking operators up to them using CREATE OPERATOR. http://www.postgresql.org/docs/8.2/static/xoper.html http://www.postgresql.org/docs/8.2/static/sql-createoperator.html Regards, BJ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Gregory Stark [EMAIL PROTECTED] writes: I realize this isn't directly addressing the problem but perhaps part of the solution would be to start advocating the use of pg_restore -1 ? That would solve the problem for the narrow case of pg_restore. Well, that would do as a quick workaround, as would disabling autovacuum during the restore. In the long run we could think about exposing some kind of command for pg_restore to use which would disable autovacuum from touching a table. Ugh. I think a real solution probably involves a mechanism that kicks autovacuum off a table when someone else wants an exclusive lock on it. This is a little bit worrisome because a steady stream of lock requests could prevent autovac from ever finishing the table, but it seems clear that not doing this is going to make autovac a lot more intrusive than people will stand for. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Gregory Stark wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: some additional datapoints: autovacuum on, delay 20: 8h 40min autovacuum on, delay 0: 4h 23min I realize this isn't directly addressing the problem but perhaps part of the solution would be to start advocating the use of pg_restore -1 ? That would solve the problem for the narrow case of pg_restore. In the long run we could think about exposing some kind of command for pg_restore to use which would disable autovacuum from touching a table. (Or take a session-level lock on the table -- shudder) In my opinion, CREATE INDEX shouldn't need to wait for autovacuum to finish, regardless of who issued it. This is like priority inversion; the autovacuum is not urgent, and runs slowly to avoid disturbing others. But if it keeps the higher priority CREATE INDEX from starting, it is disturbing others. Could we arrange things so that the effective cost delay of the autovacuum process that's in the way gets set to 0 (like priority inheritance)? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] adding operators
Islam Hegazy wrote: Thanks for this information. It was really helpful. Another problem that is facing me is altering existing functions. For example, what if I want to change the execution of the SUM function to work as follows: select sum(a) from mytable w(5); which means to sum only 5 records or records that arrived in the last 5 minutes. Do I need to change the core code of PostgreSQL to implement such thing? The first can be done with a limit clause, the second using a where clause on a timestamp column - there is no good case that I can see for either usage, not to mention the question of standards compatibility. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] IDE
On 10/1/07, Michael Glaesemann [EMAIL PROTECTED] wrote: Hello Pedro, You are probably looking for a tool like pgAdmin (http:// www.pgadmin.org) or PhpPgAdmin (http://phppgadmin.sourceforge.net). There are also some commercial applications : http://www.postgresql.org/download/ commercial However, please take notice that your question is not suited for the pgsql-hackers mailing list : this list is for discussions about developing PostgreSQL itself. That may be what he means. Unfortunately develop PostgreSQL can be taken both ways. In case he's working on internals, I believe Emacs is used by a number of PostgreSQL hackers. And as for developing PostgreSQL-backed applications, I find $EDITOR + psql to work quite well. Oh, you are right Michael. I've missed the second interpretation of Pedro's words. My apologises if I got it wrong ... -- Adrian Maier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] IDE
If you are talking about working on the code (internals), I find eclipse works very well for working on PostgreSQL. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Pedro Belmino Sent: Monday, October 01, 2007 6:42 AM To: pgsql-hackers@postgresql.org Subject: [HACKERS] IDE Hello, I am having problems of productivity with IDE that I am using. Exists some IDE that recommended to develop postgresql? I am thankful, -- Pedro Belmino.
Re: [HACKERS] PG on NFS may be just a bad idea
On Mon, 2007-10-01 at 10:13 -0700, Josh Berkus wrote: Maybe we need to actively discourage people from running Postgres against NFS-mounted data directories. Shane Kerr's paper cited above mentions some other rather scary properties, including O_EXCL file creation not really working properly. Wouldn't you be describing a Linux-specific issue, though? And possibly kernel-specific? Possibly, though if you have any specific refutations of the Kerr paper then it would be a good idea to air them. It isn't enough to just hint some exist. It's hard to reconcile this with the real-world performance of PostgreSQL on NFS, which is happening all over the place. Most notably, Joe Conway's 20,000 txn/sec. I *do* think it's an accurate statement that if you're going to use Postgres, or any other OLTP database, on NFS you'd better have access to a NAS expert. But to say that it's a bad idea even if you have expert help is probably going to far. I can see many papers on database performance on NFS, but I don't see any discussion of potential reliability concerns. If anybody sits near an NAS expert, it would be great to have that discussion. I have found some comments that other databases require specific configuration settings to ensure efficient and correct usage of NFS to access NAS storage devices. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PG on NFS may be just a bad idea
On Mon, 2007-10-01 at 19:36 +0100, Simon Riggs wrote: I *do* think it's an accurate statement that if you're going to use Postgres, or any other OLTP database, on NFS you'd better have access to a NAS expert. But to say that it's a bad idea even if you have expert help is probably going to far. I can see many papers on database performance on NFS, but I don't see any discussion of potential reliability concerns. If anybody sits near an NAS expert, it would be great to have that discussion. http://blogs.netapp.com/dave/2007/08/oracle-optimize.html -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Text - C string
As discussed on -hackers, I'm trying to get rid of some redundant code by creating a widely useful set of functions to convert between text and C string in the backend. The new extern functions, declared in include/utils/builtins.h and defined in backend/utils/adt/varlena.c, are: char * text_cstring(const text *t) char * text_cstring_limit(const text *t, int len) text * cstring_text(const char *s) text * cstring_text_limit(const char *s, int len) Within varlena.c, the actual conversions are performed by: char * do_text_cstring(const text *t, const int len) text * do_cstring_text(const char *s, const int len) These functions now do the work for the fmgr functions textin and textout, as well as being directly accessible by backend code. I've searched through the backend for any code which converted between text and C string manually (with memcpy and VARDATA), replacing with calls to one of the four new functions as appropriate. I came across some areas which were using the same, or similar, conversion technique on other varlena data types, such as bytea or xmltype. In cases where the conversion was completely identical I used the new functions. In cases with any differences (even if they seemed minor) I played it safe and left them alone. I'd now like to submit my work so far for review. This patch compiled cleanly on Linux and passed all parallel regression tests. It appears to be performance-neutral based on a few rough tests; I haven't tried to profile the changes in detail. There is still a lot of code out there using DirectFunctionCall1 to call text(in|out)). I've decided to wait for some community feedback on the patch as it stands before replacing those calls. There are a great many, and it would be a shame to have to go through them more than once. I would naively expect that replacing fmgr calls with direct calls would lead to a performance gain (no fmgr overhead), but honestly I'm not sure whether that would actually make a difference. Thanks for your time, BJ text-cstring_1.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Heikki Linnakangas escribió: In my opinion, CREATE INDEX shouldn't need to wait for autovacuum to finish, regardless of who issued it. This is like priority inversion; the autovacuum is not urgent, and runs slowly to avoid disturbing others. But if it keeps the higher priority CREATE INDEX from starting, it is disturbing others. Could we arrange things so that the effective cost delay of the autovacuum process that's in the way gets set to 0 (like priority inheritance)? This is an interesting idea, but I think it's attacking the wrong problem. To me, the problem here is that an ANALYZE should not block CREATE INDEX or certain forms of ALTER TABLE. We do have a mechanism for changing the cost-delay parameters of an autovac worker, but even if we made it to work, it would still delay the other operation needlessly until the end of the analyze. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Alvaro Herrera [EMAIL PROTECTED] writes: This is an interesting idea, but I think it's attacking the wrong problem. To me, the problem here is that an ANALYZE should not block CREATE INDEX or certain forms of ALTER TABLE. I doubt that that will work; in particular I'm pretty dubious that you can safely make CREATE INDEX and VACUUM run together. Since they'd be unlikely to be using the identical OldestXmin horizon, you'd likely end up with dangling index entries (ie, CREATE INDEX indexes a tuple that the VACUUM removes shortly afterward). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: This is an interesting idea, but I think it's attacking the wrong problem. To me, the problem here is that an ANALYZE should not block CREATE INDEX or certain forms of ALTER TABLE. I doubt that that will work; in particular I'm pretty dubious that you can safely make CREATE INDEX and VACUUM run together. Since they'd be unlikely to be using the identical OldestXmin horizon, you'd likely end up with dangling index entries (ie, CREATE INDEX indexes a tuple that the VACUUM removes shortly afterward). I think the main issue is ANALYZE not VACUUM (at least in this thread) since it's DB load times that are in question. Can CREATE INDEX and ANALYZE be made to run concurrently? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Matthew T. O'Connor escribió: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: This is an interesting idea, but I think it's attacking the wrong problem. To me, the problem here is that an ANALYZE should not block CREATE INDEX or certain forms of ALTER TABLE. I doubt that that will work; in particular I'm pretty dubious that you can safely make CREATE INDEX and VACUUM run together. Since they'd be unlikely to be using the identical OldestXmin horizon, you'd likely end up with dangling index entries (ie, CREATE INDEX indexes a tuple that the VACUUM removes shortly afterward). I think the main issue is ANALYZE not VACUUM (at least in this thread) since it's DB load times that are in question. Right. Autovac will not issue VACUUM against the freshly restored tables anyway, since there are no deleted tuples. Can CREATE INDEX and ANALYZE be made to run concurrently? I don't see why not (except for the fact that both try to update reltuples and relpages AFAIR, so we would need to be careful about that). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On Mon, 2007-10-01 at 16:50 -0400, Alvaro Herrera wrote: Can CREATE INDEX and ANALYZE be made to run concurrently? I don't see why not (except for the fact that both try to update reltuples and relpages AFAIR, so we would need to be careful about that). This seems like the most desirable solution, everything else I've thought of is really just a kluge. We could make ANALYZE use optimistic locking, so it doesn't attempt the catalog lock until later, so ANALYZE would be two internal transactions. It should not overwrite the reltuples values from a concurrent CREATE INDEX either. We should not allow VACUUM to be concurrent with either CREATE INDEX or ANALYZE, but then thats not the problem here anyway. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Simon Riggs [EMAIL PROTECTED] writes: We should not allow VACUUM to be concurrent with either CREATE INDEX or ANALYZE, but then thats not the problem here anyway. I can't believe anyone is short-sighted enough to think that. The problem here is that autovac takes locks that block foreground sessions that want exclusive locks. We've always known this and always ignored it, but if autovac is on by default then it's going to be in people's faces a lot more than it was before, and they won't be happy. If you insist on crafting a solution that only fixes this problem for pg_restore's narrow usage, you'll be back revisiting it before beta1 has been out a month. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Tom Lane escribió: Simon Riggs [EMAIL PROTECTED] writes: We should not allow VACUUM to be concurrent with either CREATE INDEX or ANALYZE, but then thats not the problem here anyway. I can't believe anyone is short-sighted enough to think that. The problem here is that autovac takes locks that block foreground sessions that want exclusive locks. We've always known this and always ignored it, but if autovac is on by default then it's going to be in people's faces a lot more than it was before, and they won't be happy. If you insist on crafting a solution that only fixes this problem for pg_restore's narrow usage, you'll be back revisiting it before beta1 has been out a month. So you say we should make any job that needs an exclusive lock on a table to be able to cancel a running autovac job? If we did that, autovac couldn't do very much of anything. If that's not what you're saying, I'm afraid I'm not getting it. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Maybe there's lots of data loss but the records of data loss are also lost. (Lincoln Yeoh) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [COMMITTERS] pgsql: Use BIO functions to avoid passing FILE * pointers to OpenSSL
Gregory Stark [EMAIL PROTECTED] writes: Magnus Hagander [EMAIL PROTECTED] writes: Use BIO functions to avoid passing FILE * pointers to OpenSSL functions. Several buildfarm machines are failing: http://www.openssl.org/docs/crypto/ERR_set_mark.html says ERR_set_mark() and ERR_pop_to_mark() were added in OpenSSL 0.9.8. Ooops. Back to the drawing board. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Tom Lane wrote: If you insist on crafting a solution that only fixes this problem for pg_restore's narrow usage, you'll be back revisiting it before beta1 has been out a month. I don't know much about what is involved in crafting these solutions, but it seems we're close to beta and probably don't want to make drastic changes to anything. As such it seems to me that solving the problem for analyze is a nice piece of low-hanging fruit that solves an immediate problem that has been reported. I would think that reducing the locking implications of VACUUM is much more involved, no? Also, I would think that the fix for ANALYZE will be totally different than the fix for VACUUM no? Are you proposing that we solve the VACUUM locking problem before we release 8.3? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PG on NFS may be just a bad idea
Simon Riggs [EMAIL PROTECTED] writes: http://blogs.netapp.com/dave/2007/08/oracle-optimize.html Not a whole lot of technical content there, but pretty interesting nonetheless. I *think* that the issues we're seeing are largely in the NFS client-side kernel code, so bypassing that stack as Oracle is doing might eliminate the problem. Of course, there's a sizable amount of code to be written to do that ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Alvaro Herrera [EMAIL PROTECTED] writes: So you say we should make any job that needs an exclusive lock on a table to be able to cancel a running autovac job? I think we're going to be seeing complaints of this form until we do that. The only reason this particular discussion is about pg_restore is that that's the OP's first exposure to 8.3. If we did that, autovac couldn't do very much of anything. In the worst case autovac could be starved out for a long time. I don't have any immediate good idea about how to fix that, but the worst consequences could be avoided if we disable the cancellation ability when running an anti-wraparound vacuum. Further down the road (*not* 8.3), when we teach autovac about maintenance windows, it might also disregard cancels during a maintenance window. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Matthew T. O'Connor [EMAIL PROTECTED] writes: I don't know much about what is involved in crafting these solutions, but it seems we're close to beta and probably don't want to make drastic changes to anything. As such it seems to me that solving the problem for analyze is a nice piece of low-hanging fruit that solves an immediate problem that has been reported. It's not as low-hanging as all that. I'm not sure if you realized it, but there was a whole lot of handwaving going in the earlier posts... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
[ on further thought ] Matthew T. O'Connor [EMAIL PROTECTED] writes: ... solving the problem for analyze is a nice piece of low-hanging fruit that solves an immediate problem that has been reported. Actually, if you wanted a low-hanging solution to that, it would probably be to revert this 8.2 patch: http://archives.postgresql.org/pgsql-committers/2006-09/msg00284.php We did that because people were complaining of unexpected failures in manual ANALYZEs, but perhaps the cure is worse than the disease. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Tom Lane escribió: [ on further thought ] Matthew T. O'Connor [EMAIL PROTECTED] writes: ... solving the problem for analyze is a nice piece of low-hanging fruit that solves an immediate problem that has been reported. Actually, if you wanted a low-hanging solution to that, it would probably be to revert this 8.2 patch: http://archives.postgresql.org/pgsql-committers/2006-09/msg00284.php We did that because people were complaining of unexpected failures in manual ANALYZEs, but perhaps the cure is worse than the disease. How about getting ShareUpdateExclusiveLock on manual analyze and plain AccessShareLock on autovacuum-induced analyze? -- Alvaro Herrerahttp://www.advogato.org/person/alvherre I must say, I am absolutely impressed with what pgsql's implementation of VALUES allows me to do. It's kind of ridiculous how much work goes away in my code. Too bad I can't do this at work (Oracle 8/9). (Tom Allison) http://archives.postgresql.org/pgsql-general/2007-06/msg00016.php ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PG on NFS may be just a bad idea
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: http://blogs.netapp.com/dave/2007/08/oracle-optimize.html Not a whole lot of technical content there, but pretty interesting nonetheless. I *think* that the issues we're seeing are largely in the NFS client-side kernel code, so bypassing that stack as Oracle is doing might eliminate the problem. Of course, there's a sizable amount of code to be written to do that ... Yeah. Next step we will be writing our own malloc. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Alvaro Herrera [EMAIL PROTECTED] writes: How about getting ShareUpdateExclusiveLock on manual analyze and plain AccessShareLock on autovacuum-induced analyze? Wouldn't fix the original problem because those two lock types don't conflict; hence might as well keep the behavior simple. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Use BIO functions to avoid passing FILE * pointers to OpenSSL
I wrote: http://www.openssl.org/docs/crypto/ERR_set_mark.html says ERR_set_mark() and ERR_pop_to_mark() were added in OpenSSL 0.9.8. Ooops. Back to the drawing board. To get the buildfarm going again, I applied a patch that turns these calls into no-ops if the local OpenSSL hasn't got the functions. I'm not entirely sure if the net result is a regression for pre-0.9.8 OpenSSLs or not --- Magnus, any thoughts on that? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Build farm failure
dugong (icc on ia64) has been failing the contrib installcheck consistently since 6 days ago with errors like: ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory I checked a cvs diff between the two timestamps and that's precisely when the self-adjusting bgwriter changes went in which knocked around bgwriter's checkpoint logic quite a bit so it seems likely this is a real bug. On the other hand it seems weird that it only occurs in contrib's check and not the normal tests. And it also seems weird it only happens on icc ia64 and not any other architecture under the sun. I installed icc on my machine (ia32) but didn't get the same problem (we don't seem to have a recent icc ia32 build farm member). The only things I know about icc are that it likes to pad structs unnecessarily and is picky about its -f arguments... LOG: database system was shut down at 2007-10-02 02:21:38 MSD LOG: autovacuum launcher started LOG: database system is ready to accept connections NOTICE: database contrib_regression does not exist, skipping NOTICE: type gbtreekey4 is not yet defined DETAIL: Creating a shell type definition. NOTICE: argument type gbtreekey4 is only a shell NOTICE: type gbtreekey8 is not yet defined DETAIL: Creating a shell type definition. NOTICE: argument type gbtreekey8 is only a shell NOTICE: type gbtreekey16 is not yet defined DETAIL: Creating a shell type definition. NOTICE: argument type gbtreekey16 is only a shell NOTICE: type gbtreekey32 is not yet defined DETAIL: Creating a shell type definition. NOTICE: argument type gbtreekey32 is only a shell NOTICE: type gbtreekey_var is not yet defined DETAIL: Creating a shell type definition. NOTICE: argument type gbtreekey_var is only a shell ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 NOTICE: database contrib_regression does not exist, skipping ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory ERROR: checkpoint request failed HINT: Consult recent messages in the server log for details. STATEMENT: CREATE DATABASE contrib_regression TEMPLATE=template0 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of
Re: [HACKERS] Build farm failure
Gregory Stark [EMAIL PROTECTED] writes: dugong (icc on ia64) has been failing the contrib installcheck consistently since 6 days ago with errors like: ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory I checked a cvs diff between the two timestamps and that's precisely when the self-adjusting bgwriter changes went in which knocked around bgwriter's checkpoint logic quite a bit so it seems likely this is a real bug. Of course moments after I sent that it finally occurred to me to look further back in dugong's history and its previous failing periods were with exactly the same errors. So the bgwriter changes are off the hook. And given the consistency and the fact that the other icc machines didn't show the same problems it sounds like it's something about that machine, not a software problem. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Build farm failure
Gregory Stark [EMAIL PROTECTED] writes: dugong (icc on ia64) has been failing the contrib installcheck consistently since 6 days ago with errors like: ERROR: could not fsync segment 0 of relation 1663/40960/41403: No such file or directory Yeah, I already asked Sergey about this but I guess he's not had time to poke at it yet: http://archives.postgresql.org/pgsql-hackers/2007-09/msg01061.php My theory is that putting an Assert right there is somehow breaking ForwardFsyncRequest --- maybe it becomes a complete no-op, maybe it forwards a corrupt request, who knows. The only way that there'd be any visible problem from that, if you weren't actually performing pull-the-power-plug tests, would be that lack of forwarding of revoke requests could lead to the bgwriter attempting to fsync files in already-dropped databases or tablespaces. Which matches the visible symptoms exactly. This looks like nothing so much as a compiler bug, particularly given that we're seeing it with only one compiler on only one platform. We should study it more carefully, both to look for workarounds and to file a suitable bug report, but I'll be pretty surprised if it's really our bug. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Build farm failure
Gregory Stark [EMAIL PROTECTED] writes: And given the consistency and the fact that the other icc machines didn't show the same problems it sounds like it's something about that machine, not a software problem. Well, we haven't *got* any other icc-on-ia64 machines AFAICS, so it could easily be a software problem. Your remark about padding set off an alarm bell on re-reading --- what if RelFileNode is padded to 16 bytes on that architecture? Junk in the padding might break lookups in bgwriter's internal hashtable. But the Intel docs I can find do not suggest any such thing. Could someone confirm what sizeof(RelFileNode) is on ia64? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Build farm failure
On Tue, 2 Oct 2007, Gregory Stark wrote: (we don't seem to have a recent icc ia32 build farm member). Sorry about that, my buildfarm member (mongoose) is down with hardware problems, and probably will be for the forseeable future. For some reason, it suddenly decided to stop recognizing its RAID card... -- In the beginning was the word. But by the time the second word was added to it, there was trouble. For with it came syntax ... -- John Simon ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
