Monitor what command is executing at the backend

Hi,

I am using postgres 8.2.3. I have recently converted my database from
sql-ascii to UTF8. I have a portal which calls a perl program to insert the
data into the database.

While inserting, I am getting an error message

DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding
"UTF8": 0x92
[for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext,
isprivate)

How can I set the client encoding in perl. Can somebody help me?

Regards
skarthi

_________________________________________________________________
Get a FREE Web site, company branded e-mail and more from Microsoft Office
Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/

Am Dienstag, 20. März 2007 20:34 schrieb Karthikeyan Sundaram:
> Hi,
>
> I am using postgres 8.2.3. I have recently converted my database from
> sql-ascii to UTF8. I have a portal which calls a perl program to insert
> the data into the database.
>
> While inserting, I am getting an error message
>
> DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding
> "UTF8": 0x92
> [for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext,
> isprivate)
>
>
> How can I set the client encoding in perl. Can somebody help me?
>
> Regards
> skarthi

After the connection is done send set client_encoding to '<Encoding Perl is
using>' to the server. The server then will convert the strings back and
forward for you.
Se manual chapter 21.2 Character Set Support for more details.

Best regards
Ivo
>
> _________________________________________________________________
> Get a FREE Web site, company branded e-mail and more from Microsoft Office
> Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

> I am using postgres 8.2.3. I have recently converted my database from
> sql-ascii to UTF8. I have a portal which calls a perl program to insert the
> data into the database.
>
> While inserting, I am getting an error message
>
> DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding
> "UTF8": 0x92
> [for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext,
> isprivate)

Try to modify client_encoding in postgresql.conf to utf8 and restart your db.

regards
Claus

On Tue, Mar 20, 2007 at 09:06:25PM +0100, Claus Guttesen wrote:
> >DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding
> >"UTF8": 0x92
> >[for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext,
> >isprivate)
>
> Try to modify client_encoding in postgresql.conf to utf8 and restart your
> db.

The data is apparently not UTF-8 so client_encoding needs to be set
to something else. 0x92 is a hint that the encoding might be
Windows-1252 or Windows-1250 since that byte represents the right
single quotation mark in those encodings; that's a common character
in data that originated in Windows. Also, client_encoding can be set
by the client without having to restart the backend.

--
Michael Fuhr

Hi,

Our database is growing fast. I want to create a cronjob that should
tell me what is the current size of the database on each day.

How can I find this from the database? Is there any pre-written scripts
written by somebody to share?

Regards
skarthi

_________________________________________________________________
Live Search Maps find all the local information you need, right when you
need it. http://maps.live.com/?icid=hmtag2&FORM=MGAC01

On 21.03.2007, at 11:25, Karthikeyan Sundaram wrote:

> Our database is growing fast. I want to create a cronjob that
> should tell me what is the current size of the database on each day.
>
> How can I find this from the database? Is there any pre-written
> scripts written by somebody to share?

Be aware of linebreaks ...

[Galadriel: cug ] cug $ psql -U postgres -c "select pg_size_pretty
(pg_database_size('testdb'));"

pg_size_pretty
----------------
495 MB
(1 row)

Hi everybody,

Is there a way to see from the log files on what sql statement is
currently by which user? In other words, I want to monitor the DB activity.

How can I find it?

Regards
skarthi

_________________________________________________________________
Get a FREE Web site, company branded e-mail and more from Microsoft Office
Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/

> Is there a way to see from the log files on what sql statement is
> currently by which user? In other words, I want to monitor
> the DB activity.

for a current snapshot you don't need the logs, try:

select * from pg_stat_activity;

(command string needs to be enabled for your database.)

George,

How will I enable command string to see the commands?

Regards
skarthi

>From: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
>To: "Karthikeyan Sundaram"
><skarthi98(at)hotmail(dot)com>,<pgsql-admin(at)postgresql(dot)org>,<pgsql-sql(at)postgresql(dot)org>
>Subject: Re: [SQL] Monitor what command is executing at the backend
>Date: Wed, 21 Mar 2007 14:56:50 -0700
>
> > Is there a way to see from the log files on what sql statement is
> > currently by which user? In other words, I want to monitor
> > the DB activity.
>
>for a current snapshot you don't need the logs, try:
>
> select * from pg_stat_activity;
>
>(command string needs to be enabled for your database.)
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

_________________________________________________________________
Its tax season, make sure to follow these few simple tips
http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=HMMartagline

> How will I enable command string to see the commands?

in your postgresql.conf set stats_command_string = true

read
http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html for
details

Karthikeyan Sundaram wrote:
> Hi,
>
> Our database is growing fast. I want to create a cronjob that
> should tell me what is the current size of the database on each day.
>
> How can I find this from the database? Is there any pre-written
> scripts written by somebody to share?
>

Hi,
there is an administrative function called pg_database_size(name), as in
SELECT pg_database_size('mydb'); which returns the disk space used by
the database with the specified name.

See Chapter 9.20.

cheers,
Hubertus
--
Hubertus v. Fuerstenberg
MAIL: Fraunhofer Institute for Solar Energy Systems
Heidenhofstr. 2, 79110 Freiburg, Germany
PHONE: +49 (761) 4588 5153 FAX: +49 (761) 4588 9286
EMAIL: hubertus(dot)fuerstenberg(at)ise(dot)fraunhofer(dot)de
--- Quis custodiet ipsos custodes? ---