Re: [HACKERS] alter + preserving dependencies
Le 7 mai 08 à 07:52, Tom Lane a écrit :
Dimitri Fontaine [EMAIL PROTECTED] writes:
Could we consider ALTER VIEW ALTER COLUMN ... SET DEFAULT ...;?
We could if we hadn't already done it five or so years ago.
Or am I missing what you need here?
My 8.3.1 installation psql \h only gives me:
Syntax:
ALTER VIEW name RENAME TO newname
Ok, I've been quite bad at explaining the case, let's retry.
CREATE TABLE t(id serial, name text not null, a_count not null default
0, option);
CREATE VIEW v AS SELECT * FROM t;
Now we add a on INSERT RULE such as INSERT INTO v will insert to t.
And we want the RULE to be able to use the a_count DEFAULT when user
didn't give a_count column in its INSERT order:
INSERT INTO v(name) VALUES ('foo');
The RULE kicks in and rewrite the INSERT to target t, and a_count not
being given explicitely any value will get rewritten to NULL, not to
DEFAULT, and the INSERT subsequently fails.
In order to be able to avoid this, AFAIK, you need to poke into
catalogs to retrieve the DEFAULT value, at the RULE definition level...
Bonus question: why is the rewriter unable to distinguish whether
NULL
comes from the user or comes from the column was not part of the user
query?
Not following this either ...
...and the RULE definition has no chance to figure out by itself if
the user omit to give any value for our not null column, or
explicitely gave a NULL, in which case we'd better not rewrite it to
DEFAULT...
Hope I made the case clear by now, thx for your interest,
--
dim
--
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] [PATCHES] [EMAIL PROTECTED]: Re: [BUGS] Problem identifying constraints which should not be inherited]
On Wed, May 7, 2008 at 12:20 AM, Alex Hunsaker [EMAIL PROTECTED] wrote: Find attached a diff from v4-v5, and a full patch. src/backend/commands/tablecmds.c | 242 +++- src/backend/utils/cache/syscache.c | 12 -- src/include/catalog/indexing.h |2 - src/include/utils/syscache.h |1 - 4 files changed, 153 insertions(+), 104 deletions(-) Currently this loops through all the constraints for a relation (old behavior of MergeAttributesIntoExisting)... Do you think its worth adding a non-unique index to speed this up? If so I can whip up a patch real quick if you think its worth it... else *sigh* Here is a fiix for a possible bogus failed to find constraint error when we are trying to drop a constraint that is not a check constraint (interesting no regression tests failed... caught it while reviewing the patch I just posted) *** a/src/backend/commands/tablecmds.c --- /bsrc/backend/commands/tablecmds.c *** ATExecDropConstraint(Relation rel, const *** 5080,5094 con = (Form_pg_constraint) GETSTRUCT(tuple); - if (con-contype != CONSTRAINT_CHECK) - continue; - if (strcmp(NameStr(con-conname), constrName) != 0) continue; else found = true; if (con-coninhcount = 0) elog(ERROR, relation %u has non-inherited constraint \%s\, childrelid, constrName); --- 5080,5095 con = (Form_pg_constraint) GETSTRUCT(tuple); if (strcmp(NameStr(con-conname), constrName) != 0) continue; else found = true; + /* Right now only CHECK constraints can be inherited */ + if (con-contype != CONSTRAINT_CHECK) + continue; + if (con-coninhcount = 0) elog(ERROR, relation %u has non-inherited constraint \%s\, childrelid, constrName); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
Tom Lane wrote: After that, and a whole bunch of foolery with manually putting the right security labels on files (because the contrib module is unhelpful for test installations with nonstandard PREFIXes), I managed to get a version that worked well enough to test. What I found out: I'll improve the way to provide security policy. Please wait for a while. * It does not come close to passing the regression tests. I saw a lot of ! ERROR: unrecognized node type: 903 which suggests that something's been screwed up about parse analysis (903 = T_A_Const, which shouldn't get further than parse analysis), Could you tell me what queries hit these errors? I guess this message was generated in expression_tree_walker() called within sepgsqlExprWalker(), but it is invoked after query rewriter. and it dumps core in the TRUNCATE test. This bug came from misoperations of List. * pgbench shows a relatively marginal speed difference, which is not too surprising since it pushes only a few tuples around per query. The worst case for sepostgres is likely to be bulk operations. I tried select count(*) from accounts (at scale factor 20, so this is 200 tuples) and got about 550 msec from CVS HEAD, about 1340 msec from HEAD plus patch. That's with no actual useful policy loaded, mind you. So the overhead is indeed pretty bad, but it seems reasonable to think that with some optimization effort it could be brought down to where it'd be acceptable for people who really need the feature. Thanks for your information. It indeed has performance degrading, but, I expect users who want to apply SELinux policy don't put their first priority on edge performance. AFAICS the only thing left that really needs to be discussed more during this commit-fest is the business about whether it's sane to be trying to apply selinux restrictions in simple_heap_update and friends. The more I think about that the more I think it's simply a bad idea. Those functions are used primarily (if not solely) for system-initiated catalog updates, and I fail to see any plausible reason to let user-written policy be applied at that level. For instance, it would be a completely bad move to allow such policy to prevent autovacuum from updating relfrozenxid values. In generally, fewer and more fundamental security checks are more comprehensive. simple_heap_update() can trap any updating, called from more than 80 places. The purpose of SE-PostgreSQL is to apply security policy on user's queries, not an internal one. Therefore, we don't apply any policy on some of *internal* operations, like updating TOAST table. Currently I don't apply any special care for autovacuum, however, it is a solution to handle *internal* updating as an exception for checks. (Because it is NOT came from external users.) Furthermore, those functions are much too low-level to have any idea of the reason for the change they are being asked to make, so they can't apply any intelligence to the allow-or-not decision. We can abstract finer-grained access control unit later, however, its reverse is difficult. For example, we have to have the following permission to create a new table. - Insert a new tuple into pg_class - Insert a new tuple into pg_type - Insert new tuples into pg_attribute These are abstracted in the default security policy. If you want non-administrative users to create/drop table, set sepgsql_enable_users_ddl boolean to turn on/off the permission set. # setsebool sepgsql_enable_users_ddl=1 Lastly, I don't see any use-case for policy there that wouldn't be served as well or better by the standard SQL privilege mechanism. So I think that really all needs to be ripped out, and do enforcement in the executor instead. The primary purpose of SE-PostgreSQL is to apply common access control policy on database objects, as if clients access filesystem objects. See the following diagram: http://sepgsql.googlecode.com/files/sepgsql_dfc.png It shows two users within different domain tries to communicate each other, and any message have to go through inter-processes communication method like files, networks, and so on. (Security policy enforces single directed data flow, from lower to higher.) SELinux can prevent data leaking via shared files because it is managed by operating system. However, it is impossible for database objects. SE-PostgreSQL apply (system-wide) common security policy on database objects. This concept enables to maintain consistency of access control policy. The standard SQL privilegs machanism is suitable to control accesses on database objects, however, it is not easy to maintain its consistency with operating systems's policy. Is it appropriate answer for your question? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:
Re: [HACKERS] alter + preserving dependencies
Le mercredi 07 mai 2008, Dimitri Fontaine a écrit :
Ok, I've been quite bad at explaining the case, let's retry.
Thanks a lot to the OP on #postgresqlfr (nickname renchap), who is providing
attached test case, where you'll see how we hacked our way into
information_schema to have the insert rule insert DEFAULT instead of NULL.
Of course the OP loses here the option to error out on NULL input, but the
application is being ported from MySQL so he's not losing any feature here.
Regards,
--
dim
gwow_dev_renchap=# select version();
version
PostgreSQL 8.2.6 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)
(1 row)
gwow_dev_renchap=# select get_site_id();
get_site_id
-
1
(1 row)
gwow_dev_renchap=# \df+ get_default_value;
List of functions
Schema | Name| Result data type |
Argument data types| Owner | Language | Source code
| Description
+---+---+---+-+--+---+-
public | get_default_value | information_schema.character_data | table
character varying, column character varying | renchap | sql | SELECT
column_default FROM information_schema.columns WHERE table_name=$1 AND
column_name=$2; |
(1 row)
gwow_dev_renchap=# \d zf_categories
Table public.zf_categories
Column | Type | Modifiers
---+---+
id| integer | not null default
nextval('zf_categories_id_seq'::regclass)
cat_name | character varying(80) | not null default 'New
Category'::character varying
disp_position | integer | not null default 0
site_id | integer |
Indexes:
zf_categories_pkey PRIMARY KEY, btree (id)
zf_categories_site_id btree (site_id)
zf_categories_site_id_idx btree (site_id)
gwow_dev_renchap=# \d z_categories
View public.z_categories
Column | Type | Modifiers
---+---+---
id| integer |
cat_name | character varying(80) |
disp_position | integer |
View definition:
SELECT zf_categories.id, zf_categories.cat_name, zf_categories.disp_position
FROM zf_categories
WHERE zf_categories.site_id = get_site_id();
Rules:
delete_z_categories AS
ON DELETE TO z_categories DO INSTEAD DELETE FROM zf_categories
WHERE zf_categories.id = old.id AND zf_categories.site_id = get_site_id()
insert_z_categories AS
ON INSERT TO z_categories DO INSTEAD INSERT INTO zf_categories (site_id,
cat_name, disp_position)
VALUES (get_site_id(), COALESCE(new.cat_name,
get_default_value('zf_categories'::character varying, 'cat_name'::character
varying)::character varying), COALESCE(new.disp_position,
get_default_value('zf_categories'::character varying,
'disp_position'::character varying)::integer))
update_z_categories AS
ON UPDATE TO z_categories DO INSTEAD UPDATE zf_categories SET id = new.id,
cat_name = new.cat_name, disp_position = new.disp_position
WHERE zf_categories.id = old.id AND zf_categories.site_id = get_site_id()
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name| disp_position | site_id
+---+---+-
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
(2 rows)
gwow_dev_renchap=# INSERT INTO z_categories (cat_name, disp_position) VALUES
('My Cat', 5);
INSERT 0 1
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name| disp_position | site_id
+---+---+-
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
22 | My Cat| 5 | 1
(3 rows)
gwow_dev_renchap=# INSERT INTO z_categories (cat_name) VALUES ('My New Cat');
INSERT 0 1
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name| disp_position | site_id
+---+---+-
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
22 | My Cat| 5 | 1
23 | My New Cat| 0 | 1
(4 rows)
gwow_dev_renchap=# UPDATE z_categories SET disp_position=2;
UPDATE 2
Re: [HACKERS] alter + preserving dependencies
Dimitri Fontaine wrote: Le 7 mai 08 à 07:52, Tom Lane a écrit : Dimitri Fontaine [EMAIL PROTECTED] writes: Could we consider ALTER VIEW ALTER COLUMN ... SET DEFAULT ...;? We could if we hadn't already done it five or so years ago. Or am I missing what you need here? My 8.3.1 installation psql \h only gives me: Syntax: ALTER VIEW name RENAME TO newname Ah, you use ALTER TABLE: ALTER TABLE my_view ALTER COLUMN view_column DEFAULT expr; -- Richard Huxton Archonet Ltd -- 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] [NOVICE] encoding problems
On Wed, Mar 12, 2008 at 4:35 PM, Tom Lane [EMAIL PROTECTED] wrote: Martijn van Oosterhout [EMAIL PROTECTED] writes: The other alternative is to convert tabs to spaces on output. Can't remember why we didn't do that. Yeah. The idea I had was to invent a parameter specifying the number of spaces a tab should expand to --- setting this to zero would give you the current \x09 behavior. I'm not sure how painful it would be to expand tabs properly in mbprint, but it seems possible. Is there any plan to fix this behaviour in 8.3 branch? For instance, the pg_stat_activity output is far less readable than before when people use tabs to indent the SQL queries in their applications. From my experience, it's pretty common and it's really a regression from the usability point of view. Regards, -- Guillaume -- 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] Concurrent psql API
Greg, Not sure whether you're working on this or not? If so, what do you think of the slightly modified syntax I proposed? I'm fairly keen on getting this patch completed fairly early on in the 8.4 cycle because it allows a new class of concurrent test case. I think many people will be happy to submit concurrent test cases once the syntax is known. That seems likely to reveal a few bugs we've not seen before, especially when we are able to get that into the build farm. It seems prudent to do that as early as possible so we have time to fix the many bugs that emerge, some of them port specific. Would you like any help? On Wed, 2008-04-23 at 15:18 +0100, Simon Riggs wrote: On Tue, 2008-04-08 at 17:10 -0400, Tom Lane wrote: What seems possibly more useful is to reintroduce \cwait (or hopefully some better name) and give it the semantics of wait for a response from any active connection; switch to the first one to respond, printing its name, and print its result. This would lead to code like, say, \c conn1 \c conn2 ... \S conn1 CREATE INDEX ... \g \S conn2 CREATE INDEX ... \g ... \cwait \cwait The number of \cwaits you need is exactly equal to the number of async commands you've issued. For regression testing purposes you'd need to design the script to ensure that only one of the connections is expected to respond next, but that seems necessary anyway --- and you don't need any extra checks to catch the case that you get an unexpected early response from another one. Hmm, this still seems a bit notation-heavy, doesn't it? What if \g takes an arg indicating which connection to issue the command on: \c conn1 \c conn2 ... CREATE INDEX ... \g conn1 CREATE INDEX ... \g conn2 ... \cwait \cwait Not totally sure about that one, but issuing a command on a background connection seems appealing for scripting purposes. It eliminates the risk that the query response comes back before you manage to switch away from the connection; which would be bad because it would mess up your count of how many cwait's you need. It seems a bit more analogous to the use of in shell scripts, too, where you implicitly fork away from the async command. (Maybe c shouldn't make the new connection foreground either?) Yes, I think the \g conn syntax seems useful. Good thinking. I agree also that the \S syntax has problems and we would wish to avoid them. I would still like a way to change the default background session. That will considerably reduce the number of changes people would need to make to long scripts in order to be able to use this facility. For example, if we have a script with 100 commands in, we may find that commands 1-50 and 51-100 are in two groups. Commands 1-50 are each dependent upon the previous command, as are 51-100. But the two groups are independent of each other. If we use the \g syntax only, we would need to make 100 changes to the script to send commands to the right session. If we had the capability to say use this background session as the default session to send commands to, then we would be able to add parallelism to the script by just making 2 changes: one prior to command 1 and one prior to command 51. The original \S command had that capability, but was designed to actually change into that session, giving the problems discussed. Something like \S (don't care what syntax, though) would definitely simplify scripting, which I think will translate directly into fewer bugs for users. I note \b is available... short for background. Though I really don't care what we call that command though, just want the capability. Also, I don't want to have to count cwaits, so I'd like a command to say wait for all background sessions that have active statements and for that to be the default. For simplicity, \cwait would do this by default. So this script \c conn1 \c conn2 ... ALTER TABLE ... ADD PRIMARY KEY \g conn1 ALTER TABLE ... ADD FOREIGN KEY \g conn1 ALTER TABLE ... ADD FOREIGN KEY \g conn1 ALTER TABLE ... ADD FOREIGN KEY \g conn1 ... ALTER TABLE ... ADD PRIMARY KEY \g conn2 ALTER TABLE ... ADD FOREIGN KEY \g conn2 ALTER TABLE ... ADD FOREIGN KEY \g conn2 ALTER TABLE ... ADD FOREIGN KEY \g conn2 ALTER TABLE ... ADD FOREIGN KEY \g conn2 ... \cwait \cwait would now become \c conn1 \c conn2 ... \b conn1 ALTER TABLE ... ADD PRIMARY KEY ... ALTER TABLE ... ADD FOREIGN KEY ALTER TABLE ... ADD FOREIGN KEY ALTER TABLE ... ADD FOREIGN KEY ... \b conn2 ALTER TABLE ... ADD PRIMARY KEY ... ALTER TABLE ... ADD FOREIGN KEY
Re: [HACKERS] [PATCHES] column level privileges
* Andrew Dunstan ([EMAIL PROTECTED]) wrote: Tom Lane wrote: I'm not sure where we go from here. Your GSOC student has disappeared, right? Is anyone else willing to take up the patch and work on it? No, he has not disappeared at all. He is going to work on fixing issues and getting the work up to SQL99 level. Great! Your review should help enormously. Stephen, perhaps you would like to work with him. I'd be happy to. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
On Wed, May 07, 2008 at 12:01:21AM -0400, Greg Smith wrote: It may be the case that clean row and column filtering at the SQL layer are pre-requisites for a clean SELinux implementation, where the only difference is that the permission checks are handled by asking SELinux instead of looking in the catalog. That strikes me as an approach more likely to be fruitful. I get the point about imposing all the restrictions at the SELinux layer. But the way to do that, I think, is to make the individual policies possible to implement in PostgreSQL simpliciter, and then have some interface to the SELinux permissions system so that it becomes possible to set those definitions outside Postgres. (I know that the latter raises all sorts of nasty DoS scenarios. That's clearly one of the problems that will need addressing.) Again, I support the effort in principle; I'm just not sure that the current proof-of-concept work is what will be needed to address the design goals. I do think that somewhat clearer scope definitions would be a big help in deciding which modifications are really needed, and where. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Behaviour of MERGE with complex Rules
It seems there is strange behaviour coming from trying to apply complex Rules to the MERGE statement. My proposal from here is to allow MERGE to work with Rules, but only when the Rules are equivalent to simply updatable views. This would restrict MERGE somewhat, yet be entirely compatible with SQL Standard behaviour of MERGE, Views etc.. Let's look at some of the strangeness: Rules allow you to define something like this CREATE RULE foo_rule AS ON INSERT TO foo DO ALSO INSERT foo2 ...; so that any insert into foo becomes 2 inserts, 1 into foo, 1 into foo2. Now if we do an INSERT INTO FOO SELECT this gets rewritten into 1. INSERT INTO foo SELECT ... 2. INSERT INTO foo2 SELECT ... This means that we fire statement-level insert triggers on foo and foo2. By analogy, we might expect MERGE to behave similarly. That could be true with trivial examples such as MERGE INTO foo USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT though with a statement like this MERGE INTO foo USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT WHEN MATCHED UPDATE what do we expect to happen exactly? We run the MERGE statement twice, with the insert statements permuted? But what happens with the UPDATE? Especially if there are also rules that apply to UPDATE. Would we run it four times? No, the MERGE query must run once and we must handle the rules within that single execution. Which means if we do that then MERGE acts differently with Rules than does an INSERT SELECT. Which feels like a warning... So we must replace the INSERT with INSERT foo; INSERT foo2 and run the MERGE. If we do this do we treat the second INSERT as a separate statement each time it is executed? If so a statement-level trigger on INSERT foo2 would be executed once for each row we insert. To make that happen correctly we would need to apply the rule as if the INSERT were a top-level statement, then execute it within MERGE as if it were not a top-level statement (i.e. do not execute statement-level triggers for that statement). If we have AFTER ROW triggers, should they be executed after each execution of the sub-statement? or should they be executed in a group at the end of the MERGE statement, as would happen if an INSERT SELECT. What would happen if one of the rules contained a MERGE statement? We'd have to recursively apply rules down into the sub-statements of the MERGE, yet unravel the trigger behaviour correctly. All of this makes the hair on the back of my neck stand up. It's taken a while to realise these issues exist. This feels to me like even stranger behaviour might lurk somewhere there. This is a long way from clear behaviour and makes me think it will be a long way from a clean and useful implementation. Clear, obvious behaviour for MERGE only seems possible when we have some restrictions on rules. My proposal is to throw an ERROR Cannot resolve rules for MERGE statement, except when the rules meet the following restrictions: * allow MERGE with rules which say DO NOTHING e.g. CREATE RULE foo_rule AS ON INSERT TO foo DO NOTHING; That's fairly trivial * allow MERGE when rules which are merely redirections e.g. CREATE RULE foo_rule AS ON INSERT TO foo DO INSTEAD INSERT foo2 So only rules that have a single replacement statement. This is roughly equivalent to a rule we might create to emulate a simply updatable view. * allow rules only when all of the actions are similarly redirected, so we only ever need to make changes to one table i.e. SELECT, UPDATEs, INSERTs and DELETEs are all redirected So a MERGE statement like this MERGE INTO foo USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT WHEN MATCHED UPDATE with these rules CREATE RULE foo_i AS ON INSERT TO foo DO INSTEAD INSERT foo2 CREATE RULE foo_u AS ON UPDATE TO foo DO INSTEAD UPDATE foo2 CREATE RULE foo_d AS ON DELETE TO foo DO INSTEAD DELETE foo2 would simply be equivalent to MERGE INTO foo2 USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT WHEN MATCHED UPDATE In fact, that might even be a better clue as to how to complete the implementation of updatable views. So: do we all agree with the restriction on MERGE to only work with rules equivalent to simple updatability? If we do, should the implementation of MERGE go on hold until after updatable views are added (with the required hooks)? -- Simon Riggs 2ndQuadrant http://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] [PATCHES] Testing pg_terminate_backend()
Magnus, others, how is the SIGTERM testing going? --- Bruce Momjian wrote: bruce wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: The closest thing I can think of to an automated test is to run repeated sets of the parallel regression tests, and each time SIGTERM a randomly chosen backend at a randomly chosen time. Then see if anything funny Yep, that was my plan, plus running the parallel regression tests you get the possibility of 2 backends. I was intentionally suggesting only one kill per test cycle. Multiple kills will probably create an O(N^2) explosion in the set of possible downstream-failure deltas. I doubt you'd really get any improvement in testing coverage to justify the much larger amount of hand validation needed. It also strikes me that you could make some simple alterations to the regression tests to reduce the set of observable downstream deltas. For example, anyplace where a test loads a table with successive INSERTs and that table is used by later tests, wrap the INSERT sequence with BEGIN/END. Then there is only one possible downstream delta (empty table) and not N different possibilities for an N-row table. I have added pg_terminate_backend() to use SIGTERM and will start running tests as discussed with Tom. I will post my scripts too. Attached is my test script. I ran it for 14 hours (asserts on), running 450 regression tests, with up to seven backends killed per regression test. I have processed the combined regression.diffs files by pickouting out all the new error messages. I don't see anything unusual in there. Should I run it differently? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + #!/bin/bash REGRESSION_DURATION=80# average duration of regression test in seconds OUTFILE=/rtmp/regression.sigterm # To analyze output, use: # grep '^\+ *[A-Z][A-Z]*:' /rtmp/regression.sigterm | sort | uniq | less cd /pg/test/regress while : do ( SLEEP=`expr $RANDOM \* $REGRESSION_DURATION / 32767` echo Sleeping $SLEEP seconds sleep $SLEEP echo Trying kill # send up to 7 kill signals for X in 1 2 3 4 5 6 7 do psql -p 55432 -qt -c SELECT pg_terminate_backend(stat.procpid) FROM (SELECT procpid FROM pg_stat_activity ORDER BY random() LIMIT 1) AS stat template1 2 /dev/null if [ $? -eq 0 ] thenecho Kill sent fi sleep 5 done ) gmake check wait [ -s regression.diffs ] cat regression.diffs $OUTFILE done -- Sent via pgsql-patches mailing list ([EMAIL PROTECTED]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] [EMAIL PROTECTED]: Re: [BUGS] Problem identifying constraints which should not be inherited]
Alex Hunsaker [EMAIL PROTECTED] writes: Currently this loops through all the constraints for a relation (old behavior of MergeAttributesIntoExisting)... Do you think its worth adding a non-unique index to speed this up? No. If we were to refactor pg_constraint as I mentioned earlier, then it could have a natural primary key (reloid, constrname) (replacing the existing nonunique index on reloid) and then a number of things could be sped up. But just piling more indexes on a fundamentally bad design doesn't appeal to me ... Will review the revised patch today. 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] [0/4] Proposal of SE-PostgreSQL patches
KaiGai Kohei [EMAIL PROTECTED] writes: Tom Lane wrote: * It does not come close to passing the regression tests. I saw a lot of ! ERROR: unrecognized node type: 903 which suggests that something's been screwed up about parse analysis (903 = T_A_Const, which shouldn't get further than parse analysis), Could you tell me what queries hit these errors? I remember seeing it on some EXECUTEs, but you really ought to run the tests for yourself. A *minimum* requirement on any submitted patch is that it should pass the regression tests. 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
[HACKERS] Posting to hackers and patches lists
Folks, can we avoid posting an email to both hackers and patches lists? I understand why people do it, but it is best avoided, I think. If you feel the need to keep patch discussion on hackers, please post just the patch to patches and a summary to hackers. Or better yet, have a URL to the patch in an email to hackers. I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
Bruce Momjian [EMAIL PROTECTED] writes: I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. Actually, I find that that is a truly awful habit and I wish that people would *not* do it that way. There are two reasons why not: * no permanent archive of the submitted patch * reviewer won't know if the submitter changes the patch after he downloads a copy, and in fact nobody will ever know unless the submitter takes the time to compare the eventual commit to what he thinks the patch is 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] [PATCHES] Testing pg_terminate_backend()
Magnus Hagander wrote: It looks pretty good from here. I have an output of about 50 million lines, and the only FATAL stuff is the terminating due to admin command. All other errors look consistent with things like the backend that creates a table gets killed, so anybody trying to access that table later will fail with a does not exist error. OK, how long does a regression test take to run, and how long did you run the script? Then please compute the number of regression runs. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. Actually, I find that that is a truly awful habit and I wish that people would *not* do it that way. There are two reasons why not: * no permanent archive of the submitted patch * reviewer won't know if the submitter changes the patch after he downloads a copy, and in fact nobody will ever know unless the submitter takes the time to compare the eventual commit to what he thinks the patch is This requires the patch submitter to send an email every time they update the URL. The problem with no archive is a problem though. It works for me because I am around to supply versions but I see your point --- perhaps we could make the system have a stable URL but allow for versioning access. Maybe email is a fine interface, of course. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
On Thu, May 8, 2008 at 12:17 AM, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. Actually, I find that that is a truly awful habit and I wish that people would *not* do it that way. There are two reasons why not: * no permanent archive of the submitted patch Yes. I can see how posting a URL to a patch would be convenient, but having the permanent record of the patch as submitted is important. What about uploading patches to the wiki? That way we have the permanent record (change history), as well as the single authoritative location for fetching the latest version. * reviewer won't know if the submitter changes the patch after he downloads a copy, and in fact nobody will ever know unless the submitter takes the time to compare the eventual commit to what he thinks the patch is Well, as long as you send another message to the lists saying I've uploaded a new version of the patch, that URL again is . If you just silently update the patch without telling anybody you're bound to run into problems. 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] [PATCHES] Testing pg_terminate_backend()
Bruce Momjian wrote: Magnus Hagander wrote: It looks pretty good from here. I have an output of about 50 million lines, and the only FATAL stuff is the terminating due to admin command. All other errors look consistent with things like the backend that creates a table gets killed, so anybody trying to access that table later will fail with a does not exist error. OK, how long does a regression test take to run, and how long did you run the script? Then please compute the number of regression runs. Hmm. This looks like somewhere between 10,000 and 20,000 runs. //Magnus -- 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] Posting to hackers and patches lists
Brendan Jurd wrote: On Thu, May 8, 2008 at 12:17 AM, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. Actually, I find that that is a truly awful habit and I wish that people would *not* do it that way. There are two reasons why not: * no permanent archive of the submitted patch Yes. I can see how posting a URL to a patch would be convenient, but having the permanent record of the patch as submitted is important. What about uploading patches to the wiki? That way we have the permanent record (change history), as well as the single authoritative location for fetching the latest version. Right, I was assuming once the patch was uploaded it would be to our infrastructure and would be permanent. * reviewer won't know if the submitter changes the patch after he downloads a copy, and in fact nobody will ever know unless the submitter takes the time to compare the eventual commit to what he thinks the patch is Well, as long as you send another message to the lists saying I've uploaded a new version of the patch, that URL again is . If you just silently update the patch without telling anybody you're bound to run into problems. Yep. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alter + preserving dependencies
Dimitri Fontaine [EMAIL PROTECTED] writes: My 8.3.1 installation psql \h only gives me: Syntax: ALTER VIEW name RENAME TO newname You're not the first person to think that ALTER VIEW covers everything that can be done to a view. I'm starting to think that we should just make ALTER VIEW be an alias for ALTER TABLE (rather than a separate node type as now), and then list in the ALTER VIEW reference page all of the ALTER TABLE variants that will work on views. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alter + preserving dependencies
Tom Lane [EMAIL PROTECTED] wrote: Dimitri Fontaine [EMAIL PROTECTED] writes: My 8.3.1 installation psql \h only gives me: Syntax: ALTER VIEW name RENAME TO newname You're not the first person to think that ALTER VIEW covers everything that can be done to a view. I'm starting to think that we should just make ALTER VIEW be an alias for ALTER TABLE (rather than a separate node type as now), and then list in the ALTER VIEW reference page all of the ALTER TABLE variants that will work on views. From a DBA perspective, it reduces the risk of error if ALTER TABLE only affects tables, not views, and ALTER VIEW only affects views, not tables. -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] Posting to hackers and patches lists
Bruce Momjian wrote: Folks, can we avoid posting an email to both hackers and patches lists? I understand why people do it, but it is best avoided, I think. If you feel the need to keep patch discussion on hackers, please post just the patch to patches and a summary to hackers. Or better yet, have a URL to the patch in an email to hackers. I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. What?! Did you just propose a patch tracker? Bruce? Hmm. I think I need to get a new email client, because this one clearly corrupts the emails I receive ;) //Magnus -- 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] Posting to hackers and patches lists
On Wed, May 7, 2008 at 8:28 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Brendan Jurd wrote: On Thu, May 8, 2008 at 12:17 AM, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. Actually, I find that that is a truly awful habit and I wish that people would *not* do it that way. There are two reasons why not: * no permanent archive of the submitted patch Yes. I can see how posting a URL to a patch would be convenient, but having the permanent record of the patch as submitted is important. What about uploading patches to the wiki? That way we have the permanent record (change history), as well as the single authoritative location for fetching the latest version. Right, I was assuming once the patch was uploaded it would be to our infrastructure and would be permanent. Heck, I dont think patch submitters really care. And Ill do whatever is in the dev faq. But Its a heck of a lot easier (for me) just to send them in email. Plus it seems awkward to move a discussion thats taking place on -hackers over to patches... Granted I could post to patches first, wait an hour then send an email to hackers/reviewer and say hey! updated patch here! But it hardly seems worth it to me... In fact I would argue -patches should go away so we dont have that split. -- 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] Posting to hackers and patches lists
Magnus Hagander wrote: Bruce Momjian wrote: Folks, can we avoid posting an email to both hackers and patches lists? I understand why people do it, but it is best avoided, I think. If you feel the need to keep patch discussion on hackers, please post just the patch to patches and a summary to hackers. Or better yet, have a URL to the patch in an email to hackers. I think it would be helpful for us to provide an infrastructure where people who don't run their own servers to store their patches at a stable URL where they can keep updating the content. I did that with the psql wrap patch and it helped me. What?! Did you just propose a patch tracker? Bruce? Hmm. I think I need to get a new email client, because this one clearly corrupts the emails I receive ;) I have suggested a patch tracker as optional for people before on this list: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00626.php -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
* Magnus Hagander ([EMAIL PROTECTED]) wrote: What?! Did you just propose a patch tracker? Bruce? Hmm. I think I need to get a new email client, because this one clearly corrupts the emails I receive ;) If you want an email and web-based tracking system, RT is wonderful (http://bestpractical.com/rt/)... Enjoy, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Testing pg_terminate_backend()
It looks pretty good from here. I have an output of about 50 million lines, and the only FATAL stuff is the terminating due to admin command. All other errors look consistent with things like the backend that creates a table gets killed, so anybody trying to access that table later will fail with a does not exist error. //Magnus Bruce Momjian wrote: Magnus, others, how is the SIGTERM testing going? --- Bruce Momjian wrote: bruce wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: The closest thing I can think of to an automated test is to run repeated sets of the parallel regression tests, and each time SIGTERM a randomly chosen backend at a randomly chosen time. Then see if anything funny Yep, that was my plan, plus running the parallel regression tests you get the possibility of 2 backends. I was intentionally suggesting only one kill per test cycle. Multiple kills will probably create an O(N^2) explosion in the set of possible downstream-failure deltas. I doubt you'd really get any improvement in testing coverage to justify the much larger amount of hand validation needed. It also strikes me that you could make some simple alterations to the regression tests to reduce the set of observable downstream deltas. For example, anyplace where a test loads a table with successive INSERTs and that table is used by later tests, wrap the INSERT sequence with BEGIN/END. Then there is only one possible downstream delta (empty table) and not N different possibilities for an N-row table. I have added pg_terminate_backend() to use SIGTERM and will start running tests as discussed with Tom. I will post my scripts too. Attached is my test script. I ran it for 14 hours (asserts on), running 450 regression tests, with up to seven backends killed per regression test. I have processed the combined regression.diffs files by pickouting out all the new error messages. I don't see anything unusual in there. Should I run it differently? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + #!/bin/bash REGRESSION_DURATION=80 # average duration of regression test in seconds OUTFILE=/rtmp/regression.sigterm # To analyze output, use: # grep '^\+ *[A-Z][A-Z]*:' /rtmp/regression.sigterm | sort | uniq | less cd /pg/test/regress while : do ( SLEEP=`expr $RANDOM \* $REGRESSION_DURATION / 32767` echo Sleeping $SLEEP seconds sleep $SLEEP echo Trying kill # send up to 7 kill signals for X in 1 2 3 4 5 6 7 do psql -p 55432 -qt -c SELECT pg_terminate_backend(stat.procpid) FROM (SELECT procpid FROM pg_stat_activity ORDER BY random() LIMIT 1) AS stat template1 2 /dev/null if [ $? -eq 0 ] thenecho Kill sent fi sleep 5 done ) gmake check wait [ -s regression.diffs ] cat regression.diffs $OUTFILE done -- Sent via pgsql-patches mailing list ([EMAIL PROTECTED]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
Alex Hunsaker wrote: Right, I was assuming once the patch was uploaded it would be to our infrastructure and would be permanent. Heck, I dont think patch submitters really care. And Ill do whatever is in the dev faq. But Its a heck of a lot easier (for me) just to send them in email. Sure, then just keep sending them via email. I often go through several revisions a day as I get feedback and having all that email volume seems wasteful. Plus it seems awkward to move a discussion thats taking place on -hackers over to patches... Granted I could post to patches first, wait an hour then send an email to hackers/reviewer and say hey! updated patch here! But it hardly seems worth it to me... In fact I would argue -patches should go away so we dont have that split. The goal is for the patches list to just discuss patches, but often there are user API issues that come up after the patch is submitted, and people often want that discussion on hackers. The current email split can certainly be awkward. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
Alex Hunsaker wrote: In fact I would argue -patches should go away so we dont have that split. +1I think the main argument for the split is to keep the large patch emails off the hackers list, but I don't think that limit is so high that it's a problem. People have to gzip their patches to the patches list fairly often 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] Posting to hackers and patches lists
Stephen Frost wrote: * Magnus Hagander ([EMAIL PROTECTED]) wrote: What?! Did you just propose a patch tracker? Bruce? Hmm. I think I need to get a new email client, because this one clearly corrupts the emails I receive ;) If you want an email and web-based tracking system, RT is wonderful (http://bestpractical.com/rt/)... STOP! We really really do NOT need to have this discussion every month of the calendar. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
On Wed, May 7, 2008 at 9:03 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Alex Hunsaker wrote: Plus it seems awkward to move a discussion thats taking place on -hackers over to patches... Granted I could post to patches first, wait an hour then send an email to hackers/reviewer and say hey! updated patch here! But it hardly seems worth it to me... In fact I would argue -patches should go away so we dont have that split. The goal is for the patches list to just discuss patches, but often there are user API issues that come up after the patch is submitted, and people often want that discussion on hackers. The current email split can certainly be awkward. A big part of my problem with the split is if there is a discussion taking place on -hackers I want to be able to reply to the discussion and say well, here is what I was thinking. Sending it to -patches first waiting for it to hit the archive so I can link to it in my reply on -hackers seems pointless and convoluted. But if thats what you want, thats what ill try to do from now on :) For instance the patch Tom reviewed of mine yesterday only -hackers was copied, so I maintained that but also added -patches because I was sending in a patch... I think It will be an ongoing problem though especially for new people as they probably wont understand the logical split... -- 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] Posting to hackers and patches lists
Alex Hunsaker wrote: A big part of my problem with the split is if there is a discussion taking place on -hackers I want to be able to reply to the discussion and say well, here is what I was thinking. Sending it to -patches first waiting for it to hit the archive so I can link to it in my reply on -hackers seems pointless and convoluted. Yea, that is a problem. Adding a new patch to patches while discussing on hackers is a receipe for confusion. But if thats what you want, thats what ill try to do from now on :) For instance the patch Tom reviewed of mine yesterday only -hackers was copied, so I maintained that but also added -patches because I was sending in a patch... Yea, sending to both is probably the worst. I would just post to hackers and mention you sent a new version of the patch to patches --- they usually show up the same time. I think It will be an ongoing problem though especially for new people as they probably wont understand the logical split... Yep, I can hardly explain it. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
Alex Hunsaker wrote: A big part of my problem with the split is if there is a discussion taking place on -hackers I want to be able to reply to the discussion and say well, here is what I was thinking. Sending it to -patches first waiting for it to hit the archive so I can link to it in my reply on -hackers seems pointless and convoluted. But if thats what you want, thats what ill try to do from now on :) For instance the patch Tom reviewed of mine yesterday only -hackers was copied, so I maintained that but also added -patches because I was sending in a patch... I think It will be an ongoing problem though especially for new people as they probably wont understand the logical split... Patches are an integral part of the conversation about development, I think trying to split them up is awkward at best. Do people really still think that the potential for larger messages is really a problem? By the way, what is the actual size limit on hackers vs patches. I would imagine that most patches would already fit in the current hackers limit, especially since you can 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] [0/4] Proposal of SE-PostgreSQL patches
In a digest for Tue, 2008-05-06 at 22:57 -0300, Tom Lane wrote: ...[discussion of SE-PostgreSQL patch deleted]... (And of course the next question after that is why we should want to depend on SELinux at all, rather than implementing row filtering in the framework of SQL permissions...) I would love to see something like this to replace Veil which I develop and support. What sort of row filtering did you have in mind? As a database application developer I have found being able to define access controls in terms of data relationships to be tremendously useful and I would love to see something like this built into postgres. As far as I have been able to understand SE-PostgreSQL, it is aimed at a very security-conscious, and expert, customer base but it cannot offer the sort of relationally-defined security access that Veil is intended for (I'd be happy to be wrong about this). On the other hand Veil is not going to be able to provide the degree of certainty (provability?) of SE-PostgreSQL. As an example of relationally-defined security, suppose I want only the members of a project team to be able to see project information: In the veil demo application ( http://veil.projects.postgresql.org/curdocs/demo-model.html ) we can assign a developer to a project by inserting into the assignments table a record for that developer, the given project and a specific role. Once assigned, the developer can see project_details for that project that previously were unavailable. If row filtering is to be implemented directly within Postgres, I would like this sort of capability to be considered. For the record, Veil was written to provide similar functionality to Oracle's Virtual Private Databases. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Posting to hackers and patches lists
Matthew T. O'connor [EMAIL PROTECTED] writes: Patches are an integral part of the conversation about development, I think trying to split them up is awkward at best. Do people really still think that the potential for larger messages is really a problem? Personally I'd be fine with abandoning -patches and just using -hackers. We could try it for awhile, anyway, and go back if it seems worse. By the way, what is the actual size limit on hackers vs patches. They do have different size limits; we'd have to raise the limit on -hackers if we do this. Marc would know exactly what the limits are. 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] Posting to hackers and patches lists
On Thu, May 8, 2008 at 1:54 AM, Matthew T. O'connor [EMAIL PROTECTED] wrote: Patches are an integral part of the conversation about development, I'd go further than that. Patches ARE conversation about development, they are just in C rather than English. Having one list for the parts of the conversation that are written in C and another for the parts that are in English is bizarre, in my opinion. Especially since you almost always want to accompany your C code with some English commentary. 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] Posting to hackers and patches lists
* Alex Hunsaker [EMAIL PROTECTED] [080507 11:38]: A big part of my problem with the split is if there is a discussion taking place on -hackers I want to be able to reply to the discussion and say well, here is what I was thinking. Sending it to -patches first waiting for it to hit the archive so I can link to it in my reply on -hackers seems pointless and convoluted. Note that even though I'm not a fan of the split, the wait to hit the archive problem is not really a problem. If you sent it, and you know it's message-id, and you can link directly to it: such as: http://archives.postgresql.org/message-id/[EMAIL PROTECTED] a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
[HACKERS] Commit fest going well
Looks like this commit fest is going very well. The wiki is full of activity, and there are lots of active committers. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alter + preserving dependencies
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Le 7 mai 08 à 16:26, Tom Lane a écrit : I'm starting to think that we should just make ALTER VIEW be an alias for ALTER TABLE (rather than a separate node type as now), and then list in the ALTER VIEW reference page all of the ALTER TABLE variants that will work on views. Quite obviously, +1. - -- dim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkgh7MMACgkQlBXRlnbh1bnhzACeM9JSHFd/yBjIO+fFLz+SAFC6 eCgAn05/5y1E7eA/qz27ZsBY5+vxHvsn =zroi -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] [0/4] Proposal of SE-PostgreSQL patches
Andrew, Marc, FWIW, I support and think important the row- and column- level access controls this seems to be proposing, at least in principle. Whether that's a support that will extend to 2x overhead on everything is rather a different matter. Also, I am more than prepared to trade away some cases in order to get a broadly useful functionality (so if you can't hide the existence of a table, but all efforts to learn its contents don't work, I might be willing to support that trade-off). Well, there are two different goals we can satisfy. One is to help support the kind of VPS functionality that Veil is designed for, and the majority of users want. The second goal is upholding the kind of security systems demanded by highly secure environments which have statutory requirements about how security should work. That is, while Veil-like funcitonality is what most developers want, it's not what NSA/Banks/military want, who have their own ideas about security. I think we can conceivably capture both. I do think that SE functionality which goes beyond reasonable SQL requirements should be a build-time options because I don't feasably see ways to implement them that won't cause a big performance hit. Also, I think you should be aware that for serious multilevel security hackers (one of whom will be working on Postgres soon) SEPostgres is the beginning and not the end. One of the requirements of many militaries, for example, is not merely data hiding by data substitution, where the row contents you see depend on your security clearance. Also, re: pg_dump: it's actually a desired feature that, for example, some users only be able to dump a subset of the database. Including some DBAs. One of the issues which SE/Mulitlevel tries to address is what happens if you don't trust your DBA 100%? So if we can retain that, it's actually a feature and not a bug. --Josh -- 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] Posting to hackers and patches lists
Tom Lane wrote: Personally I'd be fine with abandoning -patches and just using -hackers. We could try it for awhile, anyway, and go back if it seems worse. I'd be good with that. The split never made much sense for me. -- 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] Posting to hackers and patches lists
On Wed, May 07, 2008 at 12:20:04PM -0400, Tom Lane wrote: Matthew T. O'connor [EMAIL PROTECTED] writes: Patches are an integral part of the conversation about development, I think trying to split them up is awkward at best. Do people really still think that the potential for larger messages is really a problem? Personally I'd be fine with abandoning -patches and just using -hackers. We could try it for awhile, anyway, and go back if it seems worse. This would make it a little tougher on me as far as maintaining the patches section of the PostgreSQL Weekly News, but I'll deal with it if I need to :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Posting to hackers and patches lists
Tom Lane [EMAIL PROTECTED] writes: Matthew T. O'connor [EMAIL PROTECTED] writes: Patches are an integral part of the conversation about development, I think trying to split them up is awkward at best. Do people really still think that the potential for larger messages is really a problem? Personally I'd be fine with abandoning -patches and just using -hackers. We could try it for awhile, anyway, and go back if it seems worse. I'm for that. By the way, what is the actual size limit on hackers vs patches. They do have different size limits; we'd have to raise the limit on -hackers if we do this. Marc would know exactly what the limits are. Note that even the size limit on -patches is too small for some patches. What I did with previous large patches which were not getting through to patches was put them up on a web page but with a new filename for each version. So the URL for a given version *was* stable, the content never changed. You could check the index page to see if there were more recent versions. I would suggest putting large patches up on the wiki in cases like that now, but isn't there a size limit on the wiki too? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] Posting to hackers and patches lists
David Fetter wrote: On Wed, May 07, 2008 at 12:20:04PM -0400, Tom Lane wrote: Matthew T. O'connor [EMAIL PROTECTED] writes: Patches are an integral part of the conversation about development, I think trying to split them up is awkward at best. Do people really still think that the potential for larger messages is really a problem? Personally I'd be fine with abandoning -patches and just using -hackers. We could try it for awhile, anyway, and go back if it seems worse. This would make it a little tougher on me as far as maintaining the patches section of the PostgreSQL Weekly News, but I'll deal with it if I need to :) Yes, it is going to make scooping patches from the mailing list harder, but the existing split seems to be causing more widespread problems that are harder to ajust. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
On Wed, May 7, 2008 at 2:13 PM, Bruce Momjian [EMAIL PROTECTED] wrote: David Fetter wrote: This would make it a little tougher on me as far as maintaining the patches section of the PostgreSQL Weekly News, but I'll deal with it if I need to :) Yes, it is going to make scooping patches from the mailing list harder, but the existing split seems to be causing more widespread problems that are harder to ajust. Sure but if patch submitters are also sticking them in the wiki maybe this is a non issue? We could also adopt the seemingly standard [PATCH] subject tag so you can filter easily for patches... -- 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] Posting to hackers and patches lists
Alex Hunsaker wrote: On Wed, May 7, 2008 at 2:13 PM, Bruce Momjian [EMAIL PROTECTED] wrote: David Fetter wrote: This would make it a little tougher on me as far as maintaining the patches section of the PostgreSQL Weekly News, but I'll deal with it if I need to :) Yes, it is going to make scooping patches from the mailing list harder, but the existing split seems to be causing more widespread problems that are harder to ajust. Sure but if patch submitters are also sticking them in the wiki maybe this is a non issue? We could also adopt the seemingly standard [PATCH] subject tag so you can filter easily for patches... Anything with a file attachment or ^diff line is probably a diff and we could flag the subject line. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] [ADMIN] 8.2.4 patches? (fwd)
Magnus Hagander wrote: Seems we should have a web site that shows our CVS logs in an easily-readable form. Have you looked at the web output on http://git.postgresql.org? I find this very useful for this kind of thing,and if it fits what we need, we shouldn't build another service to do the same thing. Have other people looked at this? It looks great. Shouldn't we link to it from our developer's site or in the Developer's FAQ, but it is more than just for developers so I am thinking it should be on the roadmap page: http://www.postgresql.org/developer/roadmap And we should be linking to the wiki on that page too instead of my URLs. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] [ADMIN] 8.2.4 patches? (fwd)
On Wed, 7 May 2008 16:41:56 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: Magnus Hagander wrote: Seems we should have a web site that shows our CVS logs in an easily-readable form. Have you looked at the web output on http://git.postgresql.org? I find this very useful for this kind of thing,and if it fits what we need, we shouldn't build another service to do the same thing. http://www.postgresql.org/developer/coding Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate signature.asc Description: PGP signature
Re: [HACKERS] Posting to hackers and patches lists
Alex Hunsaker [EMAIL PROTECTED] writes: Sure but if patch submitters are also sticking them in the wiki maybe this is a non issue? We could also adopt the seemingly standard [PATCH] subject tag so you can filter easily for patches... Hm, I wonder how hard it would be to make a perl script which automatically uploads any attachments sent to -hackers to the wiki. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] [pgsql-hackers] Posting to hackers and patches lists [OT]
and thus spake [EMAIL PROTECTED] [2008.05.07 @ 16:23]: Date: Wed, 07 May 2008 11:18:48 -0400 From: Andrew Dunstan [EMAIL PROTECTED] If you want an email and web-based tracking system, RT is wonderful (http://bestpractical.com/rt/)... STOP! Sorry for biting... I just couldn't read RT and wonderful in the same sentance and keep quiet. -Steve signature.asc Description: Digital signature
Re: [HACKERS] Posting to hackers and patches lists
Gregory Stark wrote: Alex Hunsaker [EMAIL PROTECTED] writes: Sure but if patch submitters are also sticking them in the wiki maybe this is a non issue? We could also adopt the seemingly standard [PATCH] subject tag so you can filter easily for patches... Hm, I wonder how hard it would be to make a perl script which automatically uploads any attachments sent to -hackers to the wiki. Not all that hard, but I'm also pretty sure that's not something we want. To make it any kind of useful we'd need something with a lot more intelligence than just picking up all attachments. //Magnus -- 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] [pgsql-www] [ADMIN] 8.2.4 patches? (fwd)
Joshua D. Drake wrote: -- Start of PGP signed section. On Wed, 7 May 2008 16:41:56 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: Magnus Hagander wrote: Seems we should have a web site that shows our CVS logs in an easily-readable form. Have you looked at the web output on http://git.postgresql.org? I find this very useful for this kind of thing,and if it fits what we need, we shouldn't build another service to do the same thing. http://www.postgresql.org/developer/coding Great, thanks. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Table inheritance surprise
Folks, When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't include foreign key constraints (8.3.1). I believe this is surprising behavior, but maybe not a bug, so I'd like to propose another bit of syntactic sugar, namely LIKE [INCLUDING FOREIGN KEYS] which would do what it looks like it does. What say? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Auto-updated fields
Folks, A co-worker pointed out to me that MySQL has a feature that, properly implemented and maybe extended, could be handy, namely what MySQL calls a timestamp field, so here's a proposal: 1. Create a generic (possibly overloaded) trigger function, bundled with PostgreSQL, which sets a field to some value. For example, a timestamptz version might set the field to now(). 2. Have some kind of pre-processing of CREATE and ALTER statements on tables which would attach the above function to the field at hand, something like: CREATE TABLE foo( last_updated TIMESTAMPTZ_UPDATED(), ... ); which would turn last_updated into a TIMESTAMPTZ with the expected behavior on UPDATEs. What do folks think of this idea? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Remove hacks for old bad qsort() implementations?
Tom, are you intending to remove this part of the sort code?
---
Tom Lane wrote:
There are several places in tuplesort.c (and perhaps elsewhere) where
we explicitly work around limitations of various platforms' qsort()
functions. Notably, there's this bit in comparetup_index_btree
/*
* If key values are equal, we sort on ItemPointer. This does not affect
* validity of the finished index, but it offers cheap insurance against
* performance problems with bad qsort implementations that have trouble
* with large numbers of equal keys.
*/
which I unquestioningly copied into comparetup_index_hash yesterday.
However, oprofile is telling me that doing this is costing
*significantly* more than just returning zero would do:
9081 0.3050 :tuple1 = (IndexTuple) a-tuple;
3759 0.1263 :tuple2 = (IndexTuple) b-tuple;
:
:{
130409 4.3800 :BlockNumber blk1 =
ItemPointerGetBlockNumber(tuple1-t_tid);
34539 1.1601 :BlockNumber blk2 =
ItemPointerGetBlockNumber(tuple2-t_tid);
:
3281 0.1102 :if (blk1 != blk2)
812 0.0273 :return (blk1 blk2) ? -1 : 1;
:}
:{
28 9.4e-04 :OffsetNumber pos1 =
ItemPointerGetOffsetNumber(tuple1-t_tid);
1 3.4e-05 :OffsetNumber pos2 =
ItemPointerGetOffsetNumber(tuple2-t_tid);
:
1 3.4e-05 :if (pos1 != pos2)
48757 1.6376 :return (pos1 pos2) ? -1 : 1;
:}
:
:return 0;
56705 1.9045 :}
Looks to me like we're eating more than seven percent of the total
runtime to do this :-(
Now as far as I can see, the original motivation for this (as stated in
the comment) is entirely dead anymore, since we always use our own qsort
implementation in preference to whatever bogus version a given libc
might supply. What do people think of removing this bit of code in
favor of just returning 0?
I can see a couple of possible objections:
1. Someday we might go back to using platform qsort. (But surely we
could insist on qsort behaving sanely for equal keys.)
2. If you've got lots of equal keys, it's conceivable that having the
index entries sorted by TID offers some advantage in indexscan speed.
I'm dubious that that's useful, mainly because the planner should prefer
a bitmap scan in such a case; and anyway the ordering is unlikely to
be preserved for long. But it's something to think about.
Comments?
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
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
Is there a version of this patch ready for application?
---
Gurjeet Singh wrote:
On Tue, Mar 18, 2008 at 7:46 PM, Andrew Dunstan [EMAIL PROTECTED] wrote:
Gurjeet Singh wrote:
On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
I assume don't want a TODO for this? (Suppress UPDATE no changed
columns)
I am starting to implement this. Do we want to have this trigger
function in the server, or in an external module?
I have the trigger part of this done, in fact. What remains to be done
is to add it to the catalog and document it. The intention is to make it
a builtin as it will be generally useful. If you want to work on the
remaining parts then I will happily ship you the C code for the trigger.
In fact, I just finished writing the C code and including it in the catalog
(Just tested that it's visible in the catalog). I will test it to see if it
does actually do what we want it to.
I have incorporated all the suggestions above. Would love to see your code
in the meantime.
Here's the C code:
Datum
trig_ignore_duplicate_updates( PG_FUNCTION_ARGS )
{
TriggerData *trigData;
HeapTuple oldTuple;
HeapTuple newTuple;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, trig_ignore_duplicate_updates: not called by trigger
manager.);
if( !TRIGGER_FIRED_BY_UPDATE(trigData-tg_event)
!TRIGGER_FIRED_BEFORE(trigData-tg_event)
!TRIGGER_FIRED_FOR_ROW(trigData-tg_event) )
{
elog(ERROR, trig_ignore_duplicate_updates: Can only be executed for
UPDATE, BEFORE and FOR EACH ROW.);
}
trigData = (TriggerData *) fcinfo-context;
oldTuple = trigData-tg_trigtuple;
newTuple = trigData-tg_newtuple;
if (newTuple-t_len == oldTuple-t_len
newTuple-t_data-t_hoff == oldTuple-t_data-t_hoff
HeapTupleHeaderGetNatts(newTuple-t_data) ==
HeapTupleHeaderGetNatts(oldTuple-t_data)
(newTuple-t_data-t_infomask ~HEAP_XACT_MASK)
== (oldTuple-t_data-t_infomask ~HEAP_XACT_MASK)
memcmp( (char*)(newTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
(char*)(oldTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
newTuple-t_len - offsetof(HeapTupleHeaderData, t_bits)
) == 0 )
{
/* return without crating a new tuple */
return PointerGetDatum( NULL );
}
return PointerGetDatum( trigData-tg_newtuple );
}
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17? 29' 34.37N, 78? 30' 59.76E - Hyderabad *
18? 32' 57.25N, 73? 56' 25.42E - Pune
37? 47' 19.72N, 122? 24' 1.69 W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
Not that I know of. I never saw Gurjeet's completed code.
cheers
andrew
Bruce Momjian wrote:
Is there a version of this patch ready for application?
---
Gurjeet Singh wrote:
On Tue, Mar 18, 2008 at 7:46 PM, Andrew Dunstan [EMAIL PROTECTED] wrote:
Gurjeet Singh wrote:
On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
I assume don't want a TODO for this? (Suppress UPDATE no changed
columns)
I am starting to implement this. Do we want to have this trigger
function in the server, or in an external module?
I have the trigger part of this done, in fact. What remains to be done
is to add it to the catalog and document it. The intention is to make it
a builtin as it will be generally useful. If you want to work on the
remaining parts then I will happily ship you the C code for the trigger.
In fact, I just finished writing the C code and including it in the catalog
(Just tested that it's visible in the catalog). I will test it to see if it
does actually do what we want it to.
I have incorporated all the suggestions above. Would love to see your code
in the meantime.
Here's the C code:
Datum
trig_ignore_duplicate_updates( PG_FUNCTION_ARGS )
{
TriggerData *trigData;
HeapTuple oldTuple;
HeapTuple newTuple;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, trig_ignore_duplicate_updates: not called by trigger
manager.);
if( !TRIGGER_FIRED_BY_UPDATE(trigData-tg_event)
!TRIGGER_FIRED_BEFORE(trigData-tg_event)
!TRIGGER_FIRED_FOR_ROW(trigData-tg_event) )
{
elog(ERROR, trig_ignore_duplicate_updates: Can only be executed for
UPDATE, BEFORE and FOR EACH ROW.);
}
trigData = (TriggerData *) fcinfo-context;
oldTuple = trigData-tg_trigtuple;
newTuple = trigData-tg_newtuple;
if (newTuple-t_len == oldTuple-t_len
newTuple-t_data-t_hoff == oldTuple-t_data-t_hoff
HeapTupleHeaderGetNatts(newTuple-t_data) ==
HeapTupleHeaderGetNatts(oldTuple-t_data)
(newTuple-t_data-t_infomask ~HEAP_XACT_MASK)
== (oldTuple-t_data-t_infomask ~HEAP_XACT_MASK)
memcmp( (char*)(newTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
(char*)(oldTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
newTuple-t_len - offsetof(HeapTupleHeaderData, t_bits)
) == 0 )
{
/* return without crating a new tuple */
return PointerGetDatum( NULL );
}
return PointerGetDatum( trigData-tg_newtuple );
}
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17? 29' 34.37N, 78? 30' 59.76E - Hyderabad *
18? 32' 57.25N, 73? 56' 25.42E - Pune
37? 47' 19.72N, 122? 24' 1.69 W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
--
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] minimal update
Andrew Dunstan wrote:
Not that I know of. I never saw Gurjeet's completed code.
This is Gurjeet's code, but it is not complete.
http://archives.postgresql.org/pgsql-hackers/2008-03/msg00668.php
---
cheers
andrew
Bruce Momjian wrote:
Is there a version of this patch ready for application?
---
Gurjeet Singh wrote:
On Tue, Mar 18, 2008 at 7:46 PM, Andrew Dunstan [EMAIL PROTECTED] wrote:
Gurjeet Singh wrote:
On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
I assume don't want a TODO for this? (Suppress UPDATE no changed
columns)
I am starting to implement this. Do we want to have this trigger
function in the server, or in an external module?
I have the trigger part of this done, in fact. What remains to be done
is to add it to the catalog and document it. The intention is to make it
a builtin as it will be generally useful. If you want to work on the
remaining parts then I will happily ship you the C code for the trigger.
In fact, I just finished writing the C code and including it in the catalog
(Just tested that it's visible in the catalog). I will test it to see if it
does actually do what we want it to.
I have incorporated all the suggestions above. Would love to see your code
in the meantime.
Here's the C code:
Datum
trig_ignore_duplicate_updates( PG_FUNCTION_ARGS )
{
TriggerData *trigData;
HeapTuple oldTuple;
HeapTuple newTuple;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, trig_ignore_duplicate_updates: not called by trigger
manager.);
if( !TRIGGER_FIRED_BY_UPDATE(trigData-tg_event)
!TRIGGER_FIRED_BEFORE(trigData-tg_event)
!TRIGGER_FIRED_FOR_ROW(trigData-tg_event) )
{
elog(ERROR, trig_ignore_duplicate_updates: Can only be executed
for
UPDATE, BEFORE and FOR EACH ROW.);
}
trigData = (TriggerData *) fcinfo-context;
oldTuple = trigData-tg_trigtuple;
newTuple = trigData-tg_newtuple;
if (newTuple-t_len == oldTuple-t_len
newTuple-t_data-t_hoff == oldTuple-t_data-t_hoff
HeapTupleHeaderGetNatts(newTuple-t_data) ==
HeapTupleHeaderGetNatts(oldTuple-t_data)
(newTuple-t_data-t_infomask ~HEAP_XACT_MASK)
== (oldTuple-t_data-t_infomask ~HEAP_XACT_MASK)
memcmp( (char*)(newTuple-t_data) +
offsetof(HeapTupleHeaderData,
t_bits),
(char*)(oldTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
newTuple-t_len - offsetof(HeapTupleHeaderData, t_bits)
) == 0 )
{
/* return without crating a new tuple */
return PointerGetDatum( NULL );
}
return PointerGetDatum( trigData-tg_newtuple );
}
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17? 29' 34.37N, 78? 30' 59.76E - Hyderabad *
18? 32' 57.25N, 73? 56' 25.42E - Pune
37? 47' 19.72N, 122? 24' 1.69 W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
Right. In fact, I already had that part in fact - see
http://people.planetpostgresql.org/andrew/index.php?/archives/22-Minimal-Update-Trigger.html
What I was waiting for was the part where it gets put in the catalog,
documented, etc.
cheers
andrew
Bruce Momjian wrote:
Andrew Dunstan wrote:
Not that I know of. I never saw Gurjeet's completed code.
This is Gurjeet's code, but it is not complete.
http://archives.postgresql.org/pgsql-hackers/2008-03/msg00668.php
---
cheers
andrew
Bruce Momjian wrote:
Is there a version of this patch ready for application?
---
Gurjeet Singh wrote:
On Tue, Mar 18, 2008 at 7:46 PM, Andrew Dunstan [EMAIL PROTECTED] wrote:
Gurjeet Singh wrote:
On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
I assume don't want a TODO for this? (Suppress UPDATE no changed
columns)
I am starting to implement this. Do we want to have this trigger
function in the server, or in an external module?
I have the trigger part of this done, in fact. What remains to be done
is to add it to the catalog and document it. The intention is to make it
a builtin as it will be generally useful. If you want to work on the
remaining parts then I will happily ship you the C code for the trigger.
In fact, I just finished writing the C code and including it in the catalog
(Just tested that it's visible in the catalog). I will test it to see if it
does actually do what we want it to.
I have incorporated all the suggestions above. Would love to see your code
in the meantime.
Here's the C code:
Datum
trig_ignore_duplicate_updates( PG_FUNCTION_ARGS )
{
TriggerData *trigData;
HeapTuple oldTuple;
HeapTuple newTuple;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, trig_ignore_duplicate_updates: not called by trigger
manager.);
if( !TRIGGER_FIRED_BY_UPDATE(trigData-tg_event)
!TRIGGER_FIRED_BEFORE(trigData-tg_event)
!TRIGGER_FIRED_FOR_ROW(trigData-tg_event) )
{
elog(ERROR, trig_ignore_duplicate_updates: Can only be executed for
UPDATE, BEFORE and FOR EACH ROW.);
}
trigData = (TriggerData *) fcinfo-context;
oldTuple = trigData-tg_trigtuple;
newTuple = trigData-tg_newtuple;
if (newTuple-t_len == oldTuple-t_len
newTuple-t_data-t_hoff == oldTuple-t_data-t_hoff
HeapTupleHeaderGetNatts(newTuple-t_data) ==
HeapTupleHeaderGetNatts(oldTuple-t_data)
(newTuple-t_data-t_infomask ~HEAP_XACT_MASK)
== (oldTuple-t_data-t_infomask ~HEAP_XACT_MASK)
memcmp( (char*)(newTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
(char*)(oldTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
newTuple-t_len - offsetof(HeapTupleHeaderData, t_bits)
) == 0 )
{
/* return without crating a new tuple */
return PointerGetDatum( NULL );
}
return PointerGetDatum( trigData-tg_newtuple );
}
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17? 29' 34.37N, 78? 30' 59.76E - Hyderabad *
18? 32' 57.25N, 73? 56' 25.42E - Pune
37? 47' 19.72N, 122? 24' 1.69 W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
--
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] minimal update
Andrew Dunstan wrote: Right. In fact, I already had that part in fact - see http://people.planetpostgresql.org/andrew/index.php?/archives/22-Minimal-Update-Trigger.html What I was waiting for was the part where it gets put in the catalog, documented, etc. I can probably do that part. Send over what you have and I will work on it. Thanks. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minimal update
Bruce Momjian wrote:
Andrew Dunstan wrote:
Right. In fact, I already had that part in fact - see
http://people.planetpostgresql.org/andrew/index.php?/archives/22-Minimal-Update-Trigger.html
What I was waiting for was the part where it gets put in the catalog,
documented, etc.
I can probably do that part. Send over what you have and I will work on
it. Thanks.
It's very similar to what Gurjeet posted (but designed to work with
earlier postgres versions)
cheers
andrew
---
|#include postgres.h
#include commands/trigger.h
#include access/htup.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
/* for pre 8.3 */
#ifndef HeapTupleHeaderGetNatts
#define HeapTupleHeaderGetNatts(th) ((th)-t_natts )
#endif
extern Datum min_update_trigger(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(min_update_trigger);
Datum
min_update_trigger(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo-context;
HeapTuple newtuple, oldtuple, rettuple;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, min_update_trigger: not called by trigger manager);
/* and that it's called on update */
if (! TRIGGER_FIRED_BY_UPDATE(trigdata-tg_event))
elog(ERROR, min_update_trigger: not called on update);
/* and that it's called before update */
if (! TRIGGER_FIRED_BEFORE(trigdata-tg_event))
elog(ERROR, min_update_trigger: not called before update);
/* and that it's called for each row */
if (! TRIGGER_FIRED_FOR_ROW(trigdata-tg_event))
elog(ERROR, min_update_trigger: not called for each row);
/* get tuple dat, set default return */
rettuple = newtuple = trigdata-tg_newtuple;
oldtuple = trigdata-tg_trigtuple;
if (newtuple-t_len == oldtuple-t_len
newtuple-t_data-t_hoff == oldtuple-t_data-t_hoff
HeapTupleHeaderGetNatts(newtuple-t_data) ==
HeapTupleHeaderGetNatts(oldtuple-t_data)
(newtuple-t_data-t_infomask ~HEAP_XACT_MASK) ==
(oldtuple-t_data-t_infomask ~HEAP_XACT_MASK)
memcmp(((char *)newtuple-t_data) +
offsetof(HeapTupleHeaderData, t_bits),
((char *)oldtuple-t_data) +
offsetof(HeapTupleHeaderData, t_bits),
newtuple-t_len - offsetof(HeapTupleHeaderData,
t_bits)) == 0)
rettuple = NULL;
return PointerGetDatum(rettuple);
}|
--
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] Lazy constraints / defaults
I am wondering whether people use ALTER TABLE ALTER COLUMN foo SET NOT NULL enough to justify concurrency coding. --- Decibel! wrote: This would be very useful for me, and would satisfy the OP's request. Can we get a TODO? On Mar 9, 2008, at 4:45 PM, Dawid Kuroczko wrote: On Sun, Mar 9, 2008 at 7:25 PM, Tom Lane [EMAIL PROTECTED] wrote: =?ISO-8859-2?Q?Micha=B3_Zaborowski?= [EMAIL PROTECTED] writes: I would like to be able to add CONSTRAINT and/or DEFAULT with out affecting old rows. You mean without actually checking that the old rows satisfy the constraint? There's approximately zero chance that that proposal will be accepted. I think the problem here is to minimize the time when table is held by exclusive lock, Something similar to the CREATE INDEX CONCURRENTLY (i.e. hold exclusive lock for a jiffy, then do the actual work for the old tuples). So, the proposal would read as to add the ability to perform: ALTER TABLE CONCURRENTLY ALTER COLUMN foo SET NOT NULL ...where exclusive lock would be held to place the constraint (so all new tuples would satisfy it), lock would be released and the old tuples would be checked to make sure the constraint is valid. Should a NULL value be found or should the backend die, the constraint should disappear or be marked invalid. Yes, it sounds strange, but... Let's say I have big table, I want to add new column, with DEFAULT and NOT NULL. Normally it means long exclusive lock. So - right now I'm adding plain new column, then DEFAULT, then UPDATE on all rows in chunks, then NOT NULL... Can it be little simpler? Just do it all in one ALTER command. alter table tab add column col integer not null default 42 check (col 0); I think this will not solve the OP's problem. He wants to minimize the time a table is under exclusive lock, and this ALTER command will effectively rewrite the whole table (to add new not null column). Probably a workable solution would be to play with inheritance: -- Add the NULL col colum: ALTER TABLE tab ADD COLUMN col integer; -- Create a table which will have col NOT NULL CREATE TABLE tab_new (LIKE tab INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ) INHERITS (tab); ALTER TABLE tab_new ALTER COLUMN col SET NOT NULL; -- Make the new values go to tab_new, if simple enough same might be done for UPDATEs CREATE RULE insert_new AS ON INSERT TO tab DO INSTEAD INSERT INTO tab_new VALUES (NEW.*); -- Now, make a job which will do something like this: START TRANSACTION ISOLATON LEVEL SERIALIZABLE; UPDATE ONLY tab SET col = 42 WHERE id BETWEEN n AND n + 1000; INSERT INTO tab_new SELECT * FROM ONLY tab WHERE id BETWEEN n AND n + 1000; -- or better: -- INSERT INTO tab_new SELECT a,b,c,42 AS col FROM ONLY tab WHERE id BETWEEN n AND n + 1000 FOR UPDATE; DELETE FROM ONLY tab WHERE id BETWEEN n AND n + 1000; COMMIT; -- Finally, exhange parti^W^W get rid of old tab: SELECT count(*) FROM ONLY tab; -- should be zero ALTER TABLE tab RENAME TO tab_old; ALTER TABLE tab_new RENAME TO tab; ALTER TABLE tab NO INHERIT tab_old; Of course each step should be done in transaction, probably starting with explicit LOCK. And extra care should be taken with respect to the UNIQUE constraints. In short: unless you are 100% sure what you are doing, don't. :-) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for db level triggers
Added to TODO: * Add database and transaction-level triggers http://archives.postgresql.org/pgsql-hackers/2008-03/msg00451.php --- Decibel! wrote: On Mar 13, 2008, at 5:14 PM, James Mansion wrote: James Mansion wrote: In usage: AFTER START clears counters and flags. UPDATE triggers on data set counters and flags. BEFORE COMMIT examines the counters and flags and performs any final validation or adjustments (or external events such as sending a MoM message) I'd like to point out also that AFTER CONNECT is a good opportunity to CREATE TEMP TABLE (be nice if a global temp table definition could be persisted and automatically duplicated into each session, but never mind). +1 on both counts. Can we get a TODO? -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgresql in FreeBSD jails: proposal
Alvaro Herrera wrote: Bruce Momjian wrote: Added to TODO: * Improve detection of shared memory segments being used by other FreeBSD jails http://archives.postgresql.org/pgsql-hackers/2008-01/msg00656.php There's a bit more than that to it -- see http://archives.postgresql.org/pgsql-hackers/2008-01/msg00673.php In short, it's not just a FreeBSD issue, but something a bit more general. Added to TODO: * Improve detection of shared memory segments being used by others by checking the SysV shared memory field 'nattch' http://archives.postgresql.org/pgsql-hackers/2008-01/msg00656.php http://archives.postgresql.org/pgsql-hackers/2008-01/msg00673.php -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-updated fields
David Fetter wrote: Folks, A co-worker pointed out to me that MySQL has a feature that, properly implemented and maybe extended, could be handy, namely what MySQL calls a timestamp field, so here's a proposal: 1. Create a generic (possibly overloaded) trigger function, bundled with PostgreSQL, which sets a field to some value. For example, a timestamptz version might set the field to now(). 2. Have some kind of pre-processing of CREATE and ALTER statements on tables which would attach the above function to the field at hand, something like: CREATE TABLE foo( last_updated TIMESTAMPTZ_UPDATED(), ... ); which would turn last_updated into a TIMESTAMPTZ with the expected behavior on UPDATEs. What do folks think of this idea? Having the pre defined triggers at hand could be useful, especially for people not writing triggers so often to get used to it but I'm really not happy with the idea of magic preprocessing. I guess this is commonly used with timestamp fields so why not include a receipe to the docs under examples for timestamp which shows how to create and use a trigger? I may be wrong but my feeling is, not to much weirdness in the core please :) (I guess mysql had it because of lacking triggers and stuff for a long time?) T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Auto-updated fields
Tino Wildenhain [EMAIL PROTECTED] writes: I may be wrong but my feeling is, not to much weirdness in the core please :) +1 ... we have wasted more than enough man-hours trying to get the magic serial type to play nicely. If I had it to do over, we'd never have put that in at all. The underlying mechanisms are perfectly good --- it's the idea that the user shouldn't need to know what they're doing that causes problems. 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
