On Thu, Mar 31, 2005 at 08:38:09AM +0200, GIROIRE Nicolas (COFRAMI) wrote:
Can we oblige pl/perl to free memory for variable ?
Or can we configure postgresql to accept this rise in load ?
Or another idea ?
Perl uses reference counting, so as long as a string is visibile
anywhere (remember
Title: RE: [GENERAL] plperl doesn't release memory
Another solution would be to use pl/python, but i don't
know anythig in this language.
Is a
solution viable ? Can pl/python replace pl/perl without losing performance and
use sort under an array ?
Are
the array native in python as in perl
Title: RE: [GENERAL] Catch of ERROR in PLPGSQL
Begin
Exception
of a basic Function.
is provided for.
What I was thinking then, it to create separate functions for the INSERT and UPDATE
which take parameters, for the table, column, and values.
Which I can then make use of the EXCEPTION.
In article [EMAIL PROTECTED],
Martijn van Oosterhout kleptog@svana.org writes:
Perl uses reference counting, so as long as a string is visibile
anywhere (remember closures), it stays around and disappears as soon as
it's unreferenced.
If you have large strings or arrays you don't need, maybe
Title: not able to connect to Database
Hi,
When I do psql test (database name) it says database "test" does not exists.
But with pgAdminIII utility I get to see the database "test" and able to create tables ,insert the data etc.
Why is this?
Rgds
Rao
On Thu, 31 Mar 2005 16:10:04 +0530, Nageshwar Rao
[EMAIL PROTECTED] wrote:
Hi,
When I do psql test (database name) it says database test does not exists.
But with pgAdminIII utility I get to see the database test and able to
create tables ,insert the data etc.
Why is this?
Hello all,
I frequently find that TRUNCATE table and CREATE or REPLACE FUNCTION
are both very slow taking 50 secs or more to complete. We have to run
both commands every minute, so this makes our application
non-functional. But it is not a slow deterioration over time.
Sometimes they run under a
Edson Vilhena de Carvalho [EMAIL PROTECTED] writes
Sorry but perhaps it is a database monitorizer that
makes the monitorization on the databases.
It's my english
Ok, don't worry about your English. Try to find out your questions in your
language here:
http://www.postgresql.org/docs/faq/
Joe Maldonado [EMAIL PROTECTED] writes
I suspect that pg_class has too many pages (49182 as below) and for
some reason the above commands spend a lot of time updating it.
vacuum/analyze on pg_class has not helped. Also, since the time taken
for these commands is not consistently fast or slow,
Carlos Roberto Chamorro Mostac [EMAIL PROTECTED] writes
Hello to all, I have a problem with the use of
temporary tables to have if somebody has an idea.
Handling an application that it requires to process
6,000 registries Parents and the processing of each
one requires to process N
Randall Perry [EMAIL PROTECTED] writes
What I'm having trouble with is figuring out how to grab the primary key
value of the current row. I tried tacking on a var with the pkey row name
to
NEW, but that doesn't work (didn't think it would).
There is an example in PG docs.
Hi
All
Im hoping someone
has an answer for this mystery.
I have a stored
procedure, which queries a table, of about 400-000 records, into a RECORD,
ordered by three columns.
I am using a
conditional INSERT, UPDATE command, basedon evaluating the last
record.column, against the new
Alvaro Herrera [EMAIL PROTECTED] writes:
On Wed, Mar 30, 2005 at 05:41:04PM -0500, Greg Stark wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Is that true even if I'm updating/deleting 1,000 tuples that all reference
the
same foreign key? It seems like that should only need a
Title: Re: [GENERAL] plperl doesn't release
memory
At 8:38 AM +0200 3/31/05, GIROIRE Nicolas (COFRAMI) wrote:
Hi,
I work with William.
In fact, we have already
done the procedure in pl/pgsql but it is too slow and we use array
which are native in perl.
The procedure is recursive, and use
Joe Maldonado [EMAIL PROTECTED] writes:
db=# vacuum analyze verbose pg_class;
INFO: vacuuming pg_catalog.pg_class
INFO: index pg_class_oid_index now contains 1404 row versions in
14486 pages
DETAIL: 443 index row versions were removed.
14362 index pages have been deleted, 14350 are
I need a way to identify Pg backends which have been running a given
query for a long time. What I have so far is to use pg_stat_activity and
the process table (in my case, via Perl's Proc::ProcessTable) to
identify processes with a lot of CPU usage and an active query. The
problem is false
Jeff Boes wrote:
What I'm really hoping for is a way to get the start time for a query
in pg_stat_activity.
... which I guess is pg_stat_activity.query_start. Duh. I will now hide
under my desk for a while.
(Don't know how I missed this; maybe I was looking at old documentation
for the
I'm not an expert in PostgreSQL but it just reminds me some problems I was
confronted to when creating temporary tables in functions...
Some internal tables like pg_class and pg_attribute were growing and VACUUM
was not able to reduce the size of these tables...
Not sure it's the same case but
Title: Message
I am having some
problems understanding how the temp tables work in PG. I have a relatively
lengthy function I am creating that makes frequent use of temporary
tables.
I am dropping and
recreating the temp tables on each run. If I run the procedure the first time
via psql
Shaun Clements wrote:
The problem is the records are not ORDERED properly into the RECORD, and
when looping through it,it is trying to INSERT somewhere down the line, and
is returning an error, saying it cant INSERT a duplicate key into unique..
etc.
What do you mean by not ordered properly? How
Title: Message
You
can find this in the FAQ
4.26) Why can't I reliably create/drop temporary tables in
PL/PgSQL functions?
PL/PgSQL caches function contents, and an unfortunate side effect is that if
a PL/PgSQL function accesses a temporary table, and that table is later dropped
and
Joseph M. Day wrote:
Nothing special about it other than tmp_tblJoin is defined as a
temporary table.
I do understand what is happening (I think). There is a stale pointer to
the previous instance of the temp table (that no longer exists) which is
causing the function to blow up. My question is
Title: Message
Thanks, I thought there might be a way to force it not to do
this.
So I
guess for my example I am going to need to create another temporary table to
retrieve the results of my query, which of course I will also have to be created
via EXECUTE, since EXECUTE will not work in
Title: Message
Depending on your need, I think you could use the structure : FOR-IN-EXECUTE
http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
Tell
us what you exactly want to do if this doesn't match your
needs...
Hello everyone
I have created a table as follows:
CREATE TABLE document
image_id int
image bytea
I want to insert a complete file lets say an open office document into
On Wed, Mar 30, 2005 at 16:45:43 -0700,
Mark Fox [EMAIL PROTECTED] wrote:
What I want is SELECT statement that references no tables but returns
the days in a given month. I'm now thinking that I might be able to
come up with something using an IN clause and using EXTRACT, but
haven't
Title: Message
Great
this is exactly what I was looking for. I read this but was not completely sure
that you could EXECUTE on it.
Just
out of curiosity, what is the performance of this? In MSSQL the only way to do
something equivalent to this was to use a cursor. Cursors are painfully
On Thu, 2005-03-31 at 12:03 -0500, Bob Powell wrote:
--
Hello everyone,
I have created a table as follows:
CREATE TABLE document (
image_id int,
image bytea
);
I want to insert a complete file, let's say an open office document
into this table. Anyone know how I would
It's possible you could get out of this by vacuum full and then reindex
each catalog, but it might be easier to dump and reload the database ...
I've got a similar issue, but caused by neglect rather than anything to to
with pg_autovacuum.
Do you have any rules of thumb for deciding when a
unsubscribe pgsql-general
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Postgresql 8.0.1
If I write the plpgsql:
declare
y int[];
begin
y[1] := 1;
y[2] := 2;
y[3] := 3;
...
All y[] array elements are NULL, as is array_dims(y).
But if I write:
declare
y int[] := '{}';
begin
y[1] := 1;
y[2] := 2;
y[3] := 3;
...
Then things work as expected.
What's going on? (As in
Our company is
moving toward developing web applications in the DNN 3 (Dot Net Nuke)
framework. We currently have several applications supported by
Postgres/PostGIS due to the spatial requirements. To ideally bridge these
two technologies together would be to develop a DNN DataProvider
First, let us consider what is already available. Here are some tools
that perform similar purposes to what you are proposing:
1. Transaction monitor (requires custom modifications to PostgreSQL):
http://starccm.sourceforge.net/
2. Statistics monitor (this is built in to the product):
On Thu, Mar 31, 2005 at 07:13:30PM +, Karl O. Pinc wrote:
Postgresql 8.0.1
If I write the plpgsql:
declare
y int[];
begin
y[1] := 1;
y[2] := 2;
y[3] := 3;
...
All y[] array elements are NULL, as is array_dims(y).
I think this has been fixed for 8.0.2:
There is a .NET data provider that I know of but its not specific to DNN in any way. It can be used in any .NET project
http://gborg.postgresql.org/project/npgsql/projdisplay.php
Best Regards,
Joe AudetteRandy How [EMAIL PROTECTED] wrote:
Our company is moving toward developing web
Greetings,
Thanks Dann, Arthur, Mike, Jeffrey, and Bruno. You've given me a
quick solution and a whole lot to chew on. I never would have come up
with anything as creative.
Thanks again,
Mark
-Original Message-
From: Mark Fox [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 30,
Dann Corbit [EMAIL PROTECTED] writes:
2. Statistics monitor (this is built in to the product):
http://www.postgresql.org/docs/current/static/monitoring-stats.html
Does anyone think an SNMP interface to these would be useful?
Nic Ferrier
http://www.tapsellferrier.co.uk
On Thu, 2005-03-31 at 16:10 +0530, Nageshwar Rao wrote:
When I do psql test (database name) it says database test does not
exists.
But with pgAdminIII utility I get to see the database test and able
to create tables ,insert the data etc.
Why is this?
maybe the existing data base is
2. Statistics monitor (this is built in to the product):
http://www.postgresql.org/docs/current/static/monitoring-stats.html
Does anyone think an SNMP interface to these would be useful?
I do. Would make it easy to hook it up to MRTG, Cacti, or some other
monitoring system. Would be nice for
Create your temp tables like this:
CREATE TEMP TABLE mytest
(
)WITHOUT OIDS ON COMMIT DELETE ROWS
PG holds onto the temp table for the duration of the connection, when the
connection ends all temp tables are dropped. This means you can simply reuse
the same tables for the duration of the
Philip Hallstrom [EMAIL PROTECTED] writes:
2. Statistics monitor (this is built in to the product):
http://www.postgresql.org/docs/current/static/monitoring-stats.html
Does anyone think an SNMP interface to these would be useful?
I do. Would make it easy to hook it up to MRTG, Cacti, or
You don't need to use execute if you create your temp tables like this:
CREATE TEMP TABLE mytest
(
)WITHOUT OIDS ON COMMIT DELETE ROWS
Then use the follwoing function(author unknown) to see if the temp table
already exists:
CREATE or REPLACE FUNCTION public.iftableexists( varchar)
RETURNS
Hello, I have the following sp, I need to return a 'Flag' if the ID of the
row is in the prior select, I tryed with the following code:
create or replace function sp_getadvertisers(ag integer) returns record as
$main$
declare
alladv record;
retrec record;
begin
-- Primero buscamos todas las
Julian Scarfe [EMAIL PROTECTED] writes:
Do you have any rules of thumb for deciding when a pg_dumpall/restore is
likely to be faster than a vacuum full? Or perhaps more straightforwardly,
how would you expect the time required for a vacuum full to scale with pages
used and rows in the
Hi to all,
I have one problem with PostgreSQL and Java. I have a table with Primary
key(serial) field, but after I insert a record i am unable to retrieve
this value. I have tried getGeneratedKeys() and i get nothing returned.
There is another method to retrieve this field?
thanks
On Fri, 2005-04-01 at 10:05 +1000, Jamie Deppeler wrote:
Hi to all,
I have one problem with PostgreSQL and Java. I have a table with Primary
key(serial) field, but after I insert a record i am unable to retrieve
this value. I have tried getGeneratedKeys() and i get nothing returned.
I am
I came across an old RDBM called Business System 12
(http://www.mcjones.org/System_R/bs12.html) a few days ago. It seemed
to have a much simpler method of specifying queries - more similar in
style to relation algebra than SQL. For example, some example code
might look like this.
view =
Hi All,
Is there a built in function which works exactly the opposite way as
to_hex().
I basically want to convert a a hexadecimal to a decimal.
Thanks
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Andrew Chambers
Sent: Thursday, March 31, 2005 4:46 PM
To: postgres
Subject: [GENERAL] your thoughts on a crazy idea please
I came across an old RDBM called Business System 12
Alvaro Herrera wrote:
Now this can't be applied right away because it's easy to run out of
memory (shared memory for the lock table). Say, a delete or update
that touches 1 tuples does not work. I'm currently working on a
proposal to allow the lock table to spill to disk ...
While
On Thu, Mar 31, 2005 at 06:54:31PM -0600, Guy Rouillier wrote:
Alvaro Herrera wrote:
Now this can't be applied right away because it's easy to run out of
memory (shared memory for the lock table). Say, a delete or update
that touches 1 tuples does not work. I'm currently working on
Is there a function I can call to see if the current user has
permissions on a certain table?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Hi!
When setting log_statement = all, and using JDBC PreparedStatements, I get
$n where arguments used to be in previous versions of postgresql:
postgres[30059]: [97-1] LOG: statement: INSERT INTO group_data
(this_group_id, item_text, link_path) VALUES ($1, $2, $3)
I really need to know the
On Thu, Mar 31, 2005 at 08:57:17PM -0500, Joseph Shraibman wrote:
Is there a function I can call to see if the current user has
permissions on a certain table?
See System Information Functions (or Miscellaneous Functions)
in the Functions and Operators chapter of the documentation.
Here's a
Chandra Sekhar Surapaneni wrote:
Hi All,
Is there a built in function which works exactly the opposite way as
to_hex().
I basically want to convert a a hexadecimal to a decimal.
Sure:
test= SELECT x'10'::integer;
int4
--
16
(1 row)
On Tue, 29 Mar 2005 15:39:28 -0600, josue [EMAIL PROTECTED] wrote:
Hello list,
I need to upgrade my dbs from 743 to 801, current data size is around
5GB, I've tried this way:
./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981
but is too slow, any idea or suggestion to properly
Hi,
I am trying to setup rules on a view that will maintain an audit trail
of modifications in the real table. Things seem to be going ok but when
I EXPLAIN my queries, the literal 'now' is being given two different
values, one 2 days earlier. I am running 7.4.7
The view is defined to be;
Bruce Momjian pgman@candle.pha.pa.us writes:
Chandra Sekhar Surapaneni wrote:
Hi All,
Is there a built in function which works exactly the opposite way as
to_hex().
I basically want to convert a a hexadecimal to a decimal.
Sure:
test= SELECT x'10'::integer;
Greetings All,
I was wondering if there was an easy way of
converting the output from a SELECT statement into an Array . I'd like to be
able to SELECT INTO MyArray[] * FROM TABLE WHERE ID=1 . is this
possible? What is the best way of doing this?
Thanks - sorry if this is newbie,
Glen Eustace [EMAIL PROTECTED] writes:
The view is defined to be;
CREATE VIEW domain_registry AS
SELECT *
FROM domain_registry_history
WHERE tstamp 'now';
Offhand I'd expect the 'now' to be reduced to a timestamp constant
at the time the view is created.
Perhaps you were
Offhand I'd expect the 'now' to be reduced to a timestamp constant
at the time the view is created.
Hmmm, my assumption had been that the 'now' constant would be evaluated
everytime the underlying SELECT was build by the planner.
although personally I'd not feel very comfortable with the idea
On Fri, Apr 01, 2005 at 03:22:58PM +1200, Zitan Broth wrote:
I was wondering if there was an easy way of converting the output
from a SELECT statement into an Array . I'd like to be able to
SELECT INTO MyArray[] * FROM TABLE WHERE ID=1 . is this possible?
In 7.4 and later you can use
Hi,
Psql -l gives names of databases in which it does not show test.
But same test I am still able to access through pgAdmin III utility.
Regards
Rao
-Original Message-
From: Ragnar Hafstað [mailto:[EMAIL PROTECTED]
Sent: Friday, April 01, 2005 2:46 AM
To: Nageshwar Rao
Cc:
Hi,
I have a 64 bit Linux box with 64GB RAM and 450GB HDD. I am running a
benchmark on database of size 40GB using the following settings:
- data=writeback
- Moved wal logs to seperate partition
- settings in postgresql.conf:
shared_buffers = 10
work_mem = 10
Cristian Prieto wrote:
Hello, I have the following sp, I need to return a 'Flag' if the ID of
the row is in the prior select, I tryed with the following code:
create or replace function sp_getadvertisers(ag integer) returns record as
$main$
declare
alladv record;
retrec record;
begin
-- Primero
65 matches
Mail list logo
