Re: [BUGS] union bug
Hello,
>> CREATE DOMAIN test_domain
>> AS varchar(64)
>> NOT NULL;
>> CREATE TYPE test_type AS
>>("Id" int4,
>> "Data" test_domain);
>> CREATE OR REPLACE FUNCTION union_test()
>> RETURNS SETOF test_type AS
>> $BODY$
>> select 1 as "Id", 'string1'::test_domain as "Data"
>> union all
>> select 2 as "Id", 'string2'::test_domain as "Data"
>> $BODY$
>> LANGUAGE 'sql' VOLATILE;
>> generates error message
>> ERROR: return type mismatch in function declared to return test_type
>> DETAIL: Final SELECT returns character varying instead of test_domain at
>> column 2.
>> CONTEXT: SQL function "union_test"
TL> The reason this happens is that select_common_type() smashes all its
TL> inputs down to base types. I'm a bit hesitant to change this behavior
TL> without thinking about all the possible consequences. There are clearly
TL> some cases where it's the right thing --- for instance, if the inputs
TL> are two different domains over the same base type, selecting the base
TL> type seems the most reasonable behavior. Also, at least some of the
TL> routine's callers seem to be relying on the assumption that the result
TL> won't be a domain type.
I'd like to offer following solution:
for given column of the union check if the types of all parts ot the
union for that column are !exactly! the same, then resulting column
type of the union is left to that type, otherwise it casts to the base
type.
In this case users can explicitly cast column types of union parts to
whatever they want to get that type in the result, i.e.
CREATE DOMAIN test_domain
AS varchar(64)
NOT NULL;
CREATE DOMAIN test_domain2
AS varchar(64)
NOT NULL
CHECK (length(trim(value)) > 0);
CREATE TYPE test_type AS
("Id" int4,
"Data" test_domain);
CREATE OR REPLACE FUNCTION union_test()
RETURNS SETOF test_type AS
$BODY$
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
$BODY$
LANGUAGE 'sql' VOLATILE;
If i understand correctly current workaround is to use outer select
with type cast (as i note in previous message). But as i see it takes
extra processing (as query plans below shows)
select "Id", "Data"::test_domain from
(
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
) as q1;
Subquery Scan q1 (cost=0.00..0.07 rows=2 width=36)
-> Append (cost=0.00..0.04 rows=2 width=0)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
Append (cost=0.00..0.04 rows=2 width=0)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
Thank you for support.
--
Best regards,
Ivanmailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[BUGS] BUG #1978: connection sinks
The following bug has been logged online:
Bug reference: 1978
Logged by: mouse
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system: Gentoo Linux 2.6.11-r9
Description:connection sinks
Details:
I have: (2) user X which can create databases and (2) database Y created by
X. Y contains some tables, sequences, etc -- data CAN be accessed via psql.
Also: have googled a lot for a proper configuration, thus postgresql.conf
has listen_addresses listening everything ('*') and in pg_hba.conf there is
an entry for my intranet IP address: 192.168.1.0/24 (for any eventuality).
Problem: when using Quantum GIS (http://qgis.org) to connect postgis
database Y, it reports that connection is successful. But, data are not
loaded!
Guide: Same thing (user X, db Y, same data) are on the online www.xyz.com
postgres database running) -- in this case I CAN see layers (tables) in the
qGIS app and load them. Remote configuration is the same adding some
additinal hosts in pg_hba...
Question: what could that be?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #1978: connection sinks
mouse wrote:
The following bug has been logged online:
Bug reference: 1978
Logged by: mouse
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system: Gentoo Linux 2.6.11-r9
Description:connection sinks
Details:
Make sure you have logging turned on, connection attempts and statements
and try and connect. See what the logs say. Post details to the
pgsql-general list, this is almost certainly not a bug in PostgreSQL.
I have: (2) user X which can create databases and (2) database Y created by
X. Y contains some tables, sequences, etc -- data CAN be accessed via psql.
Also: have googled a lot for a proper configuration, thus postgresql.conf
has listen_addresses listening everything ('*') and in pg_hba.conf there is
an entry for my intranet IP address: 192.168.1.0/24 (for any eventuality).
Problem: when using Quantum GIS (http://qgis.org) to connect postgis
database Y, it reports that connection is successful. But, data are not
loaded!
You fail to mention what happens when you connect from the qgis machine
when you use psql. That's another bit of information to post to
pgsql-general too.
Guide: Same thing (user X, db Y, same data) are on the online www.xyz.com
postgres database running) -- in this case I CAN see layers (tables) in the
qGIS app and load them. Remote configuration is the same adding some
additinal hosts in pg_hba...
Question: what could that be?
Something is different between the two machines. Can't say what from the
information you've given here. Check the logging and come over to
-general and I'm sure someone can get you connected.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1962: ECPG and VARCHAR
Am Freitag, 14. Oktober 2005 21:15 schrieb C Wegrzyn: > The customer I am writing this for will only accept it based on released > code. That means I run either 8.0.3 or 8.0.4. Would it help you if I do a special ecpg release just for you? Or does it have to be part of the postgres release? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] SIGSEGV in Postgres 8.0.3 (libpq4)
Anand Kumria <[EMAIL PROTECTED]> writes: > I have a set of perl scripts which invoke each other (via system()); > eventually I found that they were crashing and ultimately causing Perl > to SIGSEGV. Are you doing anything multi-threaded in those scripts? libpq is not thread-aware --- it's up to you to ensure that only one thread touches a given PG connection (only one at a time anyway). That might have nothing to do with your problem, but no other theories come to mind with the limited info you've provided... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function
Tom Lane wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> writes: And you might want to make it a project at http://pgfoundry.org so others can make use of it. You might also want to define it as accepting an array; I think that would allow you to accept any number of parameters. I think Tony is trying to avoid putting in any actual work ;-). And that he wants to write queries that work on Oracle, MySQL, and PostgreSQL at the same time. One point I would make is that although || might appear to break MySQL at the moment, you can set the operator to be concat for the application (maybe in the function that connects to the DB?) Best Wishes Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function
Tony Marston wrote: No, but Oracle does, which is why I am trying to produce SQL statements that will run on MySQL, PostgreSQL and Oracle without the need for conversion. Hi Tony, Let me make a constructive suggestion. I see what you are trying to do and I can understand why this is useful. However, I agree with the main individuals here that it should not be a part of the core project. Fortunately PostgreSQL is extensible and it is quite easy to release custom extensions that can make these things happen without messing with the core project. One of the things I am going to be doing is creating either a Perl (client) or PLPGSQL (server) function to create concat() functions with up to x number of arguments. This will be used as part of our server-side porting framework to allow for easier migration from MySQL in particular. Would you be interested in participating in/testing/contributing to such a project? Best Wishes, Chris Travers Metatron Technology Consulting Tony Marston http://www.tonymarston.net -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: 10 October 2005 18:19 To: [EMAIL PROTECTED] Cc: Bruce Momjian; 'David Fetter'; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function PostgreSQL runs on machines that use EBCDIC? On Mon, Oct 10, 2005 at 04:26:15PM +0100, [EMAIL PROTECTED] wrote: Here is a direct quote from the ORACLE manual: On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets. Tony Marston http://www.tonymarston.net pgman@candle.pha.pa.us wrote: Tony Marston wrote: which Oracle supports and MySQL can be made to support via a runtime option. They also both support CONCAT() because there are sometimes difficulties in dealing with vertical bars in the character sets used by certain operating systems and file systems. If enough database vendors offer it then it becmes a "de facto" standard. I have never heard of problems with vertical bars in any of those settings. Can you elaborate? I don't see how operating systems and file system character sets relate to SQL query characters. -- 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: don't forget to increase your free space map settings -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] function currval(character varying) does not exist, PostgreSQL 8.1 beta3
Hi,
pstmt =
connection.prepareStatement("select currval(?)"); throws "function
currval(character varying) does not exist"
Using pgAdmin, I could see that
currval() now takes a regclass parameter, so
I got it to work using
pstmt =
connection.prepareStatement("select currval(cast(cast(? as text) as
regclass))");
Is currval(text) gone for good or is
this an oversight?
If it's gone for good, is cast(cast(?
as text) as regclass) the recommended way of handling this?
I am using PostgreSQL 8.1 beta3
under Windows XP
Service Pack 2 with JDBC3 8.1 Build
402.
It worked fine with PostgreSQL 8.1
beta2 and with 8.0.*
Thanks,
Jean-Pierre Pelletier
e-djuster
Re: [BUGS] function currval(character varying) does not exist, PostgreSQL 8.1 beta3
"Jean-Pierre Pelletier" <[EMAIL PROTECTED]> writes:
>pstmt = connection.prepareStatement("select currval(?)"); throws =
> "function currval(character varying) does not exist"
>Is currval(text) gone for good or is this an oversight?
currval(text) is gone for good; the oversight is that we stuck in an
implicit cast from text to regclass, but not one from varchar to
regclass (and the lookup is stupid, there has to be an exact match in
pg_cast).
I think we probably should add an implicit varchar cast. To completely
duplicate the cases in which previous versions would work quietly,
we might need casts from bpchar and name as well, but I'm less excited
about adding those (especially since they would require additional
actual functions, rather than just one more row in pg_cast).
Comments anyone? Do we need to force initdb for this (I suppose so if
we want to be perfectly clean :-()
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
