Re: [PERFORM] Fixed width rows faster?
If you have an actual business-logic requirement to restrict a field to no more than N characters, then by all means use varchar(N); that's what it's for. But I agree with what I think Josh meant: there is very seldom any non-broken reason to have a hard upper limit on string lengths. If you think you need varchar(N) you should stop and ask why exactly. If you cannot give a specific, coherent reason why the particular value of N that you're using is the One True Length for the field, then you really need to think twice. One nice reason to have like VARCHAR(4096) or whatever is that if there is a bug in your website and you forget to length check some user input, it stops them from screwing you by uploading megs and megs of data into a 'firstname' field, say. 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
Re: [PERFORM] Fixed width rows faster?
> Frankly, the only reason to use anything other than TEXT is > compatibility with other databases and applications. The main reason why I am still using VARCHAR rather than TEXT in many places is to ensure that the column can be indexed. Postgres, it seems, refuses to insert a string that is longer than some value into an indexed column, and I'll rather have such errors flagged while inserting a row rather than while rebuilding an index after having inserted lots of rows. ---(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: [PERFORM] Fixed width rows faster?
On Sat, Mar 06, 2004 at 02:17:35PM +0100, Eric Jain wrote: > places is to ensure that the column can be indexed. Postgres, it seems, > refuses to insert a string that is longer than some value into an > indexed column, and I'll rather have such errors flagged while inserting Care to provide some details of this? It sure sounds like a bug to me, if it's true. I've never run into anything like this, though. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Feature request: smarter use of conditional indexes
On 3/3/04 6:53 PM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> writes:
>> Given an index like this:
>> CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
>> and a query like this:
>> SELECT * FROM t1 WHERE c1 = 123;
>> I'd like the planner to be smart enough to use an index scan using i1.
>
> Send a patch ;-)
>
> The routine you want to teach about this is pred_test_simple_clause() in
> src/backend/optimizer/path/indxpath.c. ISTM that it's legitimate to
> conclude that "foo IS NOT NULL" is implied by "foo op anything" or
> "anything op foo" if the operator is marked strict.
Gack, C is not my forte...
So...I'm noodling around in pred_test_simple_clause() and my test query of:
SELECT * FROM t1 WHERE c1 = 123;
lands me in pred_test_simple_clause() with a "predicate" with a NodeTag of
NullTest, and a "clause" with a NodeTag of OpExpr. The clause "rightop"
IsA() Const. So far, it seems to make sense. It's comparing the clause "c1
= 123" with the predicate on the "i1" index ("IS NOT NULL") to see if one
implies the other.
But now I'm stuck, because IsA(predicate, NullTest) is *also* true if the
index i1 is dropped and index i2 is created like this:
CREATE UNIQUE INDEX i2 ON t1 (c1) WHERE c1 IS NOT NULL;
IOW, both "IS NOT NULL" and "IS NULL" lead to IsA(predicate, NullTest) being
true. I found this, which looked promising:
typedef enum BoolTestType
{
IS_TRUE, IS_NOT_TRUE, IS_FALSE, IS_NOT_FALSE, IS_UNKNOWN, IS_NOT_UNKNOWN
} BoolTestType;
typedef struct BooleanTest
{
Exprxpr;
Expr *arg;/* input expression */
BoolTestType booltesttype;/* test type */
} BooleanTest;
But then I realized that "predicate" is "Expr *" inside the
pred_test_simple_clause() function, and Expr seems only to have a single
field, which is tested by IsA()
typedef struct Expr
{
NodeTagtype;
} Expr;
So apparently all I can do is find out if it's a null test, but not if it is
specifically "IS NOT NULL"
Now I'm stuck, and thinking that I'd have to modify more than
pred_test_simple_clause() to make this work. Any additional pointers? :)
-John
---(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: [PERFORM] Fixed width rows faster?
"Eric Jain" <[EMAIL PROTECTED]> writes: > The main reason why I am still using VARCHAR rather than TEXT in many > places is to ensure that the column can be indexed. Postgres, it seems, > refuses to insert a string that is longer than some value into an > indexed column, and I'll rather have such errors flagged while inserting > a row rather than while rebuilding an index after having inserted lots > of rows. This is bogus reasoning. The limit on index entry length will not change when you rebuild the index. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Feature request: smarter use of conditional indexes
John Siracusa <[EMAIL PROTECTED]> writes:
> So apparently all I can do is find out if it's a null test, but not if it is
> specifically "IS NOT NULL"
No, because once you have determined that the node really IsA NullTest,
you can cast the pointer to (NullTest *) and look at the
NullTest-specific fields. Think of this as poor man's object-oriented
programming: Node is the supertype of Expr which is the supertype of
NullTest (and a lot of other kinds of nodes, too).
It'd look something like
if (IsA(predicate, NullTest) &&
((NullTest *) predicate)->nulltesttype == IS_NOT_NULL)
{
/* check to see if arg field matches either side of opclause,
* and if so check whether operator is strict ...
*/
}
You can find plenty of examples of this programming pattern throughout
the backend. In fact pred_test_simple_clause is doing exactly this
to check that what it's given is an OpExpr and not some other node type.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Fixed width rows faster?
> Actually, I don't. Good reason to have a check constraint on it though > (hint, check constraints can be changed while column types cannot be, at > this moment). Is there a way to copy a table INCLUDING the check constraints? If not, then that information is lost, unlike varchar(n). -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Fixed width rows faster?
On Sat, 2004-03-06 at 20:16, Mike Nolan wrote: > > Actually, I don't. Good reason to have a check constraint on it though > > (hint, check constraints can be changed while column types cannot be, at > > this moment). > > Is there a way to copy a table INCLUDING the check constraints? If not, > then that information is lost, unlike varchar(n). No, not constraints. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Fixed width rows faster?
Mike Nolan wrote: Is there a way to copy a table INCLUDING the check constraints? If not, then that information is lost, unlike varchar(n). "pg_dump -t" should work fine, unless I'm misunderstanding you. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Fixed width rows faster?
> Mike Nolan wrote: > > Is there a way to copy a table INCLUDING the check constraints? If not, > > then that information is lost, unlike varchar(n). > > "pg_dump -t" should work fine, unless I'm misunderstanding you. I was specifically referring to doing it in SQL. The COPY command goes from table to file or file to table, the CREATE TABLE ... SELECT loses the check constraints. Is there no SQL command that allows me to clone a table, including check constraints? Something like COPY TABLE xxx TO TABLE yyy WITH CHECK CONSTRAINTS. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Feature request: smarter use of conditional indexes
On 3/3/04 6:53 PM, Tom Lane wrote:
John Siracusa <[EMAIL PROTECTED]> writes:
Given an index like this:
CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
and a query like this:
SELECT * FROM t1 WHERE c1 = 123;
I'd like the planner to be smart enough to use an index scan using
i1.
Send a patch ;-)
How does this look? It seems to do what I want without horribly
breaking anything as far as I can tell. I ran "make check" and got the
same result as I did before my changes (5 failures in OS X 10.3.2).
But then, I also got the same result when I wasn't even checking to
make sure that both clauses were looking at the same variable :) I'm
not sure how to add a test for this particular change either.
% cvs diff src/backend/optimizer/path/indxpath.c
Index: src/backend/optimizer/path/indxpath.c
===
RCS file:
/projects/cvsroot/pgsql-server/src/backend/optimizer/path/indxpath.c,v
retrieving revision 1.156
diff -r1.156 indxpath.c
1032a1033,1055
> {
> /* One last chance: "var = const" or "const = var" implies "var is
not null" */
> if (IsA(predicate, NullTest) &&
> ((NullTest *) predicate)->nulltesttype == IS_NOT_NULL &&
> is_opclause(clause) && op_strict(((OpExpr *) clause)->opno) &&
> length(((OpExpr *) clause)->args) == 2)
> {
> leftop = get_leftop((Expr *) clause);
> rightop = get_rightop((Expr *) clause);
>
> /* One of the two arguments must be a constant */
> if (IsA(rightop, Const))
>clause_var = leftop;
> else if (IsA(leftop, Const))
>clause_var = rightop;
> else
>return false;
>
> /* Finally, make sure "var" is the same var in both clauses */
> if (equal(((NullTest *) predicate)->arg, clause_var))
>return true;
> }
>
1033a1057
> }
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Feature request: smarter use of conditional indexes
--On Saturday, March 06, 2004 21:29:27 -0500 John Siracusa <[EMAIL PROTECTED]> wrote: On 3/3/04 6:53 PM, Tom Lane wrote: John Siracusa <[EMAIL PROTECTED]> writes: Given an index like this: CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL; and a query like this: SELECT * FROM t1 WHERE c1 = 123; I'd like the planner to be smart enough to use an index scan using i1. Send a patch ;-) Just a suggestion, please use diff -c format, as it makes it easier for the folks who apply the patches to do so. [snip] -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [PERFORM] Fixed width rows faster?
On Sat, 2004-03-06 at 21:26, Mike Nolan wrote: > > Mike Nolan wrote: > > > Is there a way to copy a table INCLUDING the check constraints? If not, > > > then that information is lost, unlike varchar(n). > > > > "pg_dump -t" should work fine, unless I'm misunderstanding you. > > I was specifically referring to doing it in SQL. > > The COPY command goes from table to file or file to table, the > CREATE TABLE ... SELECT loses the check constraints. > > Is there no SQL command that allows me to clone a table, including check > constraints? There is not in the spec or in PostgreSQL. Although, this may be a relevant extension to the LIKE structure inheritance in 200N spec (partly implemented 7.4). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Feature request: smarter use of conditional indexes
Larry Rosenman <[EMAIL PROTECTED]> writes: > Just a suggestion, please use diff -c format, as it makes it easier for > the folks who apply the patches to do so. That's not just a suggestion ... patches that aren't in diff -c (or at least diff -u) format will be rejected out of hand. Without the context lines provided by these formats, applying a patch is an exercise in risk-taking, because you can't be certain that you are applying the same patch the submitter intended. Personally I consider -c format the only one of the three that is readable for reviewing purposes, so even if I weren't intending immediate application, I'd ask for -c before looking at the patch. There are some folks who consider -u format readable, but I'm not one of them ... BTW, patches really ought to go to pgsql-patches ... they're a bit off-topic here. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
