[GENERAL] Transform_Null_Equals does not work in Functions

[Daniel Schuchardt]

Hy Group,

we use:
 PostgreSQL 9.0alpha4, compiled by Visual C++ build 1400, 32-bit

and i tried to set Transform_null_equals in a Trigger to avoid a complex 
If Statement with many Coalesce, but it didnt work. You can try it 
easily with that example:



CREATE OR REPLACE FUNCTION show_transform_problem(with_transform BOOL) 
RETURNS BOOL AS $$

DECLARE result BOOL;
BEGIN
 IF with_transform THEN
 SET transform_null_equals TO ON;
 END IF;
 RESULT:=NULL=1;
 SET transform_null_equals TO OFF;
 RETURN result;
END $$ LANGUAGE plpgsql;


SUNFLOWER=# SELECT show_transform_problem(false);
 show_transform_problem


(1 row)


SUNFLOWER=# SELECT show_transform_problem(true);
 show_transform_problem


(1 row)


SUNFLOWER=# SET transform_null_equals TO ON;
SET
SUNFLOWER=# SELECT null=1;
 ?column?
--
 f
(1 row)


--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

[GENERAL] dynamically access columns in trigger

[Daniel Schuchardt]

Hy,

i need to have some idea how to dynamically access columns in a trigger.

Use/Case:

We have a workflowmodule. In different cases different workflows should 
be fired. In the Workflowdefinitiontable, we say "tablename, when". so e.g.

~~table 1 -> insert~~
~~table 2 -> update~~.

We have a central trigger that fires on all tables. Inside the trigger i 
evaluate with "TG_REL_NAME=wf_tablename" -> start workflow.


Now the problem is that there are different workflows in the update 
case. e.g. if the price changes, a "CheckNewPrice" workflow is startet, 
if a date changes a "CheckNewDeliveryDate" workflow is started.


My idea is to give my workflowdefinitiontable a additional column the 
holds the condition, e.g.

~~table 2 -> update -> ~ new.pricecolumn<>old.pricecolumn ~~~
so i need a solution how to evaluate that dynamic statement in my global 
trigger funktion.


Any ideas?

--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] JOIN Record returning Function

[Daniel Schuchardt]

Hy Group,

i have a function that returns a record.

myfunc(IN id INTEGER) RETURNS RECORD.

in that function the record is build from some subquery's in dependence 
of data.


Now i need to join that function to its correponding main table that 
holds the id.


SELECT myfunc.* FROM maintable JOIN myfunc(maintable.pk) ON true WHERE 
maintable.field=statisticdata;


ERROR:  invalid reference to FROM-clause entry for table "maintable"
TIP:  There is an entry for table "maintable", but it cannot be 
referenced from this part of the query.


so far so good, thats clear. But does anyone know a tricky solution for 
that problem?

--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Daniel Schuchardt]

Ok, thats fine for us.

Most of our customers run Debian in 64Bit mode with Postgres 81 64 Bit, 
but some won't use a *nix.


Thanks.


Am 13.01.2010 18:49, schrieb Joshua D. Drake:
   

we plan to upgrade to 8.4 the next time but currently everything is
build with 8.1. thanks for your awnsers, so we wont try it.
but i think we will wait till 8.5 because of the 64 bit problem.

we did not upgrade because 81 has autocast and later version doesn't,
so we need to check all our statements and triggers/stored procedures
wich is currently in work.

 

PostgreSQL 8.3/8.4/8.5 will run just fine on 64bit Windows. Just
understand that PostgreSQL will be running in 32bit mode, which frankly
on windows is not a problem compared to other platforms.

Joshua D. Drake



   



--
Mit freundlichen Grüssen,

Daniel Schuchardt
/Softwareentwicklung/

*CIMPCS GmbH
*Grünewaldstrasse 19
D-99099 Erfurt

TelefonFaxMobil
0049 361 65347180049 361 65347170049 172 7766971

Firmensitz : Erfurt
Geschäftsführer : Herbert Weber; Heinrich Kühni
Registriert : Amtsgericht Erfurt, HRB 501091
USt-IdNr : DE252754686

www.prodat-sql.de <http://www.prodat-sql.de>

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Daniel Schuchardt]

Am 13.01.2010 16:00, schrieb Craig Ringer:

On 13/01/2010 6:15 PM, Daniel Schuchardt wrote:

Hy,

can anybody give us a hint if we can use that combination?


You can use libpq on 64-bit windows to talk to an 8.1 database if you 
really must.


I really wouldn't recommend running the 8.1 database on windows. Win32 
releases of Pg see big improvements with every version and 8.1 is 
really not recommended or supported anymore. 8.1 was ... deficient ... 
on Windows.


Run a recent Pg server on 64-bit windows, or run your 8.1 server on a 
UNIX machine and connect to it from 64-bit windows using a modern 
64-bit libpq.


Why 8.1, anyway?

--
Craig Ringer


we plan to upgrade to 8.4 the next time but currently everything is 
build with 8.1. thanks for your awnsers, so we wont try it.

but i think we will wait till 8.5 because of the 64 bit problem.

we did not upgrade because 81 has autocast and later version doesn't, so 
we need to check all our statements and triggers/stored procedures wich 
is currently in work.


--
Mit freundlichen Grüssen,

Daniel Schuchardt
/Softwareentwicklung/

*CIMPCS GmbH
*Grünewaldstrasse 19
D-99099 Erfurt

TelefonFaxMobil
0049 361 65347180049 361 65347170049 172 7766971

Firmensitz : Erfurt
Geschäftsführer : Herbert Weber; Heinrich Kühni
Registriert : Amtsgericht Erfurt, HRB 501091
USt-IdNr : DE252754686

www.prodat-sql.de <http://www.prodat-sql.de>

[GENERAL] postgresql 8.1 windows 2008 64 bit

[Daniel Schuchardt]

Hy,

can anybody give us a hint if we can use that combination?

Thanks,

Daniel.
--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] crosstab function - pivot - use column value as column description

[Daniel Schuchardt]

See bottom.

Am 12.01.2010 22:00, schrieb Merlin Moncure:

On Tue, Jan 12, 2010 at 3:27 PM, Daniel Schuchardt
  wrote:
   

Hy,

i'm looking for a solution to get this table dynamicaly in that resultset:

SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM
recnokeyword WHERE r_dbrid=X
  r_dbrid  | r_kategorie |  r_descr
--+-+
  14725737 |material | Alu Sorte1
  14725737 |farbe | Blau
  14725737 |gewicht | 100 kg

>

material | farbe | gewicht
--+-+
alu sorte 1|blau | 100kg

>  the number of columns depends on data.

is there is a simple solution for that?
 

have you ruled out the appropriate contrib module?

http://www.postgresql.org/docs/8.4/static/tablefunc.html

merlin

   


yes, but that function requires a return type -> so i has to know how 
many columns. also the column description ist defined by data but by 
return type.


(

SELECT * FROM crosstab('...', '...')
  AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 
text);
   


This will produce a result something like:

  <==  value  columns   ==>
   row_name   extra   cat1   cat2   cat3   cat4

)

Daniel
--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] crosstab function - pivot - use column value as column description

[Daniel Schuchardt]

Hy,

i'm looking for a solution to get this table dynamicaly in that resultset:

SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM 
recnokeyword WHERE r_dbrid=X

 r_dbrid  | r_kategorie |  r_descr
--+-+
 14725737 |material | Alu Sorte1
 14725737 |farbe | Blau
 14725737 |gewicht | 100 kg

>

material | farbe | gewicht
--+-+
alu sorte 1|blau | 100kg

> the number of columns depends on data.

is there is a simple solution for that?
--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Access a Field / Column of a resultset by Number

[Daniel Schuchardt]

hy scott,  thanks for your awnser.

yes thats clear. but i can't find any sourcecode. the pl* language 
doesnt matter, i need that feature only in one function all over my db.


did you have any sourcecode examples?

http://www.postgresql.org/docs/8.4/interactive/plperl-database.html

nothing about how to access a column by fieldname.

daniel

Scott Marlowe schrieb:

On Fri, Nov 27, 2009 at 10:09 AM, Daniel Schuchardt
 wrote:

thats exactly the same i'm looking for:

http://wiki.postgresql.org/wiki/Todo
http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php

(todo list for plpgsql)

*Server-Side Languages
*PL/pgSQL
*
*[D] Allow listing of record column names, and access to record columns *via
variables, e.g. columns := r.(*), tval2 := r.(colname)
*
*Re: PL/PGSQL: Dynamic Record Introspection


is that is possible in any pl* language?


Yes, if the language has the architecture to handle it.  plpgsql
doesn't right now.  pltcl, plperl, and plain old C functions can
examine records and do dynamic stuff with them.  Any attempt at doing
dynamic queries right now in plpgsql leads to madness, or so I've been
told.




--
Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Access a Field / Column of a resultset by Number

[Daniel Schuchardt]

hy group,

i currently look for a solution to access a resultset in a db-stored 
function by number. in plpgsql thats not possible.

so i checked out plpython. so far so good, thats working:

CREATE OR REPLACE FUNCTION dokv_dorecnokeywords(sqlstatement VARCHAR) 
RETURNS VOID AS

$$
 rv = plpy.execute(sqlstatement, 1)
 |->"SELECT * FROM art WHERE ak_nr='TEST'"
 s  = 'ak_bez'   fieldname is static here, should be by number
 plpy.notice(s+'='+rv[0][s])
 return
$$ LANGUAGE plpythonu;

now i need a solution to get the number of fields as well as the 
fieldnames of that resultset and run through all fields (by number or by 
name). (i need the fieldname too)


--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Access a Field / Column of a resultset by Number

[Daniel Schuchardt]

thats exactly the same i'm looking for:

http://wiki.postgresql.org/wiki/Todo
http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php

(todo list for plpgsql)

*Server-Side Languages
*PL/pgSQL
*
*[D] Allow listing of record column names, and access to record columns 
*via variables, e.g. columns := r.(*), tval2 := r.(colname)

