Re: [GENERAL] Postgres standard versus Postgres Plus Advanced Server
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, the load can be high, and I want > maximum performance. > > Is there any reason to use standard Postgres over Postgres Plus > Advanced Server? postgres plus advanced server is a proprietary database. You will have to pay a licence fee for it. Standard postgres is open source, and a such free of costs. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Make sure there is no two column with same value - What is the best practice?
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 **entire** procedure atomic and synchronized.
// This means, every time, there is only
// one thread can execute it. But, does PostgreSQL provide such features?
//
stored_procedure
{
if (column.value is not "Mickey Mouse") {
insert new row with one of the column.value is "Mickey Mouse"
}
return row id with its column.value is "Mickey Mouse"
}
stored_procedure
{
// I make this column.value to be unique
if (column.value is not "Mickey Mouse") {
try {
insert new row with one of the column.value is "Mickey Mouse"
}
catch (Is Not Unique Exception) {
}
}
return row id with its column.value is "Mickey Mouse"
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres standard versus Postgres Plus Advanced Server
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 high, and I want maximum performance. Maximum performance from a 4GB machine with Windows 7 and presumably the usual RAM-chewing Windows dev tools? When you aren't going to have any data cached because the server is mostly dormant? And only one disk (wild guess)? You'll be lucky. On the other hand, you don't say what you consider a high load to be, how much data you will have or what "maximum performance" would mean to you. So - you might be fine. Just go with the standard PostgreSQL, unless you're interested in buying a support contract. If you ask questions on the list you'll get many more people who are running the standard setup rather than one of the commercial offshoots. They are fine products, but unless you're buying support or testing shows a particular benefit, don't worry. Once installed, you'll probably want to tune some of the settings. There are guides on the wiki, and if you have python installed on your machine, pgtune might be useful. http://wiki.postgresql.org/wiki/Performance_Optimization http://pgfoundry.org/projects/pgtune/ If the database is just for development purposes and you don't mind the risk of corruption if your power fails, you could turn fsync off in the config too. Not recommended for production use, but will reduce disk load. Quick upgrades to your workstation might include more RAM and more disks (perhaps consider an SSD?) -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Make sure there is no two column with same value - What is the best practice?
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 best
practice?
// Shall I make the **entire** procedure atomic and synchronized.
// This means, every time, there is only
// one thread can execute it. But, does PostgreSQL provide such features?
//
stored_procedure
{
if (column.value is not "Mickey Mouse") {
insert new row with one of the column.value is "Mickey Mouse"
}
return row id with its column.value is "Mickey Mouse"
}
You could lock the whole table, but that will kill performance.
stored_procedure
{
// I make this column.value to be unique
if (column.value is not "Mickey Mouse") {
try {
insert new row with one of the column.value is "Mickey Mouse"
}
catch (Is Not Unique Exception) {
}
}
return row id with its column.value is "Mickey Mouse"
}
This would be a more typical approach, perhaps. You will want to loop
checking for the row and trying to insert. It is possible another
connection might insert then delete "Mickey Mouse" while this procedure
is executing.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [SOLVED] C function to create tsquery not working
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), weights IN varchar(4), maxpos IN smallint ) RETURNS tsquery AS 'MODULE_PATHNAME' LANGUAGE C STRICT; There were some small errors, but the main one was setting SET_VARSIZE passing the pointer to the query in spite of the query. I'll need some smaller help to polish everything. It is a small work but there was someone on the list that showed some interest and it may be a nice simple helper for tsearch. What would be the right place to advertise it and make it available? To sum it up... I wrote 2 functions: 1 takes a tsvector and return it as a setof record text, int[], int[] 2 takes a tsvector, filter it according to weights and maximum position and return a | or & tsquery The first is just for "debugging" or to be able to build more complicated tsqueries in your preferred language. The second can come handy to look for text similarity skipping to compute tsvectors twice. create or replace function similar(_id int, out id int, out title text) returns setof record as $$ declare tsvin tsvector; tsq tsquery; begin select into tsvin from table where id = _id; tsq := tsvector_to_tsquery( tsvin, '|', 'AB', 100); return query select t.id, t.title from table t where t.tsv @@ tsq ; return; end; $$ language plpgsql stable; -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function that creates a custom (temporary) table AND returns a pointer to it = impossible in pg?
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 results for planning of the outer query. This would imply a layered query planning/execution. I think this approach would seriously extend the possibilities of pg in a rather "object-oriented" way. Or am I wrong? Regards, Davor "Davor J." wrote in message news:hl1170$29j...@news.hub.org... > 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 LOOP; > > /*create indexes on it, manipulate, whatever...*/ > > RETURN 'tbl_temp'::text; > > END; > $BODY$ > LANGUAGE 'plpgsql' > > 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'..., but not what I want: "SELECT * FROM tbl_temp;" Can this be > done in Postgres? > > Original post: > http://forums.devshed.com/postgresql-help-21/function-that-creates-a-custom-table-and-returns-a-pointer-675539.html > > Kind regards, > Davor > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function that creates a custom table AND returns it = impossible in pg?
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"*/);
END LOOP;
/*create indexes on it, manipulate, whatever...*/
RETURN QUERY SELECT * FROM tbl_temp;
END;
$BODY$
LANGUAGE 'plpgsql'
The point is: only the function knows the structure (i.e. rowtype) of the
created table, not the initializer. The initializer is only supposed to
supply the arguments, run"SELECT * FROM f(some args);" and fetch the
results. Is this possible in Postgres??
(And for those who insist: no, the intializer can not run SELECT * FROM
f(some args) AS table("table structure...");" This would imply the
initializer already knows what the function will do. It goes beyond the
point of modularity: why not just skip the function then and let the
initializer write the whole query itself?)
Original post:
http://forums.devshed.com/postgresql-help-21/function-that-creates-a-custom-table-and-returns-it-impossible-675540.html
Kind regards,
Davor
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function that creates a custom (temporary) table AND returns a pointer to it = impossible in pg?
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 LOOP; /*create indexes on it, manipulate, whatever...*/ RETURN 'tbl_temp'::text; END; $BODY$ LANGUAGE 'plpgsql' 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'..., but not what I want: "SELECT * FROM tbl_temp;" Can this be done in Postgres? Original post: http://forums.devshed.com/postgresql-help-21/function-that-creates-a-custom-table-and-returns-a-pointer-675539.html Kind regards, Davor -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function that creates a custom table AND returns it = impossible in pg?
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
must obey the one predefined, or (in case of SETOF RECORDS) known by the
initializer. The last, IMHO, is simply a bad workaround, not only because it
is "unmodular" (see infra), but also because it allows serieus coding
mistakes. (Suppose f() returns a table (men, women), and you redefine it in
your query to (women, men) and then put constrainsts on the men column: the
results would be untrue, and the bug hard to find.)
So because every function needs to have a predefined output, this constraint
severely limits the "PL" in pgSQL. In my own project, I wanted to write a
couple of fucntions with which the application would interact, instead of
writing sql in the application itself. pg apparently hardly makes this
possible.
I think there is a solution for this, which is implementing a keyword for a
more generic execution of a function that bypasses the planner and is
equivalent to SELECT * FROM f(). For example, EXECUTE FUNCTION
function_name()...
So... is there a grain of truth in all this?
Regards,
Davor
"Davor J." wrote in message
news:hl11bd$2fs...@news.hub.org...
> 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"*/);
> END LOOP;
>
> /*create indexes on it, manipulate, whatever...*/
>
> RETURN QUERY SELECT * FROM tbl_temp;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql'
>
> The point is: only the function knows the structure (i.e. rowtype) of the
> created table, not the initializer. The initializer is only supposed to
> supply the arguments, run"SELECT * FROM f(some args);" and fetch the
> results. Is this possible in Postgres??
>
> (And for those who insist: no, the intializer can not run SELECT * FROM
> f(some args) AS table("table structure...");" This would imply the
> initializer already knows what the function will do. It goes beyond the
> point of modularity: why not just skip the function then and let the
> initializer write the whole query itself?)
>
> Original post:
> http://forums.devshed.com/postgresql-help-21/function-that-creates-a-custom-table-and-returns-it-impossible-675540.html
>
> Kind regards,
> Davor
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function that creates a custom (temporary) table AND returns a pointer to it = impossible in pg?
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'..., > but not what I want: "SELECT * FROM tbl_temp;" Can this be done in Postgres? Take a look at http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Weeding out unused user created database objects, could I use pg_catalog?
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 inline with this evolution and stopped calling (making use of) the older database objects I had previously written. Now I would like to only keep these database objects that are currently been used by the application. I have dedicted a single database object for use with this application, and I have all the DDL scripts in files which I execute for a clean deployment. 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 quick look at the pg_catalog but was unable to determine the tables that may contain pieces of this information. If pg_catalog could provide me with this solution, what are the table(s) to query? Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] possible bug with inheritance?
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_pkey" for table "parent" CREATE TABLE test=*# create table child (age int) inherits (parent) ; CREATE TABLE test=*# \d child Table "public.child" Column | Type | Modifiers +-+--- name | text| not null age| integer | Inherits: parent Nice, the 'not null' constraint is in the child-table. test=*# rollback; ROLLBACK test=# create table parent (name text); CREATE TABLE test=*# create table child (age int) inherits (parent) ; CREATE TABLE test=*# alter table parent add primary key (name); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "parent_pkey" for table "parent" ALTER TABLE test=*# \d child Table "public.child" Column | Type | Modifiers +-+--- name | text| age| integer | Inherits: parent Doh, there isn't the 'not null' - modifier. The parent-table contains this modifier as part of the primary key - definition. Other test: test=# create table parent (name text); CREATE TABLE test=*# create table child (age int) inherits (parent) ; CREATE TABLE test=*# alter table parent alter column name set not null; ALTER TABLE test=*# \d child Table "public.child" Column | Type | Modifiers +-+--- name | text| not null age| integer | Inherits: parent Okay, we have again the 'not null'. I think, that's not really clearly. In some case the 'not null' - constraint are in the child-table, in other case they are not. Version 8.4.2. Bug, feature? What have i overlooked? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weeding out unused user created database objects, could I use pg_catalog?
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 quick look at the pg_catalog but was unable to determine the tables that may contain pieces of this information. If pg_catalog could provide me with this solution, what are the table(s) to query? Quickest solution might be to use the --list option of pg_restore (you'll need -Fc on pg_dump too). That will list everything in the database dump and you can just compare the lists. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump: SQL command failed
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('572838'::pg_catalog.oid) AS viewdef
The complete script is
#!/bin/bash
nice /usr/bin/pg_dump -U postgres -f
/var/local/backup/agilebaseschema_`/bin/date +%H`.sql agilebaseschema
nice /usr/bin/pg_dump -U postgres --exclude-table=dbint_log_* -f
/var/local/backup/agilebasedata_`/bin/date +%H`.sql agilebasedata
Running the script again manually, it works with no errors and I haven't
noticed the error before. Unfortunately I recently deleted my archive of cron
results so I can't do an exhaustive check and I can't see anything in the
postgres log either but it's the first time I've noticed it since I can
remember.
A web search shows other people have come across this before now and again
though I can't see any resolutions. I'm running postgres v8.4.1.
Running that SELECT statement manually on both databases returns
agilebasedata=# SELECT pg_catalog.pg_get_viewdef('572838'::pg_catalog.oid) AS
viewdef;
viewdef
Not a view
(1 row)
Views are regularly altered, dropped and created in the agilebasedata database,
15 times today to date, which have all succeeded without error.
Any help on what could cause that error?
Regards
Oliver Kohll
oli...@agilebase.co.uk / 0845 456 1810 / 07814 828608
www.agilebase.co.uk - software
www.gtwm.co.uk - company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with SQL join
> -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 > > retrieving results from one table: sales I have another > table called > > customers with a couple of fields (customerid, and customername are > > two of the fields). > > I want to join on the customerid in both tables to retrieve the > > customername in the query. > > So I need the results to look something like this: > > > > customerid|customername| > > TODAYS_USERS|LAST 7 DAYS| > LAST 30 DAYS > > bigint|varchar | > > bigint |bigint > > > |bigint > > > -- > -- > > 8699| Joe Smith | > 1 > > | > > 1 |1 > > 8700| Sara Olson| > 1 > > |12 > > |17 > > 8701| Mike Jones | > 3 > > | > > 5 | 19 > > > > Can someone show me how to use a JOIN with the above SQL > Statement? I > > need to bring the customername field into the query from the other > > table and I have been having issues writting the query... can this > > even be done? > > something like... > > SELECT results.customerid, c.customername, count(distinct > count1) AS "TODAYS_USERS", count(distinct count2) AS "LAST 7 > DAYS" , count(distinct count3) AS "LAST 30 DAYS" > FROM (SELECT distinct case when s.modified >= > '2010-02-11' then s.modelnumber else null end as count1, >case when s.modified >= '2010-02-04' then > s.modelnumber else null end as count2, >case when s.modified >= '2010-01-11' then > s.modelnumber else null end as count3, s.customerid >FROM sales as s WHERE s.modelnumber LIKE 'GH77%') > AS results > JOIN customers as c ON (results.customerid = c.customerid) > GROUP BY results.customerid > One correction: you should "group" on all non-aggregate columns in your "select" list, i.e.: SELECT results.customerid, c.customername, count(distinct count1) AS "TODAYS_USERS", count(distinct count2) AS "LAST 7 DAYS" , count(distinct count3) AS "LAST 30 DAYS" FROM (SELECT distinct case when s.modified >= '2010-02-11' then s.modelnumber else null end as count1, case when s.modified >= '2010-02-04' then s.modelnumber else null end as count2, case when s.modified >= '2010-01-11' then s.modelnumber else null end as count3, s.customerid FROM sales as s WHERE s.modelnumber LIKE 'GH77%') AS results JOIN customers as c ON (results.customerid = c.customerid) GROUP BY results.customerid, c.customername Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump: SQL command failed
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 somebody dropped a view between the
pg_dump saw it and the time pg_dump tried to grab the view definition.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weeding out unused user created database objects, could I use pg_catalog?
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 used in some >> way during a complete run of my application. I had a quick look at the >> pg_catalog but was unable to determine the tables that may contain >> pieces of this information. If pg_catalog could provide me with this >> solution, what are the table(s) to query? > > Quickest solution might be to use the --list option of pg_restore (you'll > need -Fc on pg_dump too). That will list everything in the database dump and > you can just compare the lists. > > -- > Richard Huxton > Archonet Ltd > I have the DDL scripts of both the old and the new database objects mixed together, I am looking for a way to distinguish between them. The objects accessed at any point during the complete run of the application are the ones I would like to retain. I have no other way to distinguish between the useful and the defunct objects. Therefore I am looking for a solution that contains "last-accessed-time" data for these objects, especially for the functions and maybe the triggers. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weeding out unused user created database objects, could I use pg_catalog?
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 constructed) that have been invoked or used in some way during a complete run of my application. I had a quick look at the pg_catalog but was unable to determine the tables that may contain pieces of this information. If pg_catalog could provide me with this solution, what are the table(s) to query? Quickest solution might be to use the --list option of pg_restore (you'll need -Fc on pg_dump too). That will list everything in the database dump and you can just compare the lists. I have the DDL scripts of both the old and the new database objects mixed together, I am looking for a way to distinguish between them. The objects accessed at any point during the complete run of the application are the ones I would like to retain. I have no other way to distinguish between the useful and the defunct objects. 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 imagine, it would be a cost you'd have to pay every time you accessed an object. 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. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] db size and VACUUM ANALYZE
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. Nothing. The db did not decrease in size, the performance stayed the same. So I backed it up using pg_dump, deleted database, and recreated it from backup. The size of db on disk went down to 600M, performance recovered to the original level. Why that is so? I thought that VACUUM ANALYZE does everything that is needed to optimize disk usage? Regards, mk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db size and VACUUM ANALYZE
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 significantly > (several times). > > So I've done VACUUM ANALYZE on entire db. Nothing. The db did not > decrease in size, the performance stayed the same. > > So I backed it up using pg_dump, deleted database, and recreated it from > backup. > > The size of db on disk went down to 600M, performance recovered to the > original level. > > Why that is so? I thought that VACUUM ANALYZE does everything that is > needed to optimize disk usage? > > Regards, > mk > > 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 With regards Amitabh Kant
Re: [GENERAL] db size and VACUUM ANALYZE
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 full? Regards, mk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db size and VACUUM ANALYZE
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 performance degrade so much? The same reason -- lack > of autovacuuming/vacuum full? > if the application makes use of INDEXes then REINDEX will also play an important role in that case REINDEXING your indexes once in a while may give you imediate improvements in performance (may give... may not give depends) moreover, you should expect that in a few days/weeks/months the database size can (probably will) grow up again... it's the way pg works try using autovacuum if you are already using it you can make it more agressive by decreasing the thresholds and so on Joao > Regards, > mk > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weeding out unused user created database objects, could I use pg_catalog?
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 > imagine, it would be a cost you'd have to pay every time you accessed an > object. > 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. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db size and VACUUM ANALYZE
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 much? The same reason -- lack > of autovacuuming/vacuum full? Note that the "correct" disk size for your database is probably closer to the 1.6G you were seeing before. This allows PG some free space within the data files to add/remove records. vacuum full removes this space, and you'll likely find that the files will simply expand to use it again. Vaccuum (without full) keeps that space at an equilibrium. As to performance degradation, you'll always see performance hits as your database size increases. I'm assuming from your need to ask about this issue that the degradation was significant. In that case, you first want to make sure that the tables in the database have indexes in all the right places -- in my experience, this is the biggest cause of performance issues. Use of EXPLAIN ANALYZE on queries that are performing slow will usually indicate where indexes can help. >From there, you may simply have too little hardware for the database to run at the speed you expect. Giving it more RAM is cheap and tends to work wonders. Any time the system runs out of RAM, it needs to use disk instead, which significantly hurts performance. Hope this is helpful. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weeding out unused user created database objects, could I use pg_catalog?
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've lost track of which database objects are in use, I'm not sure your test suite is such a reliable indicator. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db size and VACUUM ANALYZE
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, relname FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') ORDER BY size_in_bytes DESC LIMIT 10; SELECT SUM(sizes.size_in_bytes) AS total_size_for_top_10_tables FROM (SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') ORDER BY size_in_bytes DESC LIMIT 10) AS sizes; SELECT SUM(sizes.size_in_bytes) AS total_size_for_all_tables FROM (SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')) AS sizes; Result before (1.6G db): size_in_bytes | relname ---+-- 806387712 | cs_ver_digests_pkey 103530496 | oai_edi_atts_pkey 62021632 | cs_ver_paths 61734912 | cs_ver_digests 55721984 | cs_fil_paths 45309952 | met_files 38412288 | met_versions 26247168 | cs_ver_content_types 25444352 | met_edi_ver 23724032 | met_edi_atts (10 rows) total_size_for_top_10_tables -- 1248534528 (1 row) total_size_for_all_tables --- 1467809792 Results now (600M db): size_in_bytes | relname ---+--- 62169088 | cs_ver_paths 55828480 | cs_fil_paths 45441024 | met_files 42000384 | cs_ver_digests 37552128 | met_versions 25509888 | met_edi_ver 24215552 | cs_ver_content_types 20717568 | met_edi_atts 18186240 | met_edi_ver_pkey 13565952 | cs_ver_content_types_pkey (10 rows) total_size_for_top_10_tables -- 345186304 (1 row) total_size_for_all_tables --- 467476480 (1 row) This allows PG some free space within the data files to add/remove records. vacuum full removes this space, and you'll likely find that the files will simply expand to use it again. Vaccuum (without full) keeps that space at an equilibrium. I don't mind slight performance degradation, the problem is that it is 2nd time that beyond certain db size the performance degradation tends to be almost runaway. As to performance degradation, you'll always see performance hits as your database size increases. I'm assuming from your need to ask about this issue that the degradation was significant. Yes, to the point of unacceptable (that is, queries took like 20-30 seconds). In that case, you first want to make sure that the tables in the database have indexes in all the right places -- in my experience, this is the biggest cause of performance issues. Use of EXPLAIN ANALYZE on queries that are performing slow will usually indicate where indexes can help. I'll try, though that will not be easy as they are complex and were not written by me (it's a closed system). From there, you may simply have too little hardware for the database to run at the speed you expect. You see that's the weird thing: the machine in question has 4 cpus and 4G of ram. When the performance was unacceptable, the loadavg was around 1, all cpus were slightly loaded, and iostat didn't show much happening on the disks. The one thing I remember is that there were many postmaster processes (like 20), they had huge virtual sizes (like 800m) and large resident sizes (like 300M). On top of having the pg_dump backup, I have copied the binary files of db when pg was stopped. I could play with those files (change them under the same pg config on another machine). Giving it more RAM is cheap and tends to work wonders. Any time the system runs out of RAM, it needs to use disk instead, which significantly hurts performance. This is my memory config: shared_buffers = 768MB temp_buffers = 32MB # min 800kB work_mem = 32MB # min 64kB max_stack_depth = 256MB # min 100kB max_fsm_pages = 153600 % sysctl -a | grep shm kernel.shmmni = 4096 kernel.shmall = 262144 kernel.shmmax = 1073741824 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weeding out unused user created database objects, could I use pg_catalog?
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 - misunderstood. There's not any timestamp kept. As you can >> imagine, it would be a cost you'd have to pay every time you accessed an >> object. > >> 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. > > regards, tom lane > Thanks Richard and Tom for your suggestions, I already have statement logging (as the application is still in development phase) in CSV format. I will create a table of the same structure as this log file and import the data into the DB, then perform the neccessary queries on this table. Since I am also capturing the duration per statement I will use the opportunity to streamline some of my queries and stored procedures. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "could not read block 0... " error followed by "database does not exist"
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 for the database. >From the logfile: LOG: received smart shutdown request LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2010-02-12 17:15:37 PST LOG: autovacuum launcher started LOG: database system is ready to accept connections But when I try to connect to the database, I get the error message: FATAL: database "subptf" does not exist I tried stopping/restarting the database several times. I also killed all user connections to the database. How do I fix this problem? Thanks, Janet -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "could not read block 0... " error followed by "database does not exist"
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 pg_database --- apparently something has truncated your pg_database table to zero bytes :-(. Which certainly explains the "no such database" errors. Have you got any chance of pulling that physical file from a backup? The one bright spot here is that pg_database is pretty static in most installations, so you could probably use even a not-very-current copy. The file you want is $PGDATA/global/1262. I don't offhand know of any bugs in 8.2.7 that could cause this, though that is rather an old version ... you might want to think about an update to 8.2.something-recent. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "could not read block 0... " error followed by "database does not exist"
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 0 of 8192 bytes Sounds like a bad sector on your hard drive. Got any recent backups? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "could not read block 0... " error followed by "database does not exist"
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 for the database. From the logfile: LOG: received smart shutdown request LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2010-02-12 17:15:37 PST LOG: autovacuum launcher started LOG: database system is ready to accept connections But when I try to connect to the database, I get the error message: FATAL: database "subptf" does not exist I tried stopping/restarting the database several times. I also killed all user connections to the database. How do I fix this problem? Thanks, Janet -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "could not read block 0... " error followed by "database does not exist"
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:42 pg_database -rw--- 1 jsjacobs deepsky 10927 Feb 12 21:57 pgstat.stat I have a pgdump from a month ago. Are you saying to restore that to a different location and then copy over $PGDATA/global/1262? Do I also need to copy over $PGDATA/global/pg_database? Thanks, Janet Tom Lane wrote: > 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 pg_database --- apparently something has truncated your > pg_database table to zero bytes :-(. Which certainly explains the > "no such database" errors. > > Have you got any chance of pulling that physical file from a backup? > The one bright spot here is that pg_database is pretty static in most > installations, so you could probably use even a not-very-current copy. > The file you want is $PGDATA/global/1262. > > I don't offhand know of any bugs in 8.2.7 that could cause this, > though that is rather an old version ... you might want to think > about an update to 8.2.something-recent. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "could not read block 0... " error followed by "database does not exist"
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--- 1 jsjacobs deepsky 0 Feb 12 17:42 pg_database > -rw--- 1 jsjacobs deepsky 10927 Feb 12 21:57 pgstat.stat Looks about as I'd expect from your description. Something clobbered 1262, and then the "flat" file pg_database got updated from that. You might want to look around at what was happening Feb 8 18:51. > I have a pgdump from a month ago. Are you saying to restore > that to a different location and then copy over > $PGDATA/global/1262? Do I also need to copy over > $PGDATA/global/pg_database? Right on both. Of course, it'd be a good idea to first make a backup of what you have in $PGDATA now (all of it) --- you want to be able to get back to where you are if this makes things worse. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db size and VACUUM ANALYZE
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 that's seriously screwed up--it will take forever to run. Use CLUSTER to accomplish the same thing much faster; it basically does the same thing as the dump/restore step that's restoring good performance to the database. Before doing that, I would run a VACUUM VERBOSE on the whole cluster and see if there are any max_fsm_pages warnings in there. Those settings might be too low, for example if large deletions are done in batches, and ultimately be the true cause of this problem. In general, the answer to most "why is my database getting too big/slow after it's been up for a while?" questions is "you aren't vacuuming often enough". Is autovacuum on? Are there any long-running transactions that keep it from working? There are use patterns where that's still not good enough, but those are less common than the case where the basics (use autovacuum and makes sure the FSM parameters are set correctly) just aren't being done. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db size and VACUUM ANALYZE
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 slow database, but don't even show up in the top 10 on the smaller one. It usually happens when your VACUUM strategy is bad and you delete/update things all the time. Notes on this topic start at http://www.postgresql.org/docs/8.3/static/sql-reindex.html You can clean it up with REINDEX or CLUSTER, but not VACUUM FULL, which actually makes the problem worse. No need to rebuild the whole DB. max_fsm_pages = 153600 It's quite possible that's way too low for your workload. I already suggested VACUUM VERBOSE would dump info into the logs suggesting as much if that's the case; try that out next time you see the database get too big. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory Usage and OpenBSD
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 is done quite easily with pgbench-tools: http://git.postgresql.org/gitweb?p=pgbench-tools.git;a=summary You can just dump a list of scales and client counts you want to test and let that loose, it will generate graphs showing TPS vs. scale/clients and everything if gnuplot is available. transaction type: TPC-B (sort of) scaling factor: 70 query mode: simple number of clients: 70 number of transactions per client: 2 number of transactions actually processed: 140/140 tps = 293.081245 (including connections establishing) tps = 293.124705 (excluding connections establishing) This is way more clients than your server is going to handle well on pgbench's TPC-B test, which is primarily a test of hard disk write speed but it will get bogged down with client contention in many conditions. Performance degrades considerably as the number of clients increases much past the number of cores in the server; typically 1.5 to 2X as many clients as cores gives peak throughput. I'm not sure what's causing your panic--not enough BSD practice. But I think Tom's suggestion of vastly decreasing from: maintenance_work_mem = 240MB Is worth trying. Reducing it won't hurt pgbench performance on quick tests, just how long it takes to get the tests setup. Sorry about pgtune being a bit aggressive in what it suggests--on the TODO list to scale it back, and hopefully provide more helpful suggestions for kernel tuning too. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
