Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Mon, 28 Jan 2008, Jeff Davis wrote: I think that pg_dump is a reasonable use case for synchoronized scans when the table has not been clustered. It could potentially make pg_dump have much less of a performance impact when run against an active system. One of the advantages I see with maintaining table dump order is that rsyncing backups to remote locations will work better. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] WIP: plpgsql source code obfuscation
Andrew Dunstan <[EMAIL PROTECTED]> writes: > using this example, it seems to me that if we dump the encrypted/encoded > source and restore into another database with a different encoding, the > decoded/decrypted source will still be in the old database encoding, > i.e. not valid in the new database encoding. We've just gone around > closing doors like this. Ah, right, I hadn't thought about that, but it would be a hazard. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Sun, 2008-01-27 at 15:07 -0500, Tom Lane wrote: > Per today's -hackers discussion, add a GUC variable to allow clients to > disable the new synchronized-scanning behavior, and make pg_dump disable > sync scans so that it will reliably preserve row ordering. This is a > pretty trivial patch, but seeing how late we are in the 8.3 release > cycle, I thought I'd better post it for comment anyway. I apologize for the late reply, but I have one comment I'd like to add. > + if (g_fout->remoteVersion >= 80300) > + do_sql_command(g_conn, "SET synchronized_scanning TO off"); > + > + /* >* Start serializable transaction to dump consistent data. >*/ I think that pg_dump is a reasonable use case for synchoronized scans when the table has not been clustered. It could potentially make pg_dump have much less of a performance impact when run against an active system. I think it's worth considering enabling sync scans for non-clustered tables if it would not interfere with the release. Of course, a painless 8.3 release is the top priority. Regards, Jeff Davis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] WIP: plpgsql source code obfuscation
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Tom Lane wrote: However, I definitely agree that a separate loadable PL is the way to go for functionality of this sort. There is no way that a dependency on pgcrypto is going to be accepted into core, not even in the (ahem) obfuscated way that it's presented here. If we do anything in core it could be to make provision for an obfuscation/encryption hook via a loadable module. My recollection is that certain cryptography laws make hooks for crypto just as problematic as actual crypto code. We'd have to tread very carefully --- "general purpose" hooks are OK but anything narrowly tailored to encryption purposes would be a hazard. This is one reason that I'd prefer to see it as an external PL rather than embedded in core. It could be something other than encryption; any sort of transformation might fit. For example, one might do something like: gzip | some-sort-of-shuffle | base64-encode as a sort or poor man's obfuscation Various interesting encoding issues could arise with dumping and restoring transformed program text - I haven't thought that through yet. I think we have already solved that with md5 passwords, and could easily reuse the same kind of approach. You just base64 encode the crypted text (or whatever you need to do to avoid funny characters in it), and make sure that there's some way to distinguish already-crypted from not-already-crypted function bodies. I don't see how a binary MD5 checksum has any encoding component. But using this example, it seems to me that if we dump the encrypted/encoded source and restore into another database with a different encoding, the decoded/decrypted source will still be in the old database encoding, i.e. not valid in the new database encoding. We've just gone around closing doors like this. You might be able to fix it by storing the database encoding name along with the encrypted/encoded source, so it could be transformed at the other end. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
"Tom Lane" <[EMAIL PROTECTED]> writes: > My recollection is that certain cryptography laws make hooks for crypto > just as problematic as actual crypto code. We'd have to tread very > carefully --- "general purpose" hooks are OK but anything narrowly > tailored to encryption purposes would be a hazard. Afaik the US was the only country with such a scheme with the ITAR export regulations and that's long since gone, at least as it applied to crypto. The current US export regulations don't have any of the stuff about hooks in them and exempt free software from any crypto export licenses. Doesn't stop some other country from coming up with the same idea of course but we don't generally worry about what laws some hypothetical country might introduce at some point in the future. That way lies madness. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Auto-explain patch
On Mon, Jan 28, 2008 at 08:49:23PM +, Dean Rasheed wrote:
>
> > On Mon, Jan 28, 2008 at 07:55:53PM +, Dean Rasheed wrote:
> >>
> >>> Dean,
> >>>
> >>> Maybe I missed something obvious here, but how does this patch handle
> >>> the situation where people have turned on INTEGER_DATETIMES?
> >>>
> >>> Cheers,
> >>> David.
> >>> --
> >>> David Fetter 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
> >>
> >> Sorry, I don't understand. I am new to this code, but I can't see
> >> how the INTEGER_DATETIMES flag will affect this code. I am using the
> >> macros and functions from instrument.h and explain.c for timing,
> >> which appear to use gettimeofday() or QueryPerformanceCounter(),
> >> coverting the result to a double to report the total time spent
> >> running the query.
> >
> > It's the double part I don't quite get. Shouldn't that be an int64 in
> > the case of INTEGER_DATETIMES?
> >
> > Cheers,
> > David.
> >
>
> All the times are 64-bit integers (or at least structures with 2
> 32-bit integers in them) until the end, when the elapsed time is
> converted to a double so that the query runtime can be printed out
> in ms ("Query runtime: %.3f ms"). This is the same as EXPLAIN
> ANALYSE, except in that case it is the total runtime ("Total
> runtime: %.3f ms\n") that gets reported, including startup/shutdown
> trigger times.
Thanks for the explanation, and sorry for the noise :)
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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> However, I definitely agree that a separate loadable PL is the way to go >> for functionality of this sort. There is no way that a dependency on >> pgcrypto is going to be accepted into core, not even in the (ahem) >> obfuscated way that it's presented here. > If we do anything in core it could be to make provision for an > obfuscation/encryption hook via a loadable module. My recollection is that certain cryptography laws make hooks for crypto just as problematic as actual crypto code. We'd have to tread very carefully --- "general purpose" hooks are OK but anything narrowly tailored to encryption purposes would be a hazard. This is one reason that I'd prefer to see it as an external PL rather than embedded in core. > Various interesting encoding issues could arise with dumping and > restoring transformed program text - I haven't thought that through yet. I think we have already solved that with md5 passwords, and could easily reuse the same kind of approach. You just base64 encode the crypted text (or whatever you need to do to avoid funny characters in it), and make sure that there's some way to distinguish already-crypted from not-already-crypted function bodies. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
Gregory Stark <[EMAIL PROTECTED]> writes: > There is a validator function which gets called when you create a > function but I don't think it has any opportunity to substitute its > result for the original in prosrc. It would have to do a heap_update on the prosrc row, but that doesn't seem like a showstopper problem. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Auto-explain patch
> Date: Mon, 28 Jan 2008 12:08:00 -0800
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> CC: pgsql-patches@postgresql.org
> Subject: Re: [PATCHES] Auto-explain patch
>
> On Mon, Jan 28, 2008 at 07:55:53PM +, Dean Rasheed wrote:
>>
>>> Dean,
>>>
>>> Maybe I missed something obvious here, but how does this patch handle
>>> the situation where people have turned on INTEGER_DATETIMES?
>>>
>>> Cheers,
>>> David.
>>> --
>>> David Fetter 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
>>
>> Sorry, I don't understand. I am new to this code, but I can't see
>> how the INTEGER_DATETIMES flag will affect this code. I am using the
>> macros and functions from instrument.h and explain.c for timing,
>> which appear to use gettimeofday() or QueryPerformanceCounter(),
>> coverting the result to a double to report the total time spent
>> running the query.
>
> It's the double part I don't quite get. Shouldn't that be an int64 in
> the case of INTEGER_DATETIMES?
>
> Cheers,
> David.
>
All the times are 64-bit integers (or at least structures with 2 32-bit
integers in them) until the end, when the elapsed time is converted to a double
so that the query runtime can be printed out in ms ("Query runtime: %.3f ms").
This is the same as EXPLAIN ANALYSE, except in that case it is the total
runtime ("Total runtime: %.3f ms\n") that gets reported, including
startup/shutdown trigger times.
Dean.
_
Share what Santa brought you
https://www.mycooluncool.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PATCHES] Friendly help for psql
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Greg Sabino Mullane wrote: -- Start of PGP signed section. > Why not run help when someone enters "help" (or "HELP ME!") on the > command line? \? is hardly an easy thing to remember (and some people > can't be bothered to actually read the screen...) > [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Auto-explain patch
On Mon, Jan 28, 2008 at 07:55:53PM +, Dean Rasheed wrote: > > > Dean, > > > > Maybe I missed something obvious here, but how does this patch handle > > the situation where people have turned on INTEGER_DATETIMES? > > > > Cheers, > > David. > > -- > > David Fetter 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 > > Sorry, I don't understand. I am new to this code, but I can't see > how the INTEGER_DATETIMES flag will affect this code. I am using the > macros and functions from instrument.h and explain.c for timing, > which appear to use gettimeofday() or QueryPerformanceCounter(), > coverting the result to a double to report the total time spent > running the query. It's the double part I don't quite get. Shouldn't that be an int64 in the case of INTEGER_DATETIMES? Cheers, David. > > As far as I can see, the INTEGER_DATETIMES flag doesn't affect this > code. Am I missing something? > > Dean. > > _ > Free games, great prizes - get gaming at Gamesbox. > http://www.searchgamesbox.com -- 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Auto-explain patch
> Dean, > > Maybe I missed something obvious here, but how does this patch handle > the situation where people have turned on INTEGER_DATETIMES? > > Cheers, > David. > -- > David Fetter 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 Sorry, I don't understand. I am new to this code, but I can't see how the INTEGER_DATETIMES flag will affect this code. I am using the macros and functions from instrument.h and explain.c for timing, which appear to use gettimeofday() or QueryPerformanceCounter(), coverting the result to a double to report the total time spent running the query. As far as I can see, the INTEGER_DATETIMES flag doesn't affect this code. Am I missing something? Dean. _ Free games, great prizes - get gaming at Gamesbox. http://www.searchgamesbox.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Friendly help for psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Uh, imagine: > > test=> SELECT * from pg_class > test-> help > > Technically 'help' is now an alias for 'pg_class'. Are you suggesting > supporting 'help' in this usage? People were saying they forget > semicolons, so this 'help' usage is quite possible. We don't want to > hear "Why doesn't 'help' work sometimes?" I don't think we have to worry about supporting that case. We only support it when the command begins with "help". No ambiguity with SQL to worry about. Don't forget that ctrl-c resets a query as well, so the above situation is not as dire as it may appear to the uninitiated user. > Supporting 'help' in psql seems like a very slippery slope. We are very > tight in defining when an entry is psql and when it is SQL and this > weakens that. Does not seem slippery to me, seems a simple, one-time fix that replaces a syntax error with a helpful message. As the instigator of this discussion, I can assure you I have no plans for anything other than "help". It's simple, standard, and points you to anything else you may need or want to do. > What would be interesting would be if the _server_ could send back some > message about "Use the help facility of your client application" but it > would have to have a trailing semicolon; unlikely. :-( Now THAT would be a bad mangling of SQL and non-SQL. We'll pretend you didn't suggest that one. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200801281451 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHnjJgvJuQZxSWSsgRA8mMAJ4jzlrwYDVguUNt2yAwMTXMvV1S3QCg+SaF n8ybxb/KbGBxW9aouZGpzaY= =igJU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Proposed patch to disallow password=foo in database name parameter
On Tue, Dec 11, 2007 at 08:58:05AM -0500, Andrew Dunstan wrote: > >I'm actually inclined to vote with Stephen that this is a silly change. > >I just put up the patch to show the best way of doing it if we're gonna > >do it ... > > OK. I'm not going to die in a ditch over it. On the other hand, warning about it in the docs would probably be a good idea... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpqQC74CaoF8.pgp Description: PGP signature
Re: [PATCHES] Better default_statistics_target
On Wed, Dec 05, 2007 at 06:49:00PM +0100, Guillaume Smet wrote: > On Dec 5, 2007 3:26 PM, Greg Sabino Mullane <[EMAIL PROTECTED]> wrote: > > Agreed, this would be a nice 8.4 thing. But what about 8.3 and 8.2? Is > > there a reason not to make this change? I know I've been lazy and not run > > any absolute figures, but rough tests show that raising it (from 10 to > > 100) results in a very minor increase in analyze time, even for large > > databases. I think the burden of a slightly slower analyze time, which > > can be easily adjusted, both in postgresql.conf and right before running > > an analyze, is very small compared to the pain of some queries - which > > worked > > before - suddenly running much, much slower for no apparent reason at all. > > As Tom stated it earlier, the ANALYZE slow down is far from being the > only consequence. The planner will also have more work to do and > that's the hard point IMHO. How much more? Doesn't it now use a binary search? If so, ISTM that going from 10 to 100 would at worst double the time spent finding the bucket we need. Considering that we're talking something that takes microseconds, and that there's a huge penalty to be paid if you have bad stats estimates, that doesn't seem that big a deal. And on modern machines it's not like the additional space in the catalogs is going to kill us. FWIW, I've never seen anything but a performance increase or no change when going from 10 to 100. In most cases there's a noticeable improvement since it's common to have over 100k rows in a table, and there's just no way to capture any kind of a real picture of that with only 10 buckets. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpgJJU7Asl3N.pgp Description: PGP signature
Re: [PATCHES] WIP: plpgsql source code obfuscation
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Maybe a better TODO would be to do this task in the way that has previously been suggested: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php I'm certainly not happy about any proposal to put a password/key in a GUC var - that strikes me as a major footgun. We didn't really have a better solution to the key management problem, though, did we? At least I don't see anything about it in that thread. Yeah. Maybe we could have the GUC var contain the name of a key file rather than the key itself. If we require that the name be relative to the datadir that might be tolerably secure. However, I definitely agree that a separate loadable PL is the way to go for functionality of this sort. There is no way that a dependency on pgcrypto is going to be accepted into core, not even in the (ahem) obfuscated way that it's presented here. If we do anything in core it could be to make provision for an obfuscation/encryption hook via a loadable module. Various interesting encoding issues could arise with dumping and restoring transformed program text - I haven't thought that through yet. But I agree a simple PL wrapper makes sense to start with, at any rate. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Pavel Stehule" <[EMAIL PROTECTED]> writes: > > > Do you thing some binary module that load some encrypted sources from > > files? It can be possible too. But if source code will be stored in > > pg_proc, then we need third method. Some like "obfuscate" (prev. are > > validate and call"), because we can't to store plain text to prosrc > > col. > > Is there a reason you couldn't, for instance, provide a function which takes > source code and encrypts it. Then you would write dump the data it spits into > your function declaration like: > > CREATE FUNCTION foo() returns integer AS $$ > ... base64 encoded data > $$ language "obfuscated:plperl"; > it's solve problem with dump well, but it's similar to my solution. "obfuscated:plperl" can be virtual language - we can have one common handler, because there is same work. I am not sure. This doesn't care any better security, only add some other necessary external toolkit. With obfuscate column or obfuscate language (it carry same information) I can use prosrc and I have not problem with dump too. It is true, so obfuscate languages move dependency to out of core - but it is more complex. > "obfuscated:plperl"'s handler function would just decrypt it and pass it off > to plperl. you need same handler for plpgsql, python, sql, ... so why don't do it generally? Pavel > > There is a validator function which gets called when you create a function but > I don't think it has any opportunity to substitute its result for the original > in prosrc. That might be interesting for other applications like compiled > languages, though I think they would still want to save the source in prosrc > and the bytecode in probin. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL > training! > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > Do you thing some binary module that load some encrypted sources from > files? It can be possible too. But if source code will be stored in > pg_proc, then we need third method. Some like "obfuscate" (prev. are > validate and call"), because we can't to store plain text to prosrc > col. Is there a reason you couldn't, for instance, provide a function which takes source code and encrypts it. Then you would write dump the data it spits into your function declaration like: CREATE FUNCTION foo() returns integer AS $$ ... base64 encoded data $$ language "obfuscated:plperl"; "obfuscated:plperl"'s handler function would just decrypt it and pass it off to plperl. There is a validator function which gets called when you create a function but I don't think it has any opportunity to substitute its result for the original in prosrc. That might be interesting for other applications like compiled languages, though I think they would still want to save the source in prosrc and the bytecode in probin. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > Maybe a better TODO would be to do this task in the way that has > > previously been suggested: > > http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php > > I'm certainly not happy about any proposal to put a password/key in a > > GUC var - that strikes me as a major footgun. > > We didn't really have a better solution to the key management problem, > though, did we? At least I don't see anything about it in that thread. > > However, I definitely agree that a separate loadable PL is the way to go > for functionality of this sort. There is no way that a dependency on > pgcrypto is going to be accepted into core, not even in the (ahem) > obfuscated way that it's presented here. > Do you thing some binary module that load some encrypted sources from files? It can be possible too. But if source code will be stored in pg_proc, then we need third method. Some like "obfuscate" (prev. are validate and call"), because we can't to store plain text to prosrc col. My patch is only solution for some users, and I know about problem with dependency. Reagards Pavel Stehule > regards, tom lane > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Maybe a better TODO would be to do this task in the way that has > previously been suggested: > http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php > I'm certainly not happy about any proposal to put a password/key in a > GUC var - that strikes me as a major footgun. We didn't really have a better solution to the key management problem, though, did we? At least I don't see anything about it in that thread. However, I definitely agree that a separate loadable PL is the way to go for functionality of this sort. There is no way that a dependency on pgcrypto is going to be accepted into core, not even in the (ahem) obfuscated way that it's presented here. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Pavel Stehule" <[EMAIL PROTECTED]> writes: > > >> In such a scheme I think you would put the key in an attribute of the > >> language. Either in pg_lang or some configuration location which the > >> obfuscate:plperl interpreter knows where to find. > >> > > > > what is advantage? > > It wouldn't require any core changes. It would be just another PL language to > load which can be installed like other ones. This could be a big advantage > because it doesn't look like there is a lot of support for putting th > obfuscation directly into the core code. can be. but I am afraid so any changes are necessary in core too Pavel > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's 24x7 Postgres support! > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Auto-explain patch
On Mon, Jan 28, 2008 at 09:21:50AM +, Dean Rasheed wrote: > > This is the patch allows logging of the explain plan for each query run, as > described here: > > http://archives.postgresql.org/pgsql-performance/2008-01/msg00245.php > > I hope this is useful. > > Dean. Dean, Maybe I missed something obvious here, but how does this patch handle the situation where people have turned on INTEGER_DATETIMES? 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
"Pavel Stehule" <[EMAIL PROTECTED]> writes: >> In such a scheme I think you would put the key in an attribute of the >> language. Either in pg_lang or some configuration location which the >> obfuscate:plperl interpreter knows where to find. >> > > what is advantage? It wouldn't require any core changes. It would be just another PL language to load which can be installed like other ones. This could be a big advantage because it doesn't look like there is a lot of support for putting th obfuscation directly into the core code. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark <[EMAIL PROTECTED]> wrote: > > Someone along the way suggested doing this as a kind of "wrapper" PL language. > So you would have a PL language like "obfuscate:plperl" which would obfuscate > the source code on the way in. Then when you execute a function it would > deobfuscate the source code and then just pass it to the normal plperl. > you can call Deobfuscate proc from any language handler - no problem > In such a scheme I think you would put the key in an attribute of the > language. Either in pg_lang or some configuration location which the > obfuscate:plperl interpreter knows where to find. > what is advantage? > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's On-Demand Production Tuning > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
Someone along the way suggested doing this as a kind of "wrapper" PL language. So you would have a PL language like "obfuscate:plperl" which would obfuscate the source code on the way in. Then when you execute a function it would deobfuscate the source code and then just pass it to the normal plperl. In such a scheme I think you would put the key in an attribute of the language. Either in pg_lang or some configuration location which the obfuscate:plperl interpreter knows where to find. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Andrew Dunstan <[EMAIL PROTECTED]> wrote: > > > Pavel Stehule wrote: > > Hello > > > > this patch define new function flag - OBFUSCATE. With this flag > > encrypted source code is stored to probin column. Password is stored > > in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server > > does (where privileged users can access system tables with source code > > or can use debugger) > > > > ToDo: Dump > > > > Maybe a better TODO would be to do this task in the way that has > previously been suggested: > http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php > > I'm certainly not happy about any proposal to put a password/key in a > GUC var - that strikes me as a major footgun. > why? we cannot ensure bigger real security. Anybody with superuser rights can add modules that show source code of plpgsql procedure, or can run debugger and attach postgres process. p.s. this topic was discussed in http://markmail.org/message/r6jy7m6oryi5owyb Pavel > cheers > > andrew > > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
Pavel Stehule wrote: Hello this patch define new function flag - OBFUSCATE. With this flag encrypted source code is stored to probin column. Password is stored in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server does (where privileged users can access system tables with source code or can use debugger) ToDo: Dump Maybe a better TODO would be to do this task in the way that has previously been suggested: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php I'm certainly not happy about any proposal to put a password/key in a GUC var - that strikes me as a major footgun. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Dave Page <[EMAIL PROTECTED]> wrote: > On Jan 28, 2008 2:26 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > sure, but do you know, Tom dislikes new columns in pg_proc :). > > Tom doesn't seem to like the idea of obfuscation of function code much > either :-) > > > This > > patch is usable sample of one possible solution and doesn't need > > initdb. And there is dependency on pgcrypto :(. But it is simply and > > it does all what is expected. Some customers wonted it. But I am not > > sure if similar patch can be accepted - this is prototype. And when > > I'll have some signals so patch can be commited I'll send final > > version with obfuscate col in pg_proc. Any patch of pg_proc needs two > > hours of work, and any change needs actualization - so lot of maybe > > useless work. > > Yeah, I realise tweaking pg_proc is a large job, and wouldn't expect > you to necessarily do it immediately - I just wanted to throw my > requirements from a tools perspective into the inevitable discussion. with "obfuscate" col in pg_proc source can be little bit more readable and robust - current patch is +/- fast hack - so your requirement is accurate. Regards Pavel . > > Cheers, Dave. > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] WIP: plpgsql source code obfuscation
On Jan 28, 2008 2:26 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > sure, but do you know, Tom dislikes new columns in pg_proc :). Tom doesn't seem to like the idea of obfuscation of function code much either :-) > This > patch is usable sample of one possible solution and doesn't need > initdb. And there is dependency on pgcrypto :(. But it is simply and > it does all what is expected. Some customers wonted it. But I am not > sure if similar patch can be accepted - this is prototype. And when > I'll have some signals so patch can be commited I'll send final > version with obfuscate col in pg_proc. Any patch of pg_proc needs two > hours of work, and any change needs actualization - so lot of maybe > useless work. Yeah, I realise tweaking pg_proc is a large job, and wouldn't expect you to necessarily do it immediately - I just wanted to throw my requirements from a tools perspective into the inevitable discussion. Cheers, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Dave Page <[EMAIL PROTECTED]> wrote: > On Jan 28, 2008 12:51 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > Hello > > > > this patch define new function flag - OBFUSCATE. With this flag > > encrypted source code is stored to probin column. Password is stored > > in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server > > does (where privileged users can access system tables with source code > > or can use debugger). > > > > ToDo: Dump > > Without making any comment of whether or not we should actually do > this, a flag in pg_proc to indicate that the function is obfuscated > would be handy for apps like pgAdmin, rather than assuming a - in > prosrc has that meaning (which may be valid for some interpreters). sure, but do you know, Tom dislikes new columns in pg_proc :). This patch is usable sample of one possible solution and doesn't need initdb. And there is dependency on pgcrypto :(. But it is simply and it does all what is expected. Some customers wonted it. But I am not sure if similar patch can be accepted - this is prototype. And when I'll have some signals so patch can be commited I'll send final version with obfuscate col in pg_proc. Any patch of pg_proc needs two hours of work, and any change needs actualization - so lot of maybe useless work. Pavel > > /D > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
Neil Conway wrote: On Sun, 2008-01-27 at 12:36 -0500, Tom Lane wrote: Both of the above arguments hold water only if we implement compatible *semantics*, not merely syntax, so I find them unconvincing at this stage. How are the semantics of the proposed patch incompatible with the SQL spec or the implementations in other systems? The proposed patch is a *subset* of the functionality in the SQL spec, but it isn't incompatible with it as far as I know (recursive and non-recursive WITH are distinct features). An implementation of WITH that inlines the subquery instead of executing it only once (if appropriate) might not be incompatible with the SQL spec, but it might very well turn out to be incompatible with other major DBMSes from a practical point of view. If people use non-recursive WITH as a replacement for constructs like CREATE TEMPORARY TABLE temp AS SELECT ... ; SELECT ... FROM temp, ... ; , and not merely to increase readability, they won't gain anything from an inlining WITH implementation. This, BTW, is the reason that the C++ standard specifies the runtime complexity (in big-O-notation) for things like vector/list/hash lookups, instead of just specifying the interface. regards, Florian Pflug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] WIP: plpgsql source code obfuscation
On Jan 28, 2008 12:51 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > Hello > > this patch define new function flag - OBFUSCATE. With this flag > encrypted source code is stored to probin column. Password is stored > in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server > does (where privileged users can access system tables with source code > or can use debugger). > > ToDo: Dump Without making any comment of whether or not we should actually do this, a flag in pg_proc to indicate that the function is obfuscated would be handy for apps like pgAdmin, rather than assuming a - in prosrc has that meaning (which may be valid for some interpreters). /D ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Including Snapshot Info with Indexes
On Jan 28, 2008 8:21 AM, Gokulakannan Somasundaram <[EMAIL PROTECTED]> wrote: > I am not seeing my mail getting listed in the archives. So i am just > resending it, in case the above one has got missed. It was sent. Archive processing is delayed. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Including Snapshot Info with Indexes
I am not seeing my mail getting listed in the archives. So i am just resending it, in case the above one has got missed. Thanks, Gokul. On Jan 28, 2008 4:14 PM, Gokulakannan Somasundaram <[EMAIL PROTECTED]> wrote: > > Doh! Can you please send another patch with gram.y as well. Mine is > > missing all of the thick index stuff. > > > > I apologize for the mistake. I am sending the revised patch - generated > against CVS Head. Thanks for the guidance provided. > > This patch also fixes a bug, which appeared in select count(1) from table > where varchar_column like 'xx%' kind of queries. > > In order to find out whether the index will be able to answer all the > where clause conditions, i have put the checks under > expand_indexqual_conditions function. Please get back, if there is any > problem with the approach. > > Waiting for feedback... > > Thanks, > Gokul. > > thickindex.patch.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] WIP: plpgsql source code obfuscation
Hello
this patch define new function flag - OBFUSCATE. With this flag
encrypted source code is stored to probin column. Password is stored
in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server
does (where privileged users can access system tables with source code
or can use debugger).
ToDo: Dump
Sample:
postgres=# show obfuscator_password;
obfuscator_password
---
moje supertajne heslo
(1 row)
postgres=# \x
Expanded display is on.
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+---
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | begin return -1; end;
Description |
postgres=# ALTER FUNCTION fx() obfuscate;
NOTICE: begin return -1; end;
ALTER FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql obfuscate;
CREATE FUNCTION
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres=# select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+
proname| fx
pronamespace | 2200
proowner | 16385
prolang| 16421
procost| 100
prorows| 0
proisagg | f
prosecdef | f
proisstrict| f
proretset | f
provolatile| v
pronargs | 0
prorettype | 23
proargtypes|
proallargtypes |
proargmodes|
proargnames|
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
[EMAIL PROTECTED] ~]$ psql -U bob postgres
Welcome to psql 8.3RC2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=> \x
Expanded display is on.
postgres=> show obfuscator_password;
ERROR: must be superuser to examine "obfuscator_password"
postgres=> select fx();
-[ RECORD 1 ]
fx | -1
postgres=> \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres=> select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+
proname| fx
pronamespace | 2200
proowner | 16385
prolang| 16421
procost| 100
prorows| 0
proisagg | f
prosecdef | f
proisstrict| f
proretset | f
provolatile| v
pronargs | 0
prorettype | 23
proargtypes|
proallargtypes |
proargmodes|
proargnames|
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
*** ./src/backend/catalog/pg_proc.c.orig 2008-01-27 21:29:42.0 +0100
--- ./src/backend/catalog/pg_proc.c 2008-01-28 11:13:31.0 +0100
***
*** 27,32
--- 27,33
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
+ #include "parser/parse_func.h"
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "utils/acl.h"
***
*** 34,39
--- 35,41
#include "utils/lsyscache.h"
#include "utils/syscache.h"
+ extern char *obfuscator_password;
Datum fmgr_internal_validator(PG_FUNCTION_ARGS);
Datum fmgr_c_validator(PG_FUNCTION_ARGS);
***
*** 45,51
static bool match_prosrc_to_literal(const char *prosrc, const char *literal,
int cursorpos, int *newcursorpos);
-
/*
* ProcedureCreate
*
--- 47,52
***
*** 74,80
Datum parameterNames,
Datum proconfig,
float4 procost,
! float4 prorows)
{
Oid retval;
int parameterCount;
--- 75,82
Re: [PATCHES] sinval contention reduction
On Sat, 2008-01-26 at 14:27 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Fri, 2008-01-25 at 19:02 -0500, Tom Lane wrote: > >> This seems large, complex, and untested (I note in particular a > >> guaranteed-to-fail Assert). > > > Yes, its for discussion. How would you describe such a patch in the > > future? I want to be able to differentiate patch status. > > "Completely untested" might be an appropriate description ... That wouldn't be true, because it passes make check. If it were true, I'd have said it. Your responses are inappropriate to a patch clearly marked "for discussion", especially when you privately suggested this topic for me to look at and you also know exactly which system I was going to run a performance test on once I had the patch agreed. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Auto-explain patch
This is the patch allows logging of the explain plan for each query run, as
described here:
http://archives.postgresql.org/pgsql-performance/2008-01/msg00245.php
I hope this is useful.
Dean.
_
Telly addicts unite!
http://www.searchgamesbox.com/tvtown.shtml*** ./doc/src/sgml/config.sgml.orig Sun Jan 27 19:12:28 2008
--- ./doc/src/sgml/config.sgml Sun Jan 27 21:58:32 2008
***
*** 2790,2795
--- 2790,2796
debug_print_rewritten (boolean)
debug_print_plan (boolean)
debug_pretty_print (boolean)
+ debug_explain_plan (boolean)
debug_print_parse configuration parameter
***
*** 2802,2807
--- 2803,2811
debug_pretty_print configuration parameter
+
+debug_explain_plan configuration parameter
+
These parameters enable various debugging output to be emitted.
***
*** 2809,2820
the resulting parse tree, the query rewriter output, or the
execution plan. debug_pretty_print indents
these displays to produce a more readable but much longer
! output format. client_min_messages or
log_min_messages must be
DEBUG1 or lower to actually send this output
to the client or the server log, respectively.
These parameters are off by default.
--- 2813,2837
the resulting parse tree, the query rewriter output, or the
execution plan. debug_pretty_print indents
these displays to produce a more readable but much longer
! output format. debug_explain_plan prints
! the plan for each executed query in the same format as
! EXPLAIN ANALYZE. This includes queries executed from
! within functions. client_min_messages or
log_min_messages must be
DEBUG1 or lower to actually send this output
to the client or the server log, respectively.
These parameters are off by default.
+
+
+
+ The reports produced by debug_explain_plan
+ are produced at a lower level in the database, as each query
+ is executed, including queries executed from functions, so
+ the output may be more verbose that of EXPLAIN ANALYZE
+ and the timings may differ.
+
+
*** ./src/backend/commands/explain.c.orig Tue Jan 1 19:45:50 2008
--- ./src/backend/commands/explain.c Sat Jan 26 11:55:54 2008
***
*** 39,65
/* Hook for plugins to get control in explain_get_index_name() */
explain_get_index_name_hook_type explain_get_index_name_hook = NULL;
-
- typedef struct ExplainState
- {
- /* options */
- bool printNodes; /* do nodeToString() too */
- bool printAnalyze; /* print actual times */
- /* other states */
- PlannedStmt *pstmt; /* top of plan */
- List *rtable; /* range table */
- } ExplainState;
-
static void ExplainOneQuery(Query *query, ExplainStmt *stmt,
const char *queryString,
ParamListInfo params, TupOutputState *tstate);
static void report_triggers(ResultRelInfo *rInfo, bool show_relname,
StringInfo buf);
- static double elapsed_time(instr_time *starttime);
- static void explain_outNode(StringInfo str,
- Plan *plan, PlanState *planstate,
- Plan *outer_plan,
- int indent, ExplainState *es);
static void show_scan_qual(List *qual, const char *qlabel,
int scanrelid, Plan *outer_plan, Plan *inner_plan,
StringInfo str, int indent, ExplainState *es);
--- 39,49
***
*** 402,408
}
/* Compute elapsed time in seconds since given timestamp */
! static double
elapsed_time(instr_time *starttime)
{
instr_time endtime;
--- 386,392
}
/* Compute elapsed time in seconds since given timestamp */
! double
elapsed_time(instr_time *starttime)
{
instr_time endtime;
***
*** 436,442
* side of a join with the current node. This is only interesting for
* deciphering runtime keys of an inner indexscan.
*/
! static void
explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
Plan *outer_plan,
--- 420,426
* side of a join with the current node. This is only interesting for
* deciphering runtime keys of an inner indexscan.
*/
! void
explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
Plan *outer_plan,
*** ./src/backend/executor/execMain.c.orig Tue Jan 1 19:45:50 2008
--- ./src/backend/executor/execMain.c Sat Jan 26 14:34:26 2008
***
*** 39,44
--- 39,45
#include "catalog/heap.h"
#include "catalog/namespace.h"
#include "catalog/toasting.h"
+ #include "commands/explain.h"
#include "commands/tablespace.h"
#include "commands/trigger.h"
#include "executor/execdebug
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
Guillaume Smet wrote: Hi Russell, On Jan 28, 2008 7:27 AM, Russell Smith <[EMAIL PROTECTED]> wrote: Can somebody explain why it's important to load with synchronized_scanning off? do_sql_command(g_conn, "SET synchronized_scanning TO off"); It's the start point of this patch. See this thread [ http://archives.postgresql.org/pgsql-hackers/2008-01/msg00987.php ] for more information Sorry, total brain fade in interpreting the patch. g_conn is our connection to the database, not the command we are dumping. Sorry Russell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
