to_char bug?

FAQ

Memberlist

Usergroups

Profile

Preferences

· nixdoc.net

· man pages

· Linux HOWTOs

· FreeBSD Tips

· Forums

Forum index » Databases » PostgreSQL

to_char bug?

Page 68 of 71 [1061 Posts]

View previous topic :: View next topic
Goto page: Previous 1, 2, 3, ..., 66, 67, 68, 69, 70, 71 Next

Author

Message

raja chidambaram
*nix forums beginner

Joined: 03 Jun 2006
Posts: 1

Posted: Sat Jun 03, 2006 4:10 am Post subject: Note

Re:psqlodbc problem Urgent

Hi all,

We use postgres version 7.5.9-2 with psqlodbc version 7.3-3 that comes
as built in package with RHEL3 for our application.We found a bug in
psqlodbc. When we try to execute a query while the postgres database is
down or crashed (Note: This condition occurs after calling SQLConnect
once to connect to postgres & several calls to executeQuery) the
SQLExecute crashes displaying Broken pipe.

Note: But i did not find this in earlier version of psqlodbc.

The Code goes like this I call this function executeQuery multiple
times with pause in between. Between two calls to executeQuery, the
postgres crashes and the very first call to executeQuery after that
causes the program to crash as mentioned above.

##################################
Void executeQuery()
{
int i, iRetValue;

iRetValue = SQLPrepare( hstmt, (UCHAR *) cmds[i], SQL_NTS );

if ( iRetValue != SQL_SUCCESS )
{
printf( "SQLError SQLPrepare() failed\n" );
return;
}

//printf("Executing: %s\n", cmds[i]);
iRetValue = SQLExecute ( hstmt );
if ( iRetValue != SQL_SUCCESS )
{
printf( "SQLError SQLExecute() failed with retVal: %d\n",
iRetValue
}
);

Is their any way to verify postgres connection. If so please help me.

with regards
raja

_______________________________________________
To unsubscribe, email ilugc-request-nW3y/O4m2H6nF1JIJmvOGw@public.gmane.org with
"unsubscribe <password> <address>"
in the subject or body of the message.
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Jim Nasby
*nix forums Guru

Joined: 15 Nov 2005
Posts: 314

Posted: Tue Jun 06, 2006 10:10 pm Post subject: Note

Re: [pgsql-advocacy] Me And My Database

Moving to -general.

On Fri, Jun 02, 2006 at 10:45:14PM +0200, Leif B. Kristensen wrote:

Quote:

Don't know if it's relevant here, but I've recently launched the third
article in a series on my transition from shrink-wrapped genealogy
software on Windows to my own custom PostgreSQL/PHP application running
on Linux. The new article is almost all about PostgreSQL, along with
some rantings about commercial genealogy software and a description on
my "structured document" (or WYSIWYG) PHP interface. Along with this,
I've created a blog about the project.

I wonder if anyone here would want to review the stuff and maybe post
some comments. It's all at <http://solumslekt.org/forays/>, and the
latest article is of course the
http://solumslekt.org/forays/exodus.php>. The second article in the
series <http://solumslekt.org/forays/blue.php> was my preliminary
PostgreSQL data definition. A lot of it is outdated, but it might be
interesting for someone as well.

Eventually I'll publish the full code, but it's still rather immature.

From the bottom of that page:

SELECT * FROM sources INTO src WHERE source_id = $1;

SELECT * is generally something to avoid. You end up shoving around more
data than needed. Granted, in this case it's only getting shoved down
into plpgsql, but it's still extra work for the server.

Also, the commentary about how MySQL is faster isn't very clear. Are you
using MySQL as some kind of result cache? When you get to running actual
concurrent access on the website, you could well find yourself very
disappointed with the performance of MyISAM and it's table-level
locking. There's probably also some gains to be had on the PostgreSQL
performance.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

Leif B. Kristensen
*nix forums beginner

Joined: 12 Mar 2005
Posts: 44

Posted: Tue Jun 06, 2006 10:44 pm Post subject: Note

Re: [pgsql-advocacy] Me And My Database

On Wednesday 7. June 2006 00:10, Jim C. Nasby wrote:

Quote:

Moving to -general.
From the bottom of that page:

SELECT * FROM sources INTO src WHERE source_id = $1;

SELECT * is generally something to avoid. You end up shoving around
more data than needed. Granted, in this case it's only getting shoved
down into plpgsql, but it's still extra work for the server.

I know that. But the table is only four columns wide, and all the
columns matter in this query. Eventually I'll remove such things
as "SELECT * FROM ..." which really is a bad habit.

Quote:

Also, the commentary about how MySQL is faster isn't very clear. Are
you using MySQL as some kind of result cache? When you get to running
actual concurrent access on the website, you could well find yourself
very disappointed with the performance of MyISAM and it's table-level
locking. There's probably also some gains to be had on the PostgreSQL
performance.

I may have been a little unclear here. My production database is
PostgreSQL, as it quite clearly is the better choice of the two, in
particular wrt data integrity. My Web presentation software is quite a
different matter. It's running at a web hotel that's only offering
MySQL for a database. I find MySQL with MyISAM quite sufficient for
that use, as its only purpose is to serve up simple selects quickly.

The reason why the generation of eg. the family sheet is faster in the
MySQL web context than in my production environment, is that I'm really
comparing apples and potatoes here. The Web database has a much flatter
and denormalized structure, due to the fact that there's no editing.
The entire Web database is repopulated from scratch every time I do an
update.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

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

Robert Treat
*nix forums addict

Joined: 14 Mar 2005
Posts: 93

Posted: Wed Jun 07, 2006 4:26 am Post subject: Note

Re: [pgsql-advocacy] Me And My Database

On Tuesday 06 June 2006 18:44, Leif B. Kristensen wrote:

Quote:

On Wednesday 7. June 2006 00:10, Jim C. Nasby wrote:
Moving to -general.
From the bottom of that page:

SELECT * FROM sources INTO src WHERE source_id = $1;

SELECT * is generally something to avoid. You end up shoving around
more data than needed. Granted, in this case it's only getting shoved
down into plpgsql, but it's still extra work for the server.

I know that. But the table is only four columns wide, and all the
columns matter in this query. Eventually I'll remove such things
as "SELECT * FROM ..." which really is a bad habit.

Also, the commentary about how MySQL is faster isn't very clear. Are
you using MySQL as some kind of result cache? When you get to running
actual concurrent access on the website, you could well find yourself
very disappointed with the performance of MyISAM and it's table-level
locking. There's probably also some gains to be had on the PostgreSQL
performance.

I may have been a little unclear here. My production database is
PostgreSQL, as it quite clearly is the better choice of the two, in
particular wrt data integrity. My Web presentation software is quite a
different matter. It's running at a web hotel that's only offering
MySQL for a database. I find MySQL with MyISAM quite sufficient for
that use, as its only purpose is to serve up simple selects quickly.

I'd think sqlite would be even faster, though it sounds like that might not be
an option for you.

Quote:

The reason why the generation of eg. the family sheet is faster in the
MySQL web context than in my production environment, is that I'm really
comparing apples and potatoes here. The Web database has a much flatter
and denormalized structure, due to the fact that there's no editing.
The entire Web database is repopulated from scratch every time I do an
update.

If you going through this kind of step now, why not just generate the whole
site from the pg database as html pages and then push those out to the
client? That way you eliminate any dbms overhead and reduce load on your
webservers (and eliminate the need for a 2nd db schema)

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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

Leif B. Kristensen
*nix forums beginner

Joined: 12 Mar 2005
Posts: 44

Posted: Wed Jun 07, 2006 1:31 pm Post subject: Note

Re: [pgsql-advocacy] Me And My Database

On Wednesday 7. June 2006 06:26, Robert Treat wrote:

Quote:

On Tuesday 06 June 2006 18:44, Leif B. Kristensen wrote:

The reason why the generation of eg. the family sheet is faster in
the MySQL web context than in my production environment, is that I'm
really comparing apples and potatoes here. The Web database has a
much flatter and denormalized structure, due to the fact that
there's no editing. The entire Web database is repopulated from
scratch every time I do an update.

If you going through this kind of step now, why not just generate the
whole site from the pg database as html pages and then push those out
to the client? That way you eliminate any dbms overhead and reduce
load on your webservers (and eliminate the need for a 2nd db schema)

Ouch. The method I'm using today, is quick, easy, and works like a
charm. It's one local script that runs in a few seconds, generating SQL
command files which are tarred and gzipped to a 1.5MB file, and scp'ed
to the server, and then a serverside load script which takes a couple
of minutes. Generating 40000+ static HTML pages, each of up to 10K,
would fill up my disk quota faster than I can spell postgresql.

And how would you write a name search for static pages?

It ain't broken, and I ain't gonna fix it.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

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

Greg
*nix forums Guru Wannabe

Joined: 11 Jan 2004
Posts: 276

Posted: Wed Jun 07, 2006 1:35 pm Post subject: Can PostGreSQL slow down a Windows PC much?

Our software will be using PostGreSQL as a database. Now I was wondering, if the database is installed on lets say an entry level Celeron, with 256MB of Ram, will it slow down the PC at all? I'm not taking any queries into account here, just generally, does installing the database slow down ones PC a bit? Unfortunately I don't have access to such an entry-level PC, so I can't test it. Thanks ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

Magnus Hagander
*nix forums Guru Wannabe

Joined: 06 Mar 2005
Posts: 158

Posted: Wed Jun 07, 2006 2:05 pm Post subject: Note

Re: Can PostGreSQL slow down a Windows PC much?

Quote:

Our software will be using PostGreSQL as a database. Now I
was wondering, if the database is installed on lets say an
entry level Celeron, with 256MB of Ram, will it slow down the
PC at all?

I'm not taking any queries into account here, just generally,
does installing the database slow down ones PC a bit?
Unfortunately I don't have access to such an entry-level PC,
so I can't test it.

As long as there are no queries running, the effect will be almost
nothing. If you're sure you don't need it, you can turn off autovacuum
which will bring it even closer to zero. (But as long as your db is
reasonably small, having autovacuum check now and then shouldn't be
noticeable either).

When you start putting a query load on it, it will show of course,
depending on what queries you have.

Depending on what you have set for shared memory, some RAM will be used
for it, but it will likely get swapped out if there is no activity.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tatsuo Ishii
*nix forums beginner

Joined: 01 Mar 2005
Posts: 48

Posted: Mon Jun 12, 2006 2:39 am Post subject: Note

Re: Pgpool-committers unsubscribe notification

Please forgive me if this is slightly off topic to the list, but I
couldn't find any suitable *mail based* forum.

I got following mail and it seems the only solution is re-subscrive to
Pgpool-committers (and do it again and again because the system will
kick off them again).

--------------------------------------------------------
From: mailman-bounces@pgfoundry.org
Subject: Pgpool-committers unsubscribe notification
Date: Wed, 07 Jun 2006 09:00:04 +0000
Message-ID: <mailman.1.1149670804.46952.pgpool-committers@pgfoundry.org>

Quote:

t-ishii@pgfoundry.org has been removed from Pgpool-committers.
--------------------------------------------------------

Background. In the default setting whenever I commit to pgpool CVS,
the commit message is sent to pgsql-committers list and that is
definitly annoying to anyone. So I changed pgpool's CVS loginfo to:

DEFAULT /usr/local/bin/activitymail -cpVgf %{sVv} -m 'pgpool -' -w http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgpool/ -t pgpool-committers@pgfoundry.org

Problem with this is it requires the email account on pgfoundry which
is not available for developers on pgfoundry. So I have to register
fake email addresses of pgpool developers.

Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

Tom Lane
*nix forums Guru

Joined: 24 Mar 2005
Posts: 2070

Posted: Mon Jun 12, 2006 2:42 am Post subject: Note

Re: Pgpool-committers unsubscribe notification

Tatsuo Ishii <ishii@sraoss.co.jp> writes:

Quote:

Please forgive me if this is slightly off topic to the list, but I
couldn't find any suitable *mail based* forum.

pgsql-www is probably the right place to report this sort of problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Jim Nasby
*nix forums Guru

Joined: 15 Nov 2005
Posts: 314

Posted: Tue Jun 13, 2006 11:19 pm Post subject: Note

Re: postgres vs. oracle for very large tables

On Mon, May 15, 2006 at 12:24:51PM -0700, TJ O'Donnell wrote:

Quote:

I've written some extensions to postgres to implement
chemical structure searching. I get inquiries about
the performance of postgres vs. oracle. This is a huge
topic, with lots of opinions and lots of facts. But,
today I got some feedback stating the opinion that:
"Postgres performance diminishes with large tables
(we?ll be going to upwards of hundreds of millions of rows)."

Is this pure speculation, opinion, known fact?
Does anyone know of measured performance of postgres
vs. oracle, specifically with very large tables?

You're more likely to run into problems with large fields being toasted
than plain large tables. IIRC Oracle's large object support is better
than PostgreSQL's.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Trent Shipley
*nix forums beginner

Joined: 03 Nov 2005
Posts: 15

Posted: Wed Jun 14, 2006 12:37 am Post subject: Note

Re: postgres vs. oracle for very large tables

On Tuesday 2006-06-13 16:19, Jim C. Nasby wrote:

Quote:

On Mon, May 15, 2006 at 12:24:51PM -0700, TJ O'Donnell wrote:
I've written some extensions to postgres to implement
chemical structure searching. I get inquiries about
the performance of postgres vs. oracle. This is a huge
topic, with lots of opinions and lots of facts. But,
today I got some feedback stating the opinion that:
"Postgres performance diminishes with large tables
(we?ll be going to upwards of hundreds of millions of rows)."

Is this pure speculation, opinion, known fact?
Does anyone know of measured performance of postgres
vs. oracle, specifically with very large tables?

You're more likely to run into problems with large fields being toasted
than plain large tables. IIRC Oracle's large object support is better
than PostgreSQL's.

There's more to it that that. If the huge tables grow, VACCUMING for XID
maintenance could put Postgres at a disadvantage relative to Oracle.

There are "behavioral" variables involved. Furthermore, it may be possible to
trade DBA tricks for initial cost of ownership. Usually the accounting
doesn't work out (DBA salaries are even more expensive than Oracle
licenses) ... but grad students work cheap.

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

