Re: [HACKERS] user-based query white list
Hi We use plproxy for this kind of security enhancement. We create plpgsql functions that do whats needed and then we create so called proxy database that contains only plproxy interfaces for these functions. Users get access only to proxy database. This way it is easier to rest assured that users don't get access by accident to something they should not. regards, Asko On Sat, Dec 6, 2008 at 8:21 PM, Andrew Chernow [EMAIL PROTECTED] wrote: Looking for a way to limited a user to a specific set of queries. I don't think this can be done right now ... or can it? Has this feature request surfaced in the past? I currently need this as an extra security measure for a libpq client app (want to block arbitrary queries from malicious attackers). The easiest way I found was to add some query_string checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in PostgresMain(). Seems to work just fine. If it doesn't match, I issue an ereport FATAL since that is seen as a malicious query execution attempt. I think it is something rather simple to design/implement (probably use a table of user allowed queries, support regex matches, etc.. loaded at session startup and SIGHUP). -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
2008/12/6 David Rowley [EMAIL PROTECTED]: I've spent last night and tonight trying to break the patch and I've not managed it. I spent 2 and a half hours on the train last night reading over the patch mainly for my own interest. I also went over the documentation and I have a few suggestions for improvement: +para + After literalWHERE/ and literalGROUP BY/ process, + rows might be windowed table, using the literalWINDOW/ + clause. +/para I think I know what you mean here. My re-write seems to have turned the sentence into a paragraph. Please tell me if I've assumed the meaning wrongly: After the literalWHERE/, literalGROUP BY/ and literalHAVING/ clauses one or more literalWINDOW/ clauses can be specified. This will allow window functions to be specified in the literalSELECT/ clause. These window functions can make use of the literalWINDOW/ clauses by making reference to the alias name of the window rather than explicitly specifying the properties of the window in each literalOVER/ clause. The WINDOW clause is a clause that starts with WINDOW, containing some window definitions, syntactically. So I rewrote it as: After the literalWHERE/, literalGROUP BY/ and literalHAVING/ clauses one or more window definitions can be specified by the literalWINDOW/ clause. This will allow window functions to be specified in the literalSELECT/ clause. These window functions can make use of the literalWINDOW/ clauses by making reference to the alias name of the window rather than explicitly specifying the properties of the window in each literalOVER/ clause. + Window functions are not placed in any of GROUP BY, HAVING and + WHERE clauses, which process values before any of the windows. If + there is need to qualify rows by the result of window functions, + whole of the query must be nested and append WHERE clause outer of + the current query. I think this one maybe needs an example to back it up. It's quite an important thing and I'm sure lots of people will need to do this. I'm not 100% happy with my new paragraph either but can't see how to word it any better. Window functions cannot be used in the WHERE, GROUP BY or HAVING clauses of the query. If there is a need to filter rows, group results or filter rows after aggregation takes place (HAVING) then the query must be nested. The query should contain the window functions in the inner query and apply the additional clauses that contain the results from the window function in the outer query, such as: SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary,empno) AS pos FROM empsalary ) AS e WHERE pos = 1; In the above query the we're filtering and only showing the results from the inner query where the ROW_NUMBER() value is equal to 1. But of course the above query would be more simple using DISTINCT ON. Maybe there is a better example... My previous marathon getting the person in 2nd place might be better but that's introducing another previously unknown table to the manual. I use this query: SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary,empno) AS pos FROM empsalary ) AS e WHERE pos 3; This isn't emulated by DISTINCT ON, is it? For all other issues, thanks, applied to my patch. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Saturday 06 December 2008 22:38:29 James Mansion wrote: Kurt Harriman wrote: The foremost opposing argument seems to have been that there should be no attempt to alleviate the existing reserved word problem without automatic enforcement to guarantee that never in the future can new occurrences be introduced. Is there anything in the source that would necessarily preclude using the C++ compiler to build *all* the code? Probably lots, but that's not the problem we are trying to solve here. And many people are seriously not interested in using C++ for PostgreSQL. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] problem i get for Patch blomming filter
When i was performancing gmake, i got a error message. The attachment is the detail, i have no ideas about it. Hope someone' help Thanks gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -L../../src/port -Wl,-R'/usr/local/pgsql/lib' access/common/heaptuple.o access/common/indextuple.o access/common/printtup.o access/common/reloptions.o access/common/scankey.o access/common/tupdesc.o access/gist/gist.o access/gist/gistutil.o access/gist/gistxlog.o access/gist/gistvacuum.o access/gist/gistget.o access/gist/gistscan.o access/gist/gistproc.o access/gist/gistsplit.o access/hash/hash.o access/hash/hashfunc.o access/hash/hashinsert.o access/hash/hashovfl.o access/hash/hashpage.o access/hash/hashscan.o access/hash/hashsearch.o access/hash/hashsort.o access/hash/hashutil.o access/heap/heapam.o access/heap/hio.o access/heap/pruneheap.o access/heap/rewriteheap.o access/heap/syncscan.o access/heap/tuptoaster.o access/index/genam.o access/index/indexam.o access/nbtree/nbtcompare.o access/nbtree/nbtinsert.o access/nbtree/nbtpage.o access/nbtree/nbtree.o access/nbtree/nbtsearch.o access/nbtree/nbtutils.o access/nbtree/nbtsort.o access/nbtree/nbtxlog.o access/transam/clog.o access/transam/transam.o access/transam/varsup.o access/transam/xact.o access/transam/xlog.o access/transam/xlogutils.o access/transam/rmgr.o access/transam/slru.o access/transam/subtrans.o access/transam/multixact.o access/transam/twophase.o access/transam/twophase_rmgr.o access/gin/ginutil.o access/gin/gininsert.o access/gin/ginxlog.o access/gin/ginentrypage.o access/gin/gindatapage.o access/gin/ginbtree.o access/gin/ginscan.o access/gin/ginget.o access/gin/ginvacuum.o access/gin/ginarrayproc.o access/gin/ginbulk.o bootstrap/bootparse.o bootstrap/bootstrap.o catalog/catalog.o catalog/dependency.o catalog/heap.o catalog/index.o catalog/indexing.o catalog/namespace.o catalog/aclchk.o catalog/pg_aggregate.o catalog/pg_constraint.o catalog/pg_conversion.o catalog/pg_depend.o catalog/pg_enum.o catalog/pg_largeobject.o catalog/pg_namespace.o catalog/pg_operator.o catalog/pg_proc.o catalog/pg_shdepend.o catalog/pg_type.o catalog/toasting.o parser/analyze.o parser/gram.o parser/keywords.o parser/parser.o parser/parse_agg.o parser/parse_cte.o parser/parse_clause.o parser/parse_expr.o parser/parse_func.o parser/parse_node.o parser/parse_oper.o parser/parse_relation.o parser/parse_type.o parser/parse_coerce.o parser/parse_target.o parser/parse_utilcmd.o parser/scansup.o commands/aggregatecmds.o commands/alter.o commands/analyze.o commands/async.o commands/cluster.o commands/comment.o commands/conversioncmds.o commands/copy.o commands/dbcommands.o commands/define.o commands/discard.o commands/explain.o commands/functioncmds.o commands/indexcmds.o commands/lockcmds.o commands/operatorcmds.o commands/opclasscmds.o commands/portalcmds.o commands/prepare.o commands/proclang.o commands/schemacmds.o commands/sequence.o commands/tablecmds.o commands/tablespace.o commands/trigger.o commands/tsearchcmds.o commands/typecmds.o commands/user.o commands/vacuum.o commands/vacuumlazy.o commands/variable.o commands/view.o executor/execAmi.o executor/execCurrent.o executor/execGrouping.o executor/execJunk.o executor/execMain.o executor/execProcnode.o executor/execQual.o executor/execScan.o executor/execTuples.o executor/execUtils.o executor/functions.o executor/instrument.o executor/nodeAppend.o executor/nodeAgg.o executor/nodeBitmapAnd.o executor/nodeBitmapOr.o executor/nodeBitmapHeapscan.o executor/nodeBitmapIndexscan.o executor/nodeHash.o executor/nodeHashjoin.o executor/nodeIndexscan.o executor/nodeMaterial.o executor/nodeMergejoin.o executor/nodeNestloop.o executor/nodeFunctionscan.o executor/nodeRecursiveunion.o executor/nodeResult.o executor/nodeSeqscan.o executor/nodeSetOp.o executor/nodeSort.o executor/nodeUnique.o executor/nodeValuesscan.o executor/nodeCtescan.o executor/nodeWorktablescan.o executor/nodeLimit.o executor/nodeGroup.o executor/nodeSubplan.o executor/nodeSubqueryscan.o executor/nodeTidscan.o executor/tstoreReceiver.o executor/spi.o lib/dllist.o lib/stringinfo.o libpq/be-fsstubs.o libpq/be-secure.o libpq/auth.o libpq/crypt.o libpq/hba.o libpq/ip.o libpq/md5.o libpq/pqcomm.o libpq/pqformat.o libpq/pqsignal.o main/main.o nodes/nodeFuncs.o nodes/nodes.o nodes/list.o nodes/bitmapset.o nodes/tidbitmap.o nodes/copyfuncs.o nodes/equalfuncs.o nodes/makefuncs.o nodes/outfuncs.o nodes/readfuncs.o nodes/print.o nodes/read.o nodes/params.o nodes/value.o optimizer/geqo/geqo_copy.o optimizer/geqo/geqo_eval.o optimizer/geqo/geqo_main.o optimizer/geqo/geqo_misc.o optimizer/geqo/geqo_mutation.o optimizer/geqo/geqo_pool.o optimizer/geqo/geqo_recombination.o optimizer/geqo/geqo_selection.o optimizer/geqo/geqo_erx.o optimizer/geqo/geqo_pmx.o optimizer/geqo/geqo_cx.o
Re: [HACKERS] problem i get for Patch blomming filter
On Sunday 07 December 2008 12:06:44 Unicron wrote: When i was performancing gmake, i got a error message. The attachment is the detail, i have no ideas about it. Hope someone' help You probably forgot make distclean before rebuilding. -- 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] user-based query white list
On Sat, 2008-12-06 at 13:30 -0500, Andrew Chernow wrote: Grzegorz Jaskiewicz wrote: On 2008-12-06, at 18:21, Andrew Chernow wrote: Looking for a way to limited a user to a specific set of queries. I don't think this can be done right now ... or can it? Has this feature request surfaced in the past? I currently need this as an extra security measure for a libpq client app (want to block arbitrary queries from malicious attackers). The easiest way I found was to add some query_string checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in PostgresMain(). Seems to work just fine. If it doesn't match, I issue an ereport FATAL since that is seen as a malicious query execution attempt. I think it is something rather simple to design/implement (probably use a table of user allowed queries, support regex matches, etc.. loaded at session startup and SIGHUP). Can it be done with views, and adjusting permissions so user is only allowed to use few views ?? Not sure. The client I am working on only calls functions, small API to interact with (no knowledge of views or tables). Then grant access to those functions only. Even if that were not the case, would views stop a client from sending in other queries, like SELECT 1+1 or something that could bog down the server? Use statement_timeout GUC to prevent bogging Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
2008/12/6 David Rowley [EMAIL PROTECTED]: Hitoshi Harada Wrote: 2008/12/3 Hitoshi Harada [EMAIL PROTECTED]: I am randomly trying some issues instead of agg common code (which I now doubt if it's worth sharing the code), so tell me if you're restarting your hack again. I'll send the whole patch. Attached is the updated patch, including: - performance tuning up for large data sets - move find_wfunc to optimizer/util/clauses.c - rename WFunc to WindowFunc - rename WinDef to WindowDef - rename wfunc-pure_agg to winagg - remove winstate-tail_ptr - fix WinFrameGetArg in case that there are more than one peer, add relevant test - duplicate GetAggInitVal(), not sharing aggregate routines with nodeAgg.c I believe the remaining work is only to optimize row_number()/rank() cases to trim tuplestore like window aggregates. This will be done by providing some kind of way for each window functions to tell Window node that it doesn't require backward_scan. I've spent hours to try this issue, and concluded it doesn't pay. First, the test is on this query: explain analyze select id, row_number() OVER (order by id) from bigtable order by id; where bigtable has ~400MB, 1000 rows. This simple row_number() query takes about 50 sec, whereas without row_number() indexscan query takes about 25 sec. I wondered what makes the difference 25 sec. With this test, the tuplestore dumps its tuples since it never trims. Then I took profile of nodeWindow in some points, tuplestore_puttupleslot 13.6 sec spool_tuples37.9 sec eval_windowfunction 9.3 sec Note that spool_tuples contains execProc(outerPlan), which means its 37.9 sec contains outer indexscan 25 sec, plus tuplestore_puttuple, 13.9 sec. Then I modified some code to let tuplestore trim and tested again then the results were: tuplestore_puttupleslot 11.2 sec spool_tuples35.8 sec eval_windowfunction 9.5 sec It shows even though tuplestore trims its tuples and stays in memory rather than dumps them on files, the performance up is only 2 sec in 50 sec. So I concluded the optimization for row_number()/rank() etc doesn't pay for its more complexity in window function API. The bottleneck of the Window node origins from something else, like puttupleslot(), not Window node algorithm. Of course those issues should be tracked more precisely, for the window functions work I ignore them. It's been a long time since the CommitFest started. This patch has come a long way in that time. We've seen various performance improvements and many fixes where the patch was not matching the standard. We've also seen quite a big change in the window buffering technique which is showing amazing performance improvements in certain queries. I've spent many hours reading the standard and comparing the results from this patch against other RDBMS' that support window functions. I wonder if we're the first to implement NTH_VALUE()?. The patch, in my opinion is getting very close to being committable. The only outstanding issues; Please correct me where I'm wrong or have omitted something. * Fix still required for rank_up. Code still has a FIXME. This was whether rank() without ORDER BY clause should be valid or not. The answer is yes as it is implemented now, so I removed only comments. * ROW_NUMBER() and RANK() performance to be looked into. As I tested above. * Slight changes required in documentation (my previous email). Applied to the patch. * Final code read by someone reviewing the code. I am looking forward. I've also spent many hours trying to break this patch and in previous versions I've managed it. Last night I spent a few hours again testing this new patch and did not managed to find anything wrong. We're getting close to the time where the community can test further by committing this patch. Agree. I'll send the latest patch and finish my work for now. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] user-based query white list
There is extra safety from using whitelists... For one, it's trivial to write a query that consumes unlimited CPU resources that accesses no built in tables or functions. There are various other dangerous things that are difficult to lock down like temp tables. Assuming you can handle paramaterized queries on the client, a whitelist is pretty easy and powerful safeguard on top of the normal protections. Your biggest concern is malformed protocol messages or parameters and there are extra possible defenses there. A whitelist is trivial to implement. So the question is: is the OP suggesting how one could be done and if so, does it make it safe to allow ssl connections from $WORLD. merlin On 12/7/08, Hannu Krosing [EMAIL PROTECTED] wrote: On Sat, 2008-12-06 at 13:30 -0500, Andrew Chernow wrote: Grzegorz Jaskiewicz wrote: On 2008-12-06, at 18:21, Andrew Chernow wrote: Looking for a way to limited a user to a specific set of queries. I don't think this can be done right now ... or can it? Has this feature request surfaced in the past? I currently need this as an extra security measure for a libpq client app (want to block arbitrary queries from malicious attackers). The easiest way I found was to add some query_string checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in PostgresMain(). Seems to work just fine. If it doesn't match, I issue an ereport FATAL since that is seen as a malicious query execution attempt. I think it is something rather simple to design/implement (probably use a table of user allowed queries, support regex matches, etc.. loaded at session startup and SIGHUP). Can it be done with views, and adjusting permissions so user is only allowed to use few views ?? Not sure. The client I am working on only calls functions, small API to interact with (no knowledge of views or tables). Then grant access to those functions only. Even if that were not the case, would views stop a client from sending in other queries, like SELECT 1+1 or something that could bog down the server? Use statement_timeout GUC to prevent bogging Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] user-based query white list
Merlin Moncure wrote: There is extra safety from using whitelists... For one, it's trivial to write a query that consumes unlimited CPU resources that accesses no built in tables or functions. There are various other dangerous things that are difficult to lock down like temp tables. Assuming you can handle paramaterized queries on the client, a whitelist is pretty easy and powerful safeguard on top of the normal protections. Your biggest concern is malformed protocol messages or parameters and there are extra possible defenses there. A whitelist is trivial to implement. So the question is: is the OP suggesting how one could be done and if so, does it make it safe to allow ssl connections from $WORLD. merlin On 12/7/08, Hannu Krosing [EMAIL PROTECTED] wrote: On Sat, 2008-12-06 at 13:30 -0500, Andrew Chernow wrote: Grzegorz Jaskiewicz wrote: On 2008-12-06, at 18:21, Andrew Chernow wrote: Looking for a way to limited a user to a specific set of queries. I don't think this can be done right now ... or can it? Has this feature request surfaced in the past? I currently need this as an extra security measure for a libpq client app (want to block arbitrary queries from malicious attackers). The easiest way I found was to add some query_string checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in PostgresMain(). Seems to work just fine. If it doesn't match, I issue an ereport FATAL since that is seen as a malicious query execution attempt. I think it is something rather simple to design/implement (probably use a table of user allowed queries, support regex matches, etc.. loaded at session startup and SIGHUP). Can it be done with views, and adjusting permissions so user is only allowed to use few views ?? Not sure. The client I am working on only calls functions, small API to interact with (no knowledge of views or tables). Then grant access to those functions only. Even if that were not the case, would views stop a client from sending in other queries, like SELECT 1+1 or something that could bog down the server? Use statement_timeout GUC to prevent bogging Hannu I think what is missing is a way to deny the execution of queries that don't operate on an object (like a table, sequence, role, schema, etc...), OR queries not covered by the priv system. Object-based queries can be locked down using the existing priv system. Not sure if denying non-object related queries would work; what happens when you call SELECT NOW() within an allowed function? Andrew Chernow esilo, LLC. -- 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] user-based query white list
Andrew Chernow wrote: I think what is missing is a way to deny the execution of queries that don't operate on an object (like a table, sequence, role, schema, etc...), OR queries not covered by the priv system. Object-based queries can be locked down using the existing priv system. Not sure if denying non-object related queries would work; what happens when you call SELECT NOW() within an allowed function? What exactly are you trying to protect against? In general, my attitude is that databases should not allow direct access from untrusted sources. The API restriction you are talking about is something that is trivially easy to build into middleware, and only the middleware should be allowed access to the database. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Peter Eisentraut wrote: On Saturday 06 December 2008 22:38:29 James Mansion wrote: Kurt Harriman wrote: The foremost opposing argument seems to have been that there should be no attempt to alleviate the existing reserved word problem without automatic enforcement to guarantee that never in the future can new occurrences be introduced. Is there anything in the source that would necessarily preclude using the C++ compiler to build *all* the code? Probably lots, but that's not the problem we are trying to solve here. And many people are seriously not interested in using C++ for PostgreSQL. The most serious problem AFAIK is that we use setjmp/longjmp, which I understand does not play at all nicely with C++ exceptions. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WAL documentation changes
Hi, the comment WRT WAL recovery and FS journals [1] is a bit misleading in it's current form. First, none of the general purpose filesystems I've seen so far do data journalling per default, since it's a huge performance penalty, even for non-RDBMS workloads. The feature you talk about is ext3 specific (and should be pointed out as such) and only disables write ordering, meaning that metadata and file content updates are not synchronized. best regards, Michael [1] 64b3d98baaf96afea815b0c37ff918f02fda11c9 -- 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] user-based query white list
Andrew Dunstan wrote: Andrew Chernow wrote: I think what is missing is a way to deny the execution of queries that don't operate on an object (like a table, sequence, role, schema, etc...), OR queries not covered by the priv system. Object-based queries can be locked down using the existing priv system. Not sure if denying non-object related queries would work; what happens when you call SELECT NOW() within an allowed function? What exactly are you trying to protect against? In general, my attitude is that databases should not allow direct access from untrusted sources. The API restriction you are talking about is something that is trivially easy to build into middleware, and only the middleware should be allowed access to the database. cheers andrew Why must this be done in middleware? Middleware wouldn't be needed as protection against untrusted sources if random queries could be denied. My little hack in PostgresMain() made it impossible to execute queries unless they are on a white list (there could be better ways of doing this). Now add in SSL and verification of certificates and things are tightly nailed down; as much as the classic application server (middleware) would be ... no? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
2008/12/7 Hitoshi Harada [EMAIL PROTECTED]: 2008/12/7 Hitoshi Harada [EMAIL PROTECTED]: 2008/12/6 David Rowley [EMAIL PROTECTED]: the time where the community can test further by committing this patch. Agree. I'll send the latest patch and finish my work for now. Here's the patch, including latest function default args. Regards, I've added a link to the commitfest page and stated that the patch is ready for a core member to review. Good work. David. -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future PostgreSQL version? On Fri, Dec 5, 2008 at 4:08 PM, Merlin Moncure [EMAIL PROTECTED] wrote: On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov [EMAIL PROTECTED] wrote: Hello. I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as a column type for another table (dt): CREATE TABLE ct (id INTEGER); CREATE TABLE dt (id INTEGER, c ct); INSERT INTO dt VALUES(1, '(666)'); SELECT * FROM dt; -- (1, '(666)') ALTER TABLE ct ADD COLUMN n INTEGER; SELECT * FROM dt; -- (1, '(666,)') You see, '(666,)' means that the new field is added successfully. But, if I declare ct as a COMPOSITE type (not a table), it is not permitted to ALTER this type (Postgres says that there are dependensies on ct). Why? Because of this there is no reason to ever use 'create type'always use 'create table'. 'alter type' can't add/remove columns anyways. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Assertion failure in new outer/semi/anti join code
Gregory Stark [EMAIL PROTECTED] writes: The following query causes an assertion failure on CVS head: SELECT * FROM (SELECT 1 AS i) AS a WHERE NOT EXISTS (SELECT 1 WHERE 1 i); Fixed, thanks for the report. Looks like it's assuming there's at least one relation on each side of the join. Yeah. I don't think we can remove that assumption easily; best to just not try to optimize this case. 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] ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
Dmitry Koterov wrote: Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future PostgreSQL version? It is not currently on the TODO list. --- On Fri, Dec 5, 2008 at 4:08 PM, Merlin Moncure [EMAIL PROTECTED] wrote: On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov [EMAIL PROTECTED] wrote: Hello. I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as a column type for another table (dt): CREATE TABLE ct (id INTEGER); CREATE TABLE dt (id INTEGER, c ct); INSERT INTO dt VALUES(1, '(666)'); SELECT * FROM dt; -- (1, '(666)') ALTER TABLE ct ADD COLUMN n INTEGER; SELECT * FROM dt; -- (1, '(666,)') You see, '(666,)' means that the new field is added successfully. But, if I declare ct as a COMPOSITE type (not a table), it is not permitted to ALTER this type (Postgres says that there are dependensies on ct). Why? Because of this there is no reason to ever use 'create type'always use 'create table'. 'alter type' can't add/remove columns anyways. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1268)
Bruce Momjian wrote: KaiGai Kohei wrote: Bruce Momjian wrote: KaiGai Kohei wrote: I don't oppose to elimination of --disable-row-acl options, however, it is not clear for me whether it should be unavoidable selection in the future, or not. Look at the existing configure options; we don't remove features via configure unless it is for some platform-specific reason. Please remove the configure option and make it always enabled. OK, I'll update it in the next patch set. Good. I assume the SQL-row security patch is not testable alone with out the rest of the patches, right? The minimum requirements are the 1st and 2nd patches. The first provides security hooks to PostgreSQL server program, and the other provides ones to pg_dump command. The 3rd, 4th and 5th are not necessary for the test purpose. First, let me say you have done an amazing job of producing patches for us, and your code quality is very high, especially considering the complexity of this code and your newness to our development process. My compliments to NEC, your employer. Also, I personally am excited about this code and what it will add to Postgres 8.4. I hate to ask for something else from you, but I am trying to figure out how we can proceed in reviewing and applying your additions. I am wondering if you can produce a patch that has the SE-Linux part separate so I can review the non-SE-Linux parts of the patch alone --- right now I am not 100% clear on what parts are always active as row-level SQL security and what needs SE-Linux to operate. I know this is an additional burden on you and if it is too much to ask, please tell me. All the SELinux specific part is stored within: - src/include/security/sepgsq.h - src/backend/security/sepgsql/* - Blocks enclosed by #if defined(HAVE_SELINUX) in src/include/security/pgace.h SELinux related codes are never invoked without pgace() hooks, so you can simply ignore the above files/parts when you are under the reviewing to non-SELinux parts. Rest of changes are commonly needed to manage security attribute and to inject security hooks. In all honesty, I hesitate to separate the patch again into two parts to be integrated later. I would be happy, if you suggested it a half year ago, because this feature was suggested as two separated patches in CommitFest:May. :( Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Polymorphic types vs. domains
The proximate cause of this complaint: http://archives.postgresql.org/pgsql-general/2008-12/msg00283.php seems to be that the polymorphic-type code doesn't consider a domain over an enum type to match an ANYENUM function argument. ISTM this is probably wrong: we need such a domain to act like its base type for matching purposes. There is an analogous problem with a domain over an array type failing to match ANYARRAY; conversely, such a domain is considered to match ANYNONARRAY which it likely should not. Comments? If this is agreed to be a bug, should we consider back-patching it? (I'd vote not, I think, because the behavioral change could conceivably break some apps that work now.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1268)
KaiGai Kohei wrote: I hate to ask for something else from you, but I am trying to figure out how we can proceed in reviewing and applying your additions. I am wondering if you can produce a patch that has the SE-Linux part separate so I can review the non-SE-Linux parts of the patch alone --- right now I am not 100% clear on what parts are always active as row-level SQL security and what needs SE-Linux to operate. I know this is an additional burden on you and if it is too much to ask, please tell me. All the SELinux specific part is stored within: - src/include/security/sepgsq.h - src/backend/security/sepgsql/* - Blocks enclosed by #if defined(HAVE_SELINUX) in src/include/security/pgace.h SELinux related codes are never invoked without pgace() hooks, so you can simply ignore the above files/parts when you are under the reviewing to non-SELinux parts. Rest of changes are commonly needed to manage security attribute and to inject security hooks. In all honesty, I hesitate to separate the patch again into two parts to be integrated later. I would be happy, if you suggested it a half year ago, because this feature was suggested as two separated patches in CommitFest:May. :( Thanks, that's what I needed to know. -- 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] contrib/pg_stat_statements 1202
On Tue, Dec 2, 2008 at 02:35, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Here is an update version of contrib/pg_stat_statements. Hello again! I was assigned to review this. Submission review: Is the patch in standard form? Yes Does it apply cleanly to current HEAD? Yes (with fuzz) Does it include reasonable tests, docs, etc? Yes Usability review: Does the patch actually implement that? Yes Do we want that? I think so Do we already have it? No Does it follow SQL spec, or the community-agreed behavior? Sure Are there dangers? No Have all the bases been covered? Yes Feature test: Does the feature work as advertised? Yes Are there corner cases the author has failed to consider? No Performance review Does the patch slow down simple tests? Does not seem to... (test.sql) select * from tenk1 a join tenk1 b using (unique1); (dual core machine, --enable-debug, --enable-cassert build) pgbench -c 2 -T60 -n -f test.sql HEAD: tps = 9.674423 PATCH: tps = 9.695784 If it claims to improve performance, does it? Does it slow down other things? Coding review: Does it follow the project coding guidelines? Yes Are there portability issues? No Will it work on Windows/BSD etc? Think so Are the comments sufficient and accurate? I think so Does it do what it says, correctly? Yes Does it produce compiler warnings? No Can you make it crash? No I'm not sure about the new counters in struct Instrumentation or the hooks (but did not see anything obviously wrong with them)... A commiter can better comment on those. Also find attached some very minor verbiage changes. If there is nothing else on your todo list for this Ill mark it as Ready for commiter on the wiki. *** a/contrib/pg_stat_statements/pg_stat_statements.c --- b/contrib/pg_stat_statements/pg_stat_statements.c *** *** 216,222 error: } /* ! * pgss_shutdown - Load statistics from file. */ static void pgss_startup(void) --- 216,222 } /* ! * pgss_startup - Load statistics from file. */ static void pgss_startup(void) *** a/doc/src/sgml/pgstatstatements.sgml --- b/doc/src/sgml/pgstatstatements.sgml *** *** 68,74 entrystructfieldcalls/structfield/entry entrytypebigint/type/entry entry/entry ! entryNumber of being executed/entry /row row --- 68,74 entrystructfieldcalls/structfield/entry entrytypebigint/type/entry entry/entry ! entryNumber of times executed/entry /row row -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs head initdb hangs on unixware
[EMAIL PROTECTED] writes: As you can see in attached initdb.log, it seems fsm_search_avail is called repeatedly and args are sort of looping... That's expected, since the system is inserting a lot of tuples successively. What it looks like to me is that the failing call is the first one where the initial test *doesn't* result in falling out immediately. So the probability is that there's something wrong with the code that descends the tree. Note that the all-zeroes pages in your dump are uninformative because none of the real FSM data has been written to disk yet. We can see from this trace that the code is dealing with not-all-zero pages. 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] ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
On Sun, Dec 7, 2008 at 7:57 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Dmitry Koterov wrote: Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future PostgreSQL version? It is not currently on the TODO list. Perhaps we could add it? It's been complained about more than once in this space. ...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] V2 of PITR performance improvement for 8.4
I understood your point. In the case of synchronous replication, because slave fails over when master crashes, there're no need to leave FPW from the beginning. In this case, only prefetch will work. Fujii's code at the slave looks very similar to pg_standby and pg_readahead will help in this case with no modification. 2008/12/4 Simon Riggs [EMAIL PROTECTED]: On Wed, 2008-12-03 at 14:22 +0900, Koichi Suzuki wrote: There's clearly a huge gain using prefetch, when we have full_page_writes = off. But that does make me think: Why do we need prefetch at all if we use full page writes? There's nothing to prefetch if we can keep it in cache. Agreed. This is why I proposed prefetch optional through GUC. So I'm wondering if we only need prefetch because we're using lesslog? If we integrated lesslog better into the new replication would we be able to forget about doing the prefetch altogether? In the case of lesslog, almost all the FPW is replaced with corresponding incremental log and recovery takes longer. Prefetch dramatically improve this, as you will see in the above result.To improve recovery time with FPW=off or FPW=on and lesslog=yes, we need prefetch. It does sound like it is needed, yes. But if you look at the architecture of synchronous replication in 8.4 then I don't think it makes sense any more. It would be very useful for the architecture we had in 8.3, but that time has gone. If we have FPW=on on primary then we will stream WAL with FPW to standby. There seems little point removing it *after* it has been sent, then putting it back again before we recover, especially when it causes a drop in performance that then needs to be fixed (by this patch). pg_lesslog allowed us to write FPW to disk, yet send WAL without FPW. So if we find a way of streaming WAL without FPW then this patch makes sense, but not until then. So far many people have argued in favour of using FPW=on, which was the whole point of pg_lesslog. Are we now saying that we would run the primary with FPW=off? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- -- Koichi Suzuki -- 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] visibility maps and heap_prune
On Sat, Dec 6, 2008 at 8:08 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: If you see a straightforward way, please submit a patch! Will do that. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] cvs head initdb hangs on unixware
Tom Lane wrote: [EMAIL PROTECTED] writes: As you can see in attached initdb.log, it seems fsm_search_avail is called repeatedly and args are sort of looping... That's expected, since the system is inserting a lot of tuples successively. Right. I suspect it was in the infinite loop yet. Try to run it for *much* longer (it'll probably take much longer than usual because it's printing all the debug stuff), until it gets stuck looping over the same pages in same relation. -- 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] Mostly Harmless: Welcoming our C++ friends
Andrew Dunstan wrote: The most serious problem AFAIK is that we use setjmp/longjmp, which I understand does not play at all nicely with C++ exceptions. Considering the complexity of the code and how it at times stretches the C standard to the point of cheating, I think anyone's three-item list of major problems is going to be much too short. -- 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] Windowing Function Patch Review - Standard Conformance
Hitoshi Harada wrote: It shows even though tuplestore trims its tuples and stays in memory rather than dumps them on files, the performance up is only 2 sec in 50 sec. So I concluded the optimization for row_number()/rank() etc doesn't pay for its more complexity in window function API. The bottleneck of the Window node origins from something else, like puttupleslot(), not Window node algorithm. Of course those issues should be tracked more precisely, for the window functions work I ignore them. The negative impact of not trimming the tuplestore comes from having to do I/O to write the tuples to temporary file. I would've expected that to show up with 400 MB table, but maybe that still fits comfortably in OS cache. Anyway, I would expect there to be a big drop in performance after the tuplestore no longer fits in cache, and trimming it would eliminate that. That said, we should try to get this committed ASAP, so I think we can live without the trimming for 8.4. -- 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