*
*Re: PL/PGSQL: Dynamic Record Introspection


is that is possible in any pl* language?




Daniel Schuchardt schrieb:

hy group,

i currently look for a solution to access a resultset in a db-stored 
function by number. in plpgsql thats not possible.

so i checked out plpython. so far so good, thats working:

CREATE OR REPLACE FUNCTION dokv_dorecnokeywords(sqlstatement VARCHAR) 
RETURNS VOID AS

$$
 rv = plpy.execute(sqlstatement, 1)
 |->"SELECT * FROM art WHERE ak_nr='TEST'"
 s  = 'ak_bez'   fieldname is static here, should be by number
 plpy.notice(s+'='+rv[0][s])
 return
$$ LANGUAGE plpythonu;

now i need a solution to get the number of fields as well as the 
fieldnames of that resultset and run through all fields (by number or by 
name). (i need the fieldname too)





--
Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] invalid byte sequence for encoding

[Daniel Schuchardt]

Yes, you'r correct with the \0 at the end. The problem is that the 
rtf-object returns wrong terminated string. i can fix the problem with a 
trim.


but look here:

X=# UPDATE art SET ak_auftxt= '*', ak_auftxt_rtf= 
'{\\rtf1\\ansi\\deff0{\\fonttbl{\\f0\\fnil\\fcharset0 
Arial;}}\r\n\\viewkind4\\uc1\\pard\\lang1031\\fs20 *

\r\n\\par }\r\n\0' WHERE ak_nr='TEST';
WARNING:  nonstandard use of \\ in a string literal at character 47
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
ERROR:  invalid byte sequence for encoding "SQL_ASCII": 0x00
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

=# SELECT version();
   version
-
 PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit

#

=# UPDATE art SET ak_auftxt= '*', ak_auftxt_rtf= 
'{\\rtf1\\ansi\\deff0{\\fonttbl{\\f0\\fnil\\fcharset0 
Arial;}}\r\n\\viewkind4\\uc1\\pard\\lang1031\\fs20 *\

r\n\\par }\r\n\0' WHERE ak_nr='TEST';
UPDATE 1
=# SELECT version();
version


 PostgreSQL 8.1.11 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 
20061115

 (prerelease) (Debian 4.1.1-21)
(1 row)

Scott Ribe schrieb:

So its not possible thats our parser.


And


Second:string:Not really: thats the orignal string, and its a string:


Look again. Where is the null character in the original string? Why does
your encoded string end with "\0"? In what character set is null a legal
character?

Your encoder is incorrect.




--
Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] invalid byte sequence for encoding

[Daniel Schuchardt]

Hi Thomas,

thanks a lot we will check out that parameter. But if i understand it in 
 the correct way that parameter will turn off all escape quoting.


I have to check out,

thanks a lot.

Thomas Kellerer schrieb:

Daniel Schuchardt wrote on 13.09.2009 18:51:
UPDATE belzeil_frei SET bz_zubez= '*', bz_zubez_rtf= 
'{\\rtf1\\ansi\\deff0{\\fonttbl{\\f0\\fnil\\fcharset0 
Arial;}}\r\n\\viewkind4\\uc1\\pard\\lang1031\\fs20 *\r\n\\par }\r\n\0' 
WHERE dbrid=295116


Result : ERROR:  invalid byte sequence for encoding "WIN1252": 0x00

In that example i try to insert a "*" with rtf-encoding.

i have the same problem with SQL_ASCII



Sounds to me as if you need to set standard_conforming_strings to true 
in the postgresql.conf


http://www.postgresql.org/docs/8.4/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS 



Thomas





--
Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] invalid byte sequence for encoding

[Daniel Schuchardt]

I know you are true with definition's and standards, however, that code 
works for about 6 years ;o)


Well, we will change our parser behavoir. We will check out that 
standard_conforming_strings parameter too but i see a lot of problems 
with our backup and restore system (plain text pg_dump's) and other points.


All in all will Postgres81 understand this E'' (\\0) escape chars too? 
Or do we have to make a several version for older Postgres versions?


Thanks a lot for your hints,

Daniel.

Peter Eisentraut schrieb:

On sön, 2009-09-13 at 22:21 +0200, Daniel Schuchardt wrote:

First:In Postgres81 everything is working fine.


In general, older versions of PostgreSQL treated encoding issues much
mroe loosely, which subsequently lead to user errors, bugs, and
confusion.  Later versions are more strict.  Therefore, experience
dictates that "$oldversion is working fine" often really means "your
application code was abusing definitional gaps and bugs".


(((with our parser:
UPDATE art SET ak_auftxt= '*', ak_auftxt_rtf= 
'{\\rtf1\\ansi\\deff0{\\fonttbl{\\f0\\fnil\\fcharset0 
Arial;}}\r\n\\viewkind4\\uc1\\pard\\lang1031\\fs20 *\r\n\\par }\r\n\0' 
WHERE dbrid=204800




At the very least, you should escape the \0 to \\0.  And then put E''
around the string.  The answer recommended elsewhere to set
standard_conforming_strings to true will also work, but might break
other code that you have currently running.  Read its documentation
carefully.





--
Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] invalid byte sequence for encoding

[Daniel Schuchardt]

First:In Postgres81 everything is working fine.

Second:string:Not really: thats the orignal string, and its a string: 
(http://de.wikipedia.org/wiki/Rich_Text_Format)

(http://en.wikipedia.org/wiki/Rich_Text_Format)


{\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0 Arial;}}
\viewkind4\uc1\pard\lang1031\fs20 *
\par }

you can save it in a textfile and open it with MS-Word or OpenOffice.


without our own parser its looking in that way:


UPDATE art SET ak_auftxt= '*', ak_auftxt_rtf= 
'{\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0 Arial;}}

\viewkind4\uc1\pard\lang1031\fs20 *
\par } ' WHERE dbrid=204800

(((with our parser:
UPDATE art SET ak_auftxt= '*', ak_auftxt_rtf= 
'{\\rtf1\\ansi\\deff0{\\fonttbl{\\f0\\fnil\\fcharset0 
Arial;}}\r\n\\viewkind4\\uc1\\pard\\lang1031\\fs20 *\r\n\\par }\r\n\0' 
WHERE dbrid=204800



WARNING:  nonstandard use of escape in a string literal
LINE 1: UPDATE art SET ak_auftxt= '*', ak_auftxt_rtf= '{\rtf1\ansi\d...
  ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
Abfrage war erfolgreich durchgeführt: 1 Zeile, 203 ms Ausführungszeit.



Peter Eisentraut schrieb:

On sön, 2009-09-13 at 18:51 +0200, Daniel Schuchardt wrote:
UPDATE belzeil_frei SET bz_zubez= '*', bz_zubez_rtf= 
'{\\rtf1\\ansi\\deff0{\\fonttbl{\\f0\\fnil\\fcharset0 
Arial;}}\r\n\\viewkind4\\uc1\\pard\\lang1031\\fs20 *\r\n\\par }\r\n\0' 
WHERE dbrid=295116


Result : ERROR:  invalid byte sequence for encoding "WIN1252": 0x00

In that example i try to insert a "*" with rtf-encoding.

i have the same problem with SQL_ASCII


Maybe you want to use the bytea type instead, because you appear to be
storing bytes rather than characters.





--
Daniel Schuchardt
/Softwareentwicklung/

www.prodat-sql.de

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] invalid byte sequence for encoding

[Daniel Schuchardt]

Hy Scott,

as wrote in my awnser to peter everything is working fine in Postgres81. 
So its not possible thats our parser.


Please look in my awnser for peter.



Scott Ribe schrieb:

In that example i try to insert a "*" with rtf-encoding.


It's not the "*" causing the error, it's the "\0"--which I'm pretty sure is
not a valid character for an RTF file either. Do you have an encoder which
is just blindly reading through the null terminator of a C string and
including it in the encoded string.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] invalid byte sequence for encoding

[Daniel Schuchardt]

Hy, i have some trouble with the new postgresql 8.4.


we check out the compatibility between postgres 8.1 and 8.4

so we have the following error:

invalid byte sequence for encoding "WIN1252": 0x00

can anyone say me a work-around?

We have to change the escape chars that are inserted by our parser, 
right? Is it is possible to simulate the postgresql-8.1 behavoir? (some 
special encoding e.g.)



UPDATE belzeil_frei SET bz_zubez= '*', bz_zubez_rtf= 
'{\\rtf1\\ansi\\deff0{\\fonttbl{\\f0\\fnil\\fcharset0 
Arial;}}\r\n\\viewkind4\\uc1\\pard\\lang1031\\fs20 *\r\n\\par }\r\n\0' 
WHERE dbrid=295116


Result : ERROR:  invalid byte sequence for encoding "WIN1252": 0x00

In that example i try to insert a "*" with rtf-encoding.

i have the same problem with SQL_ASCII


Daniel.
--
Daniel Schuchardt
/Softwareentwicklung/

www.prodat-sql.de <http://www.prodat-sql.de>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Foreign Key question

[Daniel Schuchardt]

Hi Dave,

that makes sense. You should read the documentation about FK. They can 
be 1:n, 1:1, n:1. Normally i would make a unique field in each table to 
avoid complex PK/FK. Eg a serial column.


Dave Clarke schrieb:

Hello

