Re: [HACKERS] Do we prefer software that works or software that
On Sat, 24 Apr 2004, Stephan Szabo wrote: On Sat, 24 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: Things like don't worry about the catalog entries don't fly when your standard functions are defined and looked up there. Answer above. Okay, under that world view (as opposed to on the fly), I think the only issues come in from shared catalogs, most importantly user names. This is In fact the above is incomplete. You also need to be able to do the right thing when creating a database with a different setting than its template database. I'm not really sure how to define right thing however if things have been added to the template db. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Rob wrote: But I think there is room to go further, I don't see any reason why that default install can't include example DBs, One reason is that a useful example database would likely have a download footprint of 10 MB or more. Having this in the default download would not be appreciated by many people. Of course having some example database available at all would be a good idea, but then as a separate download. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
I'm opening a new thread, as the previous one was too nested, and contained too much emotions. I'll start by my understanding of a summary of the thread so far. The solution we are seeking would have to satisfy the following conditions: 1. Setting should be on a per-database level. A per-server option is not good enough, and a per-session option is too difficult to implement, with no apparent justifiable return. 2. Old applications already working with PG's lowercase folding should have an option to continue working unmodified for the foreseeable future. Solutions offered so far, and their status: 1. Tri-state. Folder upper, if failes, fold lower, if succeeds, warn. 2. Dual state. Fold lower or upper. Break if client is broken. 3. Create a database conversion tool to change existing case. Solution 1: As currently the case folding is performed disjointed from the actual use of the identifier, this solution requires quite a big amount of work. On the other hand, and on second thought, it's main benefit - gradual transition of applications from one to the other, is not really necessary once you declare the current behaviour as there to stay. Existing applications can simply choose to continue using whatever method they currently use. No need for migration. As such, I think we can simply state that tri-state migration path solution can be discarded for the time being. Solution 2: Obviously, this is the way to go. We will have a dabase attribute that states whether things are lower or upper case there. Solution 3: (unrelated to the above) There seems to be some ambiguity about how to handle the translation. Such a tool seems to require guessing which identifiers are accessed quoted, unquoted, or both. The last option, of course, will never work. We may need such a tool, for some projects may wish to transform from one way to the other. It seems to me, however, that such a tool can wait a little. Open issues: 1. What do we do with identifiers in template1 upon database creation? 2. How do we handle queries to tables belonging the catalog that are shared between databases? Observation: on a lowercase folding DB, any identifier that is not composed only of lowercase characters MUST can be automatically assumed to be accessed only through quoted mode. I therefor suggest the following path to a solution: 1. CreateDB will be able to create databases from either type. 2. template1 will be defined to be one or the other. For the sake of this discussion, let's assume it's lowercase (current situation) 3. CreateDB, upon being asked to create a new DB that has uppercase folding, will copy over template1, as it currently does. 4. While copying, it will check each identifier. If the identifier is not lowercase only, it is safe to copy it verbatim. 5. If the identifier is lowercase only, convert it to uppercase only. I am assuming here that the authors of the client code chose an uppercase-folding database, so they should know what they are doing when accessing stuff from the standard offering. 6. I'm not sure what are the shared tables from the catalog. I don't think it so unreasonable to ask anyone doing catalog work to assume that catalog entries are case-sensitive. As such, maybe it's best to just leave the data as is. 7. Column headers, however, will have to have a solution. A point still open in current design. I'm hoping this summary helps in furthering the discussion. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
5. If the identifier is lowercase only, convert it to uppercase only. I am assuming here that the authors of the client code chose an uppercase-folding database, so they should know what they are doing when accessing stuff from the standard offering. You've just broken one of my databases. In one project I quote nearly everything and do so in lower case only. This was done to ensure portability between PostgreSQL, Oracle, etc. -- but with my preference of lower case names. If someone copied this database with the wrong case folding option, it would break a (reasonably) spec compliant application that is regularly installed on environments where we have little to no control over the database settings. I think copied attributes need to be left alone. Train the PostgreSQL utilities to always quote the identifiers instead. If you want case to be folded, run an external utility to does a bunch of ALTER ... RENAMEs. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
Rod Taylor wrote: 5. If the identifier is lowercase only, convert it to uppercase only. I am assuming here that the authors of the client code chose an uppercase-folding database, so they should know what they are doing when accessing stuff from the standard offering. You've just broken one of my databases. In one project I quote nearly everything and do so in lower case only. This was done to ensure portability between PostgreSQL, Oracle, etc. -- but with my preference of lower case names. I'm not sure you understood me. First, if we don't convert lower-upper, how can anyone expect the following query to work: select lower(id) from table; Even if you quote everything, you'd still probably have: select lower(id) from table; Noone can expect you to do: select lower(id) from table; The problem is that lower is defined in template1. If we don't uppercase it when we create the database, the above won't work. Then again, I'm fairly sure that the identifiers you placed as lowercase in your database are not defined by template1. In short, I don't think this suggestion broke your database. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
Shachar Shemesh wrote: I'm opening a new thread, as the previous one was too nested, and contained too much emotions. I'll start by my understanding of a summary of the thread so far. The solution we are seeking would have to satisfy the following conditions: 1. Setting should be on a per-database level. A per-server option is not good enough, and a per-session option is too difficult to implement, with no apparent justifiable return. I am not convinced on this point. Why is per-server not good enough? The obvious place to make these changes seems to me to be during or immediatly after the bootstrap phase of initdb. It would avoid a host of later troubles. 2. Old applications already working with PG's lowercase folding should have an option to continue working unmodified for the foreseeable future. Solutions offered so far, and their status: 1. Tri-state. Folder upper, if failes, fold lower, if succeeds, warn. 2. Dual state. Fold lower or upper. Break if client is broken. 3. Create a database conversion tool to change existing case. I don't think we should rush at this. All of these solutions are based on the existing structures. I have started thinking about a solution that would involve keeping two versions of catalog names: a canonical name and a name as supplied at creation. There would be heaps of wrinkles, but it might get us where we want to be. But I have not had time to sort it out in my head yet, let alone make any experiments. Let's keep getting more ideas. 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] Bringing PostgreSQL torwards the standard regarding
On Sun, Apr 25, 2004 at 12:23:55PM -0400, Andrew Dunstan wrote: I don't think we should rush at this. All of these solutions are based on the existing structures. I have started thinking about a solution that would involve keeping two versions of catalog names: a canonical name and a name as supplied at creation. Why do you want two names? Just keep the original casing, and a boolean saying if it's quoted or not. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No deja de ser humillante para una persona de ingenio saber que no hay tonto que no le pueda enseñar algo. (Jean B. Say) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
Alvaro Herrera wrote: On Sun, Apr 25, 2004 at 12:23:55PM -0400, Andrew Dunstan wrote: I don't think we should rush at this. All of these solutions are based on the existing structures. I have started thinking about a solution that would involve keeping two versions of catalog names: a canonical name and a name as supplied at creation. Why do you want two names? Just keep the original casing, and a boolean saying if it's quoted or not. Because I was not just considering the upper/lower issue - refer to the thread that relates to unquoted names with case preserved. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
I wrote: Alvaro Herrera wrote: On Sun, Apr 25, 2004 at 12:23:55PM -0400, Andrew Dunstan wrote: I don't think we should rush at this. All of these solutions are based on the existing structures. I have started thinking about a solution that would involve keeping two versions of catalog names: a canonical name and a name as supplied at creation. Why do you want two names? Just keep the original casing, and a boolean saying if it's quoted or not. Because I was not just considering the upper/lower issue - refer to the thread that relates to unquoted names with case preserved. Sorry - brain malfunction - yes, original casing plus boolean would work. In effect you could derive the canonical form from those two. 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: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Peter Eisentraut wrote:
Rob wrote:
But I think there is room to go further, I don't see any reason why
that default install can't include example DBs,
One reason is that a useful example database would likely have a
download footprint of 10 MB or more. Having this in the default
download would not be appreciated by many people. Of course having
some example database available at all would be a good idea, but then
as a separate download.
Here is a little psql script I wrote to populate a table with random
data.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
\set ECHO all
\timing
DROP TABLE perftest;
CREATE TABLE perftest (col text);
-- prime table with one row
INSERT INTO perftest VALUES ('0.364461265208414');
-- continously double the table size
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
-- insert a constant in the middle of the table, for use later
INSERT INTO perftest VALUES ('0.608254158221304');
INSERT INTO perftest SELECT random()::text FROM perftest;
-- 32770 rows
-- vacuum, create index
VACUUM ANALYZE perftest;
CREATE INDEX i_perftest ON perftest (col);
-- reduce chance of checkpoint during tests
CHECKPOINT;
-- turn on logging
SET log_duration = TRUE;
SET client_min_messages = 'log';
-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
-- first time the entire statement
SET log_statement_stats = TRUE;
-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
-- now log each query stage
SET log_statement_stats = FALSE;
SET log_parser_stats = TRUE;
SET log_planner_stats = TRUE;
SET log_executor_stats = TRUE;
-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
A sort of debate started by Bruce about what MySQL does better vs what PostgreSQL could do, then there was a thread about removing /contrib from the main download, and a few other posts. I don't think these are unrelated. They all fall under the umbarella of How does a new user come to use PostgreSQL? I have come to believe it is a number of issues, it isn't just the site, it isn't just the setup, it isn't whether or not contributed modules are easy to find, I think it is all of these. (1) I think the PostgreSQL web presence has been lacking for some time. I am not a web developer myself, but I do know that the PostgreSQL site does not seem to look and feel like other sites. The new site is better than before, but I think it still needs some more standardization with the rest of the web. There are a lot of usability issues that can be fixed easily. Links to the left, fluff items to the right, main message in the middle, symplified menus, etc. Again, while I'm not a web designer, I have a small moch-up of something I think would have more visual clues to new users at http://www.mohawksoft.com/PostgreSQL.html (2) Removing the contrib directory in the main download. I think this is a good idea, but it means that we need to make it easier to make extensions. I write a lot of extensions and I have a standard Makefile for building them. (Granted I have to change it periodically for different versions) You have to specify the PostgreSQL build directory to build an extension. It isn't clear to me if there is a way to build an extension to PostgreSQL without having built PostgreSQL first. It would be nice if there were a standardized set of extension headers and a well documented process to make an extension. Then we could focus on a standard API. :-) (3) Configuration. What can I say, power users edit postgresql.conf, start scripts, pg_hba.conf, etc. I think it can safely be said, that we need to show non-power users the light. For this, there needs to be an application, in some portable scripting language, that can configure PostgreSQL. Maybe it is in the form of a web server like Samba's SWAT utility, I don't know (A SWAT type utility could run as the PostgreSQL user for the correct rights), but users need this. I don't think the typical debate of GUI tool aren't really easier or That's just bloat or Do we even want these users using PostgreSQL are relevent. If we want to increase usership, this is a requirement. (4) Blessed projects, lets play favorites. Lets find good and meaningful extensions on gborg and ./contrib and work with the authors and make them part of the PostgreSQL environment. Projects like, replication, .NET service provider, ODBC, pgAdmin, etc. are important and users need to find them close to PostgreSQL's main location. (5) Programming languages. We need to make a programming language standard in PostgreSQL. plpgsql is good, but isn't someone working on a Java language. That would be pretty slick. (6) In keeping with item #4, lets make some Binary distributions and make those available on the mirrors. Think about how a Windows user tries software: Click on a site, install, and run. I could update my Windows installer and console system and create a zipped install. I know there are rpms and debs out there, but if you look at programs like mozilla and realplayer, they have a pretty good installer. Maybe we could use the Mozilla installer? What happend to the Great Bridge installer? When all is said and done, I think the PostgreSQL project lacks a Product Management group which steers the public perception and defines usability. This is something *all* other systems have, including MySQL. If we want to make PostgreSQL a wildly popular product, there will be some pain. There should be a Product Management group. The leader(s) of this group should be chosen carefully, as he (they) must be free to define what PostgreSQL is. They must have a good feel for product development and understanding of the underlying technology, but not be so techie that we don't address the issues intended. They must be able to rally the troops and direct development efforts. Lastly, he (they) must have the confidence of the core hackers, as it is likely that there will be disagreements with the direction of PostgreSQL, and it wouldn't work if Product Management couldn't actually manage what the product was because nobody listened. Is anyone really ready for this sort of commitment? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] btbulkdelete
On -performance we have been discussing a configuration where a bulk
delete run takes almost a day (and this is not due to crappy hardware or
apparent misconfiguration). Unless I misinterpreted the numbers,
btbulkdelete() processes 85 index pages per second, while lazy vacuum is
able to clean up 620 heap pages per second.
Is there a special reason for scanning the leaf pages in *logical*
order, i.e. by following the opaque-btpo_next links? Now that FSM
covers free btree index pages this access pattern might be highly
nonsequential.
I'd expect the following scheme to be faster:
for blknum = 1 to nblocks {
read block blknum;
if (block is a leaf) {
process it;
}
}
As there is no free lunch this has the downside that it pollutes the
cache with unneeded inner nodes and free pages.
OTOH there are far less inner pages than leaf pages (even a balanced
binary tree has more leaves than inner nodes), and if free pages become
a problem it's time to re-index.
Did I miss something else?
Servus
Manfred
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Number of pages in a random sample (was: query slows down with more accurate stats)
On Mon, 19 Apr 2004 12:00:10 -0400, Tom Lane [EMAIL PROTECTED] wrote:
A possible compromise is to limit the number of pages sampled to
something a bit larger than n, perhaps 2n or 3n. I don't have a feeling
for the shape of the different-pages probability function; would this
make a significant difference, or would it just waste cycles?
I would have replied earlier, if I had a good answer. What I have so
far contains at least one, probably two flaws. Knowing not much more
than the four basic arithmetic operations I was not able to improve my
model. So I post what I have:
As usual we assume a constant number c of tuples per page. If we have a
table of size B pages and want to collect a sample of n tuples, the
number of possible samples is (again in OOo syntax)
left( binom{cB}{n} right)
If we select an arbitrary page, the number of possible samples that do
NOT contain any tuple from this page is
left( binom {c (B-1)} {n} right)
Let's forget about our actual implementations of sampling methods and
pretend we have a perfect random sampling method. So the probability
Pnot(c, B, n) that a certain page is not represented in a random sample
is
left( binom {c (B-1)} {n} right) over left( binom{cB}{n} right)
which can be transformed into the more computing-friendly form
prod from{i=0} to{n-1} {{cB-c - i} over {cB - i}}
Clearly the probability that a certain page *is* represented in a sample
is
Pyes(c, B, n) = 1 - Pnot(c, B, n)
The next step assumes that these probabilities are independent for
different pages, which in reality they are not. We simply estimate the
number of pages represented in a random sample as
numPag(c, B, n) = B * Pyes(c, B, n)
Here are some results for n = 3000:
B \ c-10 | 100 | 200
---+---+---+---
100 | --- | 100 | 100
1000 | 972 | 953 | 951
2000 | 1606 | 1559 | 1556
3000 | 1954 | 1902 | 1899
6000 | 2408 | 2366 | 2363
9000 | 2588 | 2555 | 2553
2 | 2805 | 2788 | 2787
3 | 2869 | 2856 | 2856
10 | 2960 | 2956 | 2956
This doesn't look to depend heavily on the number of tuples per page,
which sort of justifies the assumption that c is constant.
In the next step I tried to estimate the number of pages that contain
exactly 1, 2, ... tuples of the sample. My naive procedure works as
follows (I'm not sure whether it is even valid as a rough approximation,
constructive criticism is very welcome):
For c=100, B=3000, n=3000 we expect 1902 pages to contain at least 1
tuple of the sample. There are 1098 more tuples than pages, these
tuples lie somewhere in those 1902 pages from the first step.
numPag(99, 1902, 1098) = 836 pages contain at least a second tuple.
So the number of pages containing exactly 1 tuple is 1902 - 836 = 1066.
Repeating these steps we get 611 pages with 2 tuples, 192 with 3, 30
with 4, and 3 pages with 5 tuples.
Here are some more numbers for c = 100 and n = 3000:
B | pages with 1, 2, ... tuples
---+
100 | 1 to 24 tuples: 0, then 1, 2, 4, 10, 18, 26, 24, 11, 4
1000 | 108, 201, 268, 229, 113, 29, 5
2000 | 616, 555, 292, 83, 12, 1
3000 | 1066, 611, 192, 30, 3
6000 | 1809, 484, 68, 5
9000 | 2146, 374, 32, 2
2 | 2584, 196, 8
3 | 2716, 138, 3
10 | 2912, 44
A small C program to experimentally confirm or refute these calculations
is attached. Its results are fairly compatible with above numbers,
IMHO.
Servus
Manfred
/*
** samsim.c - sampling simulator
*/
#include stdio.h
#include stdlib.h
#include sys/time.h
#include unistd.h
typedef int bool;
#define MAX_RANDOM_VALUE (0x7FFF)
static void initrandom()
{
struct timeval tv;
gettimeofday(tv, NULL);
srandom(tv.tv_sec ^ tv.tv_usec);
}/*initrandom*/
/* Select a random value R uniformly distributed in 0 R 1 */
static double
random_fract(void)
{
longz;
/* random() can produce endpoint values, try again if so */
do
{
z = random();
} while (z = 0 || z = MAX_RANDOM_VALUE);
return (double) z / (double) MAX_RANDOM_VALUE;
}
/*
** data structure for (modified) Algorithm S from Knuth 3.4.2
*/
typedef struct
{
longN; /* number of tuples, known in advance
*/
int n; /* sample size */
longt; /* current tuple number */
int m; /* tuples selected so far */
} SamplerData;
typedef SamplerData *Sampler;
static void Sampler_Init(Sampler bs, long N, int samplesize);
static bool Sampler_HasMore(Sampler bs);
static long
[HACKERS] No threading option for FreeBSD 4.X in 7.5
FreeBSD 4.X will not support --enable-thread-safety in 7.5. Let me explain why. In 7.4, if we didn't have a *_r function, and the non-*_r function was not thread-safe, we called the non-*_r function with thread locks and copied the value into the passed storage pointer. I thought that was OK, but several people pointed out that this was not safe if another part of the program called the non-*_r function at the same time. I only understood the ramifications of that after 7.4 was released so I didn't change the 7.4 code. In 7.5, such locking/copying is gone, and if the function isn't thread-safe, and there is no *_r function, we throw a configure error as for threading. FreeBSD 4.9 fails the getpwuid() test, and there is no getpwuid_r(). The documentation for getpwuid() is clear that it isn't thread-safe, so I think the test is correct. Basically, there is nothing we can do to fix this situation on FreeBSD 4.9 and probably other 4.X releases. The good news is that the new configure tests for threading worked perfectly and the system spit out a clear message explaining the failure. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [HACKERS] linked list rewrite
(To resurrect an old thread, I've finally got some time to devote to this; I'll post a patch once I've got something ready for public consumption.) On 23-Mar-04, at 4:59 PM, Tom Lane wrote: Let's see ... fleshing out this idea a bit, here's a rundown of all the symbols in pg_list.h and suggested new names: [...] Most of your suggestions are agreeable; a few minor quibbles follow. lfirstcell new function to get first cell, or NULL if none [...] llastnode llastcell What do you think of list_head() and list_tail() instead? set_union list_union set_ptrUnionlist_union_ptr list_union_int not currently used set_uniono list_union_oid I don't see the need for anything more than set_difference() and set_difference_ptr() -- if we're passed a T_IntList or a T_OidList, we can examine the list tag and do the Right Thing automagically. ISTM we only need the xxx_int() and xxx_oid() variants when something in the function's signature depends on the type of the list's elements. set_difference list_difference set_ptrDifference list_difference_ptr list_difference_int not currently used set_differenceo list_difference_oid Ibid. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
On Sun, 25 Apr 2004, Andrew Dunstan wrote:
Why do you want two names? Just keep the original casing, and a boolean
saying if it's quoted or not.
Sorry - brain malfunction - yes, original casing plus boolean would
work. In effect you could derive the canonical form from those two.
Say that you have this in the table with the identifier
name quoted
--
Foo False
Now you want to add the name FOO
FOO True
should you be allowed or is it a clash with the above?
What if you also add foo
foo True
One of these two should be forbidden. And what about a quoted FOO:
FOO False
FOO True
This case says it is not enough with an expressional unique index on
(upper(name), quoted). It would be easier to enforce uniqueness if one
store both the converted name and the original name:
name orig_name
-
FOO NULL -- quoted one
FOO FOO -- unquoted one
and the first case
FOO Foo -- unquoted
FOO NULL -- clashes with the first, good
foo NULL -- no clash, works fine
With this one can always use upper case translation as per sql spec and
psql can optionally show all unquoted identifiers as upper, lower or mixed
case.
Then we also have the INFORMATION_SCHEMA that should show the names in
UPPER CASE when not quoted, this since applications that are written for
the standard might depend on that (probably no application do today but it
would be a valid case of use of the information schema).
--
/Dennis Björklund
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
