Jeff Ross wrote:
I think I'm doing it right. Here's the whole script. I run it from
another server on the lan.
That looks basically sane--your description was wrong, not your program,
which is always better than the other way around.
Note that everything your script is doing and way more i
Marcin Krol wrote:
Result before (1.6G db):
size_in_bytes | relname
---+--
806387712 | cs_ver_digests_pkey
103530496 | oai_edi_atts_pkey
There's your problem. This is called "index bloat"; these are the two
biggest relations in the large and
Amitabh Kant wrote:
You need to do VACUUM FULL ANALYZE to claim the disk space, but this
creates a exclusive lock on the tables.
See http://www.postgresql.org/docs/8.3/static/sql-vacuum.html
First off, you don't need the ANALYZE in there.
Second, VACUUM FULL is a terrible way to fix a table t
Janet S Jacobsen writes:
> Hi. What I see when I do ls on the current (corrupt)
> $PGDATA/global is
> ...
> - rw--- 1 jsjacobs deepsky 0 Feb 8 18:51 1262
> ...
> -rw--- 1 jsjacobs deepsky 602 Feb 12 17:42 pg_auth
> -rw--- 1 jsjacobs deepsky 8192 Feb 12 17:42 pg_control
> -rw-
Hi. What I see when I do ls on the current (corrupt)
$PGDATA/global is
...
- rw--- 1 jsjacobs deepsky 0 Feb 8 18:51 1262
...
-rw--- 1 jsjacobs deepsky 602 Feb 12 17:42 pg_auth
-rw--- 1 jsjacobs deepsky 8192 Feb 12 17:42 pg_control
-rw--- 1 jsjacobs deepsky 0 Feb 12 17:
Hi. I am running Postgres 8.2.7 on a Linux system for over
a year now with no problems.
Today one of the database users reported the following error:
psql: FATAL: could not read block 0 of relation 1664/0/1262: read
only 0 of 8192 bytes
I tried stopping and restarting the Postgres serve
On Fri, Feb 12, 2010 at 7:11 PM, Janet S Jacobsen wrote:
> Hi. I am running Postgres 8.2.7 on a Linux system for over
> a year now with no problems.
>
> Today one of the database users reported the following error:
>
> psql: FATAL: could not read block 0 of relation 1664/0/1262: read
> only
Janet S Jacobsen writes:
> Hi. I am running Postgres 8.2.7 on a Linux system for over
> a year now with no problems.
> Today one of the database users reported the following error:
>psql: FATAL: could not read block 0 of relation 1664/0/1262: read
>only 0 of 8192 bytes
Ugh. 1262 is p
Hi. I am running Postgres 8.2.7 on a Linux system for over
a year now with no problems.
Today one of the database users reported the following error:
psql: FATAL: could not read block 0 of relation 1664/0/1262: read
only 0 of 8192 bytes
I tried stopping and restarting the Postgres server
On Fri, Feb 12, 2010 at 9:13 PM, Tom Lane wrote:
> Richard Huxton writes:
>> On 12/02/10 15:10, Allan Kamau wrote:
>>> Therefore I am looking for a solution that contains
>>> "last-accessed-time" data for these objects, especially for the
>>> functions and maybe the triggers.
>
>> Ah, sorry - mis
Bill Moran wrote:
Note that the "correct" disk size for your database is probably closer
to the 1.6G you were seeing before.
This might be the case, but how do I find out what are the "correct" sizes?
I have a script that does following queries:
SELECT relpages * 8192 AS size_in_bytes, reln
On 12/02/10 18:13, Tom Lane wrote:
Richard Huxton writes:
The best you can do is to turn on statement logging, parse the logs to
see what objects are used and then keep those and their dependencies.
Or: remove some objects, run your test case, see if it succeeds.
Repeat as needed.
If you'v
In response to Marcin Krol :
> Amitabh Kant wrote:
> > You need to do VACUUM FULL ANALYZE to claim the disk space, but this
> > creates a exclusive lock on the tables.
> >
> > See http://www.postgresql.org/docs/8.3/static/sql-vacuum.html
>
> Aha!
>
> OK but why did the performance degrade so m
Richard Huxton writes:
> On 12/02/10 15:10, Allan Kamau wrote:
>> Therefore I am looking for a solution that contains
>> "last-accessed-time" data for these objects, especially for the
>> functions and maybe the triggers.
> Ah, sorry - misunderstood. There's not any timestamp kept. As you can
>
On Fri, 2010-02-12 at 18:43 +0100, Marcin Krol wrote:
> Amitabh Kant wrote:
> > You need to do VACUUM FULL ANALYZE to claim the disk space, but this
> > creates a exclusive lock on the tables.
> >
> > See http://www.postgresql.org/docs/8.3/static/sql-vacuum.html
>
> Aha!
>
> OK but why did the
Amitabh Kant wrote:
You need to do VACUUM FULL ANALYZE to claim the disk space, but this
creates a exclusive lock on the tables.
See http://www.postgresql.org/docs/8.3/static/sql-vacuum.html
Aha!
OK but why did the performance degrade so much? The same reason -- lack
of autovacuuming/vacuum
On Fri, Feb 12, 2010 at 10:40 PM, Marcin Krol wrote:
> Hello,
>
> The db in the application I maintain but didn't write (it obviously
> makes use of PG, v 8.3), has been systematically growing in size from
> about 600M to 1.6G.
>
> At the same time, the performance of the app has degraded signifi
Hello,
The db in the application I maintain but didn't write (it obviously
makes use of PG, v 8.3), has been systematically growing in size from
about 600M to 1.6G.
At the same time, the performance of the app has degraded significantly
(several times).
So I've done VACUUM ANALYZE on entire db.
On 12/02/10 15:10, Allan Kamau wrote:
On Fri, Feb 12, 2010 at 3:47 PM, Richard Huxton wrote:
On 12/02/10 12:32, Allan Kamau wrote:
If I start with a clean deployment, is there a way I could perhaps
query the table(s) in pg_catalog for example to find out the database
objects (I have construct
On Fri, Feb 12, 2010 at 3:47 PM, Richard Huxton wrote:
> On 12/02/10 12:32, Allan Kamau wrote:
>>
>> If I start with a clean deployment, is there a way I could perhaps
>> query the table(s) in pg_catalog for example to find out the database
>> objects (I have constructed) that have been invoked or
Oliver Kohll - Mailing Lists writes:
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: could not open relation with OID
> 572838
> pg_dump: The command was: SELECT
> pg_catalog.pg_get_viewdef('572838'::pg_catalog.oid) AS viewdef
Looks like a race condition somebod
> -Original Message-
> From: John R Pierce [mailto:pie...@hogranch.com]
> Sent: Thursday, February 11, 2010 3:01 PM
> To: pgsql-general@postgresql.org
> Subject: Re: help with SQL join
>
> Neil Stlyz wrote:
> > Now... here is the problem I am having... the above SQL query is
> > retri
Hello,
I've just come across this in an output from a cron backup script:
/etc/cron.hourly/gtwm_backup_databases.sh:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not open relation with OID
572838
pg_dump: The command was: SELECT
pg_catalog.pg_get_viewdef('57283
On 12/02/10 12:32, Allan Kamau wrote:
If I start with a clean deployment, is there a way I could perhaps
query the table(s) in pg_catalog for example to find out the database
objects (I have constructed) that have been invoked or used in some
way during a complete run of my application. I had a q
Hi,
Our documentation says:
"All check constraints and not-null constraints on a parent table are
automatically inherited by its children."
Okay, this works as expected:
test=# create table parent (name text primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pke
Hi,
I have been written several psql functions, tiggers and defined
several tables over time for a database application I have been
developing. The application has evolved as I have gained better
understanding of the solution and so I have written newer psql
functions and other database objects in
In response to Davor J. :
> What I want is something similar to this:
>
>
> Basically, what I want is a similar function f() that returns me a "pointer"
> to the table which I can use in some query like this: SELECT * FROM
> regclass(f()); Currently, this query only gives me one row 'tbl_temp'.
I am starting to see that this (and certainly my previous post about a
function as table-pointer) is impossible because of the pg planner. But
because the planner is required in every execution of a function, it puts
the constraint that a function cannot determine it's output by itself, but
mus
What I want is something similar to this:
CREATE OR REPLACE FUNCTION f( /* "some args..." */)
RETURNS text AS
$BODY$
DECLARE
...
BEGIN
DROP TABLE IF EXISTS tbl_temp;
CREATE TEMPORARY TABLE tbl_temp(
-- "based on args..."
);
WHILE
INSERT INTO tbl_temp VALUES (/*"some values"*/);
END
What I want is something similar to this:
CREATE OR REPLACE FUNCTION f( /* "some args..." */)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
...
BEGIN
DROP TABLE IF EXISTS tbl_temp;
CREATE TEMPORARY TABLE tbl_temp(
-- "based on args..."
);
WHILE
INSERT INTO tbl_temp VALUES (/*"some values"*/
The impossibility of a function as a pointer seems clear to me: if it was
possible, it would switch off the planner. But it should not be the case.
For example: the planner could postpone planning if it detected a function
that returns a set, execute it and wait for the results, and then use the
On Thu, 11 Feb 2010 20:11:54 +0100
Ivan Sergio Borgonovo wrote:
> I'm still having trouble making this work:
> http://pgsql.privatepaste.com/14a6d3075e
Finally I got it working, not the above version anyway...
CREATE OR REPLACE FUNCTION tsvector_to_tsquery(IN tsv tsvector, op
IN char(1), weigh
On 12/02/10 08:17, Yan Cheng Cheok wrote:
Hello, currently, I would like to make sure :
(1) When insert a new item, there is no 2nd row with same column value.
(2) If there is, I will just directly take the existing row.
I was thinking out of 2 approach. May I know which one is the common used
On 12/02/10 01:20, Robert James wrote:
Hi. I'm setting up a new workstation (Win7 64bit Quad Core 4GB) with
Postgres, for development work, and trying to pick which version I
should install. Most of the time, Postgres is dormant - I'm not using
it all - but when I do use it, the load can be hig
Hello, currently, I would like to make sure :
(1) When insert a new item, there is no 2nd row with same column value.
(2) If there is, I will just directly take the existing row.
I was thinking out of 2 approach. May I know which one is the common used best
practice?
// Shall I make the **entir
On Friday, February 12, 2010, Robert James wrote:
> Hi. I'm setting up a new workstation (Win7 64bit Quad Core 4GB) with
> Postgres, for development work, and trying to pick which version I
> should install. Most of the time, Postgres is dormant - I'm not using
> it all - but when I do use it, t
36 matches
Mail list logo
