Craig Ringer writes:
> These reports seem to come up a bit, with disk full issues resulting in
> the need to pg_resetxlog, dump, and re-initdb, but I wouldn't be too
> shocked if they all turned out to be on xfs or something like that.
Well, there are cases where that might actually be the best s
Adam B wrote:
> Hello all,
>
> Is it possible to prevent Postgre from aborting the transaction upon a
> constraint violation?
Not without use of savepoints.
What I like to do is bulk-insert the suspect data into a temp table
without constraints, then INSERT INTO ... SELECT it into the target
ta
Tom Lane wrote:
> Craig Ringer writes:
>> I've been wondering about this for a while. Why does Pg end up with the
>> database in an unusable, unrecoverable state after a disk-full error?
>
> It doesn't. There must have been some other filesystem misfeasance
> involved in the OP's problem.
Cool
How do I view the result set of a function that returns a refcursor in
pgAdmin?
I am trying to test it in pgadmin my calling it like this, but I can't see
the result set, it only says:
Query result with 1 rows discarded.
Query result with 328 rows discarded.
Query returned successfully with no r
Perhaps I'm doing something wrong. I'm consistently taking over 20s for
the following test case. (Without savepoints it takes under 10s)
CREATE TABLE lots2
(
lid serial NOT NULL,
"name" character varying(64),
CONSTRAINT lots2pk PRIMARY KEY (lid),
CONSTRAINT lots2_unique_name UNIQUE (nam
Hi,
Tomas Vondra wrote:
$conn = pg_connect(...);
$res = pg_query("SELECT mime, thumbnail_data FROM images WHERE filename
= ");
$row = pg_fetch_assoc($row);
header('Content-Type: ' . $row['mime']);
echo pg_unescape_bytea($row['thumbnail_data']);
PHP?
Try running your script in a PHP debu
I'm intrigued by this solution, Johan. It might be just the ticket!
I'll do some benchmarks when I have time in a week or so.
Johan Nel wrote:
Adam
B wrote:
Hello all,
Is it possible to prevent Postgre from aborting the transaction upon a
constraint violation?
>From th
Strange indeed. Perhaps there's some background stuff happening that
messes with the results (auto VACUUM?).
In my mind, however, it makes sense that it would take longer: 2 extra
operations against the server (save&release).
Thomas Kellerer wrote:
Adam B
wrote on 01.05.2009 22:59:
Perh
Steve Atkins writes:
> On May 1, 2009, at 2:42 PM, David Wall wrote:
>> Does anybody know if PG will perform better with the table join
>> instead of evaluating the series of OR/IN? The OR/IN has to be
>> parsed, but the comparisons may be faster than the table join.
> It used to be that pop
Adam B wrote on 01.05.2009 22:59:
Perhaps I'm doing something wrong. I'm consistently taking over 20s for
the following test case. (Without savepoints it takes under 10s)
That's really strange. I can reproduce your results on my computer (25 vs. 65
seconds).
When running my import progra
On May 1, 2009, at 2:42 PM, David Wall wrote:
(quoted from Chris)
Select field1,field2 FROM table1 inner join relationships on
table1.creator_user_id = relationships.employee WHERE
relationships.manager = ?
(quoted from Steve)
select table1.field1, table2.field2 from table1, reports where
Cygwin comes with rsync on Windows.
On 02/05/2009, at 4:06 AM, John R Pierce wrote:
Joshua D. Drake wrote:
Well that's just it. Out of the box it doesn't actually work.
PostgreSQL
only gives you the facilities to roll your own PITR solution. You can
look at PITR Tools:
https://projects.comm
(quoted from Chris)
Select field1,field2 FROM table1 inner join relationships on
table1.creator_user_id = relationships.employee WHERE
relationships.manager = ?
(quoted from Steve)
select table1.field1, table2.field2 from table1, reports where
table1.creator_user_id = reports.peon and report
Adam B wrote on 01.05.2009 19:50:
I realize that I could set a save-point before every INSERT but that
nearly doubles the processing time.
That's interesting.
I did a quick test with JDBC inserting 500,000 rows and the time when using a
savepoint for each INSERT was not really different to t
Adam B wrote:
Hello all,
Is it possible to prevent Postgre from aborting the transaction upon a
constraint violation?
From the help files maybe the following could get you on the right track:
This example uses exception handling to perform either UPDATE or INSERT,
as appropriate:
CREATE TAB
On Fri, May 1, 2009 at 12:06 PM, John R Pierce wrote:
> Joshua D. Drake wrote:
>>
>> Well that's just it. Out of the box it doesn't actually work. PostgreSQL
>> only gives you the facilities to roll your own PITR solution. You can
>> look at PITR Tools:
>>
>> https://projects.commandprompt.com/pub
A separate table for managing the relationships. One column for the manager
and one for employee.
Then you end up with a query like this.
Select field1,field2 FROM table1 inner join relationships on
table1.creator_user_id = relationships.employee WHERE relationships.manager
= ?
_
Fr
On May 1, 2009, at 10:49 AM, David Wall wrote:
We have a database report function that seemed clean when the number
of users was small, but as the number of users grow, I was wondering
if anybody had any good ideas about how to handle OR or IN for
SELECTs.
The general scenario is that a
- "Joshua D. Drake" wrote:
> On Fri, 2009-05-01 at 09:22 -0700, PostGre Newbie wrote:
> > Hi everyone,
>
> > I searched the web and I know I should get a snapshot of the
> > filesystem and then backup the WAL archive logs but this is the
> part
> > where I am confused. I do not know of any
We have a database report function that seemed clean when the number of
users was small, but as the number of users grow, I was wondering if
anybody had any good ideas about how to handle OR or IN for SELECTs.
The general scenario is that a manager runs reports that list all
records that were
Hello all,
Is it possible to prevent Postgre from aborting the transaction upon a
constraint violation?
Using JDBC if I catch the constraint violation and try another statement
I get:
/ERROR: current transaction is aborted, commands ignored until end of
transaction block/
I realize that I
Joshua D. Drake wrote:
Well that's just it. Out of the box it doesn't actually work. PostgreSQL
only gives you the facilities to roll your own PITR solution. You can
look at PITR Tools:
https://projects.commandprompt.com/public/pitrtools
It doesn't quite work on Windows due to lack of rsync and
On Friday 01 May 2009, PostGre Newbie wrote:
>I do not know of any open source backup utilities
> that can take snapshots of the filesystem. I get the overall concept
> of online backups but I am still unclear EXACTLY how the system works.
> I would be grateful if anyone could explain it to me. I
On Fri, 2009-05-01 at 09:47 -0700, Ben Chobot wrote:
> On Fri, 1 May 2009, PostGre Newbie wrote:
>
> > I know that taking the backup of the whole database every 10 minutes
> > is a very bad way to go about it but until I find a better way to do
> > it, it will have to do for now. Any suggestions/t
On Fri, 2009-05-01 at 09:22 -0700, PostGre Newbie wrote:
> Hi everyone,
> I searched the web and I know I should get a snapshot of the
> filesystem and then backup the WAL archive logs but this is the part
> where I am confused. I do not know of any open source backup utilities
> that can take sna
On Fri, 1 May 2009, PostGre Newbie wrote:
I know that taking the backup of the whole database every 10 minutes
is a very bad way to go about it but until I find a better way to do
it, it will have to do for now. Any suggestions/tips/articles on how
to do the backup would be appreciated very much
Hi everyone,
I am new to PostGreSql but I have to use it at work. I have to take
backups of the database every 10 mins so that work isn't lost in case
of an accident. I use PostGreSql v8.1 on Windows XP.
I have already set up a system that automatically takes the backup of
the database every 10 m
Craig Ringer writes:
> Note that it's not very likely that PostgreSQL was the process that used
> up all your memory. It was just unlucky enough to be picked as the one
> to be killed, because the OOM killer is terrible at estimating which
> process is using the most memory when programs like Post
Wow, a response from the famous Tom Lane to my lame problem :-) .
> What I'd try is a "ping" to the database server, and not initiate any
> libpq operation unless the server is answering pings. If you get
> a failure due to connectivity loss midway through an operation,
> PQreset is appropriate t
"Raymond O'Donnell" writes:
> On 30/04/2009 10:01, Phil Couling wrote:
>> If so am I right to assume that, if the function is used too frequently,
>> it could cause the database to crash by wraping OIDs?
> I'd imagine that this depends on how often the database is VACUUMed.
Wrapping around the O
Craig Ringer writes:
> I've been wondering about this for a while. Why does Pg end up with the
> database in an unusable, unrecoverable state after a disk-full error?
It doesn't. There must have been some other filesystem misfeasance
involved in the OP's problem.
regard
Hi, it seems it didn't make it the first time.
Début du message réexpédié :
De : Dimitri Fontaine
Date : 30 avril 2009 12:03:10 HAEC
À : pgsql-general@postgresql.org
Objet : Rép : [GENERAL] triggers and execute...
On Monday 27 April 2009 22:32:22 Scott Marlowe wrote:
OK, I'm hitting a wall he
Peter Geoghegan writes:
> I'm developing a PostgreSQL application for Windows CE 5 on a PDT/ PDA
> in C++/Qt, using Hiroshi Saito's libpq port for that platform. Since
> the connection is established over wi-fi, and wi-fi connectivity is
> often flaky, I feel that I have to "fail gracefully" to as
On Wed, Apr 29, 2009 at 07:54:20AM -0700, DaNieL wrote:
> ERROR: syntax error at or near "IF"
>
> Where am i mistaken?
>
> p.s: dont focus on the example functionality, its just a trial for me
> to understand the transactions.. and now, the IF clause...
As others have said, IF statements are on
In response to Craig Ringer :
> Bill Moran wrote:
>
> > Sounds like you're reinventing message digests ...
[snip your comments about why I was wrong about MDs working]
> So long as I don't call it "xor encryption" ... sigh.
>
> > Most of the systems I've seen like this do one of a few things:
It now appears that I was presumptuous in blaming libpq for my
application's shutdown. It probably was down to a problem with the way
I was remote debugging the application - a fluke.
I can now get the message to appear, and then re-establish a
connection by either disconnecting and reconnecting t
Peter Geoghegan wrote:
> though, but it does. My guess is that libpq is calling abort() or
> something similar, directly or indirectly. I know that would cause an
> error message with windows XP, but windows CE is funny.
Maybe this is a stupid question (I don't really do WinCE) but ... can't
you
Toomas Vendelin wrote:
I'm writing CGI scripts in Perl using Postgresql via DBI interface.
RAISE_ERROR is on.
For some reason (unlike with MySQL), when a Perl script dies from
Postgresql error, the line number of Perl script where the error
occurred is not reported, just the SQL s
Bill Moran wrote:
> Sounds like you're reinventing message digests ...
Message digests do not guarantee non-colliding output for a given input.
They provide a result UNLIKELY to collide for any reasonable set of
inputs. They need to produce quite large output values to do this
effectively. Trunc
I apologise for the duplicate posts - my initial post wasn't appearing
on the mailing list archives hours later, and someone in #postgresql
said that there was a problem with the service provider, so I thought
I'd resend.
> such an approach is doomed to failure, you have just implemented a
> race
Any idea why xml support is off ?
The libxml2, version 2.6.23 is there on centos4.7 (which is what I am
using), is there any known problem with xml that it is off, or is just
because they wanted to make sure that the package is going to work in
versions prior to 4.7 as well ??
any ideas ?
--
GJ
In response to Jasen Betts :
> On 2009-04-30, Craig Ringer wrote:
> > Hi
> >
> > This must be a fairly common requirement, but either I don't know how to
> > ask Google about it or there's not as much out there as I would've expected.
> >
> > I'm looking for a way to map the output from a monoton
> On all our servers we have a cron job that runs daily and reports disk
> usage stats.
> Maybe you need something similar.
Of course. I have Cacti running to monitor disk usage on all my servers.
That doesn't help if a user creates several duplicates of a huge table,
or otherwise gobbles disk s
You can wrap a temporary function in a script and call it this way:
keaton:811:~$more my_shell_script.sh
#!/bin/bash
OS=`uname -s`
PSQL="/usr/bin/psql"
USERNAME="postgres"
export PGPASSWORD="${PASSWORD}"
DATABASE="mydatabase"
${PSQL} "${DATABASE}" -U "${USERNAME}" << EOF
BEGIN;
CREATE OR REPLA
Craig Ringer wrote:
Peter Eisentraut wrote:
On Thursday 23 April 2009 18:30:27 Dan Armbrust wrote:
I had a test system (read as not backed up, sigh) which had the disk
go full while PostgreSQL was loaded, consequently, PostgreSQL will no
longer start.
It is logging an error about detecting an
Could if be referencing the second IF..the one in your "END IF" that doesn't
have a semicolon after it...?
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of DaNieL
Sent: Wednesday, April 29, 2009 9:54 AM
To: pgsql-general@p
On 2009-04-30, Craig Ringer wrote:
> Hi
>
> This must be a fairly common requirement, but either I don't know how to
> ask Google about it or there's not as much out there as I would've expected.
>
> I'm looking for a way to map the output from a monotonically increasing
> sequence (not necessaril
On 2009-04-29, Stefan Sturm wrote:
> Hello,
>
> we are changing the structure of our database from a new release.
> Now we need to export large amounts of data and import it into the new
> db( with a new structure).
>
> Are there any tools( for osx ) to support me doing this?
last time I had to c
Carlo Stonebanks wrote:
> (I suppose one possibility would be something that created a temporary
> stored proc to execute the code, then cleaned up after itself.)
Yep, that's what I do -
CREATE FUNCTION fred() RETURNS blah AS $$
$$ LANGUAGE 'plpgsql';
SELECT fred();
DROP FUNCTION fred();
I've
On 2009-05-01, Carlo Stonebanks wrote:
> One of our developers asked me, "is there any way to execute arbitrary
> plpgsql"? By that I beleive he means: is there some way to execute ad-hoc
> pl/pgsql code without creating a stored procedure or a function?
no. arbitrary SQL is no problem,
arbi
On 2009-04-29, Peter Geoghegan wrote:
> Hello,
>
> I'm developing a PostgreSQL application for Windows CE 5 on a PDT/ PDA
> in C++/Qt, using Hiroshi Saito's libpq port for that platform. Since
> the connection is established over wi-fi, and wi-fi connectivity is
> often flaky, I feel that I have t
Andrew Maclean wrote:
I am using C++ and trying to programatically import a large
object from the client side into a server.
In that context, the simplest way is to use libpq's C functions:
http://www.postgresql.org/docs/8.3/static/lo-interfaces.html
Best regards,
--
Daniel
PostgreSQL
Daniel,
IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
ROLLBACK;
END IF
COMMIT;
i always get the error
ERROR: syntax error at or near "IF"
Where am i mistaken?
SELECT returns in essence a record or setof records.
DECLARE _credit int;
...
SELECT credit FROM users WHERE name = '
On 30/04/2009 10:01, Phil Couling wrote:
> I've just written a search function which creates a temp table, preforms
> some reasoning on it returning results then drops it again.
> I'm using temp tables in an attempt to gain efficiency (not repeating
> work between one section of the function and a
Hi guys, im new with postgresql, and already got my first problem..
Well, I wroted some code for understend how the transaction works,
following step by step the manual.
TO make it short, i've created 2 tables, user and movements: in the
firs one there are the name, email and credit colons, in th
On Fri, May 1, 2009 at 6:25 AM, Carlo Stonebanks
wrote:
> One of our developers asked me, "is there any way to execute arbitrary
> plpgsql"? By that I beleive he means: is there some way to execute ad-hoc
> pl/pgsql code without creating a stored procedure or a function?
>
> I believe MS SQL Serve
On Thu, Apr 30, 2009 at 3:00 PM, paulo matadr wrote:
> Hi all,
> my database entry in mode recovery,
> analyzing my pg_log I seem this:
> system logger process (PID 6517) was terminated by signal 9
> background writer process (PID 6519) was terminated by signal 9
> terminating any other active ser
Tomas Vondra wrote:
> $conn = pg_connect(...);
> $res = pg_query("SELECT mime, thumbnail_data FROM images WHERE filename
> = ");
> $row = pg_fetch_assoc($row);
> header('Content-Type: ' . $row['mime']);
> echo pg_unescape_bytea($row['thumbnail_data']);
PHP?
Try running your script in a PHP d
Greg Smith wrote:
> Normal practice here is to set:
>
> listen_address='*'
>
> So that the server is remotely accessible from all of its interfaces,
> and then you can do all filtering of who can connect just via
> pg_hba.conf instead.
Just to expand on that:
listen_addresses is usually used i
I am using C++ and trying to programatically import a large object
from the client side into a server. I am using QT and, for the server
side I can pass a command like:
insert into x values('x1',lo_import('c:/temp/x1.txt'));
Which works.
However this will not work from the client side.
For the cli
Hi all,
I had a big problem that made me crazy... I want to simple backup some
tables from a my db and restore them to another.
My db:
table_two(
id serial PRIMARY KEY,
...
)
table_one (
id serial PRIMARY KEY,
real_name text NOT NULL,
username text NOT NULL,
id_table_two i
paulo matadr wrote:
> Hi all,
> my database entry in mode recovery,
> analyzing my pg_log I seem this:
>
> system logger process (PID 6517) was terminated by signal 9
> background writer process (PID 6519) was terminated by signal 9
> terminating any other active server processes
You haven't told
One of our developers asked me, "is there any way to execute arbitrary
plpgsql"? By that I beleive he means: is there some way to execute ad-hoc
pl/pgsql code without creating a stored procedure or a function?
I believe MS SQL Server can do this - has any one heard of some sort of
command shel
Could you end the query with a "LIMIT 1"?
SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, r.id AS
reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND
(r.start_date, r.end_date) OVE
John Smithus writes:
> [ 'HADT' is not recognized as a timezone abbreviation ]
> The server is running PostgreSQL 8.3.7 on an AMD64 Gentoo Linux
> machine. The system time zone is set to 'America/Adak' and datestyle
> is set to 'sql, mdy' in postgresql.conf.
Not every timezone abbreviation in the
Peter Eisentraut wrote:
On Thursday 23 April 2009 18:30:27 Dan Armbrust wrote:
I had a test system (read as not backed up, sigh) which had the disk
go full while PostgreSQL was loaded, consequently, PostgreSQL will no
longer start.
It is logging an error about detecting an invalid shutdown, try
On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote:
Oh man, it just gets worse. I really need a simple elegant solution
here, because if I try to build the query by hand null inputs make
life a nightmare. I had built something like this:
q = 'insert into '||schem||'.page_access_'||part||' value
I need a bit of help understanding what might be causing a deadlock.
To duplicate the problem I'm running a test script that forks two
child processes. Each child runs the same transaction and thus the
order of execution is exactly the same. (i.e. not like the typical
deadlock where the order o
On Tue, Apr 28, 2009 at 10:46 PM, David Fetter wrote:
> On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>> wrote:
>> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe
>> > wrote:
>> >> OK, I'm hitting a wall here. I've written
Hi
I've just written a search function which creates a temp table, preforms
some reasoning on it returning results then drops it again.
I'm using temp tables in an attempt to gain efficiency (not repeating
work between one section of the function and another).
However I'm worried that there ma
Greetings:
We are researching implementing a warm backup solution for our existing
databases. We have a two node cluster running RH which are connected to a
SAN. There is a total of 11 database clusters with the two node linux cluster
balancing the load. At the moment, we are not doing any WAL
Hello,
we are changing the structure of our database from a new release.
Now we need to export large amounts of data and import it into the new
db( with a new structure).
Are there any tools( for osx ) to support me doing this?
Thanks for your help,
Stefan Sturm
--
Sent via pgsql-general maili
72 matches
Mail list logo
