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:massiv
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
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,
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 ...
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 re
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_
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 t
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,
> "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 MONT
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/read
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)-
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 q
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 s
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 looki
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
su
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
-
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>
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, C
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
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> assu
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
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 s
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
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 precis
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 c
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' a
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:
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 except
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,
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 mul
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
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 be
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 earli
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
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
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
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,
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 mont
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
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
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 ju
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 imp
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 ... u
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 h
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
yo
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 '
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. Nevert
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 f
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
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 fu
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
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
>
> "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, o
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
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 ow
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 wa
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
> >>
57 matches
Mail list logo
