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 b
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
maintenance_
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: pgsql-genera
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
> 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 id
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
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,
Bruce Momjian 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;
>int4
>
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;
CREATE
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
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 r
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.
He
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 ar
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
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
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 ...
-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
(http://www.mcjones.org/Syste
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
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 =
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.
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
-
"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 t
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 campa
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 pg_
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 M
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 conn
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 g
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 dat
"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
---
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
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 applicat
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:
http://archi
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):
http://w
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
as
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 "Gosh
unsubscribe pgsql-general
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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 pg_dum
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 ho
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 slo
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
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 docu
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...
-
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 t
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
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 recre
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
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 i
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 hope
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 pg_sta
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 pos
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, 1435
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 request
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 o
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, based on evaluating the last
record.column, against the new recor
"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.
http://www.po
"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 r
"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 o
"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
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 se
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.
>
>
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
In article <[EMAIL PROTECTED]>,
Martijn van Oosterhout 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 you need to
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.
Kind
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
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 c
65 matches
Mail list logo
