Re: [HACKERS] Wrote a connect-by feature
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Bertram Scharpf wrote: Wouldn't the release be a good opportunity for providing this little tool? As for whether we could accept this for 8.4, I thought the general consensus was that we should implement the SQL-spec WITH syntax. The only good reason for supporting CONNECT BY would be to be Oracle-compatible, which this patch isn't. (Being Oracle-compatible isn't necessarily good anyway; are we sure they don't have a patent on their way of doing this?) I believe this patch is an update to the table_funcs contrib module. Sincerely, Joshua D Drake Various people have looked at supporting WITH in the past; IIRC Greg Stark has been the most recent contender. I'd suggest joining forces with him if you'd like to make this happen. regards, tom lane - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGvbFuATb/zqfZUUQRAiwTAJ45uppSOwSbk/d0KTENmjkMk6GK0wCgk0Tf kirMXmEOH9fYSYt+CG9UMIM= =D9x7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Wrote a connect-by feature
Joshua D. Drake wrote: I believe this patch is an update to the table_funcs contrib module. I spent 2 minutes looking. It has no Makefile and no comments. It doesn't use our code conventions either. At that stage I stopped looking. The author needs to spend some time looking at the developer documentation and perusing the lists to see what our requirements are if he wants to be taken seriously. Also, he needs to understand that the best way to go about such a project is to float ideas first, code later, not the other way around. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Wrote a connect-by feature
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Andrew Dunstan wrote: Joshua D. Drake wrote: I believe this patch is an update to the table_funcs contrib module. I spent 2 minutes looking. It has no Makefile and no comments. It doesn't use our code conventions either. At that stage I stopped looking. The author needs to spend some time looking at the developer documentation and perusing the lists to see what our requirements are if he wants to be taken seriously. Also, he needs to understand that the best way to go about such a project is to float ideas first, code later, not the other way around. Woah there silver! Read the persons first post. He updated an existing module to fit *his* needs. He then, instead of whining to the lists about a missing feature, implemented what *he* needed. It also appears that he fixed a few bugs *and* increased the usability of the module. He then gave that code back. I say bravo and thanks for the patch. Yes the patch needs some work. I don't argue that but come on. Example 1: Son: Dad, I just took cleaned out the garage. Dad: I see you used the wrong broom and didn't close the garage door Example 2: Son: Dad, I just cleaned out the garage. Dad: Oh? Thanks, In the future use the shop broom and close the garage door. Sheesh, I know that everyone is trying to get the release out but let's not remove our community building in the process. Sincerely, Joshua D. Drkae cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGvclJATb/zqfZUUQRAuGfAJwMg3ZiZy8XBG8cIMea+X+JkXshAwCeKlmf IYxJfh1FAOJ2k+bDt6QKOg4= =o9VJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Wrote a connect-by feature
Joshua D. Drake wrote: Andrew Dunstan wrote: Joshua D. Drake wrote: I believe this patch is an update to the table_funcs contrib module. I spent 2 minutes looking. It has no Makefile and no comments. It doesn't use our code conventions either. At that stage I stopped looking. The author needs to spend some time looking at the developer documentation and perusing the lists to see what our requirements are if he wants to be taken seriously. Also, he needs to understand that the best way to go about such a project is to float ideas first, code later, not the other way around. Woah there silver! Read the persons first post. He updated an existing module to fit *his* needs. He then, instead of whining to the lists about a missing feature, implemented what *he* needed. That might be what he said, but maybe you need to go and actually compare this with the contrib module. And he did whine that nobody was responding to his posts. :-) It also appears that he fixed a few bugs *and* increased the usability of the module. He then gave that code back. I say bravo and thanks for the patch. Again, he said so, but there if there are bugs they should be fixed quite separately from any new feature. Bug fixes we can include in this release and backport if necessary, and he should post patches for those ASAP. I'm not saying no thanks to his code, although we almost certainly don't want the feature in anything like this form. I am saying that if he wants the code to be considered for inclusion, he needs to follow processes and meet standards. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Wrote a connect-by feature
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joe Conway wrote: Joshua D. Drake wrote: Tom Lane wrote: Bertram Scharpf wrote: Wouldn't the release be a good opportunity for providing this little tool? As for whether we could accept this for 8.4, I thought the general consensus was that we should implement the SQL-spec WITH syntax. The only good reason for supporting CONNECT BY would be to be Oracle-compatible, which this patch isn't. (Being Oracle-compatible isn't necessarily good anyway; are we sure they don't have a patent on their way of doing this?) I believe this patch is an update to the table_funcs contrib module. I guess I should weigh in here. I have to agree with Tom, namely that: 1. It is way to late for a massive change to the existing contrib for Postgres 8.3 I am not in anyway promoting this for inclusion in 8.3. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGvdVlATb/zqfZUUQRAmF9AJ99WTbAD4ALyEhajSWVtD62FJ6ieQCfT49s F8UBcgGen6Pl6M9cmVG29UI= =ZV14 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] regexp_matches and regexp_split are inconsistent
On Fri, 10 Aug 2007, Tom Lane wrote:
I noticed the following behavior in CVS HEAD, using a pattern that is
capable of matching no characters:
regression=# SELECT foo FROM regexp_matches('ab cde', $re$\s*$re$, 'g') AS
foo;
foo
---
{}
{}
{ }
{}
{}
{}
{}
(7 rows)
regression=# SELECT foo FROM regexp_split_to_table('ab cde', $re$\s*$re$) AS
foo;
foo
-
a
b
c
d
e
(5 rows)
If you count carefully, you will see that regexp_matches() reports a
match of the pattern at the start of the string and at the end of the
string, and also just before 'c' (after the match to the single space).
However, regexp_split() disregards these degenerate matches of the
same pattern.
Is this what we want? Arguably regexp_split is doing the most
reasonable thing for its intended usage, but the strict definition of
regexp matching seems to require what regexp_matches does. I think
we need to either change one function to match the other, or else
document the inconsistency.
Thoughts?
I'm not sure how many languages do this, but at least perl seems to work
similarly, which makes me guess that it's probably similar in a bunch of
languages. If it is, then we should probably just document the
inconsistency.
Perl seems to document the split behavior with Empty leading (or
trailing) fields are produced when there are positive width matches at the
beginning (or end) of the string; a zero-width match at the beginning (or
end) of the string does not produce an empty field.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] pgcheck - data integrity check
Robert, I am working on a data integrity check tool (pgcheck). I would like to discuss the following issues: I'm a little confused. I assumed that your project would check the pages of a *shut-down* database or one in recovery (single-user) mode as part of recovery after a crash or HW failure. We don't really want users running the database in normal mode if there's any significant chance of data-page corruption. Can you back up and explain what you're trying to accomplish with this tool? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Interesting misbehavior of repalloc()
* Tom Lane ([EMAIL PROTECTED]) wrote: [...] 3. When regexp_matches is done with the current call, it politely releases the chunk, and AllocSetFree sticks it into the freelist for 4K chunks. 4. The next call of regexp_matches asks for a 2K chunk. There's nothing in the 2K chunk freelist, so AllocSetAlloc allocates a new chunk from the end of the context's current memory block. Lather, rinse, repeat --- each cycle adds another entry to the 4K-chunk freelist, which we'll never use. This is likely to be naive, but perhaps it'll help others understand too. Would it be sensible to look at trying to fill a 2K request from the next-larger (4K-chunk) freelist before allocating a new chunk? Could it, essentially, downgrade the 4K chunk into 2 2K chunks when that's what is being asked for (and the 2K freelist is empty, and the 4K freelist isn't, etc)? The realloc-in-place seems like a good idea in general, but this test case does need to be handled in some clean way. Perhaps allowing a downgrade path along with the upgrade path would work well. (If that's what you were suggesting already, then sorry for the noise) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] 2D partitioning of VLDB - sane or not?
Jason, Aside from running into a known bug with too many triggers when creating gratuitous indices on these tables, I feel as it may be possible to do what I want without breaking everything. But then again, am I taking too many liberties with technology that maybe didn't have use cases like this one in mind? Well, you're pushing PostgreSQL partitioning further than it's currently able to go. Right now our range exclusion becomes too costly to be useful somewhere around 300 to 1000 partitions (depending on CPU and other issues) because the constraints are checked linearly. To make your scheme work, you'd need to improve our existing partitioning mechanisms to scale to 100,000 partitions. It would also help you to implement multiple inheritance so that you could have a partition which belonged to two masters. I'd be very interested in seeing you do so, of course, but this may be more hacking than you had in mind. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Interesting misbehavior of repalloc()
Stephen Frost [EMAIL PROTECTED] writes: This is likely to be naive, but perhaps it'll help others understand too. Would it be sensible to look at trying to fill a 2K request from the next-larger (4K-chunk) freelist before allocating a new chunk? That doesn't sound like a very good idea --- in the worst case it could lead to giving out 8K chunks to satisfy very small requests. I realize that you're thinking of looking at only the next-larger freelist, but that would not be enough to solve the problem, because the repalloc'd chunk might've been enlarged more than 2X before it finally got returned to the freelists. (I did not mention it before because it didn't seem relevant, but the test case I was looking at actually does go through 2 rounds of doubling of the regexp_matches workspace before it returns it to the freelists.) So to solve the problem that way you'd have to be willing to use *any* larger chunk to satisfy a request, and that seems to me to be way too inefficient storage-wise. Also, if you're thinking of dividing an existing chunk into two half-size chunks, that doesn't work because of the need for header space for each chunk --- you'd end up with some chunks slightly smaller than standard, which would add its own inefficiencies. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Interesting misbehavior of repalloc()
Tom Lane [EMAIL PROTECTED] writes: Perhaps we should just remove lines 934-982 of aset.c, and always handle small-chunk reallocs with the brute force case. Can anyone see a way to salvage something from the realloc-in-place idea? One thought that comes to mind is to try to make AllocSetFree recognize when it's pfree'ing the last chunk in a memory block, and to handle that by decrementing the freeptr instead of putting the chunk into any freelist. We could also only do the realloc-in-place only if there isn't a 4k chunk in the 4k freelist. I'm imagining that usually there wouldn't be. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] proper way to fix information_schema.key_column_usage view
Tom Lane commits
(http://www.postgresql.org/community/weeklynews/pwn20070121.html)
- Fix incorrect permissions check in
information_schema.key_column_usage view: it was checking a
pg_constraint OID instead of pg_class OID, resulting in relation with
OID n does not exist failures for anyone who wasn't owner of the
table being examined. Per bug #2848 from Laurence Rowe. Note: for
existing 8.2 installations a simple version update won't fix this; the
easiest fix is to CREATE OR REPLACE this view with the corrected
definition.
and from http://www.postgresql.org/docs/8.2/interactive/release-8-2-2.html
Fix incorrect permission check in information_schema.key_column_usage view (Tom)
The symptom is relation with OID n does not exist errors. To get
this fix without using initdb, use CREATE OR REPLACE VIEW to install
the corrected definition found in share/information_schema.sql. Note
you will need to do this in each database.
***
I had to feel my way carrying out this fix, and I don't know if I did
it right - I only know that it appears I no longer have the error.
Please confirm whether I was supposed to execute all of
share/information_schema.sql --- or just the portion that CREATEs or
REPLACEs key_column_usage view.
I did not execute the whole share/information_schema.sql --- I only
executed the part of it referring to the key_column_usage view and
each of the functions necessary to support that. CREATE OR REPLACE the
key_column_usage view gave errors otherwise for numerous missing
functions. That worries me because it makes me think I should execute
the whole thing. Instead I just kept adding the function definitions
until it successfully executed.
This is against a very large database which would take a long time to
restore if running the complete information_schema.sql screws things
up. Also I am not sure I would know right away if it was screwed up or
not. So hopefully you can tell me something such as it's just a view
of info and won't cause changes in your data?
Here's what I executed, successfully, and apparently the error in the
log file has ceased:
CREATE FUNCTION _pg_underlying_index(oid) RETURNS oid
LANGUAGE sql STRICT STABLE
AS $$
SELECT refobjid FROM pg_catalog.pg_depend
WHERE classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND
objid = $1 AND
refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass AND
refobjsubid = 0 AND deptype = 'n'
$$;
CREATE DOMAIN cardinal_number AS integer
CONSTRAINT cardinal_number_domain_check CHECK (value = 0);
CREATE DOMAIN sql_identifier AS character varying;
CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
RETURNS SETOF RECORD
LANGUAGE sql STRICT IMMUTABLE
AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
pg_catalog.array_upper($1,1),
1) as g(s)';
CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
LANGUAGE sql STRICT STABLE
AS $$
SELECT (ss.a).n FROM
(SELECT information_schema._pg_expandarray(indkey) AS a
FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
WHERE (ss.a).x = $2;
$$;
CREATE or REPLACE VIEW key_column_usage AS
SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(nc_nspname AS sql_identifier) AS constraint_schema,
CAST(conname AS sql_identifier) AS constraint_name,
CAST(current_database() AS sql_identifier) AS table_catalog,
CAST(nr_nspname AS sql_identifier) AS table_schema,
CAST(relname AS sql_identifier) AS table_name,
CAST(a.attname AS sql_identifier) AS column_name,
CAST((ss.x).n AS cardinal_number) AS ordinal_position,
CAST(CASE WHEN contype = 'f' THEN
_pg_index_position(_pg_underlying_index(ss.coid),
ss.confkey[(ss.x).n])
ELSE NULL
END AS cardinal_number)
AS position_in_unique_constraint
FROM pg_attribute a,
(SELECT r.oid AS roid, r.relname, nc.nspname AS nc_nspname,
nr.nspname AS nr_nspname,
c.oid AS coid, c.conname, c.contype, c.confkey, c.confrelid,
_pg_expandarray(c.conkey) AS x
FROM pg_namespace nr, pg_class r, pg_namespace nc,
pg_constraint c
WHERE nr.oid = r.relnamespace
AND r.oid = c.conrelid
AND nc.oid = c.connamespace
AND c.contype IN ('p', 'u', 'f')
AND r.relkind = 'r'
AND (NOT pg_is_other_temp_schema(nr.oid))
AND (pg_has_role(r.relowner, 'USAGE')
OR has_table_privilege(r.oid, 'SELECT')
OR has_table_privilege(r.oid, 'INSERT')
OR has_table_privilege(r.oid, 'UPDATE')
Re: [HACKERS] Interesting misbehavior of repalloc()
Gregory Stark [EMAIL PROTECTED] writes: We could also only do the realloc-in-place only if there isn't a 4k chunk in the 4k freelist. I'm imagining that usually there wouldn't be. Or in general, if there's a free chunk of the right size then copy to it, else consider realloc-in-place. Counterintuitive but it might work. I'm not sure how often there wouldn't be a free chunk though ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Wrote a connect-by feature
Hi, Am Samstag, 11. Aug 2007, 10:22:24 -0400 schrieb Andrew Dunstan: Joshua D. Drake wrote: I believe this patch is an update to the table_funcs contrib module. I spent 2 minutes looking. It has no Makefile and no comments. It doesn't use our code conventions either. At that stage I stopped looking. The author needs to spend some time looking at the developer documentation and perusing the lists to see what our requirements are if he wants to be taken seriously. I added a Makefile and a README. I scanned the Developers FAQ but found no pointer to any coding conventions. I at least changed the SQL keywords to upper case although in my eyes this is rather a bad habit than a convention. It compiles and installs correctly as well standalone as in the contribs directory. http://www.bertram-scharpf.de/tmp/connectby.tar.gz or $ cvs -d :pserver:[EMAIL PROTECTED]:/var/cvsroot/open export -D now connectby Also, he needs to understand that the best way to go about such a project is to float ideas first, code later, not the other way around. All I wanted to do is to float an idea by presenting a piece of code that does what I mean instead of describing what it should do if I considered right. In fact the most aspects intruded when I wrote it, not when I wrote about it. Sorry for any inconvenience. Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Wrote a connect-by feature
Bertram Scharpf wrote: All I wanted to do is to float an idea by presenting a piece of code that does what I mean instead of describing what it should do if I considered right. But that's exactly what I was making a point about. If you want to get something included in PostgreSQL (and that's what the -hackers list is about, after all) then writing code first is the wrong way to go about it. You need to discuss the idea and implementation before you write a single line of code, or you run the risk that it will be pretty much rejected out of hand. If you had perused the mailing list archives you would have found that thjis subject has come up before, and as Tom Lane told you we are really only interested in the SQL standard way of doing this. In addition, presenting a piece of code that has not a single comment is not likely to advance your cause at all. You say you read the Developers FAQ, but you clearly ignored this entry: http://www.postgresql.org/docs/faqs.FAQ_DEV.html#item1.4 In fact the most aspects intruded when I wrote it, not when I wrote about it. I don't understand this sentence. Sorry for any inconvenience. There's no inconvenience (except possibly to you). I wouldn't have replied at all except that you didn't seem to understand why people didn't embrace your code with unalloyed enthusiasm and joy. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgcheck - data integrity check
Josh Berkus wrote: Robert, I am working on a data integrity check tool (pgcheck). I would like to discuss the following issues: I'm a little confused. I assumed that your project would check the pages of a *shut-down* database or one in recovery (single-user) mode as part of recovery after a crash or HW failure. We don't really want users running the database in normal mode if there's any significant chance of data-page corruption. Can you back up and explain what you're trying to accomplish with this tool? So far I was taking it just as a tool for validating the data in a database. A tool, which would be runned from psql and which would tell the user, whether the data is ok or not, eventually if there is any corrupted data, tell where the problem is... Just for checking the data integrity I thing this is possible way. It will be probably different in case of the data recovery tool and that is what I am trying to get opinions on.. Maybe one possible way (but probably not very convenient) is to run the check from psql to find out where the problem is and then run recovery tool on the corrupted files or use the sigle-user mode for recovery with the database being turned off. The integrity check tool could be also implemented to be runned on a *shut-down* database (for case of database crash), but I thing that being able to run it under *normal* conditions should be helpful as well. Robert ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] proper way to fix information_schema.key_column_usage view
April Lorenzen [EMAIL PROTECTED] writes: I had to feel my way carrying out this fix, and I don't know if I did it right - I only know that it appears I no longer have the error. Please confirm whether I was supposed to execute all of share/information_schema.sql --- or just the portion that CREATEs or REPLACEs key_column_usage view. You should only have had to CREATE OR REPLACE the one view. I did not execute the whole share/information_schema.sql --- I only executed the part of it referring to the key_column_usage view and each of the functions necessary to support that. CREATE OR REPLACE the key_column_usage view gave errors otherwise for numerous missing functions. That worries me because it makes me think I should execute the whole thing. Instead I just kept adding the function definitions until it successfully executed. What it sounds like to me is you had the wrong search_path set, and so what you've done is create a new copy of the view (and supporting functions) in whichever schema was first in your search path --- possibly public. This isn't a good fix. You should clean out the extraneous view and supporting functions, and try again with set search_path = information_schema; regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