I have a table that I'm trying to refactor and I'm by no means a SQL
expert (apologies if I'm posting to the wrong group). The table in
question has a column that allows NULLs. I want to move that column
into a separate table and set up a FK reference back to the original
table. My question is whether this is the correct way to refactor this
table.

Original table (other columns elided)

PurchaseOrder
-
POType
PONum
ServiceProviderNum
WorkOrderRef (NULLs allowed)

PK: POType + PONum
Candidate Key: PONum + ServiceProviderNum

Proposed structure

PurchaseOrder
-
POType
PONum
ServiceProviderNum

PK: PONum + ServiceProviderNum

WorkOrder
---
PONum
ServiceProviderNum
WorkOrderRef (NULLs not allowed)

PK: PONum + ServiceProviderNum
FK: PurchaseOrder( PONum + ServiceProviderNum)

Does that make sense? My intention is to be able to join PurchaseOrder
and WorkOrder to get the set of PurchaseOrder's that have been
assigned WorkOrderRef's. As I understand it, FK's are generally used
for 1 to many relationships where as this is expressing a 1 to 1
relationship.

I would be very grateful for any assistance with this. Thanks, Dave
  



--

Daniel Schuchardt
/Softwareentwicklung/

www.prodat-sql.de <http://www.prodat-sql.de>

Re: [GENERAL] Autocast script of peter e in PostgreSQL 8.3

[Daniel Schuchardt]

Martijn van Oosterhout schrieb:



Did you not find this during your searching?
http://archives.postgresql.org/pgsql-general/2008-03/msg00995.php
and followups.

Have a nice day,
  


no i didnt found it in google.

now everything is working and as excpected:

EXPLAIN ANALYSE big query

Runtime 450 ms in Postgres 8.1 
Runtime 199 ms in Postgres 8.3


thats fine :-)


thanks a lot.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autocast script of peter e in PostgreSQL 8.3

[Daniel Schuchardt]

Martijn van Oosterhout schrieb:

On Tue, May 06, 2008 at 02:09:30PM +0200, Daniel Schuchardt wrote:
  

*SCDS=# SELECT 1||'~'||1;
ERROR:  operator is not unique: integer || unknown at character 9
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

LINE 1: SELECT 1||'~'||1;



Check the archives. I think Tom pointed out that while it readds all
the casts, some other changes were made for the '||' operator and you
need to delete those. IIRC you need to manipuate the catalog tables
directly for that.

Have a nice day,
  


Hey Matijn,

it simply does not work.  Every created CAST will crash with the '||' 
operator.


Thnx for your comments,

Daniel.



DEMO1=# SELECT 1||'~'||1;
?column?
--
1~1
(1 row)
DEMO1=# CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT 
IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';

CREATE FUNCTION
DEMO1=# CREATE CAST (integer AS text) WITH FUNCTION 
pg_catalog.text(integer) ASIMPLICIT;

CREATE CAST
DEMO1=# SELECT 1||'~'||1;
ERROR:  operator is not unique: integer || unknown at character 9
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

LINE 1: SELECT 1||'~'||1;

DEMO1=# SELECT 1.1||'~'||1.1;
?column?
--
1.1~1.1
(1 row)
DEMO1=# CREATE FUNCTION pg_catalog.text(numeric) RETURNS text STRICT 
IMMUTABLE LANGUAGE SQL AS 'SELECT textin(numeric_out($1));';

CREATE FUNCTION
DEMO1=# CREATE CAST (numeric AS text) WITH FUNCTION 
pg_catalog.text(numeric) AS IMPLICIT;

CREATE CAST
DEMO1=# SELECT 1.1||'~'||1.1;
ERROR:  operator is not unique: numeric || unknown at character 11
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

LINE 1: SELECT 1.1||'~'||1.1;

DEMO1=# SELECT current_date||'~'||current_date;
  ?column?
---
2008-05-06~2008-05-06
(1 row)
DEMO1=# CREATE FUNCTION pg_catalog.text(date) RETURNS text STRICT 
IMMUTABLE LANG

UAGE SQL AS 'SELECT textin(date_out($1));';
CREATE FUNCTION
DEMO1=# CREATE CAST (date AS text) WITH FUNCTION pg_catalog.text(date) 
AS IMPLICIT;

CREATE CAST
DEMO1=# SELECT current_date||'~'||current_date;
ERROR:  operator is not unique: date || unknown at character 20
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

LINE 1: SELECT current_date||'~'||current_date;
  ^



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Autocast script of peter e in PostgreSQL 8.3

[Daniel Schuchardt]

If i use this script
http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html
in PostgreSql 8.3 to reactivate autocast i get the following problems:


*without autocast* script (i learned that 8.3 does autocast if one of 
the || parameters is Text(or Char/Varchar) :-P ):


demo=# SELECT 1||'~'||1;
?column?
--
1~1
(1 row)


*with autocast:

*SCDS=# SELECT 1||'~'||1;
ERROR:  operator is not unique: integer || unknown at character 9
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

LINE 1: SELECT 1||'~'||1;

SCDS=# SELECT 1||'~'::TEXT||1;
ERROR:  operator is not unique: integer || text at character 9
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

LINE 1: SELECT 1||'~'::TEXT||1;


Any hints?

Daniel.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] now i'm really confused. insert/update does autocast, where sometimes.

[Daniel Schuchardt]

Daniel Schuchardt schrieb:

Hey Group,

i know what all will say but i need to recreate the = operator for 
datatypes varchar and integer in PostgreSQL 8.3.


Our Software Project has Millions of Lines and so it would be 
difficult to check all queries and Datatypes. Also it works really 
fine and we all know the risk of wrong auto casting.


Anyone knows the Syntax?

Thanks a lot for your great work.


Daniel.


so it depends on ? if i need an explicit cast?

demo=# CREATE TABLE a (a VARCHAR, b VARCHAR);
CREATE TABLE
demo=# CREATE SEQUENCE test;
CREATE SEQUENCE
demo=# ALTER TABLE a ALTER COLUMN a SET DEFAULT nextval('test');
ALTER TABLE
demo=# INSERT INTO a (b) VALUES ('C');
INSERT 0 1
demo=# SELECT * FROM a;
a | b
---+---
1 | C
(1 row)

demo=# INSERT INTO a (b) VALUES (nextval('test'));
INSERT 0 1
demo=# INSERT INTO a (b) VALUES (5);
INSERT 0 1
demo=# SELECT * FROM a WHERE b=5;
ERROR:  operator does not exist: character varying = integer at character 24
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.

LINE 1: SELECT * FROM a WHERE b=5;
  ^
demo=# SELECT * FROM a WHERE b='5';
a | b
---+---
4 | 5
(1 row)

demo=# UPDATE a SET a=nextval('test'), b=nextval('test');
UPDATE 3
demo=# UPDATE a SET b=nextval('test')+3;
UPDATE 3
demo=# UPDATE a SET b=nextval('test')+3||'~1';
UPDATE 3
demo=# SELECT * FROM a;
a |  b
---+--
5 | 20~1
6 | 21~1
7 | 22~1
(3 rows)

demo=# UPDATE a SET b=3||'~1';
UPDATE 3
demo=# SELECT * FROM a;
a |  b
---+-
5 | 3~1
6 | 3~1
7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b=3||'~1';
a |  b
---+-
5 | 3~1
6 | 3~1
7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b LIKE 3||'%';
a |  b
---+-
5 | 3~1
6 | 3~1
7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b LIKE 3;
ERROR:  operator does not exist: character varying ~~ integer at 
character 25
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.

LINE 1: SELECT * FROM a WHERE b LIKE 3;
   ^
demo=# ALTER TABLE a ADD COLUMN c INTEGER;
ALTER TABLE
demo=# UPDATE a SET a=1, c=nextval('test');
UPDATE 3
demo=# SELECT * FROM a WHERE c=1;
a | b | c
---+---+---
(0 rows)

demo=# SELECT * FROM a WHERE c='1';
a | b | c
---+---+---
(0 rows)

demo=# SELECT * FROM a WHERE c=a;
ERROR:  operator does not exist: integer = character varying at character 24
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.

LINE 1: SELECT * FROM a WHERE c=a;
  ^

demo=# SELECT * FROM a WHERE a=1;
ERROR:  operator does not exist: character varying = integer at character 24
HINT:  No operator matches the given name and argument type(s). You 
might need t

o add explicit type casts.
LINE 1: SELECT * FROM a WHERE a=1;
  ^
demo=# SELECT * FROM a WHERE a='1';
a |  b  | c
---+-+
1 | 3~1 | 23
1 | 3~1 | 24
1 | 3~1 | 25
(3 rows)


demo=# CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$ BEGIN RAISE 
EXCEPTION '%', 1||'B'||current_date; RETURN; END$$LANGUAGE plpgsql;

CREATE FUNCTION
demo=# SELECT test();
ERROR:  1B2008-05-06

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] now i'm really confused. insert/update does autocast, where sometimes.

[Daniel Schuchardt]

demo=# ALTER TABLE a ADD COLUMN d VARCHAR;
ALTER TABLE
demo=# UPDATE a SET d=current_date;
UPDATE 3
demo=# SELECT * FROM a WHERE d=current_date;
ERROR:  operator does not exist: character varying = date at character 24
HINT:  No operator matches the given name and argument type(s). You 
might need t

o add explicit type casts.
LINE 1: SELECT * FROM a WHERE d=current_date;


so and now think what takes happen in plpgsql functions if you work with 
variables.


DECLARE z INTEGER;

calculations

UPDATE a SET b=z WHERE xyz;

GET DIAGNOSTICS rows = ROW_COUNT;
IF rows=0 THEN
ELSE
   FOR r IN SELECT * FROM a WHERE b=z
END IF;



i know that are hypotethical issues but i will show the risk.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator is not unique: integer || integer

[Daniel Schuchardt]

Alban Hertroys schrieb:

another example?:

RAISE NOTICE "error during validation % :", 
'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)


Ehm... What's wrong with RAISE NOTICE "error during validation 
ks:"%"@"%" :', ks, loopdate; ? (I don't quite understand the purpose 
of that colon at the end, btw).
Allows you to format the date to your liking too, just add a 
to_char(loopdate, ).


I know these were just a few examples of your troubles, but so far it 
appears it's desirable to get rid of them for better code.


may be thats a good thing but its not possible. we have to do a major 
rollout for this. we have to look through all the code.
and our next and more important project is to become .net compatible. we 
have to upgrade our development enviroment because of trouble with 
incompatibilities ;-)

so we have to stay on postgresql81 the next years.
if it is nice or not to work with autocasts doesnt matter, it was able 
to do it so we used it for many years.


here another nice example:
(old.dbrid is INTEGER)

 EXECUTE 'UPDATE '||old.rc_tablename||' SET wvod=NULL WHERE 
