pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with PQfmod() returning -1 on varchar field (libpq-8.0.0 )?
7+ messages / 4 participants
[nested] [flat]
* Problems with PQfmod() returning -1 on varchar field (libpq-8.0.0 )?
@ 2005-04-02 00:21 Jerry Day <jerry_day@esri.com>
0 siblings, 2 replies; 7+ messages in thread
From: Jerry Day @ 2005-04-02 00:21 UTC (permalink / raw)
To: pgsql-interfaces
Hi all,
I've recently encountered a situation where PQfmod() fails to return the
defined length of a varchar field referenced in a query, as expected -
returning no information (-1), instead. Has anyone else encountered this
issue? If so, why does this occur? Can you recommended a method to more
reliably obtain the length of varchar fields, as defined in the relations
referenced by a query?
Thanks,
Jerry L. Day
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with PQfmod() returning -1 on varchar field
@ 2005-04-03 01:26 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jerry Day <jerry_day@esri.com>
1 sibling, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2005-04-03 01:26 UTC (permalink / raw)
To: Jerry Day <jerry_day@esri.com>; +Cc: pgsql-interfaces
Jerry Day wrote:
> Hi all,
>
> I've recently encountered a situation where PQfmod() fails to return the
> defined length of a varchar field referenced in a query, as expected -
> returning no information (-1), instead. Has anyone else encountered this
> issue? If so, why does this occur? Can you recommended a method to more
> reliably obtain the length of varchar fields, as defined in the relations
> referenced by a query?
Can we see the query? I suspect the column is being manipulated in some
way and the length isn't carried through to the query output --- if so,
there might not be much we can do about 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with PQfmod() returning -1 on varchar field (libpq-8.0.0 )?
@ 2005-04-03 01:56 Michael Fuhr <mike@fuhr.org>
parent: Jerry Day <jerry_day@esri.com>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-04-03 01:56 UTC (permalink / raw)
To: Jerry Day <jerry_day@esri.com>; +Cc: pgsql-interfaces
On Fri, Apr 01, 2005 at 04:21:03PM -0800, Jerry Day wrote:
>
> I've recently encountered a situation where PQfmod() fails to return the
> defined length of a varchar field referenced in a query, as expected -
> returning no information (-1), instead. Has anyone else encountered this
> issue? If so, why does this occur? Can you recommended a method to more
> reliably obtain the length of varchar fields, as defined in the relations
> referenced by a query?
Are you sure the column has a length specifier? What does "\d tablename"
show, or what's the result of the following query?
SELECT attname, attnum, atttypid::regtype, atttypmod
FROM pg_attribute
WHERE attrelid = 'tablename'::regclass
AND attnum >= 1
AND attisdropped IS FALSE
ORDER by attnum;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with PQfmod() returning -1 on varchar f
@ 2005-04-04 17:54 Jerry Day <jerry_day@esri.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jerry Day @ 2005-04-04 17:54 UTC (permalink / raw)
To: 'Bruce Momjian' <pgman@candle.pha.pa.us>; Jerry Day <jerry_day@esri.com>; +Cc: pgsql-interfaces
Bruce,
Thanks for your quick response. The column in question is defined as 'DATA
VARCHAR(20) NOT NULL'. The issue occurs when this is referenced in a fairly
complex query, such as:
SELECT data,int16_col,int64_col,float32_col,float64_col,
date_col,row_id
FROM
(SELECT b.data,b.int16_col,b.int64_col,
b.float3_col,b.float64_col,b.date_col,b.row_id
FROM table1 b
LEFT JOIN
(SELECT *
FROM table3
) d ON b.ROW_ID = d.ROW_ID
WHERE d.ROW_ID IS NULL
UNION ALL
SELECT a.data,a.int16_col,a.int64_col,a.float32_col,
a.float6_col,a.date_col,a.row_id
FROM table2 a
LEFT JOIN
(SELECT *
FROM table3
) d ON (a.ROW_ID = d.ROW_ID)
WHERE d.ROW_ID IS NULL
) V__282 LIMIT 0;
Where:
CREATE TABLE table1
(
data varchar(20) NOT NULL,
int16_col int2,
int64_col int8,
float32_col float4,
float64_col float8,
date_col timestamp,
row_id int4 NOT NULL,
CONSTRAINT table1_pk PRIMARY KEY (row_id)
)
WITHOUT OIDS;
CREATE TABLE table2
(
data varchar(20) NOT NULL,
int16_col int2,
int64_col int8,
float32_col float4,
float64_col float8,
date_col timestamp,
row_id int4 NOT NULL,
CONSTRAINT table2_pk PRIMARY KEY (row_id)
)
WITHOUT OIDS;
CREATE TABLE table3
(
row_id int8 NOT NULL,
int32_col int4 NOT NULL,
int64_col int8 NOT NULL,
CONSTRAINT table3_pk PRIMARY KEY (row_id)
)
WITHOUT OIDS;
In this particular case, I can go back and describe each of the referenced
tables, but there are other times in our application where this is not
practical. Can you recommend a more reliable method to describe such
queries? Is this a current limitation in Postgres which may be addressed at
a future release?
Thanks,
Jerry
_|_|
_|_| ESRI
Jerry L. Day
Software Developer
(909)793-2853 Ext. 1477
jerry_day@esri.com
-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Saturday, April 02, 2005 5:27 PM
To: Jerry Day
Cc: 'pgsql-interfaces@postgresql.org'
Subject: Re: [INTERFACES] Problems with PQfmod() returning -1 on varchar
field (libpq-8.0.0 )?
Jerry Day wrote:
> Hi all,
>
> I've recently encountered a situation where PQfmod() fails to return the
> defined length of a varchar field referenced in a query, as expected -
> returning no information (-1), instead. Has anyone else encountered this
> issue? If so, why does this occur? Can you recommended a method to more
> reliably obtain the length of varchar fields, as defined in the relations
> referenced by a query?
Can we see the query? I suspect the column is being manipulated in some
way and the length isn't carried through to the query output --- if so,
there might not be much we can do about 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with PQfmod() returning -1 on varchar f
@ 2005-04-04 18:00 Jerry Day <jerry_day@esri.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jerry Day @ 2005-04-04 18:00 UTC (permalink / raw)
To: 'Michael Fuhr' <mike@fuhr.org>; Jerry Day <jerry_day@esri.com>; +Cc: pgsql-interfaces
Michael,
The relevant column is defined as 'DATA VARCHAR(20) NOT NULL'. Query
attname, attnum, atttypid, atttypmod returns:
"data";1;"character varying";24
"int16_col";2;"smallint";-1
"int64_col";3;"bigint";-1
"float32_col";4;"real";-1
"float64_col";5;"double precision";-1
"date_col";6;"timestamp without time zone";-1
"row_id";7;"integer";-1
Thanks,
Jerry
_|_|
_|_| ESRI
Jerry L. Day
Software Developer
(909)793-2853 Ext. 1477
jerry_day@esri.com
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Saturday, April 02, 2005 5:56 PM
To: Jerry Day
Cc: 'pgsql-interfaces@postgresql.org'
Subject: Re: [INTERFACES] Problems with PQfmod() returning -1 on varchar
field (libpq-8.0.0 )?
On Fri, Apr 01, 2005 at 04:21:03PM -0800, Jerry Day wrote:
>
> I've recently encountered a situation where PQfmod() fails to return the
> defined length of a varchar field referenced in a query, as expected -
> returning no information (-1), instead. Has anyone else encountered this
> issue? If so, why does this occur? Can you recommended a method to more
> reliably obtain the length of varchar fields, as defined in the relations
> referenced by a query?
Are you sure the column has a length specifier? What does "\d tablename"
show, or what's the result of the following query?
SELECT attname, attnum, atttypid::regtype, atttypmod
FROM pg_attribute
WHERE attrelid = 'tablename'::regclass
AND attnum >= 1
AND attisdropped IS FALSE
ORDER by attnum;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with PQfmod() returning -1 on varchar f
@ 2005-04-05 01:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jerry Day <jerry_day@esri.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2005-04-05 01:43 UTC (permalink / raw)
To: Jerry Day <jerry_day@esri.com>; +Cc: 'Bruce Momjian' <pgman@candle.pha.pa.us>; pgsql-interfaces
Jerry Day <jerry_day@esri.com> writes:
> Thanks for your quick response. The column in question is defined as 'DATA
> VARCHAR(20) NOT NULL'. The issue occurs when this is referenced in a fairly
> complex query, such as:
> SELECT data,int16_col,int64_col,float32_col,float64_col,
> date_col,row_id
> FROM
> (SELECT b.data,b.int16_col,b.int64_col,
> b.float3_col,b.float64_col,b.date_col,b.row_id
> FROM table1 b
> LEFT JOIN
> (SELECT *
> FROM table3
> ) d ON b.ROW_ID = d.ROW_ID
> WHERE d.ROW_ID IS NULL
> UNION ALL
> SELECT a.data,a.int16_col,a.int64_col,a.float32_col,
> a.float6_col,a.date_col,a.row_id
> FROM table2 a
> LEFT JOIN
> (SELECT *
> FROM table3
> ) d ON (a.ROW_ID = d.ROW_ID)
> WHERE d.ROW_ID IS NULL
> ) V__282 LIMIT 0;
The UNION will drop the typmod, even if both its inputs have the same
typmod. See e.g.
http://archives.postgresql.org/pgsql-general/2005-04/msg00204.php
This is on the agenda to look at for 8.1 ...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with PQfmod() returning -1 on varchar f
@ 2005-04-10 04:28 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2005-04-10 04:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jerry Day <jerry_day@esri.com>; pgsql-interfaces
Tom Lane wrote:
> > SELECT a.data,a.int16_col,a.int64_col,a.float32_col,
> > a.float6_col,a.date_col,a.row_id
> > FROM table2 a
> > LEFT JOIN
> > (SELECT *
> > FROM table3
> > ) d ON (a.ROW_ID = d.ROW_ID)
> > WHERE d.ROW_ID IS NULL
> > ) V__282 LIMIT 0;
>
> The UNION will drop the typmod, even if both its inputs have the same
> typmod. See e.g.
> http://archives.postgresql.org/pgsql-general/2005-04/msg00204.php
>
> This is on the agenda to look at for 8.1 ...
This is not on the community TODO? Is it on yours? Should it be on the
communties?
--
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-04-10 04:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-02 00:21 Problems with PQfmod() returning -1 on varchar field (libpq-8.0.0 )? Jerry Day <jerry_day@esri.com>
2005-04-03 01:26 ` Re: Problems with PQfmod() returning -1 on varchar field Bruce Momjian <pgman@candle.pha.pa.us>
2005-04-03 01:56 ` Michael Fuhr <mike@fuhr.org>
2005-04-04 17:54 Re: Problems with PQfmod() returning -1 on varchar f Jerry Day <jerry_day@esri.com>
2005-04-05 01:43 ` Re: Problems with PQfmod() returning -1 on varchar f Tom Lane <tgl@sss.pgh.pa.us>
2005-04-10 04:28 ` Re: Problems with PQfmod() returning -1 on varchar f Bruce Momjian <pgman@candle.pha.pa.us>
2005-04-04 18:00 Re: Problems with PQfmod() returning -1 on varchar f Jerry Day <jerry_day@esri.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox