Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
On 2005-03-23, Tom Lane <[EMAIL PROTECTED]> wrote: > No, it wouldn't, because by the time you do the first FK trigger you'd > have one row/one page in the referenced table, so it'd still look like a > seqscan situation to the planner. The only way we could make that work > is to effectively disable seqscans entirely, by *always* pretending the > table size is large enough to trigger an indexscan, even when the > planner can plainly see that it's not. This is not an acceptable answer > IMHO. I'm not yet convinced the planner is right to _ever_ choose a seqscan for FK triggers. The idea that a seqscan is faster on small tables is traditional, and it has some justification in the case where nothing is in the cache (since index scan will touch the disk twice in that case), but I'm finding that for tables of the order of 50 rows (easily fitting in one page) that index scans are as fast as or faster than seqscans for doing simple one-row lookups provided the tables are in cache. -- 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: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
test=> select interval '1 hour 1 minute'; interval -- 01:01:00 (1 row) Hmm, I don't think I really like having a seconds field in the output, given that the column is by definition only storing data to a precision of a minute. -roy ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami <[EMAIL PROTECTED]> writes: > test=> select interval '1 hour 1 minute'; >interval > -- >01:01:00 > (1 row) > Hmm, I don't think I really like having a seconds field in the output, > given that the column is by definition only storing data to a > precision of a minute. Leaving out the seconds would make the display ambiguous. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Tom Lane wrote: > Roy Badami <[EMAIL PROTECTED]> writes: > > test=> select interval '1 hour 1 minute'; > > interval > > -- > > 01:01:00 > > (1 row) > > > Hmm, I don't think I really like having a seconds field in the output, > > given that the column is by definition only storing data to a > > precision of a minute. > > Leaving out the seconds would make the display ambiguous. Agreed. What is really weird is that the time is always displayed for a zero value: test=> select interval '0 years' year; interval -- 00:00:00 (1 row) but a non-zero shows the proper units: test=> select interval '1 years' year; interval -- 1 year (1 row) Is that OK? -- 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 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Bruce Momjian writes: > Agreed. What is really weird is that the time is always displayed for a > zero value: > test=> select interval '0 years' year; >interval > -- >00:00:00 > (1 row) > but a non-zero shows the proper units: > test=> select interval '1 years' year; >interval > -- >1 year > (1 row) > Is that OK? Well, it's a bit arbitrary, but I suppose it was done because otherwise a zero interval would show nothing at all ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
I wrote: > ... Maybe we could > put in a hack that detects whether a table has yet been vacuumed, and > sets 10/1000 as the minimum stats --- not fixed values, but minimum > values that can be overridden when the table is actually larger --- > until it has been vacuumed. For lack of any better suggestions, I've done this in HEAD and 8.0 branches. It proved simplest to just limit the page estimate to be at least 10 pages when relpages == 0. The tuple estimate will be derived from that using pre-existing code that estimates the average tuple size. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1532: typecast problem between arrays of an int8 derived datatype and varchar[]
"Ezequiel Tolnay" <[EMAIL PROTECTED]> writes: > I've created the cardnumber_t datatype, which is an int8, to provide > implicit typecasting with varchar padding the result with zeroes. > Conversions work as expected between int4, int8, cardnumber_t and varchar. > They also work fine between int4[], int8[] and cardnumber_t[], but when an > attempt is made to convert a cardnumber_t[] to a varchar[], the connection > is dropped. What's going on here is that array_map thinks it can use fn_extra of the passed FmgrInfo for its own purposes. That means that if the function to be called tries to use fn_extra for *its* own purposes, we have a conflict that is going to lead to core dumps in most cases. In other words, array_map pretty much doesn't work for calling anything except built-in functions. I think the best solution to this is to require array_map's caller to provide the state storage array_map wants, instead of doing it locally. Both of the existing callers can easily incorporate array_map's state data into their own state structs. Joe, you have any better ideas? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] pg_dump table ordering bug [8.0.1]
Andreas Lange <[EMAIL PROTECTED]> writes: > Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables > got dumped in the wrong order (before their dependecies) and had to get > their contents added manually after the restore. I've atleast isolated > the part where things go wrong. I don't think this is a pg_dump bug: the problem is you are abusing check constraints to emulate foreign key constraints. pg_dump has no way to know what those functions are doing and therefore no way to realize that the check constraints impose a data load ordering dependency. Furthermore, the check constraints are fundamentally wrong anyway because they don't create a two-way relationship --- that is, altering the referenced tables won't raise an error if the check is now violated for something in the referencing table. It would be best to find a way to express these relationships with ordinary foreign keys. Maybe you could add a column to form_a_int that is a foreign key reference to both of form_instance.fid and form_q.fid, for example? regards, tom lane ---(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: [BUGS] BUG #1532: typecast problem between arrays of an int8
Tom Lane wrote: "Ezequiel Tolnay" <[EMAIL PROTECTED]> writes: I've created the cardnumber_t datatype, which is an int8, to provide implicit typecasting with varchar padding the result with zeroes. Conversions work as expected between int4, int8, cardnumber_t and varchar. They also work fine between int4[], int8[] and cardnumber_t[], but when an attempt is made to convert a cardnumber_t[] to a varchar[], the connection is dropped. What's going on here is that array_map thinks it can use fn_extra of the passed FmgrInfo for its own purposes. That means that if the function to be called tries to use fn_extra for *its* own purposes, we have a conflict that is going to lead to core dumps in most cases. In other words, array_map pretty much doesn't work for calling anything except built-in functions. I think the best solution to this is to require array_map's caller to provide the state storage array_map wants, instead of doing it locally. Both of the existing callers can easily incorporate array_map's state data into their own state structs. Joe, you have any better ideas? That certainly looks like the least invasive fix for 8.0.x and 7.4.x. I have thought before that we were overloading fn_extra a bit too much. Is there any merit in having more than one "extra" member in FmgrInfo going forward? Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1513: pg_dump -t doesn't include sequences for pre-v8-created tables
"Chris Hutchinson" <[EMAIL PROTECTED]> writes: > pg_dump -t includes SERIAL column sequence details for the dumped table when > the table has been created in v8 postgresql. > Tables migrated from an earlier pg version aren't being dumped with their > sequence details. (These tables were created in 7.1, and migrated through > 7.3, 7.4 and 8.0) Yup. You might try the contrib/adddepend tool to fix this. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1532: typecast problem between arrays of an int8 derived datatype and varchar[]
Joe Conway <[EMAIL PROTECTED]> writes: > I have thought before that we were overloading fn_extra a bit too much. > Is there any merit in having more than one "extra" member in FmgrInfo > going forward? Not sure what --- how would you decide which to use, and what stops there being a conflict on one of them anyway? The basic rule is that "fn_extra belongs to the called function", and array_map was definitely breaking that rule. So I don't think this bug is a symptom of a system-wide issue. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