Joshua D. Drake
*nix forums Guru

Joined: 09 Mar 2005
Posts: 341

Posted: Wed Jun 14, 2006 2:08 am Post subject: Note

Re: postgres vs. oracle for very large tables

Jim C. Nasby wrote:

Quote:

It really depends. I have many customers with hundred of millions of
rows that don't have ANY problems.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

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

Leif B. Kristensen
*nix forums beginner

Joined: 12 Mar 2005
Posts: 44

Posted: Wed Jun 14, 2006 9:04 am Post subject: Note

Re: Me And My Database

On Wednesday 7. June 2006 00:10, Jim C. Nasby wrote:

Quote:

I've rewritten that passage to make it clearer what it's about. I've
also included the complete table definitions, along with my views and
functions. The article is still at
<http://solumslekt.org/forays/exodus.php>.

One question: When I have a function like this:

CREATE OR REPLACE FUNCTION get_source_text(INTEGER) RETURNS TEXT AS $$
DECLARE
src sources%ROWTYPE;
mystring TEXT;
BEGIN
SELECT * FROM sources INTO src WHERE source_id = $1;
mystring := src.large_text;
IF src.parent_id <> 0 THEN
mystring := get_source_text(src.parent_id) || ' ' || mystring;
END IF;
RETURN mystring;
END;
$$ LANGUAGE plpgsql;

