Re: [HACKERS] Making hash indexes worthwhile
On Sun, Oct 4, 2009 at 7:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Janes jeff.ja...@gmail.com writes: I've played around a bit with hash indexes, and it seems to me that making them generally worthwhile will take (at least) reducing or entirely doing away with the heavy-wait locks. Concurrency is really the least of the issues for hash indexes. So far it's not clear that they're fast enough even in sequential use ... Do you know why that should be? I've done some work with gprof, and the results are pretty suspect, because the total gprof time adds up to only about 1/3 of the total time the backend spends on CPU (according to top), and I don't know where the unaccounted for time is going. But a good part of the accounted-for time seems to be associated with the locking system, even when there is only one active backend. Cheers, Jeff -- 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] Rules: A Modest Proposal
On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: I am not sure where that view implemenation is, but I doubt its stalled because of the rule system. It is. You can definitely create updatable views using rules. Sure you can, but they won't work in various significant corner cases. Search the archives for updatable views for details. -- 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] Rules: A Modest Proposal
On Sun, 2009-10-04 at 20:54 -0400, Alvaro Herrera wrote: While I don't agree with David Fetter's premise, I think rehashing how we handle VIEWs would be a good step towards updatable views. Right now, the implementation of that is stalled precisely because of the rule system. The way forward with updatable views is triggers on views. I was going to write something about that in the future. I haven't worked out all the details. But the select part of views will still need to be done with rules. -- 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] Privileges and inheritance
On Mon, 2009-10-05 at 12:15 +0900, KaiGai Kohei wrote: On the other hand, it also needs to check permission both of child table and its parents when we select data from a table with its parents, You can't do that anyway. -- 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] Privileges and inheritance
Peter Eisentraut wrote: On Mon, 2009-10-05 at 12:15 +0900, KaiGai Kohei wrote: On the other hand, it also needs to check permission both of child table and its parents when we select data from a table with its parents, You can't do that anyway. Sorry, I'm not clear why it is impossible. If we adopt the perspective that inherited columns are a part of the parent tables, it is quite natural to bypass permisson checks on the child tables, when we select data from the parent table. However, we also can select data from the child table which contains inherited columns from the parent table. In this case, it seems to me unnatural to bypass permission checks on the parent tables/columns, although it adopts the perspective. What I wanted to say is... For example) CREATE TABLE tbl_p (int a, int b); CREATE TABLE tbl_c (int x) INHERITS(tbl_p); SELECT a,b FROM tbl_p; -- It selects data from only tbl_p. It is reasonable to bypass checks on tbl_c. SELECT b,x FROM tbl_c; -- It selects data from tbl_p and tbl_c concurrently, if we consider the inherited columns are a part of the parent table. SELECT x FROM tbl_c;-- ??? In this case, I don't think it is necessary to check permissions on the parent table. Am I missing something? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Privileges and inheritance
On Sat, 2009-10-03 at 09:45 +0300, Peter Eisentraut wrote: We could use a GUC variable to ease the transition, perhaps like sql_inheritance = no | yes_without_privileges | yes The original way of doing things was quite useful if you wanted some people to be able to see history and others just see recent data. I don't think many people are aware of or take advantage of that, so your proposal does simplify things for many people. Would it not be better to offer this as a table-level option, with default of check-permission-on-parent-only? -- Simon Riggs www.2ndQuadrant.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] Privileges and inheritance
On Mon, 2009-10-05 at 16:27 +0900, KaiGai Kohei wrote: CREATE TABLE tbl_p (int a, int b); CREATE TABLE tbl_c (int x) INHERITS(tbl_p); SELECT a,b FROM tbl_p; -- It selects data from only tbl_p. It is reasonable to bypass checks on tbl_c. SELECT b,x FROM tbl_c; -- It selects data from tbl_p and tbl_c concurrently, if we consider the inherited columns are a part of the parent table. I think you need to distinguish between the definition of columns and the data in the columns. tbl_c has inherited the definition of the columns from tbl_p, but the data is part of tbl_c, not tbl_p. So there is not reason for this second query to ask tbl_p for permission, because it does not touch data in tbl_p at all. -- 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] Rules: A Modest Proposal
--On 5. Oktober 2009 09:51:29 +0300 Peter Eisentraut pete...@gmx.net wrote: The way forward with updatable views is triggers on views. I was going to write something about that in the future. I haven't worked out all the details. In the mentioned discussion there was already the notion of substitution rules. The notion of this pretty much applies to something like instead of statement triggers. AFAIR, the discussion came up with a proposal for some CURRENT OF-Syntax in rules, which creates some magic rule effectively avoiding the self join and substitute the original query with the WHERE-condition of the view appended. -- Thanks Bernd -- 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] Rules: A Modest Proposal
--On 4. Oktober 2009 21:37:45 -0400 Robert Haas robertmh...@gmail.com wrote: This is the last I remember hearing of it, which seems to suggest that only a week's worth of work (maybe a bit more for those of us who are not Tom Lane) is needed: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01746.php But maybe you have some other thoughts? The reason i didn't spent any time on this, is because i'm not sure that following the Rules path is the way to go. As Peter mentioned, an alternative (and pretty much the same way like other databases do), is to figure out how triggers on views can handle this. -- Thanks Bernd -- 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] Privileges and inheritance
Peter Eisentraut wrote: On Mon, 2009-10-05 at 16:27 +0900, KaiGai Kohei wrote: CREATE TABLE tbl_p (int a, int b); CREATE TABLE tbl_c (int x) INHERITS(tbl_p); SELECT a,b FROM tbl_p; -- It selects data from only tbl_p. It is reasonable to bypass checks on tbl_c. SELECT b,x FROM tbl_c; -- It selects data from tbl_p and tbl_c concurrently, if we consider the inherited columns are a part of the parent table. I think you need to distinguish between the definition of columns and the data in the columns. tbl_c has inherited the definition of the columns from tbl_p, but the data is part of tbl_c, not tbl_p. So there is not reason for this second query to ask tbl_p for permission, because it does not touch data in tbl_p at all. Yes, I can understand the second query selects data stored within only tbl_c in this case, not tbl_p, even if tbl_c inherits its definitions from the parent. However, this perspective may be inconsistent to the idea to bypass permission checks on the child (tbl_c) when we select data from the parent (tbl_p), because the first query also fetches data stored within the tbl_c, not only the tbl_p. IMO, if we adopt the perspective which considers the access control depends on the physical location, the current implementation works fine. However, this idea proposed a different perspective. It allows to bypass permission checks on the child tables, because the child has identical definition with its parent and these are a part of the parent table. If so, I think this perspective should be ensured without any exception. BTW, I basically think this perspective change is better. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Privileges and inheritance
On Mon, 2009-10-05 at 09:22 +0100, Simon Riggs wrote: On Sat, 2009-10-03 at 09:45 +0300, Peter Eisentraut wrote: We could use a GUC variable to ease the transition, perhaps like sql_inheritance = no | yes_without_privileges | yes The original way of doing things was quite useful if you wanted some people to be able to see history and others just see recent data. I don't think many people are aware of or take advantage of that, so your proposal does simplify things for many people. Wouldn't that look something like data -- empty data_recent INHERITS (data) data_old INHERITS (data) data_ancient INHERITS (data) GRANT ... ON data_recent TO A GRANT ... ON data_old TO B I guess you could also do data -- recent data data_old INHERITS (data) data_ancient INHERITS (data) GRANT ... ON data TO A GRANT ... ON data_old TO B And then A, who has only access to the recent data, would always have to use ONLY data to be able to do anything. That would be a pretty weird setup. The workaround is to change it to the setup above, which you can do with a few renames. -- 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] Privileges and inheritance
On Mon, 2009-10-05 at 12:30 +0300, Peter Eisentraut wrote: On Mon, 2009-10-05 at 09:22 +0100, Simon Riggs wrote: On Sat, 2009-10-03 at 09:45 +0300, Peter Eisentraut wrote: We could use a GUC variable to ease the transition, perhaps like sql_inheritance = no | yes_without_privileges | yes The original way of doing things was quite useful if you wanted some people to be able to see history and others just see recent data. I don't think many people are aware of or take advantage of that, so your proposal does simplify things for many people. Wouldn't that look something like data -- empty data_recent INHERITS (data) data_old INHERITS (data) data_ancient INHERITS (data) GRANT ... ON data_recent TO A GRANT ... ON data_old TO B I guess you could also do data -- recent data data_old INHERITS (data) data_ancient INHERITS (data) GRANT ... ON data TO A GRANT ... ON data_old TO B And then A, who has only access to the recent data, would always have to use ONLY data to be able to do anything. That would be a pretty weird setup. The workaround is to change it to the setup above, which you can do with a few renames. If you use multiple inheritance it all works as I described. top level: data-template main tables: data, data-recent both inherit from data-template all partitions inherit from data only recent partitions inherit from data-recent grants are issued on data and data-recent Now that I think about it more, I want the change you describe but don't think its a system-wide setting. You may have PostgreSQL inheritance apps next door to partitioning apps. The right place to fix this is when we implement partitioning syntax, so we can set a flag saying make permissions easier for partitions. -- Simon Riggs www.2ndQuadrant.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] Privileges and inheritance
On Mon, 2009-10-05 at 10:47 +0100, Simon Riggs wrote: top level: data-template main tables: data, data-recent both inherit from data-template all partitions inherit from data only recent partitions inherit from data-recent grants are issued on data and data-recent I don't see where the problem is here. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Triggers on columns
Tom Lane t...@sss.pgh.pa.us writes: In cases where you do have related functions, I suggest having SQL-callable V1 functions that absorb their arguments in this style, and then have them call a common subroutine that's a plain C function. Unless you have high performance requirements, IME. Avoiding the SQL function call is indeed measurable, even if very low in the radar. Regards, -- dim You have a nice quote about the sins we'd accept/follow in the name of performance, but google will about only find sin/cos etc material... -- 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] Privileges and inheritance
On Mon, 2009-10-05 at 13:06 +0300, Peter Eisentraut wrote: On Mon, 2009-10-05 at 10:47 +0100, Simon Riggs wrote: top level: data-template main tables: data, data-recent both inherit from data-template all partitions inherit from data only recent partitions inherit from data-recent grants are issued on data and data-recent I don't see where the problem is here. In your last post you said it was necessary to use ONLY to address the required partitions and so setup would be weird. I am showing that this is not required and the setup is smooth. The main point though is that this should not be a system-wide setting. I agree with your overall intention, just not your specific solution. We need improvements in partitioning, not minor tweaks. It seems much better to me to hack out the portion of the last partitioning patch (Kedar's) so that we just support new syntax without any underlying changes (yet). We can mark a table as being partitioned and for such tables skip the permission checks - no new GUC. If you can push that change through as initial infrastructure then others can work on internals - which were not close in that last patch. -- Simon Riggs www.2ndQuadrant.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] Hot Standby on git
On Mon, 2009-09-28 at 11:25 +0300, Heikki Linnakangas wrote: Heikki Linnakangas wrote: Per Simon's request, for the benefit of the archive, here's all the changes I've done on the patch since he posted the initial version for review for this commitfest as incremental patches. This is extracted from my git repository at git://git.postgresql.org/git/users/heikki/postgres.git. Further fixes extracted from above repository attached.. I've applied changes on all these patches apart from 0006-... which has some dependencies on earlier work I'm still looking at. -- Simon Riggs www.2ndQuadrant.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] Hot Standby on git
On Sun, 2009-09-27 at 13:57 +0300, Heikki Linnakangas wrote: Per Simon's request, for the benefit of the archive, here's all the changes I've done on the patch since he posted the initial version for review for this commitfest as incremental patches. This is extracted from my git repository at git://git.postgresql.org/git/users/heikki/postgres.git. I'm working my way through these changes now. 1, 2, 15 and 16 applied. We discussed briefly your change 0011-Replace-per-proc-counters-of-loggable-locks-with-per.patch. I don't see how that helps at all. The objective of lock counters was to know if we can skip acquiring an LWlock on all lock partitions. This change keeps the lock counters yet acquires the locks we were trying to avoid. This change needs some justification since it is not a bug fix. -- Simon Riggs www.2ndQuadrant.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] Review of SQLDA support for ECPG
Hi,
thank you very much for the review.
Noah Misch írta:
I took a look at 2-pg85-sqlda-10-ctxdiff.patch. Starting from CVS HEAD of
roughly 2009-10-03 05:00 UTC, prerequisite patches 1a-1h applied cleanly.
2-pg85-sqlda hit a trivial whitespace reject in ecpg.trailer along with a more
substantive reject at ecpg.addons:407 (FetchStmtMOVEfetch_args). Fixing it up
by hand leads to my first question - why did the transition from
`opt_ecpg_into'
to `opt_ecpg_fetch_into' affect FETCH FORWARD and not FETCH BACKWARD?
This was a total oversight coming from the previous
dynamic cursorname patch. *That* was buggy as it didn't
have the two de-factorized rules for FETCH BACKWARD.
When I did the fine-grained split-up, I fixed that but I didn't
test my SQLDA patch after the new dynamic cursorname
patches.
I will post a new patch for SQLDA and for all others that need
updating.
The main test suite acquired no regressions, but I get failures in two tests
of
the ecpg test suite (make -C src/interfaces/ecpg/test check). I attach
regression.{out,diff} and postmaster.log from the test run. The abort in
sqlda.pgc looks like the interesting failure, but I exhausted time with which
to
dig into it further. preproc/cursor.pgc creates (and ideally drops) the same
table `t1' as compat_informix/{sqlda,cursor}.pgc, so a crash in either of the
others makes it fail too. Could they all use temp tables, use different table
names, or `DROP TABLE IF EXISTS t1' first?
Do those logs suggest the cause of the sqlda.pgc failure? If not, I will look
into it further. Otherwise, I'm happy to review a future iteration of the
patch.
No, this is not a real error. I have run into this as well,
which is quickly solved if you execute make install
before running make check under the ecpg directory.
I guess you already have an installed PG tree at the same
prefix as where you have pointed the new one with the
SQLDA patch applied. Another solution may be to use
a different prefix for the SQLDA source tree.
I start to think that the same remedy would be needed here
as the contrib subdirectory: make installcheck is a mental
note the you test the installed libraries, not the freshly compiled
ones under the source directory.
As a side note, with patch 1* but not patch 2, test_informix entered an
infinite
loop with this error:
ERROR: syntax error at or near c at character 15
STATEMENT: fetch forward c
Do you mean that you applied all the split-up patches posted
for the dynamic cursorname extension? I didn't get this error.
What did you do exactly?
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
--
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] Rules: A Modest Proposal
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 At the moment, user-accessible RULEs have, as far as I know, just two sane uses: * Writing to VIEWs * Routing writes to partitions Maybe you need a larger clientele list, because I still run up against RULEs in production environments that don't fit into the categories above. Here's one I came across just a couple weeks ago. Names changed for privacy: CREATE RULE update_other_table AS ON INSERT TO myschema.mytable DO ALSO INSERT INTO myschema.othertable (col1,col2,col3) VALUES (NEW.col1, NEW.col2, NEW.col3); Could this be done with a trigger? Yes, but on the plus rules side: * It's faster * It's easier to write * It's immediately viewable as to what is going on with a \d mytable * Dropping it won't leave an unused function around * We can still do ALTER TABLE DISABLE TRIGGER ALL I can give more examples, if you like, but removing a major feature of Postgres with no real justificatgion seems a bit hasty, to say the least. They're mostly a foot-gun. Lots of things in Postgres could be considered potential foot guns. Frankly, I don't think rules are even near the top of such a list. Can you give examples of rule foot guns? - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 200910050758 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkrJ5wUACgkQvJuQZxSWSsjS7ACeMl8YfE38aVjnhZX3/gp8Ffgq tZsAoLQPaPxS5ky4SZ8yXMdKNTWN1ZVX =RmyV -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
Greg Sabino Mullane wrote: Could this be done with a trigger? Yes, but on the plus rules side: * It's faster * It's easier to write * It's immediately viewable as to what is going on with a \d mytable * Dropping it won't leave an unused function around * We can still do ALTER TABLE DISABLE TRIGGER ALL I can give more examples, if you like, but removing a major feature of Postgres with no real justificatgion seems a bit hasty, to say the least. Agreed, here is another rules example that logs table changes to a log table: http://www.postgresql.org/files/documentation/books/aw_pgsql/node124.html -- Bruce Momjian br...@momjian.ushttp://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] Inappropriate failure conditions in foreign_data regression test
By chance I noticed that the foreign_data regression test fails if run in an installation where bob is a live user. It appears to be assuming that half a dozen other fairly common names don't belong to real users, either. Could we make this a little less fragile? Maybe Attached is a patch that attempts to fix that -- user mappings are now created for regress_test_missing_role instead of bob or baz. call them no_such_user_N. Or for that matter do we really need quite so many tests of the same error condition? The code paths leading to those error conditions are different (or at least they should be). The initial aim was to get as much test coverage as possible, but I guess the tests could be simplified if this is turning out to be a maintenance burden regards, Martin *** a/src/test/regress/expected/foreign_data.out --- b/src/test/regress/expected/foreign_data.out *** *** 524,531 List of user mappings (0 rows) -- CREATE USER MAPPING ! CREATE USER MAPPING FOR baz SERVER s1; -- ERROR ! ERROR: role baz does not exist CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR ERROR: server s1 does not exist CREATE USER MAPPING FOR current_user SERVER s4; --- 524,531 (0 rows) -- CREATE USER MAPPING ! CREATE USER MAPPING FOR regress_test_missing_role SERVER s1; -- ERROR ! ERROR: role regress_test_missing_role does not exist CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR ERROR: server s1 does not exist CREATE USER MAPPING FOR current_user SERVER s4; *** *** 565,572 RESET ROLE; (7 rows) -- ALTER USER MAPPING ! ALTER USER MAPPING FOR bob SERVER s4 OPTIONS (gotcha 'true'); -- ERROR ! ERROR: role bob does not exist ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR ERROR: server ss4 does not exist ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true');-- ERROR --- 565,572 (7 rows) -- ALTER USER MAPPING ! ALTER USER MAPPING FOR regress_test_missing_role SERVER s4 OPTIONS (gotcha 'true'); -- ERROR ! ERROR: role regress_test_missing_role does not exist ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR ERROR: server ss4 does not exist ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true');-- ERROR *** *** 595,608 RESET ROLE; (7 rows) -- DROP USER MAPPING ! DROP USER MAPPING FOR bob SERVER s4;-- ERROR ! ERROR: role bob does not exist DROP USER MAPPING FOR user SERVER ss4; ERROR: server ss4 does not exist DROP USER MAPPING FOR public SERVER s7; -- ERROR ERROR: user mapping public does not exist for the server ! DROP USER MAPPING IF EXISTS FOR bob SERVER s4; ! NOTICE: role bob does not exist, skipping DROP USER MAPPING IF EXISTS FOR user SERVER ss4; NOTICE: server does not exist, skipping DROP USER MAPPING IF EXISTS FOR public SERVER s7; --- 595,608 (7 rows) -- DROP USER MAPPING ! DROP USER MAPPING FOR regress_test_missing_role SERVER s4; -- ERROR ! ERROR: role regress_test_missing_role does not exist DROP USER MAPPING FOR user SERVER ss4; ERROR: server ss4 does not exist DROP USER MAPPING FOR public SERVER s7; -- ERROR ERROR: user mapping public does not exist for the server ! DROP USER MAPPING IF EXISTS FOR regress_test_missing_role SERVER s4; ! NOTICE: role regress_test_missing_role does not exist, skipping DROP USER MAPPING IF EXISTS FOR user SERVER ss4; NOTICE: server does not exist, skipping DROP USER MAPPING IF EXISTS FOR public SERVER s7; *** a/src/test/regress/sql/foreign_data.sql --- b/src/test/regress/sql/foreign_data.sql *** *** 204,210 DROP SERVER s3 CASCADE; \deu -- CREATE USER MAPPING ! CREATE USER MAPPING FOR baz SERVER s1; -- ERROR CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR CREATE USER MAPPING FOR current_user SERVER s4; CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate --- 204,210 \deu -- CREATE USER MAPPING ! CREATE USER MAPPING FOR regress_test_missing_role SERVER s1; -- ERROR CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR CREATE USER MAPPING FOR current_user SERVER s4; CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate *** *** 228,234 RESET ROLE; \deu -- ALTER USER MAPPING ! ALTER USER MAPPING FOR bob SERVER s4 OPTIONS (gotcha 'true'); -- ERROR ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true');-- ERROR ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test');-- ERROR --- 228,234 \deu -- ALTER USER MAPPING ! ALTER USER MAPPING FOR regress_test_missing_role SERVER s4 OPTIONS
Re: [HACKERS] commented out para in docs
Andrew Dunstan and...@dunslane.net wrote: We have this para in the CREATE TABLE docs, commented out Surely we should either include it or remove it. +1 If it's deleted, it'll still be in CVS history if someone wants it -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] first-draft patch for aggregate ORDER BY
What it does:
# select array_agg(b order by a)
from (values (3,'foo'),(2,'bar'),(1,'baz')) v(a,b);
array_agg
---
{baz,bar,foo}
(1 row)
What it doesn't do:
- no docs or regression tests yet
- no support for agg(... ORDER BY ...) OVER window
(which the spec does allow)
Requires initdb. Beware of bugs. Slippery when wet.
--
Andrew (irc:RhodiumToad)
aorder-20091005.patch.gz
Description: aggregate ordering patch
--
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] Making hash indexes worthwhile
Jeff Janes jeff.ja...@gmail.com writes: Do you know why that should be? I've done some work with gprof, and the results are pretty suspect, because the total gprof time adds up to only about 1/3 of the total time the backend spends on CPU (according to top), and I don't know where the unaccounted for time is going. Are you sure that gprof is delivering trustworthy numbers at all? I've seen cases where it was consistently mis-timing things. https://bugzilla.redhat.com/show_bug.cgi?id=151763 Admittedly that was an old Linux version, but ... 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] Rules: A Modest Proposal
Greg == Greg Sabino Mullane g...@turnstep.com writes: They're mostly a foot-gun. Greg Lots of things in Postgres could be considered potential foot Greg guns. Frankly, I don't think rules are even near the top of Greg such a list. Can you give examples of rule foot guns? There are so many it's hard to know where to start. Here are a couple of the more common ones: 1) any reference in an insert rule to NEW.col where col has a volatile default, or the expression in the insert statement was volatile, or the expression's value is changed by the insert, will do the wrong thing: create table t (a integer); create table t_log (a integer); create rule t_ins AS ON insert TO t do also insert into t_log values (NEW.a); insert into t values (floor(random()*1000)::integer); select * from t; a 33 (1 row) select * from t_log; a - 392 (1 row) (think nextval or uuid_generate_* for more realistic examples) 2) any rule with multiple actions, each action is affected by the results of the previous ones. A classic example of this is in the use of OLD in delete or update rules; OLD _does not return a row_ if a previous action in the rule deleted the row or updated it so that it no longer matches. -- Andrew (irc:RhodiumToad) -- 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] Rules: A Modest Proposal
On Mon, Oct 05, 2009 at 02:53:56PM +0100, Andrew Gierth wrote: Here are a couple of the more common ones: 1) any reference in an insert rule to NEW.col where col has a volatile default, or the expression in the insert statement was volatile, or the expression's value is changed by the insert, will do the wrong thing: ISTM it may be possible to use the new WITH construct here. So the rule evaluation for the following create table t (a integer); create table t_log (a integer); create rule t_ins AS ON insert TO t do also insert into t_log values (NEW.a); insert into t values (floor(random()*1000)::integer); becomes something like: WITH NEW AS ( insert into t values (floor(random()*1000)::integer); RETURNING * ) insert into t_log values (NEW.a); Would this not have the required semantics? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] Privileges and inheritance
Simon Riggs si...@2ndquadrant.com writes: On Mon, 2009-10-05 at 13:06 +0300, Peter Eisentraut wrote: I don't see where the problem is here. In your last post you said it was necessary to use ONLY to address the required partitions and so setup would be weird. I am showing that this is not required and the setup is smooth. Peter is right and you are wrong: this setup STILL needs ONLY, unless permissions are in sync with inheritance, ie, every child has the union of its parents' permissions. It would work at least as well under Peter's proposal as with the existing behavior. The main point though is that this should not be a system-wide setting. No, it should be a flat-out behavioral change, no setting anywhere. I have never seen an example where the current behavior is actually useful, because of precisely the point that you'd have to use ONLY to avoid permissions errors unless you have granted permissions on all children of each parent. 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] Hot Standby on git
Simon Riggs si...@2ndquadrant.com writes: I don't see how that helps at all. The objective of lock counters was to know if we can skip acquiring an LWlock on all lock partitions. This change keeps the lock counters yet acquires the locks we were trying to avoid. This change needs some justification since it is not a bug fix. [ scratches head ... ] Why is hot standby messing with this sort of thing at all? It sounds like a performance optimization that should be considered separately, and *later*. 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] Privileges and inheritance
On Mon, 2009-10-05 at 10:14 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Mon, 2009-10-05 at 13:06 +0300, Peter Eisentraut wrote: I don't see where the problem is here. In your last post you said it was necessary to use ONLY to address the required partitions and so setup would be weird. I am showing that this is not required and the setup is smooth. Peter is right and you are wrong: this setup STILL needs ONLY, unless permissions are in sync with inheritance, ie, every child has the union of its parents' permissions. It would work at least as well under Peter's proposal as with the existing behavior. What I proposed works, so perhaps we are talking about different things. If you wish to see all data you grant access to parent_full, if you wish to see recent data you grant access to parent_partial. The partitions can then be given access to the various users. -- Simon Riggs www.2ndQuadrant.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] Rules: A Modest Proposal
Martijn van Oosterhout klep...@svana.org writes: ISTM it may be possible to use the new WITH construct here. So the rule evaluation for the following create table t (a integer); create table t_log (a integer); create rule t_ins AS ON insert TO t do also insert into t_log values (NEW.a); insert into t values (floor(random()*1000)::integer); becomes something like: WITH NEW AS ( insert into t values (floor(random()*1000)::integer); RETURNING * ) insert into t_log values (NEW.a); Would this not have the required semantics? Interesting idea, but it's not clear how to make it work with multiple DO ALSO rules, nor with conditional DO INSTEAD rules. 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] Rules: A Modest Proposal
On Mon, Oct 05, 2009 at 10:32:53AM -0400, Tom Lane wrote: Martijn van Oosterhout klep...@svana.org writes: WITH NEW AS ( insert into t values (floor(random()*1000)::integer); RETURNING * ) insert into t_log values (NEW.a); Would this not have the required semantics? Interesting idea, but it's not clear how to make it work with multiple DO ALSO rules, nor with conditional DO INSTEAD rules. Well, my (possibly naive) view is: - Multiple DO ALSO rules seem easy. There is a patch in the works which makes INSERT/UPDATE/DELETE into proper node types so they can actually appear in the WITH clause above. With a minor extension you could create a MultipleStatement node type which merely runs each substatement, like Append, but for plans. - Conditional DO INSTEAD rules are brain benders. Logically, I think they split the plan in two, one with the condition, one with the negative of the condition. So *maybe* they could also be handled by such a MultipleStatement node but then... I get visions of people writing a SELECT rule with a conditional DELETE statement with RETURNING *. Then, SELECTing the table would return everything but conditionally DELETE some rows. Something like: WITH OLD AS (SELECT * FROM foo) MULTISTATEMENT( SELECT * FROM OLD WHERE condition; DELETE FROM OLD WHERE NOT condition RETURNING *; ) As for actual implementation it seems doable, but I may be being impossibly naive. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] 8.5 TODO: any info on Create dump tool for write-ahead logs... in PITR section (1.4)?
On Sun, Oct 4, 2009 at 10:28 PM, Fujii Masao masao.fu...@gmail.com wrote: I think that xlogdump (http://xlogviewer.projects.postgresql.org/) is the first step to address that TODO item. Though I'm not sure if the xlogdump project is still active. I believe it has been dead for quite awhile now. Though, Tom may still maintain his own xlogdump. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Rules: A Modest Proposal
On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: I am not sure where that view implemenation is, but I doubt its stalled because of the rule system. It is. You can definitely create updatable views using rules. Sure you can, but they won't work in various significant corner cases. Search the archives for updatable views for details. I don't even want updatable views! I'm looking through those archives and its vague what killed them, but bad rules are definitely part of it. However, that doesn't mean you ditch the rule system because it didn't work for this particular situation. Maybe you could highlight some messages that point to the precise corner cases that make rules so bad? I would expect these corner cases would have nothing to do with updatable views, since they are such a bad idea to have automatically implemented. -- --Dan -- 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] Rules: A Modest Proposal
Dan Colish wrote: On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: You can definitely create updatable views using rules. Sure you can, but they won't work in various significant corner cases. Search the archives for updatable views for details. I don't even want updatable views! Why would you argue that point? They are specified in the SQL standard somewhere. -- 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] COPY enhancements
Hi Selena, This is my first pass at the error logging portion of this patch. I'm going to take a break and try to go through the partitioning logic as well later this afternoon. caveat: I'm not familiar with most of the code paths that are being touched by this patch. Overall: * I noticed '\see' included in the comments in your code. These should be removed. Should we avoid any doxygen tags in general in the Postgres code? * The regression is failling, as Jeff indicated, and I didn't figure out why yet either. Hopefully will have a look closer this afternoon. Let me know if the response I sent to Jeff works for you. Comments: * copy.c: Better formatting, maybe rewording needed for comment starting on line 1990. Some of the bad formatting has been left on purpose to minimize the size of the patch otherwise there would have been many tab/white spaces changes due to the indentation in the PG_TRY blocks. I suggest that whoever is going to commit the code runs pg_indent or I can fix the formatting once the reviewing is done. ** Maybe say: Check that errorData-sqlerrcode only logged tuples that are malformed. This ensures that we let other errors pass through. Ok. * copy.c: line: 2668 - need to fix that comment :) (/* Regular code */) -- this needs to be more descriptive of what is actually happening. We fell through the partitioning logic, right, and back to the default behavior? Yes. * copy.c: line 2881, maybe say instead: Mark that we have not read a line yet. This is necessary so that we can perform error logging on complete lines only. Ok. Formatting: * copy.c: whitespace is maybe a little off at line: 2004-2009 * NEW FILES: src/backend/utils/misc/errorlogging.c errorlogging.h need headers I was not sure what is auto-generated by SVN commit scripts. I'd be happy to add headers. Is there a specification somewhere or should I just copy/paste from another file? Code: * copy.c: line 1990 - cur_lineno_str[11] related: why is this conversion necessary? (sorry if that is a dumb question) This conversion is necessary if you log in the error table the index of the row that is causing the error (this is what is logged by default in ERROR_LOGGING_KEY). * copy.c: line 2660 - what if we are error logging for copy? Does this get logged properly? Yes, this is in a try/catch block that performs error logging. * errorlogging.c: Noticed the comment at 503 - 'note that we always enable wal logging'.. Thinking this through - the reasoning is that you won't be rolling back the error logging no matter what, right? I think that this was the original idea but we should probably rollback the error logging if the command has been rolled back. It might be more consistent to use the same hi_options as the copy command. Any idea what would be best? * src/include/commands/copy.c and copy.h: struct CopyStateData was moved from copy.c to copy.h; this made sense to me, just noting. That move made it a little tricky to find the changes that were made. There were 10 items added. Yes, patch can be a little bit lost when you move a big data structure like this one. ** super nit pick: 'readlineOk' uses camel-case instead of underscores like the rest of the new variables Yes, queryDesc also has camel-case. I will fix readlineOk. * errorlogging.c: could move currentCommand pg_stat call in Init routine into MalformedTupleIs, or maybe move the setting of that parameter into the Init routine for consistency's sake. The advantage of calling pg_stat in InitializeErrorLogging is that it is evaluated only once for the entire copy command (and it's not going to change during the execution of the command). I am not sure to understand what your second suggestion is since currentCommand is set and initialized in Init. Documentation: * doc/src/sgml/ref/copy.sgml: line 313: 'failif' needs a space ok ** Also: The error table log examples have relations shown in a different order than the actual CREATE TABLE declaration in the code. The order of the columns does not really matter but for consistency sake we can put the same order. * src/test/regress/sql/copyselect.sql: Format of options changed.. added parenthesis. Is this change documented elsewhere? (sorry if I just missed this in the rest of the thread/patch) Yes this has already been committed by Tom. The new format of options has been introduced just before this patch. I am attaching a revised version of the patch. Emmanuel -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com aster-copy-newsyntax-patch-8.5v6context.txt.gz Description: GNU Zip compressed data -- 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] Rules: A Modest Proposal
On Mon, Oct 05, 2009 at 11:28:13AM -0400, Alvaro Herrera wrote: Dan Colish wrote: On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: You can definitely create updatable views using rules. Sure you can, but they won't work in various significant corner cases. Search the archives for updatable views for details. I don't even want updatable views! Why would you argue that point? They are specified in the SQL standard somewhere. I do not really think updatable views are needed. Maybe when the standard was written things are different; I guess you're talking about 2003. Just because something is in a standard, doesnt mean it has to be implemented. As long as you don't implement something outside of the standard, I do not have an issue. -- --Dan -- 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] Rules: A Modest Proposal
2009/10/5 Dan Colish d...@unencrypted.org: On Mon, Oct 05, 2009 at 11:28:13AM -0400, Alvaro Herrera wrote: Dan Colish wrote: On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: You can definitely create updatable views using rules. Sure you can, but they won't work in various significant corner cases. Search the archives for updatable views for details. I don't even want updatable views! Why would you argue that point? They are specified in the SQL standard somewhere. I do not really think updatable views are needed. Maybe when the standard was written things are different; I guess you're talking about 2003. Just because something is in a standard, doesnt mean it has to be implemented. As long as you don't implement something outside of the standard, I do not have an issue. Updatable views are important for porting enterprise applications. I thing, so it has a sense. Regards Pavel Stehule -- --Dan -- 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] Rules: A Modest Proposal
On Mon, Oct 05, 2009 at 11:28:13AM -0400, Alvaro Herrera wrote: Dan Colish wrote: On Mon, Oct 05, 2009 at 09:50:18AM +0300, Peter Eisentraut wrote: On Sun, 2009-10-04 at 18:24 -0700, Dan Colish wrote: You can definitely create updatable views using rules. Sure you can, but they won't work in various significant corner cases. Search the archives for updatable views for details. I don't even want updatable views! Why would you argue that point? They are specified in the SQL standard somewhere. Feature T111, described in sections 15.9, 15.12 and 15.15 of SQL:2008, in particular. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
Stephen Frost sfr...@snowman.net wrote: Do we have a patch which implements the necessary mechanics to replace RULEs, even for the specific situations you list? Until then, I don't think there's much to discuss. I thought that until we had discussion and consensus it was premature to start working on a patch -Kevin -- 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] Rules: A Modest Proposal
* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: Stephen Frost sfr...@snowman.net wrote: Do we have a patch which implements the necessary mechanics to replace RULEs, even for the specific situations you list? Until then, I don't think there's much to discuss. I thought that until we had discussion and consensus it was premature to start working on a patch In general that's true, but in this case we're talking about removing a pretty major feature and replacing it with something else. We havn't got the 'something else' hammered out yet (or so it sounds to me..) and I have doubts that we'd be able to really make a call on removing RULEs until we know and have the specifics of what's replacing it. That might be possible to do without a patch, but it requires a great deal more documentation, planning, and information in general before a decision could be made. Specifically, what people will actually do to implement the things that RULEs used to provide. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] COPY enhancements
Emmanuel, I think that this was the original idea but we should probably rollback the error logging if the command has been rolled back. It might be more consistent to use the same hi_options as the copy command. Any idea what would be best? Well, if we're logging to a file, you wouldn't be *able* to roll them back. Also, presumbly, if you abort a COPY because of errors, you probably want to keep the errors around for later analysis. No? --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
Re: [HACKERS] Rules: A Modest Proposal
Andrew, 1) any reference in an insert rule to NEW.col where col has a volatile default, or the expression in the insert statement was volatile, or the expression's value is changed by the insert, will do the wrong thing: Is this different from triggers? 2) any rule with multiple actions, each action is affected by the results of the previous ones. A classic example of this is in the use of OLD in delete or update rules; OLD _does not return a row_ if a previous action in the rule deleted the row or updated it so that it no longer matches. I know this is not any different from triggers which cascade. David's basic proposal, as I understand, is to remove RULEs and replace them with triggers on views. However, there are *lots* of ways to screw yourself up with triggers as well. For example see my previously reported bug about referential integrity and self-triggers. Triggers also have potential security issues which rules lack. So while rules are hard to use and easy to mess up, so are triggers. So while an (arguable) problem is being pointed out, no real solution is being proposed. With some irony, this discussion came about starting with the writable CTE patch ... which is a truly massive foot-gun for someone who doesn't know how to write CTEs. Huge opportunities there for a new DBA to either lock up the server or overwrite half their database. Does that mean we shouldn't do them? No. I happen to like having RULEs in my arsenal of tricks for getting the database to do Nifty Stuff. I've always considered them advanced database programming, and not for beginners. But that describes a lot of PostgreSQL functionality: security definer functions, run-time DDL generation, SQL/MED, untrusted languages, user-defined types and operators. But it's these advanced features which are what makes PostgreSQL interesting as a database. --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
Re: [HACKERS] Privileges and inheritance
Simon, We could use a GUC variable to ease the transition, perhaps like sql_inheritance = no | yes_without_privileges | yes The original way of doing things was quite useful if you wanted some people to be able to see history and others just see recent data. I don't think many people are aware of or take advantage of that, so your proposal does simplify things for many people. Would it not be better to offer this as a table-level option, with default of check-permission-on-parent-only? No, I don't think so. The original way *wasn't* actually useful if you wanted to differentiate between which partitions people could see. Example: You partition the sales table geographically. You want salespeople to only be able to see their own geo, but management to be able to see all: role staff manager inherits staff sales_USA inherits staff sales_CAN inherits staff sales_EUR inherits staff master table sales grant SELECT: staff sales_CAN inherits salesgrant SELECT: manager, sales_CAN sales_USA inherits salesgrant SELECT: manager, sales_USA sales_EUR inherits salesgrant SELECT: manager, sales_EUR So, then a USA-role salesperson does SELECT sum(gross) FROM sales;. What happens? A permissions error. *Not* the desired seeing only the USA data. In order for a user which privs on only some partitions to see the data in those partitions, that user needs to query the partitions directly. The proposed patch would not change that. The only thing it would change is that DBAs would need to be careful to be restrictive about permissions granted on the master table. --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
Re: [HACKERS] dblink memory leak
Tom Lane wrote: Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: I think PG_TRY blocks are not enough, too. PG_TRY requires a statement block, but we need to return from dblink functions per tuple. That bit will have to be undone. There is no reason for dblink not to return a tuplestore. That's a really good point. It was originally written thinking we would eventually be able to stream tuples rather than materialize them, but given that many years have passed and we are no closer (I believe) to a true streaming mode for SRFs, a tuplestore would be much cleaner. Given that change, is there even any leak to even worry about? As long as the PGresult object is created in the correct memory context, it ought to get cleaned up automatically, no? I can't promise to make this change before 15 October, but I will get to it before the end of CF3. Joe signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Privileges and inheritance
On Mon, 2009-10-05 at 11:45 +0100, Simon Riggs wrote: On Mon, 2009-10-05 at 13:06 +0300, Peter Eisentraut wrote: On Mon, 2009-10-05 at 10:47 +0100, Simon Riggs wrote: top level: data-template main tables: data, data-recent both inherit from data-template all partitions inherit from data only recent partitions inherit from data-recent grants are issued on data and data-recent I don't see where the problem is here. In your last post you said it was necessary to use ONLY to address the required partitions and so setup would be weird. I am showing that this is not required and the setup is smooth. Well, you posted this: top level: data-template main tables: data, data-recent both inherit from data-template all partitions inherit from data only recent partitions inherit from data-recent grants are issued on data and data-recent But this is too vague for me to make out who can read what and what would change under the proposed change and why that would be a problem. -- 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] Privileges and inheritance
On Mon, 2009-10-05 at 10:27 -0700, Josh Berkus wrote: Simon, We could use a GUC variable to ease the transition, perhaps like sql_inheritance = no | yes_without_privileges | yes The original way of doing things was quite useful if you wanted some people to be able to see history and others just see recent data. I don't think many people are aware of or take advantage of that, so your proposal does simplify things for many people. Would it not be better to offer this as a table-level option, with default of check-permission-on-parent-only? No, I don't think so. The original way *wasn't* actually useful if you wanted to differentiate between which partitions people could see. Example: You partition the sales table geographically. You want salespeople to only be able to see their own geo, but management to be able to see all: role staff manager inherits staff sales_USA inherits staff sales_CAN inherits staff sales_EUR inherits staff master table salesgrant SELECT: staff sales_CAN inherits sales grant SELECT: manager, sales_CAN sales_USA inherits sales grant SELECT: manager, sales_USA sales_EUR inherits sales grant SELECT: manager, sales_EUR So, then a USA-role salesperson does SELECT sum(gross) FROM sales;. What happens? A permissions error. *Not* the desired seeing only the USA data. In order for a user which privs on only some partitions to see the data in those partitions, that user needs to query the partitions directly. The proposed patch would not change that. The only thing it would change is that DBAs would need to be careful to be restrictive about permissions granted on the master table. OK, make the change. A small addition though, please. This functionality has been available since 8.1 and changing things could cause existing people's scripts to fail when they upgrade. If we make this change then we should make sure that explicitly GRANTing a permission on the child tables does not fail. -- Simon Riggs www.2ndQuadrant.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] Hot Standby on git
On Mon, 2009-10-05 at 10:19 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: I don't see how that helps at all. The objective of lock counters was to know if we can skip acquiring an LWlock on all lock partitions. This change keeps the lock counters yet acquires the locks we were trying to avoid. This change needs some justification since it is not a bug fix. [ scratches head ... ] Why is hot standby messing with this sort of thing at all? It sounds like a performance optimization that should be considered separately, and *later*. Possibly. We have 3 suggested approaches: * Avoid taking LockPartition locks while we get info for Hot Standby during normal running, by means of a ref counting scheme (Simon) * Take the locks and implement a ref counting scheme (Heikki) * Take the locks, worry later (Tom) The middle ground seems pointless to me. I'm happy to go with simple lock-everything-for-now but it's pretty clear its going to be a annoying performance hit. If we do that we should put in a parameter to turn on/off so that those who will never use Hot Standby can avoid this completely. I'll wait for Heikki's thoughts before implementing anything. -- Simon Riggs www.2ndQuadrant.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] dblink memory leak
Joe Conway m...@joeconway.com writes: Given that change, is there even any leak to even worry about? As long as the PGresult object is created in the correct memory context, it ought to get cleaned up automatically, no? No, because libpq knows nothing of backend memory contexts; it just allocates with malloc. You'll still need a PG_TRY block to ensure you release PGresults during error cleanup. The change to using tuplestores will just help you localize that requirement in well-defined places. I can't promise to make this change before 15 October, but I will get to it before the end of CF3. No big hurry, I think, considering the leak has always been there. 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] Privileges and inheritance
Simon, A small addition though, please. This functionality has been available since 8.1 and changing things could cause existing people's scripts to fail when they upgrade. If we make this change then we should make sure that explicitly GRANTing a permission on the child tables does not fail. You seem to have misunderstood the patch. We're not disabling the ability to GRANT permissions on individual child tables. We're just disabling the child table permissions check if someone comes in through the master. And we'll *definitely* need to warn people about the security implications. --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
Re: [HACKERS] dblink memory leak
Tom Lane wrote: Joe Conway m...@joeconway.com writes: Given that change, is there even any leak to even worry about? As long as the PGresult object is created in the correct memory context, it ought to get cleaned up automatically, no? No, because libpq knows nothing of backend memory contexts; it just allocates with malloc. You'll still need a PG_TRY block to ensure you release PGresults during error cleanup. The change to using tuplestores will just help you localize that requirement in well-defined places. I should have known that! Thanks for the wack on the head... I can't promise to make this change before 15 October, but I will get to it before the end of CF3. No big hurry, I think, considering the leak has always been there. Great. It seems like this is too invasive a change to backport. My feeling is that not enough people have complained about this specific scenario to warrant the risk. Joe signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [PATCH] DefaultACLs
Petr Jelinek pjmo...@pjmodos.net writes: [ latest default-ACLs patch ] Applied with a fair amount of editorial polishing. Notably I changed the permissions requirements a bit: * for IN SCHEMA, the *target* role has to have CREATE permission on the target schema. Without this, the command is a bit pointless since the permissions can never be used. The original coding checked whether the *calling* role had USAGE, which seems rather irrelevant. * I simplified the target-role permission test to is_member_of. The original check for ADMIN seemed pointlessly strong, because if you're a member of the role you can just become the role and set owned objects' permissions however you like. I didn't see the point of the CREATEROLE exemption either, and am generally suspicious of anything that would let people change permissions on stuff they didn't own. One thing that seems like it's likely to be an annoyance in practice is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl entries for a role to be dropped. But I can't see any very good way around that, since the entries might be in some other database. One thing that might at least reduce the number of keystrokes is to have REASSIGN OWNED act as DROP OWNED BY for default ACLs. I can't convince myself whether that's a good idea though, so I left it as-is for the moment. 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] Unicode UTF-8 table formatting for psql text output
On Sun, Oct 04, 2009 at 11:22:27PM +0300, Peter Eisentraut wrote: I have a comment on this bit: @@ -125,6 +128,17 @@ main(int argc, char *argv[]) /* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */ pset.popt.topt.format = PRINT_ALIGNED; + + /* Default table style to plain ASCII */ + pset.popt.topt.table_style = asciiformat; +#if (defined(HAVE_LANGINFO_H) defined(CODESET)) + /* If a UTF-8 locale is available, switch to UTF-8 box drawing characters */ + if (pg_strcasecmp(nl_langinfo(CODESET), UTF-8) == 0 || + pg_strcasecmp(nl_langinfo(CODESET), utf8) == 0 || + pg_strcasecmp(nl_langinfo(CODESET), CP65001) == 0) + pset.popt.topt.table_style = utf8format; +#endif + pset.popt.topt.border = 1; pset.popt.topt.pager = 1; pset.popt.topt.start_table = true; Elsewhere in the psql code, notably in mbprint.c, we make the decision on whether to apply certain Unicode-aware processing based on whether the client encoding is UTF8. The same should be done here. There is a patch somewhere in the pipeline that would automatically set the psql client encoding to whatever the locale says, but until that is done, the client encoding should be the sole setting that rules what kind of character set processing is done on the client side. OK, that makes sense to a certain extent. However, the characters used to draw the table lines are not really that related to the client encoding for data sent from the database (IMHO). I think that (as you said) making the client encoding the same as the locale character set the same in the future would clear up this discrepancy though. Using the client encoding, there's no guarantee the client locale/terminal can handle UTF-8 when the client encoding is UTF-8. I have attached an updated patch which implements your suggested behaviour. It also renames the option to linestyle rather than tablestyle which I think represents its purpose a bit more clearly. Thanks, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 85e9375..cd1c137 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1690,6 +1690,54 @@ lo_import 152801 /varlistentry varlistentry + termliterallinestyle/literal/term + listitem + para + Sets the line drawing style of text table output to one + of literalascii/literal, or literalutf8/literal. + Unique abbreviations are allowed. (That would mean one + letter is enough.) literalutf8/literal will be selected + by default if supported by your locale, + otherwise literalascii/literal will be used. + /para + + para + Query result tables are displayed as text for some output + formats (literalunaligned/literal, + literalaligned/literal, and literalwrapped/literal + formats). The tables are drawn using characters of the + user's locale character set. By + default, acronymASCII/acronym characters will be used, + which will display correctly in all locales. However, if + the user is using a locale with a acronymUTF-8/acronym + character set, the default will be to + use acronymUTF-8/acronym box drawing characters in place + of ASCII punctuation to display more readable tables. + /para + + para + This option is useful for overriding the default line + style, for example to force the use of + only acronymASCII/acronym characters when extended + character sets such as acronymUTF-8/acronym are + inappropriate. This might be the case if preserving output + compatibility with older psql versions is important (prior + to 8.5.0). + /para + + para + quoteUTF8/quote use Unicode acronymUTF-8/acronym box + drawing characters. + /para + + para + quoteASCII/quote use plain acronymASCII/acronym characters. + /para + + /listitem + /varlistentry + + varlistentry termliteralcolumns/literal/term listitem para diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index da57eb4..223f11c 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -46,6 +46,7 @@ #include input.h #include large_obj.h #include mainloop.h +#include mbprint.h #include print.h #include psqlscan.h #include settings.h @@ -596,6 +597,14 @@ exec_command(const char *cmd, /* save encoding info into psql internal data */ pset.encoding = PQclientEncoding(pset.db);
Re: [HACKERS] [PATCH] DefaultACLs
Josh Berkus j...@agliodbs.com writes: On 10/3/09 8:09 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: let's let the default, global default ACL contain the hard-wired privileges, instead of making them hardwired. Wow, that would be great. It would meant that DBAs could change the global default permissions. Committed that way. One possible disadvantage down the road is that people may now have the default privileges instantiated in their databases, which will pose a hazard if we ever want to change the default privilege sets. I imagine that we could have pg_upgrade go through and modify the contents of pg_default_acl if we got in a bind over this, but it's going to be something to think about. 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] dblink memory leak
Joe Conway m...@joeconway.com writes: Tom Lane wrote: No big hurry, I think, considering the leak has always been there. Great. It seems like this is too invasive a change to backport. My feeling is that not enough people have complained about this specific scenario to warrant the risk. Agreed, the risk/reward ratio doesn't seem favorable for a backport. 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] Privileges and inheritance
On Mon, 2009-10-05 at 11:58 -0700, Josh Berkus wrote: Simon, A small addition though, please. This functionality has been available since 8.1 and changing things could cause existing people's scripts to fail when they upgrade. If we make this change then we should make sure that explicitly GRANTing a permission on the child tables does not fail. We're not disabling the ability to GRANT permissions on individual child tables. Until I raised it, that subject had not been mentioned at all, so I've no idea how you know that either was or was not intended. I wish to make sure we don't make that error by explicitly stating requirements. -- Simon Riggs www.2ndQuadrant.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] Unicode UTF-8 table formatting for psql text output
Roger Leigh rle...@codelibre.net writes: On Sun, Oct 04, 2009 at 11:22:27PM +0300, Peter Eisentraut wrote: Elsewhere in the psql code, notably in mbprint.c, we make the decision on whether to apply certain Unicode-aware processing based on whether the client encoding is UTF8. The same should be done here. There is a patch somewhere in the pipeline that would automatically set the psql client encoding to whatever the locale says, but until that is done, the client encoding should be the sole setting that rules what kind of character set processing is done on the client side. OK, that makes sense to a certain extent. However, the characters used to draw the table lines are not really that related to the client encoding for data sent from the database (IMHO). Huh? The data *in* the table is going to be in the client_encoding, and psql contains no mechanisms that would translate it to something else. Surrounding it with decoration in a different encoding is just a recipe for breakage. 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] [PATCH] Reworks for Access Control facilities (r2311)
On Wed, Sep 30, 2009 at 11:17 PM, Stephen Frost sfr...@snowman.net wrote: * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: Stephen Frost wrote: Thanks. To make sure it gets picked up, you might respond to Tom's message above with this same email. Just a thought. The following message was my reply. http://archives.postgresql.org/pgsql-hackers/2009-08/msg01420.php Right, but now there's actually a patch to go with it.. Just thinking that Tom might pick up on it more easily if the patch was sent to that thread, that's all. Of course, he seems to know all anyway, so it's entirely likely that I'm just being silly. Now I'm removing something with behavior change such as above patch, and eliminating comments to be discussed in other thread. I would like to quote them not to forget them away. That's fine. You'll start new threads with different subject lines for them, right? That way other people will see the specific issues and comment if they want to. I expect few people are actually following this very long thread at this level. :) So what's the status of this patch currently? ...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] [PATCH] DefaultACLs
2009/10/6 Tom Lane t...@sss.pgh.pa.us: Applied with a fair amount of editorial polishing. Notably I changed the permissions requirements a bit: Thanks and congratulations! I'm really looking forward to this feature. I pulled the latest sources and gave it a whirl. Things worked as expected in psql, but I was a little surprised when I headed into the documentation. The first place I visited was Chapter 20 - Database Roles and Privileges, but there was no mention of the default ACLs feature in there. If you head into the SQL Reference, it's all there under ALTER DEFAULT PRIVILEGES, but that's only helpful if you already know that it's there and what the command is called. At the moment the only way you'd find out about Default ACLs via the documentation is if you noticed the link several paragraphs into the reference for GRANT. Perhaps we should have something in Chapter 20 along the lines of Rather than tediously assigning privileges to objects every time you create them, you can set default privileges on a schema etc. IMO we should be actively pointing people, especially Postgres/DBA newbies, to this very useful functionality. Cheers, BJ -- 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] [PATCH] Reworks for Access Control facilities (r2311)
* Robert Haas (robertmh...@gmail.com) wrote: So what's the status of this patch currently? I'll be reviewing the updates shortly. After that, I'd like a committer to review it. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Hot Standby on git
Simon Riggs wrote: We discussed briefly your change 0011-Replace-per-proc-counters-of-loggable-locks-with-per.patch. I don't see how that helps at all. The objective of lock counters was to know if we can skip acquiring an LWlock on all lock partitions. This change keeps the lock counters yet acquires the locks we were trying to avoid. This change needs some justification since it is not a bug fix. Well, the original code was buggy. But more to the point, it's a lot simpler this way, I don't see any reason why the counters should be per-process, meaning that they need to be exposed in the pgproc structs or procarray.c. The point is to avoid the seqscan of the lock hash table. I presumed that's the expensive part in GetRunningTransactionLocks(). Tom Lane wrote: [ scratches head ... ] Why is hot standby messing with this sort of thing at all? It sounds like a performance optimization that should be considered separately, and *later*. Yeah, I too considered just ripping it out. Simon is worried that locking all the lock partitions and scanning the locks table can take a long time. We do that in the master, while holding both ProcArrayLock and XidGenLock in exclusive mode (hmm, why is shared not enough?), so there is some grounds for worry. OTOH, it's only done once per checkpoint. -- 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] Hot Standby on git
Simon Riggs wrote: On Mon, 2009-09-28 at 11:25 +0300, Heikki Linnakangas wrote: Heikki Linnakangas wrote: Per Simon's request, for the benefit of the archive, here's all the changes I've done on the patch since he posted the initial version for review for this commitfest as incremental patches. This is extracted from my git repository at git://git.postgresql.org/git/users/heikki/postgres.git. Further fixes extracted from above repository attached.. I've applied changes on all these patches apart from 0006-... which has some dependencies on earlier work I'm still looking at. Simon, you don't need to apply those patches. Just review them, and post comments or subsequent patches on top of the repository. -- 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] [PATCH] DefaultACLs
Tom Lane napsal(a): Petr Jelinek pjmo...@pjmodos.net writes: [ latest default-ACLs patch ] Applied with a fair amount of editorial polishing. Notably I changed the permissions requirements a bit: Thank you very much Tom. One thing that seems like it's likely to be an annoyance in practice is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl entries for a role to be dropped. But I can't see any very good way around that, since the entries might be in some other database. One thing that might at least reduce the number of keystrokes is to have REASSIGN OWNED act as DROP OWNED BY for default ACLs. I can't convince myself whether that's a good idea though, so I left it as-is for the moment. Yeah I am not happy about this either but there is not much we can do about it. Btw I think in the version I sent in REASSIGN OWNED acted as DROP OWNED for default ACLs. -- Regards Petr Jelinek (PJMODOS)
Re: [HACKERS] 8.5 TODO: any info on Create dump tool for write-ahead logs... in PITR section (1.4)?
shakahsha...@gmail.com wrote: Can anyone elaborate (or point me to some additional info) on the 8.5 TODO item in the Point-In-Time Recover (PITR) section (1.4): Create dump tool for write-ahead logs for use in determining transaction id for point-in-time recovery This is useful for checking PITR recovery. I poked around a bit and found some code that walks the WAL logs (in src/backend/access/transam/xlog.c, I think) and I could probably figure out how to display a WAL log file contents, but I'm hoping someone can provide some context as to what issue this TODO item is trying to address (i.e., what output would be useful). That TODO item is a lot less important after we have Hot Standby. It contains functions that allow you to pause and continue WAL replay, and step through the WAL one transaction at a time. It won't let you go backwards, though, so it won't completely replace a tool like xlogdump, which you can use to look into the WAL before applying it. -- 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] [PATCH] DefaultACLs
Petr Jelinek pjmo...@pjmodos.net writes: Tom Lane napsal(a): One thing that seems like it's likely to be an annoyance in practice is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl entries for a role to be dropped. Yeah I am not happy about this either but there is not much we can do about it. Btw I think in the version I sent in REASSIGN OWNED acted as DROP OWNED for default ACLs. IIRC it just threw a warning, which didn't seem tremendously useful to me. 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] [PATCH] DefaultACLs
Brendan Jurd dire...@gmail.com writes: I pulled the latest sources and gave it a whirl. Things worked as expected in psql, but I was a little surprised when I headed into the documentation. The first place I visited was Chapter 20 - Database Roles and Privileges, but there was no mention of the default ACLs feature in there. I looked at that (as well as the material in section 5.6) and concluded that it was a once-over-lightly presentation that didn't really need to delve into this. But if you want to work it over, feel free to send in a docs patch. Personally I'd like to see less duplication between 5.6 and 20.3, but I'm not quite sure how to refactor it --- the material is arguably somewhat relevant in both places. 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] [PATCH] DefaultACLs
Tom Lane napsal(a): Petr Jelinek pjmo...@pjmodos.net writes: Tom Lane napsal(a): One thing that seems like it's likely to be an annoyance in practice is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl entries for a role to be dropped. Yeah I am not happy about this either but there is not much we can do about it. Btw I think in the version I sent in REASSIGN OWNED acted as DROP OWNED for default ACLs. IIRC it just threw a warning, which didn't seem tremendously useful to me. Oh did it ? Then I must have discarded that idea for some reason. I probably didn't want to be too pushy there. -- Regards Petr Jelinek (PJMODOS)
[HACKERS] moving system catalogs to another tablespace
Hi, it seems like we can't do this. At least a get this error: db=# alter table pg_largeobject set tablespace otro; ERROR: permission denied: pg_largeobject is a system catalog but pg_largeobject seems sensible to move to another table space for space considerations, no? are there any reasons for this? i guess i still could this with symlinks, no? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] moving system catalogs to another tablespace
Jaime Casanova jcasa...@systemguards.com.ec writes: it seems like we can't do this. At least a get this error: db=# alter table pg_largeobject set tablespace otro; ERROR: permission denied: pg_largeobject is a system catalog You can move *all* of the system catalogs with ALTER DATABASE SET TABLESPACE. pg_largeobject might be a special case, but in general I would think there's no use-case for moving individual catalogs. 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] [PATCH] DefaultACLs
2009/10/6 Tom Lane t...@sss.pgh.pa.us: Brendan Jurd dire...@gmail.com writes: I pulled the latest sources and gave it a whirl. Things worked as expected in psql, but I was a little surprised when I headed into the documentation. The first place I visited was Chapter 20 - Database Roles and Privileges, but there was no mention of the default ACLs feature in there. I looked at that (as well as the material in section 5.6) and concluded that it was a once-over-lightly presentation that didn't really need to delve into this. Well 5.6 goes as far as to give mention about WITH GRANT OPTION, so I don't think we'd be going too deep to give a pointer regarding default ACLs as well. I don't think we need a fully detailed explanation of default ACLs here, just a brief mention and a pointer to the reference page. But if you want to work it over, feel free to send in a docs patch. I'll have a play around with it and see if I can come up with wording that I like. Personally I'd like to see less duplication between 5.6 and 20.3, but I'm not quite sure how to refactor it --- the material is arguably somewhat relevant in both places. I hear you about the duplication. 20.3 is so brief that I'm quite tempted to just remove it and instead have a paragraph referencing GRANT and REVOKE somewhere in 20.1. Cheers, BJ -- 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] moving system catalogs to another tablespace
Jaime Casanova escreveu: it seems like we can't do this. At least a get this error: db=# alter table pg_largeobject set tablespace otro; ERROR: permission denied: pg_largeobject is a system catalog but pg_largeobject seems sensible to move to another table space for space considerations, no? are there any reasons for this? i guess i still could this with symlinks, no? This was discussed some time ago [1]. A possible solution was proposed in [2] (it's in pt-br but you can check the commands to accomplish your goal). [1] http://archives.postgresql.org/pgsql-hackers/2004-06/msg00835.php [2] http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-March/014374.html -- Euler Taveira de Oliveira http://www.timbira.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] dblink memory leak
On Mon, Oct 5, 2009 at 1:46 PM, Joe Conway m...@joeconway.com wrote: Tom Lane wrote: Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: I think PG_TRY blocks are not enough, too. PG_TRY requires a statement block, but we need to return from dblink functions per tuple. That bit will have to be undone. There is no reason for dblink not to return a tuplestore. That's a really good point. It was originally written thinking we would eventually be able to stream tuples rather than materialize them, but given that many years have passed and we are no closer (I believe) to a true streaming mode for SRFs, a tuplestore would be much cleaner. Given that change, is there even any leak to even worry about? As long as the PGresult object is created in the correct memory context, it ought to get cleaned up automatically, no? I can't promise to make this change before 15 October, but I will get to it before the end of CF3. Another possibility is that Itagaki Takahiro, who developed the original patch, might be willing to develop something along these lines instead. However, it does seem that that original patch will not be accepted, for the reason that the committers prefer the approach discussed upthread. Therefore, I am marking the query cancel issues in dblink patch as Returned with Feedback. ...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] Lock Wait Statistics (next commitfest)
On Sun, Oct 4, 2009 at 4:14 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Sep 28, 2009 at 12:14 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Aug 8, 2009 at 7:47 PM, Mark Kirkwood mar...@paradise.net.nz wrote: Patch with max(wait time). Still TODO - amalgamate individual transaction lock waits - redo (rather ugly) temporary pg_stat_lock_waits in a form more like pg_locks This version has the individual transaction lock waits amalgamated. Still TODO: redo pg_stat_lock_waits ... it applies with some hunks, compiles fine and seems to work... i'm still not sure this is what we need, some more comments could be helpful. what kind of questions are we capable of answer with this and and what kind of questions are we still missing? for example, now we know number of locks that had to wait, total time waiting and max time waiting for a single lock... but still we can have an inaccurate understanding if we have lots of locks waiting little time and a few waiting a huge amount of time... Aren't the huge ones already loggable from the deadlock detector? With the max, we can at least put an upper limit on how long the longest ones could have been. However, is there a way to reset the max? I tried deleting data/pg_stat_tmp, but that didn't work. With cumulative values, you can you take snapshots and then take the difference of them, that won't work with max. If the max can't be reset except with an initdb, I think that makes it barely usable. something i have been asked when system starts to slow down is can we know if there were a lock contention on that period? for now the only way to answer that is logging locks I was surprised to find that running with track_locks on did not cause a detectable difference in performance, so you could just routinely do regularly scheduled snapshots and go back and mine them over the time that a problem was occurring. I just checked with pgbench over various levels of concurrency and fsync settings. If potential slowness wouldn't show up there, I don't know how else to look for it. It seems that this patch had open TODO items at the beginning of the CommitFest (so perhaps we should have bounced it immediately), and I think that's still the case now, so I am going to mark this as Returned with Feedback. A lot of good reviewing has been done, though, so many this can be submitted for a future CommitFest in something close to a final form. Thanks, ...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] 8.5 TODO: any info on Create dump tool for write-ahead logs... in PITR section (1.4)?
Hi, On Tue, Oct 6, 2009 at 7:47 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: That TODO item is a lot less important after we have Hot Standby. It contains functions that allow you to pause and continue WAL replay, and step through the WAL one transaction at a time. I don't think this is practical for PITR purpose, since applying one transaction at a time up to the recovery target point might take very long time. So the tool to quickly determine the recovery target point is necessary even after we have HS, I think. Am I missing something? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] dblink memory leak
Robert Haas wrote: On Mon, Oct 5, 2009 at 1:46 PM, Joe Conway m...@joeconway.com wrote: I can't promise to make this change before 15 October, but I will get to it before the end of CF3. Another possibility is that Itagaki Takahiro, who developed the original patch, might be willing to develop something along these lines instead. Either way is fine -- good suggestion ;-) However, it does seem that that original patch will not be accepted, for the reason that the committers prefer the approach discussed upthread. Therefore, I am marking the query cancel issues in dblink patch as Returned with Feedback. Yes, thanks for doing that. Joe signature.asc Description: OpenPGP digital signature
[HACKERS] CommitFest 2009-09: how do we close this one out?
There are now 19 patches out of an original total of 48 to be dealt with for this CommitFest. Of those, 10 are marked as Ready for Committer, 1 is marked as Needs Review and the listed reviewer is a committer, 7 are waiting for review or re-review by non-committers, and 1 is waiting on the patch author, as listed below and on commitfest.postgresql.org. The list below also shows the specific reviewer/committer, where known. Because there are so many patches that are ready for committer review already, and several more that will probably get there soon, it seems likely that we will still have patches in that state on 10/15, the nominal date for the end of CommitFest. We need to think about what we want to do about that. As far as I can see, there are three main questions that we need to resolve: 1. Do we bundle alpha2 on or shortly after 10/15, or do we wait until the patches that are ready for committer action have gotten it? 2. Do the committers keep working on the patch queue after 10/15, or do we postpone patches that are basically ready until 11/15 to give the committers a break to do their own work? Obviously, postponing things risks an even longer backlog next time. Of course, if there's a flurry of activity in the next week these questions may become moot. But we already have 10 patches marked Ready for Committer, which is the highest I can remember seeing, and there are a couple more that are likely to get to that state soon. ...Robert Committers (11) == Streaming Replication (Heikki Linnakangas) Hot Standby (Heikki Linnakangas) Triggers on columns (Peter Eisentraut) CREATE LIKE INCLUDING COMMENTS and STORAGE (Andrew Dunstan) Allow more complex user/database default GUC settings (Alvaro Herrera) GRANT ON ALL IN schema Largeobject access controls Named and mixed notation support DML node in support of writeable CTEs Buffer usage in EXPLAIN and pg_stat_statements Encoding issues in console and eventlog on win32 (Magnus Hagander) Reviewers (7) = Reworks for Access Controls (Stephen Frost) Pgbench Shell command (Dan Colish/Gabrielle Roth) Enhancements to COPY (error logging and autopartitioning) (Selena Deckelmann/Jeff Davis) Dynamic cursor support for ECPG (Dan Colish) SQLDA support for ECPG (Noah Misch) DESCRIBE [OUTPUT] support for ECPG Out of scope cursor handling for ECPG Patch Authors (1) = psql: Unicode UTF-8 table formatting for text output -- 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] Rules: A Modest Proposal
Josh == Josh Berkus j...@agliodbs.com writes: 1) any reference in an insert rule to NEW.col where col has a volatile default, or the expression in the insert statement was volatile, or the expression's value is changed by the insert, will do the wrong thing: Josh Is this different from triggers? Absolutely. In an AFTER trigger, the trigger's NEW variable is guaranteed to be exactly the inserted values. So doing a log table with triggers is reliable, whereas doing it with rules is not. 2) any rule with multiple actions, each action is affected by the results of the previous ones. A classic example of this is in the use of OLD in delete or update rules; OLD _does not return a row_ if a previous action in the rule deleted the row or updated it so that it no longer matches. Josh I know this is not any different from triggers which cascade. Of course it is different. A trigger's value of OLD is always the actual content of the previous row version, it doesn't magically disappear the way that rule OLD does. Josh David's basic proposal, as I understand, is to remove RULEs and Josh replace them with triggers on views. However, there are *lots* Josh of ways to screw yourself up with triggers as well. There is simply no comparison here. Triggers are simple procedural logic which any novice can use effectively with little chance of falling into any major pitfalls; rules are a bizarre macro-language which even experts have a hard time using correctly. Josh For example see my previously reported bug about referential Josh integrity and self-triggers. link? Josh Triggers also have potential security issues which rules lack. Example? Josh I happen to like having RULEs in my arsenal of tricks for Josh getting the database to do Nifty Stuff. I've always considered Josh them advanced database programming, and not for beginners. The difference is that rules aren't for advanced users either (as you've just demonstrated by not understanding the differences in behaviour between rules and triggers). -- Andrew (irc:RhodiumToad) -- 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] [PATCH] Largeobject access controls
I rebased the largeobject access controls patch to the CVS HEAD
because of the patch confliction to the default ACL patch.
The only difference was a switch-case statement was moved from
shdepDropOwned() to RemoveRoleFromObjectACL(), so we had to
change the point to be patched.
I don't think this change needs whole of reviewing again.
Actual changes are as follows:
$ diff -up r2333.patch r2353.patch
snip
+*** base/src/backend/catalog/aclchk.c Tue Oct 6 08:45:40 2009
+--- blob/src/backend/catalog/aclchk.c Tue Oct 6 09:44:51 2009
@@ -310,9 +310,21 @@ diff -Nrpc base/src/backend/catalog/aclc
case ACL_OBJECT_NAMESPACE:
foreach(cell, objnames)
{
+*** RemoveRoleFromObjectACL(Oid roleid, Oid
+*** 1156,1161
+--- 1184,1192
+ case LanguageRelationId:
+ istmt.objtype = ACL_OBJECT_LANGUAGE;
+ break;
++ case LargeObjectMetadataRelationId:
++ istmt.objtype = ACL_OBJECT_LARGEOBJECT;
++ break;
+ case NamespaceRelationId:
+ istmt.objtype = ACL_OBJECT_NAMESPACE;
+ break;
*** ExecGrant_Language(InternalGrant *istmt)
snip
-*** base/src/backend/catalog/pg_shdepend.c Thu Jun 18 10:20:52 2009
blob/src/backend/catalog/pg_shdepend.c Thu Sep 24 10:46:38 2009
+*** base/src/backend/catalog/pg_shdepend.c Tue Oct 6 08:45:40 2009
+--- blob/src/backend/catalog/pg_shdepend.c Tue Oct 6 09:44:51 2009
***
-*** 24,29
24,30
- #include catalog/pg_conversion.h
+*** 25,30
+--- 25,31
#include catalog/pg_database.h
+ #include catalog/pg_default_acl.h
#include catalog/pg_language.h
+ #include catalog/pg_largeobject_metadata.h
#include catalog/pg_namespace.h
#include catalog/pg_operator.h
#include catalog/pg_proc.h
-*** shdepDropOwned(List *roleids, DropBehavi
-*** 1210,1215
1211,1219
- case LanguageRelationId:
- istmt.objtype = ACL_OBJECT_LANGUAGE;
- break;
-+ case LargeObjectMetadataRelationId:
-+ istmt.objtype = ACL_OBJECT_LARGEOBJECT;
-+ break;
- case NamespaceRelationId:
- istmt.objtype = ACL_OBJECT_NAMESPACE;
- break;
*** shdepReassignOwned(List *roleids, Oid ne
-*** 1365,1370
1369,1378
+*** 1332,1337
+--- 1333,1342
AlterLanguageOwner_oid(sdepForm-objid, newrole);
break;
@@ -1178,9 +1178,9 @@ diff -Nrpc base/src/backend/catalog/pg_s
+ AlterLargeObjectOwner(sdepForm-objid, newrole);
+ break;
+
- default:
- elog(ERROR, unexpected classid %d, sdepForm-classid);
- break;
+ case DefaultAclRelationId:
+ /*
+* Ignore default ACLs; they should be handled by
diff -Nrpc base/src/backend/commands/alter.c blob/src/backend/commands/alter.c
*** base/src/backend/commands/alter.c Sat Jan 3 13:01:35 2009
--- blob/src/backend/commands/alter.c Thu Sep 24 10:46:38 2009
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com
sepgsql-02-blob-8.5devel-r2353.patch.gz
Description: application/gzip
--
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] [PATCH] Reworks for Access Control facilities (r2311)
Stephen Frost wrote: * Robert Haas (robertmh...@gmail.com) wrote: So what's the status of this patch currently? I'll be reviewing the updates shortly. After that, I'd like a committer to review it. Do you think this version also should rework an invocation of pg_namespace_aclcheck() newly added due to the default ACL feature? IMO, we don't need to hurry-up to catch up these new features just now, because we have only a week in this commit fest. It is desirable to improve the patch being commitable earlier. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] 8.5 TODO: any info on Create dump tool for write-ahead logs... in PITR section (1.4)?
Fujii Masao escreveu: On Tue, Oct 6, 2009 at 7:47 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: That TODO item is a lot less important after we have Hot Standby. It contains functions that allow you to pause and continue WAL replay, and step through the WAL one transaction at a time. I don't think this is practical for PITR purpose, since applying one transaction at a time up to the recovery target point might take very long time. So the tool to quickly determine the recovery target point is necessary even after we have HS, I think. Am I missing something? No. BTW, I have an unpublished version here that I didn't commit because I need to clean it up. :( -- Euler Taveira de Oliveira http://www.timbira.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] moving system catalogs to another tablespace
On Mon, Oct 5, 2009 at 7:15 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: db=# alter table pg_largeobject set tablespace otro; ERROR: permission denied: pg_largeobject is a system catalog [1] http://archives.postgresql.org/pgsql-hackers/2004-06/msg00835.php seems like the original idea was to forbid this in all system catalogs except pg_largeobject, what happen then? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] moving system catalogs to another tablespace
Jaime Casanova jcasa...@systemguards.com.ec writes: seems like the original idea was to forbid this in all system catalogs except pg_largeobject, what happen then? Nothing ... nobody got around to doing anything about it. 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] Rules: A Modest Proposal
On Mon, Oct 5, 2009 at 10:17 AM, Josh Berkus j...@agliodbs.com wrote: So while rules are hard to use and easy to mess up, so are triggers. So while an (arguable) problem is being pointed out, no real solution is being proposed. If you want to implement updatable views I still stand by my (much) earlier design suggestion. They should be implemented just like SELECT on views is currently. The rule is a simple substitution and doesn't try to analyze and decompose the query and figure out how to rewrite it into a complete different query. Most of the work is done, not in the rule, but in the regular SQL parser and statement analyzer where it has a lot more information available to it. So for example this view CREATE VIEW foo AS SELECT a AS aa, b+1 AS bb FROM tab expands this sql: SELECT bb FROM foo into this: SELECT bb FROM (SELECT a AS aa, b+1 AS bb FROM tab) AS foo and it should expand this sql: UPDATE foo SET a=1 WHERE bb=1 into this: UPDATE (SELECT a AS aa, b+1 AS bb FROM tab) AS foo SET a=1 WHERE bb=1 This means extending our regular UPDATE syntax to allow arbitrary inline views in place of the update target. That's harder than the hacks we've been playing with so far to try to reverse engineer the right way to write the update statement for a given view but it would be much much more robust. The statement analyzer handling the update statement has a much better idea of what columns it needs to write to, which tables they depend on, and so on. The problems people run into with rules always come from trying to put too much cleverness into the rule. When you put conditions on the rule based on your partition key or put intelligence in the rule to handle your updatable view logic it embeds dependencies on subtle assumptions about the eventual query which will come along. We've never run into any problems with regular rules used for regular views because all they do is substitute the view in the right place in the query. The select machinery takes care of figuring out how it relates to the rest of the query. As long as the updatable views do the same thing then rules will be exactly the right tool for the job. -- greg -- 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] [PATCH] DefaultACLs
I tried to check the default ACL behavior.
postgres=# \c - ymj
psql (8.5devel)
You are now connected to database postgres as user ymj.
postgres= ALTER DEFAULT PRIVILEGES REVOKE INSERT ON TABLE FROM ymj;
ALTER DEFAULT PRIVILEGES
postgres= CREATE TABLE t2 (x int, y text);
CREATE TABLE
postgres= SELECT relname, relacl FROM pg_class WHERE oid = 't2'::regclass;
relname | relacl
-+--
t2 | {ymj=rwdDxt/ymj}
(1 row)
postgres= INSERT INTO t2 VALUES (1, 'aaa');
ERROR: permission denied for relation t2
It works for me fine, good, but ...
postgres= SELECT * INTO t3 FROM t1;
SELECT
postgres= SELECT * FROM t3;
a | b
---+-
1 | aaa
2 | bbb
(2 rows)
postgres= INSERT INTO t3 VALUES (3,'ccc');
ERROR: permission denied for relation t3
In this case, the new table t3 is created with the default ACL which does not
allow to insert any values by the owner of the relation.
SELECT INTO does not check ACL_INSERT on the newly created tables, because
we had been able to assume the table owner always has privilege to insert
values into the new table.
So, OpenIntoRel() didn't check this obvious privilege.
But the default ACL feature breaks this assumption. The table owner may not
have privilege to insert values into new tables.
So, it is necessary to put actual access controls on the OpenIntoRel().
Thanks,
Tom Lane wrote:
Petr Jelinek pjmo...@pjmodos.net writes:
[ latest default-ACLs patch ]
Applied with a fair amount of editorial polishing. Notably I changed
the permissions requirements a bit:
* for IN SCHEMA, the *target* role has to have CREATE permission on the
target schema. Without this, the command is a bit pointless since the
permissions can never be used. The original coding checked whether the
*calling* role had USAGE, which seems rather irrelevant.
* I simplified the target-role permission test to is_member_of. The
original check for ADMIN seemed pointlessly strong, because if you're a
member of the role you can just become the role and set owned objects'
permissions however you like. I didn't see the point of the CREATEROLE
exemption either, and am generally suspicious of anything that would let
people change permissions on stuff they didn't own.
One thing that seems like it's likely to be an annoyance in practice
is the need to explicitly do DROP OWNED BY to get rid of pg_default_acl
entries for a role to be dropped. But I can't see any very good way
around that, since the entries might be in some other database. One
thing that might at least reduce the number of keystrokes is to have
REASSIGN OWNED act as DROP OWNED BY for default ACLs. I can't convince
myself whether that's a good idea though, so I left it as-is for the
moment.
regards, tom lane
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
