Sebastián Baioni wrote:
Every day we run a Windows Programmed pg_dump, it used to
work fine with PostgreSQL 8.0, but since we installed the new
version we are not able to make a backup. We tried to make a
whole database backup and it never ends. We tried to backup
table by table and it
I've read this
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod
int)
AS $$
BEGIN
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql;
at
http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
what if I need explicit
On 1/3/08, Chris Browne [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (Bruce Momjian) writes:
I am not sure if I am asking too much but does it make sense, and is it
possible, to enhance NOTIFY that process name/value pair? Like this:
NOTIFY MyName=MyValue;
With the capability of
Hi,
i had a rather strange crash of my server (log file at the end of my mailling)
and i was googling for Signal 4 and read http://en.wikipedia.org/wiki/SIGILL
i am running on linux 2.6.18-5-686 and postgresql-8.1.9-0etch2.
Most (all?) other processes on this machine got signal 4 at this time
On Jan 4, 2008, at 12:44 AM, [EMAIL PROTECTED] wrote:
Hi,
i had a rather strange crash of my server (log file at the end of my
mailling)
and i was googling for Signal 4 and read http://en.wikipedia.org/wiki/SIGILL
i am running on linux 2.6.18-5-686 and postgresql-8.1.9-0etch2.
Most
Hi,
try using liquibase. http://www.liquibase.org/ . It works very well.
kind regards,
Janning
Am Dienstag, 16. Oktober 2007 18:38 schrieb Stanislav Raskin:
Hello everybody,
I am currently running two PostgreSQL servers on two different machines.
One of them I use for development and the
On Fri, 4 Jan 2008 09:38:35 +0100
Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote:
create or replace function testA(out _BasketID1 int, out _BasketID2
int) as $$
begin
_BasketID1:=1;
_BasketID2:=2;
return;
end;
$$ language plpgsql;
create or replace function testB(out
Am Freitag, 4. Januar 2008 10:03 schrieb Steve Atkins:
On Jan 4, 2008, at 12:44 AM, [EMAIL PROTECTED] wrote:
I just want to ask if there is something else
besideds
hardware failure which could force a signall 4 (ILL)?
Software bugs can on rare occasions (by overwriting return stack data
Where can I ask about pg/tcl?
___
Support the World Aids Awareness campaign this month with Yahoo! For Good
http://uk.promotions.yahoo.com/forgood/
---(end of broadcast)---
TIP 6:
Tom Lane [mailto:[EMAIL PROTECTED] wrote:
Fernando Hevia [EMAIL PROTECTED] writes:
With 8.2.x the ARST abbreviation was recognized after including the
following line in /usr/share/postgresql/8.2/timezonesets/Default
ARST -14400 D # Argentina Summer Time
Um ... is that really
On Fri, 28 Dec 2007 03:06:27 -0800 (PST)
[EMAIL PROTECTED] wrote:
In the past, I've used Druid III. Terrific tool, but no longer seems
to be under active development. Is there anything better than Druid?
Well if it was a terrific tool... it is still under development. Last
version was uploaded
On Fri, 28 Dec 2007, thereverandpdawg wrote:
I have some field data which comes off of a data logger. In some cases
the data is out of bounds of logger or instrumentation. In which case
it is reported as 'NaN' for 'not a number.'
Is there such a value in postgresql? If not I will have to hack
On Wed, Jan 02, 2008 at 11:17:52PM -0800, Goboxe wrote:
Hi,
What are required to be configured in order to send logs to
remote syslog server for pg running on Windows platform?
In http://pgfouine.projects.postgresql.org/tutorial.html , it says
You can set syslog to send the log to another
[EMAIL PROTECTED] wrote:
Hi,
We are currently trying to migrate an Oracle database to PostgreSQL.
Unfortunately, the Java application that access the database does not
use the AS keyword to alias column names in select statements.
Thus, the following statement SELECT col1 alias1 FROM
I'm just wetting my hands with slony and during the setup of the slave,
I did and dump and restore of the master DB to the Slave DB.
However during the startup of slony, I noticed that it issues a truncate
command to the (to be) replicated table. Hence, this means that there's
no such need for me
Hi all
I have following problem when I use this
select headline('asd asd asd asd asd asd asd asd asd asd asd asd more more
more more more more more', to_tsquery('asd'), '');
I got this
basd/b basd/b basd/b basd/b basd/b basd/b
basd/b basd/b basd/b basd/b basd/b basd/b more
more more
I've just spent a few hours searching and reading about the postgres
way of selecting distinct records. I understand the points made about
the ORDER BY limitation of DISTINCT ON, and the abiguity of GROUP BY,
but I think there's a (simple, common) case that have been missed in
the discussion.
We have run into a situation where our postgresql 8.2.5 database appears
to be corrupt and we are no longer able to run pg_dump. We don't know
what is causing the data corruption issues and unfortunately this is one
of those cases where we cannot reliably reproduce the problem (so no
test case
I am new to this group so forgive me if this is the wrong area to post
in.
I have some field data which comes off of a data logger. In some cases
the data is out of bounds of logger or instrumentation. In which case
it is reported as 'NaN' for 'not a number.'
Is there such a value in postgresql?
Hi,
We are currently trying to migrate an Oracle database to PostgreSQL.
Unfortunately, the Java application that access the database does not
use the AS keyword to alias column names in select statements.
Thus, the following statement SELECT col1 alias1 FROM any_table fails.
AFAIK, AS is
Hi,
What are required to be configured in order to send logs to
remote syslog server for pg running on Windows platform?
In http://pgfouine.projects.postgresql.org/tutorial.html , it says
You can set syslog to send the log to another server through the
network with @ip.ad.dr.ess.
So try to
On Fri, Dec 28, 2007 at 10:22:03AM -0800, thereverandpdawg wrote:
I have some field data which comes off of a data logger. In some cases
the data is out of bounds of logger or instrumentation. In which case
it is reported as 'NaN' for 'not a number.'
Is there such a value in postgresql? If
On Thu, 03 Jan 2008 14:20:41 -0500, Chris Browne [EMAIL PROTECTED]
said:
TODO already has:
* Add optional textual message to NOTIFY
This would allow an informational message to be added to the notify
message, perhaps indicating the row modified or other custom
On 1/4/08, CN [EMAIL PROTECTED] wrote:
On Thu, 03 Jan 2008 14:20:41 -0500, Chris Browne [EMAIL PROTECTED]
said:
TODO already has:
* Add optional textual message to NOTIFY
This would allow an informational message to be added to the notify
message, perhaps
On Jan 4, 2008, at 9:35 AM, Rich Shepard wrote:
It would be appropriate to convert NaN to NULL since the valid is
literally unknown.
It's not unknown: it's known to be something other than a number,
which is not the same as unknown.
*considers quoting Rumsfield, but common sense
On Jan 4, 2008, at 10:00 AM, Rich Shepard wrote:
Maybe we have a difference in semantics that is dependent upon
the application.
The distinction can be important, as SQL has only partially
implemented 3-valued logic (TRUE/FALSE/UNKNOWN) and treats NULL in
sometimes unexpected ways. NaN
Glyn Astill wrote:
Where can I ask about pg/tcl?
What about it?
___
Support the World Aids Awareness campaign this month with Yahoo! For Good
http://uk.promotions.yahoo.com/forgood/
---(end of
On Fri, 4 Jan 2008, Michael Glaesemann wrote:
It's not unknown: it's known to be something other than a number, which is
not the same as unknown.
Michael,
Perhaps I mis-interpreted the original message. If a measurement is out of
the instrument's range it means that the value is unknown.
Hi there,
I am creating functionality where there are blocks of text that are
being stored in the DB and that need to be searched for. No like or
pattern matching, just a plain old WHERE clause. Obviously, hash indexes
would be best here, however I've been warned away from PG's hash
On Jan 4, 2008 8:33 AM, Magnus Hagander [EMAIL PROTECTED] wrote:
On Wed, Jan 02, 2008 at 11:17:52PM -0800, Goboxe wrote:
Hi,
What are required to be configured in order to send logs to
remote syslog server for pg running on Windows platform?
In
[EMAIL PROTECTED] wrote:
I've just spent a few hours searching and reading about the postgres
way of selecting distinct records. I understand the points made about
the ORDER BY limitation of DISTINCT ON, and the abiguity of GROUP BY,
but I think there's a (simple, common) case that have
O'Shea, Brendan [EMAIL PROTECTED] writes:
pg_dump: reading schemas
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: catalog is missing 9
attribute(s) for relid 10297
Ugh. Does it work if you do
export PGOPTIONS=--ignore_system_indexes=1
first? If so, the
On Fri, Jan 04, 2008 at 09:31:41AM -0500, Bruce Momjian wrote:
Unfortunately, this is on our TODO list as something we are not planning
to do:
* Allow AS in SELECT col AS label to be optional (not wanted)
Because we support postfix operators, it isn't possible to
On Wed, 2 Jan 2008, Jan Sunavec wrote:
Hi all
I have following problem when I use this
select headline('asd asd asd asd asd asd asd asd asd asd asd asd more more
more more more more more', to_tsquery('asd'), '');
I got this
basd/b basd/b basd/b basd/b basd/b basd/b basd/b
basd/b basd/b
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
On Fri, Jan 04, 2008 at 09:31:41AM -0500, Bruce Momjian wrote:
Unfortunately, this is on our TODO list as something we are not planning
to do:
* Allow AS in SELECT col AS label to be optional (not wanted)
On Sat, Jan 05, 2008 at 03:20:54AM +1100, Naz Gassiep wrote:
Hi there,
I am creating functionality where there are blocks of text that are
being stored in the DB and that need to be searched for. No like or
pattern matching, just a plain old WHERE clause. Obviously, hash indexes
would
[EMAIL PROTECTED] writes:
I've just spent a few hours searching and reading about the postgres
way of selecting distinct records. I understand the points made about
the ORDER BY limitation of DISTINCT ON, and the abiguity of GROUP BY,
but I think there's a (simple, common) case that have
While running some programs overnight to populate the database, it
crashed. I now can not start the database server. I have a backup of
the database, but to restore I will need to connect... any help is much
appreciated.
Here is the log from the crash (looks like I ran out of disk space?
there
Hello All
I recently ran out of disk space on the volume hosting my default
tablespace. As it turned out, the sysad didnt allocate all of the disk to
the volume when he installed it, so he resized the volume and restarted the
database. The server came up fine so I assumed all was well ( the
Like Jonathan pointed out, looks like a permission issue. We see
these things show up on Windows boxes quite often when a virus checker
/ spyware checker kicks in and does something stupid like lock a file
that postgresql needs to have exclusive access to.
Simple answer there is to put
Kyle Wilcox wrote:
Here is the log from the crash (looks like I ran out of disk space?
there is over 200gb free space...) :
2008-01-04 00:56:34 ERROR: could not extend relation 1663/42463/47343:
Permission denied
^
2008-01-04 00:56:34 HINT: Check free disk space.
You
On Thu, Jan 03, 2008 at 11:15:23AM +0800, Ow Mun Heng wrote:
I'm just wetting my hands with slony and during the setup of the slave,
I did and dump and restore of the master DB to the Slave DB.
Nope, you don't need to do that. You need a copy of the _schema_ on the
target machine. But slony
Checked some logs and a backup job was running at the same time as the
insertions. Must have been the backup job locking a file. Is there a
way to get the server to restart or should I reinstall?
Scott Marlowe wrote:
Like Jonathan pointed out, looks like a permission issue. We see
these
SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes:
I want to download postgresql 8.3 beta 1 version for testing a patch. Im not
able to download this from this website
http://www.postgresql.org/ftp/source/v8.3beta1/
and its giving an FTP error 550 Failed to change directory
Why in the world would
On Jan 4, 2008 11:46 AM, Kyle Wilcox [EMAIL PROTECTED] wrote:
Checked some logs and a backup job was running at the same time as the
insertions. Must have been the backup job locking a file. Is there a
way to get the server to restart or should I reinstall?
Seeing as your backup program
O'Shea, Brendan [EMAIL PROTECTED] writes:
Lane, Tom [EMAIL PROTECTED] writes:
Ugh. Does it work if you do
export PGOPTIONS=--ignore_system_indexes=1
I tried that, but unfortunately pg_dump still fails to run and the error
message is identical to previous attempts.
Huh. So it's not index
Hi,
I want to download postgresql 8.3 beta 1 version for testing a patch. Im not
able to download this from this website
http://www.postgresql.org/ftp/source/v8.3beta1/
and its giving an FTP error 550 Failed to change directory
Is there any other way I can get this betat1 version?
Thanks
Hello all,
I've got an application that keeps a persistent connection into the
database and constantly executes number of stored functions. Sometimes
I need to modify the implementation of those functions to change the
data layout, but doing that results in errors like this:
unrecognized node
Ken Winter [EMAIL PROTECTED] writes:
When I do a pg_dump from an 8.1 database (with options schema-only,
no-owner, and plain format), the dump file includes the following:
ALTER SEQUENCE transaction_transaction_id_seq OWNED BY
transaction.transaction_id;
Don't use 8.2 pg_dump if you are
Alex Vinogradovs [EMAIL PROTECTED] writes:
I've got an application that keeps a persistent connection into the
database and constantly executes number of stored functions. Sometimes
I need to modify the implementation of those functions to change the
data layout, but doing that results in
[EMAIL PROTECTED] (Andrew Sullivan) writes:
On Thu, Jan 03, 2008 at 11:15:23AM +0800, Ow Mun Heng wrote:
I'm just wetting my hands with slony and during the setup of the slave,
I did and dump and restore of the master DB to the Slave DB.
Nope, you don't need to do that. You need a copy of
When I do a pg_dump from an 8.1 database (with options schema-only,
no-owner, and plain format), the dump file includes the following:
--
-- TOC entry 1623 (class 1259 OID 17618)
-- Dependencies: 5 1624
-- Name: transaction_transaction_id_seq; Type: SEQUENCE; Schema: public;
-- Owner: -
--
[EMAIL PROTECTED] [EMAIL PROTECTED] writes:
lo_creat fails with:
ERROR: large object oid already exists
Well, does it?
What PG version is this? I'm suspicious that it's pre-8.1 and the
problem is that your OID counter has wrapped around to a region that
is already pretty densely
Tom
You hit the nail on the head actually! Further delving into the issue
revealed that but I hadnt had a chance to post.
We recently used copy to put some lobs back into the database, about 70k of
them. All of these lobs were assigned sequential oids incremented by 1. We
then wrapped around
Thank you both!! That got me back up and running ( for now ). Hopefully
this will give me enough enough time to finish the migration next week.
Thanks again!!
On Jan 4, 2008 4:21 PM, Alvaro Herrera [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] wrote:
Is there anyway to force 8.0 to start at
[EMAIL PROTECTED] wrote:
Is there anyway to force 8.0 to start at a specific value? That would at
least get us back up and running.
Stop the system (cleanly) and use pg_resetxlog -o.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication,
Chris Browne wrote:
In contrast, Slony-I regenerates all the indexes on a given table in a
one swell foop fashion, which might be expected to allow cacheing to
provide a bit better performance than you could get with pg_dump |
psql.
I'm left wondering whether the swoop is actually fell. I
[EMAIL PROTECTED] [EMAIL PROTECTED] writes:
Is there anyway to force 8.0 to start at a specific value? That would at
least get us back up and running.
You're in luck: 8.0's pg_resetxlog has the -o switch, so just shut down
(cleanly), use pg_resetxlog to put the OID counter where you want it,
Hi,
In the TODO
http://www.postgresql.org/docs/faqs.TODO.html
there's an item for the server to suggest indexes:
SHOW/SET
Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM
ANALYZE, and CLUSTER
I haven't seen this mentioned on the list recently but I'd just like
to add a
I think select pg_backend_pid(); will do that.
Perfect. I tried googling but I didn't try \df *pid* which would have found it
I tried to figure out the pg_stat_activity, but I can't think of a
WHERE condition that would make it give me the info I wanted.
Thx again to everyone.
Webb Sprague wrote:
Hi all,
Is there a way to determine the pid of a database connection from
within that connection?
As a hypothetical example, I would like to be able to do the following:
$ps x
PID TTY STAT TIME COMMAND
11674 ?S 0:00 sshd: [EMAIL PROTECTED]/1
11675
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Fri, 4 Jan 2008 14:59:47 -0800
Webb Sprague [EMAIL PROTECTED] wrote:
Hi all,
Is there a way to determine the pid of a database connection from
within that connection?
As a hypothetical example, I would like to be able to do the
following:
Hi all,
Is there a way to determine the pid of a database connection from
within that connection?
As a hypothetical example, I would like to be able to do the following:
$ps x
PID TTY STAT TIME COMMAND
11674 ?S 0:00 sshd: [EMAIL PROTECTED]/1
11675 pts/1Ss 0:00
Joshua D. Drake wrote:
On Fri, 4 Jan 2008 14:59:47 -0800
Webb Sprague [EMAIL PROTECTED] wrote:
Hi all,
Is there a way to determine the pid of a database connection from
within that connection?
As a hypothetical example, I would like to be able to do the
following:
$ps x
PID TTY STAT
On Fri, Jan 04, 2008 at 06:11:40PM -0300, Alvaro Herrera wrote:
swooping elephants must be an interesting sight. If pigs can fly ...
Is this what you had in mind?
http://www.amoeba.com/dynamic-images/blog/dumbo.gif
A
---(end of broadcast)---
Everything I'm doing involves only 8.1. I don't have any 8.2 instances at
all...
Oops, just I just got a message from my tool's developer that the tool uses
the 8.2 pg_dump no matter what actual PostgreSQL version it's working on.
Sigh.
~ Thanks
~ Ken
-Original Message-
From:
I built a new system and installed 8.3-beta4. While i'm testing it i
noticed a big performance gap between the production system running
8.2.5 and the new one. The new one, in spite of being much better, is
much slower.
The same insert query takes 20 minutes in the production system and 2
hours
We need some advice on how to handle some large table autovacuum
issues. One of our 8.1.2 autovacuums is launching a DB-wide
vacuum on our 270GB database to prevent xid wrap-around, but is
getting hung-up and/or bogged down for hours on a 40gb table and
taking the server performance down with
On Jan 4, 2008 6:38 PM, Ed L. [EMAIL PROTECTED] wrote:
We need some advice on how to handle some large table autovacuum
issues. One of our 8.1.2
First of all, update your 8.1 install to 8.1.10. Failing to keep up
with bug fixes is negligent. who knows, you might be getting bitten
by a bug
On Friday 04 January 2008 6:21 pm, Scott Marlowe wrote:
Have you tried adjusting the
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-1 credits
#vacuum_cost_page_miss = 10 # 0-1 credits
#vacuum_cost_page_dirty = 20
On Friday 04 January 2008 6:21 pm, Scott Marlowe wrote:
On Jan 4, 2008 6:38 PM, Ed L. [EMAIL PROTECTED] wrote:
We need some advice on how to handle some large table
autovacuum issues. One of our 8.1.2
First of all, update your 8.1 install to 8.1.10. Failing to
keep up with bug fixes is
Clodoaldo [EMAIL PROTECTED] writes:
The same insert query takes 20 minutes in the production system and 2
hours in the new one.
Hmph. It's the same plan, so it's not a planner mistake. Could you
post EXPLAIN ANALYZE rather than just EXPLAIN for both cases?
Also, it'd be worth watching vmstat
On Jan 4, 2008 7:41 PM, Ed L. [EMAIL PROTECTED] wrote:
On Friday 04 January 2008 6:21 pm, Scott Marlowe wrote:
On Jan 4, 2008 6:38 PM, Ed L. [EMAIL PROTECTED] wrote:
We need some advice on how to handle some large table
autovacuum issues. One of our 8.1.2
First of all, update your
Ed L. wrote:
On Friday 04 January 2008 6:21 pm, Scott Marlowe wrote:
On Jan 4, 2008 6:38 PM, Ed L. [EMAIL PROTECTED] wrote:
We need some advice on how to handle some large table
autovacuum issues. One of our 8.1.2
First of all, update your 8.1 install to 8.1.10. Failing to
keep up with bug
D. Dante Lorenso [EMAIL PROTECTED] writes:
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
regression=# select null IS NOT DISTINCT FROM 42;
?column?
--
f
(1 row)
regression=# select null IS NOT DISTINCT FROM null;
?column?
--
t
(1 row)
All,
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
I have a BIGINT column which might contain NULL values. I want to pass
a value to compare with that column in my WHERE clause. If the value
I'm comparing is 0, I want it to match the NULL values. Here
Tom Lane wrote:
D. Dante Lorenso [EMAIL PROTECTED] writes:
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
If the value I'm comparing is 0, I want it to match the NULL values.
[ raised eyebrow... ] Sir, you need to rethink your data
representation.
Tom,
Tom Lane wrote:
D. Dante Lorenso [EMAIL PROTECTED] writes:
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
regression=# select null IS NOT DISTINCT FROM 42;
?column?
--
f
(1 row)
regression=# select null IS NOT DISTINCT FROM null;
?column?
On Jan 4, 2008 7:29 PM, Ed L. [EMAIL PROTECTED] wrote:
On Friday 04 January 2008 6:21 pm, Scott Marlowe wrote:
Have you tried adjusting the
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-1 credits
#vacuum_cost_page_miss =
D. Dante Lorenso wrote:
But if I want to find all the items which are not in any folder, I want:
SELECT *
FROM mytable
WHERE folder_id IS NULL;
I don't have any folder_id 0, so on a URL I might do this:
http://xyz/page.php?fid=123
http://xyz/page.php?fid=0
Why not just have fid
D. Dante Lorenso [EMAIL PROTECTED] writes:
Here's what I'm doing, tell me if I'm crazy:
The column I'm comparing to is 'folder_id'. The folder_id column is a
foreign key to a folder table. If folder_id is NULL, the row is not in
a folder.
Yup, you're crazy. The best interpretation of
81 matches
Mail list logo
