John Sidney-Woollett wrote:
I've got a 7.4.6 db running on linux where we've been vacuuming full
the database each night, and have recently switched to vacuum analyse
instead.
We keep seeing this message at the end of the vacuum run:
WARNING: some databases have not been vacuumed in 2013308218
Von: Duffner
Von: Shachar Shemesh
Betreff: Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters
How do I call a StoredProcdure, written in PlPqSql
which returns a set of records (or tableWhatever)
and wants parameters,
from ADO while using adCmdStoredProc ?
Which driver are you using?
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I
assumed that they'd be in our database.
Do I need to need to (plain) vacuum, or vacuum full these template0 and
template1? And is this something that can be done once a week rather
John Sidney-Woollett wrote:
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I
assumed that they'd be in our database.
Do I need to need to (plain) vacuum, or vacuum full these template0 and
template1? And is this something that can be
Hi,
Id like to know if there is something like insert
/*+ append */ into and materialized views (from Oracle) on PostgreSQL.
How can I pass hints to postgre and what hints are available?
Regards,
Bruno
Thanks Richard, I found the page too...
However the implication (and my confusion) is that you need to vacuum
your own databases only. It's not clear (to me) that you have to do the
same for template0 and template1 as well.
Perhaps when someone is updating the docs, something more explicit than
Hi list,
I am working with Postgres 8.0.0 final on a SuSE Linux 9.2 box. I tried
dropdb ax20050206
WARNING: could not remove database directory
/export/home/postgres/data2/base/115101837
DROP DATABASE
(maybe there was still some vacuum activity on the database...)
a) There was a posting in
I've just noticed that the v8 docs are MUCH better in explaining this
than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production).
Perhaps if the same texts
QUOTE
play=# VACUUM;
WARNING: some databases have not been vacuumed in 1613770184 transactions
HINT: Better vacuum them
I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.
I have an address table, with all the normal fields and a customer name
field
(Apologies if this message comes twice - my imap server and mail client
had a little problem)...
I've just noticed that the v8 docs are MUCH better in explaining this
than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production).
Perhaps if the same texts
QUOTE
play=# VACUUM;
David Goodenough wrote:
I could do this by doing a select * from addresses where customer = ?
and type = 'billing', looking to see if there is a result row and if not
repeating the query with type = 'default', but that seems inelegant to
me.
Use NULL to indicate that the customer type is
Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
I have an address table, with all the normal fields and a customer name
field and an address type. There is a constraint that means that the
combination of customer and type have to be unique. Normally the
only record per customer
David Goodenough wrote:
I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.
I have an address table, with all the normal fields and a
does postgres support notifications.
for eg. through one connection to the server, i
begin a transaction and i do inserts there.
in another sessions, i want to get notified that
inserts are taking place in someother session.
please help.
a few other questions
Q1. is there anyway by which we
On Friday 11 Feb 2005 6:01 pm, Surabhi Ahuja wrote:
does postgres support notifications.
for eg. through one connection to the server, i begin a transaction and i
do inserts there. in another sessions, i want to get notified that inserts
are taking place in someother session. please help.
Surabhi Ahuja wrote:
does postgres support notifications.
for eg. through one connection to the server, i begin a transaction and i do inserts there.
in another sessions, i want to get notified that inserts are taking place in someother session.
please help.
You might want to look in the
On Feb 11, 2005, at 7:47 AM, Shridhar Daithankar wrote:
On Friday 11 Feb 2005 6:01 pm, Surabhi Ahuja wrote:
does postgres support notifications.
for eg. through one connection to the server, i begin a transaction
and i
do inserts there. in another sessions, i want to get notified that
inserts
On Fri, Feb 11, 2005 at 11:07:24 +,
David Goodenough [EMAIL PROTECTED] wrote:
I thought of using an inner select for the join, and using limit 1 to
get just the one, and forcing the order by to give me the billing
address by preference, but I am then dependant on the sort order
of the
I'm still quite new to postgres, struggeling with 'views'... I'm on
OS X, using aquadatastudio to interface the db.
Now, after issuing a 'CREATE VIEW' command, I get back a '1 record
affected' message, and I can use the view. However, the view doesn't
appear in the tree view of aquadatastudio
On Friday 11 February 2005 07:47 am, Shridhar Daithankar saith:
No. PostgreSQL backend process is single threaded. However each connection
gets a separate backend process. Hence on SMP machines, all available CPUs
can potentially be used for multiple connections.
Regards,
Shridhar
So
On Thu, Feb 10, 2005 at 07:34:07PM -0500, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
I've been wondering about that. A while ago the change was made from
outputting a NOTICE with the EXPLAIN output to returning a resultset.
If you could agree on what columns to return
Please ignore my previous post, if you still can... :-) I simply and
embarassingly forgot to commit the view to the db ooops.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Thu, Feb 10, 2005 at 10:30:26PM -0500, Tom Lane wrote:
Well, the point is that there are potentially three types of statements
involved:
1. SELECTs
2. Utility statements that can return tuples (EXPLAIN, SHOW, etc)
3. Utility statements that can't return tuples (ALTER,
Tom Lane [EMAIL PROTECTED] writes:
Well, the point is that there are potentially three types of statements
involved:
1. SELECTs
2. Utility statements that can return tuples (EXPLAIN, SHOW, etc)
3. Utility statements that can't return tuples (ALTER, etc)
I'm not sure
On Thursday 10 February 2005 08:56 pm, Alex Turner wrote:
Just a small warning for those people using python with postgresql:
pysgresql and psycopg are very different animals. You cannot drop in
one as a replacement for the other, even though both 'claim' to be DB
API 2.0 compliant.
None of
Terry Lee Tucker wrote:
On Friday 11 February 2005 07:47 am, Shridhar Daithankar saith:
No. PostgreSQL backend process is single threaded. However each connection
gets a separate backend process. Hence on SMP machines, all available CPUs
can potentially be used for multiple connections.
Regards,
[EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hi,
How do I call a StoredProcdure, written in PlPqSql
which returns a set of records (or tableWhatever)
and wants parameters,
from ADO while using adCmdStoredProc ?
I don't believe that works (anyone who thinks I'm wrong, please
Hi,
I'm getting some errors in log file saying invalid character at
position
#20... I know that this is most likely that query is wrong.
Is it possible to capture all queries that get send or at least the
invalid queries?
I'm using postgresql 7.4.3 on Red Hat 9
Thanks,
Mark
John Sidney-Woollett [EMAIL PROTECTED] writes:
However the implication (and my confusion) is that you need to vacuum
your own databases only. It's not clear (to me) that you have to do the
same for template0 and template1 as well.
You have to vacuum template1, but not template0 because the
Thanks Tom, I figured it out after I saw the error message when trying
to vacuum template0.
I think the 7.4.x docs could do with a more explanation.
The 8.0 docs are better although a more explicit explanation stating
that you MUST vacuum analyze template1 (periodically) would be useful.
M Hi,
M I'm getting some errors in log file saying invalid character at
M position
M #20... I know that this is most likely that query is wrong.
M Is it possible to capture all queries that get send or at least the
M invalid queries?
M I'm using postgresql 7.4.3 on Red Hat 9
M Thanks,
M Mark
To log all queries - in your postgresql.conf file, set the following:
log_statement = true
Mark wrote:
Hi,
I'm getting some errors in log file saying invalid character at
position
#20... I know that this is most likely that query is wrong.
Is it possible to capture all queries that get send or at
On Friday 11 February 2005 11:31, Matt K wrote:
David Goodenough wrote:
I could do this by doing a select * from addresses where customer = ?
and type = 'billing', looking to see if there is a result row and if not
repeating the query with type = 'default', but that seems inelegant to
me.
On Friday 11 February 2005 11:46, Richard Huxton wrote:
David Goodenough wrote:
I realise this is not strictly a Postgreslql question, but if the best
way to solve it involves using PG extensions, such as the PG procedural
languages I am only going to do this on PG and so I am happy to use
On Friday 11 February 2005 11:41, Janning Vygen wrote:
Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
I have an address table, with all the normal fields and a customer name
field and an address type. There is a constraint that means that the
combination of customer and type
On Friday 11 February 2005 13:39, Bruno Wolff III wrote:
On Fri, Feb 11, 2005 at 11:07:24 +,
David Goodenough [EMAIL PROTECTED] wrote:
I thought of using an inner select for the join, and using limit 1 to
get just the one, and forcing the order by to give me the billing
address by
I'd like to present some statistics on our database tables,
showing the number of rows and approximate amount of data
in bytes, e.g. something like this from one of our other
databases:
tasks (546916 Kb,62018 rows)
Exact numbers don't matter, I just want to present a feel
for the amount of data
Bruno Almeida do Lago wrote:
Hi,
I'd like to know if there is something like insert /*+ append */ into
and materialized views (from Oracle) on PostgreSQL.
We have SELECT INTO if that helps.
You can do materialized views and use triggers or rules to keep the
materialized view current.
Hi there,
How can I test the type of a parameter passed to a function via ANY data type?
I want something like this:
CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
BEGIN
IF _param IS OF INTEGER TYPE THEN
-- do something
On Fri, Feb 11, 2005 at 10:20:47AM -0800, Mark Harrison wrote:
I'd like to present some statistics on our database tables,
showing the number of rows and approximate amount of data
in bytes, e.g. something like this from one of our other
databases:
tasks (546916 Kb,62018 rows)
Take a
Mark Harrison [EMAIL PROTECTED] writes:
I'd like to present some statistics on our database tables,
showing the number of rows and approximate amount of data
in bytes, e.g. something like this from one of our other
databases:
tasks (546916 Kb,62018 rows)
Exact numbers don't matter, I just
Hi all.
I created a function, using EMS:
CREATE OR REPLACE
FUNCTION geo_schema.search_geo () RETURNS SETOF varchar
AS
$body$
declare x_longlat
cursor for select long_lat from h2s ;
declare
var_longlat public.geometry ;
declare x_id
varchar ;
begin
open x_longlat ;
loop1
loop
On Fri, Feb 11, 2005 at 08:40:53PM +, NosyMan wrote:
How can I test the type of a parameter passed to a function via ANY data
type?
I want something like this:
CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
BEGIN
IF _param IS OF INTEGER
Ignacio Colmenero [EMAIL PROTECTED] writes:
I get the error: ERROR: control reached end of function without RETURN
What can be wrong?
You don't have a RETURN statement.
regards, tom lane
---(end of broadcast)---
TIP 5:
Michael Fuhr [EMAIL PROTECTED] writes:
On Fri, Feb 11, 2005 at 08:40:53PM +, NosyMan wrote:
I want something like this:
CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
PostgreSQL has an undocumented IS OF construct:
... which won't help since IS OF is a
Thanks Tom.
I didn't know I needed a RETURN statement at the end. I assumed that the
return next statement would be enough. It works fine now.
---
Ignacio Colmenero
Software Development
Micotan Software Company Ltd.
-Original Message-
From:
On Fri, Feb 11, 2005 at 02:32:31PM -0500, Tom Lane wrote:
There are some limited cases you could handle in plpgsql using the
polymorphic-functions stuff (ie, ANYELEMENT not ANY) but it still has
no concept of a run-time type test.
Eh? What am I misunderstanding then? The following done in
Michael Fuhr [EMAIL PROTECTED] writes:
On Fri, Feb 11, 2005 at 02:32:31PM -0500, Tom Lane wrote:
There are some limited cases you could handle in plpgsql using the
polymorphic-functions stuff (ie, ANYELEMENT not ANY) but it still has
no concept of a run-time type test.
Eh? What am I
In article [EMAIL PROTECTED],
Martijn van Oosterhout kleptog@svana.org writes:
To be honest, I'm not sure this a real problem. You could simply label
the first columns a rownumber and a depth number.
[See below ]
At the moment people are talking about parsing strings to get the
output. That
Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
Linux Fedora Core 3.
The tables and objects in the database schema are working ok...
I was just writing that function:
--- Begin of my function ---
CREATE OR REPLACE FUNCTION public.sp_insert_users_new (varchar, varchar,
Funny you should say that - the system I am developing has a similar
system, although not as fully developed. I am now having trouble with
psycopg locking up my database somehow. It's almost like there are
outstanding locks on objects in the database that are preventing other
threads operating.
I am getting an error from pygresql:
Exception pg.InternalError: 'Connection already closed' in ignored
I have already mailed the pysgresql list but without much luck. I'm
hoping somebody here might be able to help me figure out whats going
on with this.
I have tried switching to psycopg, but
On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote:
I am now having trouble with psycopg locking up my database somehow.
It's almost like there are outstanding locks on objects in the database
that are preventing other threads operating.
Do you mean the Python script itself is locked
What does the column 'relation' in pg_locks key to (Is there any docs
on the website for this?)
Alex
On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr [EMAIL PROTECTED] wrote:
On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote:
I am now having trouble with psycopg locking up my
pg_locks - awesome - I will check it out...
I think it's uncommitted transactions that are causing the problem.
The original code was written very transactionaly.
Alex Turner
netEconomist
On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr [EMAIL PROTECTED] wrote:
On Fri, Feb 11, 2005 at
On Fri, Feb 11, 2005 at 04:24:17PM -0500, Alex Turner wrote:
pg_locks - awesome - I will check it out...
See also pg_stat_activity. If you don't see anything in the
current_query column then edit postgresql.conf and set
stats_command_string = true, then restart the database. With
this
On Fri, Feb 11, 2005 at 04:26:04PM -0500, Alex Turner wrote:
What does the column 'relation' in pg_locks key to (Is there any docs
on the website for this?)
See the System Catalogs chapter in the documentation (substitute
your version of PostgreSQL in the link):
On Fri, 2005-02-11 at 21:35 +0100, Anton Berezin wrote:
On Fri, Feb 11, 2005 at 11:10:15AM -0500, Sven Willenberger wrote:
On Fri, 2005-02-11 at 16:46 +0100, Palle Girgensohn wrote:
--On fredag, februari 11, 2005 10.24.22 -0500 Sven Willenberger
[EMAIL PROTECTED] wrote:
FreeBSD
On Fri, Feb 11, 2005 at 03:50:05PM +0100, Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Martijn van Oosterhout kleptog@svana.org writes:
At the moment people are talking about parsing strings to get the
output. That output has the same issues as what's being proposed here,
we're just
On Fri, Feb 11, 2005 at 02:56:09PM -0600, Cristian Prieto wrote:
Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
Linux Fedora Core 3.
The tables and objects in the database schema are working ok...
I was just writing that function:
Here's the function definition:
My database table holds phone numbers that may contain characters other
than digits (that's not a problem in itself).
I want to be able to apply a regular expression (to ignore all
characters except digits) to the attribute 'phone' first and then for
the ILIKE to compare
the result to
Martijn van Oosterhout kleptog@svana.org writes:
Here's the function definition:
CREATE OR REPLACE FUNCTION public.sp_insert_users_new (varchar, varch=
ar,
varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
varchar, varchar, varchar, varchar, varchar) RETURNS integer AS
We have started
getting the error
FATAL:terminating connection due to administrator
command
in some of our
processes. Searching in the archives, I gather that this is caused by a SIGTERM,
and might be coming from a ulimit problem.
We are running
Solaris 9/Intel, and the ulimit for
Sven Willenberger [EMAIL PROTECTED] writes:
2. _Or_ plperl does not go all the way to be a conformant perl-embedding
application. It looks at $Config{archlibexp}, but it does not follow
directions described in perlembed(1). In this case it's linking
should be fixed to respect that.
You
On Fri, 2005-02-11 at 23:20 +0100, Anton Berezin wrote:
On Fri, Feb 11, 2005 at 11:09:35PM +0100, Anton Berezin wrote:
On Fri, Feb 11, 2005 at 04:41:55PM -0500, Sven Willenberger wrote:
On Fri, 2005-02-11 at 21:35 +0100, Anton Berezin wrote:
On Fri, Feb 11, 2005 at 11:10:15AM -0500, Sven
David Parker [EMAIL PROTECTED] writes:
We have started getting the error
FATAL: terminating connection due to administrator command
in some of our processes. Searching in the archives, I gather that this
is caused by a SIGTERM, and might be coming from a ulimit problem.
It is coming from
OK, well that's good to know. You mentioned ulimit in
http://archives.postgresql.org/pgsql-bugs/2003-12/msg00080.php
which ulimit parameters were you thinking of? That post is what set me
barking up
this tree ;-) The only other thing not set to unlimited is stack,
which is set to
8480 for the
Sven Willenberger [EMAIL PROTECTED] writes:
it looks like the sed line is stripping out pgac_tmp2 from the pgac_tmp1
leaving the system to use the default perl libperl.so.
when I modified perl_embed_ldflags to not remove the -R line, the
compile works and links to the proper libperl.so
It
David Parker [EMAIL PROTECTED] writes:
OK, well that's good to know. You mentioned ulimit in
http://archives.postgresql.org/pgsql-bugs/2003-12/msg00080.php
Well, that was a different scenario --- or at least I thought it was.
Are you seeing unwanted shutdown of the entire database cluster, or
On Fri, 2005-02-11 at 18:35 -0500, Tom Lane wrote:
Sven Willenberger [EMAIL PROTECTED] writes:
it looks like the sed line is stripping out pgac_tmp2 from the pgac_tmp1
leaving the system to use the default perl libperl.so.
when I modified perl_embed_ldflags to not remove the -R line, the
It's just an individual backend. Unfortunately I don't have the postgres
log file yet, which would obviously help - I only have the application
client's log, but the people reporting the problem know they need to get
me the database log file next time it happens. I haven't been able to
repro this
I using 8.0.1. I create 3 tables with these definitions:
create table t1 (serv_id varchar(50) not null);
create table t2 (serv_id varchar(50) not null);
create table t3 (serv_id varchar(50) not null);
Now I create a view like this:
create or replace view v1 as select * from t1;
Next, I
On Fri, 11 Feb 2005, Guy Rouillier wrote:
I using 8.0.1. I create 3 tables with these definitions:
create table t1 (serv_id varchar(50) not null);
create table t2 (serv_id varchar(50) not null);
create table t3 (serv_id varchar(50) not null);
Now I create a view like this:
create or
Sven Willenberger [EMAIL PROTECTED] writes:
The rpath solution you provided to patch GNUmakefile did not
work however, bailing with:
gcc -O2 -fno-strict-aliasing -Wmissing-declarations, -Wl,
-rpath, /usr/local/lib/perl5/5.8.6/mach/CORE -fpic -DPIC -I.
-I/usr/local/lib/perl5/5.8.6/mach/CORE
On Fri, 2005-02-11 at 19:54 -0500, Tom Lane wrote:
Sven Willenberger [EMAIL PROTECTED] writes:
The rpath solution you provided to patch GNUmakefile did not
work however, bailing with:
gcc -O2 -fno-strict-aliasing -Wmissing-declarations, -Wl,
-rpath, /usr/local/lib/perl5/5.8.6/mach/CORE
Stephan Szabo [EMAIL PROTECTED] writes:
On Fri, 11 Feb 2005, Guy Rouillier wrote:
Now I create a view like this:
create or replace view v1 as select * from t1;
Next, I attempt to update this view like this:
create or replace view v1 as select * from t1 union select * from t2;
I receive:
Sven Willenberger [EMAIL PROTECTED] writes:
plperl.c: In function `compile_plperl_function':
plperl.c:541: warning: cast to pointer from integer of different size
plperl.c:730: warning: cast from pointer to integer of different size
BTW, these scare the heck out of me. It looks to me like
On Fri, 11 Feb 2005 20:40:18 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Sven Willenberger [EMAIL PROTECTED] writes:
plperl.c: In function `compile_plperl_function':
plperl.c:541: warning: cast to pointer from integer of different size
plperl.c:730: warning: cast from pointer to integer of
When I do a pg_dump, (--data-only), PG7 used to dump the data out in
order, so that all foreign-key checks worked correctly when loading
the data back in.
Now it seems with PG8 it's dumping it completely out of order (one of
my completely foreign-key join tables first!) - and I can't get it to
On Fri, Feb 11, 2005 at 03:37:01PM -0500, Tom Lane wrote:
[ thinks about that for awhile... ] Oh, I see. The reason this appears
to work is that plpgsql compiles a separate version of the function for
each actual parameter datatype that is used in a given session. So in
your example, you
Miles Keaton [EMAIL PROTECTED] writes:
When I do a pg_dump, (--data-only), PG7 used to dump the data out in
order, so that all foreign-key checks worked correctly when loading
the data back in.
If it did so, it was by pure luck.
In general it is not possible for pg_dump to guarantee this
Michael Fuhr [EMAIL PROTECTED] writes:
On Fri, Feb 11, 2005 at 03:37:01PM -0500, Tom Lane wrote:
[ thinks about that for awhile... ] Oh, I see. The reason this appears
to work is that plpgsql compiles a separate version of the function for
each actual parameter datatype that is used in a
Try using the ~ regex matching operator instead of ILIKE.
Regards,
Jeff Davis
On Fri, 2005-02-11 at 22:21 +, fiona wrote:
My database table holds phone numbers that may contain characters other
than digits (that's not a problem in itself).
I want to be able to apply a regular
Tom and Stephan, thank you both for taking the time to reply. Further
comments inline.
Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
On Fri, 11 Feb 2005, Guy Rouillier wrote:
Now I create a view like this:
create or replace view v1 as select * from t1;
Next, I attempt to update
Guy Rouillier [EMAIL PROTECTED] writes:
We appear to have two issues here:
(1) What is the meaning of replace as in replace view?
(2) What are the semantics for multiple set operations?
My original issue deals with (1). I'm unfortunately not well versed in
the SQL spec, but from a layman's
85 matches
Mail list logo