dbrid='||old.rc_dbrid;


TIMESTAMP
 f:=EXTRACT(MINUTE FROM new.bd_anf_rund-CAST('0:'||CAST(f3 AS 
VARCHAR) AS TIME));


TIMESTAMP too
 new.bd_anf_rund:=timestamp_to_date(new.bd_anf) || ' ' || f4+f2 || 
':' || f1*tplrund;




PS:

i pick up only some string concatanation examples because i can identify 
them fast. all other things i cant see so easy.

(varcharfield=integerfield ^^)

PSPS:
don't touch a running system ;-)

Daniel.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator is not unique: integer || integer

[Daniel Schuchardt]

Klint Gore schrieb:
> RAISE NOTICE "error during validation % :", 
'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)


You know you can use more than one % in a raise and it will take care 
of the data types?



yes i know. the real code looks like this:

S:='another ABG found on ks:"'||ks||'"@"'||loopdate||'"';
PERFORM internalcreatemessage(current_user, 'W', S);


greets.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator varchar = integer

[Daniel Schuchardt]

Tom Lane schrieb:

David Fetter <[EMAIL PROTECTED]> writes:
  

On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:


our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting.  and: everything is working fine ;-)
:-P
  


  

How do you know?  8.3 removed the implicit casts precisely because
they were producing results that could most generously be describe as
"surprising."



This should not be underestimated.  From the reports we've seen so far,
a very sizable fraction of people who find this kind of failure with 8.3
find out that their application was doing something unexpected in the
cases where it happened.  If you've got as many failures as you suggest,
I'd be willing to bet that some of them are bugs in your code, not just
reliance on an implicit feature.

regards, tom lane

  

yes true your right but lets make a calculation:

our application runs about 6 Years now so lets say there are 5% queries 
that run still in a mistake caus of auto cast.
We have to check about 1200 views, 500 functions, every runtime created 
query and so on. Lets say 95% of them run fine now. Now lets think we 
check and rewrite all of them. If you let out the time (our customers 
wont pay for such changes, they will have more errors the first time 
than the last years), we should expect about X % errors because of the 
changes (wrong parantheses and so on). So it would be a horror for us.

thats the problem.

examples:

RAISE EXCEPTION "error during validation % :", 
'ks:"'||ks||'"@"'||loopdate||'"';  (KS is DECLARED VARCHAR, LoopDate is 
a TIMESTAMP);


stempz:=Round(SUM(COALESCE(ba_efftime, timediff(ba_anf, CAST(now() AS 
TIMESTAMP(0) WITHOUT TIME ZONE) FROM bdea WHERE 
timestamp_to_date(ba_anf)=current_date AND ba_ks=oks AND 
*ba_ix||'~'||ba_op* IN (SELECT *a2_ab_ix||'~'||a2_n* FROM ab2_wkstplan 
JOIN ab2 ON a2_id=a2w_a2_id WHERE a2w_oks=oks AND a2w_ks=ks AND 
a2w_planweek=week);


this are integer fields. but they are unique with "*a2_ab_ix||'~'||a2_n*".

another one:

here we need to add 4 CASTS.
CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS 
INTEGER AS $$

DECLARE R INTEGER;
BEGIN
IF extract(month FROM $1)<11 THEN
R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
ELSE
R:=extract(year FROM $1)||extract(month FROM $1)-1;
END IF;
RETURN  R;
END$$LANGUAGE plpgsql IMMUTABLE;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator is not unique: integer || integer

[Daniel Schuchardt]

Tino Wildenhain schrieb:


RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;

was too clean and easy? ;))

Looks like a good oportunity to clean up your code before anything
unexpected happens :-)

Cheers
T.




LOL. Yes I don't like such easy things  :-P

But you see i have a function so i simply can change it ;-)

thnx for the hint, i picked up the best example ever :-P

Daniel.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator is not unique: integer || integer

[Daniel Schuchardt]

Tino Wildenhain schrieb:

Hi,

Daniel Schuchardt wrote:
...

in 81:

postgres=# SELECT 1::INTEGER||1::INTEGER;
?column?
--
11
(1 row)


*shudder* is this actually a port of an application originally
targeted at M*Sql? ;)

Are you using those columns somewhere with their real type - as
integer? I mean if you use them as text everywhere why not change
the type once?

T.

*g*

yes, sure we have to CAST it now. thats no problem. but the problem is 
to find all the places where to cast. and you see that there are many 
possiblilitys.


another example?:

RAISE NOTICE "error during validation % :", 
'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)


another one:

here we need to add 4 CASTS. you see.

CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS 
INTEGER AS $$

DECLARE R INTEGER;
BEGIN
IF extract(month FROM $1)<11 THEN
R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
ELSE
R:=extract(year FROM $1)||extract(month FROM $1)-1;
END IF;
RETURN  R;
END$$LANGUAGE plpgsql IMMUTABLE;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator varchar = integer

[Daniel Schuchardt]

Tino Wildenhain schrieb:

Hi,

Daniel Schuchardt wrote:

David Fetter schrieb:

That technical debt is a risk to your whole project, and you need to
dedicate resources to paying it down.

<http://en.wikipedia.org/wiki/Technical_debt>

There are ways to get those automated casts, but they will only make
your situation worse in the long run.

Cheers,
David.
  


*g* interesting standpoint and your right but:

it is impossible for us to find all the points where the new 8.3 
behavoir would crash at the first time. so our next versions would be 
very buggy and our customers wouldn't be happy ;-)
the next problem is that our service personal has to be traineed too; 
they dont know much about casting, 81 does it automatically; problems 
problems problems.


if it is not possible (i know it is) ;-) to recreate automatic casts 
in 83 we would not be able to upgrade to 83 the next years. the next 
possible date would be in about 3-4 years with the next major release.


PS:
our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views 
that all use implicit casting.

and: everything is working fine ;-) :-P

so we have to choose another way.


Well err... implicit table joining is also off per default I believe.
So if you had used it a lot you would have a similar problem.

Comparing int with text in general does not sound like a very good
idea to me. It should be quite easy to write a script to identify
such places so you can either change the datatypes (preferred) or
add the cast. Then rerun your automated regression tests...

Cheers
T.

it is not possible to do this. we also used automatic casting as a feature:
if a user starts a search, we never check the data type he puts in. in 
81 postgresql automatically casts the user input in dependece of the 
column. so all our search masks has to be checked to, edit fields need 
to get a data type and so on.


example:

user types 1  -> our application automatically starts a search "LIKE '1%'"
user types 12  -> our application automatically starts a search "LIKE 
'12%'"


and so on. and it doenst matter if the searchfield is char or integer, 
81 does it automatically.
if it is a good or bad thing to do it is another question. it works in 
81 and so we used this feature.


cheers.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] operator is not unique: integer || integer

[Daniel Schuchardt]

yeah, its clear that an upgrade from 8.1 to 8.3 is impossible for us 
without a major relase.

there are to many changes so the whole project has to be rechecked.

another example:

in 83:

postgres=# SELECT 1::INTEGER||1::INTEGER;
ERROR:  operator does not exist: integer || integer at character 18
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.



83 with autocast from peter e:

ERROR:  operator is not unique: integer || integer


in 81:

postgres=# SELECT 1::INTEGER||1::INTEGER;
?column?
--
11
(1 row)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator varchar = integer

[Daniel Schuchardt]

No buts.  If you create those automated casts, you are just putting
off the inevitable.
  
yes but than it will become a future project. (i think with postgres 8,5 
or 8,6 :-P )


You're just confirming the fact that your project is in existential
trouble.
  

no, we would get trouble if we upgrade. now everybody is happy :-).
btw: our first software release was with postgres 7.3 and since this 
time we use this feature (about 6 years?).

How do you know?  8.3 removed the implicit casts precisely because
they were producing results that could most generously be describe as
"surprising."
  
yeah thats an old discussion. we know about this and so we look for it. 
it is a powerfull feature in 81 (and earlier) and yes if you use it in 
the wrong way it will produce "surprising" results but if you use it in 
the right way it is very powerfull. and if we get a suprising behavoir 
we do in an explicit cast. done. ;-)

Cheers,
David.
  


daniel.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator varchar = integer

[Daniel Schuchardt]

David Fetter schrieb:

That technical debt is a risk to your whole project, and you need to
dedicate resources to paying it down.



There are ways to get those automated casts, but they will only make
your situation worse in the long run.

Cheers,
David.
  


*g* interesting standpoint and your right but:

it is impossible for us to find all the points where the new 8.3 
behavoir would crash at the first time. so our next versions would be 
very buggy and our customers wouldn't be happy ;-)
the next problem is that our service personal has to be traineed too; 
they dont know much about casting, 81 does it automatically; problems 
problems problems.


if it is not possible (i know it is) ;-) to recreate automatic casts in 
83 we would not be able to upgrade to 83 the next years. the next 
possible date would be in about 3-4 years with the next major release.


PS:
our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views 
that all use implicit casting.

and: everything is working fine ;-) :-P

so we have to choose another way.


cheers.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] operator varchar = integer

[Daniel Schuchardt]

ups;

i have this script but forgott to execute it in the new createt db
monday morning

thnx.

Filip Rembiałkowski schrieb:
2008/5/5 Daniel Schuchardt <[EMAIL PROTECTED] 
<mailto:[EMAIL PROTECTED]>>:


Hey Group,

i know what all will say but i need to recreate the = operator for
datatypes varchar and integer in PostgreSQL 8.3.

Our Software Project has Millions of Lines and so it would be
difficult to check all queries and Datatypes. Also it works really
fine and we all know the risk of wrong auto casting.

Anyone knows the Syntax?



http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html

PS. you could search archives or even use google.


--
Filip Rembiałkowski 


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] operator varchar = integer

[Daniel Schuchardt]

Hey Group,

i know what all will say but i need to recreate the = operator for 
datatypes varchar and integer in PostgreSQL 8.3.