What do you suggest that I write instead of "SELECT * FROM sources INTO
src", when src is defined as sources%ROWTYPE? The table sources is
defined as:

CREATE TABLE sources (
source_id INTEGER PRIMARY KEY,
parent_id INTEGER NOT NULL REFERENCES sources (source_id),
small_text VARCHAR(50) NOT NULL DEFAULT '',
large_text TEXT NOT NULL DEFAULT ''
);

I only need (source_id, parent_id, large_text) in the query. The
small_text column is largely unused, but holds at most 50 chars.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

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

Martijn van Oosterhout
*nix forums Guru

Joined: 02 Mar 2005
Posts: 674

Posted: Wed Jun 14, 2006 9:09 am Post subject: Note

Re: Me And My Database

On Wed, Jun 14, 2006 at 11:04:19AM +0200, Leif B. Kristensen wrote:

Quote:

One question: When I have a function like this:

CREATE OR REPLACE FUNCTION get_source_text(INTEGER) RETURNS TEXT AS $$
DECLARE
src sources%ROWTYPE;

<snip>

Quote:

What do you suggest that I write instead of "SELECT * FROM sources INTO
src", when src is defined as sources%ROWTYPE? The table sources is
defined as:

IIRC, if you just declare src as type "record" you can select any
fields you like. AIUI, declaring a row to be of a specific type is only
really important if you plan to return it or pass it to another
function.

Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Leif B. Kristensen
*nix forums beginner

