-hackers@postgresql.org; Dann
Corbit; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question: A summary
Chuck McDevitt [EMAIL PROTECTED] writes:
Just a curiosity question: Why is the type of a literal '1' unknown
instead of varchar(1)?
Because, for instance, it might be intended
On Wed, Jun 13, 2007 at 02:12:37AM -0400, Chuck McDevitt wrote:
So, where x = '(1,2)' might be legal for comparing to x, but a field of
type varchar(5) might not be, as in where x = y, where y is type
varchar(5) containing '(1,2)'.
Normally, just about every type can be converted to or from
Chuck McDevitt [EMAIL PROTECTED] writes:
Just a curiosity question: Why is the type of a literal '1' unknown
instead of varchar(1)?
Even if it was assigned a text datatype it would be the unconstrainted text
not varchar(1). If we used varchar(1) then things like:
create table foo as select
For some Unicode character sets, element_width can be as much as 4
In UTF8 one char can be up to 6 bytes, so 4 is not correct in general.
Andreas
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
Ühel kenal päeval, E, 2007-06-11 kell 22:55, kirjutas Dann Corbit:
-Original Message-
...
I hope someone who truly understands database interfaces will read
this thread and address the issue.
For now we will have to special case postgres in our application
until it is
; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Ühel kenal päeval, E, 2007-06-11 kell 22:11, kirjutas Larry McGhaw:
As far as I am aware these statements are true. If you have a
specific example you could provide to the contrary that would be
interesting
Larry McGhaw wrote:
Again, *all* other major relational databases do this ... even blob fields
have a maximum length reported from the database.
So what are you doing with the max length? Not all data types and values
have a meaningful max length, so you have to be able to deal with
Again, *all* other major relational databases do this ...
even blob fields have a maximum length reported from the database.
So what are you doing with the max length? Not all data types
and values have a meaningful max length, so you have to be
able to deal with variable length data
Larry McGhaw [EMAIL PROTECTED] writes:
The database *knows* this size of the char constant (obviously), and
should report the size via a metadata call, as all other relational
databases do.
I'm not even clear whether you and Dan are talking about the same thing. He's
talking about the number
Dann Corbit wrote:
-Original Message-
From: Hannu Krosing [mailto:[EMAIL PROTECTED]
Since libpq function PQfsize returns -2 for all constant character
strings in SQL statements ... What is the proper procedure to determine
the length of a constant character column after query execution
Thats exactly the point. Consider
select mytext from mytable ;
How can PostgreSQL possibly know the maximum length of the
returned values *before* it has scanned the whole table?
I think this focuses too much on those cases where it is not possible.
When it is not feasible like with a
Zeugswetter Andreas ADI SD wrote:
Thats exactly the point. Consider
select mytext from mytable ;
How can PostgreSQL possibly know the maximum length of the
returned values *before* it has scanned the whole table?
I think this focuses too much on those cases where it is not possible.
When it
Heikki Linnakangas wrote:
Actually, if you're in such a high throughput, client-side CPU-intensive
situation that this makes any difference, why are you copying the value
to another buffer in the first place? Just access it directly in the
libpq buffer returned by PQgetvalue, and move on.
On Tue, Jun 12, 2007 at 12:47:55PM +0200, Zeugswetter Andreas ADI SD wrote:
I think this focuses too much on those cases where it is not possible.
When it is not feasible like with a text column, clients deal with it
already (obviously some better than others).
It is for those cases where it
Larry McGhaw wrote:
Again, the issue is not our tool, but the deficiency in libpq/postgres
... even mysql gets its right .. why not Postgres?
Its not hard for a database to report metadata properly.
if I issue a sql statement:
select '123' from any table
the database should report that
: [HACKERS] Selecting a constant question
Larry McGhaw wrote:
Again, the issue is not our tool, but the deficiency in libpq/postgres
... even mysql gets its right .. why not Postgres?
Its not hard for a database to report metadata properly.
if I issue a sql statement:
select '123' from any
Hi,
Nobody is tring to attack anyone, but we're all surprised this is an
issue since you're the first person to have mentioned it. I have some
a query to test below:
On Tue, Jun 12, 2007 at 10:21:09AM -0700, Larry McGhaw wrote:
We noticed inexplicably that when we used a constant with a
Larry McGhaw wrote:
I'm really frustrated by this process I'm not trying to attack anyone
here. I'm just surprised that no one will even entertain the idea that
this is an issue that needs to be addressed.
Instead nearly all of the responses have been attacking the applications
that rely on
-Original Message-
From: Brian Hurt [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 12, 2007 11:09 AM
To: Larry McGhaw
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Larry McGhaw wrote:
I'm really frustrated by this process I'm not trying to attack
Larry McGhaw wrote:
For constant '123'::varchar(3) libpq returns the following:
Pqfsize returns -1
Pqfmod returns -1
That one certainly looks odd.
cheers
andrew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
That one surprised me as well.
Thanks
lm
-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 12, 2007 12:00 PM
To: Larry McGhaw
Cc: Brian Hurt; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Larry McGhaw wrote
Larry McGhaw [EMAIL PROTECTED] writes:
The statement above is contrary to my actual results. The proper length
is returned in all non-const cases.
Here is a specific example:
test=# create table test1 ( a varchar(20), b char(10), c integer );
CREATE TABLE
It's not returning a length at
Dunstan; Hannu Krosing; Tom Lane; Alvaro Herrera; Dann
Corbit; Gregory Stark; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Hi,
Nobody is tring to attack anyone, but we're all surprised this is an
issue since you're the first person to have mentioned it. I have some
First a comment:
At CONNX Solutions Inc., we believe sincerely that we should do whatever
is necessary to make our customers prosper. This means creation of
excellent tools and being responsive to customer needs. Secondly, we
believe that we should treat the customers the way that we want to be
-Original Message-
From: Larry McGhaw
Sent: Tuesday, June 12, 2007 1:40 PM
To: Martijn van Oosterhout
Cc: Andrew Dunstan; Hannu Krosing; Tom Lane; Alvaro Herrera; Dann
Corbit;
Gregory Stark; pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] Selecting a constant question
Dann Corbit wrote:
First a comment:
At CONNX Solutions Inc., we believe sincerely that we should do whatever
is necessary to make our customers prosper. This means creation of
excellent tools and being responsive to customer needs. Secondly, we
believe that we should treat the customers
Dan,
Secondly, we
believe that we should treat the customers the way that we want to be
treated.
I think that the PostgreSQL group has managed the first objective, but
not the second.
I just read this whole thread, and I feel that the sort of comment above is
completely unjustified, and
Dann Corbit [EMAIL PROTECTED] writes:
In the case of a SELECT query that selects a fixed constant of any sort,
it would be a definite improvement for PostgreSQL to give some sort of
upper maximum.
What's the point? You keep reminding us that your code is middleware
that can't assume anything
@postgresql.org; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question: A summary
Dann Corbit [EMAIL PROTECTED] writes:
In the case of a SELECT query that selects a fixed constant of any
sort, it would be a definite improvement for PostgreSQL to give some
sort of upper maximum
On 6/12/07, Josh Berkus [EMAIL PROTECTED] wrote:
Tom,
What's the point? You keep reminding us that your code is middleware
that can't assume anything much about the queries you're dealing with.
Therefore, I see no real value in fixing up one corner case. Your
argument about space
Josh Berkus [EMAIL PROTECTED] writes:
What's the point? You keep reminding us that your code is middleware
that can't assume anything much about the queries you're dealing with.
Hmmm? I thought that Dann was just talking about constants, and not column
results. Am I confused?
Well, the
Andrew Hammond [EMAIL PROTECTED] writes:
- Implicit casting of unknown to char(n) or anything else seems rather
sketchy to me, but I can't see any specific objection, except that...
- I don't know when the right time to do the cast is. And doing it too
early seems obviously wrong.
Well, I
Ühel kenal päeval, T, 2007-06-12 kell 13:40, kirjutas Larry McGhaw:
For what its worth .. Your statement about why we are the first people
to mention this problem really got me thinking. Anyone who would
attempt to write an ODBC driver for Postgres would run into the exact
same issue. So I
Just a curiosity question: Why is the type of a literal '1' unknown
instead of varchar(1)?
Wouldn't varchar(1) cast properly to any use of the literal '1'?
What is the benefit of assuming it's an unknown?
---(end of broadcast)---
TIP 7: You can
Chuck McDevitt [EMAIL PROTECTED] writes:
Just a curiosity question: Why is the type of a literal '1' unknown
instead of varchar(1)?
Because, for instance, it might be intended as an integer or float or
numeric value. Change the content a little, like '(1,2)' or '12:34',
and maybe it's a point
Hannu Krosing wrote:
Ühel kenal päeval, T, 2007-06-12 kell 13:40, kirjutas Larry McGhaw:
For what its worth .. Your statement about why we are the first people
to mention this problem really got me thinking. Anyone who would
attempt to write an ODBC driver for Postgres would run into the exact
SELECT 1 FROM test.dbo.a_003
gets about 60,000 records per second
SELECT '1' FROM test.dbo.a_003
gets about 600 records per second.
The cause is that postgres describes the return column as unknown
length 65534 in the 2nd case.
Since the value is a constant, it seems rather
Dann Corbit [EMAIL PROTECTED] writes:
SELECT 1 FROM test.dbo.a_003
gets about 60,000 records per second
SELECT '1' FROM test.dbo.a_003
gets about 600 records per second.
The cause is that postgres describes the return column as unknown
length 65534 in the 2nd case.
Postgres describes it
Dann Corbit [EMAIL PROTECTED] writes:
SELECT 1 FROM test.dbo.a_003
gets about 60,000 records per second
SELECT '1' FROM test.dbo.a_003
gets about 600 records per second.
The cause is that postgres describes the return column as unknown
length 65534 in the 2nd case.
Wait, back up.
-Original Message-
From: Gregory Stark [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 12:48 PM
To: Dann Corbit
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit [EMAIL PROTECTED] writes:
SELECT 1 FROM test.dbo.a_003
On Mon, Jun 11, 2007 at 12:55:55PM -0700, Dann Corbit wrote:
The issue is this:
Postgres describes the column with a typmod of -1 (unknown) and a length
of 65534.
Postgres does no such thing. How can it possibly know the maximum size
of a column before executing the query?
Have a nice day,
Dann Corbit [EMAIL PROTECTED] writes:
The issue is this:
Postgres describes the column with a typmod of -1 (unknown) and a length
of 65534.
Oh, you're looking at typlen not typmod. Please observe the comments in
pg_type.h:
/*
* For a fixed-size type, typlen is the number of
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 1:32 PM
To: Dann Corbit
Cc: Gregory Stark; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit [EMAIL PROTECTED] writes:
The issue
Tom Lane [EMAIL PROTECTED] writes:
Trying the example in psql seems to be about the same speed both ways, with
if anything a slight advantage to select '1'.
Fwiw I see a slight advantage for '1' as well. I wonder why.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
-Original Message-
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 1:46 PM
To: Dann Corbit
Subject: Re: [HACKERS] Selecting a constant question
On Mon, Jun 11, 2007 at 01:29:37PM -0700, Dann Corbit wrote:
Our application is using the libPQ
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Dann Corbit
Sent: Monday, June 11, 2007 1:52 PM
To: Martijn van Oosterhout
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
-Original Message
Dann Corbit [EMAIL PROTECTED] writes:
Surely, we cannot be the only people who will need this information. If
(for example) someone wants to bind to a grid, then the maximum size has
to be known in advance.
In fact psql needs it and implements this. It has to skim through the entire
result
-Original Message-
From: Gregory Stark [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 2:41 PM
To: Dann Corbit
Cc: Martijn van Oosterhout; pgsql-hackers@postgresql.org; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit [EMAIL PROTECTED] writes
Dann Corbit wrote:
Dann Corbit [EMAIL PROTECTED] writes:
In fact psql needs it and implements this. It has to skim through the
entire
result set to calculate the column widths. It's quite a lot of work
but
the
server is in no better position to do it than psql.
Reading the data
-Original Message-
From: Alvaro Herrera [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 3:16 PM
To: Dann Corbit
Cc: Gregory Stark; Martijn van Oosterhout;
pgsql-hackers@postgresql.org;
Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit wrote
Dann Corbit wrote:
Oh, you have the length information for each datum all right. It's on
the first four bytes of it.
Sure, but when I bind to a grid, I need to know a-priori how big the
biggest returned instance can be. Reading the entire data set twice to
learn the size of a constant
On Mon, Jun 11, 2007 at 03:18:33PM -0700, Dann Corbit wrote:
Sure, but when I bind to a grid, I need to know a-priori how big the
biggest returned instance can be. Reading the entire data set twice to
learn the size of a constant seems rather conceptually odd to me.
To be honest, the concept
Dann Corbit [EMAIL PROTECTED] writes:
Giving me the information about the data type will be enough. As an
example, in this case we have varchar data. If the server should be so
kind as to report varchar(1) for '1' or varchar(3) for '123' then I
would not have any difficulty binding the data
Dann Corbit wrote:
I have a PostgreSQL feature request:
Report the maximum size of a variable length string from the server.
Surely, we cannot be the only people who will need this information. If
(for example) someone wants to bind to a grid, then the maximum size has
to be known in
-Original Message-
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 3:29 PM
To: Dann Corbit
Cc: Alvaro Herrera; Gregory Stark; pgsql-hackers@postgresql.org; Larry
McGhaw
Subject: Re: [HACKERS] Selecting a constant question
On Mon, Jun 11, 2007
Dann Corbit wrote:
If the server bound the data as UNICODE, then it will tell me
UNICODE(3). I know how big this will be.
In the worst case scenario it will fit in 3*4 = 12 bytes.
If the server is built without UNICODE enabled, then it will definitely
fit in 3 bytes.
Unless it's some
Dann Corbit [EMAIL PROTECTED] writes:
To be honest, the concept that a widget requires a constant that can't
be changed later is also a bit odd.
Not when the data itself is a constant that cannot be changed.
Surely this case is not sufficiently important to justify designing
your entire
-Original Message-
From: Alvaro Herrera [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 3:44 PM
To: Dann Corbit
Cc: Tom Lane; Gregory Stark; Martijn van Oosterhout; pgsql-
[EMAIL PROTECTED]; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit wrote
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 3:50 PM
To: Dann Corbit
Cc: Martijn van Oosterhout; Alvaro Herrera; Gregory Stark; pgsql-
[EMAIL PROTECTED]; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit
; Gregory Stark; Martijn van Oosterhout;
pgsql-hackers@postgresql.org; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit wrote:
If the server bound the data as UNICODE, then it will tell me
UNICODE(3). I know how big this will be.
In the worst case scenario
; Gregory Stark; Martijn van Oosterhout;
pgsql-hackers@postgresql.org; Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit wrote:
If the server bound the data as UNICODE, then it will tell me
UNICODE(3). I know how big this will be.
In the worst case scenario
On Mon, 11 Jun 2007, Larry McGhaw wrote:
I think perhaps we have lost sight of the main issue:
2) libpq can properly describe the maximum internal data size of any
varchar column via Pqfmod
SELECT cola || colb FROM tab;
3) libpq can properly describe the maximum internal data size of any
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Kris Jurka
Sent: Monday, June 11, 2007 5:04 PM
To: Larry McGhaw
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
On Mon, 11 Jun 2007, Larry
Larry McGhaw wrote:
4) libpq **cannot** describe the maximum internal data size of a char or
varchar constant!
Example: select '123' from any table
This is clearly a bug or serious oversight in libpq that should be
addressed.
The database *knows* this size of the char constant (obviously),
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Andrew Dunstan
Sent: Monday, June 11, 2007 5:12 PM
To: Larry McGhaw
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Larry McGhaw wrote:
4
Larry McGhaw [EMAIL PROTECTED] writes:
I think perhaps we have lost sight of the main issue:
1) libpq can properly describe the maximum internal data size of any
numeric or char column in a table via Pqfsize
2) libpq can properly describe the maximum internal data size of any
varchar column
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 5:32 PM
To: Larry McGhaw
Cc: Alvaro Herrera; Dann Corbit; Gregory Stark; Martijn van
Oosterhout;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Larry McGhaw
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 3:35 PM
To: Dann Corbit
Cc: Gregory Stark; Martijn van Oosterhout;
pgsql-hackers@postgresql.org;
Larry McGhaw
Subject: Re: [HACKERS] Selecting a constant question
Dann Corbit [EMAIL PROTECTED
until it is
addressed.
Thanks
lm
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Mon 6/11/2007 5:32 PM
To: Larry McGhaw
Cc: Alvaro Herrera; Dann Corbit; Gregory Stark; Martijn van Oosterhout;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting
Ühel kenal päeval, E, 2007-06-11 kell 13:38, kirjutas Dann Corbit:
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 1:32 PM
To: Dann Corbit
Cc: Gregory Stark; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant
Ühel kenal päeval, E, 2007-06-11 kell 22:11, kirjutas Larry McGhaw:
As far as I am aware these statements are true. If you have a
specific example you could provide to the contrary that would be
interesting.
Even if there are such conditions it does not change the fact that
libpq and/or
-Original Message-
From: Hannu Krosing [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 8:42 PM
To: Dann Corbit
Cc: Tom Lane; Gregory Stark; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
Ühel kenal päeval, E, 2007-06-11 kell 13:38, kirjutas
-Original Message-
From: Hannu Krosing [mailto:[EMAIL PROTECTED]
Sent: Monday, June 11, 2007 10:43 PM
To: Larry McGhaw
Cc: Tom Lane; Alvaro Herrera; Dann Corbit; Gregory Stark; Martijn van
Oosterhout; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question
73 matches
Mail list logo