Our Software Project has Millions of Lines and so it would be difficult 
to check all queries and Datatypes. Also it works really fine and we all 
know the risk of wrong auto casting.


Anyone knows the Syntax?

Thanks a lot for your great work.


Daniel.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bug with >to_char('2007-12-31'::DATE, 'YYYYIW')

[Daniel Schuchardt]

BTW:

Windows 2003 Server.

LOLL2=# SELECT version();

  version

PostgreSQL 8.1.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 
3.4.2 (mingw-special)

(1 row)

Regards :)


Daniel Schuchardt schrieb:

LOLL2=# SELECT  to_char('2007-12-31'::DATE, 'IW');
to_char
-
200701
(1 row)


2007-12-31 should be week 2008-01

regards,

   Daniel.


---(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

[GENERAL] bug with >to_char('2007-12-31'::DATE, 'YYYYIW')

[Daniel Schuchardt]

LOLL2=# SELECT  to_char('2007-12-31'::DATE, 'IW');
to_char
-
200701
(1 row)


2007-12-31 should be week 2008-01

regards,

   Daniel.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Any Delphi programmers on this list?

[Daniel Schuchardt]

So whats the problem?

Frank Church schrieb:

I need to access PostgreSQL on a low level using libpq.dll.

Are there any programmers using Delphi here? Free Pascal users is also fine.



---(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

  


---(end of broadcast)---
TIP 1: 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: [GENERAL] shared Locks

[Daniel Schuchardt]

Martijn van Oosterhout schrieb:



Yes you'r right here. Because we use Cursor Fetch, every statement 
starts a transaction. So your right I tested it and this forces a table 
lock. Hm... i will look how to do this in another way.
   



Just COMMIT when you're done. This does kill the cursor though...

If you put a timeout in your app so that it commits that transaction
after, say, 30 seconds idle then your ALTER commands will only wait for
a while. Although, your ALTER will in turn block the following users...

Is your biggest problem that people tend to leave connections open
overnight or something? I simple timeout would work fine if there you
only want to make changes when there are just a few active users.
 

Y i will try it this way. There are some other problems : Some 
connections catch CNC-Center and other mashine data all over the time. 
So its not that easy at all. But i will try it with a commit on idle. 
And Reconnect on that 24h connections. (They sleep most of the time so 
it would be a better technic to terminate the connection and reconnect 
all 5 mins. It will save resources too)


Daniel

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] shared Locks

[Daniel Schuchardt]

Martijn van Oosterhout schrieb:


On Tue, Sep 20, 2005 at 11:18:48AM +0200, Daniel Schuchardt wrote:
 

So if we want to change a table structure (add a field or sth like this) 
many clients own AccessShareLock's because it seams that a simple SELECT 
* FROM table will grant a AccessShareLock and don't release it unitl the 
connection is terminated. Is that true? Is it is possible to release 
this lock without a disconnect? {Problem is that about 30 users has to 
disconnect sometimes. :-( }
   



I think you'll find that locks are held to the end of the transaction.
You're not holding a transaction open but not doing anything, are you?
 

Yes you'r right here. Because we use Cursor Fetch, every statement 
starts a transaction. So your right I tested it and this forces a table 
lock. Hm... i will look how to do this in another way.


thnx,
Daniel

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Blob data type and it's efficiency on PostgreSQL

[Daniel Schuchardt]

Stas Oskin schrieb:


Hi.

 

We are using PostgreSQL as the RDBMS for our product, and are very 
happy with it. Recently, we have encountered a need to store a lot of 
binary files, mainly images (up to ~100,000 files, with sizes varying 
from 300K-2MB).


 

The question is, how well PostgreSQL performs with the blob data type, 
and is it practical to store these files as blobs?


 


Thanks in advance,

Stas Oskin.

We save binary data in pgsql without problems. (Especially icons, Report 
definitions, Images, ...) Be carefull with dump and restore because it 
is a bit complicated to work with blobs here.


Daniel

---(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

[GENERAL] shared Locks

[Daniel Schuchardt]

Hi group,

I have the following problem:

We have developed a  ERP/PPS Developed with pgsql over the last 4 years. 
Now we introduce it on some of our customers {pgsql works great and gets 
good ratings :-)} and so we have to change Tablestructure and so on very 
often. For technologie reasons every clients starts a Connection and 
holds it until the client terminates his program.


So if we want to change a table structure (add a field or sth like this) 
many clients own AccessShareLock's because it seams that a simple SELECT 
* FROM table will grant a AccessShareLock and don't release it unitl the 
connection is terminated. Is that true? Is it is possible to release 
this lock without a disconnect? {Problem is that about 30 users has to 
disconnect sometimes. :-( }



thnx for comments,
Daniel

---(end of broadcast)---
TIP 1: 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

[GENERAL] Divide a float4 by 1 - what is going on???????

[Daniel Schuchardt]

Hi people,

can anyone explain what is going on here : ?

CIMSOFT=# CREATE TEMP TABLE test (n1 FLOAT4);
CREATE TABLE
CIMSOFT=# INSERT INTO test (n1) VALUES (2.456677);
INSERT 6571521 1
CIMSOFT=# SELECT * FROM test;
  n1   
-

2.45668
(1 row)

CIMSOFT=# SELECT n1/1 FROM test;
?column?
--
2.45667695999146
(1 row)

Why I get so many digits by a division with one? Anybody should have 
learned that everything / 1 = everything ;-)


Thnx,
Daniel

PS :

PostgreSQL 8.0.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 
(mingw


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking

[Daniel Schuchardt]

You could also open a transaction and
SELECT FOR UPDATE.
So a second transaction can't Select the same data for update. That 
works fine. (You can try in with pgsql too).

Daniel
Philippe Lang schrieb:
Hi,
I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1, 
and I have a question:
How do you implement an optimistic locking strategy with these tools? With an 
Access front-end, and the ODBC driver, this is completely transparent. A test 
showed me that the Delphi client writes to the database without worrying about 
another user doing that meanwhile...
I saw it's possible to manipulate the isolation level (read commited or serializable 
only) in the ZEOS controls, but it does not help at all here. An optimistic lock is a 
kind of "long transaction" for me.
Thanks for your time!
Philippe
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Arthur 
Hoogervorst
Envoyé : lundi, 9. mai 2005 12:46
À : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Adventures in Quest for GUI RAD
Hi,
The company I work for actually uses the Zeos lib/Postgres extensively to track 
the shipping and sales side for almost 3 years.
We're still running on a 7.2/7.4 Postgres database, because I haven't been 
convinced yet to either update or upgrade to 8.x.x. I'm curious if others have 
successfully moved their (production) database successfully to Postgres 8.0.
Regards,
Arthur
On 5/9/05, Philippe Lang <[EMAIL PROTECTED]> wrote:
 

Hi,
I'm testing Delphi 2005 at the moment, with ZEOS Lib (libpq), and I have to say 
it work fine, as Tony mentioned. I have a few questions:
1) I'm curious: are there a lot of big projects using ZEOS with PG or is that 
technology still relatively new? I would like to use it a replacement for ODBC, 
but I have no experience regarding its stability.
   

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq
 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Delphi - Developers start develop Access components

[Daniel Schuchardt]

Tony Caduto schrieb:
unless we are thinking of something different I do this:
sql.clear;
sql.add('select myfunction();');
sql.add('fetch all from return_cursor;');
open;
>
> ??
> How? On EoF of a TDataSet? Its clear that you can fetch manually. 
How do you do that?
>


??? Thats not really cursor fetch. You always fetch all data this way. 
Cursor fetch for example :

DECLARE c_test CURSOR FOR SELECT * FROM art ORDER BY ak_nr;
FETCH FORWARD 10 FROM c_test;
now only 10 Records are fetched via network. So networktraffic is low 
and the user has a result imediatly.
now you can do again

FETCH FORWARD 10 FROM c_test;
With CURSOR FETCH I mean fetch data on demand and not as a complete block.
And why you dont return your resulset directly from your function?
example:
CREATE TYPE testresulttest AS (id INTEGER, test VARCHAR);
CREATE OR REPLACE FUNCTION testresult() RETURNS SETOF testresulttest AS $$
DECLARE t testresulttest;
BEGIN
t.id:=1; t.test:='first row'; --or some record data
RETURN NEXT t;
t.id:=2; t.test:='middle row';
RETURN NEXT t;
t.id:=3; t.test:='last row';
RETURN NEXT t;
RETURN;
END $$ LANGUAGE plpgsql;
SELECT * FROM testresult();
this works fine from delphi too.
Daniel
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Delphi - Developers start develop Access components

[Daniel Schuchardt]

Tony Caduto schrieb:
The issues mentioned with Zeos have been fixed, the 6.x branch works 
very well now and it much faster
thant the 5.x versions.
I use cursors all the time in my functions and return a refcursor 
which I then fetch.
??
How? On EoF of a TDataSet? Its clear that you can fetch manually. How do 
you do that?

The latest version of PostgresDAC from microolap also works good, I 
tried there 1.x stuff but it was designed for PG 7.2
and had no support at all for schemas.
Zeos does seem to be going no where.

I for one would not object to starting something new that just 
targeted Postgresql and worked with all features
i.e. like canceling a query and asynchronous queries.
The best bet would to just fork Zeos and pull out what is needed, no 
need to start totally from scratch.
Thats what I would do too. Start a completely new Project makes no 
sence, there are good Sources we could take and modify. But for speed 
reasons I would support only PostgreSQL and RIP out anything else.

Does anyone have experience writing Tdataset decendents?
Hm me not really, I fixed up things in zeos but thats all. All in all 
that can't be really complicated, there are many small components 
(textdrivers) and so that all are TDataSet descendents. So I think thats 
not the  point most complicated out there.

The .net driver does work well, but ADO.net does not support always 
connected sessions, it works in a offline mode exactly
like tclientdataset.

Even with these small problems I still would never do a GUI database 
app with C# or Java when I can do it much faster and
easier with Delphi.

Hm I think in that way too. But I try to reduce buissines logic in 
Delphi. Delphi is still the best RAD - Developement tool IMHO.

Daniel
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Delphi - Developers start develop Access components for Postgres?

[Daniel Schuchardt]

Me not.
The problem with lazarus is that they don't support many third party 
components. We use havily Teechart and Developer Express in our software 
so Lazarus isn't a choice for us.

Daniel
Zlatko Matic schrieb:
What about Lazarus Has anybody tried working with Lazarus?
- Original Message ----- From: "Daniel Schuchardt" 
<[EMAIL PROTECTED]>
To: 
Sent: Tuesday, May 10, 2005 2:27 PM
Subject: [GENERAL] Delphi - Developers start develop Access components 
for Postgres?


Hy,
in thread "Adventures in Quest for GUI RAD" there awnsered some 
Delphi - Developers.
We actually use Delphi to access PostgreSQL too. But with some problems:

The older Versions of microolab postgresql dac are absolutely trash. 
I haven't tried the newer ones.
ODBC / Delphi BDE is ripped out by Borland.
ADO is Microsoft and we look for a not Microsoft dependend product 
because are application also runs on unix (with Kylix).
dbexpress is really hard to handle; I dont have extended experience 
with DBExpress and vitavoom - dbexpress driver. (www.vitavoom.com).

we use zeos but the older version because of we found many problems 
in 6.X - Versions. I have fixed up many bugs in zeos 5.4 so it now 
works fine with postgres.(7.3-8 running) All in all i have to say 
Zeos works but i'm not really happy with zeos because it seems not 
clear if it has a future.

Components are very inefficient, every time you open a table all 
configuration / table meta data is fetched. No internal caching or 
sth like this. (they are still faster than dbexpress / odbc / Zeos6.X)
Fields with unknown length are mapped wrong.
and so on-

With Zeos 6.X you cannot use Postgresql - search path because 6.X 
Versions always wrote schema name before table name. 6.X does not 
support Cursor Fetch. 6.X isn't speacially for postgres, they try to 
support all bigger databases.

Has anyone tried .net - pgsql - driver with Delphi 2005?
All in all my opinion is that there is no really good solution to 
access Postgres from Delphi.

Perhaps we should start a project at pgfoundry "PostgreSQL - Access 
for Delphi".

Opinions?
Daniel.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Delphi - Developers start develop Access components for Postgres?

[Daniel Schuchardt]

Hy,
in thread "Adventures in Quest for GUI RAD" there awnsered some Delphi - 
Developers.
We actually use Delphi to access PostgreSQL too. But with some problems:

The older Versions of microolab postgresql dac are absolutely trash. I 
haven't tried the newer ones.
ODBC / Delphi BDE is ripped out by Borland.
ADO is Microsoft and we look for a not Microsoft dependend product 
because are application also runs on unix (with Kylix).
dbexpress is really hard to handle; I dont have extended experience with 
DBExpress and vitavoom - dbexpress driver. (www.vitavoom.com).

we use zeos but the older version because of we found many problems in 
6.X - Versions. I have fixed up many bugs in zeos 5.4 so it now works 
fine with postgres.(7.3-8 running) All in all i have to say Zeos works 
but i'm not really happy with zeos because it seems not clear if it has 
a future.

Components are very inefficient, every time you open a table all 
configuration / table meta data is fetched. No internal caching or sth 
like this. (they are still faster than dbexpress / odbc / Zeos6.X)
Fields with unknown length are mapped wrong.
and so on-

With Zeos 6.X you cannot use Postgresql - search path because 6.X 
Versions always wrote schema name before table name. 6.X does not 
support Cursor Fetch. 6.X isn't speacially for postgres, they try to 
support all bigger databases.

Has anyone tried .net - pgsql - driver with Delphi 2005?
All in all my opinion is that there is no really good solution to access 
Postgres from Delphi.

Perhaps we should start a project at pgfoundry "PostgreSQL - Access for 
Delphi".

Opinions?
Daniel.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Adventures in Quest for GUI RAD

[Daniel Schuchardt]

Arthur Hoogervorst schrieb:
Hi,
The company I work for actually uses the Zeos lib/Postgres extensively
to track the shipping and sales side for almost 3 years.
We're still running on a 7.2/7.4 Postgres database, because I haven't
been convinced yet to either update or upgrade to 8.x.x. I'm curious
if others have successfully moved their (production) database
successfully to Postgres 8.0.
Regards,
Arthur
On 5/9/05, Philippe Lang <[EMAIL PROTECTED]> wrote:
 

Hi,
I'm testing Delphi 2005 at the moment, with ZEOS Lib (libpq), and I have to say 
it work fine, as Tony mentioned. I have a few questions:
1) I'm curious: are there a lot of big projects using ZEOS with PG or is that 
technology still relatively new? I would like to use it a replacement for ODBC, 
but I have no experience regarding its stability.
   

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org
 

