Re: [HACKERS] Hashagg planning bug (8.0.1)
On T, 2005-05-10 at 13:17 -0400, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: It's the = operator that Slony adds for xxid comparisons. I didn't even think of changes Slony would have made. ssdb=# select * from pg_operator where oid = 716373; oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprltcmpop | oprgtcmpop |oprcode| oprrest | oprjoin -+--+--+-++-+--+---++---+++++---+-+--- = | 2200 | 588 | b | t | 716353 | 716353 |16 | 716373 |716372 | 716371 | 716371 | 716371 | 716369 | _ssrep.xxideq | eqsel | eqjoinsel (1 row) I think you need to have a word with the Slony boys. They shouldn't be marking the operator oprcanhash if they aren't providing a valid hash opclass for the datatype. Per the manual: Why does slony use its own transaction id type (xxid) in the first place, why can't we just use standard xid ? Also, perhaps we could get the getcurrentxid() function accepted in postgresql core, maybe as pg_get_current_xid(), perhaps together with pg_oldest_running_xid() and pg_oldest_visible_xid() for determining if there is any benefit from running vacuum. I think that knowing current xid is something other applications besides slony can benefit from. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] Table Partitioning, Part 1
On T, 2005-05-10 at 23:09 +0100, Simon Riggs wrote: On Tue, 2005-05-10 at 16:31 +0300, Hannu Krosing wrote: If all partitions in the query had identical indexes on them, then we have another option. In that case, each index could be thought to form part of a larger index ordered initially on the Partitioning Key (PPK). If the first column was actually the PPK, then the set of indexes would be exactly equivalent to a Global Index. We can call this a Pseudo Global Index. The Pseudo Global Index could also be used to enforce uniqueness. If all of the composite indexes were defined unique and the index contained the PPK as one of its columns, this would work. The index enforces uniqueness within each partition and the PPK enforces uniqueness across partitions because the same PPK value cannot be in two partitions. But only uniqueness of PPK, not any other columns. No, it would work for *any* set of columns that included the PPK. What I meant was that it would quarantee the uniqueness of the whole set only, not any other columns except the PPK. and in case PPK was itself uniqe the other columns don't matter at all. Still there may be cases where smarter access methods make sense as an additional feture, though I cant come up with an example right now. Look at PPUC 2 Join partition elimination, which is the classic Fact to TimeDimension join. Maybe using a global index (+ bitmap scan if number of matching rows is large and not clustered) here is enough for stage 1 implementation. As PPUC2 needs PE elimination step for each and every value, using global index for kind of PE could be the most efficient way to do it for quite large class of PPUC2 queries. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Cost of XLogInsert CRC calculations
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 10 May 2005 23:22 To: Simon Riggs Cc: Bruce Momjian; Mark Cave-Ayland (External); pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Cost of XLogInsert CRC calculations (cut) That's awfully vague --- can't you give any more detail? I have seen XLogInsert eating significant amounts of time (up to 10% of total CPU time) on non-Intel architectures, so I think that dropping down to 32 bits is warranted in any case. But if you are correct then that might not fix the problem on Intel machines. We need more info. regards, tom lane Hi Tom/Simon, Just for the record, I found a better analysis of Adler-32 following some links from Wikipedia. In summary, the problem with Adler-32 is that while it is only slightly less sensitive than CRC-32, it requires roughly a 1k run-in in order to attain full coverage of the bits (with respect to sensitivity of the input). This compares to 4 bytes of run-in required for CRC-32. So unless we can guarantee a minimum of 1k data per Xlog record then Adler-32 won't be suitable. See the following two links for more information: http://en.wikipedia.org/wiki/Adler-32 http://www.ietf.org/rfc/rfc3309.txt One other consideration would be that since CRC-32 calculations for Xlog records occur so often, perhaps the CRC-32 routines could be written in in-line assembler, falling back to C for unsupported processors. It would be interesting to come up with some benchmarks to see if indeed this would be faster than the current C implementation, since as the routine is called so often it could add up to a significant saving under higher loads. Kind regards, Mark. WebBased Ltd 17 Research Way Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Oracle Style packages on postgres
David Fetter wrote: On Tue, May 10, 2005 at 09:49:13PM -0400, Bruce Momjian wrote: David Fetter wrote: On Tue, May 10, 2005 at 06:55:39PM -0400, Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? Well, some kind of nestable namespace for objects, anyhow. How would nested namespaces be different from nested schemas? I thought the two were the same. I was thinking of nested namespaces in the more limited sense of namespaces for bundles of functions/stored procedures rather than a full-on hierarchy where a table can have a schema which resides inside another schema which resides...unless people really want to have it that way. Oh, so allow only functions to sit in the sub-namespace? Yea, we could do that, but it seems sort of limiting. However, I am unclear how we would do sub-namespaces either. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Cleaning up unreferenced table files
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: On Tue, 10 May 2005, Bruce Momjian wrote: The current code is nice and localized and doesn't add any burden on our existing code, which is already complicated enough. I think we either fix checkfiles.c, or we remove it and decide it isn't worth checking for unrefrenced files. Let's pull the patch for now. FWIW, I was OK with the idea of adding something similar to the given patch to find out whether we had a problem or not. With sufficient evidence that lost files are a big problem, I'd be in favor of a mechanism of the kind proposed in Heikki's latest messages. The disconnect for me at the moment is that there's no evidence to justify that amount of effort/risk. A startup-time patch would have provided that evidence, or else have proven that it's not worth spending more time on. Agreed. Imagine a backend creates a table file, then the operating system crashes. I assume WAL wasn't fsync'ed, so there is no way that WAL can discover that unreferenced file. While I think WAL can correct some cases, I don't think it can correct them all, so it seems it is necessary to check the file system against pg_class to catch all the cases. The transaction and file system semantics are just different and need to be checked against each other. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 11:42 PM To: Bruce Momjian Cc: Dave Held; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] There's been a lot of handwaving about nested schemas in this thread, but no one has explained how they could actually *work* given the SQL syntax rules. In general, a is a column from the current table set, a.b is a column b in table/alias a from the current query, a.b.c is a column c from table b in schema a, a.b.c.d is a column d from table c in schema b in catalog a, and any more than that is a syntax error. I do not see how to add nested schemas without creating unworkable ambiguities, not to say outright violations of the spec. Clearly nested schemas would violate the SQL spec, as do the numerous missing features in Postgres. Obviously, they would have to be a sort of non-conforming extension. It's an opportunity for Postgres to take the lead and influence the next standard, I guess. Unless the community decides that it's not worth the hassle, which seems much more likely. I am curious to know what the unworkable ambiguities are. I propose that if there is any ambiguity at all, just fail the parse and leave it to the user to write something sensible. Otherwise, it's just a matter of defining a precise precedence for resolving name scopes, which doesn't seem very tricky at all. That is, if a.b is the name of a schema b nested within a schema a, then a.b.c.d refers to a column d of table c in schema b in schema a. If a is not the name of a schema, then check to see if it's the name of a database. If it is, then a.b.c.d has the meaning you define above. If it's not, then it's an error. The rule is simple: when the identifier has more than two parts, search for the first part among the schemas first, and then the catalogs. For the parts after the first and before the last two, just search the appropriate schemas. As far as I can tell, this syntax is completely backwards-compatible with existing SQL syntax. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 3: 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] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] writes: The rule is simple: when the identifier has more than two parts, search for the first part among the schemas first, and then the catalogs. This doesn't actually work, because there is already ambiguity as to which level the first name is. See for instance the comments in transformColumnRef(). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Views, views, views: Summary of Arguments
I guess I'm having difficulty understanding why the system catalogs themselves and provision of support for information_schema are not sufficient for what exists in core. At one point, there was a stored procedure database for Pl/PgSQL. It seems like a system view service like that could easily be created and maintained independently of what is actually considered the core PostgreSQL distribution. If one of the primary issues is a lack of clarity in the documentation of the system catalogs, then that is certainly something that ought to be addressed. But if another of the primary issues is a need for easier access to the information contained in the system catalogs/information schema, then that can be addressed by a public repository that can certainly be moderated and maintained. Think VPAN (Views of PostgreSQL Archive Network)... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On May 10, 2005, at 12:21 PM, Josh Berkus wrote: Folks, We've meandered a bit on this, so I wanted to summarize the arguments presented on the new system views to date so that we might have some hope of consensus before feature freeze. As I see it, there are 3 main arguments about having the new system views at all. These obviously need to be settled before we go any further on security models, column names, etc. Please add if I've missed anyone's arguments, I'm trying to summarize across 2 weeks of discussion and am obviously not impartial. Argument (1): Are the views useful to users? Pro: Several people, particularly the proposers, contend that they are. They cite as evidence the popularity of related articles on General Bits, commercial precedent, and the prevalence of user-created system views. Mostly, the usefulness is aimed at new users. Con: A few people say that they are not useful, and that the system tables are easily understood. Argument (2): Do they provide sufficiently distinct functionality from the information_schema? Pro: The proposers contend that the information_schema, by SQL spec, is unable to show all PostgreSQL objects in sufficient detail. That the permissions and uniqueness models are wrong for PostgreSQL, and these things are not easily fixed by extension without breaking the SQL spec. That we don't want to confuse the information_schema with PostgreSQL-specific extensions. Con: Several people, most notably Peter, contend that much of the new system views are duplicative of information_schema, and that efforts should be made to extend infomation_schema instead of providing a parallel interface. That we should make serious efforts to support a standard rather than developing a proprietary interface. A few people claimed that there was nothing that information_schema didn't have, or that users didn't need that information anyway. Argument (3): Would the new system views be useful to interface designers? Pro: Christopher Kings-Lynne said yes for phpPgAdmin. Josh argued that we need to look at interface designers who are designing for 3rd-party multi-database products who are not supporting PostgreSQL yet and will be unlikely to learn the system tables. Con: Dave Page said no for pgAdmin. Several people pointed out issues with the idea of maintaining backwards compatibility through abstraction. Others cited argument (2) in favor of information_schema, above. ... thus, as I see it, the *primary* question is in fact argument (2). That is, is information_schema sufficient, and if not, can it be extended without breaking SQL standards? Argument (1) did not seem to have a lot of evidence on the con side, and the strongest argument against (3) is that we should use information_schema. Andrew, can you do a more cohesive set of points on the 2nd half of that question? That is, how much SQL spec would we have to break (other than extension) to cover all of the stuff that pg_sysviews currently covers? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views: Summary of Arguments
Thomas, All, I guess I'm having difficulty understanding why the system catalogs themselves and provision of support for information_schema are not sufficient for what exists in core. Because you can't answer the question: What tables does user phil have update permissions on? or How many overloaded versions of function df_new_company() do I have?, and similar, without doing advanced queries on the system tables. Queries which are prone to mistakes: earlier on this thread a *pgsql hacker* posted a sample system catalog query which contained a mistake. Asking our general users to navigate the complexity of the system catalogs is just not good application practice. I don't really think a VPAN is any kind of solution for this purpose (though I'd like it for other things). The purpose of these views is to make PostgreSQL more user-friendly, and telling people: Oh just go to http://name/of/obscure/site, lookup these 10 views, log in as superuser and load them and you're golden is not much of an improvement in user-friendliness. To reiterate my point previously: these system views are NOT aimed at the people on *this* list; they are for the people on the -NOVICE and -GENERAL lists and IRC and the people who don't yet use PostgreSQL. Please stop thinking exclusively in terms of whether they would be useful to you, personally. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Server instrumentation for 8.1
There's still a lengthy discussion going on whether it's a good idea to add a forth way to read pgsql's schema (pg_* tables, pg_* views, information_schema, did I miss one?), but I'd like to see helper functions for issues *not* covered in the core package. - dbsize has been in contrib for a long time, though it appears to me as quite a basic functionality to find out about storage needs. - The superuser only generic file functions in the admin package have been posted for 8.0, but where (more or less ) silently dropped. These functions allow pgadmin to display the server logs, as well as editing pg_hba.conf and postgresql.conf without console access to whatever-pgsql-is-running-on. I'd like to see this at least as contrib module (the functions are probably safer than pl_sh). Both these modules are bundled with the pgsql win32 installer, and are installed by default. Both are supported by (at least) pgAdmin. - There was a pg_kill_backend function in pre-8.0, but it was dropped because it's too dangerous. Incidentially, I've been in the situation more than once where I needed to kill a backend process that was running wild; alternatively, I'd have to shutdown the whole server. I had to do this on the linux console with kill -9 (fortunately I did have access), or using the win32 task manager (same). This appears even more error prone to me than to point to the malicious process and kill it (through pgadmin/pg_kill_backend) - We don't have a profiling facility to tap an individual backend for a limited period to find out what the client is doing there, so we need to use log_statement for this (I'd like to work on profiling, but I didn't find the time so far). Consequently, we have to deal with long logfiles, containing much stuff we don't need. In the past, I found it to be very helpful if a fresh logfile could be used (on a private installation, stop/start server), that's why my logfile process implementation did include a logfile rotation trigger functionality. Tom didn't need it, so he dropped it. I'd opt for re-adding it again. Yes yes I know, all of these can be done by a local administrator with console access and an editor and cmd line tools, but there are indeed people that do *not* have console access, or like to use decent tools Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 7.3.10 working
has anyone successfully built 7.3.10? I get the following when running
make check on a slackware...
== removing existing temp installation==
== creating temporary installation==
== initializing database system ==
pg_regress: initdb failed
Examine ./log/initdb.log for the reason.
make[2]: *** [check] Error 2
make[2]: Leaving directory `/usr/local/src/postgresql-7.3.10/src/test/regress'
make[1]: *** [check] Error 2
make[1]: Leaving directory `/usr/local/src/postgresql-7.3.10/src/test'
make: *** [check] Error 2
[EMAIL PROTECTED]:/usr/local/src/postgresql-7.3.10$ cat
src/test/regress/log/initdb.log
Running with noclean mode on. Mistakes will not be cleaned up.
The files belonging to this database system will be owned by user postgres.
This user must also own the server process.
The database cluster will be initialized with locale C.
creating directory
/usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data... ok
creating directory
/usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/base... ok
creating directory
/usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/global... ok
creating directory
/usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/pg_xlog... ok
creating directory
/usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/pg_clog... ok
creating template1 database in
/usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/base/1...
/usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/install//usr/local/pgsql-7.3.10/bin/initdb:
line 582: 1095 Segmentation fault $PGPATH/postgres -boot -x1
$PGSQL_OPT $BACKEND_TALK_ARG template1
initdb failed.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets
[redirected to -hackers] Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Is it worth rearranging things for plpython so that it follows the same test layout as the other 2 (i.e. a test subdir with all the test files and a script called runtest that does the work)? Especially if we bring in other PLs as has been discussed, some standard might be useful. Actually, we have a standard: it's pg_regress. The right thing for someone to do is migrate all these tests into the form already used for the main backend and all of contrib. I think this would require a small addition to the pg_regress script to make it configurable as to which PL to install, instead of always installing plpgsql, but that seems like a reasonable thing to do. I'm not sure why it would matter having it there. I would just make the first test to load the language in question - pretty much this, right? CREATE FUNCTION plperl_call_handler () RETURNS language_handler AS '$libdir/plperl' LANGUAGE C; CREATE TRUSTED LANGUAGE plperl HANDLER plperl_call_handler; CREATE LANGUAGE plperlu HANDLER plperl_call_handler; cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Views, views, views: Summary of Arguments
To reiterate my point previously: these system views are NOT aimed at the people on *this* list; they are for the people on the -NOVICE and -GENERAL lists and IRC and the people who don't yet use PostgreSQL. Please stop thinking exclusively in terms of whether they would be useful to you, personally. I think the above paragraph is very important. When doing development it is very easy to think only in terms of what would be useful to you. I have experienced this quite bit when working on the new book as well. It is very difficult for me to write a chapter on installing PostgreSQL for Windows, because for me it is completely useless and fairly, If you can't figure it out, you shouldn't be using PostgreSQL in the first place. However that is not the case with most of our users. Most of our users have no clue what is in the pg_ tables or even how to get access to them. A set of documented views would be very useful in this sense. It would be a lot easier for someone to say: select size from large_ojects_metainfo where loid = 12545; Then the other methods to get the large_object size. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.3.10 working
works for me on FC3 (although it fails the geometry test as usual - I wish we could stop that). I bet you have a library clash. cheers andrew Robert Treat wrote: has anyone successfully built 7.3.10? I get the following when running make check on a slackware... == removing existing temp installation== == creating temporary installation== == initializing database system == pg_regress: initdb failed Examine ./log/initdb.log for the reason. make[2]: *** [check] Error 2 make[2]: Leaving directory `/usr/local/src/postgresql-7.3.10/src/test/regress' make[1]: *** [check] Error 2 make[1]: Leaving directory `/usr/local/src/postgresql-7.3.10/src/test' make: *** [check] Error 2 [EMAIL PROTECTED]:/usr/local/src/postgresql-7.3.10$ cat src/test/regress/log/initdb.log Running with noclean mode on. Mistakes will not be cleaned up. The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. creating directory /usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data... ok creating directory /usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/base... ok creating directory /usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/global... ok creating directory /usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/pg_xlog... ok creating directory /usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/pg_clog... ok creating template1 database in /usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/data/base/1... /usr/local/src/postgresql-7.3.10/src/test/regress/./tmp_check/install//usr/local/pgsql-7.3.10/bin/initdb: line 582: 1095 Segmentation fault $PGPATH/postgres -boot -x1 $PGSQL_OPT $BACKEND_TALK_ARG template1 initdb failed. Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 11, 2005 10:55 AM
To: Dave Held
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] writes:
The rule is simple: when the identifier has
more than two parts, search for the first part among the schemas
^^^
first, and then the catalogs.
This doesn't actually work, because there is already ambiguity as to
which level the first name is. See for instance the comments in
transformColumnRef().
I don't follow. switch (numnames) case 3 is unambiguous under either
syntax. case 1 and 2 are unchanged under my proposed rules. It's
really only case 4+ that is affected. And the change is as follows:
if (numnames MAX_SCHEMA_DEPTH + 3)
{
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg(improper qualified name (too many dotted names): %s,
NameListToString(cref-fields;
return NULL;
}
switch (numnames)
{
case 1: ...
case 2: ...
case 3: ...
default:
{
char* name[MAX_SCHEMA_DEPTH + 3];
char** i;
char** end = name + numnames;
char* colname = name + numnames - 1;
for (i = name; i != end; ++i)
{
/* definition of lnth() should be easy enough to infer */
*i = strVal(lnth(cref-fields));
}
/*
* We check the catalog name and then ignore it.
*/
if (!isValidNamespace(name[0]))
{
if (strcmp(name[0], get_database_name(MyDatabaseId)) != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(cross-database references are not
implemented: %s,
NameListToString(cref-fields;
i = name + 1;
numnames -= 3;
}
else
{
i = name;
numnames -= 2;}
/*
* isValidNamespace() should work like LookupExplicitNamespace()
* except that it should return false on failure instead of
* raising an error
*/
/* Whole-row reference? */
if (strcmp(end[-1], *) == 0)
{
node = transformWholeRowRef(pstate, i, numnames, end[-2]);
break;
}
/*
* Here I've changed the signature of transformWholeRowRef() to
* accept a char** and an int for the schema names
*/
/* Try to identify as a twice-qualified column */
node = qualifiedNameToVar(pstate, i, numnames, end[-1], true);
/*
* And obviously we have to hack qualifiedNameToVar() similarly
*/
if (node == NULL)
{
/* Try it as a function call */
node = transformWholeRowRef(pstate, i, numnames, end[-2]);
node = ParseFuncOrColumn(pstate,
list_make1(makeString(end[-1])),
list_make1(node),
false, false, true);
}
break;
}
}
What am I missing?
__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East, Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: I think this would require a small addition to the pg_regress script to make it configurable as to which PL to install, instead of always installing plpgsql, but that seems like a reasonable thing to do. I'm not sure why it would matter having it there. I would just make the first test to load the language in question - pretty much this, right? CREATE FUNCTION plperl_call_handler () RETURNS language_handler AS '$libdir/plperl' LANGUAGE C; CREATE TRUSTED LANGUAGE plperl HANDLER plperl_call_handler; CREATE LANGUAGE plperlu HANDLER plperl_call_handler; The point is that I'd rather test createlang than duplicate it. (In the back of my mind also is that running createlang is a waste of time for the contrib tests, and so it'd be nice if pg_regress didn't load any PL unless told to.) regards, tom lane ---(end of broadcast)--- TIP 3: 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] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] writes:
/*
* We check the catalog name and then ignore it.
*/
if (!isValidNamespace(name[0]))
{
if (strcmp(name[0], get_database_name(MyDatabaseId)) != 0)
ereport(ERROR,
Which more or less proves my point: the syntax is fundamentally
ambiguous. I suppose people would learn not to use schema names that
match the database they are in, but that doesn't make it a good idea to
have sensible behavior depend on non-overlap of those names.
[ thinks for awhile ... ]
OTOH, what if we pretended that two-level-nested schemas ARE catalogs
in the sense that the SQL spec expects? Then we could get rid of the
pro-forma special case here, which isn't ever likely to do anything more
useful than throw an error anyway. Thus, we'd go back to the pre-7.3
notion that the current Postgres DB's name isn't part of the SQL naming
scheme at all, and instead handle the spec's syntax requirements by
setting up some conventions that make a schema act like what the spec
says is a catalog.
There are some nontrivial issues to be thought about here, like under
what conditions CREATE SCHEMA foo ought to create a top-level schema
versus creating a schema under some other schema that we are pretending
is the active catalog. But it seems on first glance like something
could be worked out.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.3.10 working
Hi, On Wed, 11 May 2005, Robert Treat wrote: has anyone successfully built 7.3.10? I get the following when running make check on a slackware... Except geometry, make check runs fine on RHEL ES 4. -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com.tr http://www.gunduz.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Server instrumentation for 8.1
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Pflug Sent: 11 May 2005 17:44 To: pgsql-hackers@postgresql.org Subject: [HACKERS] Server instrumentation for 8.1 There's still a lengthy discussion going on whether it's a good idea to add a forth way to read pgsql's schema (pg_* tables, pg_* views, information_schema, did I miss one?), but I'd like to see helper functions for issues *not* covered in the core package. I was going to write pretty much the same message - thanks for saving me the time! - dbsize has been in contrib for a long time, though it appears to me as quite a basic functionality to find out about storage needs. Agreed. - The superuser only generic file functions in the admin package have been posted for 8.0, but where (more or less ) silently dropped. These functions allow pgadmin to display the server logs, as well as editing pg_hba.conf and postgresql.conf without console access to whatever-pgsql-is-running-on. I'd like to see this at least as contrib module (the functions are probably safer than pl_sh). Both these modules are bundled with the pgsql win32 installer, and are installed by default. Both are supported by (at least) pgAdmin. I would like to see these as permanent additions to the server. They are useful functions that allow functionality to be included in interfaces like pgAdmin that any user coming from MS SQL or other DBMSs would probably expect to find. For anyone wanting to take a look, the module can be found in our shiny new Subversion repo at http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/xtra/admin/ - There was a pg_kill_backend function in pre-8.0, but it was dropped because it's too dangerous. Incidentially, I've been in the situation more than once where I needed to kill a backend process that was running wild; alternatively, I'd have to shutdown the whole server. I had to do this on the linux console with kill -9 (fortunately I did have access), or using the win32 task manager (same). This appears even more error prone to me than to point to the malicious process and kill it (through pgadmin/pg_kill_backend) This is also essential functionality, though only if it can be made safe imo. - We don't have a profiling facility to tap an individual backend for a limited period to find out what the client is doing there, so we need to use log_statement for this (I'd like to work on profiling, but I didn't find the time so far). Consequently, we have to deal with long logfiles, containing much stuff we don't need. In the past, I found it to be very helpful if a fresh logfile could be used (on a private installation, stop/start server), that's why my logfile process implementation did include a logfile rotation trigger functionality. Tom didn't need it, so he dropped it. I'd opt for re-adding it again. Yes, I ran into exactly this problem this morning as it happens when tracking down an obscure bug in some code that couldn't easily be debugged. Now I know you're all thinking 'oh yeah, obviously the pgAdmin team are putting on a united front', but honestly, I knew nothing about Andreas' email until I saw it, and he knew nothing of my intention to write one! :-) Regards, Dave. ---(end of broadcast)--- TIP 3: 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] [PATCHES] plperl and pltcl installcheck targets
Tom Lane wrote: The point is that I'd rather test createlang than duplicate it. (In the back of my mind also is that running createlang is a waste of time for the contrib tests, and so it'd be nice if pg_regress didn't load any PL unless told to.) Aha. ok. should be fairly trivial. I'm thinking of something like --load-languages=lang1,lang2,lang3 (in case we ever want more than one). cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets
Andrew Dunstan [EMAIL PROTECTED] writes: Aha. ok. should be fairly trivial. I'm thinking of something like --load-languages=lang1,lang2,lang3 (in case we ever want more than one). Might be a little easier as multiple switches: --load-language=lang1 --load-language=lang2 regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Cost of XLogInsert CRC calculations
On Wed, 2005-05-11 at 13:40 +0100, Mark Cave-Ayland wrote: So unless we can guarantee a minimum of 1k data per Xlog record then Adler-32 won't be suitable. Most records are either 8KB or much less than 1KB. Is the benefit gained from the 8KB records worth the loss on the more frequent smaller records? perhaps the CRC-32 routines could be written in in-line assembler If you can do this, step right up. :-) Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
There are some nontrivial issues to be thought about here, like under what conditions CREATE SCHEMA foo ought to create a top-level schema versus creating a schema under some other schema that we are pretending is the active catalog. But it seems on first glance like something could be worked out. Just go the extra info and call the top level catalogs in the commands as well: CREATE DATABASE mydb; \c mydb CREATE CATALOG foo; CREATE SCHEMA foo.bar CREATE TABLE foo.bar.baz (bif serial); -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle Style packages on postgres
On Wed, 2005-05-11 at 15:41 -0400, Rod Taylor wrote: There are some nontrivial issues to be thought about here, like under what conditions CREATE SCHEMA foo ought to create a top-level schema versus creating a schema under some other schema that we are pretending is the active catalog. But it seems on first glance like something could be worked out. Just go the extra info and call the top level catalogs in the commands Extra inch, not info. as well: CREATE DATABASE mydb; \c mydb CREATE CATALOG foo; CREATE SCHEMA foo.bar CREATE TABLE foo.bar.baz (bif serial); -- ---(end of broadcast)--- TIP 3: 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] Oracle Style packages on postgres
Rod Taylor [EMAIL PROTECTED] writes: There are some nontrivial issues to be thought about here, like under what conditions CREATE SCHEMA foo ought to create a top-level schema versus creating a schema under some other schema that we are pretending is the active catalog. But it seems on first glance like something could be worked out. Just go the extra info and call the top level catalogs in the commands as well: Nope, doesn't meet the spec requirements. One thing we can certainly say is that there would have to be a notion of an active catalog (which could be determined by outside-the-spec means, perhaps a GUC variable) because CREATE SCHEMA foo would have to create foo as a child of the active catalog. I'm also fairly unclear on what this implies for search_path searches. Currently, as soon as you have more than one dotted name, search_path is ignored ... but should it be used? Maybe a.b ought to be sought as foo.a.b for successive values of foo from the search path. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Oracle Style packages on postgres
Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: There are some nontrivial issues to be thought about here, like under what conditions CREATE SCHEMA foo ought to create a top-level schema versus creating a schema under some other schema that we are pretending is the active catalog. But it seems on first glance like something could be worked out. Just go the extra info and call the top level catalogs in the commands as well: Nope, doesn't meet the spec requirements. One thing we can certainly say is that there would have to be a notion of an active catalog (which could be determined by outside-the-spec means, perhaps a GUC variable) because CREATE SCHEMA foo would have to create foo as a child of the active catalog. I'm also fairly unclear on what this implies for search_path searches. Currently, as soon as you have more than one dotted name, search_path is ignored ... but should it be used? Maybe a.b ought to be sought as foo.a.b for successive values of foo from the search path. How is a catalog different from a schema? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Server instrumentation for 8.1
On Wed, May 11, 2005 at 04:44:21PM +, Andreas Pflug wrote: Yes yes I know, all of these can be done by a local administrator with console access and an editor and cmd line tools, but there are indeed people that do *not* have console access, or like to use decent tools Is there a reason they couldn't be bundled into a separate package, and either put in contrib/ or (my preference) put on gforge or whatever? The less-is-more approach in the default source seems to me to be a good thing. I'm not convinced that packaged systems should ship that way -- maybe these should be included in desktop systems -- but enabled-by-default for many of these things seems to me to be too dangerous. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
Bruce Momjian pgman@candle.pha.pa.us writes: How is a catalog different from a schema? In the spec there's a hard-wired difference: catalogs contain schemas, schemas don't contain other schemas. The idea at hand here is to make our namespaces serve both purposes. (I knew there was a good reason not to use the word schema for namespaces ;-)) The spec behavior would be met by using exactly two levels of namespace, but there wouldn't be anything stopping people from using more, except that their queries wouldn't look like spec-compatible queries. There are a number of issues that would have to be solved to make this actually work, but on first glance it seems like a possibly attractive idea. Besides, I can't wait to hear the moans from the newsysviews crew when the implications of this sink in ;-) ;-) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Server instrumentation for 8.1
On Wed, May 11, 2005 at 04:44:21PM +, Andreas Pflug wrote: There's still a lengthy discussion going on whether it's a good idea to add a forth way to read pgsql's schema (pg_* tables, pg_* views, information_schema, did I miss one?), but I'd like to see helper functions for issues *not* covered in the core package. - dbsize has been in contrib for a long time, though it appears to me as quite a basic functionality to find out about storage needs. FWIW, I believe the new system views cover all the dbsize cases. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
On Wed, May 11, 2005 at 04:49:52PM -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: How is a catalog different from a schema? In the spec there's a hard-wired difference: catalogs contain schemas, schemas don't contain other schemas. The idea at hand here is to make our namespaces serve both purposes. (I knew there was a good reason not to use the word schema for namespaces ;-)) The spec behavior would be met by using exactly two levels of namespace, but there wouldn't be anything stopping people from using more, except that their queries wouldn't look like spec-compatible queries. So is the *only* difference in which contains the other? It sounds like they just use a different name to enforce that there's only 2 levels. Besides, I can't wait to hear the moans from the newsysviews crew when the implications of this sink in ;-) ;-) Oh no, not recursive function calls! :P Actually, for the performance we're trying to obtain on the more important views (ie tables, indexes), it might become an issue. It would probably force us to C functions which we've thus-far avoided. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 3: 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
[HACKERS] Understanding Rule System
Hello all I have been studying the rule system in Postgres. I understand that the original query tree is the input at the rewrite, and then this query tree is modified by the rewrite in case that there is a rule. SQL query Parser Rewrite Planner Executor Query New Query treetree I would like to know how the rewrite detects that a rule in a table exists, and if it possible you indicate me where in the source code it's implemented. Any comments are welcome, thanks in advance. Juan P. Espino ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Server instrumentation for 8.1
Andreas, I think you bring up some good points, but I also think that each package you propose needs to be dealt with individually. - dbsize has been in contrib for a long time, though it appears to me as quite a basic functionality to find out about storage needs. Although not needed so much if the new system views are approved; we have a view that calculates database size. Not that multiple options isn't cool. - The superuser only generic file functions in the admin package have been posted for 8.0, but where (more or less ) silently dropped. These functions allow pgadmin to display the server logs, as well as editing pg_hba.conf and postgresql.conf without console access to whatever-pgsql-is-running-on. I'd like to see this at least as contrib module (the functions are probably safer than pl_sh). Heck, I didn't even see these. I was going to write some in pgperl for my own use. These seem potentially very dangerous though, so we wouldn't want them installed by default. - There was a pg_kill_backend function in pre-8.0, but it was dropped because it's too dangerous. Incidentially, I've been in the situation more than once where I needed to kill a backend process that was running wild; alternatively, I'd have to shutdown the whole server. I had to do this on the linux console with kill -9 (fortunately I did have access), or using the win32 task manager (same). This appears even more error prone to me than to point to the malicious process and kill it (through pgadmin/pg_kill_backend) Certainly. But this was dropped because Tom couldn't get the bugs out (as I recall) and make it safe to use, even for the superuser. You could take a stab at fixing it. Also, if this were an enable-at-build-time option, it would also help defuse the security argument, since it wouldn't necessarily be installed. - We don't have a profiling facility to tap an individual backend for a limited period to find out what the client is doing there, so we need to use log_statement for this (I'd like to work on profiling, but I didn't find the time so far). Consequently, we have to deal with long logfiles, containing much stuff we don't need. In the past, I found it to be very helpful if a fresh logfile could be used (on a private installation, stop/start server), that's why my logfile process implementation did include a logfile rotation trigger functionality. Tom didn't need it, so he dropped it. I'd opt for re-adding it again. +1 Yes yes I know, all of these can be done by a local administrator with console access and an editor and cmd line tools, but there are indeed people that do *not* have console access, or like to use decent tools To support Andrew's assertion, automated server room administration tools (like Hyperic and Embarcadero) could really use the above tools. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] New Contrib Build?
Folks, Hey, I can see a way for /contrib to become a lot better option for stuff-we're-not-sure-whether-to-include. What if we could build contrib modules through a build-time switch for PostgreSQL? Like, ./configure --with-perl --with-dblink --with-newsysviews This would seem a *lot* more user friendly to me, and easier on the package builders. What's the technical obstacle to it? Also, I think that /contrib modules should have documentation included in the main docs, probably as an appendix. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, May 11, 2005 at 04:49:52PM -0400, Tom Lane wrote: Besides, I can't wait to hear the moans from the newsysviews crew when the implications of this sink in ;-) ;-) Oh no, not recursive function calls! :P No, actually, I was wondering where the potentially N levels of schema names would appear in the output ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Adding callback support.
On Tue, May 10, 2005 at 09:36:59AM +0200, Nicolai Petri wrote: I'm currently building some stored procedures in C that uses some internal hash tables - It could be really nice to be able to deallocate those correctly when e.g. a memctx is destroyed. Would it be possible to add this as a postgresql feature and how should it be done. Why don't you just create the hash table inside a context that has the right longevity? For example, TopTransactionContext. So at transaction end the hash table is automatically freed. I also have some other tasks where I would like to do specific actions when a transaction completes or aborts. This could be used for maintaining a pgmemcache correctly and many other cases. Could this be combined with the above feature so a generic callback framework was added ? Hmm, is there anything the following functions don't do for you: extern void RegisterXactCallback(XactCallback callback, void *arg); extern void UnregisterXactCallback(XactCallback callback, void *arg); extern void RegisterSubXactCallback(SubXactCallback callback, void *arg); extern void UnregisterSubXactCallback(SubXactCallback callback, void *arg); See src/backend/access/transam/xact.c -- Alvaro Herrera ([EMAIL PROTECTED]) Ni aun el genio muy grande llegaría muy lejos si tuviera que sacarlo todo de su propio interior (Goethe) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] New Contrib Build?
Josh Berkus wrote: Folks, Hey, I can see a way for /contrib to become a lot better option for stuff-we're-not-sure-whether-to-include. What if we could build contrib modules through a build-time switch for PostgreSQL? Like, ./configure --with-perl --with-dblink --with-newsysviews This would seem a *lot* more user friendly to me, and easier on the package builders. What's the technical obstacle to it? Also, I think that /contrib modules should have documentation included in the main docs, probably as an appendix. I could see --with-contrib but other than that... there are ALOT of packages in contrib. Sincerely, Joshua Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
On Wed, May 11, 2005 at 05:28:22PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, May 11, 2005 at 04:49:52PM -0400, Tom Lane wrote: Besides, I can't wait to hear the moans from the newsysviews crew when the implications of this sink in ;-) ;-) Oh no, not recursive function calls! :P No, actually, I was wondering where the potentially N levels of schema names would appear in the output ... My immediate thought is that they would be appended together in 'dot notation'; 'schema1.schema2.schema3', since that's the definative way to refer to the schema in such a scheme. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, May 11, 2005 at 05:28:22PM -0400, Tom Lane wrote: No, actually, I was wondering where the potentially N levels of schema names would appear in the output ... My immediate thought is that they would be appended together in 'dot notation'; 'schema1.schema2.schema3', since that's the definative way to refer to the schema in such a scheme. That's OK for human consumption but I'm not so sure it'll be of any value to programs. At the very least you'd have to quotify the names, so that a.b can be told from a.b. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle Style packages on postgres
Adding to the ambiguity is the dot notation used for
composite columns. Don't forget the other end ignoring
those required parens.
is foo.bar.zap
a database.schema.table
a schema.table.column
a table.column.column
--elein
On Wed, May 11, 2005 at 03:21:42PM -0400, Tom Lane wrote:
Dave Held [EMAIL PROTECTED] writes:
/*
* We check the catalog name and then ignore it.
*/
if (!isValidNamespace(name[0]))
{
if (strcmp(name[0], get_database_name(MyDatabaseId)) != 0)
ereport(ERROR,
Which more or less proves my point: the syntax is fundamentally
ambiguous. I suppose people would learn not to use schema names that
match the database they are in, but that doesn't make it a good idea to
have sensible behavior depend on non-overlap of those names.
[ thinks for awhile ... ]
OTOH, what if we pretended that two-level-nested schemas ARE catalogs
in the sense that the SQL spec expects? Then we could get rid of the
pro-forma special case here, which isn't ever likely to do anything more
useful than throw an error anyway. Thus, we'd go back to the pre-7.3
notion that the current Postgres DB's name isn't part of the SQL naming
scheme at all, and instead handle the spec's syntax requirements by
setting up some conventions that make a schema act like what the spec
says is a catalog.
There are some nontrivial issues to be thought about here, like under
what conditions CREATE SCHEMA foo ought to create a top-level schema
versus creating a schema under some other schema that we are pretending
is the active catalog. But it seems on first glance like something
could be worked out.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New Contrib Build?
On Wed, May 11, 2005 at 02:34:21PM -0700, Joshua D. Drake wrote: I could see --with-contrib but other than that... there are ALOT of packages in contrib. I'm not configure expert, but I think it wouldn't be hard to do something like --with-contrib='module1 module2 module3'. I believe there's existing syntax in gmake to control what directories you descend into, so this would just need to envoke that syntax. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] New Contrib Build?
Josh Berkus wrote: Folks, Hey, I can see a way for /contrib to become a lot better option for stuff-we're-not-sure-whether-to-include. First, I *really* wish we'd call it something else. Contrib conveys unsupported to people. Maybe we should call it modules or something like that. What if we could build contrib modules through a build-time switch for PostgreSQL? Like, ./configure --with-perl --with-dblink --with-newsysviews This would seem a *lot* more user friendly to me, and easier on the package builders. What's the technical obstacle to it? I honestly don't see that it buys a lot. (and the technical obstacle is that there's a maintenance cost, if nothing else). Also, I think that /contrib modules should have documentation included in the main docs, probably as an appendix. No, not as an appendix, please. Again, that gives the wrong impression. Let's add another main section on optional modules. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New Contrib Build?
Andrew, First, I *really* wish we'd call it something else. Contrib conveys unsupported to people. Maybe we should call it modules or something like that. Agreed. I honestly don't see that it buys a lot. (and the technical obstacle is that there's a maintenance cost, if nothing else). It would save, me, personally a bunch o' time and troubleshooting, since I regularly install 5-6 different contrib modules. No, not as an appendix, please. Again, that gives the wrong impression. Let's add another main section on optional modules. OK by me. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Server instrumentation for 8.1
On 2005-05-11, Jim C. Nasby [EMAIL PROTECTED] wrote: On Wed, May 11, 2005 at 04:44:21PM +, Andreas Pflug wrote: There's still a lengthy discussion going on whether it's a good idea to add a forth way to read pgsql's schema (pg_* tables, pg_* views, information_schema, did I miss one?), but I'd like to see helper functions for issues *not* covered in the core package. - dbsize has been in contrib for a long time, though it appears to me as quite a basic functionality to find out about storage needs. FWIW, I believe the new system views cover all the dbsize cases. dbsize looks at the actual size of files on disk; newsysviews does not, it shows estimated sizes as taken from relpages. newsysviews doesn't allow you to see size info for databases you're not connected to. However, newsysviews knows about toast tables, and gives a breakdown of table size by main storage, toast and index. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] New Contrib Build?
Jim C. Nasby wrote: On Wed, May 11, 2005 at 02:34:21PM -0700, Joshua D. Drake wrote: I could see --with-contrib but other than that... there are ALOT of packages in contrib. I'm not configure expert, but I think it wouldn't be hard to do something like --with-contrib='module1 module2 module3'. I believe there's existing syntax in gmake to control what directories you descend into, so this would just need to envoke that syntax. That would work... with a --with-contrib='all' etc... -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
On Wed, May 11, 2005 at 05:43:32PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, May 11, 2005 at 05:28:22PM -0400, Tom Lane wrote: No, actually, I was wondering where the potentially N levels of schema names would appear in the output ... My immediate thought is that they would be appended together in 'dot notation'; 'schema1.schema2.schema3', since that's the definative way to refer to the schema in such a scheme. That's OK for human consumption but I'm not so sure it'll be of any value to programs. At the very least you'd have to quotify the names, so that a.b can be told from a.b. Very true. Ultimately the best way to handle this is probably to keep the views basically as they are (meaning you would only show the schema_name and oid of the schema that an object is in), and have a function that will provide you a full schema path given a schema_oid. On another note... is dbname.schema.table.column part of the standard? It seems like if we're ever going to allow native cross-database communication we'd want to preserve that. One thought is the use of a leading . to indicate you're starting at the database level. No leading . means you're in whatever database you're connected to. Another possibility is that 'remote' databases (which might be on the same server) get mapped into a fixed portion of the namespace hierarchy, such as pg_rdb. I don't like cryptic names, but I certainly don't want to type 'pg_remote_databas' everytime I refer to something remote. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle Style packages on postgres
On Wed, May 11, 2005 at 02:41:43PM -0700, elein wrote: Adding to the ambiguity is the dot notation used for composite columns. Don't forget the other end ignoring those required parens. is foo.bar.zap a database.schema.table a schema.table.column a table.column.column Wouldn't that be handled by the FROM clause having to identify only tables and views? Is there anyplace where dot notation actually extends from database name down to columns? If that's the case, it seems reasonable to me to require the use of table aliases in cases where there's ambiguity. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Server instrumentation for 8.1
Josh Berkus josh@agliodbs.com writes: - The superuser only generic file functions in the admin package have been posted for 8.0, but where (more or less ) silently dropped. These functions allow pgadmin to display the server logs, as well as editing pg_hba.conf and postgresql.conf without console access to whatever-pgsql-is-running-on. I'd like to see this at least as contrib module (the functions are probably safer than pl_sh). Heck, I didn't even see these. I was going to write some in pgperl for my own use. These seem potentially very dangerous though, so we wouldn't want them installed by default. My recollection is that they weren't silently dropped, they were explicitly rejected after much discussion because of security worries (and possibly other concerns, I don't recall for sure anymore). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New Contrib Build?
On Wed, May 11, 2005 at 02:55:46PM -0700, Josh Berkus wrote: First, I *really* wish we'd call it something else. Contrib conveys unsupported to people. Maybe we should call it modules or something like that. Agreed. Ditto I honestly don't see that it buys a lot. (and the technical obstacle is that there's a maintenance cost, if nothing else). It would save, me, personally a bunch o' time and troubleshooting, since I regularly install 5-6 different contrib modules. Likewise, I think it would make our various OS ports cleaner. All port systems are setup to easily allow different optional config options, but it's generally more difficult to tie additional make steps in. This means that the only options I've seen for installing things out of contrib from a port system are to either install all contrib modules or to hunt down where the source was extracted to. No, not as an appendix, please. Again, that gives the wrong impression. Let's add another main section on optional modules. OK by me. Ditto. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] New Contrib Build?
Andrew Dunstan [EMAIL PROTECTED] writes: Josh Berkus wrote: What if we could build contrib modules through a build-time switch for PostgreSQL? Like, I honestly don't see that it buys a lot. (and the technical obstacle is that there's a maintenance cost, if nothing else). I'm with Andrew on this: what's the point? We don't currently have any contrib modules that are noticeably harder to build than any others, and I don't see any coming over the horizon. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] New Contrib Build?
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Josh Berkus wrote: What if we could build contrib modules through a build-time switch for PostgreSQL? Like, I honestly don't see that it buys a lot. (and the technical obstacle is that there's a maintenance cost, if nothing else). I'm with Andrew on this: what's the point? We don't currently have any contrib modules that are noticeably harder to build than any others, and I don't see any coming over the horizon. I'd buy it more with Jim Nasby's suggested --with-contrib option. Or let's say --with-modules. One nice side effect would be that this info would get into pg_config's configure settings. That would also help to make the whole build more replayable. To be of any use I guess this should ensure that the specified modules are built, installed and tested from the main makefile. cheers andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Understanding Rule System
On 5/11/05, Juan Pablo Espino [EMAIL PROTECTED] wrote: Hello all I have been studying the rule system in Postgres. I understand that the original query tree is the input at the rewrite, and then this query tree is modified by the rewrite in case that there is a rule. SQL query Parser Rewrite Planner Executor Query New Query treetree I would like to know how the rewrite detects that a rule in a table exists, and if it possible you indicate me where in the source code it's implemented. Any comments are welcome, thanks in advance. src/backend/rewrite/*.c The entry point i think is rewriteHandler.c IIRC, there is a catalog that is used to know if there are any rules for a table i think it is pg_rewrite, but i can be wrong. -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] New Contrib Build?
Andrew Dunstan wrote: First, I *really* wish we'd call it something else. Contrib conveys unsupported to people. And that's exactly what it is supposed to mean. We say, these modules do not necessarily meet our standards with regard to code quality, portability, user interfaces, internationalization, documentation, etc. There is certainly a lot of good software in contrib and one could in individual cases consider moving them out of there, but contrib is what it is. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: 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] New Contrib Build?
Josh Berkus wrote: What if we could build contrib modules through a build-time switch for PostgreSQL? Like, ./configure --with-perl --with-dblink --with-newsysviews This would seem a *lot* more user friendly to me, and easier on the package builders. What's the technical obstacle to it? I don't see how this makes it any more user friendly or easier on package builders. Is your aim to make building contrib more accessible or building only specific contrib modules more accessible? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] New Contrib Build?
Peter, I don't see how this makes it any more user friendly or easier on package builders. Is your aim to make building contrib more accessible or building only specific contrib modules more accessible? Building specific contrib modules. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Cost of XLogInsert CRC calculations
perhaps the CRC-32 routines could be written in in-line assembler If you can do this, step right up. :-) Best Regards, Simon Riggs Surely there's an open source code floating around somewhere? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] patches for items from TODO list
Hello all, We would like to contribute to the Postgresql community by implementing the following items from the TODO list (http://developer.postgresql.org/todo.php): . Allow COPY to understand \x as a hex byte . Allow COPY to optionally include column headings in the first line . Add XML output to COPY The changes are straightforward and include implementation of the features as well as modification of the regression tests and documentation. Before sending a diff file with the changes, we would like to know if these features have been already implemented. Best regards, Jason Lucas and Sergey Ten SourceLabs Dependable Open Source Systems ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] patches for items from TODO list
Sergey Ten wrote: Hello all, We would like to contribute to the Postgresql community by implementing the following items from the TODO list (http://developer.postgresql.org/todo.php): . Allow COPY to understand \x as a hex byte . Allow COPY to optionally include column headings in the first line . Add XML output to COPY The changes are straightforward and include implementation of the features as well as modification of the regression tests and documentation. Before sending a diff file with the changes, we would like to know if these features have been already implemented. Please check the web site version. Someone has already implemented Allow COPY to optionally include column headings in the first line. As far as XML, there has been discussion on where that should be done? In the backend, libpq, or psql. It will need discussion on hackers. I assume you have read the developer's FAQ too. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] patches for items from TODO list
Please check the web site version. Someone has already implemented Allow COPY to optionally include column headings in the first line. As far as XML, there has been discussion on where that should be done? In the backend, libpq, or psql. It will need discussion on hackers. I assume you have read the developer's FAQ too. The other issue is 'what XML format'? Find me a standard data dump XML DTD and I'll change phpPgAdmin to use it as well. Otherwise, phpPgAdmin's is quite simple. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
The SQL_ASCII-breaks-JDBC issue just came up yet again on the JDBC list, and I'm wondering if we can do something better on the server side to help solve it. The problem is that people have SQL_ASCII databases with non-7-bit data in them under some encoding known only to a (non-JDBC) application. Changing client_encoding has no effect on a SQL_ASCII database, it's always passthrough. So when a JDBC client is later written, and the JDBC driver sets client_encoding=UNICODE, we get data corruption and/or complaints from the driver that the server is sending it invalid unicode (because it's really LATIN1 or whatever the original inserter happened to use). At this point the user has real problems as there is existing data in their database in one or more encodings, but the encoding info associated with that data has been lost. Converting such a database to a single database-wide encoding is painful at best. I suppose that we can't change the semantics of SQL_ASCII without backwards compatibility problems. I wonder if introducing a new encoding that only allows 7-bit ascii, and making that the default, is the way to go. This new encoding would be treated like any other normal encoding, i.e. setting client_encoding does transcoding (I expect that'd be a 1:1 mapping in most or all cases) and rejects unmappable characters as soon as they're encountered. Then the problem is visible as soon as problematic strings are given to the server, rather than when a client that depends on having proper encoding information (such as JDBC) happens to be used. If the DB is only using simple 7-bit ASCII, then there's no change in behaviour. If the DB does need to store additional characters, the user is forced to choose an appropriate encoding before any encoding info is lost. Any thoughts on this? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL_ASCII vs. 7-bit ASCII encodings
I suppose that we can't change the semantics of SQL_ASCII without backwards compatibility problems. I wonder if introducing a new encoding that only allows 7-bit ascii, and making that the default, is the way to go. A while back I requested a new encoding that is '7BITASCII'. It would be excellent for those of use who require that the data is ascii, latin1 and utf8. This new encoding would be treated like any other normal encoding, i.e. setting client_encoding does transcoding (I expect that'd be a 1:1 mapping in most or all cases) and rejects unmappable characters as soon as they're encountered. Personally, I'd like UTF8 to be the default encoding :) This is the 21st century :D Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New Contrib Build?
Josh Berkus josh@agliodbs.com writes: Peter, I don't see how this makes it any more user friendly or easier on package builders. Is your aim to make building contrib more accessible or building only specific contrib modules more accessible? Building specific contrib modules. On re-reading the thread, I'm more than a bit confused by this response. I thought you were suggesting that the top-level configure should have a simple option that says please build and install all the contrib modules while you are at it. Right now that requires a separate step cd contrib; make; make install which I agree could be handled by a top-level configure option. And it would also be reasonable for make check at the top level to include running the contrib regression tests if this option had been specified. (Memo to Andrew Dunstan: that should also happen for the PL-specific tests, as soon as they've been pg_regress-ified.) What I'm not seeing anywhere here is an argument that we need a configure option to build just selected ones of the contrib modules. Certainly the RPM packagers would have no use for that. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Views, views, views: Summary of Arguments
I'm not thinking exclusively in terms of whether they would be useful to me, personally. In fact, I'm certain that they would be useful to me, personally. What I question is whether they need to be a part of the internal development of PostgreSQL. To me, CPAN is an integral part of being able to do Perl development effectively. Whether or not a VPAN setup could come to seem as natural and easy for new users to use, I don't know. Regardless, these new views are going to need to be documented similarly so that new users are aware of them. And it still isn't clear (to me) how the debate over how to shape them as included by default will resolve, so punting to an externally maintained repository is just a suggestion as an alternative. I think it's important to consider the perspective of both developers and users, and the internal views clearly creates issues for the developers. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On May 11, 2005, at 11:35 AM, Josh Berkus wrote: Thomas, All, I guess I'm having difficulty understanding why the system catalogs themselves and provision of support for information_schema are not sufficient for what exists in core. Because you can't answer the question: What tables does user phil have update permissions on? or How many overloaded versions of function df_new_company() do I have?, and similar, without doing advanced queries on the system tables. Queries which are prone to mistakes: earlier on this thread a *pgsql hacker* posted a sample system catalog query which contained a mistake. Asking our general users to navigate the complexity of the system catalogs is just not good application practice. I don't really think a VPAN is any kind of solution for this purpose (though I'd like it for other things). The purpose of these views is to make PostgreSQL more user-friendly, and telling people: Oh just go to http://name/of/obscure/site, lookup these 10 views, log in as superuser and load them and you're golden is not much of an improvement in user-friendliness. To reiterate my point previously: these system views are NOT aimed at the people on *this* list; they are for the people on the -NOVICE and - GENERAL lists and IRC and the people who don't yet use PostgreSQL. Please stop thinking exclusively in terms of whether they would be useful to you, personally. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] New Contrib Build?
Peter Eisentraut [EMAIL PROTECTED] writes: Andrew Dunstan wrote: First, I *really* wish we'd call it something else. Contrib conveys unsupported to people. And that's exactly what it is supposed to mean. We say, these modules do not necessarily meet our standards with regard to code quality, portability, user interfaces, internationalization, documentation, etc. There is certainly a lot of good software in contrib and one could in individual cases consider moving them out of there, but contrib is what it is. Which is as it should be, I think. Contrib is essentially the not quite ready for prime time area. If it were 100% up to speed then it'd be in the core backend already ... while if we required it to be 100% in advance, then it'd not have gotten out there in the first place. The real issue seems to be that we have a disconnect between what is presently in contrib and what is on gborg or pgfoundry. There are certainly many contrib modules that are only there on seniority: if they were submitted today then they'd have gotten put on pgfoundry. But I'm not sure that there's much value in an enforced cleanup. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Views, views, views: Summary of Arguments
Thomas F. O'Connell [EMAIL PROTECTED] writes: I think it's important to consider the perspective of both developers and users, and the internal views clearly creates issues for the developers. FWIW, I don't see the issue as internal vs external at all. What's bothering me is whether these views can be considered sufficiently more stable and better designed than the physical system catalogs to justify recommending that application designers should rely on the views instead of the catalogs. That point doesn't seem to me to have been proven. The recent arguments in favor seem to boil down to novices will find these easier to use, which is very possibly true, but novices don't have the same needs as programs. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Views, views, views: Summary of Arguments
FWIW, I don't see the issue as internal vs external at all. What's bothering me is whether these views can be considered sufficiently more stable and better designed than the physical system catalogs to justify recommending that application designers should rely on the views instead of the catalogs. That point doesn't seem to me to have been proven. The recent arguments in favor seem to boil down to novices will find these easier to use, which is very possibly true, but novices don't have the same needs as programs. As lead phpPgAdmin developer, I'm officially in favour of them. The main reason being all the extra fruit they have that shows database size, etc. That means we can display this meta information in phpPgAdmin and not worry about having to re-implement it all. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
