[HACKERS] the un-vacuumable table
I found this error message in my log files repeatedly: Error: failed to re-find parent key in ledgerdetail_2008_03_idx2 for deletion target page 64767 I though hmm, that index looks broken. I'd better re-create it. So, I dropped the index and then tried to create a new one to replace it. Which completely locked up the backend that was running the CREATE TABLE. I ran truss against the backend in question and it didn't register anything (except signals 2 and 15 when I tried to cancel the query and kill the backend respectively). I eventually had to restart the database to get the CREATE INDEX process to go away (well, to release that big nasty lock). I then tried to do a VACUUM FULL, but it didn't complete after more than 2 hours. I cancelled that and tried a CLUSTER in the hopes that it might work a little faster. It did the exact same thing as the create index command: completely locked up the backend. So, I'm wondering what if anything I can do to get that table cleaned up. Running 8.1.11 on FreeBSD 6.2. Anyway, the current plan is to drop the table and reload it from backup. I'm posting here in case there's interest in gathering some forensic data or a clever suggetion about how I can recover this situation or even some ideas about what's causing it. Andrew
[HACKERS] CVS Head psql bug?
Hi, I'm getting following error while envoking psql: $ psql psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC This is Linux glibc 2.3.4 and readline 5.1. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal for smaller indexes on index-ordered tables
Jeffrey Baker wrote: The way I read it, the current btree index stores the index value and the TID of every tuple having that value. When you have a table with three columns, you index one of them and you get an index which is practically as large as the table itself. Supposing the table is generally or strictly ordered by the column to be indexed, it would be more compact if the index stored ranges of tuples. Instead of storing the TID of every tuple with that value, the index would store a first and last TID, between which all tuples have the value. Search the archives for the Grouped Index Tuples, also known as Clustered Indexes patch I worked on in spring 2007. It did almost exactly that. It didn't make it into 8.3 for various reasons: the patch was quite invasive, the design and performance characteristics were not well-understood by fellow hackers, and there was not that much interest in it back then. I'm not working on it or planning to work on it for now, but if you're interested, the patch is still out there. It requires a lot of work, but the design is still viable. I'm certainly willing to help with it if someone wants to pick it up. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
Andrew Hammond wrote: I found this error message in my log files repeatedly: Error: failed to re-find parent key in ledgerdetail_2008_03_idx2 for deletion target page 64767 I though hmm, that index looks broken. I'd better re-create it. So, I dropped the index and then tried to create a new one to replace it. Which completely locked up the backend that was running the CREATE TABLE. I ran truss against the backend in question and it didn't register anything (except signals 2 and 15 when I tried to cancel the query and kill the backend respectively). I eventually had to restart the database to get the CREATE INDEX process to go away (well, to release that big nasty lock). What kind of an index is it? Does SELECT COUNT(*) from table work? Anyway, the current plan is to drop the table and reload it from backup. I'm posting here in case there's interest in gathering some forensic data or a clever suggetion about how I can recover this situation or even some ideas about what's causing it. Yes, please take a filesystem-level backup right away to retain the evidence. Could you connect to the hung backend with gdb and get a stacktrace? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Extended security/restriction to any role with login access
Hello ! I'm trying to use postgresql in an application that by design will give access to users to a subset of the database. For example for customers access to products_view (wich will only show public offers), orders (only their own orders). I'll provide an application as user interface for the data. For that I'll give for each of then a role in the database that will belong to a group role customers_group. The customers_group only has access to the views/functions that I'll specify. Till here no problem postgresql do that pretty well. My concern is once I give login access to any user, even without grant him/her any access to any database, he/she can using an application like pgadmin3 view all databases/roles/functions/table-definitions on my server. And that was not my intention. Removing all from public doesn't work : revoke all on schema public from public; What I think would be the server behavior when I create a role with login access an say that I only grant access to one view like this: create role oneuser login; grant select on somedatabase.someview to oneuser; In that case when the user login the only thing he/she sees is the view database.someview, even when they use pgadmin3 to connect. Actually he/she can see with pgadmin3 : all databases, all roles and it's right access, all tables on every database (no access to data), all functions, all triggers, all table definitions. The above isn't the intention to a user with a restrict view of the database. Can I achieve it actually, if not how hard could be to implement that in the official release ? Thanks in advance for any feedback/ideas !
[HACKERS] Extended security/restriction to any role with login access
Hello ! I'm trying to use postgresql in an application that by design will give access to users to a subset of the database. For example for customers access to products_view (wich will only show public offers), orders (only their own orders). I'll provide an application as user interface for the data. For that I'll give for each of then a role in the database that will belong to a group role customers_group. The customers_group only has access to the views/functions that I'll specify. Till here no problem postgresql do that pretty well. My concern is once I give login access to any user, even without grant him/her any access to any database, he/she can using an application like pgadmin3 view all databases/roles/functions/table-definitions on my server. And that was not my intention. Removing all from public doesn't work : revoke all on schema public from public; What I think would be the server behavior when I create a role with login access an say that I only grant access to one view like this: create role oneuser login; grant select on somedatabase.someview to oneuser; In that case when the user login the only thing he/she sees is the view database.someview, even when they use pgadmin3 to connect. Actually he/she can see with pgadmin3 : all databases, all roles and it's right access, all tables on every database (no access to data), all functions, all triggers, all table definitions. The above isn't the intention to a user with a restrict view of the database. Can I achieve it actually, if not how hard could be to implement that in the official release ? Thanks in advance for any feedback/ideas !
[HACKERS] Planner creating ineffective plans on LEFT OUTER joins
Hi, After pondering on the problem for quite some time and discussing it on IRC with RhodiumToad I thought the most sensible thing is to post the problem here (as RhodiumToad suggested as well). The original (although already quite reduced) problematic query and the related plan: http://anarazel.de/postgres/orig_query.sql http://anarazel.de/postgres/orig_query.plan I.e. it builds the right side of the LEFT JOIN for all elements it could possibly contain and not only for the ones which exist on the left side. (Database is freshly VACUUM ANALYZE'd) Perhaps I expect to much from the planner here? With this query this is not much of a problem, but the plan is the same if the inner part of the query yields some million rows (and possibly is not only In order to make testing easier I tried to reproduce the problem (with help of RhodiumToad): http://anarazel.de/postgres/create_testtables.sql Testquery: SELECT * FROM ab LEFT OUTER JOIN ( bc JOIN cd ON bc.c = cd.d ) ON ab.b = bc.b WHERE ab.a = 2 As ab.a = 2 occurs only once in ab one would expect that it just does an index scan on bc for ab.b = bc.b. Unfortunately it builds the complete right side of the join first, and then selects the one element it needs... Queryplan: http://anarazel.de/postgres/testtable_query1.plan If there is no relatively easy fix for this, any idea how to work around that problem? Thanks, Andres Freund PS: Tested with 8.3.3 and 8.2.7. The problem was the same since 8.0 though (I didn't test earlier versions ) signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] the un-vacuumable table
On Wed, Jun 25, 2008 at 2:58 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Andrew Hammond wrote: I found this error message in my log files repeatedly: Error: failed to re-find parent key in ledgerdetail_2008_03_idx2 for deletion target page 64767 I though hmm, that index looks broken. I'd better re-create it. So, I dropped the index and then tried to create a new one to replace it. Which completely locked up the backend that was running the CREATE TABLE. I ran truss against the backend in question and it didn't register anything (except signals 2 and 15 when I tried to cancel the query and kill the backend respectively). I eventually had to restart the database to get the CREATE INDEX process to go away (well, to release that big nasty lock). What kind of an index is it? Does SELECT COUNT(*) from table work? After the restart I did a count(*) and it worked. A little under 13m rows. So, sequential scans seem to work. posting here in case there's interest in gathering some forensic data or a clever suggetion about how I can recover this situation or even some ideas about what's causing it. Anyway, the current plan is to drop the table and reload it from backup. I'm Yes, please take a filesystem-level backup right away to retain the evidence. Well, I've already burned our downtime allowance for this month, but we do a regular PITR type backup which hopefully will be sufficient to replicate the problem. Could you connect to the hung backend with gdb and get a stacktrace? The backend is no longer hung (two restarts later). I'll try to reproduce this problem on my workstation (same binary, same OS, libraries etc) using the PITR dump. Andrew
Re: [HACKERS] Dept of ugly hacks: eliminating padding space in system indexes
Mark, Not that I disagree with your change, but 5 Mbytes in 4 Gbytes of RAM for my main PostgreSQL system that I manage seems like a drop in the bucket. Even if 40% of pg_class_relname and pg_proc_proname indices was saved - we're talking about 154 Kbytes saved on both those indices combined. Minor? Major? I bet I wouldn't notice unless my database requirements used up all RAM, and even then I'm suspecting it wouldn't matter except for border line cases (like all pages required for everything else happened to equal 4 Gbytes near exactly). Again, I think the best way to test this would be to create an installation with more than 100,000 tables views. That's not hypothetical; I've encountered it already twice in production users. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Creating a VIEW with a POINT column
I have a VIEW that consists of two tables, of which contain a POINT column. When trying to select from the view I get an error... ERROR: could not identify an ordering operator for type point HINT: Use an explicit ordering operator or modify the query. Any suggestions??? -Nick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Nick wrote: I have a VIEW that consists of two tables, of which contain a POINT column. When trying to select from the view I get an error... ERROR: could not identify an ordering operator for type point HINT: Use an explicit ordering operator or modify the query. Any suggestions??? -Nick Does your view per chance do ORDER BY point_column ? You should then do as told and use an explicit ordering operator. See http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-ORDERBY. To get better feedback you really should post your view definition (and the definitions of those two underlying tables). Cheers, Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Latest on CITEXT 2.0
Howdy, I just wanted to report the latest on my pet project: implementing a new case-insensitive text type, citext, to be locale-aware and to build and run on PostgreSQL 8.3. I'm not much of a C programmer (this is only the second time I've written *anything* in C), so I also have a few questions about my code, best practices, coverage, etc. You can grab the latest here: https://svn.kineticode.com/citext/trunk/ BTW, the tests in sql/citext.sql use the pgtap.sql file to run TAP regression tests. So you can run them using `make installcheck` or `make test`. The latter requires that pg_prove be installed; you can get it here: https://svn.kineticode.com/pgtap/trunk/ Anyway, I think I've got it pretty close to done. The tests cover a lot of stuff -- nearly everything I could figure out, anyway. But there are a few gaps. As a result, I'd appreciate a little help with these questions, all in the name of making this a solid data type suitable for use on production systems: * There seem to still be some implicit CASTS to text that I'd like to duplicate. For example, select '192.168.1.2'::cidr::text;` works, but `select '192.168.1.2'::cidr::citext;` does not. Where can I find the C functions that do these casts for TEXT so that I can put them to work for citext, too? The internal cast functions used in the old citext distribution don't exist at all on 8.3. * There are casts from text that I'd also like to harness for use by citext, like `cidr(text)`. Where can I find these C functions as well? (The upshot of this and the previous points is that I'd like citext to be as compatible with TEXT as possible, and I just need to figure out how to fill in the gaps in that compatibility.) * Regular expression and LIKE comparisons using the the operators properly work case-insensitively, but functions like replace() and regexp_replace() do not. Should they? and if so, how can I make them do so? * The tests assume that LC_COLLATE is set to en_US.UTF-8. Does that work well for standard PostgreSQL regression tests? How are locale- sensitive tests run in core regression tests? * As for my C programming, well, what's broken? I'm especially concerned that I pfree variables appropriately, but I'm not at all clear on what needs to be freed. Martijn mentioned before that btree comparison functions free memory, but I'm such a C n00b that I don't know what that actually means for my implementation. I'd actually appreciate a bit of pedantry here. :-) * Am I in fact getting an appropriate nul-terminated string in my cilower() function using this code? char * str = DatumGetCString( DirectFunctionCall1( textout, PointerGetDatum( arg ) ) ); Those are all the questions I had about my implementation. I'd like to get this thing done and released soon, so that I can be done with this particular Yak and get back to what I'm *supposed* to be doing with my time. BTW, would there be any interest in this code going into contrib/ in the distribution? I think that, if we can ensure that it works just like LOWER() = LOWER(), but without requiring that code, then it would be a great type to point people to to use instead of that SQL hack (with all the usual caveats about it being locale-sensitive and not canonically case-insensitive in the Unicode sense). If so, I'd be happy to make whatever changes are necessary to make it fit in with the coding and organization standards of the core and to submit it. But please, don't expect a civarchar type from me anytime soon. ;-) Many thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GIT repo broken
Hi, I noticed the other day that the branches in the GIT postgresql.git repository do not contain any patch after it was released. For example, in http://git.postgresql.org/?p=postgresql.git;a=shortlog;h=REL8_3_STABLE the last commit is on 2008-02-12. For example this commit is not there: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/nls-global.mk?rev=1.14 What's going on here? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: I'm getting following error while envoking psql: $ psql psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC I believe this is the typical symptom of failing to link to the correct variant of curses/termcap needed by readline. We haven't changed the code around that in awhile --- is this a different platform than you've used before? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Nope, im not ordering by the POINT column. Heres an example...
CREATE TABLE table1 (
title character varying,
sorter integer,
xy point
);
CREATE TABLE table2 (
title character varying,
sorter integer,
xy point
);
INSERT INTO table1 VALUES ('one', 1, '(1,1)');
INSERT INTO table1 VALUES ('two', 2, '(2,2)');
INSERT INTO table1 VALUES ('three', 3, '(3,3)');
INSERT INTO table2 VALUES ('four', 4, '(4,4)');
INSERT INTO table2 VALUES ('five', 5, '(5,5)');
INSERT INTO table2 VALUES ('six', 6, '(6,6)');
CREATE VIEW myview AS
SELECT table1.title, table1.sorter, table1.xy FROM table1 UNION
SELECT table2.title, table2.sorter, table2.xy FROM table2;
SELECT title FROM myview ORDER BY sorter;
ERROR: could not identify an ordering operator for type point
HINT: Use an explicit ordering operator or modify the query.
In statement:
SELECT title FROM myview ORDER BY sorter
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii ishii(at)postgresql(dot)org writes: I'm getting following error while envoking psql: $ psql psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC I believe this is the typical symptom of failing to link to the correct variant of curses/termcap needed by readline. We haven't changed the code around that in awhile --- is this a different platform than you've used before? regards, tom lane Sorry for not follow the thread and posting from different account than before. I'm currently having trouble with [EMAIL PROTECTED] account. Marc is kindly working for me and will be fixed soon but... No the plaform is not changed. I compare the link command for psql 8.3 and current. 8.3: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o large_obj.o print.o describe.o psqlscan.o tab-complete.o mbprint.o dumputils.o ../../../src/backend/parser/keywords.o -L../../../src/port -lpgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,-rpath,'/usr/local/pgsql/lib' -lpgport -lz -lreadline -lncurses -lcrypt -ldl -lm -o psql CVS Head: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o large_obj.o print.o describe.o psqlscan.o tab-complete.o mbprint.o dumputils.o ../../../src/backend/parser/keywords.o -L../../../src/port -lpgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/src/pgsql/current/lib' -lpgport -lz -lreadline -lncurses -lcrypt -ldl -lm -o psql So the difference seems: --as-needed -Wl, is added to CVS Head. Actually if I remove the option and link psql, then everything is fine. Any idea? -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Nick wrote: Nope, im not ordering by the POINT column. Heres an example... CREATE VIEW myview AS SELECT table1.title, table1.sorter, table1.xy FROM table1 UNION SELECT table2.title, table2.sorter, table2.xy FROM table2; Hmm, the error seems to be coming from UNION. It's because Postgres implements UNION by sorting both result sets merging them together. Sample queries that also fail: SELECT * FROM myview; SELECT DISTINCT * FROM table1; SELECT title, sorter, xy FROM table1 GROUP BY title, sorter, xy; All three try to sort the table first, and as there's no comparision operator for the POINT datatype, they fail. Which seems to be wrong - if there is no comparision operator, you still can do DISTINCT, only less efficiently. The quick solution I'd propose is replacing UNION with UNION ALL. This will not throw away duplicate entries present in both table1 and table2, but if you can live with that, it will work. Remeber though, it changes the semantic of that view, so think carefuly before doing that. I guess some senior hacker should confirm, but I believe this is a bug. Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: So the difference seems: --as-needed -Wl, is added to CVS Head. There is code in configure that's supposed to check whether or not that breaks readline. Would you look at the portion of config.log where it tests that, and see why it failed to notice a problem? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: So the difference seems: --as-needed -Wl, is added to CVS Head. There is code in configure that's supposed to check whether or not that breaks readline. Would you look at the portion of config.log where it tests that, and see why it failed to notice a problem? It seems configure only checks whether linking was successful. I think it should check whether ./conftest was successful. configure:25642: checking if gcc supports -Wl,--as-needed configure:25674: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -D_GNU_SOURCE-Wl,--as-needed conftest.c -lz -lreadline -lncurses -lcrypt -ldl -lm 5 configure:25680: $? = 0 configure:25686: result: yes configure:25765: using CFLAGS=-O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing configure:25767: using CPPFLAGS= -D_GNU_SOURCE configure:25769: using LDFLAGS= -Wl,--as-needed configure:25911: creating ./config.status -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes: All three try to sort the table first, and as there's no comparision operator for the POINT datatype, they fail. Which seems to be wrong - if there is no comparision operator, you still can do DISTINCT, only less efficiently. Type point has no btree opclass, no hash opclass, and not even an operator named = (it looks like the functionality is named ~= for some odd reason). I'd be interested to hear either a proposal of a principled way to define DISTINCT, or a way to implement it that was better than comparing every element to every other element... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: It seems configure only checks whether linking was successful. I think it should check whether ./conftest was successful. Wouldn't work when cross-compiling. What platform is this exactly? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: It seems configure only checks whether linking was successful. I think it should check whether ./conftest was successful. Wouldn't work when cross-compiling. But configure already does this, doesn't it? configure:24466: checking for working memcmp configure:24519: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -D_GNU_SOURCEconftest.c -lz -lreadline -lncurses -lcrypt -ldl -lm 5 configure:24522: $? = 0 configure:24528: ./conftest configure:24531: $? = 0 configure:24547: result: yes What platform is this exactly? The Linux distributions is Vine Linux, which is a variant of Red Hat Linux (I think). $ uname -a Linux localhost.localdomain 2.6.16-0vl76.33 #1 SMP Fri Feb 15 11:06:36 JST 2008 i686 i686 i386 GNU/Linux -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes: All three try to sort the table first, and as there's no comparision operator for the POINT datatype, they fail. Which seems to be wrong - if there is no comparision operator, you still can do DISTINCT, only less efficiently. Type point has no btree opclass, no hash opclass, and not even an operator named = (it looks like the functionality is named ~= for some odd reason). I'd be interested to hear either a proposal of a principled way to define DISTINCT, or a way to implement it that was better than comparing every element to every other element... I agree - a byte-wise comparison of the internal encoding might be inadequate (compare 0.0e+1 to 0.0e+2 is not equal for instance?). If the poster is referring to a translation to string before comparing, this could face similar issue. What if it's not a point but a fraction - does 2/4 = 1/2? With an operator implementing =, making any assumption may be making the wrong assumption, and I really like that PostgreSQL will refuse to do things rather than silently continue to do what may be the wrong thing (MySQL silent truncation when assigning into a varchar(8) for example). The problem here seems to that point should have an equality operator? Cheers, mark -- Mark Mielke [EMAIL PROTECTED]
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: What platform is this exactly? The Linux distributions is Vine Linux, which is a variant of Red Hat Linux (I think). If it's rpm-based, could we see the RPM package version numbers for binutils, readline, and ncurses? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
The Linux distributions is Vine Linux, which is a variant of Red Hat Linux (I think). If it's rpm-based, could we see the RPM package version numbers for binutils, readline, and ncurses? Sure. binutils-2.15.92.0.2-5vl2 readline-5.1-0vl1 readline-devel-5.1-0vl1 ncurses-devel-5.4-0vl3 ncurses-5.4-0vl3 -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
Thanks for all yours suggestions, use cases and opinion about this thread, I saw that there are more things to consider than I was thinking and this make me consider that it is a hard work to do for now. -- []s Dickson S. Guedes - Projeto Colmeia - Curitiba - PR +55 (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO item: Have psql show current values for a sequence
On Sat, May 24, 2008 at 12:27 AM, Dickson S. Guedes [EMAIL PROTECTED] wrote: Hi all, These patch implements the TODO item: Have psql show current values for a sequence. Hi all, There are some comments or suggestions about this patch? Thanks all. -- []s Dickson S. Guedes - Projeto Colmeia - Curitiba - PR +55 (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Mark Mielke [EMAIL PROTECTED] writes: The problem here seems to that point should have an equality operator? For starters ;-). The current implementation of UNION requires it to have a complete btree opclass. In principle I suppose we could implement hash-based DISTINCT, which would require only a hash opclass, but that isn't there either. Note that the only way that the system knows that an operator has the semantics of equality is for it to be the equality member of a btree or hash opclass; there isn't any other representation of operator semantics in Postgres. So the opclasses not only provide necessary execution infrastructure, but also the justification for using a particular operator to define DISTINCT-ness. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CVS Head psql bug?
Tatsuo Ishii [EMAIL PROTECTED] writes: binutils-2.15.92.0.2-5vl2 readline-5.1-0vl1 readline-devel-5.1-0vl1 ncurses-devel-5.4-0vl3 ncurses-5.4-0vl3 Okay, it seems fairly close to RHEL-4 --- at least almost the same version of binutils, which is probably what counts here. Doesn't look like there are any RHEL4 or CentOS4 machines in the buildfarm, which is why we didn't find out about it earlier. I'll go see if I can reproduce the problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Mark Mielke wrote: Tom Lane wrote: Type point has no btree opclass, no hash opclass, and not even an operator named = (it looks like the functionality is named ~= for some odd reason). I'd be interested to hear either a proposal of a principled way to define DISTINCT, or a way to implement it that was better than comparing every element to every other element... The way I see it there's nothing wrong with the definition of DISTINCT and for types that can't be compared there is no way of calculating distinct values other than comparing every element to every other. My point is that it is theoretically possible to do DISTINCT with only a equality operator. Sure, it's impractical, but it's a valid operation. If you can tell which elements are equal, you can take the largest subset of elements, among which no two are equal. The least that can be done is improve the error message. Maybe something like: The query required to sort elements of type foo to calculate the result efficiently, but there is no ordering operator for type foo would do. And document, that GROUP BY, DISTINCT and UNION fail on types that can't be sorted. I agree - a byte-wise comparison of the internal encoding might be inadequate (compare 0.0e+1 to 0.0e+2 is not equal for instance?). If the poster is referring to a translation to string before comparing, The problem here seems to that point should have an equality operator? I think it has (=~, as Tom pointed out). The real problem is: should there be code to do GROUP BY / DISTINCT when there are no btree or hash opclasses, or should it be considered an error, because doing it would take very long for larger result sets? Cheers, Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS]
i am studying the source code of postgresql and want to become a developer of it.What should i do? thanks _ Explore the seven wonders of the world http://search.msn.com/results.aspx?q=7+wonders+worldmkt=en-USform=QBRE
Re: [HACKERS] Creating a VIEW with a POINT column
Nick wrote: I have a VIEW that consists of two tables, of which contain a POINT column. When trying to select from the view I get an error... ERROR: could not identify an ordering operator for type point HINT: Use an explicit ordering operator or modify the query. Any suggestions??? -Nick I'm a lurker on this list (came for the 8.3 release, stayed for the delightful banter), but I have noticed that seems to be a real issue, at least for the moment. Not trying to be snotty, but perhaps using postGIS http://postgis.refractions.net/ would be a suitable alternate ? It does require admin rights to install but the point does have an equality op, GIST indexing and is reasonably light-weight in disk space. Ok, you probably already rejected this for good reason ... back to the real thread. Apologies for the signage below ... Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [HACKERS] CVS Head psql bug?
I wrote: Okay, it seems fairly close to RHEL-4 --- at least almost the same version of binutils, which is probably what counts here. I was able to reproduce the problem on current RHEL-4, and on examination I don't think it's really ld's fault. The problem is that libreadline hasn't got a DT_NEEDED entry for libncurses nor libtermcap. Apparently some benighted fool thought this was a good idea since then you could use either one :-(. More recent releases have resolved the issue by replacing both ncurses and termcap with libtinfo, which thankfully readline now sports a DT_NEEDED dependency for. Even assuming I could get Red Hat to change the situation in RHEL4 (which would be a long shot), it would take a long time if not forever for the fix to propagate to derived distros such as Tatsuo-san is using. So it seems we've got to deal with the situation as it stands. I studied the ld man page for awhile but couldn't find any fix other than the one Tatsuo suggests of trying to run the linked test program. That means we have to guess at what to do in a cross-compilation. I suppose the safest choice is to not try to use --as-needed when cross-compiling, but does anyone want to argue for the other choice? Whichever way we jump, it'd be possible for someone to override configure's choice by editing the generated Makefile.global, so my guess is we ought to favor the guaranteed-to-work option. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes: Tom Lane wrote: Type point has no btree opclass, no hash opclass, and not even an operator named = (it looks like the functionality is named ~= for some odd reason). I'd be interested to hear either a proposal of a principled way to define DISTINCT, or a way to implement it that was better than comparing every element to every other element... The way I see it there's nothing wrong with the definition of DISTINCT and for types that can't be compared there is no way of calculating distinct values other than comparing every element to every other. for types that can't be compared? Do you not see the logical disconnect in that sentence? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes: Tom Lane wrote: Type point has no btree opclass, no hash opclass, and not even an operator named = (it looks like the functionality is named ~= for some odd reason). I'd be interested to hear either a proposal of a principled way to define DISTINCT, or a way to implement it that was better than comparing every element to every other element... The way I see it there's nothing wrong with the definition of DISTINCT and for types that can't be compared there is no way of calculating distinct values other than comparing every element to every other. for types that can't be compared? Do you not see the logical disconnect in that sentence? OK, there might have been a mental shortcut there. Can't be compared was supposed to mean can't decide whether one value of that type is bigger than another. Doing DISTINCT without an equality operator is nonsense. Doing it without a comparision operator is only very slow. -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins
Andres Freund [EMAIL PROTECTED] writes: SELECT * FROM ab LEFT OUTER JOIN ( bc JOIN cd ON bc.c = cd.d ) ON ab.b = bc.b WHERE ab.a = 2 As ab.a = 2 occurs only once in ab one would expect that it just does an index scan on bc for ab.b = bc.b. The only way it could do that would be by interchanging the order of the left and inner joins, ie (ab left join bc) join cd; which would change the results. I believe it could interchange the joins if they were both LEFT or both INNER. Do you really need exactly these semantics? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed Patch - LDAPS support for servers on port 636 w/o TLS
Added to TODO: * Improve LDAP authentication configuration options http://archives.postgresql.org/pgsql-hackers/2008-04/msg01745.php --- steve layland wrote: -- Start of PGP signed section. Thank you all for your comments. I was unaware the ldaps: scheme was not supposed to be used for LDAP+TLS encryption, but it makes sense now that you mention it. There's a nice discussion about how the folks working on mod_ldap for Apache worked this out way back in 2005: http://mail-archives.apache.org/mod_mbox/httpd-dev/200501.mbox/[EMAIL PROTECTED] Anyway, I think we've distilled the issue down to how to best enable TLS for ldap:// connections. By my reckoning, that means we can have: 1) per-hba.conf entry configuration where the configuration can be: a) of the ldap URL extension form mentioned by David (!StartTLS). b) key=value type of param string as suggested by Magnus c) a specific URI scheme like ldap+tls:// like Tom suggested. d) a new authentication type ldaptls 2) per-postgres server configuration which can be: a) an old LDAPTLS environment variable ? needs research b) a server-wide GUC variable (along with TLSCERT specifications?) as in the current patch I'm open to other suggestions. One other thing to keep in mind is how best to map database roles to ldap Distinguished Name (dn) entries? In other words, we need to take the user jimmy in psql -U jimmy and translate into an ldap authentication request for the distinguished name that is entirely dependent on the site and ldap impl, example: uid=jimmy,ou=people,dc=example,dc=com I've racked my brain thinking of ways that this can fit cleanly in hba.conf, but I haven't found anything I _really_ like (current patch and proposal 3 below are prob my favorites.) Any other ideas/comments/suggestions? # Current Functionality for reference - no tls control host dbname all 127.0.0.0/32ldap ldap://ldap.example.com[:port]/ignored;uid=;ou=people,dc=example,dc=com; # Current Functionality in patch (w/ server wide TLS control in GUC var) # GUC var causes all ldap entries to use same authentication. can be # applied to service lookup as well host dbname all 127.0.0.0/32ldap ldap://ldap.example.com[:port]/ou=people,dc=example,dc=com;uid=; # proposal 1 - RFC 2255 URI kind of yucky; scope, attributes, filter # not actually used in simple authentication host dbname all 127.0.0.0/32ldap ldap://ldap.example.com[:port]/uid=%u,ou=people,dc=example,dc=com???!StartTLS; # proposal 1b - still RFC 2255 compliant, but semantically weird. no # filter is actually used in simple authentication host dbname all 127.0.0.0/32ldap ldap://ldap.example.com[:port]/ou=people,dc=example,dc=com?one??(uid=%u)!StartTLS # proposal 2 - psuedo-URI scheme; hacky but easy host dbname all 127.0.0.0/32ldap ldap+tls://ldap.example.com[:port]/ou=people,dc=example,dc=com;uid=; # proposal 3 - mod hba parsing, add new ldaptls auth type; reasonably # easy and least invasive; host dbname all 127.0.0.0/32ldaptls ldap://ldap.example.com[:port]/ou=people,dc=example,dc=com;uid=;; # proposal 4 - mod hba parsing host dbname all 127.0.0.0/32ldap ldap://ldap.example.com[:port]/ou=people,dc=example,dc=com;uid=;; StartTLS # proposal 5 - Magnum's key = value like idea (i'm guessing here, # Magnum. If I misinterpret, please explain) host dbname all 127.0.0.0/32ldap ldap://ldap.example.com[:port]/ou=people,dc=example,dc=com;prefix=uid=;start_tls=1; I have some radical ideas as well involving completely ripping out the pg_hba.conf file but I'll leave that for another, more appropriate day. :) Thanks again for the feedback, and sorry for the verbosity. -Steve (#postgresql rockpunk) -- End of PGP section, PGP failed! -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes: OK, there might have been a mental shortcut there. Can't be compared was supposed to mean can't decide whether one value of that type is bigger than another. Doing DISTINCT without an equality operator is nonsense. Doing it without a comparision operator is only very slow. Well, you're still missing my point, which is how do you decide which operator is equality? It was already pointed out upthread that ignoring the type's operators and using bitwise comparison is a pretty sucky alternative. The only infrastructure in Postgres that can identify which operators have which semantics is index opclasses. I see two possible TODO items in this discussion. One is that type point is sorely lacking in opclass support. The other is that it might be interesting to support DISTINCT in cases where only a hash opclass, not a btree opclass, is available --- which would lead to a hash-aggregation-like implementation instead of sort-and-uniq. The value as far as type point is concerned is that you'd not have to invent some arbitrary linear sort ordering for points. The idea of supporting DISTINCT with neither type of opclass available seems to me to be indefensible on *both* semantics and performance grounds. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes: Same thing for GROUP BY. Yeah. The GROUP BY case is even more annoying, because we *have* the planner/executor infrastructure to do it via hashing; but the parser barfs immediately if there is not btree opclass support for the type. I'm not sure how to fix the parser and the parsetree representation to be agnostic about hash versus sort implementations --- any thoughts? ... One last remark: unless something is done about it in 8.4, maybe it is worthwhile to change the error message (which clearly confused Nick) and add some documentation about why you can't use DISTINCT with types without a btree index opclass? IIRC, the error message is correct for some other cases, so it might be hard to fix it without taking two steps backward overall. But feel free to take a look if you want to. Nuthin wrong with more documentation either ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating a VIEW with a POINT column
Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes: OK, there might have been a mental shortcut there. Can't be compared was supposed to mean can't decide whether one value of that type is bigger than another. Doing DISTINCT without an equality operator is nonsense. Doing it without a comparision operator is only very slow. Well, you're still missing my point, which is how do you decide which operator is equality? It was already pointed out upthread that ignoring the type's operators and using bitwise comparison is a pretty sucky alternative. The only infrastructure in Postgres that can identify which operators have which semantics is index opclasses. All right, I get it. You get the equality operator from the index opclass for the type, I didn't understand fully how it worked. I see two possible TODO items in this discussion. One is that type point is sorely lacking in opclass support. The other is that it might be interesting to support DISTINCT in cases where only a hash opclass, not a btree opclass, is available --- which would lead to a hash-aggregation-like implementation instead of sort-and-uniq. Same thing for GROUP BY. One last remark: unless something is done about it in 8.4, maybe it is worthwhile to change the error message (which clearly confused Nick) and add some documentation about why you can't use DISTINCT with types without a btree index opclass? -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins
SELECT * FROM ab LEFT OUTER JOIN (bc JOIN cd ON bc.c = cd.d) ON ab.b = bc.b WHERE ab.a = 2 As ab.a = 2 occurs only once in ab one would expect that it just does an index scan on bc for ab.b = bc.b. The only way it could do that would be by interchanging the order of the left and inner joins, ie (ab left join bc) join cd; which would change the results. In theory, I believe this could be rewritten as: SELECT * FROM ab LEFT OUTER JOIN (SELECT bc.b FROM ab JOIN bc ON ab.b = bc.b JOIN cd ON bc.c = cd.d WHERE ab.b = 2) dummy ON ab.b = dummy.b WHERE ab.a = 2 ...without affecting the results. If the condition ab.a = 2 is highly selective, this is a big win. I can predict that Tom will say that the planning time it would take to avoid this problem isn't justified by the number of queries that it would improve. That's possible, but it's unfortunate that there's no way to fiddle with the knobs and get the planner to do this kind of thing when you want it to. Rewriting the query as described above is OK when you're writing the whole query from scratch, but I don't know of an easy fix for this: CREATE VIEW xyz AS SELECT * FROM ab LEFT OUTER JOIN (bc JOIN cd ON bc.c = cd.d) ON ab.b = bc.b Sometimes I want to SELECT * FROM xyz ORDER BY a LIMIT 100 (to let the user browse records) and sometimes I want to SELECT * FROM WHERE a = 2 (retrieve a single record). Neither query performs acceptably if the planner generates the entire cross-product of bc and cd and then throws most of it away, unless bc and cd are very small tables. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
Added to TODO: * Improve server security options http://archives.postgresql.org/pgsql-hackers/2008-04/msg01875.php http://archives.postgresql.org/pgsql-hackers/2008-05/msg0.php --- KaiGai Kohei wrote: I updated the series of SE-PostgreSQL patches for the latest pgsql-8.4devel tree. [1/4] sepostgresql-pgace-8.4devel-3-r739.patch provides PGACE (PostgreSQL Access Control Extension) framework. http://sepgsql.googlecode.com/files/sepostgresql-pgace-8.4devel-3-r739.patch [2/4] sepostgresql-sepgsql-8.4devel-3-r739.patch provides SE-PostgreSQL feature, based on PGACE framework. http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r739.patch [3/4] sepostgresql-pg_dump-8.4devel-3-r739.patch enables to dump databases with security attribute. http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r739.patch [4/4] sepostgresql-policy-8.4devel-3-r739.patch provides the default security policy of SE-PostgreSQL. http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r739.patch We provide a quick overview for SE-PostgreSQL at: http://code.google.com/p/sepgsql/wiki/WhatIsSEPostgreSQL Thanks, KaiGai Kohei wrote: The series of patches are the proposal of Security-Enhanced PostgreSQL (SE-PostgreSQL) for the upstreamed PostgreSQL 8.4 development cycle. [1/4] sepostgresql-pgace-8.4devel-3.patch provides PGACE (PostgreSQL Access Control Extension) framework http://sepgsql.googlecode.com/files/sepostgresql-pgace-8.4devel-3-r704.patch [2/4] sepostgresql-sepgsql-8.4devel-3.patch provides SE-PostgreSQL feature, based on PGACE framework. http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r704.patch [3/4] sepostgresql-pg_dump-8.4devel-3.patch enables pg_dump to dump database with security attribute. http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r704.patch [4/4] sepostgresql-policy-8.4devel-3.patch provides the default security policy for SE-PostgreSQL. http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r704.patch We can provide a quick overview for SE-PostgreSQL at: http://code.google.com/p/sepgsql/wiki/WhatIsSEPostgreSQL ENVIRONMENT --- Please confirm your environment. The followings are requriements of SE-PostgreSQL. * Fedora 8 or later system * SELinux is enabled and working * kernel-2.6.24 or later * selinux-policy and selinux-policy-devel v3.0.8 or later * libselinux, policycoreutils INSTALLATION $ tar jxvf postgresql-snapshot.tar.bz2 $ cd postgresql-snapshot $ patch -p1 ../sepostgresql-pgace-8.4devel-3.patch $ patch -p1 ../sepostgresql-sepgsql-8.4devel-3.patch $ patch -p1 ../sepostgresql-pg_dump-8.4devel-3.patch $ patch -p1 ../sepostgresql-policy-8.4devel-3.patch $ ./configure --enable-selinux $ make $ make -C contrib/sepgsql-policy $ su # make install # /usr/sbin/semodule -i contrib/sepgsql-policy/sepostgresql.pp (NOTE: semodule is a utility to load/unload security policy modules.) # /sbin/restorecon -R /usr/local/pgsql (NOTE: restorecon is a utilicy to initialize security context of files.) SETUP - # mkdir -p /opt/sepgsql # chown foo_user:var_group /opt/sepgsql # chcon -t postgresql_db_t /opt/sepgsql (NOTE: chcon is a utility to set up security context of files.) # exit $ /usr/sbin/run_init /usr/local/pgsql/bin/initdb -D /opt/sepgsql (NOTE: run_init is a utility to start a program, as if it is branched from init script.) $ /usr/local/pgsql/bin/pg_ctl -D /opt/sepgsql start SUMMARYS FOR EVERY PATCHES -- [1/4] - sepostgresql-pgace-8.4devel-3.patch This patch provides PGACE (PostgreSQL Access Control Extension) framework. It has a similar idea of LSM (Linu Security Module). It can provide a guest module several hooks at strategic points. The guest module can make its decision whether required actions should be allowed, or not. In addition, PGACE also provides falicilites to manage security attribute of database objects. Any tuple can have a its security attribute, and the guest module can refer it to control accesses. A more conprehensive memo at: http://code.google.com/p/sepgsql/wiki/WhatIsPGACE [2/4] - sepostgresql-sepgsql-8.4devel-3.patch This patch provides SE-PostgreSQL facilities based on PGACE. Security-Enhanced PostgreSQL (SE-PostgreSQL) is a security extension built in PostgreSQL, to provide system-wide consistency in access controls. It enables to apply a single unigied security policy of SELinux for both operating system and database management system. In addition, it also provides fine-grained mandatory access
Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins
Robert Haas [EMAIL PROTECTED] writes: I can predict that Tom will say that the planning time it would take to avoid this problem isn't justified by the number of queries that it would improve. Took the words right out of my mouth ;-) It would be *possible* to do this sort of thing, but what it would imply is re-planning entire join nests on the strength of assumptions that some additional constraints are available. Right now we only do that for individual inner-side relations. AFAICS extending that to sub-joins would result in an exponential increase in plan time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
