Re: [GENERAL] parsed queries (cursors) cashing issues
No that is not I meant. The problem in Prepared statements is in that you should determine SQL inside the function. I want to pass a query as a parameter, as well as query parameters. For example (I want to create a function like the following): select * from exec_query( /*query text => */ 'select f1, f2 from table where f3 = $1' , /*param1 => */ 1::integer ) as (f1 integer, f2 text) so function exec_query got a query text as parameter, query parameters, executed it and returned result as SETOF. In case of such a query had been executed at least once, prepare step should be excluded (stored execution plan should be used). On 8/3/07, Sibte Abbas <[EMAIL PROTECTED]> wrote: > > On 8/2/07, Sergey Moroz <[EMAIL PROTECTED]> wrote: > > The problem is that I can't find the way to exclude query parsing > (prepare > > step) for custom queries. In other words I want to create a function > that > > accepts a query text with "$1, $2, etc." and variables as params, > executes > > the query and returns a set of record. I could use 'execute' in plpgsql > but > > in such case a query will be parsed each time it is called. I check SPI > and > > found the way to store execution plans for the duration of the session, > but > > no convenient way to check if the plan was already generated for the > query. > > So I should create and store hash table by myself, and associate plan > > pointers and query hash by myself. I'm not a C/C++ guy so it's not an > easy > > task for me :). Is there any way to solve the problem? By the way - why > not > > to store hashes for queries and execution plans in a shared pool to have > an > > opportunity not to parse already parsed queries for any session as > Oracle > > does? > > > > Looks like you want to cache the query plans and then simply execute > them in subsequent invocations. The answer to this is Prepared > statements. Go to > http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html for > more details. > > regards, > -- Sibte > -- Sincerely, Sergey Moroz
[GENERAL] PostgreSQL Documentation on PalmOS
Plucker ( http://plkr.org/ ) is a tool for viewing documentation on handhelds running the PalmOS, and I just noticed that they have the PostgreSQL documentation available: http://projects.plkr.org/postgresql-documentation/ -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: index bloat WAS: [GENERAL] reindexing pg_shdepend
Joseph S <[EMAIL PROTECTED]> writes: > ... and when I notice that the tuplesperpage for the indexes is low (or > that the indexes are bigger then the tables themselves) I know it is > time for a VACUUM FULL and REINDEX on that table. If you are taking the latter as a blind must-be-wrong condition, you are fooling yourself -- it's not true for small tables. For instance, in a freshly initdb'd database: postgres=# vacuum verbose pg_opclass; INFO: vacuuming "pg_catalog.pg_opclass" INFO: index "pg_opclass_am_name_nsp_index" now contains 107 row versions in 4 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.00 sec. INFO: index "pg_opclass_oid_index" now contains 107 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_opclass": found 0 removable, 107 nonremovable row versions in 2 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 1 pages contain useful free space. 0 pages are entirely empty. CPU 0.01s/0.00u sec elapsed 0.00 sec. VACUUM postgres=# Have you checked whether the VACUUM FULL + REINDEX actually makes anything smaller? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] == PostgreSQL Weekly News - July 29 2007 ==
Joseph S <[EMAIL PROTECTED]> writes: > David Fetter wrote: >> - Arrange to put TOAST tables belonging to temporary tables into >> special schemas named pg_toast_temp_nnn, alongside the pg_temp_nnn > Does this apply to indexes on temp tables as well? Or is this not even > an issue with indexes? Indexes always live in the same schema as their parent table. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What do people like to monitor (or in other words, what might be nice in pgsnmpd)?
Josh Tolley escribió: > On 8/2/07, Gavin M. Roy <[EMAIL PROTECTED]> wrote: > > Are you contemplating providing access to data that's currently not stored > > in the pg_ catalog tables? I currently monitor the statio data, > > transactions per second, and active/idle backends. Things that I think > > would be useful would be average query execution time, longest execution > > time, etc. Other pie in the sky ideas would include current level of total > > bloat in a database, total size on disk of a database broken down by tables, > > indexes, etc. > > My own goal is to have pgsnmpd able, as much as possible, to fill the > same role the set of scripts an arbitrary PostgreSQL DBA sets up on a > typical production server. That includes statistics tables and catalog > tables, but certainly isn't limited to just that. So doing things like > categorizing total sessions in interesting and useful ways (for > instance, # of idle connections, # of active connections, max > transaction length, etc.) are certainly within pgsnmpd's purview. More ideas: autovacuum metrics, for example how long since the last vacuum of tables, age(pg_class.relfrozenxid), how many dead tuples there are, pg_class.relpages (do tables shrink, grow or stay constant-size?), etc. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "La felicidad no es mañana. La felicidad es ahora" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] What do people like to monitor (or in other words, what might be nice in pgsnmpd)?
On 8/2/07, Gavin M. Roy <[EMAIL PROTECTED]> wrote: > Are you contemplating providing access to data that's currently not stored > in the pg_ catalog tables? I currently monitor the statio data, > transactions per second, and active/idle backends. Things that I think > would be useful would be average query execution time, longest execution > time, etc. Other pie in the sky ideas would include current level of total > bloat in a database, total size on disk of a database broken down by tables, > indexes, etc. > > Regards, > > Gavin My own goal is to have pgsnmpd able, as much as possible, to fill the same role the set of scripts an arbitrary PostgreSQL DBA sets up on a typical production server. That includes statistics tables and catalog tables, but certainly isn't limited to just that. So doing things like categorizing total sessions in interesting and useful ways (for instance, # of idle connections, # of active connections, max transaction length, etc.) are certainly within pgsnmpd's purview. In short, all the suggestions you listed are useful, and provided the framework allows us to get reasonably good values for them, worthy of implementation in pgsnmpd. Thanks. -Josh ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
index bloat WAS: [GENERAL] reindexing pg_shdepend
Tom Lane wrote:
Joseph S <[EMAIL PROTECTED]> writes:
Me too. I don't change my db schema that much, but I experience bloat
in the pg_tables that I don't expect. For instance pg_opclass needs a
VACUUM FULL/REINDEX once a week or I notice the indexes are larger than
the table itself. Could it be my heavy use of temp tables?
pg_opclass? That's read-only for most people. What are you doing with
operator classes?
I know. I can't figure it out. I barely know what operator classes
are, but I'm pretty sure I'm not modifying them in any way.
Heavy use of temp tables would expand pg_class, pg_type, and especially
pg_attribute, but as long as you have a decent vacuuming regimen (do you
use autovac?) they shouldn't get out of hand.
I do use autovac. Like I said they don't get really out of hand, only
up to 20 megs or so before I noticed that it was weird. The large
indexes are what tipped me off that something strange was going on.
I only noticed this because I was making an effort to monitor index
bloat on my regular tables. It could be there are a lot of people out
there who are experiencing this but don't notice because 20 megs here
and there don't cause any noticeable problems.
So how about it list? Do you know how bloated your indexes are getting?
I use this sql:
select (select nspname FROM pg_catalog.pg_namespace where oid =
relnamespace) AS schema, relname, CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN
's' THEN 'special' END as "Type" ,CASE c.relkind IN ('i','r','S','')
WHEN true THEN pg_relation_size(relname) END AS bytes, CASE relpages >
0 WHEN true THEN reltuples/relpages END AS tuplesperpage FROM
pg_catalog.pg_class c WHERE pg_catalog.pg_table_is_visible(c.oid) order
by schema, relname;
... and when I notice that the tuplesperpage for the indexes is low (or
that the indexes are bigger then the tables themselves) I know it is
time for a VACUUM FULL and REINDEX on that table.
If you really want to get fancy you can save the results of that into a
table with a timestamp. Then every (insert time period here) run VACUUM
FULL/REINDEXs on the individual tables and store the new sizes with
timestamps.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] parsed queries (cursors) cashing issues
On 8/2/07, Sergey Moroz <[EMAIL PROTECTED]> wrote: > The problem is that I can't find the way to exclude query parsing (prepare > step) for custom queries. In other words I want to create a function that > accepts a query text with "$1, $2, etc." and variables as params, executes > the query and returns a set of record. I could use 'execute' in plpgsql but > in such case a query will be parsed each time it is called. I check SPI and > found the way to store execution plans for the duration of the session, but > no convenient way to check if the plan was already generated for the query. > So I should create and store hash table by myself, and associate plan > pointers and query hash by myself. I'm not a C/C++ guy so it's not an easy > task for me :). Is there any way to solve the problem? By the way - why not > to store hashes for queries and execution plans in a shared pool to have an > opportunity not to parse already parsed queries for any session as Oracle > does? > Looks like you want to cache the query plans and then simply execute them in subsequent invocations. The answer to this is Prepared statements. Go to http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html for more details. regards, -- Sibte ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] reindexing pg_shdepend
Me too. I don't change my db schema that much, but I experience bloat in the pg_tables that I don't expect. For instance pg_opclass needs a VACUUM FULL/REINDEX once a week or I notice the indexes are larger than the table itself. Could it be my heavy use of temp tables? Today I noticed that pg_statistic (which I actually expect to be updated in the normal course of operations) was over 20 meg (with large indexes as well) so I gave it a VACUUM FULL/REINDEX and it now stands at 344,064. These tables never get *really* large, so I've never noticed a big performance hit, but they still get bigger than they should be and could be slowing everything down a little. around 3pm today I did a VACUUM FULL/REINDEX of all the trouble tables I have in my list, and I didn't save the before/after sizes, bit it is 9:30 pm now and I can tell you how much they've grown since then: BEFORE VACUUM FULL/REINDEX at 9:30: pg_catalog pg_classtable 196,608 21.526 pg_catalog pg_class_oid_index index 49,152 81.8 pg_catalog pg_class_relname_nsp_index index 172,032 21.526 pg_catalog pg_type table 180,224 15.045 pg_catalog pg_type_oid_index index 40,960 66.2 pg_catalog pg_type_typname_nsp_index index 106,496 25.462 AFTER: pg_catalog pg_classtable 81,920 41.1 pg_catalog pg_class_oid_index index 32,768 102.5 pg_catalog pg_class_relname_nsp_index index 57,344 58.714 pg_catalog pg_type table 65,536 41.375 pg_catalog pg_type_oid_index index 16,384 165.5 pg_catalog pg_type_typname_nsp_index index 49,152 55.167 My apologies for the tabs. That was a cut & paste from a web page I set up to monitor the database size. The columns are: schema relname Type bytes tuplesperpage The list of tables I have in my list are: pg_attribute pg_class pg_depend pg_index pg_shdepend pg_proc pg_statistic pg_type pg_trigger pg_shdepend I put them in my list bec. I once noticed that their indexes seemed big relative to the size of the table itself. I didn't really analyze if they were indeed recurring problems or just one time problems, but I know pg_class and pg_opclass are ones where this is a recurring problem. BTW Tom do you prefer the replies to go to you directly as well as to the list? Most of the time I just hit 'Reply' and since this list doesn't set the Reply-to: the replies go to the OP as well. Tom Lane wrote: Joseph S <[EMAIL PROTECTED]> writes: My pg_shdepend table has a size of 16,384, but pg_shdepend_depender_index has a size of 19,169,280 and pg_shdepend_reference_index has a size of 49,152. I'd be interested to see the usage pattern that made it get like that ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reindexing pg_shdepend
Joseph S <[EMAIL PROTECTED]> writes: > Me too. I don't change my db schema that much, but I experience bloat > in the pg_tables that I don't expect. For instance pg_opclass needs a > VACUUM FULL/REINDEX once a week or I notice the indexes are larger than > the table itself. Could it be my heavy use of temp tables? pg_opclass? That's read-only for most people. What are you doing with operator classes? Heavy use of temp tables would expand pg_class, pg_type, and especially pg_attribute, but as long as you have a decent vacuuming regimen (do you use autovac?) they shouldn't get out of hand. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] == PostgreSQL Weekly News - July 29 2007 ==
David Fetter wrote: == PostgreSQL Weekly News - July 29 2007 == - Arrange to put TOAST tables belonging to temporary tables into special schemas named pg_toast_temp_nnn, alongside the pg_temp_nnn Does this apply to indexes on temp tables as well? Or is this not even an issue with indexes? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reindexing pg_shdepend
Joseph S <[EMAIL PROTECTED]> writes: > My pg_shdepend table has a size of 16,384, but > pg_shdepend_depender_index has a size of 19,169,280 and > pg_shdepend_reference_index has a size of 49,152. I'd be interested to see the usage pattern that made it get like that ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Linux distro
Chris Browne wrote: > > The server does not need the overhead of having *any* of the "X > desktop" things running; it doesn't even need an X server. > > You don't need X running on the server in order use those "enterprise > management" tools; indeed, in a "lights out" environment, that server > hasn't even got a graphics card, which means that an X server *can't* > be running on it. Well, sure it can. Nothing says a X server has to write directly to a graphics card or anything. It could write to some frame buffer in memory and allow access to it through VNC, for example. http://www.cl.cam.ac.uk/research/dtg/attarchive/vnc/xvnc.html But of course I agree it makes no sense for a database server to also be running a GUI server; and ideally not even wasting electricity and space in the box for a graphics chip or monitor connector. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] reindexing pg_shdepend
Joseph S wrote: > My pg_shdepend table has a size of 16,384, but pg_shdepend_depender_index > has a size of 19,169,280 and pg_shdepend_reference_index has a size of > 49,152. When I try to reindex the table I get: > > ERROR: shared table "pg_shdepend" can only be reindexed in stand-alone > mode > > So is there any way I can clear this bloat w/o restarting the server? Nope :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] reindexing pg_shdepend
My pg_shdepend table has a size of 16,384, but pg_shdepend_depender_index has a size of 19,169,280 and pg_shdepend_reference_index has a size of 49,152. When I try to reindex the table I get: ERROR: shared table "pg_shdepend" can only be reindexed in stand-alone mode So is there any way I can clear this bloat w/o restarting the server? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] tsearch2 upgrade problem and mediawiki
Richard Huxton wrote: Try just adding the column, I'm guessing it was dropped while you were taking out the old tsearch2 installation. ALTER TABLE pagecontent ADD COLUMN textvector tsvector; Thanks, that fixed the problem. -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham <[EMAIL PROTECTED]>Rio de Janeiro, Brasil Personal: http://www.jandr.org/ ***-*--**---***--- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] array_to_set functions
On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
> On 8/1/07, Decibel! <[EMAIL PROTECTED]> wrote:
> > David Fetter and I just came up with these, perhaps others will find
> > them useful:
> >
> > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF
> > anyelement LANGUAGE SQL AS $$
> > SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1,
> > $2)) i
> > $$;
> > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement
> > LANGUAGE SQL AS $$
> > SELECT array_to_set($1, 1)
> > $$;
>
> very nice, although IMO there is a strong justification for these
> functions to be in core and written in C for efficiency (along with
> array_accum, which I have hand burn from copying and pasting out of
> the documentation).
>
> merlin
>
Excellent timing guys. :^)
I was trying to build a function to list the items of an array, but
ran into problems and was going to post what I had been working on.
Your functions work great.
In case you don't have the function to generate an array from a set
here is one I have been using :
CREATE AGGREGATE array_accum (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Magnus Hagander wrote: > Andrei Kovalevski wrote: >>> Have you done any development yet? >> Yes, you can download and try it. Now it's a single pgtray.exe >> application. I'm going to make an msi installer and add "Autostart" >> option to the menu. > > When you do the installer, please make sure it's compatible with > stackbuilder (http://pgfoundry.org/projects/stackbuilder) so it can use > the new easy-to-install method on windows. There is a setup kit in the stackbuilder CVS which makes it a snap to build a compatible MSI installer. Regards. Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tsearch2 upgrade problem and mediawiki
Jon Lapham wrote:
After upgrading from Postgres 8.1 to 8.2 (via pg_dumpall) I had problems
with mediawiki (v1.9.2) that utilized tsearch2. I thought I had fixed
the problem by removing tsearch2 in my v8.1 databases, pg_dumpall-ing
and reloading to v8.2, and re-installing tsearch2 from scratch.
When updating a page in mediawiki, I get an error. This is what I am
seeing in my pg_log:
STATEMENT: SELECT nextval('rev_rev_id_val')
ERROR: record "new" has no field "textvector"
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.textvector = to_tsvector('default',NEW.old_text);
ELSIF NEW.old_text != OLD.old_text THEN
NEW.textvector := to_tsvector('default',NEW.old_text);
END IF;
RETURN NEW;
END;
This looks like a problem with tsearch2 to me... can anyone give me a
pointer on where to go from here to debug this? Thanks!
Well, that's a trigger that's updating a column called "textvector" of
type tsvector (a tsearch2 type). It's not a tsearch2 trigger function
though, so presumably it comes with mediawiki.
Try just adding the column, I'm guessing it was dropped while you were
taking out the old tsearch2 installation.
ALTER TABLE pagecontent ADD COLUMN textvector tsvector;
Incidentally, I tend to install tsearch2 in its own schema now, makes it
simpler to check what's installed. It does mean you need to do alter
your search_path though.
Oh, and 8.3 will have tsearch as a built-in.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] want to drop corrupt db
"Gauthier, Dave" <[EMAIL PROTECTED]> writes: > After a db restore failed for reasons having to do with lack of disk > space, when I try to restart the DB, I get... > 2007-08-02 14:17:14 PANIC: could not fsync control file: Disk quota > exceeded > If the DB is trashed, how can I just delete it and start from scratch? Why do you conclude that the DB is trashed? Looks to me like you just need to fix the disk space problem. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux distro
On 8/2/07, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote: > On 8/3/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > > they do, but experience has shown it is prudent to be able to > > administrate the hardware directly from the box. > > I'm curious: which aspect of hardware administration > on a Linux box would require X (to be running)? If I *really* > needed applet such-and-such I could still run it easily, with > less overhead and w/o the X server even being installed on > the big iron from my desktop ... > > ssh -X [EMAIL PROTECTED] whizbangGUItool > [...] > Cheers, > Andrej There is of course no *requirement* for this to be the case, but one must make concessions for the fact that not everyone is at the same level of administration. To make Linux more accessible, a GUI is added for those people. Those people are just as smart and equally talented, but simply may not have the time to learn every command line detail to get a server up. While I agree that one can also use ssh and a remote X display (and is personally how I would do it, if not just pure command line), it is not that much of a stretch to understand that someone else's circumstances may not allow this without more additional setup which requires more time. Also, while the usual runlevel for a system would be 3, keeping a system at runlevel 5 would not realistically use more resources. When the system starts up, it will load the X server and xdm only (not gnome or anything else until someone logs in), and when not used all of that will get paged out to disk, so all it is taking up is a fraction of the CPU to make the login cursor blink. Any default screensaver will basically just blank the screen. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] What do people like to monitor (or in other words, what might be nice in pgsnmpd)?
I usually monitor blks_read and blks_hit (of block level stats), when the latter is high I see shared memory is doing a good job, when the former then it also shows something Also, database-wide number of commits and rollbacks (btw, Slony has a habit of calling ROLLBACK when it done nothing -- I wonder if calling ROLLBACK instead of COMMIT on a SELECT-only transaction is such a win? It certainly blurrs the image for me. ;) And a number of clients waiting on a lock. By the way, one nice thing to have could be counters which record how much time did it take to load a page into shared memory (less than 1ms, <2ms, <4ms, <8ms, <16m and so on. Could help fine-tuning things like vacuum cost/delay and so on. Seen it somewhere in Oraclish stats tables. Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux distro
On 8/3/07, Ben <[EMAIL PROTECTED]> wrote: > > I'm curious: which aspect of hardware administration > > on a Linux box would require X (to be running)? If I *really* > It's not that it can't be done, it's that having a window environment can > make things easier. (I find 24x80 pretty cramped, and I like large > scrollback buffers.) So I make my xterm on my workstation be 134x80, with a line buffer of 8000 and then ssh into the server :} > But like most computer efficiency questions, this whole thread tangent > boils down to how much overhead you want your computer to dedicate to > making your life easier at the expense of whatever else it was supposed to > be doing. True that. Same for how many potential vulnerabilities I'm willing to introduce for the sake of convenience. Not that I'd find it convenient to drive the 40Km to the data- centre to get physical access to the servers, mind you. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux distro
On Fri, 3 Aug 2007, Andrej Ricnik-Bay wrote: I'm curious: which aspect of hardware administration on a Linux box would require X (to be running)? If I *really* It's not that it can't be done, it's that having a window environment can make things easier. (I find 24x80 pretty cramped, and I like large scrollback buffers.) But like most computer efficiency questions, this whole thread tangent boils down to how much overhead you want your computer to dedicate to making your life easier at the expense of whatever else it was supposed to be doing. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] want to drop corrupt db
After a db restore failed for reasons having to do with lack of disk space, when I try to restart the DB, I get... dfg_suse> postmaster -D /proj/cad_sserv/cellibcmp/daveg/Postgres/DB -i > /proj/cad_sserv/cellibcmp/daveg/Postgres/postmaster.log & [1] 29644 dfg_suse> 2007-08-02 14:17:14 LOG: database system was interrupted while in recovery at 2007-08-02 14:16:21 EDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2007-08-02 14:17:14 LOG: checkpoint record is at B/258A7100 2007-08-02 14:17:14 LOG: redo record is at A/A700B9C0; undo record is at 0/0; shutdown FALSE 2007-08-02 14:17:14 LOG: next transaction ID: 64132; next OID: 73638687 2007-08-02 14:17:14 LOG: database system was not properly shut down; automatic recovery in progress 2007-08-02 14:17:14 PANIC: could not fsync control file: Disk quota exceeded 2007-08-02 14:17:14 LOG: startup process (PID 29647) was terminated by signal 6 2007-08-02 14:17:14 LOG: aborting startup due to startup process failure [1]Exit 1postmaster -D /proj/cad_sserv/cellibcmp/daveg/Postgres/DB -i > ... dfg_suse> If the DB is trashed, how can I just delete it and start from scratch? Thanks -dave
Re: [GENERAL] Linux distro
On 8/3/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: > they do, but experience has shown it is prudent to be able to > administrate the hardware directly from the box. I'm curious: which aspect of hardware administration on a Linux box would require X (to be running)? If I *really* needed applet such-and-such I could still run it easily, with less overhead and w/o the X server even being installed on the big iron from my desktop ... ssh -X [EMAIL PROTECTED] whizbangGUItool > I expect trend of desktop style management to continue > (for the record, I would really prefer these devices to present > html interfaces vs. java). I'm afraid you're right - and it was what I dislike(d) most about Oracles products. The fact that 10g ships with the actual database on a CD, and the admin stuff on DVDs. Friggin nightmare. > I'm actually recently converted from the 'anti-x' camp. This is > because I'm now using linux on a desktop and found it to be remarkably > efficient but also was recently in a situation where I regretted not > having it installed. I completely understand and sympathize with the > other side of the argument however. I just don't care anymore, maybe > I'm getting old :-). Nuh mate, that's not old, it's inefficient (and too lazy to know the ropes, relying on an "intuitive GUI") ;} ... /me ducks > merlin Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] tsearch2 upgrade problem and mediawiki
After upgrading from Postgres 8.1 to 8.2 (via pg_dumpall) I had problems
with mediawiki (v1.9.2) that utilized tsearch2. I thought I had fixed
the problem by removing tsearch2 in my v8.1 databases, pg_dumpall-ing
and reloading to v8.2, and re-installing tsearch2 from scratch.
When updating a page in mediawiki, I get an error. This is what I am
seeing in my pg_log:
STATEMENT: SELECT nextval('rev_rev_id_val')
ERROR: record "new" has no field "textvector"
CONTEXT: PL/pgSQL function "ts2_page_text" line 3 at assignment
STATEMENT: INSERT /* Revision::insertOn */ INTO pagecontent
(old_id,old_text,old_flags) VALUES ('2128','{| class="wikitable [snip]
The table "pagecontent" has a trigger:
wikidb=> \d pagecontent
Table "mediawiki.pagecontent"
Column | Type | Modifiers
---+-+---
old_id| integer | not null default
nextval('text_old_id_val'::regclass)
old_text | text|
old_flags | text|
Indexes:
"pagecontent_pkey" PRIMARY KEY, btree (old_id)
Triggers:
ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent FOR EACH ROW
EXECUTE PROCEDURE ts2_page_text()
with this code:
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.textvector = to_tsvector('default',NEW.old_text);
ELSIF NEW.old_text != OLD.old_text THEN
NEW.textvector := to_tsvector('default',NEW.old_text);
END IF;
RETURN NEW;
END;
This looks like a problem with tsearch2 to me... can anyone give me a
pointer on where to go from here to debug this? Thanks!
-Jon
--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
Jon Lapham <[EMAIL PROTECTED]>Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--**---***---
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Andrei Kovalevski wrote: >> Have you done any development yet? > Yes, you can download and try it. Now it's a single pgtray.exe > application. I'm going to make an msi installer and add "Autostart" > option to the menu. When you do the installer, please make sure it's compatible with stackbuilder (http://pgfoundry.org/projects/stackbuilder) so it can use the new easy-to-install method on windows. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Tony Caduto wrote: > Andrei Kovalevski wrote: >>Hi all! >> >>Everyone who use PostgreSQL server on Windows knows - it would be >> nice to have some tray management and monitoring tool for PostgreSQL >> server which is running as NT Service (for example - MS SQL already >> have such tool). I have created a new project on pgfoundry - >> http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can >> we improve this tool and what features whould be helpful. >> >> >> Thanks, Andrei. >> > Have you done any development yet? I can do something in Delphi in a > matter of hours and would be able to donate the code as a BSD license. > I have done a similar tray application for Firebird and it would be just > a matter of changing the service it monitors. yeah, this is good stuff. (not having tested it..) > We could also do a control panel applet. I don't really see the point. Given that "the modern way of doing things" is sticking the icon in administrative tools and not control panel. But I won't prevent you of course :-) //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SQL function and "UPDATE...RETURNING"
On 8/2/07, Vincenzo Romano <[EMAIL PROTECTED]> wrote: > Hi all. > > Is there a way to write an SQL function like this: > > create or replace function afunction( recid bigint ) > returns bigint as $body$ > update atable set afield=0 where recid=$1 returning anotherfield; > $body$ language SQL; > > If i write the function this way, postgres will complain that > the update stetement won't return values. > > I also understand that by switching to PLPGSQL I would b able to > do write the function. But I'd prefer SQL in this case. It's not really possible to do it that way :( merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] why is the LIMIT clause slowing down this SELECT?
On Wed, 2007-08-01 at 21:42 -0500, Mason Hale wrote: > The score in this case are definitely not a normal distribution. They > follow a power law pattern, with a few with very high scores and a > long tail. > > I ended up coercing it to use plan 2 by dropping the index on > topic_feed(score). > I think Tom had the correct advice, you should try an index on (topic_id,score). > Which raises another question -- if the planner has already used an > index on topic_id to select the rows, would it ever us another index > on score to order the rows? Or is a compound topic_feed(topic_id, > score) index the way to go there? > Two indexes can only be combined for a bitmap index scan, and a bitmap is in heap order, not index order. That means additional indexes only help to do additional filtering before it tries to fetch from the table itself. In your case there is no filter on "score" at all, "score" is just a sort order. A compound index should give you what you want. Regards, Jeff Davis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Andrei Kovalevski wrote: Hi all! Everyone who use PostgreSQL server on Windows knows - it would be nice to have some tray management and monitoring tool for PostgreSQL server which is running as NT Service (for example - MS SQL already have such tool). I have created a new project on pgfoundry - http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can we improve this tool and what features whould be helpful. Thanks, Andrei. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Have you done any development yet? I can do something in Delphi in a matter of hours and would be able to donate the code as a BSD license. I have done a similar tray application for Firebird and it would be just a matter of changing the service it monitors. We could also do a control panel applet. Later, Tony Caduto AM Software Design ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] What do people like to monitor (or in other words, what might be nice in pgsnmpd)?
I'd like to know what the age of the oldest running transaction is. i.e. hunt look out for old idle in transaction transactions that are holding up vacuuming. Info on the shared buffers like % used, % that hasn't been updated or seen in x minutes / hours / days. % used on various tablespaces connection stats: how many clients connected, by what accounts, % failed auths, stale connections harvested by tcp_keepalive timeouts... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] GiST index implementation
"Elena Camossi" <[EMAIL PROTECTED]> writes: > Hi list, > > what is the default implementation for GiST index? B-Tree or R-Tree? > That is, if i execute the following SQL command: > > CREATE index ON table USING Gist (column) > > what is the type of the index that is actually built? uhm, GIST. GIST is a particular type of index just like btree. What are you actually trying to do? Do you have a particular problem you're trying to solve? > How can I specify in SQL one of the two implementations provided (e.g. > R-Tree)? R-Tree indexes don't exist in Postgres any more. GIST indexes are very similar only more general. They handle 2D geometric data types like RTree did previously as well as n-dimensional data types and other more exotic things like intarrays and full text search. There are different "kinds" of GIST indexes which you can specify by specifying an operator class with you define the index. But most data types only have a single operator class available to them so that's probably not what you need unless you're trying to do something unusual. An "operator class" defines a set of operators which can be optimized by an index organized the same way and usually correspond to a particular interpretation of the data type. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Tony Caduto wrote: Andrei Kovalevski wrote: Hi all! Everyone who use PostgreSQL server on Windows knows - it would be nice to have some tray management and monitoring tool for PostgreSQL server which is running as NT Service (for example - MS SQL already have such tool). I have created a new project on pgfoundry - http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can we improve this tool and what features whould be helpful. Thanks, Andrei. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Have you done any development yet? Yes, you can download and try it. Now it's a single pgtray.exe application. I'm going to make an msi installer and add "Autostart" option to the menu. I can do something in Delphi in a matter of hours and would be able to donate the code as a BSD license. I have done a similar tray application for Firebird and it would be just a matter of changing the service it monitors. We could also do a control panel applet. Oh, very interesting. I'm not sure it's really required - but can you provide some links about this? Andrei. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] SQL function and "UPDATE...RETURNING"
Hi all. Is there a way to write an SQL function like this: create or replace function afunction( recid bigint ) returns bigint as $body$ update atable set afield=0 where recid=$1 returning anotherfield; $body$ language SQL; If i write the function this way, postgres will complain that the update stetement won't return values. I also understand that by switching to PLPGSQL I would b able to do write the function. But I'd prefer SQL in this case. -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] file-system snapshot under freebsd for backup
In response to ProAce <[EMAIL PROTECTED]>: > I use SAN, not NAS. :) > > Because some bugdet issue, I just have a basic SAN environment. > For multipathing, I use geom_fox to complete. > For snapshot, I want to use mksnap_ffs. > > But I don't have any experience of using mksnap_ffs to backup pgsql. > Does anyone give me some advice? If you don't want to do PITR, read this: http://www.postgresql.org/docs/8.2/static/backup-file.html You can also use the mount command to make filesystem snapshots. Assuming your PG data directory is under the mount point /var/db: mount -o snapshot -u /var/db/snapshot /var/db mdconfig -a -t vnode -f /var/db/snapshot -u 4 mount -r /dev/md4 /mnt [ ... do whatever you do to back up /mnt (which is the snapshot of /var/db ...] umount /mnt mdconfig -d -u 4 rm /var/db/snapshot > 2007/8/2, Bill Moran <[EMAIL PROTECTED]>: > > > > Maybe. I'm confused by your question, but: > > * If the data directory is on an FFS2 volume on a FreeBSD machine, you > > can use mksnap_ffs to back it up, and that approach is actually > > recommended for PITR-type backups. > > * If the data is mounted via NFS or something similar and the filesystem > > is not FFS2, then snapshots are not available on FreeBSD. > > > > -- > > Bill Moran > > http://www.potentialtech.com > > -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Linux distro
[EMAIL PROTECTED] ("Merlin Moncure") writes:
> On 8/2/07, Ron Johnson <[EMAIL PROTECTED]> wrote:
>> Pardon me for being the contrarian, but why does a server need a
>> GUI? Isn't that just extra RAM & CPU overhead that could be more
>> profitably put to use powering the application?
>
> A server with a GUI sitting on a login screen is wasting zero
> resources. Some enterprise management tools are in java which
> require a GUI to use so there is very little downside to installing
> X, so IMO a lightweight window manager is appropriate...a full gnome
> is maybe overkill. Obviously, you want to turn of the 3d screen
> saver :-)
The server does not need the overhead of having *any* of the "X
desktop" things running; it doesn't even need an X server.
You don't need X running on the server in order use those "enterprise
management" tools; indeed, in a "lights out" environment, that server
hasn't even got a graphics card, which means that an X server *can't*
be running on it.
--
"cbbrowne","@","linuxfinances.info"
http://linuxfinances.info/info/x.html
"Linux poses a real challenge for those with a taste for late-night
hacking (and/or conversations with God)." -- Matt Welsh
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] file-system snapshot under freebsd for backup
I use SAN, not NAS. :) Because some bugdet issue, I just have a basic SAN environment. For multipathing, I use geom_fox to complete. For snapshot, I want to use mksnap_ffs. But I don't have any experience of using mksnap_ffs to backup pgsql. Does anyone give me some advice? 2007/8/2, Bill Moran <[EMAIL PROTECTED]>: > > Maybe. I'm confused by your question, but: > * If the data directory is on an FFS2 volume on a FreeBSD machine, you > can use mksnap_ffs to back it up, and that approach is actually > recommended for PITR-type backups. > * If the data is mounted via NFS or something similar and the filesystem > is not FFS2, then snapshots are not available on FreeBSD. > > -- > Bill Moran > http://www.potentialtech.com > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Restrict access
On 2/8/07 13:35, in article [EMAIL PROTECTED], "Michael Knudsen" <[EMAIL PROTECTED]> wrote: > Hi, > > I have created a database and imported a lot of data. I would like to > share this database with other people but they should not be able to > change anything. That is, they should only be allowed to use the > SELECT command. > > If I create a user, this user can access the database and see the > names of all tables. I can now GRANT this user permission to use > SELECT on all tables. Apparently, this seems to solve the problem. > However, I have noticed that if people access the database using my > username (i.e. by typing "psql foo -u" and the providing my username) > they get full access. I have tried to disable this by "ALTER USER > username WITH PASSWORD 'password'" but that doesn't help. You still > don't need to supply any password. > > I have used Google for several hours with coming even near a solution > so any help is appreaciated! Take a look at the pg_hba.conf in the PGDATA directory, you probably have the connection set to 'trust'... Which won't require a password from that connection location/type. You will need to run a "pg_ctl reload" to make any changes take effect HTH Adam ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] GiST index implementation
Hi list, what is the default implementation for GiST index? B-Tree or R-Tree? That is, if i execute the following SQL command: CREATE index ON table USING Gist (column) what is the type of the index that is actually built? If I specify R-Tree instead, with: CREATE index ON table USING Rtree (column) I got this message: NOTICE: substituting access method "gist" for obsolete method "rtree" and when I look at the SQL code that created the index in pgAdmin, I found again CREATE index ON table USING Gist (column). How can I specify in SQL one of the two implementations provided (e.g. R-Tree)? Thank you for helping. Regards, -Elena
Re: [GENERAL] file-system snapshot under freebsd for backup
The size of my db is more then 50GB , so I consider that the pg_dump is not a good backup tool for me. And, sometimes, I need query data which is some days ago ( between 1 ~ 14 days ). If I want to transfer my db to different PG version, I will use slony-I. :) I worried that mksnap_ffs spend much time on making a new snapshot. I don't know whether PG work very well during the period of making snapshot. Or the snapshot be made successfully when the PG execute / commit sql statments continuously. 2007/8/2, Naz Gassiep <[EMAIL PROTECTED]>: > Have you considered using pg_dump to backup your databases? Taking > file-level snapshots is not recommended as you may want/need to restore to a > different version of PG that is not binary compatible with the snapshot > you've taken. > Regards, > - Naz. > > > ProAce wrote: > I already put the data directory on hds san storage, but there is > no snapshot license on it. Could I use mksnap_ffs under freebsd to make > snapshot for backup ? ---(end of > broadcast)--- TIP 6: explain > analyze is your friend > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Hi all! Everyone who use PostgreSQL server on Windows knows - it would be nice to have some tray management and monitoring tool for PostgreSQL server which is running as NT Service (for example - MS SQL already have such tool). I have created a new project on pgfoundry - http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can we improve this tool and what features whould be helpful. Thanks, Andrei. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Error installing postgresql-8.2.4 on windows 2003 server
Hkrenske wrote: I have been successful in installing to Windows 2000 but when installing the binary installation “postgresql-8.2.msi” as a service, I receive the error The program "postgres" is needed by initdb but was not found in the same directory as "C:/Program Files/PostgreSQL/8.2/bin/initdb". Check your installation. I have checked and found both the postgres.exe and initdb.exe files in the directory "C:/Program Files/PostgreSQL/8.2/bin/initdb". Does anyone have any ideas what the problem could be? Hugh Try to run initdb under non-privileged user. You can use runas /user:username "initdb -D ..." Andrei. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Error installing postgresql-8.2.4 on windows 2003 server
I have been successful in installing to Windows 2000 but when installing the binary installation "postgresql-8.2.msi" as a service, I receive the error The program "postgres" is needed by initdb but was not found in the same directory as "C:/Program Files/PostgreSQL/8.2/bin/initdb". Check your installation. I have checked and found both the postgres.exe and initdb.exe files in the directory "C:/Program Files/PostgreSQL/8.2/bin/initdb". Does anyone have any ideas what the problem could be? Hugh
Re: [GENERAL] Linux distro
On 8/2/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > > "Merlin Moncure" <[EMAIL PROTECTED]> writes: > > > A server with a GUI sitting on a login screen is wasting zero > > resources. Some enterprise management tools are in java which require > > a GUI to use so there is very little downside to installing X, so IMO > > a lightweight window manager is appropriate...a full gnome is maybe > > overkill. Obviously, you want to turn of the 3d screen saver :-) > > That's kind of the crux of it. X sessions tend to do things like run 3d screen > savers, periodically check cdrom drives for new disks, periodically wake up to > update load graphs or network graphs, etc. > > Even login screens are getting fancier and even the regular non-3d screen > saver is a problem. > > For a benchmark machine you really don't want to find out after you run your > benchmarks that there are mysterious spikes or dips and have to waste energy > tracking down where they come from. > > I'm unclear why you would be running the enterprise management tools on > individual machines though. Isn't the point of enterprise management tools > that you can manage the whole enterprise? Ie, that they work remotely? they do, but experience has shown it is prudent to be able to administrate the hardware directly from the box. I expect trend of desktop style management to continue (for the record, I would really prefer these devices to present html interfaces vs. java). Also, I just checked cpu usage of X on my desktop and it was using 0.03 seconds of cpu time every 20 seconds or so, or about 0.18%, some of which was used to update top on screen (i was running a failsafe terminal)...gdm, etc are completely idle. ubuntu, at least, gives you nothing you have to turn off. I'm actually recently converted from the 'anti-x' camp. This is because I'm now using linux on a desktop and found it to be remarkably efficient but also was recently in a situation where I regretted not having it installed. I completely understand and sympathize with the other side of the argument however. I just don't care anymore, maybe I'm getting old :-). merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pgpool2 vs sequoia
Hi, I have some experience with HA-JDBC and on the whole it is pretty good (very easy to setup), with it's only sight weakness being it synchronisation (renders the DB read only) but this is to be improved in the future. I have tried to setup sequioa but it is pretty complex (and more heavyweight than ha-jdbc), and i never quite managed to get it working exactly as i wanted it to. Cheers, Andy On 02/08/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Am Donnerstag, 2. August 2007 12:04 schrieb Andy Dale: > > Hi, > > > > You might also want to check out HA-JDBC at > http://ha-jdbc.sourceforge.net > > thanks for this suggestion, so i have three options to choose from: > > - pgpool2 > - sequoia > - ha-jdbc > > Can someone share his experience on these? > > kind regards > janning > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq >
Re: [GENERAL] file-system snapshot under freebsd for backup
In response to ProAce <[EMAIL PROTECTED]>: > I already put the data directory on hds san storage, but there is no > snapshot license on it. > Could I use mksnap_ffs under freebsd to make snapshot for backup ? Maybe. I'm confused by your question, but: * If the data directory is on an FFS2 volume on a FreeBSD machine, you can use mksnap_ffs to back it up, and that approach is actually recommended for PITR-type backups. * If the data is mounted via NFS or something similar and the filesystem is not FFS2, then snapshots are not available on FreeBSD. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgpool2 vs sequoia
Am Donnerstag, 2. August 2007 12:04 schrieb Andy Dale: > Hi, > > You might also want to check out HA-JDBC at http://ha-jdbc.sourceforge.net thanks for this suggestion, so i have three options to choose from: - pgpool2 - sequoia - ha-jdbc Can someone share his experience on these? kind regards janning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgpool2 vs sequoia
Hi, You might also want to check out HA-JDBC at http://ha-jdbc.sourceforge.net Cheers, Andy On 02/08/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Hi, > > i would like to use a statement replication for postgresql > > i have found the following solutions: > - pgpool > - pgpool2 > - sequoia (jdbc, formerly known as c-jdbc) > > pgpool has only two nodes, so this is not an option. > > I will never change my underlying database, of course :-) > So i dont have any advantage of the sequoia jdbc abstraction. i'd rather > sometimes use other tools than jdbc to access the database with some perl > scripts, so this is an advantage for pgpool2. but sequoia looks very nice > at > first glance. > > What are other advantages/disadvantages in respect of > - performance/scalability > - stability > - support / ease-of-use / etc. > > i would be very happy if someone can share his/her expierence with such a > solution. > > kind regards, > janning > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster >
[GENERAL] pgpool2 vs sequoia
Hi, i would like to use a statement replication for postgresql i have found the following solutions: - pgpool - pgpool2 - sequoia (jdbc, formerly known as c-jdbc) pgpool has only two nodes, so this is not an option. I will never change my underlying database, of course :-) So i dont have any advantage of the sequoia jdbc abstraction. i'd rather sometimes use other tools than jdbc to access the database with some perl scripts, so this is an advantage for pgpool2. but sequoia looks very nice at first glance. What are other advantages/disadvantages in respect of - performance/scalability - stability - support / ease-of-use / etc. i would be very happy if someone can share his/her expierence with such a solution. kind regards, janning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux distro
Gregory Stark wrote: "Merlin Moncure" <[EMAIL PROTECTED]> writes: A server with a GUI sitting on a login screen is wasting zero resources. Some enterprise management tools are in java which require a GUI to use so there is very little downside to installing X, so IMO a lightweight window manager is appropriate...a full gnome is maybe overkill. Obviously, you want to turn of the 3d screen saver :-) That's kind of the crux of it. X sessions tend to do things like run 3d screen savers, periodically check cdrom drives for new disks, periodically wake up to update load graphs or network graphs, etc. Spent a happy afternoon some years ago trying to figure out why an NT server would be fine while I was checking its settings, but would seem to crawl after half an hour. Turned out it had some funky 3D screensaver enabled - it'd grind to a halt, I'd come up, hit the spacebar and not find anything slowing the system down. Obvious once, I'd got the system monitoring turned on, but PITA until then. Moral: If it's not doing something immediately useful, I don't want it running on my server. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Survey on changing a Free/Open Source Software project
Hello Developers, your experiences with working with Free Software / Open Source projects is needed. As part of my PhD thesis and with the goal to support the community to understand itself better, I am trying to find out how projects change the way they work over time, in particular what YOU did to change them. I have put together a small survey (11 questions, which should take max. 15 minutes) that ask you for your experiences in putting your ideas into reality (for instance when you convinced your project to switch to Subversion, use a new build tool, choose a plug-in architecture, or failed to switch to a different license). http://survey.mi.fu-berlin.de/public/survey.php?name=changeProcess&test=1 The PostgreSQL-General list is the first list I am contacting with this, so it might be that the survey contains some questions that are difficult to understand or misleading. If you find something that needs correcting, please let me know, ASAP. Thank you very much, your participation is much appreciated! Christopher Oezbek P.S.: Results will be made available at the end of August on http://www.inf.fu-berlin.de/w/SE/SurveyChange ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] parsed queries (cursors) cashing issues
The problem is that I can't find the way to exclude query parsing (prepare step) for custom queries. In other words I want to create a function that accepts a query text with "$1, $2, etc." and variables as params, executes the query and returns a set of record. I could use 'execute' in plpgsql but in such case a query will be parsed each time it is called. I check SPI and found the way to store execution plans for the duration of the session, but no convenient way to check if the plan was already generated for the query. So I should create and store hash table by myself, and associate plan pointers and query hash by myself. I'm not a C/C++ guy so it's not an easy task for me :). Is there any way to solve the problem? By the way - why not to store hashes for queries and execution plans in a shared pool to have an opportunity not to parse already parsed queries for any session as Oracle does?
Re: [GENERAL] Linux distro
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > A server with a GUI sitting on a login screen is wasting zero > resources. Some enterprise management tools are in java which require > a GUI to use so there is very little downside to installing X, so IMO > a lightweight window manager is appropriate...a full gnome is maybe > overkill. Obviously, you want to turn of the 3d screen saver :-) That's kind of the crux of it. X sessions tend to do things like run 3d screen savers, periodically check cdrom drives for new disks, periodically wake up to update load graphs or network graphs, etc. Even login screens are getting fancier and even the regular non-3d screen saver is a problem. For a benchmark machine you really don't want to find out after you run your benchmarks that there are mysterious spikes or dips and have to waste energy tracking down where they come from. I'm unclear why you would be running the enterprise management tools on individual machines though. Isn't the point of enterprise management tools that you can manage the whole enterprise? Ie, that they work remotely? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] What do people like to monitor (or in other words, what might be nice in pgsnmpd)?
Josh Tolley wrote: > So please respond, if you feel so inclined, describing things you like > to monitor in your PostgreSQL instances as well as things you would > like to be able to easily monitor in a more ideal world. I can think of a few things I'd like to be able to monitor... Connection usage: - total number of connections - number of idle vs active connections - total number per user/database - number of idle vs active connections per user/database I'm not entirely sure whether to split on user or on database or maybe both? Also interesting: The number of queries that take more than an arbitraty amount of time to complete. Maybe per user/database? I suppose this number is only interesting on an uncongested database server. Otherwise there will be queries passing that treshold that normally wouldn't, because they have to wait for the real troublemakers to finish. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Linux distro
On 8/2/07, Madison Kelly <[EMAIL PROTECTED]> wrote: > What I do is install Gnome, "just in case" I need it for some reason > (ie: opening many terminal windows at a higher res that I can alt+tab > between). ssh and/or screen ... > Madi Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux distro
Ron Johnson wrote: Pardon me for being the contrarian, but why does a server need a GUI? Isn't that just extra RAM & CPU overhead that could be more profitably put to use powering the application? What I do is install Gnome, "just in case" I need it for some reason (ie: opening many terminal windows at a higher res that I can alt+tab between). Then once the install is done I delete the '/etc/rc2.d/S??gdm' file, then '/etc/init.d/gdm stop'. Problem solved. :) This gives me the *option* of using a GUI without it wasting any resources besides some disk space. Madi ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Linux distro
On 8/2/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > I think most of the virtual memory used by X is actually the map of the > > > graphics card's memory AFAIK, so it's not as significant as you think. > > That machine has an on-board chipset (i845) and has only 8MB > > shared memory allotted to the card > You don't seem familiar with the meaning of VIRT in the memory > allocation listing there. That'll be Alvaro, maybe? I'm quite aware of it, I just pointed out to him that the VSIZE is NOT the devices memory mapping. And I still think that running X on a server is unnecessary, wasteful and potentially harmful. The less you run, the less can go wrong, the less potential exploits you have. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