Hy,
we use zeos too but the older version because of we found many problems 
in 6.X - Versions. I have fixes up many bugs in zeos 5.4 so it now works 
fine with postgres. All in all i have to say Zeos works but i'm not 
really happy with zeos because it seems not clear for if ut has a future.

What versions of Zeoslib do you use?
Do you use lo with zeos?
With Zeos 6.X you cannot use Postgresql - search path because 6.X 
Versions always wrote schema name before table name. 6.X does not 
support Cursor Fetch.
Components are very inefficient, every time you open a table all 
configuration / table meta data is fetched. No internal caching or sth 
like this.
Fields with unknown length are mapped wrong.
and so on-

Has anyone here tried Delphi dbexpress with www.vitavoom.com - dbexpress 
driver?
Has anyone tried .net - pgsql - driver with Delphi 2005?

All in all my opinion is that there is no really good solution to access 
Postgres from Delphi.

Are here many Delphi - Developers? Perhaps we should start a project at 
pgfoundry "PostgreSQL - Access for Delphi". I spend very much time to 
fix up Zeos-lib. So perhaps we can share.
(Last week i fixes a bug with "--" comment style for example; that style 
of comments wasnt accepted because ZeosLib deleted all #10#13 and so 
everything was a single line -> everything after "--" was a comment for PG)

Daniel.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Windows install/uninstall as a "service"

[Daniel Schuchardt]

[EMAIL PROTECTED] schrieb:
When I did the windows install (NOT as a windows service) but as a 
task to be manually started and stopped, it did not creat a DATA 
directory, nor did it create a postgresql.conf file.
I even used windows explorer search to confirm that no such file 
exists.  I did the uninstall and then reinstalled several times, to 
see if there was any different choice I could make.  My next step 
would be to configure it as a Windows service.  I am just concerned 
whether the uninstall of such a windows service would be a clean 
uninstall.  I would appreciate any advice on how to get this 
postgresql windows install working on my Dell with XP Professional OS.
 
I feel that IF I could achieve an install which actually creates a 
DATA folder, and a posgresql.conf file, that I could then attempt to 
add the listening command.
 
Thanks in advance for any advice or suggestions.
 
It seems that you have installed only binarys. So you have to initialize 
your databasecluster with initdb. Take a look at initdb --help. This 
will create both, Datadirectory and postgresql.conf files.

Daniel
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] What means Postgres?

[Daniel Schuchardt]

Michael Fuhr schrieb:
According to "The design of POSTGRES" by Stonebreaker and Rowe,
POSTGRES means "POST inGRES" (the successor to INGRES).  Various
other sources say that INGRES means "INteractive Graphics (and)
REtrieval System."
Ah, this is what i searched.
I read
http://www.postgresql.org/docs/8.0/interactive/history.html
but there it is only clear that Postgres is based in Ingres. But i also don't 
know what Ingres means.
now I know
;-)
Daniel
---(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

[GENERAL] What means Postgres?

[Daniel Schuchardt]

Just for fun and interrest.
What means Postgres? Where and why this name was born?
Daniel
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Can't install plpython on Windows 8.0

[Daniel Schuchardt]

Magnus Hagander schrieb:
Probably the problem has to do with not finding the underlying
language's DLL (eg, python.dll).
   

Do I need to install a particular version of python then? Is 
 

there any
   

further documentation that can help me with this?
 

Darn if I know.  If the installer documentation doesn't tell you,
I'd suggest griping at the pgsql-hackers-win32 list ...
   

The requirements are listed in the installation instructions at
http://pginstaller.projects.postgresql.org, bullet number 7.
The installer will autodetect if you have the prerequisites installed
when you put it in and disable the option to activate it during the
installation, but you're still free to add it later of course - assuming
you've installed perl/python/tcl by then.
In order to find out what is missing, please run "depends" on the
plpython.dll file (depends.exe is available in the Windows Support
Tools, which are on  the standard CD but not installed by default). It
will show you which imports are missing.
//Magnus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq
 

Ok, the missing library is python23.dll. I installed ActivePython23 and 
now everything works fine.

Daniel.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Can't install plpython on Windows 8.0

[Daniel Schuchardt]

Steve - DND schrieb:
I just tried using createlang to install plpython into a database on my
Windows 8.0 installation and continue to receive the error below. The file
plpython.dll is in exactly the location specified below as well. What
exactly am I doing wrong here?
createlang -e -U postgres plpythonu junk
Password:
SELECT oid FROM pg_language WHERE lanname = 'plpythonu';
SELECT oid FROM pg_proc WHERE proname = 'plpython_call_handler' AND
prorettype =
'pg_catalog.language_handler'::regtype AND pronargs = 0;
CREATE FUNCTION "plpython_call_handler" () RETURNS language_handler AS
'$libdir/
plpython' LANGUAGE C;
CREATE LANGUAGE "plpythonu" HANDLER "plpython_call_handler";
createlang: language installation failed: ERROR:  could not load library
"C:/Pro
gram Files/PostgreSQL/8.0/lib/plpython.dll": dynamic load error
Thanks,
Steve
 

I have the same problems :
C:\Postgres\bin>createlang.exe -d template1 -U root plpythonu
createlang: language installation failed: ERROR:  could not load library 
"C:/postgres/lib/plpython.dll": dynamic load error

(library exists at this place...)
Daniel.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Inheritance and such

[Daniel Schuchardt]

Stephane Bortzmeyer schrieb:
I have a problem which MAY be in the same category.
CREATE TABLE base (
id serial not null primary key,

);
CREATE TABLE specialized (

) INHERITS base;
Now, I try to set up a trigger AFTER UPDATE ON base but, when I update
"specialized", the trigger is not called. Same thing with CREATE or
DELETE. I have to define the trigger to be AFTER UPDATE ON
specialized. Is it normal?
 

Yes, thats known.
You have to define a trigger on each child table. You can point that 
trigger on the same function.

Daniel.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] PG 8.0.1 Does not use Index with IS NOT NULL

