Re: [BUGS] BUG #3973: pg_dump using inherited tables do not always restore
"Alex Hunsaker" <[EMAIL PROTECTED]> writes: > create table junk (val integer not null, val2 integer); > create table junk_child () inherits (junk_1); > alter table junk_child alter column val drop not null; > insert into junk_child (val2) values (1); > pg_dump -t junk -t junk_child > pg_restore/psql will fail because junk_child.val now has a not null > constraint Actually the bug is that ALTER TABLE allows you to do that. It should not be possible to drop an inherited constraint, but right now there's not enough information in the system catalogs to detect the situation. Fixing this has been on the TODO list for awhile: o %Prevent child tables from altering or dropping constraints like CHECK that were inherited from the parent table regards, tom lane ---(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
[BUGS] BUG #3973: pg_dump using inherited tables do not always restore
The following bug has been logged online: Bug reference: 3973 Logged by: Alex Hunsaker Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.0 Operating system: Linux Description:pg_dump using inherited tables do not always restore Details: create table junk (val integer not null, val2 integer); create table junk_child () inherits (junk_1); alter table junk_child alter column val drop not null; insert into junk_child (val2) values (1); pg_dump -t junk -t junk_child pg_restore/psql will fail because junk_child.val now has a not null constraint ---(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: [BUGS] BUG #3969: pg_ctl cannot detect server startup
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > I found this bug comes from the definition of WHITESPACE > characters in pg_ctl.c. WHITESPACE is defined as folows: >#define WHITESPACE "\f\n\r\t\v" > In fact, WHITESPACE does not contain whilespace (0x20) :-( Ooops :-( > I attach a patch to fix it. Actually, this coding seems gratuitously ugly/inconsistent/fragile, so I'm inclined to rewrite it to eliminate WHITESPACE altogether. It seems bad style to switch between loops using isspace() and an entirely different type of string searching that (as demonstrated by the bug) isn't easy to keep in sync. Adding an additional loop of the same kind to scan over the parameter value would take a couple more lines, but I think it'll be a lot more readable. In fact there are more bugs here: it won't deal correctly with a quoted port value, and it'd be fooled by '-p' appearing in the argument value for another option type. I'm not sure how tense we should be about getting it to exactly match the backend's behavior for corner cases, but at the very least it probably shouldn't be fooled by -p appearing inside quotes. > BTW, I also found similar definitions in some places. > (Please grep with "\t\n\r".) > They are a bit different from each other. > For example, whitespaces is defined as " \t\n\r" in tzparser.c. > Is it ok in the inconsistency? Or, should we always use " \f\n\r\t\v" ? Not sure. One point is that vertical whitespace shouldn't necessarily be treated the same as horizontal whitespace. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] GetNewOidWithIndex can cause infinite loop on user tables(not catalog).
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Jacky Leng wrote: >> --Create table t(a int) with oids; >> --create unique index it on t(oid); >> --insert 4G-16384 rows into t; > ... Therefore: don't do that. Indeed. It might be a good idea if that loop had a CHECK_FOR_INTERRUPTS, though. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
Gregory Stark <[EMAIL PROTECTED]> writes: > "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: >> Return type of hash* functions is just 32 bits. I wonder if that's wide >> enough >> to avoid accidental collisions? Depends on the application of course... > Oh, I missed that you were suggesting a UNIQUE index. That seems unsafe to me > even for md5 or its ilk. But that would depend on the application too. md5 is designed to be a signature, remember? If there weren't a very high probability of its output being different for different inputs, it wouldn't be good for anything. The built-in hash functions definitely cannot be relied on to not have collisions, though. regards, tom lane ---(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: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
On Wed, Feb 20, 2008 at 12:21:03PM +0100, Francisco Olarte Sanz wrote: > On Wednesday 20 February 2008, Gregory Stark wrote: > > > Unless you need cryptographic security I would not suggest using MD5. MD5 > > is intentionally designed to take a substantial amount of CPU resources to > > calculate. > > I thought it was the exact opposite, quoting from RFC1321: And if you *do* need cryptographic security then don't use MD5, and consider using SHA-256 instead of SHA-1. See RFC 4270 for discussion. ftp://ftp.rfc-editor.org/in-notes/rfc4270.txt -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3972: ERROR: function 59015 returned NULL
Maxime Carbonneau wrote: > with a database encoded with UTF8, I cannot insert into a varchar(100) > column the letter œ (\u0x0153). It gives me: "ERROR: function 59015 > returned NULL" What's function 59015? Try a select proname from pg_proc where oid = 59015; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, 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: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: >> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: >> >>> As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a >>> pretty >>> good work-around. >> >> Unless you need cryptographic security I would not suggest using MD5. MD5 is >> intentionally designed to take a substantial amount of CPU resources to >> calculate. > > Return type of hash* functions is just 32 bits. I wonder if that's wide enough > to avoid accidental collisions? Depends on the application of course... Oh, I missed that you were suggesting a UNIQUE index. That seems unsafe to me even for md5 or its ilk. But that would depend on the application too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(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: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
Gregory Stark wrote: "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a pretty good work-around. Unless you need cryptographic security I would not suggest using MD5. MD5 is intentionally designed to take a substantial amount of CPU resources to calculate. Postgres's internal hash method is exposed for most data types as hashtext() hashfloat8(), hashint4(), etc. These functions were chosen for their lightweight design. Cryptographic security is important only if you're concerned with people being able to intentionally create collisions. In this scenario that's probably not a top threat. Conceivably someone could create a denial-of-service attack slowing down your server by causing your indexes to become unbalanced. But it would be fairly challenging to engineer. Return type of hash* functions is just 32 bits. I wonder if that's wide enough to avoid accidental collisions? Depends on the application of course... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
On Wednesday 20 February 2008, Gregory Stark wrote: > Unless you need cryptographic security I would not suggest using MD5. MD5 > is intentionally designed to take a substantial amount of CPU resources to > calculate. I thought it was the exact opposite, quoting from RFC1321: The MD5 algorithm is designed to be quite fast on 32-bit machines. In addition, the MD5 algorithm does not require any large substitution tables; the algorithm can be coded quite compactly. F.O.S. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] GetNewOidWithIndex can cause infinite loop on user tables(not catalog).
Jacky Leng wrote: e.g. --Create table t(a int) with oids; --create unique index it on t(oid); --insert 4G-16384 rows into t; --insert into t values(1); As all oids has been used, GetNewObjectId will never find a usable Oid, so. GetNewObjectId doesn't try to guarantee uniqueness. You will get duplicate oids, unless you have a unique index on the oid column. If you do have a unique index, you will get into an endless loop in GetNewOidWithIndex. Therefore: don't do that. At worst, you might be able to turn this into a denial-of-service attack, by something like 2^32 CREATE TEMPORARY TABLE calls, using up the OID space of pg_class. But if you have access to CREATE TEMPORARY TABLE, there's plenty of other ways to launch a DoS attack, so I wouldn't worry about this too much. Per documentation of CREATE TABLE: Using OIDs in new applications is not recommended: where possible, using a SERIAL or other sequence generator as the table's primary key is preferred. A sequence will give you more control over wrap-around as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] GetNewOidWithIndex can cause infinite loop on user tables(not catalog).
e.g. --Create table t(a int) with oids; --create unique index it on t(oid); --insert 4G-16384 rows into t; --insert into t values(1); As all oids has been used, GetNewObjectId will never find a usable Oid, so. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3972: ERROR: function 59015 returned NULL
The following bug has been logged online: Bug reference: 3972 Logged by: Maxime Carbonneau Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.6 Operating system: Linux Description:ERROR: function 59015 returned NULL Details: with a database encoded with UTF8, I cannot insert into a varchar(100) column the letter Å (\u0x0153). It gives me: "ERROR: function 59015 returned NULL" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a > pretty > good work-around. Unless you need cryptographic security I would not suggest using MD5. MD5 is intentionally designed to take a substantial amount of CPU resources to calculate. Postgres's internal hash method is exposed for most data types as hashtext() hashfloat8(), hashint4(), etc. These functions were chosen for their lightweight design. Cryptographic security is important only if you're concerned with people being able to intentionally create collisions. In this scenario that's probably not a top threat. Conceivably someone could create a denial-of-service attack slowing down your server by causing your indexes to become unbalanced. But it would be fairly challenging to engineer. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
Juho Saarikko wrote: I suggest implementing unique hash indexes and automatically creating one (and turning the b-tree index into a non-unique one) when a large value is inserted to fix this. Alternatively, fix b-trees so they can handle large values; however, a hash index should be far more efficient for this specific case, since the size of a hash is independent of pre-hash data size. The current implementation of hash indexes actually store the whole key, in addition to the hash, so the size of the hash index is not independent of the key size. There has been some discussion on revamping the hash index implementation, and changing that among other things, but don't hold your breath. As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a pretty good work-around. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
