Re: [GENERAL] pg_buffercache's usage count
Ben Chobot wrote: On Feb 23, 2010, at 3:06 PM, Ben Chobot wrote: I'm looking at the usage count column of pg_buffercache's info, and I'm confused. Several buffers that supposed have LRU values of 5 belong to non-unique indices which supposedly have never been used. As I understand things, that shouldn't happen. Am I missing something? (And maybe more to the point, when does the LRU go down in value?) Usage counts only go up when a page is pinned because some backend requested that particular block for its work via a call to BufferAlloc with the file/block it needs. Usage counts go down every time the clock sweep hand looking to allocate new buffers passes over that page during its constant circular scanning of the buffer cache. The mechanics involved are described in a pretty detailed way by the Inside the PostgreSQL Buffer Cache presentation at http://www.westnet.com/~gsmith/content/postgresql/ (and no where else I'm aware of, besides the source code itself). -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Not able to change the owner of function
Jignesh Shah wrote: could you tell me what could be the issue in below command. I could see that there is an option for changing OWNER of function but not sure why it is giving this error. techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip; ERROR: unrecognized configuration parameter owner techdb=# Just use the correct syntax as documented: ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) OWNER TO new_owner i.e., without the SET. Yours, Laurenz Albe -- 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]
Shu Ho wrote:
do you clean up the server file by removing them
use
find $logfile -mtime +$NUMBER_DAYS_TO_KEEP -type f -print
-exec rm -f {} \;
in postgres ?
I do it similarly, but I don't use -exec, I rather pipe the results
of find into something like xargs rm -f for better performance.
How to remove the archive log files in postgres ? is the same
way as remove backup files and server log files ?
Yes.
Just make very sure that you don't delete any archived WAL files
that you might still need - say, to resort to an older backup if
your most recent backups failed or the tape got eaten by mice
or whatever.
Yours,
Laurenz Albe
--
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] Explaining duplicate rows in spite of unique index
I wrote: We recently found a couple of rows in a production database that had identical values in the columns constituting the primary key (The problem surfaced because a pg_dump could not be restored). Now I'm looking for explanations how this could happen. The rows originate from around the time when we had a hardware failure that corrupted the file system. The database came up after a file system check, and people continued working until we noticed that some tables were corrupted. At that point we restored an online backup and recovered past the time of the hardware failure. The WALs were intact and recovery completed successfully. Now does the following explanation sound plausible: [...] For the record, I set up a small experiment and could reproduce the corruption like this: - initdb new cluster, start the postmaster - create a table with a primary key constraint - perform an online backup - stop the postmaster - cp -p pg_clog/ pg_clog/.bak - start the postmaster - insert a record, commit - stop the postmaster - mv pg_clog/.bak pg_clog/ - start the postmaster - Insert and commit a second record with the same primary key. This will succeed since the previous commit is gone. - stop the postmaster - perform PITR from the online backup to the end of WALs The PITR will succeed, and you end up with two rows with the same primary key, but only the second one is in the unique index. Here's what I learned: - Don't let anybody work on a database after a hardware problem that affected the integrity of the file system, even if the postmaster comes up without complaining. - Never perform point-in-time-recovery beyond the time when the hardware problem happened. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction isolation when applying DDLs
Hello, I'm considering to apply DDLs (such as altering columns or constraints) without stopping the application running against the database. This is quite unusual but could work under certain circumstances, because the application is aware of any change applied to the underlying database structures. By experimenting with transactional DDL I noticed that the database truly handles them atomically, even in complex cases, up to hundreds or thousands of DDL statements. My question: How is transaction isolation defined when applying DDLs? Some experiments showed this behaviour: 1. external transactions seems to be fully isolated from column adds drops in a DDL transaction (structurally speaking, a repeateable read) 2. external transactions seems to be fully isolated from dropping and recreating a column in a DDL transaction (structurally speaking, a repeateable read) 3. external transactions report an error like could not open relation with OID xx when the DDL transaction drops and recreates a single table, all tables or an entire schema This is more than acceptable, as dropping/recreating structures is not likely to happen in a productive system. What else should I know/consider? Thanks Luigi Antognini
Re: [GENERAL] make check failed on 8.4.2 install
On 23 Feb 2010, at 21:34, Tom Lane wrote: adam_pgsql adam_pg...@witneyweb.org writes: On 23 Feb 2010, at 17:41, Tom Lane wrote: That's very peculiar. It looks more like dynamic linker breakage than Postgres' fault, though. What platform is this (no, the kernel version doesn't do it for me)? What have you changed since you last built Postgres successfully? This is Debian 4.0. I haven't changed anything on that box recently. My current working version is 8.2.12. As a test I just successfully ran configure, make and make check for 8.3.8. But both a fresh tarball for 8.4.1 and 8.4.2 fail with the same error. Hmph. We have several debian boxes in the buildfarm and none of them have ever showed a failure like this AFAIR: http://buildfarm.postgresql.org/cgi-bin/show_status.pl What I'd suggest is taking a close look at the link commands that build libpq.so and pg_ctl. Compare the 8.3 and 8.4 cases on your box, and also look at the make logs from the Debian buildfarm animals, to see if you can see what's being done differently. BTW, so far as I can tell in a quick look at the sources, the only explicit call of dlopen() anywhere in Postgres is in the backend, not in either pg_ctl or libpq. So you're definitely dealing with a dynamic linker foulup, not a real missing reference. I checked the output from make but couldn't see anything particular different apart from the removal of -Winline everywhere. However I have narrowed down the problem to openssl. ./configure without openssl works ok for 8.4.2, but ./configure --with-openssl --with-libraries=/usr/local/ssl/lib/ --with-includes=/usr/local/ssl/include/ still fails. The server has OpenSSL 0.9.8i installed, but upgrading to the latest OpenSSL 0.9.8l still fails. This is gcc version 2.95.4 if that helps? thanks again adam -- 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 check failed on 8.4.2 install
adam_pgsql wrote: This is gcc version 2.95.4 if that helps? It's a very old version of gcc, and also one that has never been officially released, according to the release notes. Run aptitude show gcc-2.95 and see the description of the package. You don't want to use that as your default compiler. Debian 4 comes with gcc-4.1, which is what you should get when you install the package named gcc. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Curious plperl behavior
Ran into this switching a DBI based thing into a plperl function. The
root cause is probably a perl variable scope thing, but still this is
very interesting behavior and may trip up others.
Given code such as this:
create or replace function plperlhell()
returns int
as $$
# prepare a plan, call a func that runs it,
# then free it.
#
# then call this again
#
# mimic use strict; but works in pl/perl
BEGIN { strict-import(); }
my $plan = spi_prepare(select version());
elog(NOTICE, Plan: $plan);
testfunc($plan);
spi_freeplan($plan);
$plan = beef;
elog(NOTICE, plan now $plan);
sub testfunc
{
my($arg) = @_;
elog(NOTICE, in testfunc, plan: $plan arg: $arg);
my $rv = spi_exec_prepared($plan);
elog(NOTICE, Results: $rv);
}
$$
language 'plperl';
we prepare a statement and then testfunc() is a helper which ends up
doing the actual exec'ing (in reality, after its done work on the
data). What I ran into was on subsequent calls to the plperl func
(not testfunc()) was I'd get an spi_exec_prepared error that the plan
was missing. When you run the above in 8.2, 8.3 or 8.4 (8.3 4 on
linux, 2 on osx perl verions 5.8.8 in both:
postgres=# select plperlhell();
NOTICE: Plan: 49abf0
NOTICE: in testfunc, plan: 49abf0 arg: 49abf0
NOTICE: Results: HASH(0x886578)
NOTICE: plan now beef
plperlhell
(1 row)
postgres=# select plperlhell();
NOTICE: Plan: 49abf0
NOTICE: in testfunc, plan: beef arg: 49abf0
ERROR: error from Perl function: spi_exec_prepared: Invalid prepared
query passed at line 26.
Notice on the second run the plan is still beef when it was set to
49abf0 (which when passed as the arg is correct)
Any perl gurus have any further info on this? It was a bit surprising
to encounter this. I'm guessing it has something to do with variable
scope and the fact plperl funcs are just anonymous functions.
Stuffing it in $_SHARED seems to work fine and ends up with results as
one would expect.
--
Jeff Trout j...@jefftrout.com
http://www.stuarthamm.net/
http://www.dellsmartexitin.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] pg_buffercache's usage count
Greg Smith wrote: Ben Chobot wrote: On Feb 23, 2010, at 3:06 PM, Ben Chobot wrote: I'm looking at the usage count column of pg_buffercache's info, and I'm confused. Several buffers that supposed have LRU values of 5 belong to non-unique indices which supposedly have never been used. As I understand things, that shouldn't happen. Am I missing something? (And maybe more to the point, when does the LRU go down in value?) Usage counts only go up when a page is pinned because some backend requested that particular block for its work via a call to BufferAlloc with the file/block it needs. BTW the only reason you don't see buffers having a larger usage is that the counters are capped at that value. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Row ordering after CREATE TABLE AS...SELECT regexp_split_to_table(source_text, regexp) AS new_column
John Gage wrote: Unfortunately, it prompts a new question. I am using 8.4.2 which I assume is new enough to trigger a yes response to If you have a version new enough to have synchronize_seqscans I have absolutely no idea how to turn that off. Perhaps the best thing would be to direct me to the documentation where turning it off is described so that I can become more autonomous. However, accompanying that with explicit directions would be welcome too. See postgresql.conf, but you probably want to leave it turned on in general and turn it off only for the specific case of this usage. (Using the SET command, or ALTER ROLE, or ALTER DATABASE). See here http://www.postgresql.org/docs/8.4/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL install fails with 1603 error
PostgreSQL version: 8.2.15 Operating system: Windows 2003 PostgreSQL 8.2 install fails with exit code 1603. Any idea?? what could be wrong. I am running install from console. I mean no RDP and using administrator AD account.
Re: [GENERAL] PostgreSQL install fails with 1603 error
In response to Mitesh Patel : PostgreSQL version: 8.2.15 Operating system: Windows 2003 PostgreSQL 8.2 install fails with exit code 1603. Any idea?? what could be wrong. I am running install from console. I mean no RDP and using administrator AD account. I can't help you, i'm not familiar with windows, but why this old version? Current version is 8.4.2. Regards, 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] select t.name from tbl t (where name is not a column name)
Joe,
What PG version are running?
8.2 here complains when running your example:
ERROR: column foo.name does not exist
LINE 6: select foo.name from foo;
^
** Error **
ERROR: column foo.name does not exist
SQL state: 42703
Igor Neyman
-Original Message-
From: Joe Conway [mailto:m...@joeconway.com]
Sent: Tuesday, February 23, 2010 9:19 PM
To: pgsql-general@postgresql.org
Subject: Re: select t.name from tbl t (where name is not a
column name)
On 02/23/2010 05:07 PM, raf wrote:
i've just noticed the following behaviour and was wondering
if there's
any documentation to explain what it's for.
create table tbl(id serial primary key, a text, b text, c text);
insert into tbl(a, b, c) values ('abc', 'def', 'ghi');
insert into tbl(a, b, c) values ('jkl', 'mno', 'pqr');
insert into tbl(a, b, c) values ('stu', 'vwx', 'yza');
select t.name from tbl t;
I forget exactly where this is documented (and could not find
it with a quick look), but calling t.name is the same as
name(t) if a column reference is not found, and name is a
function, which it is.
So t.name is essentially casting the whole row as a name
datatype and outputting the result. Try it with text:
test=# \d foo
Table public.foo
Column | Type | Modifiers
+-+---
f | integer |
test=# select foo.text from foo;
text
--
(-1)
(1 row)
test=# drop TABLE foo;
DROP TABLE
test=# create table foo(f int, text text); CREATE TABLE
test=# insert into foo values(-1,'abc'); INSERT 0 1
test=# select foo.text from foo;
text
--
abc
(1 row)
test=# select foo.name from foo;
name
--
(-1,abc)
(1 row)
HTH,
Joe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] archive_timeout in postgresql.conf
Hi All - I am trying to set up the PG_STANDBY on our database setup. our requirement is, In case of disaster we should be able to bring up standby, the lag time allowed in our setup is up to 2 hours. The question I have is, what should be the value I set for the archive_timeout in the postgressql.conf file? Can you please help? Regards
[GENERAL] C function manipulating tsquery doesn't work with -O2
http://psql.privatepaste.com/53cde5e24a I've the above function. Output is something like: '9788876412646':A | ( '8876412646':A | ( 'edizioni':D | ( 'quasi':B | ( 'estat':B | ( 'levi':C | ( 'lia':C | ( 'e/o':D | 'un':B ) ) ) ) ) ) ) It seems it always work with -O0 I can make it work with -O2 in: select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items limit 2; -- q1 select itemid, tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items; -- q2 select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items where itemid=10 or itemid=15 or itemid=27; -- q3 select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items order by random() limit 3; -- q4 As soon as I run select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items limit 3; -- q5 it commit seppuku in 2 ways: ERROR: unrecognized operator type: 50 (first run) or ERROR: stack depth limit exceeded HINT: Increase the configuration parameter max_stack_depth, after ensuring the platform's stack depth limit is adequate. This looks to happen in infix() tsquery.c This doesn't seem to be related with length of the tsvector passed. Crazily increasing the only palloc in the function (3x) doesn't solve the problem. This is what I get back from gdb once the function exit: (gdb) backtrace #0 0x0053739a in ?? () #1 0x00536fd4 in ExecProject () #2 0x0053d150 in ExecScan () #3 0x00536470 in ExecProcNode () #4 0x00549ea0 in ExecLimit () #5 0x00536458 in ExecProcNode () #6 0x00534337 in ExecutorRun () #7 0x005d6b2b in ?? () #8 0x005d8339 in PortalRun () #9 0x005d2de9 in ?? () #10 0x005d4624 in PostgresMain () #11 0x005a6c68 in ?? () #12 0x005a7b30 in PostmasterMain () #13 0x0055aaae in main () version is: PostgreSQL 8.3.9 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 -- 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] select t.name from tbl t (where name is not a column name)
On 02/24/2010 07:16 AM, Igor Neyman wrote: Joe, What PG version are running? 8.2 here complains when running your example: ERROR: column foo.name does not exist LINE 6: select foo.name from foo; ^ ** Error ** ERROR: column foo.name does not exist SQL state: 42703 Prior to 8.3 you aren't able to cast a rowtype as text or name datatype, so no matching function is found. - in 8.3.x - contrib_regression=# select text(foo) from foo; text -- (-1) (1 row) - in 8.2.x - contrib_regression=# select text(foo) from foo; ERROR: function text(foo) does not exist LINE 1: select text(foo) from foo; ^ HINT: No function matches the given name and argument types. You may need to add explicit type casts. Joe signature.asc Description: OpenPGP digital signature
[GENERAL] bugs reporting
Hello Admin, Can you please grant me access to post a bug report on pgsql-general section?? Thanks, Mitesh
Re: [GENERAL] how to clear server log
this is the server log file, how many days server log files need to be keep
as a mimumum ?
thanks
On Tue, Feb 23, 2010 at 1:49 PM, Amy Smith vah...@gmail.com wrote:
All
do you clean up the server file by removing them
use
find $logfile -mtime +$NUMBER_DAYS_TO_KEEP -type f -print -exec rm -f {}
\;
in postgres ?
How to remove the archive log files in postgres ? is the same way as remove
backup files and server log files ?
thanks
Amy
Re: [GENERAL] Npgsql connection string editor?
Hi!
This code is for initial design time support on VS.net 2003.
We are still working to get vs.net 2005 design time support as the
code to support it changed completely.
Sorry for this confusion. I'll check the docs to clarify this.
On Tue, Feb 23, 2010 at 17:00, Radcon Entec radconen...@yahoo.com wrote:
Greetings!
I have found references on the Internet to a connection string designer for
npgsql, but I haven't found where to get it. I don't seem to have it with
my download of npgsq. Or am I just looking in the wrong place?
For example, http://npgsql.projects.postgresql.org/exampleprograms.html ,
which is from the main npgsql web site, contains the following:
public void ConnectToData()
{
string DSN;
Npgsql.Design.ConnectionStringEditorForm Ndesign = new
Npgsql.Design.ConnectionStringEditorForm();
Ndesign.ShowDialog();
DSN = Ndesign.ConnectionString.ToString();
if (DSN == )
return;
conn = new NpgsqlConnection(DSN);
dset = new DataSet(npdata);
NpAdapter = new NpgsqlDataAdapter();
NpAdapter.SelectCommand = new NpgsqlCommand(query, conn);
NpAdapter.Fill(dset, npdata);
dtsource = dset.Tables[npdata];
deleteCmd();
updateCmd();
insertCmd();
}
How can I get ConnectionStringEditorForm?
RobR
--
Regards,
Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior
Sent from Brasilia, DF, Brazil
--
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] bugs reporting
On 24/02/2010 14:20, Mitesh Patel wrote: Can you please grant me access to post a bug report on pgsql-general section?? You don't need any special access - have a look at this page: http://www.postgresql.org/support/submitbug Or you could just post to this list about your problem - feedback is usually pretty quick and effective. :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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_buffercache's usage count
Alvaro Herrera wrote: BTW the only reason you don't see buffers having a larger usage is that the counters are capped at that value. Right, the usage count is limited to 5 for no reason besides that seems like a good number. We keep hoping to come across a data set and application with a repeatable benchmark where most of the data ends up at 5, but there's still a lot of buffer cache churn, to allow testing whether a further increase could be valuable. So far nobody has actually found such a set. If I shrunk shared_buffers on Ben's data I think I could create that situation. As is usually the case, I doubt he has another server with 128GB of RAM hanging around just to run that experiment on though, which has always been the reason why I can't simulate this more easily--systems it's prone to happening on aren't cheap. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cast char to number
I have a character field I want to change to a number. The values in that field are all numbers that may or may not be padded with spaces or 0's. What is the best way to do that? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.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] Cast char to number
In response to Christine Penner christ...@ingenioussoftware.com: I have a character field I want to change to a number. The values in that field are all numbers that may or may not be padded with spaces or 0's. What is the best way to do that? Put the values in numeric fields to begin with and cast to chars as needed. Basically reverse what you're doing. -- 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] Cast char to number
I don't understand what you mean. This is a column in a table that is already a char and has numbers in it. I want it to be a number field not character. How can I change the data type of that column without loosing the data I have in it? Christine At 11:38 AM 24/02/2010, you wrote: In response to Christine Penner christ...@ingenioussoftware.com: I have a character field I want to change to a number. The values in that field are all numbers that may or may not be padded with spaces or 0's. What is the best way to do that? Put the values in numeric fields to begin with and cast to chars as needed. Basically reverse what you're doing. -- 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 -- 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] Cast char to number
On 24/02/2010 19:53, Christine Penner wrote: I don't understand what you mean. This is a column in a table that is already a char and has numbers in it. I want it to be a number field not character. How can I change the data type of that column without loosing the data I have in it? Christine At 11:38 AM 24/02/2010, you wrote: In response to Christine Penner christ...@ingenioussoftware.com: I have a character field I want to change to a number. The values in that field are all numbers that may or may not be padded with spaces or 0's. What is the best way to do that? Put the values in numeric fields to begin with and cast to chars as needed. Basically reverse what you're doing. I think what he means is that you should have been doing the reverse to begin with - storing numbers in the database as numeric columns, and then casting them to a character format as needed for display. However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. (iv) Rename the new column to the same name as the old column (v) Recreate any of the constraints dropped in step (iii). I think the cast in step (ii) might not be necessary - not sure about this. HTH. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Cast char to number
On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. Or, in any recent version of PG you can do this via ALTER TABLE http://www.postgresql.org/docs/8.4/static/sql-altertable.html ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; You might want to clean up the values before doing this. -- 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] Cast char to number
Raymond O'Donnell wrote: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. (iv) Rename the new column to the same name as the old column (v) Recreate any of the constraints dropped in step (iii). Or try in a single step: ALTER TABLE tablename ALTER column column_name TYPE numeric USING column_name::numeric; (replace numeric by the desired type if it's not numeric). Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] Cast char to number
On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. Or, in any recent version of PG you can do this via ALTER TABLE http://www.postgresql.org/docs/8.4/static/sql-altertable.html ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; You might want to clean up the values before doing this. That won't work in this case. char() can't be cast to int/numeric. Not only that it isn't possible to clean up the data in table because char automatically pads. postgres=# alter table foo alter column id type numeric; ERROR: column id cannot be cast to type pg_catalog.numeric postgres=# Joshua D. Drake -- Richard Huxton Archonet Ltd -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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] Cast char to number
In response to Raymond O'Donnell r...@iol.ie: On 24/02/2010 19:53, Christine Penner wrote: At 11:38 AM 24/02/2010, you wrote: In response to Christine Penner christ...@ingenioussoftware.com: I have a character field I want to change to a number. The values in that field are all numbers that may or may not be padded with spaces or 0's. What is the best way to do that? Put the values in numeric fields to begin with and cast to chars as needed. Basically reverse what you're doing. I don't understand what you mean. This is a column in a table that is already a char and has numbers in it. I want it to be a number field not character. How can I change the data type of that column without loosing the data I have in it? I think what he means is that you should have been doing the reverse to begin with - storing numbers in the database as numeric columns, and then casting them to a character format as needed for display. Actually, I misunderstood the question. I thought you were trying to figure out how to extract the data for display. But fixing the fields to be the right type is a noble goal :) However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. (iv) Rename the new column to the same name as the old column (v) Recreate any of the constraints dropped in step (iii). I think the cast in step (ii) might not be necessary - not sure about this. Agreed. There's a slightly shorter way, you can do: ALTER TABLE tablename ALTER COLUMN columnname TYPE INT; If that doesn't work because the cast isn't automatic, you can add a USING clause: ALTER TABLE tablename ALTER COLUMN columnname TYPE INT USING columnname::INT; (as an example, the using clause may need to be more complicate than that). -- 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] Cast char to number
In response to Joshua D. Drake j...@commandprompt.com: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. Or, in any recent version of PG you can do this via ALTER TABLE http://www.postgresql.org/docs/8.4/static/sql-altertable.html ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; You might want to clean up the values before doing this. That won't work in this case. char() can't be cast to int/numeric. Not only that it isn't possible to clean up the data in table because char automatically pads. postgres=# alter table foo alter column id type numeric; ERROR: column id cannot be cast to type pg_catalog.numeric postgres=# Remember that what comes after the using clause can be arbitrarily complex (I have written ALTER TABLE statements with USING CASE ... that are pages and pages long to fix data consistency problems in the same step as correcting a poorly chosen column type ;) -- 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] Cast char to number
On Wed, Feb 24, 2010 at 1:27 PM, Joshua D. Drake j...@commandprompt.com wrote:
On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote:
On 24/02/10 20:06, Raymond O'Donnell wrote:
However, to address your immediate problem, you could try something like
this:
(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.
Or, in any recent version of PG you can do this via ALTER TABLE
http://www.postgresql.org/docs/8.4/static/sql-altertable.html
ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;
You might want to clean up the values before doing this.
That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.
postgres=# alter table foo alter column id type numeric;
ERROR: column id cannot be cast to type pg_catalog.numeric
postgres=#
The example given works fine for me:
smarlowe=# create table abc (c char(10));
CREATE TABLE
smarlowe=# insert into abc values ('0010'),('90'),('66');
INSERT 0 3
smarlowe=# alter table abc alter column c type numeric using c::numeric;
ALTER TABLE
--
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] Cast char to number
On 24/02/10 20:27, Joshua D. Drake wrote:
On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote:
On 24/02/10 20:06, Raymond O'Donnell wrote:
However, to address your immediate problem, you could try something like
this:
(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.
Or, in any recent version of PG you can do this via ALTER TABLE
http://www.postgresql.org/docs/8.4/static/sql-altertable.html
ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;
You might want to clean up the values before doing this.
That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.
postgres=# alter table foo alter column id type numeric;
ERROR: column id cannot be cast to type pg_catalog.numeric
Well if it's actually char(10) or somesuch you need to do a little
more I grant you (though not much). I was assuming varchar myself.
richardh= CREATE TABLE intastext (i char(10));
CREATE TABLE
richardh= INSERT INTO intastext (i) VALUES ('1'),
('02'),('3.0'),('3.5'),('X');
INSERT 0 5
richardh= SELECT * FROM intastext ;
i
1
02
3.0
3.5
X
(5 rows)
richardh= ALTER TABLE intastext ALTER COLUMN i TYPE integer USING
i::text::numeric::integer;
ERROR: invalid input syntax for type numeric: X
richardh= DELETE FROM intastext WHERE i = 'X';
DELETE 1
richardh= ALTER TABLE intastext ALTER COLUMN i TYPE integer USING
i::text::numeric::integer;
ALTER TABLE
richardh= SELECT * FROM intastext ;
i
---
1
2
3
4
(4 rows)
Of course USING can have any expression to convert the type.
richardh= CREATE FUNCTION my_map(char(10)) RETURNS integer AS $$ SELECT
CASE WHEN $1='0' AND $1='9' THEN $1::numeric::integer ELSE -999 END;
$$ LANGUAGE SQL;
CREATE FUNCTION
richardh= ALTER TABLE intastext ALTER COLUMN i TYPE integer USING
my_map(i);ALTER TABLE
richardh= SELECT * FROM intastext ;
i
--
1
2
3
4
-999
(5 rows)
--
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] Curious plperl behavior
On Feb 24, 2010, at 8:44 AM, Jeff wrote:
Notice on the second run the plan is still beef when it was set to
49abf0 (which when passed as the arg is correct)
Any perl gurus have any further info on this? It was a bit
surprising to encounter this. I'm guessing it has something to do
with variable scope and the fact plperl funcs are just anonymous
functions.
Stuffing it in $_SHARED seems to work fine and ends up with results
as one would expect.
Thanks to RhodiumToad on irc for the pointer - posting this here for
posterity.
From perlref:
Thus is because named subroutines are created (and
capture any outer lexicals) only once at compile time, whereas
anony-
mous subroutines get to capture each time you execute the
'sub' opera-
tor. If you are accustomed to using nested subroutines in
other pro-
gramming languages with their own private variables, you'll
have to
work at it a bit in Perl. The intuitive coding of this type
of thing
incurs mysterious warnings about will not stay shared. For
example,
this won't work:
sub outer {
my $x = $_[0] + 35;
sub inner { return $x * 19 } # WRONG
return $x + inner();
}
A work-around is the following:
sub outer {
my $x = $_[0] + 35;
local *inner = sub { return $x * 19 };
return $x + inner();
}
Now inner() can only be called from within outer(), because of
the tem-
porary assignments of the closure (anonymous subroutine). But
when it
does, it has normal access to the lexical variable $x from the
scope of
outer().
--
Jeff Trout j...@jefftrout.com
http://www.stuarthamm.net/
http://www.dellsmartexitin.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] Cast char to number
On Wed, 2010-02-24 at 13:35 -0700, Scott Marlowe wrote:
You might want to clean up the values before doing this.
That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.
postgres=# alter table foo alter column id type numeric;
ERROR: column id cannot be cast to type pg_catalog.numeric
postgres=#
The example given works fine for me:
smarlowe=# create table abc (c char(10));
CREATE TABLE
smarlowe=# insert into abc values ('0010'),('90'),('66');
INSERT 0 3
smarlowe=# alter table abc alter column c type numeric using c::numeric;
ALTER TABLE
Well that is interesting. I would have thought it would have failed
because of the padding...
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or
Sir.
--
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] Cast char to number
Joshua D. Drake j...@commandprompt.com writes: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; That won't work in this case. char() can't be cast to int/numeric. Not only that it isn't possible to clean up the data in table because char automatically pads. postgres=# alter table foo alter column id type numeric; ERROR: column id cannot be cast to type pg_catalog.numeric That just indicates that there isn't an *implicit* coercion from char to numeric. With a USING clause you can specify an arbitrary conversion. I agree with the recommendation to test it out before actually doing the table change though. Maybe look at the results of select id, id::numeric from your_table to see if it looks sane for all the different data formats in the column. 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] Cast char to number
On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; That won't work in this case. char() can't be cast to int/numeric. Not only that it isn't possible to clean up the data in table because char automatically pads. postgres=# alter table foo alter column id type numeric; ERROR: column id cannot be cast to type pg_catalog.numeric That just indicates that there isn't an *implicit* coercion from char to numeric. With a USING clause you can specify an arbitrary conversion. I agree with the recommendation to test it out before actually doing the table change though. Maybe look at the results of select id, id::numeric from your_table to see if it looks sane for all the different data formats in the column. And if the database is mostly sitting idle (i.e. no other users) you can always just do begin; alter table yada; test how it went and then commit or rollback. For the OP: It's a bad idea to do that kind of stuff in production cause you'll put a lock on the table others will have to wait for. -- 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] Curious plperl behavior
Jeff thres...@threshar.is-a-geek.com writes: [ oracular excerpt from perlref ] So is this just a dark corner of Perl, or is plperl doing something to help you get confused? In particular, do we need to add anything to the plperl documentation? We're not trying to explain Perl to people, but if plperl is doing something that contributes to this, maybe it requires documentation. 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] Curious plperl behavior
On 24/02/10 20:55, Tom Lane wrote: Jeffthres...@threshar.is-a-geek.com writes: [ oracular excerpt from perlref ] So is this just a dark corner of Perl, or is plperl doing something to help you get confused? In particular, do we need to add anything to the plperl documentation? We're not trying to explain Perl to people, but if plperl is doing something that contributes to this, maybe it requires documentation. It is documented. http://www.postgresql.org/docs/8.4/static/plperl-funcs.html Note: The use of named nested subroutines is dangerous in Perl, especially if they refer to lexical variables in the enclosing scope. Because a PL/Perl function is wrapped in a subroutine, any named subroutine you create will be nested. In general, it is far safer to create anonymous subroutines which you call via a coderef. See the perldiag man page for more details. There's two ways to read that: 1. Dangerous in Perl - well, what isn't? 2. Dangerous in Perl - blimey, if they think it's dangerous, it must make lion-wrestling safe. -- 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] Curious plperl behavior
Richard Huxton d...@archonet.com writes: On 24/02/10 20:55, Tom Lane wrote: but if plperl is doing something that contributes to this, maybe it requires documentation. It is documented. http://www.postgresql.org/docs/8.4/static/plperl-funcs.html Note: The use of named nested subroutines is dangerous in Perl, especially if they refer to lexical variables in the enclosing scope. Because a PL/Perl function is wrapped in a subroutine, any named subroutine you create will be nested. In general, it is far safer to create anonymous subroutines which you call via a coderef. See the perldiag man page for more details. Hmm. Jeff found some relevant material on perlref. Should that link be added? Should the link(s) be more specific than telling you to read the whole d*mn man page? Neither of those pages are short, and each contains a wealth of material that isn't related to this issue. 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] Cast char to number
This is what I did. set all blank columns to '0' because they were causing errors. alter table T alter column a type integer using a::integer That worked perfectly. Thanks everyone for the help. Christine At 12:46 PM 24/02/2010, Scott Marlowe wrote: On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; That won't work in this case. char() can't be cast to int/numeric. Not only that it isn't possible to clean up the data in table because char automatically pads. postgres=# alter table foo alter column id type numeric; ERROR: column id cannot be cast to type pg_catalog.numeric That just indicates that there isn't an *implicit* coercion from char to numeric. With a USING clause you can specify an arbitrary conversion. I agree with the recommendation to test it out before actually doing the table change though. Maybe look at the results of select id, id::numeric from your_table to see if it looks sane for all the different data formats in the column. And if the database is mostly sitting idle (i.e. no other users) you can always just do begin; alter table yada; test how it went and then commit or rollback. For the OP: It's a bad idea to do that kind of stuff in production cause you'll put a lock on the table others will have to wait for. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Curious plperl behavior
On 24/02/10 21:34, Tom Lane wrote: Richard Huxtond...@archonet.com writes: On 24/02/10 20:55, Tom Lane wrote: but if plperl is doing something that contributes to this, maybe it requires documentation. It is documented. http://www.postgresql.org/docs/8.4/static/plperl-funcs.html Hmm. Jeff found some relevant material on perlref. Should that link be added? Should the link(s) be more specific than telling you to read the whole d*mn man page? Neither of those pages are short, and each contains a wealth of material that isn't related to this issue. Hmm - perhaps a suggestion to google for perl nested named subroutine. That seems to give a set of relevant results. Includes perldiag, perlref, our mailing lists and Apache's mod_perl (which makes sense). -- 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] Cast char to number
On 24/02/2010 21:42, Christine Penner wrote: This is what I did. set all blank columns to '0' because they were causing errors. alter table T alter column a type integer using a::integer That worked perfectly. Glad you got sorted. Bill's advice upthread is worth taking on board - if you're in a position to do so, make sure that numbers are stored as numbers rather than text, and you'll save yourself all sorts of wacky grief. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Cast char to number
In response to Raymond O'Donnell r...@iol.ie: On 24/02/2010 21:42, Christine Penner wrote: This is what I did. set all blank columns to '0' because they were causing errors. alter table T alter column a type integer using a::integer That worked perfectly. Glad you got sorted. Bill's advice upthread is worth taking on board - if you're in a position to do so, make sure that numbers are stored as numbers rather than text, and you'll save yourself all sorts of wacky grief. As an aside, I feel that this is a natural part of software evolution. No matter how carefully you try to plan, you end up with someone saying, this will be a serial number that will contain both numbers and letters ... so you make it a text type field. Then, a year later you find out that the serial number is really just a number, and you actually want to be able to do math on it because you can find out the year the part was designed by dividing by 1000 or something. You make the best decisions you can based on the available information. If you get it wrong, there's always ALTER TABLE :) -- 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] Curious plperl behavior
Richard Huxton d...@archonet.com writes: On 24/02/10 21:34, Tom Lane wrote: Hmm. Jeff found some relevant material on perlref. Should that link be added? Should the link(s) be more specific than telling you to read the whole d*mn man page? Neither of those pages are short, and each contains a wealth of material that isn't related to this issue. Hmm - perhaps a suggestion to google for perl nested named subroutine. That seems to give a set of relevant results. Includes perldiag, perlref, our mailing lists and Apache's mod_perl (which makes sense). Seems like a reasonable idea to me --- any objections? We should probably say search not google but otherwise seems like a fine solution. (BTW, I notice that one of the earlier hits is Andrew's suggestion to add the existing paragraph to our docs ;)) 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] Cast char to number
On 24/02/10 22:03, Bill Moran wrote: Then, a year later you find out that the serial number is really just a number, and you actually want to be able to do math on it because you can find out the year the part was designed by dividing by 1000 or something. You make the best decisions you can based on the available information. If you get it wrong, there's always ALTER TABLE :) Coming in 9.1: ALTER CUSTOMER ... SET REQUIREMENTS ... -- 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] Performance comparison
Hoi, I remember a while back someone posted a graphs showing a scalability of postgresql for various versions (I think 8.0 to 8.4). I've tried to find this image again but havn't been able to locate it. Does anyone here remember? Mvg, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] pg_buffercache's usage count
On Feb 24, 2010, at 11:09 AM, Greg Smith wrote: Alvaro Herrera wrote: BTW the only reason you don't see buffers having a larger usage is that the counters are capped at that value. Right, the usage count is limited to 5 for no reason besides that seems like a good number. We keep hoping to come across a data set and application with a repeatable benchmark where most of the data ends up at 5, but there's still a lot of buffer cache churn, to allow testing whether a further increase could be valuable. So far nobody has actually found such a set. If I shrunk shared_buffers on Ben's data I think I could create that situation. As is usually the case, I doubt he has another server with 128GB of RAM hanging around just to run that experiment on though, which has always been the reason why I can't simulate this more easily--systems it's prone to happening on aren't cheap. Well as it happens we *did* just get our third slony node in today, and it could spend some time doing burn-in experiments if it would be helpful. Unfortunately, I won't be able to drive the same load against it, so I don't know how useful it would be. -- 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] how to clear server log
On Feb 24, 2010, at 7:57 AM, Amy Smith wrote: this is the server log file, how many days server log files need to be keep as a mimumum ? thanks That question is up to you to answer. How far back do you want to be able to look? What do you do with your log files? As you no doubt have realized, more logs take more space and so you can't just keep everything, but generally the more the better. As a data point that may or may not be good advice for you, we keep a week's worth of log files, but mostly that's just for paranoia. We do all our processing in real-time or the day after. -- 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] How to get the permissions assigned to user?
On Tue, Feb 23, 2010 at 23:22, Jignesh Shah jignesh.shah1...@gmail.com wrote:
Hi,
Is there any way to get the set of permissions list assigned to user? I want
to know whether user has create table permissions on particular schema or
not?
See
http://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE.
You can of course troll through the system tables... You might find
psql -E useful if as it will show you the queries psql runs for the
backslash commands'\d').
For example:
$ psql -E
=\dpn
Schema | Name | Type |Access privileges |
Column access privileges
+--+---+--+--
public | a| table | | logged_session=arwdDxt/guy |
: read_only=r/guy
gives me the sql:
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S'
THEN 'sequence' END as Type,
pg_catalog.array_to_string(c.relacl, E'\n') AS Access privileges,
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS Column access privileges
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S') ORDER BY 1, 2;
--
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] Performance comparison
Martijn van Oosterhout wrote: I remember a while back someone posted a graphs showing a scalability of postgresql for various versions (I think 8.0 to 8.4). I've tried to find this image again but havn't been able to locate it. Does anyone here remember? http://suckit.blog.hu/2009/09/29/postgresql_history -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Possible causes for database corruption and solutions
Bruce Momjian wrote: Is changing the OS/X wal_sync_method default something we should consider? It's certainly reasonable to consider changing both OS X and Windows so wal_sync_method defaulted to fsync_writethrough, and provide safer operation by default on both those platforms. It would also result in a mass of complaints that PG 9.0 was much slower than 8.4 from people who were running it in an unsafe way before. Given that we're already starting to see that bad PR on Linux+ext4: http://www.phoronix.com/scan.php?page=articleitem=ext4_then_nownum=3 I wonder whether it's the right time for the reliable is the default on every platform to just suck up and adopt everywhere, if we're already going to be fighting this why is PG so slow on recent Linux versions? PR campaign anyway. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Missing clog, PITR
Patryk Sidzina wrote: 1) how do the clogs relate to wal shipping based replication? Clearly the master doesn't need that clog but the slave does. They should just be kept in sync. There's some useful background on this topic at http://old.nabble.com/control-the-number-of-clog-files-and-xlog-files-td19173165.html 2) could 'pg_clearxlogtail' in archive_command be a cause of this? This is our archive_command: 'cat %p | pg_clearxlogtail | gzip -c | ssh slavehost cat /var/lib/postgresql/replication/in/%f' Kind of doubt that. If pg_clearxlogtail were mangling your data, I'd expect a more dramatic failure to restore. 3) is there a faster way to debug this problem? Clogs fill slowly. It takes about a month on a very busy production server for a clog to be removed by master DB. You could create a bunch of transactions and then freeze things, following the ideas in the reference I suggested above. More info: PostgreSQL 8.2.14 64-bit (though this happened in older versions also) pg_standby from PostgreSQL 8.3.6 There was a bug in this area fixed in 8.2.10: http://www.postgresql.org/docs/8.2/static/release-8-2-10.html Fix potential miscalculation of datfrozenxid (Alvaro) * This error may explain some recent reports of failure to remove old pg_clog data. If you were running this database with a version before that, I wonder if maybe there's still some junk left behind from that old, buggy version that's causing your issues. You might try doing some manual VACUUM or VACUUM FREEZE work to remove any lingering issues and then re-create your standby systems afterwards. I'm not quite familiar enough with this specific bug to suggest a clearer resolution path, or if in fact this is the same issue you're seeing. It sure seems possible they're related though. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] archive_timeout in postgresql.conf
akp geek wrote: I am trying to set up the PG_STANDBY on our database setup. our requirement is, In case of disaster we should be able to bring up standby, the lag time allowed in our setup is up to 2 hours. The question I have is, what should be the value I set for the archive_timeout in the postgressql.conf file? Can you please help? You might as well set it to something similar in scale to checkpoint_timeout; 5 or 10 minutes would be completely reasonable for archive_timeout. That parameter isn't the only component to lag time though, it primarily impacts how many transactions you're willing to lose if commits happen on the master that aren't transferred over to the standby and the master dies. Lag time is that time, plus however long it takes the standby to keep up with processing the incoming archive files, plus how long it takes to bring it out of recovery after a primary failure. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] select issue with order v8.1
Hello, I have an application that is doing something stupid in that it is tacking on its own order clause at the end of the statement I am providing. For example, I am putting this statement in: select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text from clients_event_log limit 100 It is tacking on ORDER BY ev_id. The problem is that isn't per the syntax. Can anyone think of anything clever to get around this stupid application doing what it is doing? For example, anything I can do beside limit? I appreciate the thoughts! -- 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] select issue with order v8.1
Terry td3...@gmail.com writes: I have an application that is doing something stupid in that it is tacking on its own order clause at the end of the statement I am providing. For example, I am putting this statement in: select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text from clients_event_log limit 100 It is tacking on ORDER BY ev_id. The problem is that isn't per the syntax. Can anyone think of anything clever to get around this stupid application doing what it is doing? For example, anything I can do beside limit? Hrm, fix the application? You might be able to make a go out of something along the lines of select ev_id,... from (select * from clients_event_log limit 100) as ss; which would admit an ORDER BY on the end. BTW, in most cases it doesn't make any sense to have a LIMIT without an ORDER BY inside the subselect --- unless you really don't care which 100 rows you get. 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] select issue with order v8.1
On Wed, Feb 24, 2010 at 8:50 PM, Terry td3...@gmail.com wrote: Hello, I have an application that is doing something stupid in that it is tacking on its own order clause at the end of the statement I am providing. For example, I am putting this statement in: select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text from clients_event_log limit 100 It is tacking on ORDER BY ev_id. The problem is that isn't per the syntax. Can anyone think of anything clever to get around this stupid application doing what it is doing? For example, anything I can do beside limit? I appreciate the thoughts! You could either wrap it in a subselect or make a view. select * from (select ev_id,type,ev_time,category,error,ev_text,userid,ex_long,client_ex_long,ex_text from clients_event_log limit 100) as a and an order by tacked on the end of that is ok. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
