Hello all
I use a crontab job to backup and restore my database every midnight every
day (using pg_dump and restore...)
My cron job run well for a while. However, today I discovered that my
database was not restored for one month.
I also fix the problem as there is corruption in the script.
My
I'm trying to remove a schema and move all the tables to another schema.
I've manually run alter table on every table to move them, however all
the foreign keys still reference the old schema, and there are too many
to do by hand.
Is there an easy way to update one of the system catalogs to do
On Mon, 6 Aug 2007, Rodrigo Faccioli wrote:
I'm a new user's postgreSQL and I have a question: Is possible add
function of intelligent System like Neural Networks or Fuzzy Logic within
postgre SGDB?
Inherently, no. But, there is a fellow at a Bulgarian university that is
developing a fuzzy S
On 8/6/07, Rodrigo Faccioli <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm a new user's postgreSQL and I have a question: Is possible add function
> of intelligent System like Neural Networks or Fuzzy Logic within postgre
> SGDB?
>
> For example: I have a database and I want to execute a select instructi
Hi,
I'm a new user's postgreSQL and I have a question: Is possible add function
of intelligent System like Neural Networks or Fuzzy Logic within postgre
SGDB?
For example: I have a database and I want to execute a select instruction.
But, this select constains there is column that uses a Percep
I have a table that creates "check-out" records that stores information when
a particular resource is being utilized. I want to maintain a friendly
shortened ID so people can reference these check outs.
At any given time, there should not be more than 99 or so check-outs, so
as the check-outs
Henrik Zagerholm <[EMAIL PROTECTED]> writes:
> At what point does the planner choose seq scans?
When it thinks it's cheaper than the other way. There's no hard and
fast answer. The immediate problem you've got is that the estimated
size of the tbl_file/tbl_filetype_suffix join is off by a factor
"Keaton Adams" <[EMAIL PROTECTED]> writes:
> Auto vacuum is not enabled:
Hmph. That shoots down the one semi-plausible idea I had about how you
got into this state. It seems that the unexpected limit_datname value
must have come from flatfiles.c, but that *certainly* won't pick up
any database w
6 aug 2007 kl. 17:31 skrev Tom Lane:
Henrik Zagerholm <[EMAIL PROTECTED]> writes:
WHERE file_indexed IS FALSE
AND file_copied
IS TRUE
AND file_siz
Hello,
> I am thinking about how to model tags for a web site in the database.
> Is tsearch2 the way to do it?
Yes, tsearch2 is the ultimate thing for all your text work in the PostgreSQL.
> Each site entry could then store its tags
> in a tsvector. That would make me able to perform partial mat
On Monday 06 August 2007 2:11 pm, Tom Lane wrote:
> "Ed L." <[EMAIL PROTECTED]> writes:
> > On Monday 06 August 2007 1:22 pm, you wrote:
> >> You really ought to be using something newer than 8.1.2.
> >
> > Perhaps. But we have yet to find a way to make major
> > version upgrades of 100+ GB,
>
> I
"Ed L." <[EMAIL PROTECTED]> writes:
> On Monday 06 August 2007 1:22 pm, you wrote:
>> You really ought to be using something newer than 8.1.2.
> Perhaps. But we have yet to find a way to make major version
> upgrades of 100+ GB,
I did not suggest a major version upgrade.
On Aug 6, 2007, at 14:44 , Ranieri Mazili wrote:
1) Can I use a function that will return a string in a where clause
like bellow?
select *
from table
where my_function_making_where()
and another_field = 'another_think'
Probably. What have you tried? What does the documentation say
Auto vacuum is not enabled:
#---
# AUTOVACUUM PARAMETERS
#---
#autovacuum = off # enable autovacuum subprocess?
#autovacuum_napt
Hello,
I have 2 questions.
1) Can I use a function that will return a string in a where clause like
bellow?
select *
from table
where my_function_making_where()
and another_field = 'another_think'
2) Can I use a function that will return a string to return the list of
columns that I
On Aug 6, 2007, at 13:17 , Perry Smith wrote:
I'm using config.active_record.schema_format = :sql. I like the
idea of constraints in the db -- I know that goes against a lot of
rails ideas.
I think most who choose Postgres come to the same conclusion. Though
do take care not to confuse R
On Monday 06 August 2007 1:22 pm, you wrote:
> "Ed L." <[EMAIL PROTECTED]> writes:
> > We're seeing some unexpected behavior in one particular
> > 64-bit Pgsql 8.1.2 running on HP-UX 11.23 and Itanium 2,
> > built with --enable-thread-safety. We think we are seeing
> > concurrent select-for-update
Trying to track down why a query executed from a web page
is much slower than same query executed from psql. I guess
this is more of a Perl/DBI question, but I thought there might
be more people here with answers than in a Perl group.
I have set up indexes on a table to allow a fast search with
--- "korry.douglas" <[EMAIL PROTECTED]> wrote:
> execution count (number of times each statement is executed)
> total execution time (how long did we spend executing each
> statement?)
> longest execution time (how long did the slowest iteration
> take?)
> number of scans (total n
What is the best tool for an app to profile procedural code in
postgres? I want to instrument my code and trace it so that I can
see which code bits are sucking up the cpu and time. I know I can
stick messages in the code and gather my own stats but I was
wondering is there are any good tools
"Ed L." <[EMAIL PROTECTED]> writes:
> We're seeing some unexpected behavior in one particular 64-bit
> Pgsql 8.1.2 running on HP-UX 11.23 and Itanium 2, built
> with --enable-thread-safety. We think we are seeing concurrent
> select-for-updates of the same rows by multiple concurrent
> backend
I tried '\r\n' in my plperl function to check for a newline character in the
table and its working fine.
Also, would E'\n' work ?
I really did not understand if word newline is a key word for a newline
character in postgres.
Thanks,
~Jas
On 8/6/07, Jeff Davis <[EMAIL PROTECTED]> wrote:
>
> On Mo
"Keaton Adams" <[EMAIL PROTECTED]> writes:
> Do I have this right?
It's not clear. You didn't answer the question about whether you were
running autovac, but even if you were I'm not sure why you got the
complaint about template0. AFAICS the only way for template0 to be
installed as oldest_datna
I ran a simple select-for-update test on 8.1.2 and was curious as
to why the semantics are what they are. Specifically, when you
have multiple select-for-update-limit queries on the same rows,
why are rows selected by the blocked query before knowing if
some of those rows will be removed/elimi
On Sat, 2007-08-04 at 23:22 -0700, RPK wrote:
> I am a beginner. I want to read the actual working of PGSQL right from the
> start when it is installed. What it does in the background when a new
> database is created, insert are issued and when the records are finally
> committed. I want to read th
We're seeing some unexpected behavior in one particular 64-bit
Pgsql 8.1.2 running on HP-UX 11.23 and Itanium 2, built
with --enable-thread-safety. We think we are seeing concurrent
select-for-updates of the same rows by multiple concurrent
backends, contrary to our understanding of select-fo
We needed the DB up right away; one of the operations guys connected
through a standalone backend to vacuum template0 and now the DB is up
again:
"sudo -u postgres /mxl/var/pgsql/bin/postgres -D /mxl/var/pgsql/data -O
-P template0
Once I got in, I just ran a VACUUM VERBOSE ANALYZE; to "r
On Mon, 2007-08-06 at 13:28 -0400, Jasbinder Singh Bali wrote:
> Hi,
> Can anyone please tell me what is the character for a new line in
> postgres ?
> I mean how does a new line get stored in postgres ?
> Is it "\n" or "\\n" or something else ?
>
You can just put the newline directly in the SQL:
On Aug 6, 2007, at 12:28 , Jasbinder Singh Bali wrote:
Can anyone please tell me what is the character for a new line in
postgres ?
I mean how does a new line get stored in postgres ?
Is it "\n" or "\\n" or something else ?
Newlines (ASCII 10) are stored as the newline character in the
da
On 8/6/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> the last few years we ran with horizontal partitioning. i always ran into
> problems with horizontal partioning because few tables must be shared across
> the databases and sometimes things are moving and i got lot of trouble with
> my prima
On Aug 5, 2007, at 18:58 , Perry Smith wrote:
I find it odd that you are resistant to the idea. To me, the
advantages are clear if it can be done without a tremendous amount
of work. Why horse around with the environment variables to set
such things as password, call sh, just to call pg_d
Hi,
I have a simple question that I can't find the answer to in the
documentation: how do I backup & restore a single database, ie. when
going from one cluster to another? I'm fairly comfortable with the
command line so I can create and restore backups but things do not
always work as exp
Hi,
Can anyone please tell me what is the character for a new line in postgres ?
I mean how does a new line get stored in postgres ?
Is it "\n" or "\\n" or something else ?
Thanks,
Jas
Oleg Bartunov writes:
Francisco,
you need to say us standard information about pg version
Postgresql 8.2 running in FreeBSD.
error message you got.
ERROR: operator does not exist: text || tsvector
Also, it'd be worth to show simplified version of your function, which
demonstrates your
Tom Lane writes:
This is not a particularly good way of accomplishing partitioning,
as you'll be needing *every* part of your application to be explicitly
aware of the exact partitioning scheme.
I am trying to follow the presentation at PGcon2007 by Robert Treat.
I created a master table, then
Thank you guys! But now i am clueless as before. please, enlighten me:
i need about 200 concurrent db connections at peak time and - at the moment -
i only have cheap hardware (2-4 GB Ram, Dual Opteron CPU, SATA Disks, RAID 1)
My database has a size of 11 GigaByte, largest table has 100.000.000
Hi all,
What is the best tool for an app to profile procedural code in
postgres? I want to instrument my code and trace it so that I can
see which code bits are sucking up the cpu and time. I know I can
stick messages in the code and gather my own stats but I was
wondering is there are any good
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> 2. IIRC there is a bug in 8.1.4 that causes template0 to increment when
> it shouldn't. Update the template0 to allow connections, vacuum full,
> dissallow connections, and then upgrade.
That should be "vacuum freeze" but otherwise this is the correc
Following up on this issue --
I found resolution to the problems that I was having, with one error
remaining when I work on the database restore (somewhat expected error
really, and one I no longer need any assistance with).
The initial problems I was having were from running pg_dump on the Solar
"Keaton Adams" <[EMAIL PROTECTED]> writes:
> Sorry, I should have mentioned that to begin with: psql 8.1.4
Hmm, you know that's pretty old don't you? The latest release in
that branch is 8.1.9 (and there'll be an 8.1.10 before long).
> It's set to false.
> mxl=3D# \c template0;
> FATAL: databa
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Keaton Adams wrote:
> Sorry, I should have mentioned that to begin with: psql 8.1.4
1. Upgrade to the actual stable release, which is 8.1.9
2. IIRC there is a bug in 8.1.4 that causes template0 to increment when
it shouldn't. Update the template0 to a
"Keaton Adams" <[EMAIL PROTECTED]> writes:
> So it is odd that over 14,000 XIDs are being consumed on template zero
> in just over one minute?
You misunderstand what you are looking at --- that's the cluster-wide
XID consumption rate, not any one database's.
regards, tom l
Sorry, I should have mentioned that to begin with: psql 8.1.4
mxl=# select datname, datallowconn from pg_database;
datname| datallowconn
--+--
postgres | t
.
.
.
template1| t
template0| f
(7 rows)
It's set to false.
mxl=# \c template0;
FATAL: da
"Keaton Adams" <[EMAIL PROTECTED]> writes:
> Our production DB shut down because of it:
[ squint... ] Which PG version is this exactly? Is template0 marked as
datallowconn in pg_database, or not?
regards, tom lane
---(end of broadcast)---
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Reinhard Max wrote:
> On Fri, 3 Aug 2007 at 19:09, Jorge Godoy wrote:
>
>> Having updated packages would be great! Specially if they would be
>> updated for, e.g., OpenSuSE 10.1 and OpenSuSE 10.2 (both already
>> released) and not just for OpenSuSE
On Sunday 05 August 2007 21:43, snacktime wrote:
> I've been going back and forth on the best way to model this.
>
> A user can have one to many bill and ship addresses.
> An order can have one bill address and one to many ship addresses
>
> Let's assume I have a single address table, with an addre
So it is odd that over 14,000 XIDs are being consumed on template zero
in just over one minute?
bash-3.00$ date; psql -dpostgres -frun.sql
Mon Aug 6 10:18:12 MDT 2007
datname|age
--+
postgres | 1074342864
.
.
.
template1| 1074269488
template0|
Our production DB shut down because of it:
<2007-08-06 09:44:08 MDT>LOG: background writer process (PID 24439)
exited with exit code 0
<2007-08-06 09:44:08 MDT>LOG: terminating any other active server
processes
<2007-08-06 09:44:08 MDT>LOG: all server processes terminated;
reinitializing
<2007-
"Keaton Adams" <[EMAIL PROTECTED]> writes:
> Our template 0 database has an XID issue:
No, it doesn't. template0 never needs vacuuming.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
Our template 0 database has an XID issue:
postgres=# select datname, age(datfrozenxid) from pg_database;
datname|age
--+
postgres | 1073965753
.
.
.
template1| 1073892377
template0| 2146706522
(7 rows)
When we try a vacuum full,
Henrik Zagerholm <[EMAIL PROTECTED]> writes:
> WHERE file_indexed IS FALSE
> AND file_copied IS TRUE
> AND file_size < (1024)
>
"Henrik Zagerholm" <[EMAIL PROTECTED]> writes:
> Ahh, my bad. It is a very small table but I have an unique index.
> CREATE UNIQUE INDEX tbl_filetype_suffix_idx ON tbl_filetype_suffix
> USING btree (filetype_suffix);
Well it can't use that to help with a join. If you had an index on
lower(filet
>>If you know for a fact that there are no more statements in processing,
there's
>>no need to call PQgetResult() any more.
thanks, that is what I thought.
>>What poll? PQconsumeInput()/PQisBusy() _is_ the poll.
comsumeInput and isBusy is not a poll. A poll tells you if data is ready.
When data
6 aug 2007 kl. 15:07 skrev Gregory Stark:
"Henrik Zagerholm" <[EMAIL PROTECTED]> writes:
Hi list,
I'm having a weird acting query which simply retrieves some files
stored in a db
which are related to a specific archive and also has a size lower
than 1024
bytes.
Explain analyze below. T
Francisco Reyes <[EMAIL PROTECTED]> writes:
> v_sql := 'INSERT INTO messagecatalog_'||
> to_char(NEW.timestampfield,'')||
> '(field1, field2) values ('
> ||New.field1||','||New.field2||')
> ')';
> EXECUTE v_sql;
This is not a particularly good way of accomplishing partitioning
> This has been fixed for 8.2.5 and 8.3
Splendid, thanks for the reply. Is there a list of stuff to be done before
8.2.5 anywhere?
> In the meantime, can you delay your app startup, perhaps by sleeping for a
> few seconds in a batch file?
Yeah, we can put a quick fix in now, safe in the knowle
In response to "Brendon Sablinski" <[EMAIL PROTECTED]>:
> I have a need to do asynchronous query processing. I am a little confused
> by the order and frequency of calls. Can someone tell me if the below is
> correct? The code comments are littered with my questions.
>
> PQsendQueryParams(pgco
"Henrik Zagerholm" <[EMAIL PROTECTED]> writes:
> Hi list,
>
> I'm having a weird acting query which simply retrieves some files stored in a
> db
> which are related to a specific archive and also has a size lower than 1024
> bytes.
> Explain analyze below. The first one is with seq-scan enabled
Hi,
Markus Schiltknecht wrote:
I've done that (zeroing out the pg_toast table page) and hope
> the running pg_dump goes through fine.
Unfortunately, pg_dump didn't go through. I already did some REINDEXing
and VACUUMing. Vacuum fixed something (sorry, don't I recall the
message), but SELECTi
Hi list,
I'm having a weird acting query which simply retrieves some files
stored in a db which are related to a specific archive and also has a
size lower than 1024 bytes.
Explain analyze below. The first one is with seq-scan enabled and the
other one with seq-scans disabled. The weird thi
Hi,
David Fetter wrote:
Very few people actually need synchronous replication, and those who
do buy Oracle's RAC (and curse it) or use DB2's offering (and also
curse it ;). For most purposes, fast asynchronous replication is good
enough.
While this is certainly true, please keep in mind that
On Fri, 3 Aug 2007 at 19:09, Jorge Godoy wrote:
> Having updated packages would be great! Specially if they would be
> updated for, e.g., OpenSuSE 10.1 and OpenSuSE 10.2 (both already
> released) and not just for OpenSuSE 10.3 (still under
> "development").
Yes, that's why I created the /pub/p
I have a need to do asynchronous query processing. I am a little confused
by the order and frequency of calls. Can someone tell me if the below is
correct? The code comments are littered with my questions.
PQsendQueryParams(pgconn, "SELECT ..", );
while(1)
{
epoll/poll/select PQsocket(pg
Thanks Ian that helped.
Couple related questions. #1, the same warning is issued by pgadmin III but
the extra instrumentation does seem to have been installed by adminpack.sql;
I can see server status on the ubuntu box from an admin tool installed on a
windows box, where I couldn't before.
#2 I
M S wrote:
> Hi, I'm getting errors in my application which suggest that Postgres has not
> completely started
> when it has told the Windows Service Control Manager that is has.
>
> Postgres log file extracts which (I believe) support this are shown below:
>
> 2007-08-06 10:27:42 LOG: database
> I would like to know what should i do to import the content (not all columns)
> of a XLS file into pgsql.
> is there something special to do ?
Simplest way is to save it as a TAB delimited file and then look at the COPY
command? Or if it needs more complicated processing... you could write a
pe
Hi, I'm getting errors in my application which suggest that Postgres has not
completely started
when it has told the Windows Service Control Manager that is has.
Postgres log file extracts which (I believe) support this are shown below:
2007-08-06 10:27:42 LOG: database system was shut down at
Hi,
I would like to know what should i do to import the content (not all
columns) of a XLS file into pgsql.
is there something special to do ?
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.2.4
PHP 5.2.1
On Mon, Aug 06, 2007 at 11:26:18AM +0200, Henrik Zagerholm wrote:
> Hi list,
>
> I'm running 8.2.4 and I've started to get these messages and even
> though I googled for some answers I couldn't find any good info
> against the 8.2 code base.
>
> 2007-08-05 04:00:58.815 CEST LOG: checkpoints
Hi list,
I'm running 8.2.4 and I've started to get these messages and even
though I googled for some answers I couldn't find any good info
against the 8.2 code base.
2007-08-05 04:00:58.815 CEST LOG: checkpoints are occurring too
frequently (17 seconds apart)
2007-08-05 04:00:58.815 CES
Francisco,
you need to say us standard information about pg version, error message you
got. Also, it'd be worth to show simplified version of your function, which
demonstrates your problem.
Oleg
On Mon, 6 Aug 2007, Francisco Reyes wrote:
I am trying to create a function to handle inserts in a
71 matches
Mail list logo
