Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
On Fri, 2005-03-18 at 23:21 +, Brian O'Reilly wrote: > The following bug has been logged online: > > Bug reference: 1552 > Logged by: Brian O'Reilly > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.1 > Operating system: Linux 2.6.11 > Description:massive performance hit between 7.4 and 8.0.1 > Details: > > When doing a lot of inserts to an empty table with a foreign key to another > table, there is an incredible performance degredation issue on 8.0.1. I have > a program that is inserting rows in an iterative loop, and in this form it > inserts about 110,000 rows. On postgresql 7.4 on a debian machine it takes a > shade over 2 minutes to complete. On an amd64 box running gentoo, it takes > over an hour and fourty minutes to complete. The query plan on the debian > host that completes quickly follows: > This may be a bug, thanks for filing it. However, we can't tell at the moment from what you've said. The EXPLAINs you've enclosed are for SELECTs, yet your bug report describes INSERTs as being the things that are slow. [You may find better performance from using COPY] Also, your tests have compared two systems, so it might be that the hardware or configuration of one system is different from the other. If you could repeat the test on one single system, then this would assist in the diagnosis of this bug report. Also, if you could describe the workload that is giving you a problem more exactly, that would help. Specifically, can you confirm that you have run ANALYZE on the tables, and also give us some idea of numbers of rows in each table at the time you first run your programs. > the query optimiser seems to be setting a default strategy of doing > sequential scans on an empty table, which is a fast strategy when the table > is empty and not particularly full, but obviously on a large table the > performance is O(N^2). > This is clearly a bug. There is clearly a problem, but it is not yet clearly a bug. If it is a bug, we're interested in solving it as much as you. > Please let me know if I can > provide any more information. Yes, all of the above, plus more. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Bruce Momjian writes: > OK, here are the TODO items I have created: > * Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO > SECOND > * Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH That example might better read, say, * Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Tom Lane wrote: > Bruce Momjian writes: > > OK, here are the TODO items I have created: > > > * Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO > > SECOND > > * Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH > > That example might better read, say, > >* Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO > MONTH Uh, I thought about that, but we are talking about an interval here, not a year, so 2005 years seems like a strange number of years to span. I will change it to 9-7 or something so it doesn't match the line above. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Bruce Momjian writes: > Tom Lane wrote: >> * Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH > Uh, I thought about that, but we are talking about an interval here, not > a year, so 2005 years seems like a strange number of years to span. Oh, right. Never mind ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Precision and scale of numeric column reported as value -1
Bug to report - For a numeric in a table defined by: CREATE TABLE SERG (F01NUM NUMERIC) ; , the precision and scale reported by ResultSetMetaData.getScale() and ResultSetMetaData.getPrecision() are value -1 Attached is a simple java program (Repro1.java) that repros the problem. Before running repro, you must first create the table "SERG" in a postgreSQL database and then modify the url, userid and password in the program to appropriate values. Notice that output from program shows precision = -1, scale = -1 Please note that the problem occurs for NUMERIC columns whose precision and scale are not explicitely stated. For example a column defined as NUMERIC(10,2) returns a correct precision and scale. Thanks, Sergio Lob Repro1.java Description: application/unknown-content-type-visualcafefile.document ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] [GENERAL] contrib module intagg crashing the backend
I wrote: > Grumble ... I seem to have managed to promote intagg from > broken-on-64bit-platforms to broken-on-every-platform ... > will look into a fix tomorrow. Ron's problem is essentially a double-free bug. In this patch: 2005-01-27 16:35 tgl * contrib/intagg/: int_aggregate.c, int_aggregate.sql.in (REL7_3_STABLE), int_aggregate.c, int_aggregate.sql.in (REL7_4_STABLE), int_aggregate.c, int_aggregate.sql.in (REL8_0_STABLE), int_aggregate.c, int_aggregate.sql.in: Fix security and 64-bit issues in contrib/intagg. This code could stand to be rewritten altogether, but for now just stick a finger in the dike. I modified intagg to declare its transition data type as int4[] (which is what it really is) rather than int4. Unfortunately that means that nodeAgg.c is now aware that the transition value is pass-by-reference, and so it thinks it needs to manage the memory used for it; which intagg.c is also trying to do; so they both free the same bit of memory. There is already a "proper" fix for this problem in CVS tip, but it's too invasive to consider back-patching; not least because nodeAgg's memory management strategy has changed since 7.3 and the fix would probably not work that far back. What I'm thinking I have to do is revert intagg in the back branches to lie about its transition data type, but still have it pull the pointer out of the passed Datum with DatumGetPointer (as opposed to the old, definitely 64-bit-broken method of DatumGetInt32 and then cast to pointer). This should work because nodeAgg doesn't inquire into the actual contents of any Datum it doesn't think is pass-by-reference; so it will never discard the upper bits of the pointer. Ugh. Glad we have a cleaner solution to go forward with. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
Simon Riggs wrote: The EXPLAINs you've enclosed are for SELECTs, yet your bug report describes INSERTs as being the things that are slow. [You may find better performance from using COPY] Simon, Brian and I are working together on this problem. We're starting with an empty database, creating four tables, and populating those tables with a total of 180,000-200,000 rows. Each table has a primary key, and several of the tables reference foreign keys in other tables. We've written a Python script, using psycopg, which executes all the queries to create the tables and insert the rows. The database is running on the same machine where the script runs. I've seen similar performance when issuing a COMMIT after each insertion, and also after batching insertions in blocks of 250 per COMMIT, so batching the commits is not helping much. I've looked at the possibility of using COPY, but in our production environment it will be prohibitive to build a flat file with all this data. I'd rather generate it on the fly, as we've been able to do with PostgreSQL 7.4. Also, your tests have compared two systems, so it might be that the hardware or configuration of one system is different from the other. When running with PostgreSQL 7.4 on a dual-CPU Athlon MP2400+ machine with a gigabyte of RAM, running Debian Linux version 2.6.8.1, we were able to insert all this data in 5-7 minutes. It's taken a while to install Postgres 8.0.1 on the same machine, but now I have, and it's taking 40-45 minutes to run the same insert script. This is similar to the performance we saw on another machine, a fast single-CPU AMD64 box running Gentoo. I don't think it's a hardware issue. I dug around a bit, and found suggestions that this sort of problem could be worked around by breaking the database connection and restarting it after the tables had been partially filled. I modified our script to break and re-establish the database connection when each table first has 4,000 records inserted, and the performance is greatly improved; it now takes only about 3.5 minutes to insert 180,000+ rows. I've since modified this script to build and populate a fifth table with over 1.3 million rows. The fifth table has no primary key, but lists a foreign key into one of the first four tables. With the above modification (break and re-build the DB connection after 4,000 rows have been inserted), the whole database can be populated in about 15 minutes. I wouldn't have dared try to build a one-million-plus-row table until I found this speed-up. If you could repeat the test on one single system, then this would assist in the diagnosis of this bug report. Also, if you could describe the workload that is giving you a problem more exactly, that would help. Specifically, can you confirm that you have run ANALYZE on the tables, and also give us some idea of numbers of rows in each table at the time you first run your programs. Just to see if it would help, I tried modifying the script to run an ANALYZE against each table after 4,000 insertions, instead of breaking and re-establishing the DB connection. I still saw ~45-minute times to insert 180,000 rows. I then tried running ANALYZE against each table after *each* 4,000 rows inserted, and again, it took about 45 minutes to run the insert. Each table is empty when I first run the program. I am dropping and re-creating the database for each test run. There is clearly a problem, but it is not yet clearly a bug. If it is a bug, we're interested in solving it as much as you. I'd be happy to run further tests or provide more details, if they'll help. We now have a workaround which is allowing us to proceed with our project, but I'd like to know if there's another way to do this. While I understand that large or complex databases require careful tuning, I was surprised to see a six- or seven-fold increase in run times between PostgreSQL 7.4 and 8.0.1 on the same hardware, on an operation which seems fairly straightforward: populating an empty table. One other thing which puzzled me: as a test, I tried modifying our script to spit out raw SQL statements instead of connecting to the database and performing the inserts itself. Normally, our script populates two tables in one pass, and then populates the third and fourth tables in a second pass. I massaged the SQL by hand to group the inserts together by table, so that the first table would be entirely populated, then the second, etc. When I ran this SQL script by piping it straight into psql, it finished in about four minutes. This is comparable to the time it takes to run my modified script which breaks and re-establishes the connection to the database. It would appear that psql is doing something right here which we have had to go out of our way to get with psycopg. Keith Browne [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your f
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
On 2005-03-23, Keith Browne <[EMAIL PROTECTED]> wrote: > One other thing which puzzled me: as a test, I tried modifying our > script to spit out raw SQL statements instead of connecting to the > database and performing the inserts itself. Normally, our script > populates two tables in one pass, and then populates the third and > fourth tables in a second pass. I massaged the SQL by hand to group the > inserts together by table, so that the first table would be entirely > populated, then the second, etc. When I ran this SQL script by piping > it straight into psql, it finished in about four minutes. Changing the order so that the referenced table is fully populated, or at least populated with more than a handful of pages of rows, before doing _any_ insert on a referencing table in the same session will avoid the misplan of the FK trigger queries, because when the first insert happens on a referencing table, there will be no reason for the planner to prefer a sequential scan. So this result is not surprising at all. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
> "Bruce" == Bruce Momjian writes: Bruce> OK, here are the TODO items I have created: Bruce> * Add support for ANSI time INTERVAL syntax, INTERVAL '1 Bruce> 2:03:04' DAY TO SECOND Bruce> * Add support for ANSI date INTERVAL Bruce> syntax, INTERVAL '1-2' YEAR TO MONTH You may as well use the correct ANSI terminology: * Add support for ANSI day-time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND * Add support for ANSI year-month INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH Bruce> * Process mixed ANSI/PG INTERVAL syntax, and round value Bruce> to requested precision Never round, I think. Throwing away precision should be an exception, unless the discarded fields were zero. Bruce> Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 Bruce> year' AS INTERVAL MONTH), and this should return '12 Bruce> months' Bruce> Is this sufficient? You also need to make EXTRACT do the right thing. eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 but EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 Ditto for day-time intervals, of course. I'll have a think about if there's anything else... -roy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Bruce> * Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 Bruce> minute 30 seconds' This seems redundant; it's just another example of the ANSI day-time interval syntax... -roy ---(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: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Bruce> Is this sufficient? Also support for ANSI interval data types is incomplete in the parser, in that it doesn't recognize the precision field. eg CREATE TABLE foo (a INTERVAL MONTH(3)); fails to parse. -roy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
Andrew - Supernews <[EMAIL PROTECTED]> writes: > Changing the order so that the referenced table is fully populated, or at > least populated with more than a handful of pages of rows, before doing > _any_ insert on a referencing table in the same session will avoid the > misplan of the FK trigger queries, because when the first insert happens > on a referencing table, there will be no reason for the planner to prefer > a sequential scan. So this result is not surprising at all. I'm still looking for an example that demonstrates why this is a common problem that we need to worry about. ISTM that if an FK reference is hit when there are still zero entries in the referenced table, that insertion will fail anyway, and so people wouldn't try to load data in such an order. In the long term it would be good to replan the FK plans when the referenced tables have grown so much that the plan ought to change. Once we have the plan invalidation machinery that Neil is working on, it might be fairly practical to do that; but no such thing is going to appear in existing release branches of course. We could band-aid this in 8.0 as previously suggested (have the planner assume > 0 pages when it sees actually 0 pages) but without seeing a concrete example I can't tell if that will fix the complaint or not. 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 #1517: SQL interval syntax is accepted by the parser,
Roy Badami wrote: > > Bruce>* Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 > Bruce> minute 30 seconds' > > This seems redundant; it's just another example of the ANSI day-time > interval syntax... The reason I added it is that usually people think of the PG syntax as '1 hour 30 seconds'. The '1:30' is a kind of subtle because both PG and ANSI support that syntax and we need to handle that. The tricky part is that we can't say by looking at '1:30' whether it is PG or ANSI, and that will affect how we deal with the clause after it. -- 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 8: explain analyze is your friend
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Bruce> The reason I added it is that usually people think of the Bruce> PG syntax as '1 hour 30 seconds'. The '1:30' is a kind of Bruce> subtle because both PG and ANSI support that syntax and we Bruce> need to handle that. The tricky part is that we can't say Bruce> by looking at '1:30' whether it is PG or ANSI, and that Bruce> will affect how we deal with the clause after it. It could be either in ANSI: INTERVAL '1:30' MINUTE TO SECOND INTERVAL '1:30' HOUR TO MINUTE Similarly, pg interprets INTERVAL '1' as INTERVAL '1 second' In ANSI, it could be any one of INTERVAL '1' SECOND INTERVAL '1' MINUTE INTERVAL '1' HOUR INTERVAL '1' DAY INTERVAL '1' MONTH INTERVAL '1' YEAR In ANSI you can only parse the string by reference to the interval type. -roy ---(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
Tom Lane wrote: I'm still looking for an example that demonstrates why this is a common problem that we need to worry about. ISTM that if an FK reference is hit when there are still zero entries in the referenced table, that insertion will fail anyway, and so people wouldn't try to load data in such an order. Tom, We're filling pairs of tables with rows having nearly a one-to-one mapping; very rarely, the second table will have multiple rows corresponding to one row in the first table. When we insert the first row in the second table, therefore, we've just put the corresponding row into the first table, so the foreign key constraint is satisfied. I can't say how common this sort of thing will be. It appears to me that BUG #1541 is similar to what we're seeing, and a search of the mailing lists also turns up this message: http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php which also describes symptoms similar to what I'm seeing. We could band-aid this in 8.0 as previously suggested (have the planner assume > 0 pages when it sees actually 0 pages) but without seeing a concrete example I can't tell if that will fix the complaint or not. It sounds like this could work for us, if it would disable sequential searches into a table which grows from 0 to >60,000 rows in one session. Is breaking and re-establishing the database session the best workaround, or is there a better way to provide a hint to the planner? Regards, Keith Browne [EMAIL PROTECTED] ---(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] Precision and scale of numeric column reported as value
Sergio Lob wrote: Bug to report - For a numeric in a table defined by: CREATE TABLE SERG (F01NUM NUMERIC) ; , the precision and scale reported by ResultSetMetaData.getScale() and ResultSetMetaData.getPrecision() are value -1 What should they return instead in this case? -O ---(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: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Roy Badami wrote: > > "Bruce" == Bruce Momjian writes: > > Bruce> OK, here are the TODO items I have created: > > Bruce> * Add support for ANSI time INTERVAL syntax, INTERVAL '1 > Bruce> 2:03:04' DAY TO SECOND > > Bruce> * Add support for ANSI date INTERVAL > Bruce> syntax, INTERVAL '1-2' YEAR TO MONTH > > You may as well use the correct ANSI terminology: > > * Add support for ANSI day-time INTERVAL syntax, INTERVAL '1 > 2:03:04' DAY TO SECOND > > * Add support for ANSI year-month INTERVAL syntax, INTERVAL '1-2' > YEAR TO MONTH > > Bruce> * Process mixed ANSI/PG INTERVAL syntax, and round value > Bruce> to requested precision OK, I added a new ANSI INTERVAL section to the TODO list: * Add ANSI INTERVAL handling o Add support for day-time syntax, INTERVAL '1 2:03:04' DAY TO SECOND o Add support for year-month syntax, INTERVAL '50-6' YEAR TO MONTH o Process mixed ANSI/PG syntax, and round value to requested precision or generate an error o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL MONTH), and this should return '12 months' o Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 minute 30 seconds' > Never round, I think. Throwing away precision should be an exception, > unless the discarded fields were zero. Seems that is implentation-dependent so I added "round' or error" to the TODO item. > Bruce> Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 > Bruce> year' AS INTERVAL MONTH), and this should return '12 > Bruce> months' > > Bruce> Is this sufficient? > > You also need to make EXTRACT do the right thing. > > eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 > > but > > EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 > > Ditto for day-time intervals, of course. Uh, I think this already works fine for PG syntax, and I assume once we support ANSI syntax it will work fine too: test=> select EXTRACT (MONTH FROM INTERVAL '1 year 2 month' YEAR TO MONTH); date_part --- 2 (1 row) -- 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 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: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Roy Badami wrote: > > Bruce> Is this sufficient? > > Also support for ANSI interval data types is incomplete in the parser, > in that it doesn't recognize the precision field. > > eg CREATE TABLE foo (a INTERVAL MONTH(3)); > > fails to parse. Added to TODO: o Support precision, CREATE TABLE foo (a INTERVAL MONTH(3)) -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Precision and scale of numeric column reported as value
Precision is the number of total digits in the number. Scale is the number of fractional digits. For instance, a column defined as NUMERIC(10,3) should return precision=10, scale=3. The error only occurs for a column defined as NUMERIC (without precision or scale specified). Presumably, there is a default precision and scale assigned to the column by postgreSQL, which is not -1. Sergio Oliver Jowett wrote: Sergio Lob wrote: Bug to report - For a numeric in a table defined by: CREATE TABLE SERG (F01NUM NUMERIC) ; , the precision and scale reported by ResultSetMetaData.getScale() and ResultSetMetaData.getPrecision() are value -1 What should they return instead in this case? -O ---(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 #1518: Conversions to (undocumented) SQL year-month and
Bruce> Seems that is implentation-dependent so I added "round' or Bruce> error" to the TODO item. Ah, OK. "A guide to the SQL standard" claims it's an exception, but I haven't checked the actual standard. Bruce> Uh, I think this already works fine for PG syntax, and I Bruce> assume once we support ANSI syntax it will work fine too: I guess. It's just that it's something that EXTRACT doesn't currently have to worry about, so it would be easy to overlook. -roy ---(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: [BUGS] Precision and scale of numeric column reported as value
On Wed, Mar 23, 2005 at 04:13:22PM -0500, Sergio Lob wrote: > Precision is the number of total digits in the number. Scale is the > number of fractional digits. > For instance, a column defined as NUMERIC(10,3) should return > precision=10, scale=3. The error only occurs for a column defined as > NUMERIC (without precision or scale specified). Presumably, there is a > default precision and scale assigned to the column by postgreSQL, which > is not -1. That assumption is wrong. There is no default. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
On 2005-03-23, Tom Lane <[EMAIL PROTECTED]> wrote: > Andrew - Supernews <[EMAIL PROTECTED]> writes: >> Changing the order so that the referenced table is fully populated, or at >> least populated with more than a handful of pages of rows, before doing >> _any_ insert on a referencing table in the same session will avoid the >> misplan of the FK trigger queries, because when the first insert happens >> on a referencing table, there will be no reason for the planner to prefer >> a sequential scan. So this result is not surprising at all. > > I'm still looking for an example that demonstrates why this is a common > problem that we need to worry about. ISTM that if an FK reference is > hit when there are still zero entries in the referenced table, that > insertion will fail anyway, and so people wouldn't try to load data in > such an order. Think "1 row", not "0 rows". It is not reasonable to assume that _all_ cases of data loading (other than perhaps the very largest) will be done by loading entire tables at a time, especially when importing from external sources where the data is differently structured. > We could band-aid this in 8.0 as previously suggested (have the planner > assume > 0 pages when it sees actually 0 pages) but without seeing a > concrete example I can't tell if that will fix the complaint or not. It won't; the problem is with 1 page, not 0. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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] Precision and scale of numeric column reported as value
On Wed, 23 Mar 2005, Sergio Lob wrote: > Precision is the number of total digits in the number. Scale is the > number of fractional digits. > For instance, a column defined as NUMERIC(10,3) should return > precision=10, scale=3. The error only occurs for a column defined as > NUMERIC (without precision or scale specified). Presumably, there is a > default precision and scale assigned to the column by postgreSQL, which > is not -1. IIRC, there is not a default precision and scale assigned to the column. It's treated as having an unspecified precision and scale. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Precision and scale of numeric column reported as value
Sergio Lob wrote: Precision is the number of total digits in the number. Scale is the number of fractional digits. For instance, a column defined as NUMERIC(10,3) should return precision=10, scale=3. Yes, I understand that. The error only occurs for a column defined as NUMERIC (without precision or scale specified). Presumably, there is a default precision and scale assigned to the column by postgreSQL, which is not -1. There appears to be no default, which is why we currently return -1. Scale should default to 0 per the standard, but defaults to whatever the precision is in PostgreSQL (see the docs for details). The docs claim an implementation precision limit of 1000, but that doesn't seem to correspond to the actual implementation -- I can insert and retrieve 2000 digits NUMERICs (for example) just fine. I can't see an obvious limit on precision in the backend code. There may be a theoretical limit somewhere around 2^30 digits, I think (limited by the backend's memory allocation sanity checks), but I have not tested that. Given that there is effectively no default, do you have suggestions for a better value to return? -O ---(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 wrote: > > Bruce> The reason I added it is that usually people think of the > Bruce> PG syntax as '1 hour 30 seconds'. The '1:30' is a kind of > Bruce> subtle because both PG and ANSI support that syntax and we > Bruce> need to handle that. The tricky part is that we can't say > Bruce> by looking at '1:30' whether it is PG or ANSI, and that > Bruce> will affect how we deal with the clause after it. > > It could be either in ANSI: > >INTERVAL '1:30' MINUTE TO SECOND >INTERVAL '1:30' HOUR TO MINUTE > > Similarly, pg interprets > >INTERVAL '1' > > as > >INTERVAL '1 second' > > In ANSI, it could be any one of > >INTERVAL '1' SECOND >INTERVAL '1' MINUTE >INTERVAL '1' HOUR >INTERVAL '1' DAY >INTERVAL '1' MONTH >INTERVAL '1' YEAR > > In ANSI you can only parse the string by reference to the interval type. This is a good point. I am thinking we are going to have some backwards compatibility problems here. Consider this TODO item: o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL MONTH), and this should return '12 months' In this, we know that '1 year' is PG syntax, and can do the cast cleanly. However, in the case of '01:02' or '1', we don't know if that is PG stynax or ANSI syntax. I think we are going to have to interpret those as ANSI. Here is a clearer TODO item. I changed this: o Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 minute 30 seconds' to this: o Interpret syntax that isn't uniquely ANSI or PG, like '1:30' or '1' as ANSI syntax, e.g. interpret '1:30' MINUTE TO SECOND as '1 minute 30 seconds' This is going to be a backward compatibility problem, but to support ANSI syntax we are going to need to do this. -- 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 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,
Bruce> This is going to be a backward compatibility problem, but Bruce> to support ANSI syntax we are going to need to do this. Given the existence of the INTERVAL '1' MONTH etc syntax is completely undocumented, I doubt it's a major problem. (Actually, INTERVAL '1' and INTERVAL '1:30' are undocumented, too, but it's probably more likely that people are relying on that). -roy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Roy Badami wrote: > Bruce> Seems that is implentation-dependent so I added "round' or > Bruce> error" to the TODO item. > > Ah, OK. "A guide to the SQL standard" claims it's an exception, but I > haven't checked the actual standard. Here is an email stating it is implementation defined: http://archives.postgresql.org/pgsql-bugs/2005-03/msg00162.php > Bruce> Uh, I think this already works fine for PG syntax, and I > Bruce> assume once we support ANSI syntax it will work fine too: > > I guess. It's just that it's something that EXTRACT doesn't currently > have to worry about, so it would be easy to overlook. I don't see how the new code would break EXTACT. I don't think we are going to have to change any internal representations. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Bruce> Here is an email stating it is implementation defined: Bruce> http://archives.postgresql.org/pgsql-bugs/2005-03/msg00162.php Hmm, looks like I'm mistaken, then. In fact the "whether to truncate or round" bit suggests you should do one or the other, rather than raise an exception. Will have to take a look at the spec when I get a moment... -roy ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami wrote: > > Bruce> This is going to be a backward compatibility problem, but > Bruce> to support ANSI syntax we are going to need to do this. > > Given the existence of the INTERVAL '1' MONTH etc syntax is completely > undocumented, I doubt it's a major problem. > > (Actually, INTERVAL '1' and INTERVAL '1:30' are undocumented, too, but > it's probably more likely that people are relying on that). Yes, I am thinking that interval strings that contain letters are going to be PG, and anything else is ANSI. -- 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 #1552: massive performance hit between 7.4 and 8.0.1
Keith Browne <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I'm still looking for an example that demonstrates why this is a common >> problem that we need to worry about. > We're filling pairs of tables with rows having nearly a one-to-one > mapping; very rarely, the second table will have multiple rows > corresponding to one row in the first table. When we insert the first > row in the second table, therefore, we've just put the corresponding row > into the first table, so the foreign key constraint is satisfied. Hmm ... >> We could band-aid this in 8.0 as previously suggested (have the planner >> assume > 0 pages when it sees actually 0 pages) but without seeing a >> concrete example I can't tell if that will fix the complaint or not. > It sounds like this could work for us, 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. [ thinks for a bit... ] The reason 7.4 and before worked reasonably for you is that they assumed the 10/1000 statistics for any never-yet-vacuumed table, whether it is empty or not. (This worked fine for your problem but shot a lot of other people in the foot, because that's what the estimate would stay at even if the table grew vastly larger, so long as it wasn't vacuuumed.) 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. I'm not sure if this is workable. It looks to me like we'd have to approximate the "never vacuumed" condition by checking whether pg_class.reltuples and relpages are both zero, which is the initial condition all right but would also arise after a vacuum finds nothing in the table. So basically the planner would never optimize the entirely-empty-table condition properly, even after vacuum. Maybe this is the least bad alternative for 8.0.*. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Bruce Momjian writes: > o Interpret syntax that isn't uniquely ANSI or PG, like '1:30' or > '1' as ANSI syntax, e.g. interpret '1:30' MINUTE TO SECOND as > '1 minute 30 seconds' > This is going to be a backward compatibility problem, but to support > ANSI syntax we are going to need to do this. We should do that *only* when an ISO (not ANSI) interval constraint is present. So plain INTERVAL '1' would still be read under PG rules. That would eliminate the backward-compatibility problem pretty well (since the constraints aren't documented and hence aren't being used now), while not posing a big problem for ISO cases (since if there's no constraint there are no ambiguous cases, I believe --- the ISO syntax would require all fields to be present). regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Tom> That would eliminate the backward-compatibility problem Tom> pretty well (since the constraints aren't documented and Tom> hence aren't being used now), while not posing a big problem Tom> for ISO cases (since if there's no constraint there are no Tom> ambiguous cases, I believe --- the ISO syntax would require Tom> all fields to be present). The 'constraint' (interval type descriptor or whatever it's really called) is mandatory in standard SQL, I think, so there's no ambiguity anyway, unless anyone is using this undocumented syntax at the moment... ---(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,
Roy> The 'constraint' (interval type descriptor or whatever it's Roy> really called) is mandatory in standard SQL, I think, so Roy> there's no ambiguity anyway, unless anyone is using this Roy> undocumented syntax at the moment... Incidentally, this was the ratinale behind my earlier suggestion, that: * if the interval type descriptor is absent, parse the interval as a traditional postgres interval * if the interval type descriptor is present, parse the interval according to the standard syntax I have no objection to allowing things like '1 hour 10 minutes' DAY TO SECOND but I'm just wondering whether the hybrid syntax is an unnecessary complication. -roy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy> I have no objection to allowing things like Roy>'1 hour 10 minutes' DAY TO SECOND Roy> but I'm just wondering whether the hybrid syntax is an Roy> unnecessary complication. And furthermore, if you really want to allow constrained postgres syntax interval literals (and I can't at the moment see how constraining a literal is useful) then why *not* use the syntax Brian suggested: INTERVAL DAY TO SECOND '1 hour 10 minutes' This keeps the non-standard postgres syntax and the SQL standard syntax nicely separate... -roy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Precision and scale of numeric column reported as value
Oliver Jowett <[EMAIL PROTECTED]> writes: > There appears to be no default, which is why we currently return -1. The spec's notion of a "default precision and scale" is that every numeric column has a specific precision and scale --- ie, is physically fixed-width --- and everything you store into it will be coerced to that precision and scale. Postgres doesn't do it that way, which is why the notion of a default is a bit meaningless. > Scale should default to 0 per the standard, but defaults to whatever the > precision is in PostgreSQL (see the docs for details). If you specify a precision only, we do assume scale 0 to go with it. It's only the case of an unconstrained numeric column that we depart from the spec for. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Tom> We should do that *only* when an ISO (not ANSI) Just to clarify, is that a distinction or just a clarification? (ie are ANSI and ISO SQL different?) -roy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Roy Badami wrote: > Bruce> Here is an email stating it is implementation defined: > > Bruce>http://archives.postgresql.org/pgsql-bugs/2005-03/msg00162.php > > Hmm, looks like I'm mistaken, then. In fact the "whether to truncate > or round" bit suggests you should do one or the other, rather than > raise an exception. > > Will have to take a look at the spec when I get a moment... OK, TODO updated: o Round or truncate values to the requested precision, e.g. INTERVAL '11 months' AS YEAR should return one or zero The full TODO section is: * Add ANSI INTERVAL handling o Add support for day-time syntax, INTERVAL '1 2:03:04' DAY TO SECOND o Add support for year-month syntax, INTERVAL '50-6' YEAR TO MONTH o Interpret syntax that isn't uniquely ANSI or PG, like '1:30' or '1' as ANSI syntax, e.g. interpret '1:30' MINUTE TO SECOND as '1 minute 30 seconds' o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL MONTH), and this should return '12 months' o Round or truncate values to the requested precision, e.g. INTERVAL '11 months' AS YEAR should return one or zero o Support precision, CREATE TABLE foo (a INTERVAL MONTH(3)) -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Bruce Momjian writes: > ... I don't think we are > going to have to change any internal representations. In order to support the spec *exactly*, we would have to. For instance we cannot presently tell the difference between '13 months' and '1 year 1 month' ... they both end up stored as '13 months'. I can't say that I find this very important, but it does put limits on how exactly we can emulate the spec. Note that I don't find "emulate the spec exactly" to be a desirable goal anyway; its failure to consider daylight-savings issues is an unacceptable shortcoming. The one internal representation change I would really like to see is to store days separately from months and seconds, so that we can distinguish '24 hours' from '1 day' (the latter should sometimes be equivalent to 23 or 25 hours). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Bruce> o Interpret INTERVAL '1 year' MONTH as I'm still not convinced that allowing this doesn't just create unnecessary confusion. How about: o Decide whether constructs such as INTERVAL '1 year' MONTH should be allowed, and if so what they should mean, eg perhaps CAST (INTERVAL '1 year' AS INTERVAL MONTH) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month
Tom> In order to support the spec *exactly*, we would have to. Tom> For instance we cannot presently tell the difference between Tom> '13 months' and '1 year 1 month' ... they both end up stored Tom> as '13 months'. I can't say that I find this very important, Tom> but it does put limits on how exactly we can emulate the Tom> spec. Which is where my comment about EXTRACT comes in. They can both be stored as 13 months, but EXTRACT (MONTH FROM ...) should return 1 month or 13 months as appropriate. Surely this isn't a problem, you know the type of the interval? So you _can_ emulate the spec, you just don't use the same internal representation that a naive implementation of the spec would... -roy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami <[EMAIL PROTECTED]> writes: > Roy> The 'constraint' (interval type descriptor or whatever it's > Roy> really called) is mandatory in standard SQL, True. ( is what SQL99 calls it.) > I have no objection to allowing things like >'1 hour 10 minutes' DAY TO SECOND > but I'm just wondering whether the hybrid syntax is an unnecessary > complication. In the context of interval literals it's probably unnecessary, but that's not the only thing to worry about. In particular we have to consider the behavior of the input and output routines for cases like COPY. I think it would be really bad to reject '1 hour 10 minutes' as data input into an interval field just because it has an ISO qualifier. Also, I would personally prefer to see the output from an interval field remain in the Postgres format, precisely because the ISO format is ambiguous without knowledge of the qualifier. (Possibly we should create a DateStyle-like GUC to determine that, but so far no one's requested one.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month
Roy Badami wrote: > > Tom> In order to support the spec *exactly*, we would have to. > Tom> For instance we cannot presently tell the difference between > Tom> '13 months' and '1 year 1 month' ... they both end up stored > Tom> as '13 months'. I can't say that I find this very important, > Tom> but it does put limits on how exactly we can emulate the > Tom> spec. > > Which is where my comment about EXTRACT comes in. They can both be > stored as 13 months, but EXTRACT (MONTH FROM ...) should return 1 > month or 13 months as appropriate. Surely this isn't a problem, you > know the type of the interval? > What happens if you store '13 months' into an interval column that is YEAR TO MONTH? Does extract MONTH return 1 or 13? Right now we return one: test=> select extract(month from interval '13 months' year to month); date_part --- 1 (1 row) but this seems strange: test=> select extract(month from interval '13 months' month); date_part --- 1 (1 row) It is because it is really '1 year 1 month': test=> select interval '13 months' as month; month -- 1 year 1 mon (1 row) What I would really like to avoid is having a different internal representation for ANSI and PG interval values. The lack of complaints all these years perhaps means people either don't care or accept the PG behavior. -- 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 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: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami <[EMAIL PROTECTED]> writes: > Tom> We should do that *only* when an ISO (not ANSI) > Just to clarify, is that a distinction or just a clarification? (ie > are ANSI and ISO SQL different?) There is no "ANSI SQL". Only ISO SQL. ANSI never issued their own spec to my knowledge ... unlike, say, C, where I believe ANSI C was in fact issued by ANSI. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Roy Badami <[EMAIL PROTECTED]> writes: > Which is where my comment about EXTRACT comes in. They can both be > stored as 13 months, but EXTRACT (MONTH FROM ...) should return 1 > month or 13 months as appropriate. Surely this isn't a problem, you > know the type of the interval? EXTRACT doesn't have access to the typmod of its input, in general. We associate typmods with stored columns but not with the results of arbitrary expressions. regards, tom lane ---(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: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month
Bruce> What happens if you store '13 months' into an interval Bruce> column that is YEAR TO MONTH? Does extract MONTH return 1 Bruce> or 13? In standard SQL the MONTH field of INTERVAL YEAR TO MONTH can't contain a value greater than 11. Though I don't immediately see how you'd go about storing 13 in the month field. I don't think there's an analogue of EXTRACT that allows you to set fields, is there? Bruce> The lack of complaints all these years perhaps means people Bruce> either don't care or accept the PG behavior. To be honest, I don't really care :-) I try to write my SQL in as standard a way as possible, in case I later want to port to another database... I would be perfectly happy for INTERVAL '1' MONTH to be a syntax error. I just don't like the fact that it gives me a zero interval. Taking out the ISO support from the parser is a valid fix as far as I'm concerned (though actually making it do the ISO thing would obviously be nicer) -roy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Tom> In the context of interval literals it's probably Tom> unnecessary, but that's not the only thing to worry about. Tom> In particular we have to consider the behavior of the input Tom> and output routines for cases like COPY. I think it would be Tom> really bad to reject '1 hour 10 minutes' as data input into Tom> an interval field just because it has an ISO qualifier. Hmm, but COPY is non-standard, so I'd be happy that it insisted on postgres interval syntax. ANSI interval syntax is confusing in this context, precisely because there is nowhere to actually put an 'interval qualifier' in the literals. Otherwise the fact that ALTERing a table to add a constraint will completely change the semantices of the COPYing data into the table worries me. I don't think that conceptually the qualifier on an interval type is really the same thing as the qualifier on an interval literal. Tom> Also, I would personally prefer to see the output from an Tom> interval field remain in the Postgres format Seconded. But that's fine, too, I think. Pretty much everything about an interactive SQL session is implementation defined. If people are using embedded SQL, or one of the libraries, than isn't it a non-issue? -roy ---(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: > There is no "ANSI SQL". Only ISO SQL. ANSI never issued their own > spec to my knowledge ... unlike, say, C, where I believe ANSI C was > in fact issued by ANSI. There is an ANSI SQL standard ANSI X3.135. At least my copy of SQL 1999 carries a combined ANSI/ISO label. Nevertheless, referring to ISO seems more appropriate. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami <[EMAIL PROTECTED]> writes:
> Tom> In particular we have to consider the behavior of the input
> Tom> and output routines for cases like COPY.
> Hmm, but COPY is non-standard, so I'd be happy that it insisted on
> postgres interval syntax.
It's not different from
INSERT INTO foo VALUES('1 year 1 month');
Nothing nonstandard about that that I can see.
> ANSI interval syntax is confusing in this
> context, precisely because there is nowhere to actually put an
> 'interval qualifier' in the literals.
Yes. The ISO design for the datatype is pretty brain-dead if you ask
me --- the basic meaning of a data literal shouldn't be so dependent
on context. Still, it's there, and we should make some effort towards
supporting all but the really awfulest parts of it ;-)
regards, tom lane
---(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: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Tom> It's not different from
Tom> INSERT INTO foo VALUES('1 year 1 month');
Tom> Nothing nonstandard about that that I can see.
Oh, does ISO allow a cast from a string to an interval? Yes, I think
it probably does. And with the interpretation of the string is
dependent on the ISO interval type, just like for literals? Hmm, yes,
I think it probably does...
That convinces me that you have to figure out what to do with
CAST ('1 year 1 month' AS INTERVAL MONTH)
It _still_ doesn't completely convince me that you need to (or even
want to) support hybrid interval syntax.
Though given you have to support both pg and ISO syntax in the cast, I
guess it becomes more consistent (and less implementation work) to
support both in the literal, too...
I'm concinced, I think. (For the moment :)
-roy
---(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> Yes. The ISO design for the datatype is pretty brain-dead if Tom> you ask me --- the basic meaning of a data literal shouldn't Tom> be so dependent on context. Arguably it's not, because the interval qualifier is an intrinsic (and mandatory) part of the literal syntax, so the full ISO interval is completely unambigous. Where you run into problems is where you are casting strings to intervals, in which case the way the string is parsed is (rather unncessarily) dependent on the exact type of the interval. This area certainly seems (over) complex, although you can see the logic behind the design... -roy ---(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: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami <[EMAIL PROTECTED]> writes:
> That convinces me that you have to figure out what to do with
> CAST ('1 year 1 month' AS INTERVAL MONTH)
> It _still_ doesn't completely convince me that you need to (or even
> want to) support hybrid interval syntax.
> Though given you have to support both pg and ISO syntax in the cast, I
> guess it becomes more consistent (and less implementation work) to
> support both in the literal, too...
Right. It's the same underlying routines; it would actually be pretty
hard to make the above cast behave differently from input into a column
of INTERVAL MONTH type, or from the interval-literal case. In all these
cases we are taking a string and cramming it into the input conversion
function for the INTERVAL datatype. The conversion function has access
to the typmod (ie the encoded ) but it doesn't know
much more about the context than that.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Roy Badami <[EMAIL PROTECTED]> writes: > Tom> Yes. The ISO design for the datatype is pretty brain-dead if > Tom> you ask me --- the basic meaning of a data literal shouldn't > Tom> be so dependent on context. > Arguably it's not, because the interval qualifier is an intrinsic (and > mandatory) part of the literal syntax, so the full ISO interval is > completely unambigous. Well, it's okay *in the context of an interval literal*, where everything you need to know is right there, even if some of it is inside the quotes and some isn't. The problem comes as soon as you consider external data representations --- if you consider the bit inside the quotes to be the preferred textual representation, then you have a situation where you can't interpret the value without context information that's not directly attached to the data. This is why I say it's ambiguous. As you point out, the ISO spec doesn't actually say that that string literal has to be the external textual representation ... but it's surely pretty bizarre that it's not a good candidate to be the textual representation. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
> "Bruce" == Bruce Momjian writes: >> You also need to make EXTRACT do the right thing. >> >> eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 >> >> but >> >> EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 >> >> Ditto for day-time intervals, of course. Bruce> Uh, I think this already works fine for PG syntax, and I Bruce> assume once we support ANSI syntax it will work fine too: Ok, so based on Tom's comments it sounds like this isn't a non-issue; in fact it sounds like it will be very hard to make this work (at least without changing the internal represnation of an interval). So perhaps there should be something on the TODO list, even if only: o figure out whether it is feasible to make EXTRACT obey ISO standard semantics. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Tom> As you point out, the ISO spec doesn't actually say that that Tom> string literal has to be the external textual representation Tom> ... but it's surely pretty bizarre that it's not a good Tom> candidate to be the textual representation. Yes, I'm convicned. Particularly after considering casts of strings to intervals... -roy ---(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,
Tom Lane wrote: > Roy Badami <[EMAIL PROTECTED]> writes: > > Tom> We should do that *only* when an ISO (not ANSI) > > Just to clarify, is that a distinction or just a clarification? (ie > > are ANSI and ISO SQL different?) > > There is no "ANSI SQL". Only ISO SQL. ANSI never issued their own > spec to my knowledge ... unlike, say, C, where I believe ANSI C was > in fact issued by ANSI. TODO updated. -- 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 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 wrote: > Roy> The 'constraint' (interval type descriptor or whatever it's > Roy> really called) is mandatory in standard SQL, I think, so > Roy> there's no ambiguity anyway, unless anyone is using this > Roy> undocumented syntax at the moment... > > Incidentally, this was the ratinale behind my earlier suggestion, that: > > * if the interval type descriptor is absent, parse the interval as a >traditional postgres interval > > * if the interval type descriptor is present, parse the interval according >to the standard syntax > > I have no objection to allowing things like > >'1 hour 10 minutes' DAY TO SECOND > > but I'm just wondering whether the hybrid syntax is an unnecessary > complication. Added to TODO: o For syntax that isn't uniquely ISO or PG syntax, like '1:30' or '1', treat as ISO if there is a range specification clause, and as PG if there no clause is present, e.g. interpret '1:30' MINUTE TO SECOND as '1 minute 30 seconds', and interpret '1:30' as '1 hour, 30 minutes' This brings up the issue of how we are to output interval values. Currently we do: test=> select interval '1 hour 1 minute'; interval -- 01:01:00 (1 row) and test=> select interval '1 hour 1 second' hour to minute; interval -- 01:00:00 (1 row) so I think we are OK because we don't output ambiguous syntax. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
Roy Badami wrote: > > "Bruce" == Bruce Momjian writes: > > >> You also need to make EXTRACT do the right thing. > >> > >> eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1 > >> > >> but > >> > >> EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13 > >> > >> Ditto for day-time intervals, of course. > > Bruce> Uh, I think this already works fine for PG syntax, and I > Bruce> assume once we support ANSI syntax it will work fine too: > > Ok, so based on Tom's comments it sounds like this isn't a non-issue; > in fact it sounds like it will be very hard to make this work (at > least without changing the internal represnation of an interval). > > So perhaps there should be something on the TODO list, even if only: > >o figure out whether it is feasible to make EXTRACT obey ISO > standard semantics. I am thinking it isn't worth following the spec in this case. -- 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 8: explain analyze is your friend