[Daniel Schuchardt]

Hy List,
I have a problem with this Query :
SELECT * FROM lifsch WHERE l_dokunr IS NOT NULL ORDER BY l_dokunr;
CIMSOFT=# ANALYSE lifsch;
ANALYZE
CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM lifsch WHERE l_dokunr IS NULL;
  QUERY PLAN

 Index Scan using test on lifsch  (cost=0.00..1400.20 rows=372 
width=201) (actual time=0.000..0.000 rows=189 loops=1)
   Filter: (l_dokunr IS NULL)
 Total runtime: 0.000 ms
(3 rows)

ok, thats fine
CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM lifsch WHERE l_dokunr IS NOT NULL;
  QUERY PLAN

 Seq Scan on lifsch  (cost=0.00..4469.47 rows=39476 width=201) (actual 
time=0.000..360.000 rows=40652 loops=1)
   Filter: (l_dokunr IS NOT NULL)
 Total runtime: 510.000 ms
(3 rows)

not fine but 510ms is ok. (why does the planner do not use the index
"lifsch_dokunr" btree (l_dokunr) WHERE l_dokunr IS NOT NULL)???
CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM lifsch WHERE l_dokunr IS NOT 
NULL ORDER BY l_dokunr;
  QUERY PLAN


 Sort  (cost=11768.20..11866.89 rows=39476 width=201) (actual 
time=4837.000..4997.000 rows=40652 loops=1)
   Sort Key: l_dokunr
   ->  Seq Scan on lifsch  (cost=0.00..4469.47 rows=39476 width=201) 
(actual time=0.000..1350.000 rows=40652 loops=1)
 Filter: (l_dokunr IS NOT NULL)
 Total runtime: 5107.000 ms
(5 rows)

5000ms, thats not fine! ORDER BY seems to slow down everything.
CIMSOFT=# SELECT count(*) FROM lifsch ;
 count
---
 40841
(1 row)
CIMSOFT=# SELECT count(*) FROM lifsch WHERE l_dokunr IS NULL;
 count
---
   189
(1 row)
Table definition:
CIMSOFT=# \d lifsch
 Table "public.lifsch"
   Column   | Type  |Modifiers
+---+---
---
 l_nr   | integer   | not null default 
nextval('public.lifsch_l_
nr_seq'::text)
 l_krz  | character varying(9)  | not null
 l_krzl | character varying(9)  | not null
 l_krzf | character varying(9)  | not null
 l_aknr | character varying(40) | not null
 l_ag_id| integer   |
 l_ldat | date  | not null default currenttime()
 l_lgort| character varying(50) | not null default ''::character 
varying
 l_lgchnr   | character varying(50) | not null default ''::character 
varying
 l_abg_mec  | integer   | not null
 l_abgg | real  | not null
 l_abgg_uf1 | real  |
 l_vkp_uf1  | real  |
 l_vkpbas   | real  |
 l_vkp  | real  |
 l_arab | real  |
 l_def  | boolean   |
 l_azutx| text  |
 l_gew  | real  |
 l_versart  | character varying(30) |
 l_dokunr   | integer   |
 l_bz_bnr   | integer   |
 l_dim1 | real  | not null default 0
 l_dim2 | real  | not null default 0
 l_dim3 | real  | not null default 0
 dbrid  | character varying | default nextval('db_id_seq'::text)
Indexes:
"lifsch_pkey" PRIMARY KEY, btree (l_nr)
"lifsch_idindex" UNIQUE, btree (dbrid)
"lifsch_dokunr" btree (l_dokunr) WHERE l_dokunr IS NOT NULL
"lifsch_ldat" btree (l_ldat)
"lifsch_seldoku" btree (l_krzl, l_dokunr)
"test" btree (l_dokunr) WHERE l_dokunr IS NULL

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] delphi -> postgresql database connect ???

[Daniel Schuchardt]

Burak B&B wrote:
hi,
delphi to postgresql database connect commands and source code free 
download . Please.
Tanks.

Burak BÝTÝKÇÝ
_
Yagmura yakalanmamak için sadece semsiyenize degil, MSN hava durumuna 
güvenin! http://www.msn.com.tr/havadurumu/

---(end of broadcast)---
TIP 8: explain analyze is your friend
free is only
www.zeoslib.net
or
ODBC and BDE
OLEDB and ADO (never tried, don't like Delphi - ADO)
Daniel.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] PostgreSQL 8 on windows very slow

[Daniel Schuchardt]

lol wrote:
I have tried RC4, but there's no differences
My results using my configuration are :
MySQL 4 is 6 times  faster than pgSQL
Firebird 1.5 is 3 times faster than pgSQL
Are these results coherent ? May be the problem comes more from ZeosLib
than pgSQL8
what zeoslib do you use?
6.X?
thay are known slow. I still use 5.X. I haven't tried this yet. Do you 
use Delphi? Have you tried dbexpress? (www.vitavoom.com)

Daniel.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] PostgreSQL 8 on windows very slow

[Daniel Schuchardt]

lol wrote:
Hi,
I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some parameters should be
adjusted. What should I check ? 
Thanks

I think first you should use a newer RC. In the first RC there were some 
probs...

With RC3 I don't have such probs. Sure If I compared Win and Linux PG 
and ma results where that LINUX is about 3 times faster under high access.

Daniel
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Exporting/moving Postgress Instance

[Daniel Schuchardt]

Nick schrieb:
Otherwise is there an recognised dump file in the same way that Oracle has
and if so how do I create it?
Hi Nick,
you should look at
pg_dump or pg_dumpall in the docs.
(or pg_dump --help)
restore the dumped database with psql.
Daniel
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Microsoft Project and PostgreSQL?

[Daniel Schuchardt]

Klaus Harbo schrieb:
according to the manual.  A simple test with PostgreSQL results in an
error something like 'Unknown data type "lo"'.  
It seems that you try to access binary data fields. In postgresql the 
type lo (BLOB) is implemented as oid or bytea. Read in the archives.

Try
CREATE DOMAIN lo AS oid;
Daniel
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Trigger question

[Daniel Schuchardt]

Hi list,
i'm a bit confused. I have a table with a trigger after insert. It is 
possible that this trigger will do inserts in the same table, but is the 
after insert trigger fired again in this case? (I would need this)

e.g.
USER INSERT
BEFORE TRIGGER
AFTER TRIGGER
->DECIDES TO INSERT X ROWS IN THE SAME TABLE
->BEFORE TRIGGER (this one is fired for each row the after trigger inserts)
->AFTER TRIGGER AGAIN FOR EACH ROW THE AFTER TRIGGER INSERTED
thnx a lot.
Daniel
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Postgres 8.0 -> BEGIN EXCEPTION END Syntax????

[Daniel Schuchardt]

Hi ,
can anybody tell me the postgres - syntax for begin excepion end if I 
want to ignore the error?

BEGIN
 exception ...
EXCEPTION
 WHEN OTHERS THEN ?what to write for do nothing?
END;
in oracle it's
WHEN OTHERS THEN null; but this syntax doesn't work in postgres.
thnx
Daniel
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Time problem again?

[Daniel Schuchardt]

Title: Nachricht



No, not 
really
 
perhaps you can do 
a ORDER BY (oid || yourtimefield). So you have the RecordOrder in the way the 
records where inserted.
 
Daniel
 
 
I need to sort some data based on a Time 
  field and the times can cross both midnight and noon. As far as I can tell, 
  there is no way to solve this without also supplying a date or am I missing 
  something?Regards,BTJ 

Re: [GENERAL] psql and blob

[Daniel Schuchardt]

Am Do, 2003-09-18 um 20.21 schrieb Darko Prenosil:

- Original Message -
From: "Daniel Schuchardt" <[EMAIL PROTECTED]>
To: "Doug McNaught" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 18, 2003 4:44 PM
Subject: Re: [GENERAL] psql and blob


> Yes thats it.
>
> Ok - one last question.
> My Script looks like this and actually i can run it only on the server (so
i have to copy all my data to the server each time i want to update my
blobs):
>
>  INSERT INTO tablexy (BLOBFIELD) VALUES (lo_import('BLOBFILE')).
>
> Now we know if I want to upload a clientfile I have to use \lo_import BUT
i cant use this inside the script.
>
>  INSERT INTO  tablexy (BLOBFIELD) VALUES (\lo_import('BLOBFILE')). is not
possible because \lo_import is a unknown command for the server.
>
> So I have to do 2 steps manually in psql:
>
>  \lo_import(ClientFile) -> Returns OID
>
>  INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID)
>
> Is there a way to do this automatically?
> Means my Clientside script should upload a local file (from the same
computer where the script is executed) to the server and insert this file in
a special table automatically.

Maybe Your problem is only to find last inserted oid ?
See: http://www.postgresql.org/docs/7.3/interactive/app-psql.html and look
for LASTOID.
Part from docs:

LASTOID
The value of the last affected OID, as returned from an INSERT or lo_insert
command. This variable is only guaranteed to be valid until after the result
of the next SQL command has been displayed.


So solution might be :
\lo_import(ClientFile)
  INSERT INTO tablexy (BLOBFIELD) VALUES (:LASTOID)
I'm not shure what exactly you want to acomplish, but this might work.

