[GENERAL] serious issue with age(relfrozenxid)::int.
Hi all Most of my tables in postgresql database 8.3 is showing up this. Pls suggest me what should be done with this and if this is something really serious caesius=# select relname, age(relfrozenxid)::int from pg_class order by 2 desc ; relname |age -+ idx_app_param_app_name | 2147483647 triggered_update_columns| 2147483647 triggers| 2147483647 usage_privileges| 2147483647 view_column_usage | 2147483647 view_routine_usage | 2147483647 view_table_usage| 2147483647 views | 2147483647 data_type_privileges| 2147483647 element_types | 2147483647 schemata| 2147483647 sequences | 2147483647 agoda_site_pkey | 2147483647 pg_toast_351460902_index| 2147483647 pg_toast_1380431671_index | 2147483647 airfare_site_market_4_pkey | 2147483647 airfare_site_market_4_uk1 | 2147483647 rollup_upload_id_seq| 2147483647 pg_toast_1174975817_index | 2147483647 rollup_upload_pkey | 2147483647 pg_toast_1128485971_index | 2147483647 report_subscriptions_id_seq | 2147483647 pk_report_subscriptions | 2147483647 report_subscriptions_unique | 2147483647 pg_toast_1496288301_index | 2147483647 idx_work_unit_1_status_5| 2147483647 pg_toast_905799698_index| 2147483647 organization_audit_pkey | 2147483647 idx_organization_audit_row_id | 2147483647 idx_organization_audit_user_id | 2147483647 pg_toast_351460980_index| 2147483647 pg_toast_351460986_index| 2147483647 pg_toast_1607259454_index | 2147483647 idx_turing_requests_date_inserted | 2147483647 account_organization_id | 2147483647 idx_tmp_new_output_counts_work_unit_id | 2147483647 ferry_lineitem_pkey | 2147483647 pg_toast_905583507_index| 2147483647 script_override_audit_pkey | 2147483647 idx_script_override_audit_row_id| 2147483647 pg_toast_618155951_index| 2147483647 idx_script_override_audit_user_id | 2147483647 pg_toast_351461018_index| 2147483647 pg_toast_618182134_index| 2147483647 finance_lineitem_pkey | 2147483647 pg_toast_618181998_index| 2147483647
Re: [GENERAL] serious issue with age(relfrozenxid)::int.
On 7/20/2014 12:29 AM, Prabhjot Sheena wrote: Most of my tables in postgresql database 8.3 is showing up this. Pls suggest me what should be done with this and if this is something really serious why are you casting age() to an int ? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Rank and Partition
On 20 Jul 2014, at 5:38, mapl...@light42.com wrote: Assume I have a table of all schools in the US, and another with all museums, and I want to see all museums that are within some distance of each school, by school. (yes this is spatial but the distance is just a function call - no mystery there) -- select distinct on (s.name) s.name as school_name, m.name as museum_name, m.admin2, st_distance( s.geom::geography, m.geom::geography )::integer as dist, rank() over ( partition by (s.name, s.admin2) order by st_distance( s.geom::geography, m.geom::geography )) as rank from museum m, school s where s.admin2 = 'Alameda County' AND m.admin1 = 'California' AND st_dwithin( m.geom::geography, s.geom::geography, 9000 ) ORDER BY s.name, dist; — this query seems to work.. comments welcome Are you sure you want to restrict museums to s specific state? What if a school is near a state-border and there are museums of interest on the other side? What about schools or musea that have multiple locations (or a central administrative location)? If performance is an issue, neither schools nor museums tend to move around a lot and there aren’t too many of either: You could store those distances in a table linking schools and musea and update that table when convenient (a daily cron job, insert triggers, whatever suits you). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] check database integrity
Hi,
we are using 9.3 with data checksums enabled. Now I am looking for a way
to check if all database blocks are still intact. First I tried
pg_filedump. In many cases it simply ignored tampered data blocks. It is
probably not made for this task.
Then I remembered about the pageinspect extension. The following select
is a bit too verbose but it seems to do the job for everything except
fsm files.
SELECT c.oid::regclass::text as rel,
f.fork,
ser.i as blocknr,
pg.*
FROM pg_class c
CROSS JOIN (values ('main'::text), ('vm'::text)) f(fork)
CROSS JOIN pg_relation_size(c.oid::regclass, f.fork) sz(sz)
CROSS JOIN generate_series(0,(sz.sz/8192)::int-1) ser(i)
CROSS JOIN page_header(get_raw_page(c.oid::regclass::text,
f.fork,
ser.i)) pg
WHERE sz.sz0
Am I right?
The problem with the select above is that either page_header() or
get_raw_page() seems to allocate the memory for the page without freeing
it again. The process size grew to ~12.5 GB and the query returned
~1,500,000 rows. And 1.5E6 * 8 kB gives roughly 12 GB. Shared buffers is
~120 MB for this database.
I ran this query in a separate transaction. The memory was freed only
when the backend process exited.
Is there a way to work around this memory leak?
Is there a better way to do what I want? I also thought about pg_dump.
But that does not read indexes, AFAIK. Best if the solution would avoid
expensive locks. Then I could also use it in production. But currently I
need it only to verify a backup.
Thanks,
Torsten
--
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] check database integrity
On Sun, Jul 20, 2014 at 02:57:20PM +0200, Torsten Förtsch wrote: I ran this query in a separate transaction. The memory was freed only when the backend process exited. Is there a way to work around this memory leak? Why do you think it's a memory leak. You asked for the full dataset; you need to have the memory to allocate for it. At least, that's what I'd expect. expensive locks. Then I could also use it in production. But currently I need it only to verify a backup. If you need to verify a backup, why isn't pg_dump acceptable? Or is it that you are somehow trying to prove that what you have on the target (backup) machine is in fact production-ready? I guess I don't really understand what you are trying to do. A -- Andrew Sullivan a...@crankycanuck.ca -- 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] serious issue with age(relfrozenxid)::int.
On 07/20/2014 01:05 AM, John R Pierce wrote: On 7/20/2014 12:29 AM, Prabhjot Sheena wrote: Most of my tables in postgresql database 8.3 is showing up this. Pls suggest me what should be done with this and if this is something really serious why are you casting age() to an int ? My question would be why are you using age() on relfrozenxid? From here: http://www.postgresql.org/docs/8.3/static/catalog-pg-class.html relfrozenxid xid All transaction IDs before this one have been replaced with a permanent (frozen) transaction ID in this table. This is used to track whether the table needs to be vacuumed in order to prevent transaction ID wraparound or to allow pg_clog to be shrunk. Zero (InvalidTransactionId) if the relation is not a table From here: http://www.postgresql.org/docs/8.3/static/functions-datetime.html age(timestamp) intervalSubtract from current_date So it is not surprising that it is not working. -- Adrian Klaver adrian.kla...@aklaver.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] serious issue with age(relfrozenxid)::int.
Prabhjot Sheena prabhjot.she...@rivalwatch.com writes: Most of my tables in postgresql database 8.3 is showing up this. Pls suggest me what should be done with this and if this is something really serious There's no reason to think that this means anything at all. You did not bother to filter by relkind, so most likely those MAXINT results correspond to entries that have relfrozenxid = 0 because the system doesn't track relfrozenxid for them (eg, indexes). 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] check database integrity
=?ISO-8859-1?Q?Torsten_F=F6rtsch?= torsten.foert...@gmx.net writes:
Then I remembered about the pageinspect extension. The following select
is a bit too verbose but it seems to do the job for everything except
fsm files.
SELECT c.oid::regclass::text as rel,
f.fork,
ser.i as blocknr,
pg.*
FROM pg_class c
CROSS JOIN (values ('main'::text), ('vm'::text)) f(fork)
CROSS JOIN pg_relation_size(c.oid::regclass, f.fork) sz(sz)
CROSS JOIN generate_series(0,(sz.sz/8192)::int-1) ser(i)
CROSS JOIN page_header(get_raw_page(c.oid::regclass::text,
f.fork,
ser.i)) pg
WHERE sz.sz0
The problem with the select above is that either page_header() or
get_raw_page() seems to allocate the memory for the page without freeing
it again.
Probably commit 45b0f3572 will help you with that.
I ran this query in a separate transaction. The memory was freed only
when the backend process exited.
AFAIK such memory is released at end of query, even without the patch.
Are you sure you aren't looking at shared-buffer usage? Or maybe you're
on a platform where libc doesn't release freed memory back to the OS.
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] Petition: Treat #!... shebangs as comments
Hi Merlin: On Fri, Jul 18, 2014 at 9:23 PM, Merlin Moncure mmonc...@gmail.com wrote: snip, snip Anyway, this is a little bit complex, as psql many times needs arguments. true, but pretty much everything you might need can be handled via the environment and the script itself. there are plenty of good reasons do this and I can't think of any not to. Oh, don't get me wrong. I would like it and use it, I use a lot of she-bangs, even on linux which has the space-pasting behaviour, and I would not have problems with the default args. The only problems I see is more features, more places for bugs to hide, and a somehow difficult to use feature which would lead to a lot of surprises for the unexperienced. After all this years I still got puzzled for some of the she-bang problems. I doubt anyone would have backwards compatibility problems. I do not have cons, but I don't see that much pros. I would also point that in the many scripts I have running against postgres, not a single one of them would benefit from these, all need some heading, tailing, redirecting or whatevering intermixed with the DB access, although I suppose some of the more db-oriented ones could be redone to use this. But I ( personally ) would stick with doing them in shell, one more system of tricky variable interpolations and redirections would be too much for me. Regards. Francisco Olarte. -- 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] Petition: Treat #!... shebangs as comments
Hi Andrew... On Fri, Jul 18, 2014 at 9:37 PM, Andrew Pennebaker andrew.penneba...@gmail.com wrote: As a workaround, I can use this shebang hack: ... More 'this no shebang hack'. But I would prefer to use a traditional (#!/usr/bin/env psql -f) shebang. It took a few hours on irc to hack this one together. I see why. The lack of shebang puzzled me until I did man bash, search for executable and found this gem If this execution fails because the file is not in executable format, and the file is not a directory, it is assumed to be a shell script, a file containing shell commands. A subshell is spawned to execute it.. Does this works in other shells? Francisco Olarte. -- 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] Petition: Treat #!... shebangs as comments
Hi Karsten: On Fri, Jul 18, 2014 at 9:47 PM, Karsten Hilbert karsten.hilb...@gmx.net wrote: Nice solution but that won't work on Windows ... If psql -f kk.psql does, it works enough. ./kk.psql would not notmally work on windows. It's been 12 years since I worked on it, but IIRC although windows ( the OS ) recognized / as a path separator CMD did not, so even if you set psql as a handler for .psql files it would not work ( and, IIRC again, the customary way to exec that kind of files would be 'kk', i.e. 'jj' not '.\jj.md' for a CMD file as windows used to consider the CWD of the current drive before the PATH ). And if you are using windows in POSIX mode with a bash shell it would probably work. Note the OP was not asking for a windows+unix shell script, but for a psql + unix shell. Francisco Olarte. -- 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] Petition: Treat #!... shebangs as comments
Hi John: On Fri, Jul 18, 2014 at 10:41 PM, John McKown john.archie.mck...@gmail.com wrote: FWIW - I like #! also. Even though it may cause the Windows users to want something equivalent. Assuming there are any Windows people who really use a command prompt. I do not now, given the current trend of changing thing for the sake of it leading to getting rid of lots of very useful features. But back in the NT 3.51 era I was a heavy windows command prompt user. And they have a feature which I consider superior to the irregular she bang support in the unix world, the file type association which let you associate an extension with an interpreter and which properly parsed multiple arguments in the interpreter definition. I used it for perl and awk, and even had a wrapper which let me execute a C file by invoking it by name ( compiling it along the way ). You would still have to overcome the problems with the weird quoting rules of every command, but that is another tale. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Copying a database.
I send a nightly dump of my production database to a development server. A script drops the existing development database and replaces it with the current production copy. Each dev uses her own copy of the database. Is there a way to copy the current development database to a differently named db on the same machine, including the data, without using the sql dump?
Re: [GENERAL] Copying a database.
On Jul 20, 2014, at 11:09 AM, maillis...@gmail.com wrote: I send a nightly dump of my production database to a development server. A script drops the existing development database and replaces it with the current production copy. Each dev uses her own copy of the database. Is there a way to copy the current development database to a differently named db on the same machine, including the data, without using the sql dump? Look at CREATE DATABASE developer_db WITH TEMPLATE production_copy; createdb with the --template option is a convenient way to do that from the commandline. Cheers, Steve -- 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] Copying a database.
On 07/20/2014 11:28 AM, Steve Atkins wrote: On Jul 20, 2014, at 11:09 AM, maillis...@gmail.com wrote: I send a nightly dump of my production database to a development server. A script drops the existing development database and replaces it with the current production copy. Each dev uses her own copy of the database. Is there a way to copy the current development database to a differently named db on the same machine, including the data, without using the sql dump? Look at CREATE DATABASE developer_db WITH TEMPLATE production_copy; Just be aware of this caveat: http://www.postgresql.org/docs/9.3/interactive/sql-createdatabase.html Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose COPY DATABASE facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes. See Section 21.3 for more information. createdb with the --template option is a convenient way to do that from the commandline. Cheers, Steve -- Adrian Klaver adrian.kla...@aklaver.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] check database integrity
On 20/07/14 17:35, Tom Lane wrote:
=?ISO-8859-1?Q?Torsten_F=F6rtsch?= torsten.foert...@gmx.net writes:
Then I remembered about the pageinspect extension. The following select
is a bit too verbose but it seems to do the job for everything except
fsm files.
SELECT c.oid::regclass::text as rel,
f.fork,
ser.i as blocknr,
pg.*
FROM pg_class c
CROSS JOIN (values ('main'::text), ('vm'::text)) f(fork)
CROSS JOIN pg_relation_size(c.oid::regclass, f.fork) sz(sz)
CROSS JOIN generate_series(0,(sz.sz/8192)::int-1) ser(i)
CROSS JOIN page_header(get_raw_page(c.oid::regclass::text,
f.fork,
ser.i)) pg
WHERE sz.sz0
The problem with the select above is that either page_header() or
get_raw_page() seems to allocate the memory for the page without freeing
it again.
Probably commit 45b0f3572 will help you with that.
Thanks, Tom. At least the patch description helped. I moved the
page_header() call to output column list and now it works perfectly.
I'll try the patch next weekend.
I ran this query in a separate transaction. The memory was freed only
when the backend process exited.
AFAIK such memory is released at end of query, even without the patch.
Are you sure you aren't looking at shared-buffer usage? Or maybe you're
on a platform where libc doesn't release freed memory back to the OS.
You are right here. When I wrote the email I restored the behaviour from
my faulty memory. Today I tried it again and the memory is indeed freed
at the end of the query.
Another question, just out of curiosity, for vm and main forks I use
pg_relation_size to figure out the highest page number. That does not
work for fsm. I have at least one fsm file that it 24 kb. Fetching page
0 works, page 1 and above gives an error:
db=# select page_header(get_raw_page(2836::oid::regclass::text, 'fsm', 0));
page_header
--
(114/23485F78,19084,0,24,8192,8192,8192,4,0)
(1 row)
db=# select page_header(get_raw_page(2836::oid::regclass::text, 'fsm',
1));
ERROR: block number 1 is out of range for relation pg_toast_1255
db=# select pg_relation_size(2836::oid::regclass, 'fsm');
pg_relation_size
--
24576
For other relations it works:
db=# select page_header(get_raw_page(60966::oid::regclass::text, 'fsm',
i)) from generate_series(0,2) i;
page_header
---
(11F/76884610,-4342,0,24,8192,8192,8192,4,0)
(11F/768825C0,22465,0,24,8192,8192,8192,4,0)
(11F/83E9EC38,-29015,0,24,8192,8192,8192,4,0)
(3 rows)
db=# select pg_relation_size(60966::oid::regclass, 'fsm');
pg_relation_size
--
24576
Is there a way to figure out the highest page number for fsm forks? Is
there perhaps a common way that works for all forks?
Thanks,
Torsten
--
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] check database integrity
On 20/07/14 16:02, Andrew Sullivan wrote: Then I could also use it in production. But currently I need it only to verify a backup. If you need to verify a backup, why isn't pg_dump acceptable? Or is it that you are somehow trying to prove that what you have on the target (backup) machine is in fact production-ready? I guess I don't really understand what you are trying to do. Sorry, for kind-of misusing the word backup. What I am doing is this. I took a base backup and replayed a few xlogs. This is what I meant with backup. What I want to verify is whether all pages in all files match their checksums. So, I have to make postgres read all pages at least once. Pg_dump does this for normal tables and toast. But it does not read index relations as far as I know. A select count(*) from all tables would also do the job, again without indexes. The sentence about the backup was only to point out that I don't really care if the query locks the database for concurrent transactions. But better if it would not acquire an exclusive lock on all tables. Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
