Re: [HACKERS] Best way to scan on-disk bitmaps
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Plan B would be to remove that restriction and teach btree and gist to cope. While a btree couldn't use a nonconsecutive restriction as part of its where-to-scan logic, I don't see any good reason why it couldn't still perform the test before returning the TID, thus possibly saving a trip to the heap. [ snip ] In this model the columns listed in the gist index are unordered. Any subset of columns can be used to perform an index lookup. Making it more like the bitmap index behaviour you're looking at than the btree index behaviour. I thought some more about this since sending my earlier message. As far as I can recall at the moment, there really isn't anything fundamental that depends on the consecutive-columns rule. The one place where the rubber meets the road is in the index cost estimation functions: if we were to relax that rule, then btcostestimate would have to be taught to include only the consecutive columns when estimating how much of a btree index is going to be touched. And more than that: if you've studied the btree code at all, you realize that that's only an incomplete heuristic anyway. For instance, if the leading key is a xxx, second keys like b yyy and b yyy act completely differently in terms of indexscan cost, but btcostestimate doesn't presently know that. I wonder if we shouldn't migrate the amcostestimate functions into the individual index AMs (which would mean adding a column to pg_am, but so what). btcostestimate could be much less phony about this if it had access to the same infrastructure that _bt_first uses to examine the index clauses. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Server instrumentation for 8.1
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: 12 May 2005 18:04 To: Andreas Pflug Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Server instrumentation for 8.1 Andreas, First, as some other msg states the views will estimate the sizes, dbsize uses actual file sizes. Second, in contrast to CKL, I would *not* use these fancy new system views, because they mean yet another dependency for pgAdmin. grin I like that. You're in favor of including the server tools because you, personally, use them. You're against including the new system views because you, personally, won't use them. Do I misunderstand you, or are you failing to put things in a broader context? Saying he won't use them is not quite the same as arguing against their inclusion for others who might. Regards, Dave. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Server instrumentation for 8.1
Andrew - Supernews wrote: On 2005-05-12, Andreas Pflug [EMAIL PROTECTED] wrote: relpages is updated from the value of RelationGetNumberOfBlocks(rel) which is definitive (it gets the value from smgr which gets it from the physical file sizes); the only inaccuracy is that it is correct only as of the time that the pg_class row was last updated (as done by any VACUUM, any ANALYZE, any CLUSTER or any CREATE INDEX on the table, at minimum). accurate unless vacuum not running. ... which renders it quite useless to find out about *real* disk usage. Interesting for the query planner, not for the admin to know when a vacuum full might be necessary. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Peter Eisentraut wrote: That would cripple a system that many users are perfectly content with now. Well, I wasn't thinking of using a 7-bit encoding always, just as a replacement for the cases where we currently choose SQL_ASCII. Does that sound reasonable? I agree with what (I think) Peter is saying: that would break things for many people for whom the default works fine now. We are currently seeing a whole lot of complaints due to the fact that 8.0 tends to default to Unicode encoding in environments where previous versions defaulted to SQL-ASCII. That says to me that a whole lot of people were getting along just fine in SQL-ASCII, and therefore that moving further away from that behavior is the wrong thing. In particular, there is not any single one of those complainants who would be happier with a 7-bit-only default; if they were using 7-bit-only data, they'd not have noticed a problem anyway. This is exactly the case where JDBC has problems, and the case I'd like to prevent happening in the first place where possible: SQL_ASCII with non-7-bit data. How do you propose that the JDBC driver converts from SQL_ASCII to UTF-16 (the internal Java String representation)? Changing client_encoding does not help. Requiring the JDBC client to specify the right encoding to use is error-prone at best, and impossible at worst (who says that only one encoding has been used?) I'm not suggesting that a 7-bit encoding is necessarily useful to everyone. I'm saying that we should make it a setting that users have to think about and correctly set before they can insert 8-bit data. If they decide they want SQL_ASCII and the associated client_encoding problems, rather than an appropriate encoding the database understands, so be it; but it's on their head, and requires active intervention before the database starts losing encoding information. If SQL_ASCII plus 8-bit data is considered the right thing to do, then I'd consider the ability to change client_encoding on a SQL_ASCII database without an error to be a bug -- you've asked the server to give you (for example) UTF8, but it isn't doing that. In that case, can we get this to generate an error when client_encoding is set instead of producing invalid output? -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Views, views, views: Summary of Arguments
On Thu, May 12, 2005 at 16:59:07 -0700, David Fetter [EMAIL PROTECTED] wrote: A PostgreSQL developer has shown in this very thread that it is extremely easy to screw up a query against those catalogs. Maybe you're better than he is, but that's not a reason to keep something simpler out. You could still study the views to see the correct way to do things, but then not actually use them in the tool. I think this is actually a good reason for developing the views, even if they end up as an add on project. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Can we get patents?
Another difficulty with establishing prior art to prevent others from obtaining patents is that different inventors and different patent agents or attorneys use different terminology to describe the same or similar inventions. To use a simple mechanical example: - Alex develops gadget that includes a ... coupling comprising two pipes aligned end-to-end with a washer in between and a clamp that grasps both pipes ... Alex publishes and/or patents her gadget. - Subsequently, Beth develops a similar gadget that includes ... a link comprising a plurality of hollow cylinders, each adjacent cylinder linked to the next by a circular gasket, the adjacent cylinders being functionally attached to each other ... Are these inventions the same? Is Beth's invention obvious in light of Alex's? They certainly appear to be the same, but without knowing the details of the patents and their prosecution history, there's no way to know. If a patent examiner working on Beth's case relied on word searches - or was just working too quickly - it is likely that Alex's would not show up in the search or not be carefully considered; and Beth's application might grant as a patent. Now consider complex software patents. The same software function can be described in a myriad of ways. It is quite possible for the first inventor to establish prior art that SHOULD block another application for the same or a similar invention, but nevertheless the second application is granted as a patent. If the second inventor tries to enforce the patent it SHOULD be declared invalid in court; but no one wants to be accused of patent infringement, forced to pay a fortune in legal fees, and dragged into Federal Court just to prove a point. One advantage of the first inventor actually patenting the invention, rather than just publishing it, is that then at least the first inventor can threaten to counter sue, and perhaps reach an quick settlement. Other than increase the price of applying for a patent (again) and hiring more and better examiners, I don't know the solution to this problem. Richard Tanzer ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle Style packages on postgres
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 11, 2005 2:22 PM
To: Dave Held
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] writes:
/*
* We check the catalog name and then ignore it.
*/
if (!isValidNamespace(name[0]))
{
if (strcmp(name[0],
get_database_name(MyDatabaseId)) != 0)
ereport(ERROR,
Which more or less proves my point: the syntax is fundamentally
ambiguous.
Not at all. Ambiguity means that there are two equally valid
parses. Under the semantics I proposed, schema names take
precedence. That is, given:
db: foo
schema: bar
schema: foo.bar
The expression foo.bar.rel.col refers to schema foo.bar, and not
to db foo, schema bar. If by fundamentally ambiguous, you mean
there is no a priori reason to choose one set of semantics over
another, I would tend to disagree, but the syntax as I proposed
it is not ambiguous. We use precedence to eliminate otherwise
valid parses all the time.
I suppose people would learn not to use schema names that
match the database they are in, but that doesn't make it a
good idea to have sensible behavior depend on non-overlap of
those names.
There's nothing wrong with using a schema name that matches the
db. The only confusion comes when you put nested elements at
both the db level and schema level having the same names. Since
I presume most people don't specify db names in their queries,
having schemas take precedence makes the most sense to me.
[ thinks for awhile ... ]
OTOH, what if we pretended that two-level-nested schemas ARE
catalogs in the sense that the SQL spec expects? Then we could
get rid of the pro-forma special case here, which isn't ever
likely to do anything more useful than throw an error anyway.
Thus, we'd go back to the pre-7.3 notion that the current
Postgres DB's name isn't part of the SQL naming scheme at all,
and instead handle the spec's syntax requirements by setting up
some conventions that make a schema act like what the spec says
is a catalog.
[...]
I think this would be worse than not having nested schemas at all.
It looks, feels, and smells like a hack. I think there should be
a reasonable depth to schema nesting, but I think it should be
much larger than 2. I think 8 is much more reasonable. One can
argue that nested schemas are nothing more than syntactic sugar,
and this is most definitely true. But as programming language
design teaches us, syntactic sugar is everything. The better our
tools can model our problem spaces, the better they can help us
solve our problems.
A way in which nested schemas are more than syntactic sugar is in
the fact that they can provide a convenient means of additinoal
security management. Rather than twiddling with the privileges on
groups of objects within a schema, objects that should have similar
privileges can be put in the same subschema.
However, returning to the original topic of the thread, nested
schemas are not nearly as interesting to me as the encapsulation
provided by a package-like feature. To be honest, though, what
tantalizes me is not the prospect of a package feature but an
expansion of the Type system.
As a reasonably popular production system, Postgres must necessarily
be conservative. But its roots lay in experimentation, and vestiges
of those roots can still be seen in its structure. Because of its
maturity, Postgres is well positioned to implement some rather
advanced concepts, but perhaps the most radical of them should be
implemented in a fork rather than the main system.
Traditionally, a database is seen as a warehouse of raw data.
ODBMSes position themselves as the next generation by viewing a
database as a collection of persistent, richly structured objects.
Both views have strengths and weaknesses. Postgres takes an
interesting middle ground position within the ORDBMS space. It
is heavily relational with strong support for standard SQL and
numerous query tuning options. But it also features an interesting
number of rather non-relational concepts, like custom operator
definitions, operator classes, user-defined conversions and types.
However, it seems to me that these features are probably very
underutilized.
This is probably due to two reasons: 1) most programmers aren't used
to being able to define custom operators in their favorite programming
language, so the concept isn't familiar enough to them to try it in
their DBMS. 2) The other features which support this aren't designed
or presented in a cohesive manner that impresses the programmer that
this is a compelling and superior way to go about things.
The fact is, operator overloading is a *very* powerful way to
program. In particular, it is one of the key factors in supporting
generic programming in a natural way. People who are unsure
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
On Fri, May 13, 2005 at 01:15:36AM -0400, Tom Lane wrote: We are currently seeing a whole lot of complaints due to the fact that 8.0 tends to default to Unicode encoding in environments where previous versions defaulted to SQL-ASCII. That says to me that a whole lot of people were getting along just fine in SQL-ASCII, and therefore that moving further away from that behavior is the wrong thing. In particular, there is not any single one of those complainants who would be happier with a 7-bit-only default; if they were using 7-bit-only data, they'd not have noticed a problem anyway. I disagree. Of course none of the complainants would be happy with 7-bit encoding, but if they had noticed they had a problem before they had inserted millions of tuples, they could have corrected their configuration right away. The problem is that a single application coming from a single environment is happy with a 8-bit-unchecked encoding, but as soon as they develop a second application using a different environment, which uses a different encoding, they start seeing invalid data pop up. And then they have a problem, because they have to dump all data, recode it, and reimport it. And that's very painful. -- Alvaro Herrera (alvherre[a]surnet.cl) Voy a acabar con todos los humanos / con los humanos yo acabaré voy a acabar con todos / con todos los humanos acabaré (Bender) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
Alvaro Herrera [EMAIL PROTECTED] writes: The problem is that a single application coming from a single environment is happy with a 8-bit-unchecked encoding, but as soon as they develop a second application using a different environment, which uses a different encoding, they start seeing invalid data pop up. [ shrug... ] The evidence at hand says that many people never get to that point. For instance, a particular database may never be accessed through anything except JDBC, and so all the incoming data will be utf8 anyway. My feeling about it is that we already made significant changes in 8.0 --- it won't default to SQL_ASCII unless your locale is C, which to me is a pretty strong indication that you are not very concerned about encodings. We should wait and see what field experience is like with that, rather than insisting on anything as anal-retentive as disallowing 8-bit data in SQL_ASCII. Doing that might have technical purity but I think it will create as many problems as it prevents. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
Alvaro Herrera [EMAIL PROTECTED] writes: In fact I've seen many more people with this problem after 8.0 was released, at least in pgsql-es-ayuda. Which problem exactly? Most of the 8.0 complaints I can recall seemed to come from people who were trying to dump from a SQL_ASCII database and reload into a UTF8 database --- that is, their problems were specifically *caused by* our decision to shift to a more restrictive default. Which is why I'm not eager to make it even more restrictive. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
On Fri, May 13, 2005 at 09:59:27AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: The problem is that a single application coming from a single environment is happy with a 8-bit-unchecked encoding, but as soon as they develop a second application using a different environment, which uses a different encoding, they start seeing invalid data pop up. [ shrug... ] The evidence at hand says that many people never get to that point. For instance, a particular database may never be accessed through anything except JDBC, and so all the incoming data will be utf8 anyway. One thing that's not clear to me is what encoding does people running on Windows get? Is it also determined based on locale, and is it something useful? In fact I've seen many more people with this problem after 8.0 was released, at least in pgsql-es-ayuda. -- Alvaro Herrera (alvherre[a]surnet.cl) La verdad no siempre es bonita, pero el hambre de ella sí ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views: Summary of Arguments
Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: Argument 3: backwards compatibility. Do you remember how tablespaces introduction broke pgAdmin? This argument, at least, is bogus. See my original comments to Josh: it is not credible that these views will be significantly more stable than the underlying catalogs. We don't change the catalogs on whims; we change them because we have to in order to make some significant improvement in functionality. For instance, if this nested-schema idea goes in, the proposed views will have to change, or else become useless for most of the purposes they are being touted for. Ok, I agree. To be honest, I think the usability arguments are specious at best when we have the information schema. In fact, IMO it is a mistake to add a third way of describing the database unless there were plans to make significant changes to the system schema. However, I think PostgreSQL has a fairly serious security problem in that the system catalogs are open to the public. I don't seem to be winning many supporters on this particular point though. I wonder if people are aware just how much information a completely unprivileged account can pull out of the database? Including but not limited to: 1. all source code of user defined functions 2. list of all users on system (but not passwords) etc. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views: Summary of Arguments
Merlin Moncure [EMAIL PROTECTED] writes: However, I think PostgreSQL has a fairly serious security problem in that the system catalogs are open to the public. I don't seem to be winning many supporters on this particular point though. No, you're not, and it's not like we've never heard this argument before. Just upthread there were several complaints about the information_schema being too restrictive to be useful --- I think we'd get a whole lot more of that if we tried to prevent direct examination of the catalogs. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views: Summary of Arguments
Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: However, I think PostgreSQL has a fairly serious security problem in that the system catalogs are open to the public. I don't seem to be winning many supporters on this particular point though. No, you're not, and it's not like we've never heard this argument before. Just upthread there were several complaints about the information_schema being too restrictive to be useful --- I think we'd get a whole lot more of that if we tried to prevent direct examination of the catalogs. There is a case for a facility to harden postgres. My experiments some time ago show you can pretty much hide everything without breaking anything badly if you're careful. I have it on my personal TODO list to complete a hardening script - although I have no idea when I'll get to it. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views: Summary of Arguments
Andrew Dunstan wrote: Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: However, I think PostgreSQL has a fairly serious security problem in that the system catalogs are open to the public. I don't seem to be winning many supporters on this particular point though. No, you're not, and it's not like we've never heard this argument before. Just upthread there were several complaints about the information_schema being too restrictive to be useful --- I think we'd get a whole lot more of that if we tried to prevent direct examination of the catalogs. There is a case for a facility to harden postgres. My experiments some time ago show you can pretty much hide everything without breaking anything badly if you're careful. I have it on my personal TODO list to complete a hardening script - although I have no idea when I'll get to it. :-). I tried it from that angle and could only come up with two modes: 'pgadmin on' and 'pgadmin off' (per user). If you can do better, I'd be thrilled. I also don't want to overblow my own argument...the database can be secured quite effectively if you know what to do. It would just be nice to have a little flexibility. I suppose a hardening script, internal or external to the project is a reasonable way of addressing my security concerns, if not superior. Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views: Summary of Arguments
Merlin Moncure wrote: I tried it from that angle and could only come up with two modes: 'pgadmin on' and 'pgadmin off' (per user). If you can do better, I'd be thrilled. I also don't want to overblow my own argument...the database can be secured quite effectively if you know what to do. It would just be nice to have a little flexibility. I suppose a hardening script, internal or external to the project is a reasonable way of addressing my security concerns, if not superior. My approach was to remove all significant permissions (including on the catalog) from public and regrant them to a pseudopublic group, comprising designated users. The designated users would notice no difference at all, while everyone else would be able to see only what was explicitly granted to them. But there would be lots of testing and thinking to be done before releasing it into the wild :-) cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Views, views, views: Summary of Arguments
Andrew, Merlin, My approach was to remove all significant permissions (including on the catalog) from public and regrant them to a pseudopublic group, comprising designated users. The designated users would notice no difference at all, while everyone else would be able to see only what was explicitly granted to them. But there would be lots of testing and thinking to be done before releasing it into the wild :-) plugDoesn't it seem like a really complete set of system views (based on information_schema or otherwise) would potentially allow securing the pg_catalog?/plug -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views: Summary of Arguments
Josh Berkus josh@agliodbs.com writes: Andrew, Merlin, My approach was to remove all significant permissions (including on the catalog) from public and regrant them to a pseudopublic group, comprising designated users. The designated users would notice no difference at all, while everyone else would be able to see only what was explicitly granted to them. But there would be lots of testing and thinking to be done before releasing it into the wild :-) plugDoesn't it seem like a really complete set of system views (based on information_schema or otherwise) would potentially allow securing the pg_catalog?/plug It'd just move the issues to a different place ... you still have to test and think ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views: Summary of Arguments
Josh Berkus wrote: Andrew, Merlin, My approach was to remove all significant permissions (including on the catalog) from public and regrant them to a pseudopublic group, comprising designated users. The designated users would notice no difference at all, while everyone else would be able to see only what was explicitly granted to them. But there would be lots of testing and thinking to be done before releasing it into the wild :-) plugDoesn't it seem like a really complete set of system views (based on information_schema or otherwise) would potentially allow securing the pg_catalog?/plug Not really, no. It would just be one more thing that my hardening script had to remove permissions from. I still have an open mind about the sysviews project, but the more oversold, hyped and promoted with bogus arguments it gets the more skeptical I become. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views: Summary of Arguments
Andrew, Not really, no. It would just be one more thing that my hardening script had to remove permissions from. Hmmm ... even though the sysviews check users' permissions? That was one of our ideas behind making it safer than the system catalogs. I still have an open mind about the sysviews project, but the more oversold, hyped and promoted with bogus arguments it gets the more skeptical I become. Geez, who poured Drano on your breakfast cereal? Lighten up. ;-) -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Views, views, views: Summary of Arguments
Josh Berkus wrote: Andrew, Not really, no. It would just be one more thing that my hardening script had to remove permissions from. Hmmm ... even though the sysviews check users' permissions? That was one of our ideas behind making it safer than the system catalogs. It might be safer, but that doesn't hit my target at all. I am aiming at a zero-knowledge user, i.e. one who cannot discover anything at all about the db. The idea is that even if subvert can subvert a client and get access to the db the amount of metadata they can discover is as close to zero as possible. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Catalog Security WAS: Views, views, views: Summary of Arguments
Andrew, It might be safer, but that doesn't hit my target at all. I am aiming at a zero-knowledge user, i.e. one who cannot discover anything at all about the db. The idea is that even if subvert can subvert a client and get access to the db the amount of metadata they can discover is as close to zero as possible. Yeah, I can see that. I've personally had this concern about our PG installation on the web server, and as you know about pgFoundry as well, especially since GForge does not use good user security. However, I see 2 seperate cases here: 1) The ISP case, where you want to hide all catalog information from the users except the database owner or superuser. 2) The Enterprise server setting, where you want to allow catalog access (for example, for pgAdmin) restricted to the current user permissions. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Views, views, views: Summary of Arguments
On 2005-05-13, Andrew Dunstan [EMAIL PROTECTED] wrote: Josh Berkus wrote: plugDoesn't it seem like a really complete set of system views (based on information_schema or otherwise) would potentially allow securing the pg_catalog?/plug Not really, no. It would just be one more thing that my hardening script had to remove permissions from. It is specifically intended that you should not have to do that. The precise qualification rules are not yet firmly defined, but if a non-superuser can see anything in the views that does not relate to a permission that was actually granted to them, then it is a bug. (Areas which I expect to need some fine-tuning are: the schema contents view, the rules and triggers views, and possibly some of the constraint info.) I still have an open mind about the sysviews project, but the more oversold, hyped and promoted with bogus arguments it gets the more skeptical I become. I have to say that I find the arguments _against_ it just as bogus. Most significantly, there is a lot of comment on what people _think_ we could do (or not do), and no comment about what we actually _did_. I strongly suggest to anyone thinking of commenting on them that you actually install them and look at them first - while the project is as yet unfinished, and there is a lack of documentation and plenty of rough edges (and quite likely some bugs too), it does actually work and a number of people (some of whom have commented in this thread) have already found it useful. Grab a copy of it from pgfoundry's CVS, go into the sql/ directory and run ./build.sh yourdatabasename (as a superuser, you can add options like -U if needed - the options to build.sh are just passed on to psql). -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Catalog Security WAS: Views, views, views: Summary of Arguments
On 2005-05-13, Josh Berkus josh@agliodbs.com wrote: Andrew, It might be safer, but that doesn't hit my target at all. I am aiming at a zero-knowledge user, i.e. one who cannot discover anything at all about the db. The idea is that even if subvert can subvert a client and get access to the db the amount of metadata they can discover is as close to zero as possible. Yeah, I can see that. I've personally had this concern about our PG installation on the web server, and as you know about pgFoundry as well, especially since GForge does not use good user security. However, I see 2 seperate cases here: 1) The ISP case, where you want to hide all catalog information from the users except the database owner or superuser. I don't believe this is ever feasible in practice, since client interfaces at any level higher than libpq will need to access metadata corresponding to the data they are retrieving. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fix PID file location?
FOlks, The problem seems to be that pg_ctl expects the PID file to be in $PGDATA, but the file actually gets written by the postmaster to the actual data directory. You can work around this by setting external_pid_file, but this then prevents you from using external_pid_file for another purpose. More about this: due to the PID file not being in the right place, pg_ctl stop never reports success: waiting for postmaster to shut down... failed pg_ctl: postmaster does not shut down This appears to be because the duplicate PID in the conf directory is not removed on shutdown. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Catalog Security WAS: Views, views, views: Summary
Andrew - Supernews wrote: 1) The ISP case, where you want to hide all catalog information from the users except the database owner or superuser. I don't believe this is ever feasible in practice, since client interfaces at any level higher than libpq will need to access metadata corresponding to the data they are retrieving. In the general case you might well be right. Following a scheme like I have in mind is not something that would be transparent to the application - it will probably impose some serious limits on the app. The little sample application I did for testing did everything by stored procedure. Anyway, as I said, it's a project for the future. cheers andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Views, views, views: Summary of Arguments
On Thu, May 12, 2005 at 04:03:39PM -0400, Andrew Dunstan wrote: I still don't have any strong views, but I do want the target audience specified - I have seen conflicting messages on that. Power users? Admin Tool builders? Client library builders? These groups don't all have the same needs. While their needs may not be identical, I don't think that means you can't provide views that serve all of their needs, unless there are requirements that are in direct opposition to on another. I can't think of any requirements in your examples that would fall under this catagory. Another use case I wanted to mention is using system views to assist in remote troubleshooting. If you need to know something about someone's database, it will be much easier to ask them to run a query against the system views as opposed to the catalog. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views: Summary of Arguments
Andrew - Supernews wrote: Most significantly, there is a lot of comment on what people _think_ we could do (or not do), and no comment about what we actually _did_. I strongly suggest to anyone thinking of commenting on them that you actually install them and look at them first - while the project is as yet unfinished, and there is a lack of documentation and plenty of rough edges (and quite likely some bugs too), it does actually work and a number of people (some of whom have commented in this thread) have already found it useful. Grab a copy of it from pgfoundry's CVS, go into the sql/ directory and run ./build.sh yourdatabasename (as a superuser, you can add options like -U if needed - the options to build.sh are just passed on to psql). I did look over them. Maybe I'd get the whole thing better if I had a brief description of each view rather that having to infer the purpose for myself from an sql statement of a list of fields. If you're concerned to make a case I think that would be useful. If that's been published and I missed it I apologise. cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views: Summary of Arguments
I did look over them. Maybe I'd get the whole thing better if I had a brief description of each view rather that having to infer the purpose for myself from an sql statement of a list of fields. If you're concerned to make a case I think that would be useful. If that's been published and I missed it I apologise. No, you're right, we need that. I'll work on it over the weekend. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
Tom Lane wrote: We should wait and see what field experience is like with that, rather than insisting on anything as anal-retentive as disallowing 8-bit data in SQL_ASCII. I didn't suggest changing the behaviour of SQL_ASCII.. -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Do you have any objections on contributing your improvements of the PostgreSQL core back into the product?
I am very, very sure that anything that makes PostgreSQL stronger will be deeply appreciated by the PostgreSQL team. To the PG team, see the following: http://dbgroup.ncsu.edu/ http://www4.ncsu.edu/~rychirko/ Especially: http://research.csc.ncsu.edu/selftune/ This is a fabulous project with smashing results, already done against PostgreSQL. Could someone on the official PostgreSQL team raise their hand, please, and say: We are interested in folding in this valuable research study back into the core of PostgreSQL, thus making it much stronger and more capable than it is now. -Original Message- From: Rada Chirkova [mailto:[EMAIL PROTECTED] Sent: Friday, May 13, 2005 2:55 PM To: Rada Chirkova Cc: Dann Corbit Subject: Re: Do you have any objections on contributing your improvements of the PostgreSQL core back into the product? Dear Dann Corbit, could you tell me whether you have heard back from PostgreSQL committers on whether they like my enhancements of PostgreSQL. Best regards, Rada Rada Chirkova wrote: Thanks! I will work over the summer on getting good students to do this. Should I get in touch with some PostgreSQL developers other than you to coordinate the effort. Best regards, Rada Dann Corbit wrote: I think that a cooperative effort between the core team of PostgreSQL developers and your graduate students is definitely the most logical approach. The PostgreSQL engine has also been updated quite a bit now with version 8, and so certainly, some changes would be needed. -Original Message- From: Rada Chirkova [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 5:53 AM To: Dann Corbit; Rada Chirkova Subject: Re: Do you have any objections on contributing your improvements of the PostgreSQL core back into the product? Dear Dann Corbit, thanks for your response. I would be really delighted to contribute to the PostgreSQL core. At the same time, I have doubts about the quality of the implementation by my students. Do you think it would make sense to have good students reimplement the functionalities, perhaps with some guidance from regular PostgreSQL contributors? Best regards, Rada Dann Corbit wrote: My name is Dann Corbit. I work for a company called CONNX Solutions Inc. (which is a database company -- we write middleware like ODBC/OLEDB/JDBC/.NET drivers and things of that nature). Here is our web site: http://www.connx.com/ We also happen to use PostgreSQL in some of our projects, and I have had some minor involvement in the PostgreSQL projects. For instance, the PostgreSQL team used some code we wrote in creating a native version of PostgreSQL 7.1.3 for creating the native windows version of PostgreSQL 8.0. I read your paper on the network and downloaded the source code that you created. It looks like a great enhancement to the PostgreSQL database system. For that reason, I was wondering if you would object to posting your changes back into the PostgreSQL 8 core so that the new functionality is available to everyone. If you go and look on the PostgreSQL list server, you will see that I post there fairly often. -Original Message- From: Rada Chirkova [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 04, 2005 4:10 PM To: Dann Corbit Subject: Re: Do you have any objections on contributing your improvements of the PostgreSQL core back into the product? Hi, could you tell me about yourself - your message was kind of brief ;) Best regards, Rada Dann Corbit wrote: It seems to me that your enhancements are extremely useful. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PREPARE TRANSACTION and friends, separate node?
Hackers, I'm looking at the grammar modifications for the 2PC patch, and I am wondering if we should leave PREPARE TRANSACTION in the same parser/ UtilityStmt node TransactionStmt or should use a different parser node, say PrepTransactionStmt. Using a different parser node seems to leave a gram.c file that's 1.5 kb smaller. However it means we need to create new copyfuncs and equalfuncs functions; and to double a couple of checks: (IsA(TransactionStmt, node) || IsA(PrepTransactionStmt, node)) However it doesn't seem to me this is too big a drawback. What do people think? Is this important at all? -- Alvaro Herrera (alvherre[a]surnet.cl) On the other flipper, one wrong move and we're Fatal Exceptions (T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
On Fri, May 13, 2005 at 10:22:06AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: In fact I've seen many more people with this problem after 8.0 was released, at least in pgsql-es-ayuda. Which problem exactly? Most of the 8.0 complaints I can recall seemed to come from people who were trying to dump from a SQL_ASCII database and reload into a UTF8 database --- that is, their problems were specifically *caused by* our decision to shift to a more restrictive default. Which is why I'm not eager to make it even more restrictive. Well, of all kinds really. There are some of those. Also I've seen several questions from people using, say, a Python environment and then they have to add a Windows/ODBC application. The Python apps have been using SQL_ASCII all along, but ODBC apparently wants UTF8 or some sort of recode. So non-7-bit chars display funny. Anyway now that I look at them ISTM that most complaints actually come from misunderstanding of the whole encoding issue. They just create a database and an app and start coding and throwing data. At first it all seems fine. Later they find out that the client is using, say, win-1250, and has been using client_encoding latin1; so it has been wrong all around and they only find out when a new, different app shows something strange. So maybe it's not about 7 bits or not, it's just lack of knowledge. But I've certainly seen much more problems reported after 8.0 was released. -- Alvaro Herrera (alvherre[a]surnet.cl) Pensar que el espectro que vemos es ilusorio no lo despoja de espanto, sólo le suma el nuevo terror de la locura (Perelandra, CSLewis) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] patches for items from TODO list
Hello all,
Thank you to all who replied for suggestions and help. Enclosed please find
code changes for the following items:
- Allow COPY to understand \x as a hex byte, and
- Add XML output to COPY
The changes include implementation of the features as well as modification
of the copy regression test.
After a careful consideration we decided to
- put XML implementation in the backend and
- use XML format described below, with justification of our decision.
The XML schema used by the COPY TO command was designed for ease of use and
to avoid the problem of column names appearing in XML element names.
XML doesn't allow spaces and punctuation in element names but Postgres does
allow these characters in column names; therefore, a direct mapping would be
problematic.
The solution selected places the column names into attribute fields where
any special characters they contain can be properly escaped using XML
entities. An additional attribute is used to distinguish null fields from
empty ones.
The example below is taken from the test suite. It demonstrates some basic
XML escaping in row 2. Row 3 demonstrates the difference between an empty
string (in col2) and a null string (in col3). If a field is null it will
always be empty but a field which is empty may or may not be null.
Always check the value of the 'null' attribute to be sure when a field is
truly null.
?xml version='1.0'?
table
row
col name='col1' null='n'Jackson, Sam/col
col name='col2' null='n'\h/col
/row
row
col name='col1' null='n'It is quot;perfectquot;./col
col name='col2' null='n'#09;/col
/row
row
col name='col1' null='n'/col
col name='col2' null='y'/col
/row
/table
Please let us know if about any concerns, objections the proposed change may
cause.
Best regards,
Jason Lucas, Sergey Ten
SourceLabs
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 11, 2005 7:11 PM
To: Sergey Ten
Cc: pgsql-hackers@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [HACKERS] patches for items from TODO list
Sergey Ten wrote:
Hello all,
We would like to contribute to the Postgresql community by implementing
the following items from the TODO list
(http://developer.postgresql.org/todo.php):
. Allow COPY to understand \x as a hex byte . Allow COPY to optionally
include column headings in the first line . Add XML output to COPY
The changes are straightforward and include implementation of the
features as well as modification of the regression tests and
documentation.
Before sending a diff file with the changes, we would like to know if
these features have been already implemented.
Please check the web site version. Someone has already implemented
Allow COPY to optionally include column headings in the first line.
As far as XML, there has been discussion on where that should be done?
In the backend, libpq, or psql. It will need discussion on hackers. I
assume you have read the developer's FAQ too.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania
19073
Index: src/backend/commands/copy.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.244
diff -u -r1.244 copy.c
--- src/backend/commands/copy.c 7 May 2005 02:22:46 - 1.244
+++ src/backend/commands/copy.c 13 May 2005 22:21:00 -
@@ -84,6 +84,16 @@
EOL_CRNL
} EolType;
+/*
+ * Represents the format of the file to be read or written
+ */
+typedef enum CopyFmt
+{
+ FMT_TXT,
+ FMT_BIN,
+ FMT_CSV,
+ FMT_XML
+} CopyFmt;
static const char BinarySignature[11] = PGCOPY\n\377\r\n\0;
@@ -129,14 +139,14 @@
static bool line_buf_converted;
/* non-export function prototypes */
-static void DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
-char *delim, char *null_print, bool csv_mode, char *quote,
+static void DoCopyTo(Relation rel, List *attnumlist, CopyFmt fmt, bool oids,
+char *delim, char *null_print, char *quote,
char *escape, List *force_quote_atts, bool header_line, bool
fe_copy);
-static void CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
- char *delim, char *null_print, bool csv_mode, char *quote, char *escape,
+static void CopyTo(Relation rel, List *attnumlist, CopyFmt fmt, bool oids,
+ char *delim, char *null_print, char *quote, char *escape,
List *force_quote_atts, bool header_line);
-static void CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
- char *delim, char *null_print, bool csv_mode, char *quote, char
Re: [HACKERS] Do you have any objections on contributing your improvements of the PostgreSQL core back into the product?
What Rada Chirkova is looking for is an endorsement of the project. The work has already been completed and studied in detail but on PG 7.3.4 rather than using the current code base. The plan is to redo it with grad students and careful supervision to ensure the highest quality. She wants to know if the PG group would want this work to be done. I am sure that she expects code reviews and beta tests and all the other standard fare. -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Friday, May 13, 2005 4:03 PM To: Dann Corbit Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Do you have any objections on contributing your improvements of the PostgreSQL core back into the product? Dann, Could someone on the official PostgreSQL team raise their hand, please, and say: We are interested in folding in this valuable research study back into the core of PostgreSQL, thus making it much stronger and more capable than it is now. As much as I would love to do just that, you know that's not the way it works. All patches ... including mine, Bruce's, Jan's, and even sometimes Tom's, have to be evaluated for usefulness, clean code, bugs, etc. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Do you have any objections on contributing your improvements of the PostgreSQL core back into the product?
Dann, Could someone on the official PostgreSQL team raise their hand, please, and say: We are interested in folding in this valuable research study back into the core of PostgreSQL, thus making it much stronger and more capable than it is now. As much as I would love to do just that, you know that's not the way it works. All patches ... including mine, Bruce's, Jan's, and even sometimes Tom's, have to be evaluated for usefulness, clean code, bugs, etc. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Do you have any objections on contributing your improvements of the PostgreSQL core back into the product?
Dann, What Rada Chirkova is looking for is an endorsement of the project. Well, let me read up on the research -- it's more than a little unclear just from the abstract what the code is supposed to accomplish. You just posted it a few days ago, and I really haven't had time to follow up. We may very well want it for Bizgres as well. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq lo_open errors when first action in connection
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The problem is that LO descriptors are only valid for the duration of the transaction. Thanks, that's it all right. I forgot to update the lo_ stuff when we switched the autocommit mojo around a while back. I think you should make the func() method open a transaction, just like a query does. Yep, that's the basic solution - for lo_creat. Thanks again. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200505130807 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFChJjdvJuQZxSWSsgRAgh9AKDBRlAjbDt4zcfqnvTuEq9Z4cBfygCgwes6 hJ0De8jAcMYKOrSUH3uGoZk= =/ooS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Catalog Security WAS: Views, views, views: Summary
On Sat, 14 May 2005 04:34 am, Andrew Dunstan wrote: Andrew - Supernews wrote: 1) The ISP case, where you want to hide all catalog information from the users except the database owner or superuser. I don't believe this is ever feasible in practice, since client interfaces at any level higher than libpq will need to access metadata corresponding to the data they are retrieving. In the general case you might well be right. Following a scheme like I have in mind is not something that would be transparent to the application - it will probably impose some serious limits on the app. The little sample application I did for testing did everything by stored procedure. Anyway, as I said, it's a project for the future. From a general user point of view, I do not know the system catalogs very well. I am very unsure of what level of information is available to every user on the system. - Which parts of other databases can be seen by users? - What is the best method to restrict connections to db's people don't have permissions to. - Is there some restrictions you can place on tables people don't have access too. Otherwise they can see all the columns and table info. These are just some of the questions I have, I'm not sure where to get answers, searching the archives may help, but it's definitely not a final answer. Especially since this stuff would be a moving target with each version change of PostgreSQL. Tom mentioned that he had not had these security concerns raised before. From my point of view I just have no idea about the level of information offered to any given user and am scared to run PostgreSQL in an ISP shared environment because of it. I am sure I can secure people from connecting to a db by refusing them access in pg_hba.conf. But I'm unsure of exactly what that buys me, and what is doesn't. A hardening script would be helpful, but some clear information on what is also available to the average user would be good too. I know I should probably step up to do this and don't have time at the moment. I'm sure if I did, I would also miss a great number of things. Regards Russell Smith ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Catalog Security WAS: Views, views, views: Summary
On Sat, May 14, 2005 at 12:25:01PM +1000, Russell Smith wrote: - Which parts of other databases can be seen by users? The name, username of the owner, etc. No table names, for example. The user list is also visible to everyone, across databases. - What is the best method to restrict connections to db's people don't have permissions to. pg_hba.conf. Apparently some people run separate postmasters for each, and there are reasons to do that (shared xlogs can be a hassle, for example; if something goes ill in another database, you may be screwed too. Or shared oldest Xid.) Tom mentioned that he had not had these security concerns raised before. From my point of view I just have no idea about the level of information offered to any given user and am scared to run PostgreSQL in an ISP shared environment because of it. Actually I've seen complaints before. Within a DB you can see the names and columns of all tables, views, etc. Some people want to hide that, and I see the point. In a shared environment you can probably get away with giving separate databases to each, and you have perfect isolation, so no worries there. But if you want to partially share data in a useful manner (say use foreign keys from one DB to another) you have to use schemas, and by doing so you are automatically granting visibility to lots of info about your database. (Not the data itself though.) -- Alvaro Herrera (alvherre[a]surnet.cl) Hoy es el primer día del resto de mi vida ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Catalog Security WAS: Views, views, views: Summary
* Russell Smith ([EMAIL PROTECTED]) wrote: Tom mentioned that he had not had these security concerns raised before. From my point of view I just have no idea about the level of information offered to any given user and am scared to run PostgreSQL in an ISP shared environment because of it. I am sure I can secure people from connecting to a db by refusing them access in pg_hba.conf. But I'm unsure of exactly what that buys me, and what is doesn't. It's certainly also a concern of mine that any given use can see every table in the database. I see that as a definite problem and just assumed it was already on the radar and something that was planned to be fixed. It astounds me that the claim is that such security is impossible. It bothers me a great deal that I can't control very easily what a given user can see when they connect over ODBC or via phppgadmin in terms of schemas, tables and columns. I fixed this in application code in phppgadmin but that's clearly insufficient since it doesn't do anything for the other access methods. I'd certainly really like to see this fixed. It would raise my comfort level a great deal wrt allowing people to use ODBC and psql directly. Thanks, Stephen signature.asc Description: Digital signature