I repeat: lo_read/lo_write from libpq are the only true "client side" way I
know. You can write small "C" program that reads the file from local
filesystem and writes it directly to sql server with no "uploads" or nothing
like that.
Look at : http://www.postgresql.org/docs/7.3/interactive/lo-libpq.html
Note that \lo_import and \lo_export PSQL INSTRUCTIONS are using the same
technique, and they act different than server side lo_import() and
lo_export() SERVER SIDE FUNCTIONS. See:
http://www.postgresql.org/docs/7.3/interactive/app-psql.html
Hope this helps.

Regards !


Ok i will try this. Thats my aim :

I have a masterdatabase located on a computer in our firm. All chages and so are located in this db. This means also icons/pictures my application uses. So this data changes during the time because of development. If we install our app on a new server or update the version I have 2 scripts. First makes a dump. So I dump my administrative tables and blobs in our firm and than i want to upload / install the dumped information to the customers server. Actually this is ugly because of the local pathes. So I have to upload my dump (it consists of seperate text (dump as INSERT WITH COLUMN NAMES) and blob files to the server to reload the data. My scripts look like this :


to prepare

pg_dump -i -h $1 -a -D -t mainmenu -f "/Sql/Dump/ZZ_7 MainMenu.sql" $2
pg_dump -i -h $1 -a -D -t reports -f "/Sql/Dump/ZZ_10 Reports.sql" $2

psql -h $1 -c "SELECT lo_export(mm_picture, '/Sql/Dump/Blobs/mainmenu.mm_id.'|| CAST(mm_id AS VARCHAR) || '.blob') FROM mainmenu" $2
psql -h $1 -c "SELECT lo_export(r_blob, '/Sql/Dump/Blobs/reports.r_id.'|| CAST(r_id AS VARCHAR) || '.blob') FROM reports WHERE r_blob IS NOT NULL" $2

to reload

psql -h $1 -c "DELETE FROM mainmenu;" $2 
psql -h $1 -c "DELETE FROM reports;" $2 

psql -h $1 -f "/var/tmp/dump/ZZ_7 MainMenu.sql" $2 
psql -h $1 -f "/var/tmp/dump/ZZ_10 Reports.sql" $2 

psql -h $1 -c "UPDATE mainmenu SET mm_picture=lo_import('/var/tmp/dump/Blobs/mainmenu.mm_id.'|| CAST(mm_id AS VARCHAR) || '.blob') WHERE mm_picture IS NOT NULL" $2 
psql -h $1 -c "UPDATE reports SET r_blob=lo_import('/var/tmp/dump/Blobs/reports.r_id.'|| CAST(r_id AS VARCHAR) || '.blob') WHERE r_blob IS NOT NULL" $2 

perhaps I should try to dump / reload with oid's

Re: [GENERAL] psql and blob

[Daniel Schuchardt]

Yes thats it. Thanks.

Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
> Daniel Schuchardt <[EMAIL PROTECTED]> writes:
> 
> > Hi @ all,
> > 
> > i'm sure there was a psql-function to transfere my Blob-Data to the
> > server but I can't remember.
> 
> The psql function to use is \lo_import--this reads the file from the
> client side.
> 
> -Doug
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] psql and blob

[Daniel Schuchardt]

Yes thats it.

Ok - one last question.
My Script looks like this and actually i can run it only on the server (so i have to 
copy all my data to the server each time i want to update my blobs):

 INSERT INTO tablexy (BLOBFIELD) VALUES (lo_import('BLOBFILE')).

Now we know if I want to upload a clientfile I have to use \lo_import BUT i cant use 
this inside the script.

 INSERT INTO  tablexy (BLOBFIELD) VALUES (\lo_import('BLOBFILE')). is not possible 
because \lo_import is a unknown command for the server.

So I have to do 2 steps manually in psql:

 \lo_import(ClientFile) -> Returns OID

 INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID)

Is there a way to do this automatically?
Means my Clientside script should upload a local file (from the same computer where 
the script is executed) to the server and insert this file in a special table 
automatically.

Thanks


Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
> Daniel Schuchardt <[EMAIL PROTECTED]> writes:
> 
> > Hi @ all,
> > 
> > i'm sure there was a psql-function to transfere my Blob-Data to the
> > server but I can't remember.
> 
> The psql function to use is \lo_import--this reads the file from the
> client side.
> 
> -Doug
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings


---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] psql and blob

[Daniel Schuchardt]

Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.

I have a script like this :

UPDATE xy SET z = lo_import('localpath_and_file');

but i want to execute this script from the client and so my blob-data is
on the client and lo_import fails (the server doesn't have this file).
Hm. 

Thanks for tips - Daniel.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] psql and blob

[Daniel Schuchardt]

Hm, but lo_import/lo_export imports/exports from the local filesystem. 
I want to upload/download a clientfile to/from the Server Database with
psql. I think this is a feature in psql. But i can't remember...

Daniel



-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
rg] Im Auftrag von Darko Prenosil
Gesendet: Mittwoch, 17. September 2003 20:07
An: Daniel Schuchardt; [EMAIL PROTECTED]
Betreff: Re: [GENERAL] psql and blob


http://www.postgresql.org/docs/7.3/interactive/largeobjects.html 
is what You need. 

Regards !

---(end of broadcast)---
TIP 8: explain analyze is your friend



---(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

[GENERAL] psql and blob

[Daniel Schuchardt]

Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.

I have a script like this :

UPDATE xy SET z = lo_import('localpath_and_file');

but i want to execute this script from the client and so my blob-data is
on the client and lo_import fails (the server doesn't have this file).
Hm. 

Thanks for tips - Daniel.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Picture with Postgres and Delphi

[Daniel Schuchardt]

Hi Edwin,

it depends on the components u use in Delphi. Normally Pictures and
ohter binary data is stored in the FieldType oid. I noticed that bytea
isn't mapped as TBlobField in Delphi in most components. I also don't
know if you are able to use the PG-Functions lo_import(), lo_export()
and so on with bytea. I use oid with Delphi and that works fine for me.
In Delphi you can use Procedures like TBlobField(MyField).LoadFromFile.
Post a Picture to the Server will look like this :

DataSet.Insert;
DataSet.BlobField.LoadFromFile('MyPictureFile');
DataSet.Post;

Now your pitcure is on the Server. You are also able to use standard
DBPicture-components...

Daniel



Am Di, 2003-09-09 um 18.54 schrieb Edwin Quijada:
> Hi !! Everybody
> I am developing app using Delphi and I have a question:
> I have to save pictures into my database. Each picture has 20 o 30k aprox.
> What is the way more optimus?
> That 's table will have 50 records around. Somebody said the best way to
> do that was encoder the picture to field bytea but I dont know about this.
> Another way is save the path to the picture file but I dont like so much
> because I need to write to disk by OS and have permission to write a
> directory to OS.
> What do u think??
> Edwin Quijada
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Kill -9 Postmaster and Temporary Tables

[Daniel Schuchardt]

Hi @ all,

I'v noticed if you kill -9 the Postmaster Temporary Tables (Shemas) will stay in the 
DB. 
Also a VACUUM FULL will not remove them after restart. If the server craches (no 
power) its the same (I'v noticed it here)

Daniel.


---(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: [GENERAL] 2 connections 1 transaction

[Daniel Schuchardt]

Hi Jason,

Thats not what I mean. Both, the Client and the App-Server connects to
the same Postgres-Database. The problem a procedure like this:

1.Client starts Transaction and does some changes.
2.Now the client notices that very huge operations are nescesarry and
starts a procedure @ App-Server
3.App-Server reads the database and makes changes. Problem : the changes
the client does are not commited -> the server can't see the changes or
the case more bad the server waits for the client connection.
(transaction isolation and table / record locking)
4.If everything works well both changes (done by the client and the
server) should commit or rollback now

So both Postgres-Connections has to be @ the same TransAction-OID in
Postgres.

greets

Daniel

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Jason
Godden
Gesendet: Donnerstag, 17. Juli 2003 14:23
An: Daniel Schuchardt; [EMAIL PROTECTED]
Betreff: Re: [GENERAL] 2 connections 1 transaction


Hi Daniel,

Maybe make procedural wrappers around all events undertaken and simulate
it?

ie:

1. Client connects to MS SQL Server (Application Server) and PG Server
2. SQL Server connects to PG Server aswell 3. Client begins new record
process 4. New record process starts by putting PG into serializable
transactions (so 
App Server can respect the changes).
5. If one server transaction fails roll back the other and vice versa
and 
start again.
6. If all good, commit both at the client as the last item in the new
record 
process.

Meanwhile the Application Server may make it's own calls against Pg
through 
strored procedures.  If any part of this PG transaction fails, error
comes 
back to App Server and App Server transaction roll backs, which tells
the 
client and the client rolls back it's own call to PG.

I don't know if this is a viable method (v. messy) and given the
concurrent 
update issue this may play havoc with the client logic if both the
client and 
the app server attempt to modify the same data at the PG Server.  Can
all 
connections simply go through the App Server leaving it to manage the PG

stuff in it's own transactions (still, concurrent update problem can
occur if 
dealing with same records)?

Rgds,

Jason

On Thu, 17 Jul 2003 09:45 pm, Daniel Schuchardt wrote:
> Hi @ all,
>
> Our software consists of a Client-Side App and a Application Server. 
> Every client owns a direct connection to the PSql-Server and for every

> Client the Application-Server also creates a connection to the 
> PSql-Server. The problem is that it is nescesary that the Client and 
> the Application-Server are in the same transaction. But how say 
> connection x to be in the same transaction like connection y?
>
> Thanks for help,
>
> Daniel


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] 2 connections 1 transaction

[Daniel Schuchardt]

Title: Nachricht



Hi @ 
all,
 
Our software 
consists of a Client-Side App and a Application Server. Every client 
owns a direct connection to the PSql-Server and for every Client the 
Application-Server also creates a connection to the PSql-Server. The problem is 
that it is nescesary that the Client and the Application-Server are in the same 
transaction. But how say connection x to be in the same transaction like 
connection y? 
 
Thanks for 
help,

Daniel