Re: [HACKERS] Surrogate keys (Was: enums)
Martijn van Oosterhout wrote: Please provides natural keys for any of the following: - A Person - A phone call: (from,to,date,time,duration) is not enough - A physical address - A phone line: (phone numbers arn't unique over time) - An internet account: (usernames not unique over time either) Ahh, a challenge. Hmm, not sure about all of them, but here goes: A Person - well, you could use a bit map of their fingerprints, or maybe their retinal scan. Of course, that could change due to serious injury. Maybe some kind of representation of their DNA? A physical address - how about longitude/latitude/height from sea level? The point here is two-fold. First, what we call 'natural' is frequently itself a surrogate key (yes, even your name is really just a surrogate key. As with all surrogate keys, it is a sequence of symbols that you use to represent yourself). The second point is even when you find a truly 'natural' key (something not arbitrarily made up by anyone, and uniquely identifying the data in question), it may be completely and utterly inappropriate to use in a database. What is 'natural' anyway? If someone phones in an order, we usually assign an order number to that request. This order number is not the actual order, and the customer couldn't care a less what it is, but I've never heard a DBA argue we should get rid of it (well, to be fair, I've never discussed order numbers with a DBA at all). After all, would it make sense for the key for that order to be the customer's name, the date/time of the order, all the items ordered, and the address to ship the order? That isn't a key, but it's the only 'natural' thing that identifies that order that immediately comes to my mind. On the other hand, would anyone argue that an order_item table should have a surrogate key? Well, I wouldn't. The key for the order_item table should be something like the order number and the inventory item number together (IMHO). The point? Surrogate keys and natural keys are two tools in the database arsenal. Just as it is unwise to use a hammer to drive a screw just because you don't believe in screwdrivers, it is unwise to just off hand discard either method of specifying a key. Rather, use intelligence and education (one of which is discussions such as this) in deciding how best to represent your data to aide in performance, ease of use, and adaptability. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Different length lines in COPY CSV
Tom Lane wrote: What's been suggested in the past is some sort of standalone file-format-conversion utility, which could deal with this sort of stuff without having to also deal with all the backend-internal considerations that COPY must handle. So (at least in theory) it'd be simpler and more maintainable. That still seems like a good idea to me --- in fact, given my druthers I would rather have seen CSV support done in such an external program. Why not add hooks into COPY to call the user's massage functions? That way you don't have to read and write the data, then read it again to load it into the database. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] generalizing the planner knobs
Simon Riggs wrote ISTM we could do some of that with another GUC, lets call it prepare_once = on. The system default is to have a prepared statement bound to a plan on its first parameter bind. If we set this to off, then the statement will replan each time we bind. This would give us both flexibility and predictability. (As ever, someone suggest a better name?). We call it deferred optimization. Do you really stop at the first parameter? What if it couldn't possibly affect the plan (col like '%M%', or col is not involved in an index)? You can continue to plan up until the first parameter that can affect the plan. At that point, you save off the plan, and when you get actual values (on the execute command), continue with the planning. You can do the same thing with correlated subqueries Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] generalizing the planner knobs
Greg Stark [EMAIL PROTECTED] writes: You more or less missed my entire point. Only because I am still getting used to how powerful and flexible Postgres is; but I am working on expanding my horizons. In the extreme, no amount of added intelligence in the optimizer is going to help it come up with any sane selectivity estimate for something like WHERE radius_authenticate(user) = 'OK' yeah, I can see where something like this would be problematic. While I still think that in an ideal world, you want to leave all of this to the engine, it is true that in the real world sometimes we still have to do some of the thinking for the computer. It's just that I've seen code absolutely littered with optimizer hints, and that really bothers me. But you can't not build a useful tool just because some would abuse it. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] generalizing the planner knobs
Greg Stark [EMAIL PROTECTED] writes: On the other hand the type I would prefer to see are hints that feed directly into filling in information the planner lacks. This only requires that the user understand his own data and still lets the planner pick the best plan based on the provided information. Optimizer hints were added because some databases just don't have a very smart optimizer. But you are much better served tracking down cases in which the optimizer makes a bad choice, and teaching the optimizer how to make a better one. That way, all users get the benefit of the fix. Remember, the purpose of SQL is to isolate the end user from having to care about how the data is retrieved; that is the RDBMS' problem. (the other thing forgotten was that it was supposed to be a natural language. NVL. Bah.) Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] generalizing the planner knobs
Gregory Maxwell [EMAIL PROTECTED] wrote: The flipside there is that a good set of hinting options may increase the amount of detailed feedback we get from users on improvements needed in the optimizer. The current knobs are pretty blunt and don't do as much as I'd like when trying to track down exactly where the optimiser has gone wrong. Point conceded. Any information that can help diagnose an issue is good information. I like the idea of only allowing it on explain. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ice-broker scan thread
No, it's all right. In fact, I believe my boss spoke to Bruce about this idea in August. But I have permission to discuss the algorithm. I may even be able to get the code, but to be honest, it isn't that much; it would probably be just as easy for it to be re-written as it would be to fit it into Postgres. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -Original Message- From: Simon Riggs [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 29, 2005 5:23 PM To: Pollard, Mike Cc: Qingqing Zhou; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] ice-broker scan thread On Tue, 2005-11-29 at 09:45 -0500, Pollard, Mike wrote: I've implemented this on another database product You're scaring me. Is the information you describe in the public domain or is it intellectual property of any particular company? Are you sure? We just recovered from one patent scare. Good to have you around though, if we're covered. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ice-broker scan thread
First, we need a new term for a thread of execution, that could be a thread or could be a process, I don't care. When discussing anything that is to run in parallel, the first thing that pops out of someones mouth is Don't you mean (thread/process)? But that's an implementation detail and should not be considered during a planning phase, unless it is fundamental to the problem. Hence, the term TOE to mean I don't really care if it is in it's own address space, or the same address space.. However, I understand that this is not in common usage, so in the following discussion I use the term thread, as it is more correct than process. I am just not defining if that thread is the only thread running in its process or not. I've implemented this on another database product, using buf reading threads to pull the data all the way into the database cache. In testing on Unix production systems (4 CPU machines, large RAID devices, 100Gb+ databases), table scans performed 5 to 7 times faster; on MVS table scans are up to 10 times faster. But, I never had much luck on getting the performance to change on Windows. Partially, I think, it's because the machine I was using was IDE, not SCSI, so I was already greatly bottlenecked. Maybe SATA would be better? I haven't tested there, either. Anyway, what I did was the following. When doing a sequential scan, we were starting at the beginning of the table and scanning forward. If I threw up some threads to read ahead, then my user thread and my read ahead threads would thrash on trying to lock the buffer slots. So, I had the read ahead threads start at some distance into the table, and work toward the beginning. The user thread would do its own I/O until it passed the read ahead threads. I also broke the read ahead section into multiple contiguous sections, and had different threads read each section, so the user thread would only have a problem with the first section; by the time it was finished with that, the other sections would be read in. When the user thread got to about 80% of the nodes that got read ahead, it would schedule another section to be read. ++ | table+ ++ (user-) (-readahead) (-readahead) (-readaehead) so above, the user threads is starting low in the table and working high; the readahead threads are starting higher (but not at the end of the table), and working low. Like I said, this worked very well for me. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Qingqing Zhou Sent: Tuesday, November 29, 2005 12:56 AM To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] ice-broker scan thread David Boreham [EMAIL PROTECTED] wrote I don't think your NT overlapped I/O code is quite right. At least I think it will issue reads at a high rate without waiting for any of them to complete. Beyond some point that has to give the kernel gut-rot. [also with reply to Gavin] look up dictionary for gut-rot, got it ... Uh, this behavior is intended - I try to push enough requests shortly to kernel so that it understands that I am doing sequential scan, so it would pull the data from disk to file system cache more efficiently. Some file systems may have free-behind mechanism, but our main thread (who really process the query) should be fast enough before the data vanished. You could re-write your program to have a single thread but use aio. In that case it should show the same read ahead benefit that you see with the thread. I guess this is also Gavin's point - I understand that will be two different methodologies to handle read-ahead. If no other thread/process involved, then the main thread will be responsible to grab a free buffer page from bufferpool and ask the kernel to put the data there by sync IO (current PostgreSQL does) or async IOs. And that's what I want to avoid. I'd like to use a dedicated thread/process to break the ice only, i.e., pull data from disk to file system cache, so that the main thread will only issue *logical* read. Regards, Qingqing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ice-broker scan thread
No, I only go x number of pages ahead of the user scan (where x is currently user defined, but it should be significantly smaller than your number of data buffers). I have found that reading about 16Mb ahead gives optimal performance, and on modern machines isn't all that much memory. Once the user scan has processed most of that 16Mb, the next section of the tree is schedule to be read. I don't keep the read ahead threads a constant distance ahead, because I found it to be more efficient if they occasionally had a lot of pages to read at once, rather than constantly having a few pages to read. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 29, 2005 10:06 AM To: Pollard, Mike Cc: Qingqing Zhou; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] ice-broker scan thread On Tue, Nov 29, 2005 at 09:45:30AM -0500, Pollard, Mike wrote: Anyway, what I did was the following. When doing a sequential scan, we were starting at the beginning of the table and scanning forward. If I threw up some threads to read ahead, then my user thread and my read ahead threads would thrash on trying to lock the buffer slots. So, I snip so above, the user threads is starting low in the table and working high; the readahead threads are starting higher (but not at the end of the table), and working low. Ok, this may be a really dumb question, but doesn't this rely on the fact that the table is smaller than the amount of buffers? If the table is large most of your data will be tossed out again by later data before it's been used by the backend. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Doubt
It Seems To Me. Heres a decent list of common acronyms: http://www.fun-with-words.com/acronyms.html Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gustavo Tonini Sent: Friday, November 25, 2005 1:24 PM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Doubt What is ISTM? Sorry, Gustavo.
Re: [HACKERS] MS SQL Server compatibility functions
If you're referring to my procedure for newid(), then it was just because of pure laziness; it was an internal proof of concept project, and I was still concentrating on getting it working. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 5:57 AM To: Mario Weilguni Cc: pgsql-hackers@postgresql.org; Pollard, Mike; Bill Bartlett; Fredrik Olsson Subject: Re: [HACKERS] MS SQL Server compatibility functions Why do you use GRANT ALL and not GRANT SELECT, UPDATE? All means everybody can do bad things with those sequences. GRANT ALL on a sequence IS GRANT SELECT UPDATE. Chris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] MS SQL Server compatibility functions
If this gets added as a contrib, here's a version of uniqueidentifier
and newid() I wrote that maintains the same format as the SQL Server
version:
CREATE SCHEMA sqlserver
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA sqlserver TO public;
CREATE SEQUENCE sqlserver.uniqueidentifier0
INCREMENT 1
MINVALUE 0
MAXVALUE 9223372036854775807
START 0
CACHE 1;
GRANT ALL ON TABLE sqlserver.uniqueidentifier0 TO public;
CREATE SEQUENCE sqlserver.uniqueidentifier1
INCREMENT 1
MINVALUE 0
MAXVALUE 9223372036854775807
START 0
CACHE 1;
GRANT ALL ON TABLE sqlserver.uniqueidentifier1 TO public;
--
-- use newid(n) to increment the sequences n times. Useful
-- for testing and timing the newid() function.
--
CREATE OR REPLACE FUNCTION sqlserver.newid(l integer)
RETURNS CHAR(36) AS $$
DECLARE
n CHAR(36);
maxl INTEGER;
BEGIN
maxl := l;
WHILE (maxl 0) LOOP
n := sqlserver.newid();
maxl := maxl - 1;
END LOOP;
RETURN(n);
END;$$ LANGUAGE plpgsql;
--
-- use newid() to acquire the next uniqueidentifier value.
-- This uses two sequences. Since a sequence returns an
-- 8-byte number, we just convert those into two 16 character
-- hex strings. Normally we just need to increment the second
-- sequence, but when that fills up, we increment the first
-- one and then reset the second one to 0. To prevent concerns
-- over a race condition, we then get the nextval of the second
-- sequence.
--
-- Note that this algorithm assumes that int8 works properly.
-- If you are porting this to a platform without a working int8,
-- then you will need to use 4 4-byte sequences instead.
--
CREATE OR REPLACE FUNCTION sqlserver.newid()
RETURNS CHAR(36) AS $$
DECLARE
numbers0 CHAR(16);
numbers1 CHAR(16);
formatted_id CHAR(36);
sq0 INT8;
sq1 INT8;
BEGIN
-- get the current sequence values
SELECT INTO sq0 last_value FROM sqlserver.uniqueidentifier0;
SELECT INTO sq1 last_value FROM sqlserver.uniqueidentifier1;
-- if sq1 is wrapped, then increment sq0 and restart sq1 at 0
IF (sq1 = 9223372036854775807)
THEN
sq0 := NEXTVAL('sqlserver.uniqueidentifier0');
sq1 := SETVAL('sqlserver.uniqueidentifier1', 0);
-- get nextval; ensures no race condition
sq1 := NEXTVAL('sqlserver.uniqueidentifier1');
ELSE
sq1 := NEXTVAL('sqlserver.uniqueidentifier1');
END IF;
numbers0 := UPPER(LPAD(TO_HEX(sq0), 16, '0'));
numbers1 := UPPER(LPAD(TO_HEX(sq1), 16, '0'));
formatted_id := SUBSTRING(numbers0, 1, 8) || '-' ||
SUBSTRING(numbers0, 9, 4) || '-' || SUBSTRING(numbers0, 13, 4) ||
'-' || SUBSTRING(numbers1, 1, 4) || '-' ||
SUBSTRING(numbers1, 5, 12);
return(formatted_id);
END;$$ LANGUAGE plpgsql;
CREATE DOMAIN sqlserver.uniqueidentifier
AS char(36)
DEFAULT sqlserver.newid();
Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.
Better to remain silent and be thought a fool than to speak out and
remove all doubt.
Abraham Lincoln
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Bill Bartlett
Sent: Wednesday, November 23, 2005 10:01 AM
To: 'Fredrik Olsson'; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] MS SQL Server compatibility functions
I'd be _very_ interested. I'll also volunteer to help out on this if
you need assistance -- we have somewhat of a mixed environment here, so
I already have a few (simple) functions that allow some compatibility
between MS SQL Server and PostgreSQL (supporting nextval on SQL
Server, etc.), but it sounds like your work has gone far beyond my work.
- Bill
Hi.
In the course of porting a database from Microsoft SQL Server to
PostgreSQL I have rewritten a few of the date and string functions in
pl/pgSQL. Started with just datepart, datediff and soundex, but once
started I continued and rewrote all date/time and string functions
supported by MS SQL 2005. Leaving only compatibility with
unicode-handling and binary objects (that MS SQL Server for
some reason
overloads string functions to work with).
I guess I am not the only one moving from MS SQL Server, so is there
interest for others to use my work, as a contrib perhaps. And
how should
I continue from here in that case?
regards
--
//Fredrik Olsson
Treyst AB
+46-19-362182
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] question about count(b) where b is a custom type
Firstly, if you just want a count, what's wrong with count(1) or count(*). Because unless the column does not allow nulls, they will not return the same value. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] question about count(b) where b is a custom type
Richard Huxton wrote: Pollard, Mike wrote: Firstly, if you just want a count, what's wrong with count(1) or count(*). Because unless the column does not allow nulls, they will not return the same value. Ah, but in the example given the column was being matched against a value, so nulls were already excluded. -- Details, details. But there is a valid general question here, and changing the semantics of the query will not address it. When doing a count(col), why convert col into a string just so you can determine if it is null or not? This isn't a problem on a small amount of data, but it seems like a waste, especially if you are counting millions of records. Is there some way to convert this to have the caller convert nulls to zero and non-nulls to 1, and then just pass an int? So logically the backend does: Select count(case col when null then 0 else 1) from table And count just adds the number to the running tally. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] question about count(b) where b is a custom type
If count(col) convert col to a string (an assumption that Martijn has cast into doubt, or perhaps shredded), then rather than convert all non-nulls that are not a string into a string, I was proposing converting the values into an int with the values 0 or 1 (0 means that row was null for that column, 1 means that row was not null;, since count(col) means count the non-null rows in col). Anyway, to make a short story long. The idea is rather than convert the column into a string, convert it into a value indicating whether the column was null or not null (which is all count cares about). In any case, it's moot idea since it appears Postgres already does that. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc. Better to remain silent and be thought a fool than to speak out and remove all doubt. Abraham Lincoln -Original Message- From: Tino Wildenhain [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 16, 2005 8:43 AM To: Pollard, Mike Cc: Richard Huxton; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] question about count(b) where b is a custom type Pollard, Mike schrieb: Richard Huxton wrote: Pollard, Mike wrote: Firstly, if you just want a count, what's wrong with count(1) or count(*). Because unless the column does not allow nulls, they will not return the same value. Ah, but in the example given the column was being matched against a value, so nulls were already excluded. -- Details, details. But there is a valid general question here, and changing the semantics of the query will not address it. When doing a count(col), why convert col into a string just so you can determine if it is null or not? This isn't a problem on a small amount of data, but Why convert? A null is always null no matter in which datatype. it seems like a waste, especially if you are counting millions of records. Is there some way to convert this to have the caller convert nulls to zero and non-nulls to 1, and then just pass an int? So logically the backend does: Select count(case col when null then 0 else 1) from table Which would be totally silly :-) no matter if its 0 or 1 it counts as 1. Do you mean sum() maybe? Even then you dont need coalesce to convert null to 0 because sum() just ignores null. And count just adds the number to the running tally. Which number here? Mike Pollard SUPRA Server SQL Engineering and Support strange... Cincom Systems, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Running PostGre on DVD
I explain myself about running PostGre as admin. In fact I don't want specifically run PostGre as admin. The problem is, on the computers the application including PostGre will run, I'm not sure that the user won't have any admin or power user rights. Furthermore, I've noticed that on certain domains, any user created is automatically added to a default group having power user rights (that is actually happening to me). To be honest, the fact that Postgres forces you to run as a non-admin user has given me nothing but headaches. (yes, I know, the problem is defaulting everyone to admin rights is the problem. But that's where I am). I have been kicking around the idea of posting a change to allow you to run as admin, but in the meanwhile if you can build Postgres on your machine, the fix is very easy. Go into src/backend/main/main.c and find the line if (pgwin32_is_admin()) and change it to if (false pgwin32_is_admin()) Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Running PostGre on DVD
The example given in this thread certainly isn't going to change anybody's mind. Hi, I propose reducing everybody's security because my local admins insist on an utterly brain-dead security policy. What's wrong with that? ;) But seriously, the proposal is not to reduce everybody's security, just make it an option for people that want to. I am not arguing that it is a good idea/bad idea. In fact, the best thing to do may be to leave it in contrib, so if someone thinks it will solve a problem, it is at least a little painful to get to it. But at least by putting it into contrib, it may be useful to someone. Especially if the idea is to put a sample database onto a removable device. I suspect this is for some kind of demo (if not, it could be used for one); you go to a prospects site, pop the CD/DVD into their machine, and show off what your product can do for them. In that case, you may have no control over the permissions on the machine, and you certainly do not want to have to create and switch users for a demo; you've just lost the customers interest. Also, in my case, I'm running the debugger and profiler against Postgres on my Windows machine. I find it much easier to throw out the admin restriction, so I can just use my own account. I agree that my default account should not have had full admin rights, but that is the way the machine came. And yes, I should have immediately created a new user and set myself up on that one. But come on, my old laptop was so old, and I was so excited... sorry, TMI. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Supporting NULL elements in arrays
and so the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of
grounds:
Can you use a default to allow the user to specify the default value for
an element? May look a little strange, though, if the user specifies a
default array and a default element value, like:
CREATE TABLE ARR(ARR CHAR(30)[] DEFAULT '{hello, good bye}' NULL);
So the first default is the array default; specify NULL if you don't
want one but do want an array element default; the second, if present,
is the array element default. I'm not sure I like this or not, but it's
an idea.
Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Supporting NULL elements in arrays
and so the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of
grounds:
Can you use a default to allow the user to specify the default value for
an element? May look a little strange, though, if the user specifies a
default array and a default element value, like:
CREATE TABLE ARR(ARR CHAR(30)[] DEFAULT '{hello, good bye}' NULL);
So the first default is the array default; specify NULL if you don't
want one but do want an array element default; the second, if present,
is the array element default. I'm not sure I like this or not, but it's
an idea.
Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Reducing the overhead of NUMERIC data
I am not able to quickly find your numeric format, so I'll just throw
this in. MaxDB (I only mention this because the format and algorithms
are now under the GPL, so they can be reviewed by the public) uses a
nifty number format that allows the use memcpy to compare two numbers
when they are in the same precision and scale. Basically, the first
byte contains the sign and number of digits in the number (number of
digits is complemented if the number is negative), then the next N bytes
contain the actual decimal digits, where N is the number of decimal
digits / 2 (so two decimal digits per byte). Trailing 0's are removed
to save space. So,
0 is stored as {128}
1 is stored as {193, 16}
1000 is stored as {196, 16}
1001 is stored as {196, 16, 1} x{C4 10 01}
-1 is stored as {63, 144}
-1001 is stored as {60, 144}
Their storage allows for a max of 63 digits in a number, but it should
be no problem to increase the size to 2 bytes, thus allowing up to
16,385 digits.
The advantages are:
- ability to memcmp two numbers.
- compact storage (can be made more compact if you choose to
save hex digits instead of decimal, but I'm not sure you want to do
that).
The disadvantages are as follows:
- this format does not remember the database definition for the
number (that is, no precision or scale); numeric functions must be told
what they are. It would be nice if the number kept track of that as
well...
- comparing two numbers that are not the same precision and
scale means converting one or both (if both precision and scale are
different you may have to convert both)
- calculations (addition, subtraction, etc) require functions to
extract the digits and do the calculation a digit at a time.
- I do not know of any trig functions, so they would need to be
written
If any one is interested, I would be happy to discuss this further.
Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