Joined: 12 Mar 2005
Posts: 44

Posted: Wed Jun 14, 2006 9:35 am Post subject: Note

Re: Me And My Database

On Wednesday 14. June 2006 11:09, Martijn van Oosterhout wrote:

Quote:

I tried:

CREATE OR REPLACE FUNCTION get_source_text(integer) RETURNS TEXT AS $$
DECLARE
src RECORD;
mystring TEXT;
BEGIN
SELECT (source_id, parent_id, large_text)
FROM sources INTO src WHERE source_id = $1;
mystring := src.large_text;
IF src.parent_id <> 0 THEN
mystring := get_source_text(src.parent_id) || ' ' || mystring;
END IF;
RETURN mystring;
END;
$$ LANGUAGE plpgsql;

But now I get this error message:

Query failed: ERROR: record "src" has no field "large_text" CONTEXT:
PL/pgSQL function "get_source_text" line 7 at assignment

PostgreSQL version 8.0.8.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

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

Google

Display posts from previous:

Page 68 of 71 [1061 Posts]

Goto page: Previous 1, 2, 3, ..., 66, 67, 68, 69, 70, 71 Next
View previous topic :: View next topic

The time now is Thu Jan 08, 2009 9:39 am | All times are GMT

Forum index » Databases » PostgreSQL

Jump to:

Topic		Author	Forum	Replies	Last Post
Similar Topics
	to_char number format with optional decimal-point?	Martin T.	Oracle	3	Thu Jul 20, 2006 10:53 am
	strange behaviour using to_char to return daynumber	mich (at work)	Oracle	4	Mon Apr 24, 2006 12:40 pm
	to_char not returning 'day' value without leading zero	Kevin Blount	Oracle	3	Thu Apr 06, 2006 9:32 pm
	to_char syntax	Liz J	Oracle	6	Thu Feb 23, 2006 12:10 am
	to_char (33, '0009') adds an extra white space	Günther De Vogelaere	Oracle	2	Mon Jan 09, 2006 1:03 pm

Mobile Phones | Credit Cards | Mortgage | Remortgages | MPAA

Other DeniX Solutions sites: Unix/Linux blog | electronics forum | medicine forum | science forum |

[ Time: 0.2522s ][ Queries: 16 (0.0933s) ][ GZIP on - Debug on ]