Re: [HACKERS] Multi CPU Queries - Feedback and/or suggestions wanted!
There is a problem trying to make Postgres do these things in Parallel. The backend code isn't thread-safe, so doing a multi-thread implementation requires quite a bit of work. Using multiple processes has its own problems: The whole way locking works equates one process with one transaction (The proc table is one entry per process). Processes would conflict on locks, deadlocking themselves, as well as many other problems. It's all a good idea, but the work is probably far more than you expect. Async I/O might be easier, if you used pThreads, which is mostly portable, but not to all platforms. (Yes, they do work on Windows) From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Baker Sent: 2008-10-20 22:25 To: Julius Stroffek Cc: pgsql-hackers@postgresql.org; Dano Vojtek Subject: Re: [HACKERS] Multi CPU Queries - Feedback and/or suggestions wanted! On Mon, Oct 20, 2008 at 12:05 PM, Julius Stroffek [EMAIL PROTECTED] wrote: Topics that seem to be of interest and most of them were already discussed at developers meeting in Ottawa are 1.) parallel sorts 2.) parallel query execution 3.) asynchronous I/O 4.) parallel COPY 5.) parallel pg_dump 6.) using threads for parallel processing [...] 2.) Different subtrees (or nodes) of the plan could be executed in parallel on different CPUs and the results of this subtrees could be requested either synchronously or asynchronously. I don't see why multiple CPUs can't work on the same node of a plan. For instance, consider a node involving a scan with an expensive condition, like UTF-8 string length. If you have four CPUs you can bring to bear, each CPU could take every fourth page, computing the expensive condition for each tuple in that page. The results of the scan can be retired asynchronously to the next node above. -jwb
Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions
On Mon, 2008-10-20 at 18:45 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote: That's been extended with an epoch counter per the docs; I don't think that's appropriate for the new functions, is it? I assumed it was, so you can subtract them easily. It can be done either way, I guess. Happy to provide what people need. I just dreamed up a few that sounded useful. I don't think you should be inventing new functions without clear use-cases in mind. Depending on what the use is, I could see either the xid or the txid definition as being *required*. The use case for the two functions was clearly stated as together allows easy arithmetic on xid difference between master and slave. In that context, xid plus epoch is appropriate. There are other use cases. We can have both, neither or just one, depending upon what people think. What would you want xid only for? Do you think that should replace the txid one? This is everybody's opportunity to say what we need. In any case, do not use the wrong return type for the definition you're implementing. err...Why would anyone do that? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Debian no longer dumps cores?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Oct 20, 2008 at 05:49:04PM -0300, Alvaro Herrera wrote: Hi, My Debian system (now running Linux 2.6.26) is no longer dumping core files, and I can't figure out why :-( Tested now with 2.6.25-2. Coredumps still work there. I submitted it as a bug: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=502948 Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFI/YmYBcgs9XrR2kYRAufMAJ9J4wdGWHSLwLPSd0mENDP5Nk/C6QCdEXOY wFk4MDQl0HfaKAXmTMUuRZs= =0J4V -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby utility and administrator functions
* pg_last_recovered_xact_xid() Will throw an ERROR if *not* executed in recovery mode. returns bigint * pg_last_completed_xact_xid() Will throw an ERROR *if* executed in recovery mode. returns bigint Should these return xid? And shouldn't these two be folded together ? It seems most usages of this xid(/lsn?) will be agnostic to the recovery mode. Or if not, it seems more convenient to have a function that returns both recovery mode and xid, no ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
Robert Haas wrote: How can you make that the default? Won't it immediately break every installation without certificates? *all* SSL installations have certificate on the server side. You cannot run without it. s/without certificates/with self-signed certificates/ which I would guess to be a common configuration Yeah, but those setups are already broken anyway; the users just appear not to know it. If you install a new web browser, would you want it to be configured by default to warn about untrusted certificates or to not bother the user about it? It's pretty much the same question here. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
Magnus Hagander wrote: Robert Haas wrote: How can you make that the default? Won't it immediately break every installation without certificates? *all* SSL installations have certificate on the server side. You cannot run without it. s/without certificates/with self-signed certificates/ which I would guess to be a common configuration Self-signed still work. In a self-signed scenario, the server certificate *is* the CA certificate. But the user needs to copy the CA to the client, which most people probably don't do nowadays. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On 21 okt 2008, at 10.04, Peter Eisentraut [EMAIL PROTECTED] wrote: Magnus Hagander wrote: Robert Haas wrote: How can you make that the default? Won't it immediately break every installation without certificates? *all* SSL installations have certificate on the server side. You cannot run without it. s/without certificates/with self-signed certificates/ which I would guess to be a common configuration Self-signed still work. In a self-signed scenario, the server certificate *is* the CA certificate. But the user needs to copy the CA to the client, which most people probably don't do nowadays. True. I'll update the docs to make this even more clear, for those who don't know ssl. I still consider that a feature and not a problem .. /magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
Then they may as well not have bothered with generating a key in the first place since an attacker can generate one of his own just as easily... Actually that's not entirely true. A non-authenticated connection still protects against passive attacks like sniffers. But active attacks are known in the wild. greg On 21 Oct 2008, at 09:04 AM, Peter Eisentraut [EMAIL PROTECTED] wrote: Magnus Hagander wrote: Robert Haas wrote: How can you make that the default? Won't it immediately break every installation without certificates? *all* SSL installations have certificate on the server side. You cannot run without it. s/without certificates/with self-signed certificates/ which I would guess to be a common configuration Self-signed still work. In a self-signed scenario, the server certificate *is* the CA certificate. But the user needs to copy the CA to the client, which most people probably don't do nowadays. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] binary representation of datatypes
Dear postgresql hackers, I would like to be able to get results from SQL commands directly in a binary format, instead of a textual one. Actually, I want to be able to get timestamps with their full precision (microsecond). googling around i found some threads on this mailing list about this: http://archives.postgresql.org/pgsql-interfaces/2007-05/msg00047.php http://archives.postgresql.org/pgsql-interfaces/2007-06/msg0.php http://archives.postgresql.org/pgsql-interfaces/2007-03/msg7.php From these threads and from postgresql source code, i figured out how to get timestamp data the way i need it: - make a PQexecParams asking for results in binary format. - convert the returned 64 bits integer from network representation to host representation (reverse the order of the bytes or do nothing, depending on the endianness of the platform) - the resulting 64 bits integer is the number of microseconds since 2000-01-01 - convert this number of microseconds as needed. (my test code currently only handles the case where timestamps are int64) This works great but i have a few questions: - Is the binary representation of data (especially timestamps) subject to change in the future? - wouldn't it be a good think if functions for dealing with this binary representation are made available to client code (for example: pq_getmsgint64 in src/backend/libpq/pqformat.c or timestamp2tm in src/interfaces/ecpg/pgtypeslib/timestamp.c). Doing so would ensure that client code does not have to reimplement things already correctly done in postgres (with all special cases, and correct error handling), and would not be broken if the internals change. Moreover it would remove from client code the burden to handle both cases of timestamp as int64 or timestamp as double. In short, what i would like (as a libpq client code writer), is a function which given an opaque binary representation of a timestamp returns me the timestamp as a number of microseconds since 2000-01-01, and a function which given a timestamp as a number of microseconds since 2000-01-01 returns me a structure similar to pg_tm, but without loss of information (with microseconds). Of course, this would be needed not only for timestamps but also for other types. If this is not possible, at least what i would like is to be sure that the code i write for converting timestamp binary representation will not be broken by future postgresql release, and is portable. best regards, -- Matthieu Imbert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On Tue, Oct 21, 2008 at 11:02:11AM +0300, Peter Eisentraut wrote: If you install a new web browser, would you want it to be configured by default to warn about untrusted certificates or to not bother the user about it? It's pretty much the same question here. We don't bother users when there is no certificate at all, so why would you if the certificate is untrusted? You seem to be making the assertion that making an encrypted connection to an untrusted server is worse than making a plaintext connection to an untrusted server, which seems bogus to me. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[HACKERS] SE-PostgreSQL wiki updates, but ...
I started to rework the SE-PostgreSQL documentation to catch up the latest implementation, because the existing PDF documents are a bit legacy to be updated. In addition, I moved them to wiki site for easier future updates. http://code.google.com/p/sepgsql/wiki/TheSepgsqlDocument However, I have a few complaints for wiki in google code. - It requires google account for all the editors, so it prevents to update documentation based on native reviewers comments. - It sends frequent commit messages for each updates, because it saves wiki pages into a part of the svn repository. Is it possiblt to host it on the wiki.postgresql.org? If possible, I want to continue it at: http://wiki.postgresql.org/wiki/SEPostgreSQL Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] binary representation of datatypes
On Tue, Oct 21, 2008 at 10:21:38AM +0200, Matthieu Imbert wrote: I would like to be able to get results from SQL commands directly in a binary format, instead of a textual one. Actually, I want to be able to get timestamps with their full precision (microsecond). Are you sure you cannot get those in textual mode? If so I wonder why I got some numbers in a quick test: ... [NO_PID]: ecpg_execute on line 37: query: select * from date_test where d = $1 ; with 1 parameter(s) on connection regress1 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_execute on line 37: using PQexecParams [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: free_params on line 37: parameter 1 = 1966-01-17 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_execute on line 37: correctly got 1 tuples with 2 fields [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_get_data on line 37: RESULT: 1966-01-17 offset: -1; array: yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_get_data on line 37: RESULT: 2000-07-12 17:34:29.140787 offset: -1; array: yes [NO_PID]: sqlca: code: 0, state: 0 ... What do I miss here? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index use during Hot Standby
I guess we'd use the same technique for GIN. ginInsertValue() ?? Hmm, you release the lock at line 412, ginbtree.c before you get the parent lock at line 428. That seems different to the LY interactions. Am I looking in the wrong place? at line 412 new page (right page) is unlocked, old page (left one) is unlocked later - at line 448, after parent page is locked. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] automatic parser generation for ecpg
(Mike, it lacks a copyright notice, I take it BSD is okay). Thats fine with me.. Also - for completeness (for the list) - I think the plan is to convert the awk to perl (via a2p + some tweaking) if awk is not already used as part of the build process (to avoid adding another prerequisite..) -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PostgreSQL wiki updates, but ...
2008/10/21 KaiGai Kohei [EMAIL PROTECTED]: Is it possiblt to host it on the wiki.postgresql.org? If possible, I want to continue it at: http://wiki.postgresql.org/wiki/SEPostgreSQL Though I don't know if this is official way, I found that you can register a new account on http://www.postgresql.org/community/signup and with that account the wiki is editable. I guess it might be better if there is more public announcement to help developers' activities. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index use during Hot Standby
On Tue, 2008-10-21 at 14:11 +0400, Teodor Sigaev wrote: I guess we'd use the same technique for GIN. ginInsertValue() ?? Hmm, you release the lock at line 412, ginbtree.c before you get the parent lock at line 428. That seems different to the LY interactions. Am I looking in the wrong place? at line 412 new page (right page) is unlocked, old page (left one) is unlocked later - at line 448, after parent page is locked. Thanks for checking. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
Martijn van Oosterhout [EMAIL PROTECTED] writes: You seem to be making the assertion that making an encrypted connection to an untrusted server is worse than making a plaintext connection to an untrusted server, which seems bogus to me. Hm, is it? If you use good old traditional telnet you know you're typing on an insecure connection. If you use ssh you expect it to be secure and indeed ssh throws up big errors if it fails to get a secure connection -- it doesn't silently fall back to an insecure connection. Actually even the example given before of the browsers follows this model. If you visit an insecure web site you get your web page. But if you visit a secure web site with a bogus certificate you get a big warning. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PostgreSQL wiki updates, but ...
Hitoshi Harada wrote: 2008/10/21 KaiGai Kohei [EMAIL PROTECTED]: Is it possiblt to host it on the wiki.postgresql.org? If possible, I want to continue it at: http://wiki.postgresql.org/wiki/SEPostgreSQL Though I don't know if this is official way, I found that you can register a new account on http://www.postgresql.org/community/signup and with that account the wiki is editable. I guess it might be better if there is more public announcement to help developers' activities. the wiki is free for everybody to use (it's a wiki after all) - it just shares the account database with the rest of the website so people don't have to keep multiple accounts around. the main page even has: In order to edit or create documents on the site, you will need a PostgreSQL community account Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On Tue, Oct 21, 2008 at 11:55:32AM +0100, Gregory Stark wrote: Martijn van Oosterhout [EMAIL PROTECTED] writes: You seem to be making the assertion that making an encrypted connection to an untrusted server is worse than making a plaintext connection to an untrusted server, which seems bogus to me. Hm, is it? If you use good old traditional telnet you know you're typing on an insecure connection. If you use ssh you expect it to be secure and indeed ssh throws up big errors if it fails to get a secure connection -- it doesn't silently fall back to an insecure connection. SSH is a good example, it only works with self-signed certificates, and relies on the client to check it. Libpq provides a mechanism for the client to verify the server's certificate, and that is safe even if it is self-signed. If the client knows the certificate the server is supposed to present, then you can't have a man-in-the-middle attack, right? Whether it's self-signed or not is irrelevent. Preventing casual snooping without preventing MitM is a rational choice for system administrators. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] SSL cleanups/hostname verification
On 21 okt 2008, at 13.12, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Tue, Oct 21, 2008 at 11:55:32AM +0100, Gregory Stark wrote: Martijn van Oosterhout [EMAIL PROTECTED] writes: You seem to be making the assertion that making an encrypted connection to an untrusted server is worse than making a plaintext connection to an untrusted server, which seems bogus to me. Hm, is it? If you use good old traditional telnet you know you're typing on an insecure connection. If you use ssh you expect it to be secure and indeed ssh throws up big errors if it fails to get a secure connection -- it doesn't silently fall back to an insecure connection. SSH is a good example, it only works with self-signed certificates, and relies on the client to check it. Libpq provides a mechanism for the client to verify the server's certificate, and that is safe even if it is self-signed. Are you referring to the method we have now? If so, it has two problems: it's not enforceable from the app, and it's off by default. Other than that, it works. If the client knows the certificate the server is supposed to present, then you can't have a man-in-the-middle attack, right? Whether it's self-signed or not is irrelevent. Yes. The importance being that it must know which, and not just blindly accept anything. Preventing casual snooping without preventing MitM is a rational choice for system administrators. Yes, but it should not be the default. It still allows you to do this... /mha -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] binary representation of datatypes
Michael Meskes wrote: On Tue, Oct 21, 2008 at 10:21:38AM +0200, Matthieu Imbert wrote: I would like to be able to get results from SQL commands directly in a binary format, instead of a textual one. Actually, I want to be able to get timestamps with their full precision (microsecond). Are you sure you cannot get those in textual mode? If so I wonder why I got some numbers in a quick test: ... [NO_PID]: ecpg_execute on line 37: query: select * from date_test where d = $1 ; with 1 parameter(s) on connection regress1 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_execute on line 37: using PQexecParams [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: free_params on line 37: parameter 1 = 1966-01-17 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_execute on line 37: correctly got 1 tuples with 2 fields [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_get_data on line 37: RESULT: 1966-01-17 offset: -1; array: yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ecpg_get_data on line 37: RESULT: 2000-07-12 17:34:29.140787 offset: -1; array: yes [NO_PID]: sqlca: code: 0, state: 0 ... What do I miss here? Michael Yes microseconds are available in textual mode but i do want to use binary mode. Let me explain why: - my data will be time series. So typical requests will return lots of timestamped data (mainly floats or int). - after extraction i need to have all timestamps stored in format convenient for calculations. I can accommodate different formats (for example: number of microseconds since 2000-01-01, or a structure similar to pg_tm (but with microsecond precision), or a time-format similar to one defined in rfc1305), but definitely storing timestamps as text is a no go for me. so i have two choices: scenario 1 - parse the textual representation of all results of requests to the database and convert textual timestamps to a binary format that i choose among those ones (number of microseconds since 2000-01-01, or a structure similar to pg_tm (but with microsecond precision), or a time-format similar to one defined in rfc1305, or something else) or scenario 2 - directly use pgsql binary timestamp format. I think the latter is far more efficient. I'm new to postgresql, but from what i understand, here are the conversions involved in both scenarios (hopping that my ascii art won't be garbled by your mail clients ;-) : scenario 1: .-. .--. .-. .--. .--. .--. .-. |timestamp| |pgsql | |timestamp| |pgsql | |timestamp | |my | |my | |storage |-|internal |-|storage |-|network |-|as |-|timestamp |-|timestamp| |in | |to| |in | |to| |textual | |conversion| |format | |database | |network | |network | |textual | |representation| |routines | | | |backend | |conversion| | | |conversion| | | | | | | | | |function | | | |function | | | | | | | '-' '--' '-' '--' '--' '--' '-' scenario 2: .-. .--. .-. .--. .-. |timestamp| |pgsql | |timestamp| |pgsql | |timestamp| |storage |-|internal |-|storage |-|network |-|official | |in | |to| |in | |to| |format | |database | |network | |network | |offical | | | |backend | |conversion| | | |conversion| | | | | |function | | | |function | | | '-' '--' '-' '--' '-' if i'm correct, it seems obvious that the second scenario is more efficient (and less ugly). In scenario 2, when talking about timestamp 'official' format, i mean timestamp expressed as number of microseconds since 2000-01-01. But of course, it only deserves this name 'official' if it is guaranteed to stay the same across postgresql versions and platforms -- Matthieu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TSEARCH2 Thesaurus limitations
Hi, I'm new to Postgres and would appreciate some help in understanding what the limitations of TSEARCH2 and the Thesauri operation. I'm trying to use the thesaurus as a geo-tagger/coder. The first part of the problem is to create placename list with additional information such as state, county and country names. But, the returned values are off. There's less of a problem when the thesaurus is small under 100 rows but I'm trying to upload 7 million rows. I have not seen the latest TSEARCH2 code release so I don't have a great deal of understanding of the inner workings. Is there specific code that I can hack which will remove a fix limitation such as the number of tokens before the indexer quits or is the index type insufficient for the scale of data. Thanks, Ted -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] corrupted pg_proc?
I need to repair the old version of Postgresql. pg_dump does not work, I tried few other things but could not come up with any workable scenario. Any help will be greatly appreciated! Thanks, Michael. cdb=# vacuum; WARNING: Rel pg_proc: TID 31/20: OID IS INVALID. TUPGONE 0. VACUUM cdb=# select version(); version -- PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3 (1 row)
Re: [HACKERS] SE-PostgreSQL wiki updates, but ...
Stefan Kaltenbrunner wrote: Hitoshi Harada wrote: 2008/10/21 KaiGai Kohei [EMAIL PROTECTED]: Is it possiblt to host it on the wiki.postgresql.org? If possible, I want to continue it at: http://wiki.postgresql.org/wiki/SEPostgreSQL Though I don't know if this is official way, I found that you can register a new account on http://www.postgresql.org/community/signup and with that account the wiki is editable. Yes, I didn't know the official way. Thanks for your information. I guess it might be better if there is more public announcement to help developers' activities. the wiki is free for everybody to use (it's a wiki after all) - it just shares the account database with the rest of the website so people don't have to keep multiple accounts around. the main page even has: In order to edit or create documents on the site, you will need a PostgreSQL community account Stefan -- KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
Martijn van Oosterhout wrote: SSH is a good example, it only works with self-signed certificates, and relies on the client to check it. Libpq provides a mechanism for the client to verify the server's certificate, and that is safe even if it is self-signed. If the client knows the certificate the server is supposed to present, then you can't have a man-in-the-middle attack, right? Whether it's self-signed or not is irrelevent. That appears to be correct, but that was not the original issue under discussion. Both a web browser and an SSH client will, when faced with an untrusted certificate, pop a question to the user. The user then verifies the certificate some other way (in theory), answers/clicks yes, and then web browser and SSH client store the certificate locally marked as trusted, so this question goes away the next time. An SSL-enabled libpq program will, when faced with an untrusted certificate, go ahead anyway, without notification. (Roughly speaking. If I understand this right, there are other scenarios depending on whether the client user has set up the requires files in ~/.postgresql. All this just leads users to do the wrong thing by neglect, ignorance, or error.) The change Magnus proposes is that SSL-enabled libpq programs will in the future refuse to connect without a trusted certificate. Being a library, we cannot really go ask the user, as web browser and SSH client do, but I could imagine that we could make psql do that and store the trusted certificate automatically in a local place. Then we would be close to the usual operating mode for SSH and web browsers, and then chances are better that users can understand this setup and use it securely and easily. Preventing casual snooping without preventing MitM is a rational choice for system administrators. I am not an expert in these things, but it seems to me that someone who can casually snoop can also casually insert DHCP or DNS packages and redirect traffic. There is probably a small niche where just encryption without server authentication prevents information leaks, but it is not clear to me where this niche is or how it can be defined, and I personally wouldn't encourage this sort of setup. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions
Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2008-10-20 at 18:45 -0400, Tom Lane wrote: In any case, do not use the wrong return type for the definition you're implementing. err...Why would anyone do that? That's what I wanted to know ;-). If these functions are really going to return txid, then they should be named to reflect that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TSEARCH2 Thesaurus limitations
Hi, I'm new to Postgres and would appreciate some help in understanding what the limitations of TSEARCH2 and the Thesauri operation. I'm trying to use the thesaurus as a geo-tagger/coder. The first part of the problem is to create placename list with additional information such as state, county and country names. But, the returned values are off. There's less of a problem when the thesaurus is small under 100 rows but I'm trying to upload 7 million rows. I have not seen the latest TSEARCH2 code release so I don't have a great deal of understanding of the inner workings. Is there specific code that I can hack which will remove a fix limitation such as the number of tokens before the indexer quits or is the index type insufficient for the scale of data. Thanks, Ted _ Want to read Hotmail messages in Outlook? The Wordsmiths show you how. http://windowslive.com/connect/post/wedowindowslive.spaces.live.com-Blog-cns!20EE04FBC541789!167.entry?ocid=TXT_TAGLM_WL_hotmail_092008
Re: [HACKERS] automatic parser generation for ecpg
Mike Aubury [EMAIL PROTECTED] writes: Also - for completeness (for the list) - I think the plan is to convert the awk to perl (via a2p + some tweaking) if awk is not already used as part of the build process (to avoid adding another prerequisite..) Hmm. I believe the current state of play on that is: * awk is required to build from source on non-Windows platforms (cf genbki.sh, Gen_fmgrtab.sh, and various random uses in the Makefiles) * perl is required to build from source on Windows (all the build scripts) * perl is required to build from a CVS pull on non-Windows too, but we avoid requiring this for builds from a distribution tarball (by including the relevant derived files in the tarball) * we get around the awk requirement on Windows by maintaining parallel code that does the same things in perl :-( So it's all pretty messy and neither choice is exactly desirable. I think maintaining parallel versions of an ecpg parser generator would be no fun at all, though, so the perl choice seems more or less forced. We could either preserve the current state of play by shipping the derived bison file in tarballs, or bite the bullet and say perl is required to build from source in all cases (in which case I'd be inclined to try to get rid of Gen_fmgrtab.sh etc). As against that ... does a2p produce code that is readable/maintainable? If the code wasn't perl to start with I'd be a little worried about ending up with ugly hard-to-read code. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On 21 okt 2008, at 13.41, Peter Eisentraut [EMAIL PROTECTED] wrote: Martijn van Oosterhout wrote: SSH is a good example, it only works with self-signed certificates, and relies on the client to check it. Libpq provides a mechanism for the client to verify the server's certificate, and that is safe even if it is self-signed. If the client knows the certificate the server is supposed to present, then you can't have a man-in-the-middle attack, right? Whether it's self-signed or not is irrelevent. That appears to be correct, but that was not the original issue under discussion. Both a web browser and an SSH client will, when faced with an untrusted certificate, pop a question to the user. The user then verifies the certificate some other way (in theory), answers/clicks yes, and then web browser and SSH client store the certificate locally marked as trusted, so this question goes away the next time. An SSL-enabled libpq program will, when faced with an untrusted certificate, go ahead anyway, without notification. (Roughly speaking. If I understand this right, there are other scenarios depending on whether the client user has set up the requires files in ~/.postgresql. All this just leads users to do the wrong thing by neglect, ignorance, or error.) The change Magnus proposes is that SSL-enabled libpq programs will in the future refuse to connect without a trusted certificate. Being a library, we cannot really go ask the user, as web browser and SSH client do, but I could imagine that we could make psql do that and store the trusted certificate automatically in a local place. Then we would be close to the usual operating mode for SSH and web browsers, and then chances are better that users can understand this setup and use it securely and easily. Preventing casual snooping without preventing MitM is a rational choice for system administrators. I am not an expert in these things, but it seems to me that someone who can casually snoop can also casually insert DHCP or DNS packages and redirect traffic. There is probably a small niche where just encryption without server authentication prevents information leaks, but it is not clear to me where this niche is or how it can be defined, and I personally wouldn't encourage this sort of setup. Yes, see the discussion with Dan Kaminsky on list a while back, which is what prompted me to finally getting around to fixing this long-time todo... /mha -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
Martijn van Oosterhout [EMAIL PROTECTED] writes: SSH is a good example, it only works with self-signed certificates, and relies on the client to check it. Libpq provides a mechanism for the client to verify the server's certificate, and that is safe even if it is self-signed. Sort of. SSH requires you to install the certificate of the server locally before connecting. If you don't it pops up a big warning and asks if you want to install it. On subsequent connections it looks up the key for the name of the host you're trying to connect to and insists it match. If it doesn't it pops up a *huge* error and refuses to connect. Preventing casual snooping without preventing MitM is a rational choice for system administrators. I think the word you're looking for is naive :) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
Gregory Stark [EMAIL PROTECTED] writes: Sort of. SSH requires you to install the certificate of the server locally before connecting. If you don't it pops up a big warning and asks if you want to install it. On subsequent connections it looks up the key for the name of the host you're trying to connect to and insists it match. If it doesn't it pops up a *huge* error and refuses to connect. Um, IIRC what it's checking there is the server's key signature, which has nada to do with certificates. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements
On Oct 17, 2008, at 4:30 AM, Vladimir Sitnikov wrote: Decibel! [EMAIL PROTECTED] wrote: I had tried to use a normal table for store stats information, but several acrobatic hacks are needed to keep performance. I guess it is not really required to synchronize the stats into some physical table immediately. I would suggest keeping all the data in memory, and having a job that periodically dumps snapshots into physical tables (with WAL etc). In that case one would be able to compute database workload as a difference between two given snapshots. From my point of view, it does not look like a performance killer to have snapshots every 15 minutes. It does not look too bad to get the statistics of last 15 minutes lost in case of database crash either. Yeah, that's exactly what I had in mind. I agree that trying to write to a real table for every counter update would be insane. My thought was to treat the shared memory area as a buffer of stats counters. When you go to increment a counter, if it's not in the buffer then you'd read it out of the table, stick it in the buffer and increment it. As items age, they'd get pushed out of the buffer. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] automatic parser generation for ecpg
Perl code thats readable and maintainable ;-) In reality - it doesn't look too disimilar from the awk original. I didn't appreciate that we'd probably need to keep 2 versions (one for unix and one for windows). In that case - I'd argue that we only need to maintain one and regenerate the other when required. Provided they both work the same, I'd say it doesn't matter what the perl one looked like, because thats not the one that'd be maintained :-) Personally - I'd be tempted to keep this as a background process for the ecpg maintiner anyway rather than a normal end user. Probably using something like a 'syncparser' make target and keep the generation separate from the normal build process. That way - awk/perl (you could then pick just one) would only be a requirement if you want to regenerate the grammer via the 'syncparser' target. This does have the benefit that the ecpg maintainer can then control when the sync'ing is done and that its less likely to inadvertantly break the ecpg branch of source tree. At the end of the day - this is something Michael has just been doing manually already and we're trying to help automate the process.. (ducks for cover) As against that ... does a2p produce code that is readable/maintainable? If the code wasn't perl to start with I'd be a little worried about ending up with ugly hard-to-read code. -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_statements in core
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Now I'm working on storing statistics into disks on server shutdown. If it is impossible unless the module is in core, I would change my policy... I'm really not happy with a proposal to put such a feature in core. Once it's in core we'll have pretty strong backwards-compatibility constraints to meet, and I don't think you are anywhere near being able to demonstrate that you have a solid API that won't require changes. It needs to be a contrib or pgfoundry package for awhile, to shake out feature issues in a context where users will understand the API is subject to change. (As an example of why I'm skittish about this: just a few days ago someone was complaining about the plans to get rid of pg_autovacuum, despite the fact that it's been clearly documented as subject to change or removal since day one. People expect stability in core features.) It seems to me that all you're really missing is a shutdown hook someplace, which would be a reasonable core addition. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On 21 okt 2008, at 13.41, Peter Eisentraut [EMAIL PROTECTED] wrote: Martijn van Oosterhout wrote: SSH is a good example, it only works with self-signed certificates, and relies on the client to check it. Libpq provides a mechanism for the client to verify the server's certificate, and that is safe even if it is self-signed. If the client knows the certificate the server is supposed to present, then you can't have a man-in-the-middle attack, right? Whether it's self-signed or not is irrelevent. That appears to be correct, but that was not the original issue under discussion. Both a web browser and an SSH client will, when faced with an untrusted certificate, pop a question to the user. The user then verifies the certificate some other way (in theory), answers/clicks yes, and then web browser and SSH client store the certificate locally marked as trusted, so this question goes away Preventing casual snooping without preventing MitM is a rational choice for system administrators. I am not an expert in these things, but it seems to me that someone who can casually snoop can also casually insert DHCP or DNS packages and redirect traffic. There is probably a small niche where just encryption without server authentication prevents information leaks, but it is not clear to me where this niche is or how it can be defined, and I personally wouldn't encourage this sort of setup. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] automatic parser generation for ecpg
Mike Aubury [EMAIL PROTECTED] writes: In reality - it doesn't look too disimilar from the awk original. I didn't appreciate that we'd probably need to keep 2 versions (one for unix and one for windows). In that case - I'd argue that we only need to maintain one and regenerate the other when required. Provided they both work the same, I'd say it doesn't matter what the perl one looked like, because thats not the one that'd be maintained :-) That'd only be acceptable if the code conversion were fully automatic --- given your reference to tweaks I wasn't sure if that could be the case. Personally - I'd be tempted to keep this as a background process for the ecpg maintiner anyway rather than a normal end user. While we could approach it that way, it doesn't really meet all the goals I was hoping for. The current process is unsatisfactory for at least two reasons above and beyond Michael has to do a lot of gruntwork: * People hacking on the core grammar might break ecpg without realizing it. They need short-term feedback from the standard build process, or at the worst from the standard buildfarm checks. * For the last little while, changing the core keyword set breaks ecpg completely, which means we have the worst of all possible worlds: core modifiers have to hack ecpg to get it to compile, and then Michael has to do more work to get it to actually work right. I've been willing to put up with the second problem because I expected the ecpg grammar build process to become fully automatic soon. If that doesn't happen then I'm going to be lobbying to revert the change that made ecpg depend directly on the core keyword set. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
On 20 okt 2008, at 16.51, Andrew Dunstan [EMAIL PROTECTED] wrote: Magnus Hagander wrote: Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: OK. Where would be a good place to put the code? Maybe a new file src/backend/utils/adt/trigger_utils.c ? I thought the plan was to make it a contrib module. Well, previous discussion did mention catalog entries, which would suggest otherwise, but I can do it as a contrib module if that's the consensus. What would be the actual reason to put it in contrib and not core? Are there any dangers by having it there? Or is it just a hack and not a real solution? No, it's not just a hack. It's very close to what we'd probably do if we built the facility right into the language, although it does involve the overhead of calling the trigger. However, it performs reasonably well - not surprising since the guts of it is just a memcmp() call. In that case, why not put the trigger in core so people can use it easily? /magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On Tue, Oct 21, 2008 at 08:47:35AM -0400, Tom Lane wrote: Um, IIRC what it's checking there is the server's key signature, which has nada to do with certificates. That depends on whether you used an X.509 certificate to authenticate the original signature. Just about nobody does, but AIUI, there's a way to do so. Anyway, in the strict sense you're right, but the comparison is wrong anyway. SSH doesn't pretend to be authenticating over SSL. It's authenticating using the SSH protocol, which has its own RFCs describing it. If I understand the description of the current behaviour, I have to agree with those who say the current behaviour is almost worse than nothing. In the presence of DNS forgery (and I'll bet a pretty good lunch most people aren't using DNSSEC), it's not hard to send a client to the wrong server. If the ssl-using client will blithely proceed if it can't authenticate the server, it's pretty hard to see in what sense this is a conforming use of anything I know as SSL. SSL is supposed to provide both encryption and authentication (the self-signed certificate nonsense is actually breakage that everyone in the protocol community wails about whenever given the opportunity, because of the results in user behaviour. It was a compromise that people made back in the period when Verisign had a lock on the market and would charge you an arm and a leg for a cert). A [Actually, to be pedantic, it might be better to call the authentication method TLS, so as not to conflate it with the Netscape-defined SSL. But this is maybe straying into a different topic.] -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
On Tue, Oct 21, 2008 at 03:34:04PM +0200, Magnus Hagander wrote: On 20 okt 2008, at 16.51, Andrew Dunstan [EMAIL PROTECTED] wrote: Magnus Hagander wrote: Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: OK. Where would be a good place to put the code? Maybe a new file src/backend/utils/adt/trigger_utils.c ? I thought the plan was to make it a contrib module. Well, previous discussion did mention catalog entries, which would suggest otherwise, but I can do it as a contrib module if that's the consensus. What would be the actual reason to put it in contrib and not core? Are there any dangers by having it there? Or is it just a hack and not a real solution? No, it's not just a hack. It's very close to what we'd probably do if we built the facility right into the language, although it does involve the overhead of calling the trigger. However, it performs reasonably well - not surprising since the guts of it is just a memcmp() call. In that case, why not put the trigger in core so people can use it easily? +1 :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
On Tue, Oct 21, 2008 at 9:34 AM, Magnus Hagander [EMAIL PROTECTED] wrote: On 20 okt 2008, at 16.51, Andrew Dunstan [EMAIL PROTECTED] wrote: No, it's not just a hack. It's very close to what we'd probably do if we built the facility right into the language, although it does involve the overhead of calling the trigger. However, it performs reasonably well - not surprising since the guts of it is just a memcmp() call. In that case, why not put the trigger in core so people can use it easily? +1 This is hard to get right and a common source of errors. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
Magnus Hagander [EMAIL PROTECTED] writes: In that case, why not put the trigger in core so people can use it easily? One advantage of making it a contrib module is that discussing how/when to use it would fit more easily into the structure of the documentation. There is no place in our docs that a standard trigger would fit without seeming like a wart; but a contrib module can document itself pretty much however it wants. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] automatic parser generation for ecpg
I share Tom's thoughts completely. My personal goal is definitely to make ecpg parser generation a fully automated task. The only manual work I see in the future is adding some special ecpg handling. I fully expect this script to generate a working parser for every single change in gram.y. However, if some new rule needs a different aka non-default handling in ecpg that will remain manual, but the automatic process should nevertheless create a parser with default handling for this new rule, thus not breaking anything but the new feature in ecpg, which of course cannot get broken because it is new. Is this understandable? :-) Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] binary representation of datatypes
On Tue, Oct 21, 2008 at 4:21 AM, Matthieu Imbert [EMAIL PROTECTED] wrote: Dear postgresql hackers, I would like to be able to get results from SQL commands directly in a binary format, instead of a textual one. Actually, I want to be able to get timestamps with their full precision (microsecond). googling around i found some threads on this mailing list about this: http://archives.postgresql.org/pgsql-interfaces/2007-05/msg00047.php http://archives.postgresql.org/pgsql-interfaces/2007-06/msg0.php http://archives.postgresql.org/pgsql-interfaces/2007-03/msg7.php From these threads and from postgresql source code, i figured out how to get timestamp data the way i need it: - make a PQexecParams asking for results in binary format. - convert the returned 64 bits integer from network representation to host representation (reverse the order of the bytes or do nothing, depending on the endianness of the platform) - the resulting 64 bits integer is the number of microseconds since 2000-01-01 - convert this number of microseconds as needed. (my test code currently only handles the case where timestamps are int64) This works great but i have a few questions: - Is the binary representation of data (especially timestamps) subject to change in the future? - wouldn't it be a good think if functions for dealing with this binary representation are made available to client code (for example: pq_getmsgint64 in src/backend/libpq/pqformat.c or timestamp2tm in src/interfaces/ecpg/pgtypeslib/timestamp.c). Doing so would ensure that client code does not have to reimplement things already correctly done in postgres (with all special cases, and correct error handling), and would not be broken if the internals change. Moreover it would remove from client code the burden to handle both cases of timestamp as int64 or timestamp as double. In short, what i would like (as a libpq client code writer), is a function which given an opaque binary representation of a timestamp returns me the timestamp as a number of microseconds since 2000-01-01, and a function which given a timestamp as a number of microseconds since 2000-01-01 returns me a structure similar to pg_tm, but without loss of information (with microseconds). Of course, this would be needed not only for timestamps but also for other types. If this is not possible, at least what i would like is to be sure that the code i write for converting timestamp binary representation will not be broken by future postgresql release, and is portable. you really want to look at libpqtypes. It does exactly what you want, as well as provides easy to follow binary handlers for every basic type. http://pgfoundry.org/projects/libpqtypes/ http://libpqtypes.esilo.com/ merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby: Caches and Locks
Next stage is handling locks and proc interactions. While this has been on Wiki for a while, I have made a few more improvements, so please read again now. Summary of Proposed Changes --- * New RMgr using rmid==8 = RM_RELATION_ID (which fills last gap) * Write new WAL message, XLOG_RELATION_INVAL immediately prior commit * LockAquire() write new WAL message, XLOG_RELATION_LOCK * Startup process queues sinval message when it sees XLOG_RELATION_INVAL * Startup process takes and holds AccessExclusiveLock when it processes XLOG_RELATION_LOCK message * At xact_commit_redo we fire sinval messages and then release locks for that transaction Explanations All read-only transactions need to maintain various caches: relcache, catcache and smgr cache. Theses caches will be maintained on each backend normally, re-reading catalog tables when invalidation messages are received. Invalidation messages will be sent by the Startup process. The Startup process will not maintain its own copy of the caches, so will never receive messages, only send them. XLOG_RELATION_INVAL messages will be sent immediately prior to commit (only) using new function LogCacheInval(), and also during EndNonTransactionalInvalidation(). We do nothing at subtransaction commit. WAL record will contain a simple contiguous array of SharedInvalidationMessage(s) that need to be sent. If nothing to do, no WAL record. We can't send sinval messages after commit in case we crash and fail to write WAL for them. We can't augment the commit/abort messages because we must cater for non-transactional invalidations also, plus commit xlrecs are already complex enough. So we log invalidations prior to commit, queue them and then trigger the send at commit (if it happens). We need do nothing in the abort case because we are not maintaining our own caches in the Startup process. In the nontransactional invalidation case we would process WAL records immediately. Startup process will need to initialise using SharedInvalBackendInit() which is not normally executed by auxiliary processes. Startup would call this from AuxiliaryProcessMain() just before we call StartupXLOG(). We will need an extra slot in state arrays to allow for Startup process. Startup process needs to reset its sinval nextMsgNum, so everybody thinks it has read messages. It will be unprepared to handle catchup requests if they were received for some reason, since only the Startup process is sending messages at this point. Startup process will continue to use XLogReadBuffer(), minimising the changes required in current ResourceManager's _redo functions - there are still some, see later. It also means that read-only backends will use ReadBuffer() calls normally, so again, no changes required throughout the normal executor code. Locks will be taken by the Startup process when it receives a new WAL message. XLOG_RELATION_LOCK messages will be sent each time a backend *successfully* acquires an AccessExclusiveLock (only). We send it immediately after the lock acquisition, which means we will often be sending lock requests with no TransactionId assigned, so the slotId is essential in tying up the lock request with the commit that later releases it, since the commit does not include the vxid. In recovery, transactions will not be permitted to take any lock higher than AccessShareLock on an object, nor assign a TransactionId. This should also prevent us from writing WAL, but we protect against that specifically as well, just in case. (Maybe we can relax that to Assert sometime later). We can dirty data blocks but only to set hint bits. (That's another reason to differentiate between those two cases anyway). Note that in recovery, we will always be allowed to set hint bits - no need to check for asynchronous commits. All other actions which cause dirty data blocks should not be allowed, though this will be just an Assert. Specifically, HOT pruning will not be allowed in recovery mode. Since read-only backends will only be allowed to take AccessShareLocks there will be no lock conflicts apart from with AccessExclusiveLocks. (If we allowed higher levels of lock we would then need to maintain Multitrans to examine lock details, which we would also rather avoid). So Startup process will not take, hold or release relation locks for any purpose, *apart* from when AccessExclusiveLocks (AELs) are required. So we will send WAL messages *only* for AELs. The Startup process will emulate locking behaviour for transactions that require AELs. AELs will be held by first inserting a dummy TransactionLock entry into the lock table with the TransactionId of the transaction that requests the lock. Then the lock entry will be made. Locks will be released when processing a transaction commit, abort or shutdown checkpoint message, and the lock table entry for the transaction will be removed. Any AEL request that conflicts with an existing lock will cause some action: if it
Re: [HACKERS] corrupted pg_proc?
Hello try to http://svana.org/kleptog/pgsql/pgfsck.html regards Pavel Stehule 2008/10/21 BRUSSER Michael [EMAIL PROTECTED]: I need to repair the old version of Postgresql. pg_dump does not work, I tried few other things but could not come up with any workable scenario. Any help will be greatly appreciated! Thanks, Michael. cdb=# vacuum; WARNING: Rel pg_proc: TID 31/20: OID IS INVALID. TUPGONE 0. VACUUM cdb=# select version(); version -- PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3 (1 row) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: In that case, why not put the trigger in core so people can use it easily? One advantage of making it a contrib module is that discussing how/when to use it would fit more easily into the structure of the documentation. There is no place in our docs that a standard trigger would fit without seeming like a wart; but a contrib module can document itself pretty much however it wants. I was thinking a new section on 'trigger functions' of the functions and operators chapter, linked from the 'create trigger' page. That doesn't seem like too much of a wart. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby utility and administrator functions
On Tue, 2008-10-21 at 09:44 +0200, Zeugswetter Andreas OSB sIT wrote: * pg_last_recovered_xact_xid() Will throw an ERROR if *not* executed in recovery mode. returns bigint * pg_last_completed_xact_xid() Will throw an ERROR *if* executed in recovery mode. returns bigint Should these return xid? And shouldn't these two be folded together ? It seems most usages of this xid(/lsn?) will be agnostic to the recovery mode. Or if not, it seems more convenient to have a function that returns both recovery mode and xid, no ? You are right that it would be better to have a single function. Functions that return multiple values are a pain to use and develop, plus we can always run the other function if we are in doubt. txid_last_completed() returns bigint (txid) seems better. I am more than happy to add an id version as well, if anybody sees the need for that. Just say. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] automatic parser generation for ecpg
Michael Meskes [EMAIL PROTECTED] writes: ... The only manual work I see in the future is adding some special ecpg handling. I fully expect this script to generate a working parser for every single change in gram.y. However, if some new rule needs a different aka non-default handling in ecpg that will remain manual, but the automatic process should nevertheless create a parser with default handling for this new rule, thus not breaking anything but the new feature in ecpg, which of course cannot get broken because it is new. Hmm --- I hadn't really thought much about the need for the generation script to make special transformations of some rules, but obviously that is going to be needed in the places where you have to sew the SQL and ecpg syntaxes together. Perhaps there is a good argument for going to perl just to be sure that we don't get backed into a corner on what can be done in such cases. awk is a great tool for certain kinds of tasks, but it's pretty limited. For instance, AFAIK you'd be out of luck if you needed to make two passes over the input. So my vote at this point would be to convert the script to perl. Also, never mind the idea about starting to require perl for all build scenarios. We'll still ship preproc.y in tarballs because we will still ship preproc.c in tarballs --- I don't think anyone was lobbying to start requiring bison to be present for builds from tarballs. So if the script is perl we'll have exactly the same build dependency scenarios as now. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] binary representation of datatypes
On Tue, Oct 21, 2008 at 01:37:44PM +0200, Matthieu Imbert wrote: Yes microseconds are available in textual mode but i do want to use binary mode. Let me explain why: ... if i'm correct, it seems obvious that the second scenario is more efficient (and less ugly). I wouldn't bet on scenario 2 being more efficient. For this you not only need less conversions but also cheaper conversion. Now I haven't looked at this in detail, but you might spend a lot of time doing stuff that has only a marginal effect. In scenario 2, when talking about timestamp 'official' format, i mean timestamp expressed as number of microseconds since 2000-01-01. But of course, it only deserves this name 'official' if it is guaranteed to stay the same across postgresql versions and platforms You shouldn't rely on this. Again I'd recommend using text. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Withdraw PL/Proxy from commitfest
On Fri, 2008-09-05 at 15:39 +0300, Marko Kreen wrote: In the previous discussion there was mentioned that Postgres should move to the SQL-MED direction in remote connection handling. SQL-MED specifies that connections should have names and referenced everywhere using names. Where did you find that in SQL-MED In my brief reading of SQL-MED spec I could only find info on defining FOREIGN SERVER and FOREIGN-DATA WRAPPER and nowhere in these could one define connection parameters like username and password. FSConnection handle uses these two, but again, I saw no place to put credentials for making the connection in; Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bitmap Indexes: request for feedback
Hi everybody, me and Gabriele Bartolini have been working on Bitmap Indexes (BMI) in the last weeks, with advice and guidance from Simon Riggs. We feel that we are about to approach the point where it is appropriate to ask for feedback from this list. Thank you, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support [EMAIL PROTECTED] | www.2ndquadrant.it ---8--8--8--8--8--8--8--8--8--- First of all, let us describe what we have done so far, what we found and how we think to proceed now. == 1. Bringing the patch up to date == We started from Gavin Sherry's patch dating May 2007 http://archives.postgresql.org/pgsql-patches/2007-05/msg00013.php As far as we could see, there has been no further activity on this subject. First, we brought the patch up to date. The latest version of the patch was anterior to the new Index Access Method Interface http://developer.postgresql.org/pgdocs/postgres/indexam.html so we adapted the patch to that interface. Then we added a few BMI page inspection functions to the pageinspect contrib module, and we used them to examine the code. In addition to finding and fixing a minor bug, we diagnosed an effect of HOT tuples on the BMI patch, described below in greater detail. This also helped us to produce extended descriptive documentation of how these indexes work, and suggested us how to produce some more tests to verify that (a newer version of) the BMI patch works; we are going to add some regression tests especially targeted to HOT tuples. After message http://archives.postgresql.org/pgsql-hackers/2008-10/msg00855.php maybe it is appropriate to mention that backwards scan would not be supported at all by BMI indexes. == 2. The effect of HOT tuples on BMI creation == The latest BMI patch mentioned above was also prior to the introduction of HOT tuples. Some parts of that patch rely on the assumption that IndexBuildHeapScan scans tuples in increasing TID order. It is easy to verify that this property is no longer valid after the introduction of HOT tuples; however, a similar but weaker property still holds (the scan is always done in non-decreasing block order). This breaks some low-level bitmap vector build routines, which have to be rewritten from scratch because they expect TIDs to came in increasing order; but it does not harm block-level locking used in that patch. == 3. What we would do after == We understand that BMI development was suspended because of lack of time from the last developer, during the improvement of the VACUUM phase. The main obstacle was that the physical size of a compressed bitmap vector can either grow or shrink, possibly creating new BMV pages, which can mean bad performance. The current VACUUM algorithm is unfinished; we are going to examine it, looking for some improvements, and to measure the current status with some ad-hoc benchmarks. == 4. Timeline == Up to now, we spent many days to isolate, describe and partially fix the incompatibilies described above; now we feel that points 1. and 2. can be cleared in a couple of days, bringing the patch up to date with current HEAD. As for the remaining part, we expect to finish the patch before the deadline for the latest CommitFest. We will re-post the patch as soon as the HOT tuples will be working; then we will post a new version the patch when also VACUUM will be done. Does anybody have any comments and/or additional requests? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On Tue, Oct 21, 2008 at 02:41:11PM +0300, Peter Eisentraut wrote: Preventing casual snooping without preventing MitM is a rational choice for system administrators. I am not an expert in these things, but it seems to me that someone who can casually snoop can also casually insert DHCP or DNS packages and redirect traffic. There is probably a small niche where just encryption without server authentication prevents information leaks, but it is not clear to me where this niche is or how it can be defined, and I personally wouldn't encourage this sort of setup. The example I know of is where there is a passive monitoring system which monitors and logs all network traffic. In this case MitM is not an issue because that's being monitored for. But avoiding the extra duplication of confidential data is worth something. It's not exactly a huge user group, but it exists. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] automatic parser generation for ecpg
On Tue, Oct 21, 2008 at 08:31:54AM -0400, Tom Lane wrote: So it's all pretty messy and neither choice is exactly desirable. I think maintaining parallel versions of an ecpg parser generator would be no fun at all, though, so the perl choice seems more or less forced. We could either preserve the current state of play by shipping the derived bison file in tarballs, or bite the bullet and say perl is required to build from source in all cases (in which case I'd be inclined to try to get rid of Gen_fmgrtab.sh etc). +1 for requiring it for source builds. We'll be able to simplify the code quite a bit :) As against that ... does a2p produce code that is readable/maintainable? Not that I've seen. There are modules on CPAN (I know, I know) for dealing with lexx and yacc, and those are probably better for the purpose. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Regression in IN( field, field, field ) performance
WHERE '12814474045' IN (people.home_phone, people.work_phone,
people.mobile_phone)
Yeah, not exactly a common case, but at least in 8.1 this was turned
into a set of ORs. Starting in 8.2 and in current HEAD, the planner
turns that into:
Filter: ('12814474045'::text = ANY ((ARRAY[home_phone, mobile_phone,
work_phone])::text[]))
Which means automatic seqscan. Would it be difficult to teach the
planner to handle this case differently? I know it's probably not
terribly common, but it is very useful.
--
Decibel! [EMAIL PROTECTED] (512) 569-9461
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cleanups/hostname verification
On Tuesday 21 October 2008 15:47:35 Tom Lane wrote: Gregory Stark [EMAIL PROTECTED] writes: Sort of. SSH requires you to install the certificate of the server locally before connecting. If you don't it pops up a big warning and asks if you want to install it. On subsequent connections it looks up the key for the name of the host you're trying to connect to and insists it match. If it doesn't it pops up a *huge* error and refuses to connect. Um, IIRC what it's checking there is the server's key signature, which has nada to do with certificates. It checks the fingerprint of the server public key. And a certificate is exactly a public key with additional information that explains whose public key that is. So when you install the fingerprint sent by the SSH server in your local known_hosts, then the server public key becomes a certificate. Sort of. But it's related. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] double-buffering page writes
Hi,
I'm trying to see if it makes sense to do the double-buffering of page
writes before going further ahead with CRC checking. I came up with the
attached patch; it does the double-buffering inconditionally, because as
it was said, it allows releasing the io_in_progress lock (and resetting
BM_IO_IN_PROGRESS) early.
So far I have not managed to convince me that this is a correct change
to make; the io_in_progress bits are pretty convoluted -- for example, I
wonder how does releasing the buffer early (before actually sending
the write to the kernel and marking it not dirty) interact with
checkpoint and a possible full-page image.
Basically the change is to take the unsetting of BM_DIRTY out of
TerminateBufferIO and into its own routine; and in FlushBuffer, release
io_in_progress just after copying the buffer contents elsewhere, and
mark the buffer not dirty after actually doing the write.
Thoughts?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/storage/buffer/bufmgr.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/storage/buffer/bufmgr.c,v
retrieving revision 1.239
diff -c -p -r1.239 bufmgr.c
*** src/backend/storage/buffer/bufmgr.c 20 Oct 2008 21:11:15 - 1.239
--- src/backend/storage/buffer/bufmgr.c 21 Oct 2008 15:30:38 -
*** static void BufferSync(int flags);
*** 84,91
static int SyncOneBuffer(int buf_id, bool skip_recently_used);
static void WaitIO(volatile BufferDesc *buf);
static bool StartBufferIO(volatile BufferDesc *buf, bool forInput);
! static void TerminateBufferIO(volatile BufferDesc *buf, bool clear_dirty,
! int set_flag_bits);
static void buffer_write_error_callback(void *arg);
static volatile BufferDesc *BufferAlloc(SMgrRelation smgr, ForkNumber forkNum,
BlockNumber blockNum,
--- 84,91
static int SyncOneBuffer(int buf_id, bool skip_recently_used);
static void WaitIO(volatile BufferDesc *buf);
static bool StartBufferIO(volatile BufferDesc *buf, bool forInput);
! static void TerminateBufferIO(volatile BufferDesc *buf, int set_flag_bits);
! static void MarkBufferNotDirty(volatile BufferDesc *buf);
static void buffer_write_error_callback(void *arg);
static volatile BufferDesc *BufferAlloc(SMgrRelation smgr, ForkNumber forkNum,
BlockNumber blockNum,
*** ReadBuffer_common(SMgrRelation smgr, boo
*** 395,401
else
{
/* Set BM_VALID, terminate IO, and wake up any waiters */
! TerminateBufferIO(bufHdr, false, BM_VALID);
}
if (VacuumCostActive)
--- 395,401
else
{
/* Set BM_VALID, terminate IO, and wake up any waiters */
! TerminateBufferIO(bufHdr, BM_VALID);
}
if (VacuumCostActive)
*** FlushBuffer(volatile BufferDesc *buf, SM
*** 1792,1797
--- 1792,1798
{
XLogRecPtr recptr;
ErrorContextCallback errcontext;
+ char dblbuf[BLCKSZ];
/*
* Acquire the buffer's io_in_progress lock. If StartBufferIO returns
*** FlushBuffer(volatile BufferDesc *buf, SM
*** 1834,1856
buf-flags = ~BM_JUST_DIRTIED;
UnlockBufHdr(buf);
smgrwrite(reln,
buf-tag.forkNum,
buf-tag.blockNum,
! (char *) BufHdrGetBlock(buf),
false);
BufferFlushCount++;
TRACE_POSTGRESQL_BUFFER_FLUSH_DONE(reln-smgr_rnode.spcNode,
reln-smgr_rnode.dbNode, reln-smgr_rnode.relNode);
- /*
- * Mark the buffer as clean (unless BM_JUST_DIRTIED has become set) and
- * end the io_in_progress state.
- */
- TerminateBufferIO(buf, true, 0);
-
/* Pop the error context stack */
error_context_stack = errcontext.previous;
}
--- 1835,1863
buf-flags = ~BM_JUST_DIRTIED;
UnlockBufHdr(buf);
+ /*
+ * We make a copy of the buffer to write. This allows us to release the
+ * io_in_progress lock early, before actually doing the write.
+ */
+ memcpy(dblbuf, BufHdrGetBlock(buf), BLCKSZ);
+
+ /* End the io_in_progress state. */
+ TerminateBufferIO(buf, 0);
+
smgrwrite(reln,
buf-tag.forkNum,
buf-tag.blockNum,
! (char *) dblbuf,
false);
+ /* Mark the buffer as clean (unless BM_JUST_DIRTIED has become set) */
+ MarkBufferNotDirty(buf);
+
BufferFlushCount++;
TRACE_POSTGRESQL_BUFFER_FLUSH_DONE(reln-smgr_rnode.spcNode,
reln-smgr_rnode.dbNode, reln-smgr_rnode.relNode);
/* Pop the error context stack */
error_context_stack = errcontext.previous;
}
*** StartBufferIO(volatile BufferDesc *buf,
*** 2578,2595
* We hold the buffer's io_in_progress lock
* The buffer is Pinned
*
- * If clear_dirty is TRUE and BM_JUST_DIRTIED is not set, we clear the
- * buffer's BM_DIRTY flag. This is appropriate when terminating a
- * successful write. The check on BM_JUST_DIRTIED is necessary to avoid
- * marking the buffer clean if it was
Re: [HACKERS] [COMMITTERS] pgsql: SQL 200N - SQL:2003
Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2008-10-20 at 16:18 +0100, Simon Riggs wrote: On Mon, 2008-10-20 at 14:26 +, Peter Eisentraut wrote: SQL 200N - SQL:2003 Why not SQL:2008? Peter? If the comment was meant to refer to SQL:2003 originally, it should probably be left that way. I don't want to get into the game of doing a global search-and-replace every time a new spec comes out. If anything, comments referring to particular spec versions should probably make a habit of referring to the *oldest* version in which a given feature exists, not the newest. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Buildfarm Cardinal going down.
I am going to do some hardware upgrading on buildfarm Cardinal. It will be down for sometime. Regards, Gevik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] automatic parser generation for ecpg
On Tue, Oct 21, 2008 at 08:45:11AM -0700, David Fetter wrote: As against that ... does a2p produce code that is readable/maintainable? Not that I've seen. There are modules on CPAN (I know, I know) for dealing with lexx and yacc, and those are probably better for the purpose. Well I think it's at least readable. Problem with your approach is that both Mike and I feel more comfortable with awk at the moment. If a2p produces a working perl script, that doesn't seem to be a problem IMO as we could maintain the awk script but deliver the perl version. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regression in IN( field, field, field ) performance
Jim 'Decibel!' Nasby [EMAIL PROTECTED] writes:
WHERE '12814474045' IN (people.home_phone, people.work_phone,
people.mobile_phone)
Yeah, not exactly a common case, but at least in 8.1 this was turned
into a set of ORs. Starting in 8.2 and in current HEAD, the planner
turns that into:
Filter: ('12814474045'::text = ANY ((ARRAY[home_phone, mobile_phone,
work_phone])::text[]))
Which means automatic seqscan.
It means no such thing.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] So what's an empty array anyway?
Currently, the constructs
'{}'::arraytype
ARRAY[]::arraytype
return zero-dimensional arrays, as does the underlying function
construct_empty_array(). I can't immediately find any way at SQL
level to produce an empty array with one or more dimensions.
However, construct_array and construct_md_array will happily build
zero-length arrays of dimension 1 or higher, leading to weirdnesses
such as illustrated here:
http://archives.postgresql.org/pgsql-general/2008-10/msg00915.php
Seems like we ought to clean this up. I'm not sure which way to jump
though: should we decree that arrays of no elements must always have
zero dimensions, or should we get rid of that and standardize on, say,
1-D array with lower bound 1 and upper bound 0?
A somewhat related issue that I noticed while poking at this is that
array_dims() returns NULL for a zero-dimension array. That seems a bit
bogus too; wouldn't an empty string be saner? Of course the issue
goes away if we get rid of zero-dimension arrays.
Thoughts?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Withdraw PL/Proxy from commitfest
Hannu Krosing wrote: In my brief reading of SQL-MED spec I could only find info on defining FOREIGN SERVER and FOREIGN-DATA WRAPPER and nowhere in these could one define connection parameters like username and password. It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take generic options (list of key/value pairs). These can be used for defining the actual connection to the remote server. From http://www.wiscorp.com/sql_2003_standard.zip 4.4 User mappings: A user mapping is an SQL-environment element, pairing an ... ... The mapping is specified by generic options defined by the foreign-data wrapper. and 13.3 user mapping definition: user mapping definition ::= CREATE USER MAPPING FOR specific or generic authorization identifier SERVER foreign server name [ generic options ] regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
2008/10/21 Tom Lane [EMAIL PROTECTED]:
Currently, the constructs
'{}'::arraytype
ARRAY[]::arraytype
return zero-dimensional arrays, as does the underlying function
construct_empty_array(). I can't immediately find any way at SQL
level to produce an empty array with one or more dimensions.
However, construct_array and construct_md_array will happily build
zero-length arrays of dimension 1 or higher, leading to weirdnesses
such as illustrated here:
http://archives.postgresql.org/pgsql-general/2008-10/msg00915.php
Seems like we ought to clean this up. I'm not sure which way to jump
though: should we decree that arrays of no elements must always have
zero dimensions, or should we get rid of that and standardize on, say,
1-D array with lower bound 1 and upper bound 0?
I believe so zero dimensions for empty array should be more clean and
more simple. This solve question about array_dims too. But this empty
dimensionless array should be simple cast to dimensional empty array.
array_ndims(array[]) -- 0
array[1,2] || array[] = array[1,2]
array[[1,2],[1,3]] || array[] = array[[1,2],[1,3]]
or
array[1,2] = array[] - false
array[[1,2],[1,3]] = array[] - false
Regards
Pavel Stehule
A somewhat related issue that I noticed while poking at this is that
array_dims() returns NULL for a zero-dimension array. That seems a bit
bogus too; wouldn't an empty string be saner? Of course the issue
goes away if we get rid of zero-dimension arrays.
Thoughts?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
Merlin Moncure [EMAIL PROTECTED] writes:
ISTM this is the way it should work from SQL level:
'{}'::int[] empty 1d
'{{},{}}'::int[] :: empty 2d
The first one looks okay, but ISTM the second one is not describing
an empty array: the upper dimension is of length 2. In particular
I think that under your proposal array_dims() would probably yield
these results:
[1:0]
[1:2][1:0]
and all of these would be different:
'{{}}'::int[] [1:1][1:0]
'{{},{}}'::int[][1:2][1:0]
'{{},{},{}}'::int[] [1:3][1:0]
Maybe this is okay but it feels a bit weird.
If you dump zero dimension arrays, then the problem about what to do
with array_dims goes away.
I'm not against dropping zero-dimension arrays ...
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regression in IN( field, field, field ) performance
On Oct 21, 2008, at 12:06 PM, Tom Lane wrote:
Jim 'Decibel!' Nasby [EMAIL PROTECTED] writes:
WHERE 'xxx' IN (people.home_phone, people.work_phone,
people.mobile_phone)
Yeah, not exactly a common case, but at least in 8.1 this was turned
into a set of ORs. Starting in 8.2 and in current HEAD, the planner
turns that into:
Filter: ('xxx'::text = ANY ((ARRAY[home_phone, mobile_phone,
work_phone])::text[]))
Which means automatic seqscan.
It means no such thing.
It won't use an index scan on this query while it's in that form
(even with enable_seqscan=off), but if I change it to a bunch of OR'd
conditions it will switch to bitmap scans. The estimated cost with
the seqscans is about 2x more expensive.
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
Re: [HACKERS] So what's an empty array anyway?
On Tue, Oct 21, 2008 at 2:23 PM, Tom Lane [EMAIL PROTECTED] wrote:
Merlin Moncure [EMAIL PROTECTED] writes:
ISTM this is the way it should work from SQL level:
'{}'::int[] empty 1d
'{{},{}}'::int[] :: empty 2d
The first one looks okay, but ISTM the second one is not describing
an empty array: the upper dimension is of length 2. In particular
I think that under your proposal array_dims() would probably yield
these results:
[1:0]
[1:2][1:0]
and all of these would be different:
'{{}}'::int[] [1:1][1:0]
'{{},{}}'::int[][1:2][1:0]
'{{},{},{}}'::int[] [1:3][1:0]
Maybe this is okay but it feels a bit weird.
agreed...you're right...and if this flies, you are still restricted to
making empty arrays for 1d only, so in this case I guess that's where
the array should be locked down.
If you dump zero dimension arrays, then the problem about what to do
with array_dims goes away.
I'm not against dropping zero-dimension arrays ...
yup.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
On Tue, 2008-10-21 at 13:50 -0400, Tom Lane wrote:
Currently, the constructs
'{}'::arraytype
ARRAY[]::arraytype
return zero-dimensional arrays, as does the underlying function
construct_empty_array(). I can't immediately find any way at SQL
level to produce an empty array with one or more dimensions.
However, construct_array and construct_md_array will happily build
zero-length arrays of dimension 1 or higher, leading to weirdnesses
such as illustrated here:
http://archives.postgresql.org/pgsql-general/2008-10/msg00915.php
Seems like we ought to clean this up. I'm not sure which way to jump
though: should we decree that arrays of no elements must always have
zero dimensions, or should we get rid of that and standardize on, say,
1-D array with lower bound 1 and upper bound 0?
A somewhat related issue that I noticed while poking at this is that
array_dims() returns NULL for a zero-dimension array. That seems a bit
bogus too; wouldn't an empty string be saner? Of course the issue
goes away if we get rid of zero-dimension arrays.
Please remove zero-dimension arrays.
The number of dimensions of an empty array really ought to be NULL, or
if we fix it to be non-NULL then 1+. Zero just makes a weird case for no
reason. An empty string only makes sense in the context of that
particular function, it doesn't really help with other maths.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: SQL 200N - SQL:2003
On Tuesday 21 October 2008 19:59:02 Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2008-10-20 at 16:18 +0100, Simon Riggs wrote: On Mon, 2008-10-20 at 14:26 +, Peter Eisentraut wrote: SQL 200N - SQL:2003 Why not SQL:2008? Peter? If the comment was meant to refer to SQL:2003 originally, it should probably be left that way. I don't want to get into the game of doing a global search-and-replace every time a new spec comes out. If anything, comments referring to particular spec versions should probably make a habit of referring to the *oldest* version in which a given feature exists, not the newest. That was the idea. I don't care much one way or another, but SQL:200N is obviously not very clear. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_ctl less than useful error message on windows when privileges wrong for postgres
I have a client who mistakenly gave the postgres user on a windows machine admin privileges. This mistake results in the service being unable to start up due to postgres refusing to start with admin privileges. The error message from pg_ctl start -D bindir is PG_CTL..could not locate matching postgres executable Is it not possible to provide a more useful error message ? Dave -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
On Oct 21, 2008, at 12:08, Simon Riggs wrote: Please remove zero-dimension arrays. The number of dimensions of an empty array really ought to be NULL, or if we fix it to be non-NULL then 1+. Zero just makes a weird case for no reason. An empty string only makes sense in the context of that particular function, it doesn't really help with other maths. If we got rid of zero dimension arrays, how would I declare a new empty array in a PL/pgSQL function? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
David E. Wheeler wrote: On Oct 21, 2008, at 12:08, Simon Riggs wrote: If we got rid of zero dimension arrays, how would I declare a new empty array in a PL/pgSQL function? Why would you want to do that? Is there a use case for that? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regression in IN( field, field, field ) performance
Decibel! [EMAIL PROTECTED] writes:
On Oct 21, 2008, at 12:06 PM, Tom Lane wrote:
Jim 'Decibel!' Nasby [EMAIL PROTECTED] writes:
Filter: ('xxx'::text = ANY ((ARRAY[home_phone, mobile_phone,
work_phone])::text[]))
Which means automatic seqscan.
It means no such thing.
It won't use an index scan on this query while it's in that form
(even with enable_seqscan=off), but if I change it to a bunch of OR'd
conditions it will switch to bitmap scans.
Works fine for me, eg
regression=# explain select * from tenk1 a, tenk1 b where
regression-# b.unique2 = any(array[a.unique1,a.ten,a.hundred]);
QUERY PLAN
--
Nested Loop (cost=0.79..49047.50 rows=29997 width=488)
- Seq Scan on tenk1 a (cost=0.00..458.00 rows=1 width=244)
- Bitmap Heap Scan on tenk1 b (cost=0.79..4.82 rows=3 width=244)
Recheck Cond: (b.unique2 = ANY (ARRAY[a.unique1, a.ten, a.hundred]))
- Bitmap Index Scan on tenk1_unique2 (cost=0.00..0.79 rows=3 width=0
)
Index Cond: (b.unique2 = ANY (ARRAY[a.unique1, a.ten, a.hundred])
)
(6 rows)
You'll need to provide a concrete test case if you think there's
something broken here.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multi CPU Queries - Feedback and/or suggestions wanted!
Hi Jeffrey, thank you for the suggestion. Yes, they potentially can, we'll consider this. Julo Jeffrey Baker wrote: I don't see why multiple CPUs can't work on the same node of a plan. For instance, consider a node involving a scan with an expensive condition, like UTF-8 string length. If you have four CPUs you can bring to bear, each CPU could take every fourth page, computing the expensive condition for each tuple in that page. The results of the scan can be retired asynchronously to the next node above. -jwb -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
On Oct 21, 2008, at 13:00, Andrew Chernow wrote: On Oct 21, 2008, at 12:08, Simon Riggs wrote: If we got rid of zero dimension arrays, how would I declare a new empty array in a PL/pgSQL function? Why would you want to do that? Is there a use case for that? Perhaps not. In older versions of PostgreSQL, I *had* to initialize an empty array in a DECLARE block or else I couldn't use it with array_append() to collect things in an array in a loop. I don't have to do so 8.3, but I keep it that way in some modules for compatibility reasons. But since that was perhaps an issue with older versions of PostgreSQL that has since been addressed, I guess I just think too much like a Perl hacker, where I can add things to an array as I need to. That's different from SQL arrays, where you can't add a value to an existing array, create a new array from an old one plus a new value. So I guess I don't *have* to have it, but for compatibility with older versions of PostgreSQL, I think they should be kept. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
David E. Wheeler [EMAIL PROTECTED] writes:
If we got rid of zero dimension arrays, how would I declare a new
empty array in a PL/pgSQL function?
Same as before, I think: initialize it to '{}'. What's at stake here
is exactly what does that notation mean ...
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
On Oct 21, 2008, at 13:58, Tom Lane wrote:
If we got rid of zero dimension arrays, how would I declare a new
empty array in a PL/pgSQL function?
Same as before, I think: initialize it to '{}'. What's at stake here
is exactly what does that notation mean ...
An empty, single-dimension array. But I got the impression from Simon
that he thought it should be NULL.
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
An empty, single-dimension array. But I got the impression from Simon
that he thought it should be NULL.
I disagree with Simon *if* that's what he's saying. '{}' isn't equivalent
to NULL any more than 0 or '' is. NULL means I don't know / Doesn't
apply wheras '{}' means purposefully left blank. It's a defect of the
Timestamp type (and a few others) that it doesn't have a standard zero
value -- the typical tri-value NULL problem.
I do agree that we ought to support multi-dimensional empty arrays for
consistency. However: is '{}' = '{}{}' or not?
--
--Josh
Josh Berkus
PostgreSQL
San Francisco
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
David E. Wheeler [EMAIL PROTECTED] writes:
On Oct 21, 2008, at 13:58, Tom Lane wrote:
Same as before, I think: initialize it to '{}'. What's at stake here
is exactly what does that notation mean ...
An empty, single-dimension array. But I got the impression from Simon
that he thought it should be NULL.
Well, we can't do that because it would clearly break too much existing
code. '{}' has got to result in something you can successfully
concatenate more elements to. But either the current behavior with
a zero-dimension array, or a one-dimensional length-zero array would
presumably work okay.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bitmap Indexes: request for feedback
Gianni, me and Gabriele Bartolini have been working on Bitmap Indexes (BMI) in the last weeks, with advice and guidance from Simon Riggs. We feel that we are about to approach the point where it is appropriate to ask for feedback from this list. The other major issue with the Bitmap index patch as it stood in 2007 was that performance just wasn't that much faster than a btree, except for specific corner cases. Otherwise, someone else would have been interested enough to pick it up and finish it. So performance testing of the patch is absolutely essential. -- --Josh Josh Berkus PostgreSQL San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
On Oct 21, 2008, at 14:16, Tom Lane wrote:
Well, we can't do that because it would clearly break too much
existing
code. '{}' has got to result in something you can successfully
concatenate more elements to.
Right, that's what I was trying to day. Badly, I guess.
But either the current behavior with
a zero-dimension array, or a one-dimensional length-zero array would
presumably work okay.
Right, that sounds right to me.
Thanks,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_ctl less than useful error message on windows when privileges wrong for postgres
Dave Cramer [EMAIL PROTECTED] writes: I have a client who mistakenly gave the postgres user on a windows machine admin privileges. This mistake results in the service being unable to start up due to postgres refusing to start with admin privileges. The error message from pg_ctl start -D bindir is PG_CTL..could not locate matching postgres executable It's fairly hard to see how that mistake leads to that symptom. Can you poke a bit more into exactly what is happening? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
On Tue, Oct 21, 2008 at 1:50 PM, Tom Lane [EMAIL PROTECTED] wrote:
Currently, the constructs
'{}'::arraytype
ARRAY[]::arraytype
return zero-dimensional arrays, as does the underlying function
construct_empty_array(). I can't immediately find any way at SQL
level to produce an empty array with one or more dimensions.
However, construct_array and construct_md_array will happily build
zero-length arrays of dimension 1 or higher, leading to weirdnesses
such as illustrated here:
http://archives.postgresql.org/pgsql-general/2008-10/msg00915.php
Seems like we ought to clean this up. I'm not sure which way to jump
though: should we decree that arrays of no elements must always have
zero dimensions, or should we get rid of that and standardize on, say,
1-D array with lower bound 1 and upper bound 0?
A somewhat related issue that I noticed while poking at this is that
array_dims() returns NULL for a zero-dimension array. That seems a bit
bogus too; wouldn't an empty string be saner? Of course the issue
goes away if we get rid of zero-dimension arrays.
Thoughts?
This reminds me a little bit of the zero point polygon issue we
uncovered a while back. IMO, you shouldn't be able to create things
that are not possible at the sql levelit invariably leads to
errors. But why do you have to force empty arrays to 1 dims? This
seems like needless restriction.
ISTM this is the way it should work from SQL level:
'{}'::int[] empty 1d
'{{},{}}'::int[] :: empty 2d
If you dump zero dimension arrays, then the problem about what to do
with array_dims goes away. Otherwise, I'd make:
''::int[] as empty 0d array
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what's an empty array anyway?
Seems like we ought to clean this up. I'm not sure which way to jump
though: should we decree that arrays of no elements must always have
zero dimensions, or should we get rid of that and standardize on, say,
1-D array with lower bound 1 and upper bound 0?
Isn't the zero-dimensional array vaguely polymorphic? If '{}'::int[]
means a one-dimensional array, how do I create an empty
two-dimensional array onto which I can concatenate one-dimensional
arrays that are all of the same length? I don't necessarily object to
changing this, even if it breaks backward-compatibility, but there
should be SOME easy way to do it.
A somewhat related issue that I noticed while poking at this is that
array_dims() returns NULL for a zero-dimension array. That seems a bit
bogus too; wouldn't an empty string be saner? Of course the issue
goes away if we get rid of zero-dimension arrays.
Most all of the existing array functions contain identical checks for
ndims out of range (and 0 is considered out of range) and return NULL
in that case. This behavior doesn't appear to make a great deal of
sense to me in general. If these functions can be called with an
object that's not an array, then the check is not nearly strong enough
to prevent chaos; if they can't, the check is unnecessary. But maybe
I'm missing something?
In any event, the correct behavior for all of these functions on a
0-dimensional array should probably be reviewed, unless we eliminate
0-dimensional arrays.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_ctl less than useful error message on windows when privileges wrong for postgres
On 21-Oct-08, at 5:24 PM, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: I have a client who mistakenly gave the postgres user on a windows machine admin privileges. This mistake results in the service being unable to start up due to postgres refusing to start with admin privileges. The error message from pg_ctl start -D bindir is PG_CTL..could not locate matching postgres executable It's fairly hard to see how that mistake leads to that symptom. Can you poke a bit more into exactly what is happening? find_my_exec returns -1 for a number of errors resulting in an error message much like above (wording is slightly different). when I executed postgres.exe directly it complained of the user having admin privs I removed admin privs from the pg user and pg_ctl worked fine. I know it's not very specific, but fairly damning. Dave -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Withdraw PL/Proxy from commitfest
On Tue, 2008-10-21 at 21:05 +0300, Martin Pihlak wrote: Hannu Krosing wrote: In my brief reading of SQL-MED spec I could only find info on defining FOREIGN SERVER and FOREIGN-DATA WRAPPER and nowhere in these could one define connection parameters like username and password. It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take generic options (list of key/value pairs). These can be used for defining the actual connection to the remote server. Are you sure this is how it is intended to be done ? From http://www.wiscorp.com/sql_2003_standard.zip 4.4 User mappings: A user mapping is an SQL-environment element, pairing an ... ... The mapping is specified by generic options defined by the foreign-data wrapper. and 13.3 user mapping definition: user mapping definition ::= CREATE USER MAPPING FOR specific or generic authorization identifier SERVER foreign server name [ generic options ] In pl/proxy context this would mean that in order to define connection info we would at least need (foreign) SERVER and USER MAPPING objects defined so CREATE SERVER foreign server name [ TYPE server type ] [ VERSION server version ] FOREIGN DATA WRAPPER foreign-data wrapper name OPTIONS (HOST host.ip, PORT port_nr, DBNAME databasename) ; probably with a default / dummy FOREIGN DATA WRAPPER called DEFAULT and CREATE USER MAPPING FOR specific or generic authorization identifier SERVER foreign server name OPTIONS (USER username, PASSWORD pwd) ; plus a possibility to GRANT USAGE on those and also the function ConnectServer(foreign server name) to actually make the connection. I guess we can skip the FOREIGN DATA WRAPPER stuff until we actually need it. there has to be some mechanism for prioritizing USER MAPPINGs in case you can use many. Maybe have an extra argument for ConnectServer(foreign server name, specific or generic authorization identifier) . Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regression in IN( field, field, field ) performance
Tom Lane [EMAIL PROTECTED] writes: Works fine for me, eg ... - Bitmap Heap Scan on tenk1 b (cost=0.79..4.82 rows=3 width=244) Recheck Cond: (b.unique2 = ANY (ARRAY[a.unique1, a.ten, a.hundred])) - Bitmap Index Scan on tenk1_unique2 (cost=0.00..0.79 rows=3 width=0 ) Index Cond: (b.unique2 = ANY (ARRAY[a.unique1, a.ten, a.hundred]) But that's an index on the lhs of the =ANY which in his example was just a constant. You'll need to provide a concrete test case if you think there's something broken here. I think he's looking for something like: 5 IN (col1,col2,col3) resulting in a bitmap or of three index scans of three different indexes on col1, col2, and col3. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bitmap Indexes: request for feedback
Josh Berkus [EMAIL PROTECTED] writes: Gianni, me and Gabriele Bartolini have been working on Bitmap Indexes (BMI) in the last weeks, with advice and guidance from Simon Riggs. We feel that we are about to approach the point where it is appropriate to ask for feedback from this list. The other major issue with the Bitmap index patch as it stood in 2007 was that performance just wasn't that much faster than a btree, except for specific corner cases. Otherwise, someone else would have been interested enough to pick it up and finish it. Actually as I recall the immediate issue was that the patch was more complex than necessary. In particular it reimplemented parts of the executor internally rather than figuring out what api was necessary to integrate it fully into the executor. When we last left our heros they were proposing ways to refactor the index api to allow index ams to stream results to the executor in bitmap form. That would allow a scan of a bitmap index to return bitmap elements wholesale and have the executor apply bitmap operations to them along with the elements returned by a btree bitmap scan or other index ams. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multi CPU Queries - Feedback and/or suggestions wanted!
I can confirm that bringing Postgres code to multi-thread implementation requires quite a bit of ground work. I have been working for a long while with a Postgres 7.* fork that uses pthreads rather than processes. The effort to make all the subsystems thread safe took some time and touched almost every section of the codebase. I recently spent some time trying to optimize for Chip Multi-Threading systems but focused more on total throughput rather than single query performance. The biggest wins came from changing some coarse grained locks in the page buffering system to a finer grained implementation. I also tried to improve single query performance by splitting index and sequential scans into two threads, one to fault in pages and check tuple visibility and the other for everything else. My success was limited and it was hard for me to work the proper costing into the query optimizer so that it fired at the right times. One place that multiple threads really helped was in index building. My code is poorly commented and the build system is a mess (I am only building 64bit SPARC for embedding into another app). However, I am using it in production and source is available if it's of any help. http://weaver2.dev.java.net Myron Scott On Oct 20, 2008, at 11:28 PM, Chuck McDevitt wrote: There is a problem trying to make Postgres do these things in Parallel. The backend code isn’t thread-safe, so doing a multi-thread implementation requires quite a bit of work. Using multiple processes has its own problems: The whole way locking works equates one process with one transaction (The proc table is one entry per process). Processes would conflict on locks, deadlocking themselves, as well as many other problems. It’s all a good idea, but the work is probably far more than you expect. Async I/O might be easier, if you used pThreads, which is mostly portable, but not to all platforms. (Yes, they do work on Windows) From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ] On Behalf Of Jeffrey Baker Sent: 2008-10-20 22:25 To: Julius Stroffek Cc: pgsql-hackers@postgresql.org; Dano Vojtek Subject: Re: [HACKERS] Multi CPU Queries - Feedback and/or suggestions wanted! On Mon, Oct 20, 2008 at 12:05 PM, Julius Stroffek [EMAIL PROTECTED] wrote: Topics that seem to be of interest and most of them were already discussed at developers meeting in Ottawa are 1.) parallel sorts 2.) parallel query execution 3.) asynchronous I/O 4.) parallel COPY 5.) parallel pg_dump 6.) using threads for parallel processing [...] 2.) Different subtrees (or nodes) of the plan could be executed in parallel on different CPUs and the results of this subtrees could be requested either synchronously or asynchronously. I don't see why multiple CPUs can't work on the same node of a plan. For instance, consider a node involving a scan with an expensive condition, like UTF-8 string length. If you have four CPUs you can bring to bear, each CPU could take every fourth page, computing the expensive condition for each tuple in that page. The results of the scan can be retired asynchronously to the next node above. -jwb -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_ctl less than useful error message on windows when privileges wrong for postgres
Dave Cramer [EMAIL PROTECTED] writes: On 21-Oct-08, at 5:24 PM, Tom Lane wrote: It's fairly hard to see how that mistake leads to that symptom. Can you poke a bit more into exactly what is happening? when I executed postgres.exe directly it complained of the user having admin privs With no arguments, that's not surprising; but pg_ctl invokes it with the -V option, which should result in a version response occurring before the root-privileges check (look at main/main.c). So I think there's something going on here that you've not identified. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bitmap Indexes: request for feedback
Gregory Stark [EMAIL PROTECTED] writes: Josh Berkus [EMAIL PROTECTED] writes: The other major issue with the Bitmap index patch as it stood in 2007 was that performance just wasn't that much faster than a btree, except for specific corner cases. Otherwise, someone else would have been interested enough to pick it up and finish it. Actually as I recall the immediate issue was that the patch was more complex than necessary. Well, yeah, but if the performance isn't there then who's going to spend time refactoring the code? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
