Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Tue, 5 May 2009, Tom Lane wrote: I agree that it probably wasn't considered carefully whether pg_bench should do that; but does anyone see a reason not to change it? I thought of one pretty weak use-case for not making this change, but would wager the additional flexibility here is far more likely to be appreciated. I'd say it's a clear net improvement. As for that case...many good database designs put all the user relations into a schema, so that it's easier to do bulk operations on all of them while avoiding catalog tables etc.--less work to filter out pg_class to find them for example. I once did some pgbench testing on a system that included a real accounts table in a named schema. pgbench -i will execute drop table if exists accounts. It had already accidentally wiped out the copy of the accounts table on the system during an earlier test, before the schema policy was in place, leaving everyone wary of it. I was able to defend the risk for running pgbench with the new schema layout by saying that can only execute against public.accounts no matter what the user search_path is, so you're safe now. That made everybody happy. Anyone counting on such behavior could be rudely surprised at this change. For all I know I'm the only person to ever actually run into that particular situation though. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] bytea vs. pg_dump
--On Dienstag, Mai 05, 2009 16:57:50 -0400 Andrew Dunstan and...@dunslane.net wrote: Hex will already provide some space savings over our current encoding method for most byteas anyway. It's not like we'd be making things less efficient space-wise. And in compressed archives the space difference is likely to dissolve to not very much, I suspect. I'm dumb: I don't understand why a hex conversion would be significantly faster than what we have now? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ToDo: Clear table counters on TRUNCATE
I had a deeper look into $subject. As Tom already noted in [1], this can't be done by simply issueing a reset message to the stats collector. TRUNCATE is transactional and can be rolled back. This is becoming more problematic, if someone is using SAVEPOINTs or is going to fill a previously truncated table with new data, does some batch jobs on it and commit the transaction. In this case we want to have accurate live and dead tuple counters, i think. After looking into the stats code (don't beat me, it's my first time looking at that code), i think we can achieve a solution by handling a truncate counter much the same like we do with tuples_inserted and tuples_deleted. We maintain a truncate counter and save it's transactional state within the stats xact structures. This gives us the possiblity to take back any incremented truncate stats when a transaction is aborted. Within the xact (or subxact) state of a backend counter we reset it's live and dead tuples to zero, as soon as we are going to increment the truncate counter. Any subsequent action on the table will adjust them again. On the stats collector side, we could distinguish between tabstat messages with a truncate counter set to zero (no TRUNCATEs at all) or set to any positive value then. A positive truncate counter will lead to reinitialize the live and dead tuple statistics to the last values set within the tabstat message, otherwise we increment live and dead tuple statistics (as we do now). One thing that's still unclear to me is wether we want to reset n_tup_ins and friends accordingly. I don't think that's a good idea, since this steals the possibility to track down heavily used tables from the DBA (and what about autovacuum?) [1] http://archives.postgresql.org//pgsql-hackers/2008-04/msg00240.php -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Values of fields in Rules
--On Dienstag, Mai 05, 2009 20:25:54 -0400 Alvaro Herrera alvhe...@commandprompt.com wrote: Not that I know of (and yes, this sucks). But doesn't this also apply to triggers? I can't think of a reliable way to distinguish specified or unspecified fields in trigger functions as wellmaybe fiddling with DEFAULT expressions. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bytea vs. pg_dump
Bernd Helmle wrote: --On Dienstag, Mai 05, 2009 16:57:50 -0400 Andrew Dunstan and...@dunslane.net wrote: Hex will already provide some space savings over our current encoding method for most byteas anyway. It's not like we'd be making things less efficient space-wise. And in compressed archives the space difference is likely to dissolve to not very much, I suspect. I'm dumb: I don't understand why a hex conversion would be significantly faster than what we have now? Quite apart from anything else you would not need the current loop over the bytea input to calculate the result length - in hex it would just be the input length * 2. 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] bytea vs. pg_dump
On Tue, May 5, 2009 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: I'm thinking plain old pairs-of-hex-digits might be the best tradeoff if conversion speed is the criterion. That's a lot less space-efficient than base64, though. Well, base64 could give a 33% savings, but it's significantly harder to encode/decode. Also, since it has a much larger set of valid data characters, it would be *much* more likely to allow old-style formatting to be mistaken for new-style. Unless we can think of a more bulletproof format selection mechanism, that could be an overriding consideration. another nit with base64 is that properly encoded data requires newlines according to the standard. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bytea vs. pg_dump
Merlin Moncure wrote: On Tue, May 5, 2009 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: I'm thinking plain old pairs-of-hex-digits might be the best tradeoff if conversion speed is the criterion. That's a lot less space-efficient than base64, though. Well, base64 could give a 33% savings, but it's significantly harder to encode/decode. Also, since it has a much larger set of valid data characters, it would be *much* more likely to allow old-style formatting to be mistaken for new-style. Unless we can think of a more bulletproof format selection mechanism, that could be an overriding consideration. another nit with base64 is that properly encoded data requires newlines according to the standard. er, no, not as I read rfc 3548 s 2.1. 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] create if not exists (CINE)
On Wed, May 6, 2009 at 7:22 AM, Asko Oja asc...@gmail.com wrote: It was just yesterday when i wondering why we don't have this feature (i was trying to use it and it wasn't there :). The group of people who think it's unsafe should not use the feature. Clearly this feature would be useful when managing large amounts of servers and would simplify our release process. On Wed, May 6, 2009 at 5:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: [...] Yes, I did. I'm not any more convinced than I was before. In particular, the example you give is handled reasonably well without *any* new features, if one merely ignores object already exists errors. It sounds pretty amazing. Ignoring errors as a suggested way to use PostgreSQL. We run our release scripts inside transactions (with exception of concurrent index creation). So if something unexpected happens we are left still in working state. PostgreSQL ability to do DDL changes inside transaction was one of biggest surprises/improvements when switching from Oracle. Now you try to bring us down back to the level of Oracle :) Hm, You can do it easily today with help of PL/PgSQL, say like this: CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$ BEGIN BEGIN CREATE TABLE foo(i int, t text); EXCEPTION WHEN duplicate_table THEN RAISE NOTICE 'Table foo already exists'; END; BEGIN ALTER TABLE foo ADD COLUMN t text; EXCEPTION WHEN duplicate_column THEN RAISE NOTICE 'Column foo.t already exists'; END; END; ...the only drawback is that you need to have PL/PgSQL installed. :-) Personally I don't like 'CREATE IF NOT EXISTS'. I find it 'messy'. :-) What I wish PostgreSQL would have is ability to do conditional rollback to savepoint. This way one could write a PostgreSQL SQL script that would contain conditional behaviour similar to exceptions handling above. For instance backend could handle sort of EXCEPTION clause: SAVEPOINT create_foo; CREATE TABLE foo(i int, t text); START EXCEPTION WHEN duplicate_table; -- if there was duplicate_table exception, all -- commands within this block are executed. -- if there was no error, all commands are -- ignored, until we reach 'END EXCEPTION;' -- command. ROLLBACK TO create_foo; ALTER TABLE foo ADD COLUMN t text; END EXCEPTION; ...or some \conditional commands at psql client side. Just my 0.02 :) Best regards, Dawid -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : qne...@gmail.com : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- 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] create if not exists (CINE)
On Wed, May 6, 2009 at 9:04 AM, Dawid Kuroczko qne...@gmail.com wrote: On Wed, May 6, 2009 at 7:22 AM, Asko Oja asc...@gmail.com wrote: It was just yesterday when i wondering why we don't have this feature (i was trying to use it and it wasn't there :). The group of people who think it's unsafe should not use the feature. Clearly this feature would be useful when managing large amounts of servers and would simplify our release process. On Wed, May 6, 2009 at 5:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: [...] Yes, I did. I'm not any more convinced than I was before. In particular, the example you give is handled reasonably well without *any* new features, if one merely ignores object already exists errors. It sounds pretty amazing. Ignoring errors as a suggested way to use PostgreSQL. We run our release scripts inside transactions (with exception of concurrent index creation). So if something unexpected happens we are left still in working state. PostgreSQL ability to do DDL changes inside transaction was one of biggest surprises/improvements when switching from Oracle. Now you try to bring us down back to the level of Oracle :) Hm, You can do it easily today with help of PL/PgSQL, say like this: CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$ BEGIN BEGIN CREATE TABLE foo(i int, t text); EXCEPTION WHEN duplicate_table THEN RAISE NOTICE 'Table foo already exists'; END; BEGIN ALTER TABLE foo ADD COLUMN t text; EXCEPTION WHEN duplicate_column THEN RAISE NOTICE 'Column foo.t already exists'; END; END; ...the only drawback is that you need to have PL/PgSQL installed. :-) Well, that and it's a lot more code to do the same thing. Personally I don't like 'CREATE IF NOT EXISTS'. I find it 'messy'. :-) What I wish PostgreSQL would have is ability to do conditional rollback to savepoint. This way one could write a PostgreSQL SQL script that would contain conditional behaviour similar to exceptions handling above. For instance backend could handle sort of EXCEPTION clause: SAVEPOINT create_foo; CREATE TABLE foo(i int, t text); START EXCEPTION WHEN duplicate_table; -- if there was duplicate_table exception, all -- commands within this block are executed. -- if there was no error, all commands are -- ignored, until we reach 'END EXCEPTION;' -- command. ROLLBACK TO create_foo; ALTER TABLE foo ADD COLUMN t text; END EXCEPTION; ...or some \conditional commands at psql client side. I don't think a psql extension is a very good approach, because not everyone wants to run their SQL via psql (I use DBD::Pg, for example). Sucking some of the functionality of PL/pgsql into the main SQL engine could be useful (I'm sure it will meet with overwhelming opposition from someone, though) but if we do I don't see much reason to imagine the syntax as you've done here. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Greg Smith gsm...@gregsmith.com writes: I once did some pgbench testing on a system that included a real accounts table in a named schema. pgbench -i will execute drop table if exists accounts. It had already accidentally wiped out the copy of the accounts table on the system during an earlier test, before the schema policy was in place, leaving everyone wary of it. Seems like the right policy for that is run pgbench in its own database. I doubt that either adding or removing the set search_path command changes the risk of trouble very much. 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] bytea vs. pg_dump
Andrew Dunstan and...@dunslane.net writes: Bernd Helmle wrote: I'm dumb: I don't understand why a hex conversion would be significantly faster than what we have now? Quite apart from anything else you would not need the current loop over the bytea input to calculate the result length - in hex it would just be the input length * 2. Another point is that the current format results in a very large number of backslashes in the output data, which translates to extra time and space at the level of the COPY protocol itself (since that has to double all those backslashes). Of course, base64 would also have these two advantages. 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] bytea vs. pg_dump
Andrew Dunstan wrote:
another nit with base64 is that properly encoded data requires
newlines according to the standard.
er, no, not as I read rfc 3548 s 2.1.
cheers
andrew
Why does encode('my text', 'base64') include newlines in its output? I
think MIME requires text to be broken into 76 char lines but why does
encode do this?
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] text_pattern_ops and complex regexps
Greetings,
I've run into an annoying issue which I would think could be handled
better. Basically, indexes using text_pattern_ops don't work with
some complex regexps even when they (imv anyway) could. I'm willing
to believe I'm wrong about the potential to use them, or that my
regexp is wrong, but I don't see it.
Test case:
create table text_test (name text);
insert into text_test values ('North');
insert into text_test values ('North West');
create index text_test_name_idx on text_test using btree (name
text_pattern_ops);
set enable_seqscan = false; -- just to show the test
-- works fine
explain analyze select * from text_test where name ~ '^(North)';
-- works fine
explain analyze select * from text_test where name ~ '^(North)( West)';
-- doesn't work
explain analyze select * from text_test where name ~ '^(North)(| West)';
Results:
CREATE TABLE
INSERT 0 1
INSERT 0 1
CREATE INDEX
SET
QUERY PLAN
---
Index Scan using text_test_name_idx on text_test (cost=0.00..8.27 rows=1
width=32) (actual time=0.071..0.077 rows=2 loops=1)
Index Cond: ((name ~=~ 'North'::text) AND (name ~~ 'Norti'::text))
Filter: (name ~ '^(North)'::text)
Total runtime: 0.121 ms
(4 rows)
QUERY PLAN
---
Index Scan using text_test_name_idx on text_test (cost=0.00..8.27 rows=1
width=32) (actual time=0.176..0.178 rows=1 loops=1)
Index Cond: ((name ~=~ 'North'::text) AND (name ~~ 'Norti'::text))
Filter: (name ~ '^(North)( West)'::text)
Total runtime: 0.209 ms
(4 rows)
QUERY PLAN
Seq Scan on text_test (cost=1.00..10001.03 rows=1 width=32)
(actual time=0.013..0.019 rows=2 loops=1)
Filter: (name ~ '^(North)(| West)'::text)
Total runtime: 0.045 ms
(3 rows)
I don't see why the last case can't use the index. Obviously, for
this example case, doing a Seq Scan is fine but with the real data set
there are cases where an index could help.
Any help would be greatly appreciated.
Thanks,
Stephen
signature.asc
Description: Digital signature
[HACKERS] Some questions about PostgreSQL source code
Hello all! I need help in study internal structures of PosrgreSQL. Sorry for my bad english. I try to get information from source code and spend five days for that, but now have many questions and few understanding =( Source code it's clear, great commented, but studing so difficult system as DBMS it's very strong only from source code. How to PostgreSQL process query? I found some description on http://anoncvs.postgresql.org/cvsweb.cgi/~checkout~/pgsql/src/tools/backend/index.htmlhttp://anoncvs.postgresql.org/cvsweb.cgi/%7Echeckout%7E/pgsql/src/tools/backend/index.html Nevetheless i have questions. Parser translate from text of query to AST. 1) Than AST go to planner for plan normalization and optimization. Planner work on AST structures, or build self internal tree of logical plan? 2) Who set types of any columns? Parser or planner? After planner, called physical plan - executor. 1) Where in source build executor's node from logical plan (result of planner)? 2) How to executor's node bulding, linked, and use one another? For example how to linked Table Scan and Sort on query select a,b,c,d from table order by a,b? Let's assume query work without indexes, for simple describing. 3) What the function called on Prepare/Execute? How this calls translated to executor's nodes? I try look for this information in source code, and found execAim.c, with big swtich. In that switch mixed brachnes of nodes, node states, some expressions and aggregation. What is mind that switch in execAim.c? How to Prepare/Execute/Fetch work with executor's nodes? 4) How to manipulate data on the nodes? I understand from comments, what every node use own childs for get tuple, where tuple - list of cells. I didn't found cells in source code =( Can you descrivbe me, how to one node get data from source node, return data for parent, and what is data and where i can found in source code this entity? For start, this questions it's very important for me. Thank you.
Re: [HACKERS] text_pattern_ops and complex regexps
Stephen Frost sfr...@snowman.net writes: I don't see why the last case can't use the index. The planner's understanding of regexps is far weaker than yours. (In particular, I think it's set up to abandon optimization if it sees | anywhere.) 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] text_pattern_ops and complex regexps
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: I don't see why the last case can't use the index. The planner's understanding of regexps is far weaker than yours. (In particular, I think it's set up to abandon optimization if it sees | anywhere.) That's kind of what I figured from the empirical data. My hope was that it might be something which could be fixed. Is this entirely the planner's doing (eg: PG code)? Perhaps this is misguided but I would think that the regexp libraries might have some support for give me all anchored required text for this regexp which we could then use in the planner. Certainly in an ideal world we wouldn't have to teach the planner the knowledge that the regexp libraries include for this. Thoughts? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] bytea vs. pg_dump
On Wed, May 6, 2009 at 8:02 AM, Andrew Dunstan and...@dunslane.net wrote: Merlin Moncure wrote: On Tue, May 5, 2009 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: I'm thinking plain old pairs-of-hex-digits might be the best tradeoff if conversion speed is the criterion. That's a lot less space-efficient than base64, though. Well, base64 could give a 33% savings, but it's significantly harder to encode/decode. Also, since it has a much larger set of valid data characters, it would be *much* more likely to allow old-style formatting to be mistaken for new-style. Unless we can think of a more bulletproof format selection mechanism, that could be an overriding consideration. another nit with base64 is that properly encoded data requires newlines according to the standard. er, no, not as I read rfc 3548 s 2.1. PostgreSQL (sort of) follows RFC 2045, not RFC 3548. I don't think it would be a good idea to introduce a second method of encoding base64. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] text_pattern_ops and complex regexps
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: (In particular, I think it's set up to abandon optimization if it sees | anywhere.) That's kind of what I figured from the empirical data. My hope was that it might be something which could be fixed. See regex_fixed_prefix(), but it's a pretty hard problem without writing a complete regex parser. Perhaps this is misguided but I would think that the regexp libraries might have some support for give me all anchored required text for this regexp which we could then use in the planner. I wouldn't see why. It's certainly worth considering to hand the pattern to the regex engine and then burrow into the data structure it builds; but right now we consider that structure to be entirely private to backend/regex/. There's also the problem that we'd have no easy way to determine how much the result depends on the current regex flavor setting. There are some cases now where regex_fixed_prefix deliberately omits possible optimizations because of uncertainty about the flavor. 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] bytea vs. pg_dump
On Tuesday 05 May 2009 17:38:33 Tom Lane wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Bernd Helmle maili...@oopsware.de wrote: Another approach would be to just dump bytea columns in binary format only (not sure how doable that is, though). If that's not doable, perhaps a base64 option for bytea COPY? I'm thinking plain old pairs-of-hex-digits might be the best tradeoff if conversion speed is the criterion. The main problem in any case would be to decide how to control the format option. The output format can be controlled by a GUC parameter. And while we are at it, we can also make bytea understand the new output format on input, so we can offer an end-to-end alternative to the amazingly confusing current bytea format and also make byteain() equally faster at the same time. For distinguishing various input formats, we could use the backslash to escape the format specification without breaking backward compatibilty, e.g., '\hexd41d8cd98f00b204e9800998ecf8427e' With a bit of extra work we can wrap this up to be a more or less SQL- conforming blob type, which would also make a lot of people very happy. -- 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] Some questions about PostgreSQL source code
=?KOI8-R?B?78zFxyDjwdLF1w==?= zabiva...@gmail.com writes: I need help in study internal structures of PosrgreSQL. Sorry for my bad english. I try to get information from source code and spend five days for that, but now have many questions and few understanding =( Source code it's clear, great commented, but studing so difficult system as DBMS it's very strong only from source code. Have you read http://developer.postgresql.org/pgdocs/postgres/overview.html ? Also, many of the backend modules have README files that are worth looking at. 1) Than AST go to planner for plan normalization and optimization. Planner work on AST structures, or build self internal tree of logical plan? Well, both. The input is a query tree and the output is a plan tree. 2) Who set types of any columns? Parser or planner? The parse analysis phase determines all data types. In principle the semantics of the query are fully specified by the query tree. 1) Where in source build executor's node from logical plan (result of planner)? The planner builds the plan tree (see createplan.c). There's also a plan state tree that's built during ExecutorStart to hold run-time variables for each plan node. This is needed because the plan tree is read-only as far as the executor is concerned. I try look for this information in source code, and found execAim.c, with big swtich. In that switch mixed brachnes of nodes, node states, some expressions and aggregation. Uh, no, execAmi just works with planstate trees (I think there's one function in it that works with plan trees). 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] Some questions about PostgreSQL source code
Олег Царев wrote: Parser translate from text of query to AST. 1) Than AST go to planner for plan normalization and optimization. Planner work on AST structures, or build self internal tree of logical plan? The planner works with different structures in different phases of planning. Some transformations are made directly to the Query-tree, which is the format that the parser outputs. In intermediate phases, various other structures are build, e.g Path-trees. The final result of the planner is a Plan-tree. 2) Who set types of any columns? Parser or planner? That's done in the so-called parse analysis phase. The entry point for that is the parse_analyze() function. After planner, called physical plan - executor. 1) Where in source build executor's node from logical plan (result of planner)? InitPlan(). 2) How to executor's node bulding, linked, and use one another? For example how to linked Table Scan and Sort on query select a,b,c,d from table order by a,b? Let's assume query work without indexes, for simple describing. The structure used by the executor is a tree of PlanState nodes (which reflects the planner's Plan-tree). See PlanState struct in execnodes.h. Each executor node (= PlanState) has a pointers to the nodes below it, usually in the lefttree and righttree fields, although some node types like AppendState use different method (AppendState.appendplans array) 3) What the function called on Prepare/Execute? How this calls translated to executor's nodes? I try look for this information in source code, and found execAim.c, with big swtich. In that switch mixed brachnes of nodes, node states, some expressions and aggregation. What is mind that switch in execAim.c? How to Prepare/Execute/Fetch work with executor's nodes? That's used for internal parameters in the executor, not for prepare/execute. They're used for things like correlated subqueries, where the subquery is run repeatedly with different values in the enclosing query. For prepare/execute, the executor is initialized, run, and shut down for each execution. The Plan tree that came from the planner is reused, but the corresponding executor tree (PlanState-tree) is recreated at each execution. 4) How to manipulate data on the nodes? I understand from comments, what every node use own childs for get tuple, where tuple - list of cells. I didn't found cells in source code =( Can you descrivbe me, how to one node get data from source node, return data for parent, and what is data and where i can found in source code this entity? This question I didn't quite understand. The basic mechanism is that the top node of the executor tree is executed, and that asks for a tuple from the node(s) below it as needed (by calling ExecProcNode()), which in turn ask for tuples from their child nodes and so forth. IOW it's a pull system, where the top node pulls the tuples through the tree. The intermediate tuples are stored in so-called tuple table slots. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bytea vs. pg_dump
Peter Eisentraut pete...@gmx.net writes: For distinguishing various input formats, we could use the backslash to escape the format specification without breaking backward compatibilty, e.g., Oh, you're right! I had been thinking that byteain treats \x as just meaning x if x isn't an octal digit, but actually it throws an error for anything except octal digits and backslashes: regression=# select E'\\x'::bytea; ERROR: invalid input syntax for type bytea LINE 1: select E'\\x'::bytea; ^ and a quick check verifies it has always done that. So the ambiguous-input problem is solved if we define the new format(s) to be started by backslash and something that the old code would reject. I'd keep it short, like \x, but there's still room for multiple formats if anyone really wants to go to the trouble. 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] text_pattern_ops and complex regexps
Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: Perhaps this is misguided but I would think that the regexp libraries might have some support for give me all anchored required text for this regexp which we could then use in the planner. I wouldn't see why. It's certainly worth considering to hand the pattern to the regex engine and then burrow into the data structure it builds; but right now we consider that structure to be entirely private to backend/regex/. There's also the problem that we'd have no easy way to determine how much the result depends on the current regex flavor setting. There are some cases now where regex_fixed_prefix deliberately omits possible optimizations because of uncertainty about the flavor. I think changeable regex flavors turned out to be a bad idea. They can wreak all sorts of havoc. You change the setting, SIGHUP, and suddenly your application fails to work as expected. Maybe we should make that setting PGC_POSTMASTER (or just get rid of it?), and provide was to pass flags to change the flavor for particular operations (this is easy for function-based stuff but not so easy for operators). That way it doesn't intrude in stuff like cached plans and so on. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] text_pattern_ops and complex regexps
Alvaro Herrera alvhe...@commandprompt.com writes: I think changeable regex flavors turned out to be a bad idea. They can wreak all sorts of havoc. You change the setting, SIGHUP, and suddenly your application fails to work as expected. Maybe we should make that setting PGC_POSTMASTER (or just get rid of it?), and provide was to pass flags to change the flavor for particular operations (this is easy for function-based stuff but not so easy for operators). That way it doesn't intrude in stuff like cached plans and so on. Maybe so. I think it was originally intended mostly as a backwards-compatibility measure when we added the support for ARE flavor. It's pretty likely that no one changes the flavor setting in practice anymore. If we just locked it down as advanced always then we could simplify the documentation by a measurable amount ... 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] conditional dropping of columns/constraints
Hi,
On 05/04/2009 04:10 PM, Andres Freund wrote:
Would a patch adding 'IF EXISTS' support to:
- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP CONSTRAINT
possibly be accepted?
A first version of a patch is attached:
- allows [ IF EXISTS ] for both, conditional dropping of columns and
constraints
- adds two tiny additions to the alter_table regression suite
- adds minimal documentation (my wording might be completely off)
As this is my first patch to PG I am happy with most sort of feedback.
Andres
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index fe3f388..9678236 100644
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
*** ALTER TABLE replaceable class=PARAMETE
*** 33,39
where replaceable class=PARAMETERaction/replaceable is one of:
ADD [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable replaceable class=PARAMETERtype/replaceable [ replaceable class=PARAMETERcolumn_constraint/replaceable [ ... ] ]
! DROP [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [ SET DATA ] TYPE replaceable class=PARAMETERtype/replaceable [ USING replaceable class=PARAMETERexpression/replaceable ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET DEFAULT replaceable class=PARAMETERexpression/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable DROP DEFAULT
--- 33,39
where replaceable class=PARAMETERaction/replaceable is one of:
ADD [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable replaceable class=PARAMETERtype/replaceable [ replaceable class=PARAMETERcolumn_constraint/replaceable [ ... ] ]
! DROP [ COLUMN ] [ IF EXISTS ] replaceable class=PARAMETERcolumn/replaceable [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [ SET DATA ] TYPE replaceable class=PARAMETERtype/replaceable [ USING replaceable class=PARAMETERexpression/replaceable ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET DEFAULT replaceable class=PARAMETERexpression/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable DROP DEFAULT
*** where replaceable class=PARAMETERact
*** 41,47
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STATISTICS replaceable class=PARAMETERinteger/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD replaceable class=PARAMETERtable_constraint/replaceable
! DROP CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE REPLICA TRIGGER replaceable class=PARAMETERtrigger_name/replaceable
--- 41,47
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STATISTICS replaceable class=PARAMETERinteger/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD replaceable class=PARAMETERtable_constraint/replaceable
! DROP CONSTRAINT [ IF EXISTS ] replaceable class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE REPLICA TRIGGER replaceable class=PARAMETERtrigger_name/replaceable
*** where replaceable class=PARAMETERact
*** 82,88
/varlistentry
varlistentry
! termliteralDROP COLUMN/literal/term
listitem
para
This form drops a column from a table. Indexes and
--- 82,88
/varlistentry
varlistentry
! termliteralDROP COLUMN [ IF EXISTS ]/literal/term
listitem
para
This form drops a column from a table. Indexes and
*** where replaceable class=PARAMETERact
*** 90,95
--- 90,98
dropped as well. You will need to say literalCASCADE/ if
anything outside the table depends on the column, for example,
foreign key references or views.
+ If literalIF EXISTS/literal is specified, no error is thrown
+ if the specified column does not exist. A notice is issued in
+ this case.
/para
/listitem
/varlistentry
*** where replaceable class=PARAMETERact
*** 192,201
/varlistentry
varlistentry
! termliteralDROP CONSTRAINT/literal/term
listitem
para
This form drops the specified constraint on a table.
/para
/listitem
/varlistentry
--- 195,207
/varlistentry
Re: [HACKERS] text_pattern_ops and complex regexps
On Wed, May 06, 2009 at 12:10:49PM -0400, Alvaro Herrera wrote: Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: Perhaps this is misguided but I would think that the regexp libraries might have some support for give me all anchored required text for this regexp which we could then use in the planner. I wouldn't see why. It's certainly worth considering to hand the pattern to the regex engine and then burrow into the data structure it builds; but right now we consider that structure to be entirely private to backend/regex/. There's also the problem that we'd have no easy way to determine how much the result depends on the current regex flavor setting. There are some cases now where regex_fixed_prefix deliberately omits possible optimizations because of uncertainty about the flavor. I think changeable regex flavors turned out to be a bad idea. +1 Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] text_pattern_ops and complex regexps
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: I think changeable regex flavors turned out to be a bad idea. They can wreak all sorts of havoc. You change the setting, SIGHUP, and suddenly your application fails to work as expected. Maybe we should make that setting PGC_POSTMASTER (or just get rid of it?), and provide was to pass flags to change the flavor for particular operations (this is easy for function-based stuff but not so easy for operators). That way it doesn't intrude in stuff like cached plans and so on. Maybe so. I think it was originally intended mostly as a backwards-compatibility measure when we added the support for ARE flavor. It's pretty likely that no one changes the flavor setting in practice anymore. If we just locked it down as advanced always then we could simplify the documentation by a measurable amount ... yeah I don't recall a single incident in the last few years that required playing with the regex flavours Stefan -- 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] text_pattern_ops and complex regexps
Alvaro Herrera alvhe...@commandprompt.com writes: ... Maybe we should make that setting PGC_POSTMASTER (or just get rid of it?), and provide was to pass flags to change the flavor for particular operations (this is easy for function-based stuff but not so easy for operators). BTW, if you are putting it on the application to use some other syntax to get at the old flavors, then there already is an adequate feature built into the regex library: a pattern beginning with (?b) or (?e) will be taken as a BRE or ERE respectively, cf. table 9-19 in current docs. So I don't see any value in inventing something additional. The only reason for regex_flavor to exist is to satisfy applications that were written to expect the pre-7.4 regex syntax to work as-is. If we think there aren't any of those anymore, let's just kill the GUC and be done with it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] text_pattern_ops and complex regexps
* Tom Lane (t...@sss.pgh.pa.us) wrote: If we think there aren't any of those anymore, let's just kill the GUC and be done with it. +1. I'll try to spend some time in backend/regexp and regex_fixed_prefix soon. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] lazy vacuum blocks analyze
My colleague hit interesting problem. His transaction hanged for a several days (PG8.3). We found that transaction (ANALYZE) command) waited on relation lock which had been acquired by lazy vacuum. Unfortunately, lazy vacuum on large table (38GB) takes vry long time - several days. The problem is that vacuum and analyze use same lock. If I understood correctly comment in analyze_rel() function it is not necessary. I think that it is very serious issue and dead space map does not help much in this case, because affected table is heavily modified. If there is not another problem I suggest to use two different locks for vacuum and analyze. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Em Qua, 2009-05-06 às 09:37 -0400, Tom Lane escreveu: Greg Smith gsm...@gregsmith.com writes: I once did some pgbench testing on a system that included a real accounts table in a named schema. pgbench -i will execute drop table if exists accounts. It had already accidentally wiped out the copy of the accounts table on the system during an earlier test, before the schema policy was in place, leaving everyone wary of it. Seems like the right policy for that is run pgbench in its own database. A text warning about this could be shown at start of pgbench if the target database isn't named pgbench, for examplo, or just some text could be added to the docs. regards. -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
Re: [HACKERS] lazy vacuum blocks analyze
Zdenek Kotala wrote: If there is not another problem I suggest to use two different locks for vacuum and analyze. By itself that won't work -- see vac_update_relstats. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Dickson S. Guedes wrote: Em Qua, 2009-05-06 às 09:37 -0400, Tom Lane escreveu: Seems like the right policy for that is run pgbench in its own database. A text warning about this could be shown at start of pgbench if the target database isn't named pgbench, for examplo, or just some text could be added to the docs. I think it would be better that the schema is specified on the command line. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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 vacuum blocks analyze
Zdenek Kotala zdenek.kot...@sun.com writes: My colleague hit interesting problem. His transaction hanged for a several days (PG8.3). We found that transaction (ANALYZE) command) waited on relation lock which had been acquired by lazy vacuum. Unfortunately, lazy vacuum on large table (38GB) takes vry long time - several days. The problem is that vacuum and analyze use same lock. If I understood correctly comment in analyze_rel() function it is not necessary. I think that it is very serious issue and dead space map does not help much in this case, because affected table is heavily modified. If there is not another problem I suggest to use two different locks for vacuum and analyze. We would have to invent another lock type just for ANALYZE. It does not seem worth it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Dickson S. Guedes lis...@guedesoft.net writes: Em Qua, 2009-05-06 às 09:37 -0400, Tom Lane escreveu: Seems like the right policy for that is run pgbench in its own database. A text warning about this could be shown at start of pgbench if the target database isn't named pgbench, for examplo, or just some text could be added to the docs. There already is a prominent warning in the pgbench docs: Caution pgbench -i creates four tables accounts, branches, history, and tellers, destroying any existing tables of these names. Be very careful to use another database if you have tables having these names! regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Wed, 2009-05-06 at 15:13 -0400, Alvaro Herrera wrote: Dickson S. Guedes wrote: Em Qua, 2009-05-06 às 09:37 -0400, Tom Lane escreveu: Seems like the right policy for that is run pgbench in its own database. A text warning about this could be shown at start of pgbench if the target database isn't named pgbench, for examplo, or just some text could be added to the docs. I think it would be better that the schema is specified on the command line. I could see that as an option but applications that use a role should adhere to the rules the DBA sets forth for that role. In this particular case I explicitly said that role bench01 was to connect to the database bench and that his search path was bench01 (thus all tables would be created under the schema bench01). Public should never come into play in that scenario. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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 vacuum blocks analyze
Alvaro Herrera píše v st 06. 05. 2009 v 15:11 -0400: Zdenek Kotala wrote: If there is not another problem I suggest to use two different locks for vacuum and analyze. By itself that won't work -- see vac_update_relstats. It says: * Note another assumption: that two VACUUMs/ANALYZEs on a table can't * run in parallel, nor can VACUUM/ANALYZE run in parallel with a * schema alteration such as adding an index, rule, or trigger. Otherwise * our updates of relhasindex etc might overwrite uncommitted updates. But what two VACUUMs/ANALYZEs on a table exactly means? It is not clear here if VACUUMxANALYZE parallel run is allowed or not. I also don't see explanation why it is not allowed? From code I don't see any problem here. Zdenek -- 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] text_pattern_ops and complex regexps
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: I think changeable regex flavors turned out to be a bad idea. They can wreak all sorts of havoc. You change the setting, SIGHUP, and suddenly your application fails to work as expected. Maybe we should make that setting PGC_POSTMASTER (or just get rid of it?), and provide was to pass flags to change the flavor for particular operations (this is easy for function-based stuff but not so easy for operators). That way it doesn't intrude in stuff like cached plans and so on. Maybe so. I think it was originally intended mostly as a backwards-compatibility measure when we added the support for ARE flavor. It's pretty likely that no one changes the flavor setting in practice anymore. If we just locked it down as advanced always then we could simplify the documentation by a measurable amount ... I know of at least one significant client (OpenACS) that still apparently requires extended flavor. Removing the compatibility option would be a major pain point for some of my clients. PGC_POSTMASTER would be fine, though. 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] text_pattern_ops and complex regexps
On Wed, 2009-05-06 at 15:55 -0400, Andrew Dunstan wrote: I know of at least one significant client (OpenACS) that still apparently requires extended flavor. Removing the compatibility option would be a major pain point for some of my clients. PGC_POSTMASTER would be fine, though. Isn't that why we wouldn't remove it from back releases? Joshua D. Drake cheers andrew -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] text_pattern_ops and complex regexps
Joshua D. Drake wrote: On Wed, 2009-05-06 at 15:55 -0400, Andrew Dunstan wrote: I know of at least one significant client (OpenACS) that still apparently requires extended flavor. Removing the compatibility option would be a major pain point for some of my clients. PGC_POSTMASTER would be fine, though. Isn't that why we wouldn't remove it from back releases? My clients aren't going to be very happy if they can't upgrade because of this. 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] text_pattern_ops and complex regexps
On Wed, 2009-05-06 at 16:10 -0400, Andrew Dunstan wrote: Isn't that why we wouldn't remove it from back releases? My clients aren't going to be very happy if they can't upgrade because of this. Certainly. Nobody wants to make clients unhappy but for the good of the code man, for the good of the code :). 8.3 will be supported for a very long time to come. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Alvaro Herrera alvhe...@commandprompt.com writes: I think it would be better that the schema is specified on the command line. Surely that's more work than the issue is worth. It's also inconvenient to use, because you'd have to remember to give the switch both for the -i run and the normal test runs. 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] text_pattern_ops and complex regexps
Andrew Dunstan and...@dunslane.net writes: I know of at least one significant client (OpenACS) that still apparently requires extended flavor. Is this demonstrable, or just speculation? The incompatibilities between ARE mode and (legal) ERE patterns are pretty darn small. 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] text_pattern_ops and complex regexps
Alvaro Herrera alvhe...@commandprompt.com writes: ... Maybe we should make that setting PGC_POSTMASTER (or just get rid of it?), Another thought here: if we do get persuaded that the regex_flavor GUC has to stay, we could eliminate it as a hazard for planning by changing its scope to PGC_BACKEND. That would be much less restrictive than PGC_POSTMASTER; for instance it'd still work to set it for a particular application via ALTER ROLE. 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] text_pattern_ops and complex regexps
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I know of at least one significant client (OpenACS) that still apparently requires extended flavor. Is this demonstrable, or just speculation? The incompatibilities between ARE mode and (legal) ERE patterns are pretty darn small. It's explicitly documented. Whether or not there is a good basis for the documentation I can't yet say. If that's going to be influential I will dig deeper. 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] Patch to fix search_path defencies with pg_bench
On Wed, 2009-05-06 at 17:42 -0300, Dickson S. Guedes wrote: Em Qua, 2009-05-06 às 16:27 -0400, Tom Lane escreveu: Alvaro Herrera alvhe...@commandprompt.com writes: I think it would be better that the schema is specified on the command line. Surely that's more work than the issue is worth. It's also inconvenient to use, because you'd have to remember to give the switch both for the -i run and the normal test runs. So, in my opinion, the Joshua alternative is a good little change that let pgbench runs in a more flexible way. But, there is the possibility that someone are using an automated script that could be broken by this change? Only if the role pgbench is using as an explicit search_path set. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Em Qua, 2009-05-06 às 16:27 -0400, Tom Lane escreveu: Alvaro Herrera alvhe...@commandprompt.com writes: I think it would be better that the schema is specified on the command line. Surely that's more work than the issue is worth. It's also inconvenient to use, because you'd have to remember to give the switch both for the -i run and the normal test runs. So, in my opinion, the Joshua alternative is a good little change that let pgbench runs in a more flexible way. But, there is the possibility that someone are using an automated script that could be broken by this change? -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
Re: [HACKERS] text_pattern_ops and complex regexps
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Is this demonstrable, or just speculation? The incompatibilities between ARE mode and (legal) ERE patterns are pretty darn small. It's explicitly documented. Whether or not there is a good basis for the documentation I can't yet say. If that's going to be influential I will dig deeper. Our fine manual asserts that the only such incompatibility is that inside square brackets (character alternative lists), ERE mode takes backslash as an ordinary character while ARE mode thinks it begins an escape. Given the additional capabilities afforded by the latter interpretation, and that every other modern regex engine on the planet takes the latter approach *without* giving you any option, it doesn't seem too unreasonable to ask OpenACS to join the twenty-first century ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Em Qua, 2009-05-06 às 13:49 -0700, Joshua D. Drake escreveu: On Wed, 2009-05-06 at 17:42 -0300, Dickson S. Guedes wrote: Em Qua, 2009-05-06 às 16:27 -0400, Tom Lane escreveu: Alvaro Herrera alvhe...@commandprompt.com writes: I think it would be better that the schema is specified on the command line. Surely that's more work than the issue is worth. It's also inconvenient to use, because you'd have to remember to give the switch both for the -i run and the normal test runs. So, in my opinion, the Joshua alternative is a good little change that let pgbench runs in a more flexible way. But, there is the possibility that someone are using an automated script that could be broken by this change? Only if the role pgbench is using as an explicit search_path set. So, in a way to avoid the scenario where a ROLE has an explicit search_path set to schemes that already have tables named same as the pgbench's tables, doesn't makes sense also create a pgbench_ suffix for them? -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Joshua D. Drake j...@commandprompt.com writes: On Wed, 2009-05-06 at 17:42 -0300, Dickson S. Guedes wrote: But, there is the possibility that someone are using an automated script that could be broken by this change? Only if the role pgbench is using as an explicit search_path set. Even then, it's not a problem from the point of view of pgbench --- the tables will still get created and used correctly. The only problem shows up if someone is ignoring the existing warning in the docs and running pgbench in a database that has application tables named accounts etc. If you're doing that you're at considerable risk anyway, no matter *what* we do or don't do with pgbench's search path. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Dickson S. Guedes lis...@guedesoft.net writes: So, in a way to avoid the scenario where a ROLE has an explicit search_path set to schemes that already have tables named same as the pgbench's tables, doesn't makes sense also create a pgbench_ suffix for them? Hm, just rename the standard scenario's tables to pgbench_accounts etc? Sure, but then we break custom pgbench scripts that happen to be using the default tables for their own purposes. There's no free lunch. 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] conditional dropping of columns/constraints
Andres Freund and...@anarazel.de writes: As this is my first patch to PG I am happy with most sort of feedback. Please add your patch to the commit-fest queue here: http://wiki.postgresql.org/wiki/CommitFestInProgress Since we are still busy with 8.4 beta, it's unlikely that anyone will take a close look until the next commit fest begins. FWIW, I took a very fast look through the patch and thought it was at least touching the right places, except I think you missed equalfuncs.c. (It'd be a good idea to grep for all uses of AlterTableCmd struct to see if you missed anything else.) I don't have time now to look closer or do any testing. 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] bytea vs. pg_dump
Bernd Helmle maili...@oopsware.de writes: --On Dienstag, Mai 05, 2009 10:00:37 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Seems like the right response might be some micro-optimization effort on byteaout. Hmm looking into profiler statistics seems to second your suspicion: Normal COPY shows: % cumulative self self total time seconds secondscalls s/call s/call name 31.29 81.3881.38 134487 0.00 0.00 CopyOneRowTo 22.88140.8959.51 134487 0.00 0.00 byteaout 13.44175.8434.95 3052797224 0.00 0.00 appendBinaryStringInfo 12.10207.3231.48 3052990837 0.00 0.00 CopySendChar 8.45229.3121.99 3052797226 0.00 0.00 enlargeStringInfo 3.90239.4510.1455500 0.00 0.00 pglz_decompress I hadn't looked closely at these numbers before, but now that I do, what I think they are telling us is that the high proportion of backslashes in standard bytea output is a real killer for COPY performance. With no backslashes, CopySendChar wouldn't be in the picture at all here, and appendBinaryStringInfo/enlargeStringInfo would be called many fewer times (roughly 134487 not 3052797224) with proportionately more characters processed per call. The inner loop of CopyOneRowTo (I assume CopyAttributeOutText has been inlined into that function) is relatively cheap for ordinary characters and much less so for backslashes, so I bet that number would go down too. And as already noted, byteaout itself works pretty hard to produce the current representation. So I'm now persuaded that a better textual representation for bytea should indeed make things noticeably better here. It would be useful though to cross-check this thought by profiling a case that dumps a comparable volume of text data that contains no backslashes... 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] WIP patch for TODO Item: Add prompt escape to display the client and server versions
This is a WIP patch (for the TODO item in the subject) that I'm putting in the Commit Fest queue for 8.5. regards... -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br psql_escape_client_server_version.patch.bz2 Description: application/bzip signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
[HACKERS] xml2 in 8.4 still alive?
HI, Although xml2 was announced to be removed from 8.4, I found 8.4beta1 documentation has xml2 description. Does it mean that xml2 is available in 8.4 as well? -- -- Koichi Suzuki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xml2 in 8.4 still alive?
Koichi Suzuki koichi@gmail.com writes: Although xml2 was announced to be removed from 8.4, I found 8.4beta1 documentation has xml2 description. Does it mean that xml2 is available in 8.4 as well? Yes. It won't be removed until the functionality is fully covered, and AFAIK we are quite some way from that yet. http://archives.postgresql.org/pgsql-general/2008-05/msg00907.php http://archives.postgresql.org/pgsql-hackers/2008-08/msg00614.php 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
