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
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 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
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 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
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
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"*/
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
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
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'.
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
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
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
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
> -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
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
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
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
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 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
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, 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
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
>
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
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
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 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
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
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
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
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
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:
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-
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
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
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
36 matches
Mail list logo
