Re: [GENERAL] Russian Language Spam
On 08/07/11 4:06 PM, Noel Cosgrave wrote: Can someone please cancel the taxation/legal spam? None of the Russian-language posts I've read here recently have anything whatsoever to do with Postgresql. I haven't seen any russian here? -- john r pierceN 37, W 122 santa cruz ca mid-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] postgres table have a large number of relpages and occupied a big memory size
Hi , I have version of PostgreSQL as given below:--- version - PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) And it does't have any n_dead_tup columns in pg_stat_all_tables like this: mydb=# \d pg_stat_all_tables View pg_catalog.pg_stat_all_tables Column | Type | Modifiers ---++--- relid | oid| schemaname| name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | In what situations table creates a new row instead of using deleted marked tuples that's why size of table increases abnormally. On Sat, Aug 6, 2011 at 12:12 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Fri, Aug 5, 2011 at 5:26 AM, Tomas Vondra t...@fuzzy.cz wrote: On 5 Srpen 2011, 10:52, Vivekkumar Pandey wrote: Also suggest the Query that can view the dead tuples in the table. You can't see the dead tuples with a query - that's why they're called dead. you can see an estimate of how many dead tuples are looking at the n_dead_tup in pg_stat_all_tables -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Thanks VIVEK KUMAR PANDEY -- 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] Granting Privileges in Postgres
On Mon, 2011-08-08 at 10:28 +0530, Adarsh Sharma wrote:
Dear all,
Today I researched on giving privileges in Postgres databases. I have 4
databases and near about 150 tables, 50-60 sequences and also some views
in it.
I want to give privileges to a new user in all these objects. I created
a function for that but don't know how to give privileges on all objects
all at once.
**Function for granting all privileges on all tables in
postgres database**
Step 1 : Create a new user with password
create user abc with password 'as123';
Step 2 :
create function grant_all(a text) returns void as $$
declare
name text;
user_name alias for $1;
begin
for name in select table_name from information_schema.tables where
table_schema = 'public' loop
execute 'grant all on table ' || name || ' to ' || user_name ;
end loop;
end;
$$ language plpgsql;
Step 3 :
select grant_all('abc');
Step 4 :
Finish
This will grant on tables only but Do I need to manually issue grant
commands on all objects.
I want to issue it all at once.
You just need to add the other GRANT ALL ON object type object name
to user name in your function.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.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] Granting Privileges in Postgres
Guillaume Lelarge wrote:
On Mon, 2011-08-08 at 10:28 +0530, Adarsh Sharma wrote:
Dear all,
Today I researched on giving privileges in Postgres databases. I have 4
databases and near about 150 tables, 50-60 sequences and also some views
in it.
I want to give privileges to a new user in all these objects. I created
a function for that but don't know how to give privileges on all objects
all at once.
**Function for granting all privileges on all tables in
postgres database**
Step 1 : Create a new user with password
create user abc with password 'as123';
Step 2 :
create function grant_all(a text) returns void as $$
declare
name text;
user_name alias for $1;
begin
for name in select table_name from information_schema.tables where
table_schema = 'public' loop
execute 'grant all on table ' || name || ' to ' || user_name ;
end loop;
end;
$$ language plpgsql;
Step 3 :
select grant_all('abc');
Step 4 :
Finish
This will grant on tables only but Do I need to manually issue grant
commands on all objects.
I want to issue it all at once.
You just need to add the other GRANT ALL ON object type object name
to user name in your function.
But how it picks all view sequence names one by one, I iterate in my
loop each table name .
Manually the command is :
grant all on sequence_name to user_name;
Thanks
Re: [GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
Just a thought, why don't you deploy your setup in your home computer, and make it accesible from the inet via your home router? Just make a software raid, get a nice UPS unit, try to harden your OS a little bit, and run your apps there. It could prove a nice free alternative until anything that looks like a free HAAS/PAAS shows up. I know it sounds old fashioned, now in the days of the cloud frenzy, but there must be some value in doing all this by yourself, even if it goes against the trends. Στις Sunday 07 August 2011 17:50:44 ο/η Fernando Pianegiani έγραψε: On Sun, Aug 7, 2011 at 4:22 PM, David Johnston pol...@yahoo.com wrote: this is very difficult, but it is exactly what I am doing in environments different from this one. Even if this risks to be considered (not so positively) as a request of charity... :-) At that point, unless you have confidentiality requirements, why not just tell everyone what it is you are working on and see if anyone responds favorably? It woul normally be deemed off-topic but at this point one more non-Postgresql post isn't going to make a big difference on this thread. Fundraising for a cause is quite a bit different than asking for a personal gift and it sound like your request falls into the former category. Dear David, thank you for your post. I have not posted exactly a non-PostgreSQL post, in fact I asked for information about possible services of free hosting platforms with PostgreSQL installed. I repeat that I didn't ask for a hosting platform but for information about possible inherent free services. The item of research focuses on the remote detection of events of health hazard, like in particular the cardiac atrial fibrillation, by wireless sensors installed on the body of the patient and a phone that forwards the data towards the hosting. If somebody can be interested I pray him to ask me for more information writing just to my email address. Thanks a lot! David J. -- Achilleas Mantzios -- 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] Granting Privileges in Postgres
On Mon, 2011-08-08 at 11:42 +0530, Adarsh Sharma wrote:
Guillaume Lelarge wrote:
On Mon, 2011-08-08 at 10:28 +0530, Adarsh Sharma wrote:
Dear all,
Today I researched on giving privileges in Postgres databases. I have 4
databases and near about 150 tables, 50-60 sequences and also some views
in it.
I want to give privileges to a new user in all these objects. I created
a function for that but don't know how to give privileges on all objects
all at once.
**Function for granting all privileges on all tables in
postgres database**
Step 1 : Create a new user with password
create user abc with password 'as123';
Step 2 :
create function grant_all(a text) returns void as $$
declare
name text;
user_name alias for $1;
begin
for name in select table_name from information_schema.tables where
table_schema = 'public' loop
execute 'grant all on table ' || name || ' to ' || user_name ;
end loop;
end;
$$ language plpgsql;
Step 3 :
select grant_all('abc');
Step 4 :
Finish
This will grant on tables only but Do I need to manually issue grant
commands on all objects.
I want to issue it all at once.
You just need to add the other GRANT ALL ON object type object name
to user name in your function.
But how it picks all view sequence names one by one, I iterate in my
loop each table name .
Manually the command is :
grant all on sequence_name to user_name;
For sequences, you need to look at information_schema.sequences. For
others, well, it depends on what objects you'll have in your database.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.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] Query with rightmost function does not use index
Here are the EXPLAIN ANALYZE outputs:
explain analyze select * from filter_item where filter_hash = MD5('');
QUERY PLAN
---
Seq Scan on filter_item (cost=0.00..424644.96 rows=86108 width=49) (actual
time=8177.807..12421.921 rows=77 loops=1)
Filter: ((filter_hash)::text = 'd41d8cd98f00b204e9800998ecf8427e'::text)
Total runtime: 12421.959 ms
(3 rows)
explain analyze select * from filter_item where filter_hash =
'd41d8cd98f00b204e9800998ecf8427e'
QUERY PLAN
--
Bitmap Heap Scan on filter_item (cost=77.92..6609.02 rows=3534 width=49)
(actual time=0.055..0.100 rows=77 loops=1)
Recheck Cond: (filter_hash = 'd41d8cd98f00b204e9800998ecf8427e'::bpchar)
- Bitmap Index Scan on filter_item__filter_hash (cost=0.00..77.04
rows=3534 width=0) (actual time=0.049..0.049 rows=77 loops=1)
Index Cond: (filter_hash = 'd41d8cd98f00b204e9800998ecf8427e'::bpchar)
Total runtime: 0.130 ms
(5 rows)
[GENERAL] Backup Restore a database in PostgreSQL
Hi All, I am also a newbie here! I need to backup a database and restore it into the target machine where the database may already present or might not. If it exists, I want the restore command to overwrite, otherwise, just create a new one. I tried using the commands: (1) BACKUP: pg_dump -h localhost -U username db dump_file.out; RESTORE: pg_dump -h localhost -U username db dump_file.out; (2) BACKUP: pg_dump -h localhost -U username -Ft db dump_file.tar; RESTORE: pg_restore -h localhost -U username -d db dump_file.tar; Backup worked perfect in the above 2 scenarios whereas restore didn't yield the exact results. For testing it, I took the back-up and intentionally deleted few records in a table. I then restored the database in the same machine where the database exists. I expected the deleted records to come back as I was restoring the one which has the complete data. It didn't yield proper results. And I wonder why.. I believe I might be doing something marginally wrong. I would appreciate if any geek over here to guide me the restore command properly. Thanks in advance, Siva. ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ---
Re: [GENERAL] Backup Restore a database in PostgreSQL
On 08/08/11 1:01 AM, Siva Palanisamy wrote: Hi All, I am also a newbie here! I need to backup a database and restore it into the target machine where the database may already present or might not. If it exists, I want the restore command to overwrite, otherwise, just create a new one. I tried using the commands: (1) BACKUP: pg_dump -h localhost -U username db dump_file.out; RESTORE: pg_dump -h localhost -U username db dump_file.out; (2) BACKUP: pg_dump -h localhost -U username -Ft db dump_file.tar; RESTORE: pg_restore -h localhost -U username -d db dump_file.tar; Backup worked perfect in the above 2 scenarios whereas restore didn't yield the exact results. For testing it, I took the back-up and intentionally deleted few records in a table. I then restored the database in the same machine where the database exists. I expected the deleted records to come back as I was restoring the one which has the complete data. It didn't yield proper results. And I wonder why.. I believe I might be doing something marginally wrong. I would appreciate if any geek over here to guide me the restore command properly. specify -c on the pg_restore, and it will drop the database objects and recreate them pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname pg_restore -c -h localhost -U user -d dbname dumpfile.pg -- john r pierceN 37, W 122 santa cruz ca mid-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] Backup Restore a database in PostgreSQL
Hi John, Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler Command was: DROP FUNCTION public.plpgsql_call_handler(); pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language Command was: DROP PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema public already exists Command was: CREATE SCHEMA public; pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: COMMENT ON SCHEMA public IS 'Standard public schema'; pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to create procedural language Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAG... pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges were granted pg_restore: WARNING: no privileges were granted WARNING: errors ignored on restore: 7 Thanks and Regards, Siva. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Monday, August 08, 2011 1:45 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Backup Restore a database in PostgreSQL On 08/08/11 1:01 AM, Siva Palanisamy wrote: Hi All, I am also a newbie here! I need to backup a database and restore it into the target machine where the database may already present or might not. If it exists, I want the restore command to overwrite, otherwise, just create a new one. I tried using the commands: (1) BACKUP: pg_dump -h localhost -U username db dump_file.out; RESTORE: pg_dump -h localhost -U username db dump_file.out; (2) BACKUP: pg_dump -h localhost -U username -Ft db dump_file.tar; RESTORE: pg_restore -h localhost -U username -d db dump_file.tar; Backup worked perfect in the above 2 scenarios whereas restore didn't yield the exact results. For testing it, I took the back-up and intentionally deleted few records in a table. I then restored the database in the same machine where the database exists. I expected the deleted records to come back as I was restoring the one which has the complete data. It didn't yield proper results. And I wonder why.. I believe I might be doing something marginally wrong. I would appreciate if any geek over here to guide me the restore command properly. specify -c on the pg_restore, and it will drop the database objects and recreate them pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname pg_restore -c -h localhost -U user -d dbname dumpfile.pg -- john r pierceN 37, W 122 santa cruz ca mid-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 ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check
Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size
On 8 Srpen 2011, 8:02, Vivekkumar Pandey wrote: Hi , I have version of PostgreSQL as given below:--- version - PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) And it does't have any n_dead_tup columns in pg_stat_all_tables like this: That's because this column was added in 8.3. BTW you're using a way too old version. It's not just that 8.1 is unsupported, but the last minor subversion in this branch is 8.1.23. There were numerous bugfixes since 8.1.2, so you should upgrade at least to this version (and plan to upgrade to something more fresh). In what situations table creates a new row instead of using deleted marked tuples that's why size of table increases abnormally. Whenever a row is modified - that means insert, update or delete. That's how PostgreSQL MVCC works. This wasted space needs to be reclaimed, which is exactly what (auto)vacuum does. PS: With HOT this is not exactly true, but HOT is not available in 8.1 anyway. Another reason to upgrade to a more recent version. Tomas -- 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] Filling null values
Hmm, no-one seemed to mention the obvious - a pl/pgsql function, either triggered or run manually depending if you want to update on insert/update or on demand. On 7 August 2011 16:05, Sim Zacks s...@compulab.co.il wrote: ** On 08/05/2011 07:32 PM, jeffrey wrote: I have a table that looks like this: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles2/4/2005 938 pre 124 NULLNULL 266 pre 124 los angeles7/4/2006 777 post I'd like to write a query so that I get the following result: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932post 124 los angeles2/4/2005 938 pre 124 los angeles2/4/2005 266 pre 124 los angeles7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? Thanks, Jeff
Re: [GENERAL] Backup Restore a database in PostgreSQL
Hi Siva Not sure if it would help, but try passing -O in your pg_restore command. Amitabh On Mon, Aug 8, 2011 at 2:04 PM, Siva Palanisamy siv...@hcl.com wrote: Hi John, Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler Command was: DROP FUNCTION public.plpgsql_call_handler(); pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language Command was: DROP PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema public already exists Command was: CREATE SCHEMA public; pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: COMMENT ON SCHEMA public IS 'Standard public schema'; pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to create procedural language Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAG... pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges were granted pg_restore: WARNING: no privileges were granted WARNING: errors ignored on restore: 7 Thanks and Regards, Siva. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Monday, August 08, 2011 1:45 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Backup Restore a database in PostgreSQL On 08/08/11 1:01 AM, Siva Palanisamy wrote: Hi All, I am also a newbie here! I need to backup a database and restore it into the target machine where the database may already present or might not. If it exists, I want the restore command to overwrite, otherwise, just create a new one. I tried using the commands: (1) BACKUP: pg_dump -h localhost -U username db dump_file.out; RESTORE: pg_dump -h localhost -U username db dump_file.out; (2) BACKUP: pg_dump -h localhost -U username -Ft db dump_file.tar; RESTORE: pg_restore -h localhost -U username -d db dump_file.tar; Backup worked perfect in the above 2 scenarios whereas restore didn't yield the exact results. For testing it, I took the back-up and intentionally deleted few records in a table. I then restored the database in the same machine where the database exists. I expected the deleted records to come back as I was restoring the one which has the complete data. It didn't yield proper results. And I wonder why.. I believe I might be doing something marginally wrong. I would appreciate if any geek over here to guide me the restore command properly. specify -c on the pg_restore, and it will drop the database objects and recreate them pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname pg_restore -c -h localhost -U user -d dbname dumpfile.pg -- john r pierceN 37, W 122 santa cruz ca mid-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 ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
Re: [GENERAL] Backup Restore a database in PostgreSQL
Hi Amitabh, I just added the -O option in the middle as detailed below. pg_restore -c -O -h localhost -U username -d db dumpfile.pg; However, I get the same list of errors as below. The weird thing is, it appears to be working fine. I could not able to comprehend the error list! I don't have any clue about it! For your information, I don't think am running using the superuser account! But just seem like a normal user account.. Could this lead to few issues on privileges? Any help would be greatly appreciated. Thanks and Regards, Siva. From: Amitabh Kant [mailto:amitabhk...@gmail.com] Sent: Monday, August 08, 2011 2:42 PM To: Siva Palanisamy Cc: John R Pierce; pgsql-general@postgresql.org Subject: Re: [GENERAL] Backup Restore a database in PostgreSQL Hi Siva Not sure if it would help, but try passing -O in your pg_restore command. Amitabh On Mon, Aug 8, 2011 at 2:04 PM, Siva Palanisamy siv...@hcl.commailto:siv...@hcl.com wrote: Hi John, Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler Command was: DROP FUNCTION public.plpgsql_call_handler(); pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language Command was: DROP PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema public already exists Command was: CREATE SCHEMA public; pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: COMMENT ON SCHEMA public IS 'Standard public schema'; pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to create procedural language Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAG... pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges were granted pg_restore: WARNING: no privileges were granted WARNING: errors ignored on restore: 7 Thanks and Regards, Siva. -Original Message- From: pgsql-general-ow...@postgresql.orgmailto:pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.orgmailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Monday, August 08, 2011 1:45 PM To: pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org Subject: Re: [GENERAL] Backup Restore a database in PostgreSQL On 08/08/11 1:01 AM, Siva Palanisamy wrote: Hi All, I am also a newbie here! I need to backup a database and restore it into the target machine where the database may already present or might not. If it exists, I want the restore command to overwrite, otherwise, just create a new one. I tried using the commands: (1) BACKUP: pg_dump -h localhost -U username db dump_file.out; RESTORE: pg_dump -h localhost -U username db dump_file.out; (2) BACKUP: pg_dump -h localhost -U username -Ft db dump_file.tar; RESTORE: pg_restore -h localhost -U username -d db dump_file.tar; Backup worked perfect in the above 2 scenarios whereas restore didn't yield the exact results. For testing it, I took the back-up and intentionally deleted few records in a table. I then restored the database in the same machine where the database exists. I expected the deleted records to come back as I was restoring the one which has the complete data. It didn't yield proper results. And I wonder why.. I believe I might be doing something marginally wrong. I would appreciate if any geek over here to guide me the restore command properly. specify -c on the pg_restore, and it will drop the database objects and recreate them pg_dump -Fc -h localhost -U user -f dumpfile.pghttp://dumpfile.pg dbname pg_restore -c -h localhost -U user -d dbname dumpfile.pghttp://dumpfile.pg --
Re: [GENERAL] Filling null values
hi, try this. If your table name is mytable: select a.homeid , a.city , coalesce(a.date, (select b.date from mytable b where b.homeid=a.homeid and b.date is not null order by b.prepost=a.prepost desc limit 1) ) as date , a.measurement , a.prepost from mytable a Thomas Am 05.08.2011 18:32, schrieb jeffrey: I have a table that looks like this: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles2/4/2005 938 pre 124 NULLNULL 266 pre 124 los angeles7/4/2006 777 post I'd like to write a query so that I get the following result: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932post 124 los angeles2/4/2005 938 pre 124 los angeles2/4/2005 266 pre 124 los angeles7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? Thanks, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get to know the current user account is superuser or not?
Hi All, I was provided with a user account to access postgresql. I want to process some high-level operations that might need a superuser/admin privileged access. How to get to know the user account I was provided having what kind of privileges? It looks like I don't have super user account! And I want to confirm this at the earliest. Thanks and Regards, Siva. ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ---
Re: [GENERAL] How to get to know the current user account is superuser or not?
On Mon, Aug 8, 2011 at 10:36 AM, Siva Palanisamy siv...@hcl.com wrote: I was provided with a user account to access postgresql. I want to process some high-level operations that might need a superuser/admin privileged access. How to get to know the user account I was provided having what kind of privileges? It looks like I don’t have super user account! And I want to confirm this at the earliest. The best thing is to spend a few minutes reading the section on Functions, which details more than 2000 functions designed to help you with this question and many others. http://www.postgresql.org/docs/9.1/static/functions.html -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Backup Restore a database in PostgreSQL
Siva -- Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler Command was: DROP FUNCTION public.plpgsql_call_handler(); Clue #1 -- you are not running as the correct user for the restore into this database. pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language Command was: DROP PROCEDURAL LANGUAGE plpgsql; Clue #2! So you need to change to being the postgres user that created the database, which presumably has the proper permissions to drop these various entities. snipped redundant error messages? HTH, Greg Williamson -- 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 to know the current user account is superuser or not?
Στις Monday 08 August 2011 12:36:44 ο/η Siva Palanisamy έγραψε: Hi All, I was provided with a user account to access postgresql. I want to process some high-level operations that might need a superuser/admin privileged access. How to get to know the user account I was provided having what kind of privileges? It looks like I don't have super user account! And I want to confirm this at the earliest. SELECT * from pg_user; Thanks and Regards, Siva. ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. --- -- Achilleas Mantzios -- 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 to know the current user account is superuser or not?
Hi Achilleas, Thanks a lot for your reply. I got what I wanted now! I knew this question was very basic but I am completely new to PostgreSQL..!! Thanks and Regards, Siva. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Achilleas Mantzios Sent: Monday, August 08, 2011 4:01 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to get to know the current user account is superuser or not? Στις Monday 08 August 2011 12:36:44 ο/η Siva Palanisamy έγραψε: Hi All, I was provided with a user account to access postgresql. I want to process some high-level operations that might need a superuser/admin privileged access. How to get to know the user account I was provided having what kind of privileges? It looks like I don't have super user account! And I want to confirm this at the earliest. SELECT * from pg_user; Thanks and Regards, Siva. ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. --- -- Achilleas Mantzios -- 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] How to get to know the current user account is superuser or not?
Hi All, I was provided with a user account to access postgresql. I want to process some high-level operations that might need a superuser/admin privileged access. How to get to know the user account I was provided having what kind of privileges? It looks like I don't have super user account! And I want to confirm this at the earliest. Thanks and Regards, Siva. Ask the person who setup your user.
Re: [GENERAL] Query with rightmost function does not use index
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of - -
Sent: Monday, August 08, 2011 3:40 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query with rightmost function does not use index
Here are the EXPLAIN ANALYZE outputs:
explain analyze select * from filter_item where filter_hash = MD5('');
QUERY PLAN
---
Seq Scan on filter_item (cost=0.00..424644.96 rows=86108 width=49) (ac
tual time=8177.807..12421.921 rows=77 loops=1)
Filter: ((filter_hash)::text = 'd41d8cd98f00b204e9800998ecf8427e'::text)
Total runtime: 12421.959 ms
(3 rows)
explain analyze select * from filter_item where filter_hash =
'd41d8cd98f00b204e9800998ecf8427e'
QUERY PLAN
--
Bitmap Heap Scan on filter_item (cost=77.92..6609.02 rows=3534 width=49)
(actual time=0.055..0.100 rows=77 loops=1)
Recheck Cond: (filter_hash = 'd41d8cd98f00b204e9800998ecf8427e'::bpchar)
- Bitmap Index Scan on filter_item__filter_hash (cost=0.00..77.04
rows=3534 width=0) (actual time=0.049..0.049 rows=77 loops=1)
Index Cond: (filter_hash =
'd41d8cd98f00b204e9800998ecf8427e'::bpchar)
Total runtime: 0.130 ms
(5 rows)
The filter_hash index uses a character(n) data type - the ::bpchar. The
second query is of unknown type and thus is converted to character and
then used in the index. The first query use a function that outputs a
text. Since the output type is known the left-side of the equals is
casted to that known type. Since the index is one the character version
of the filter_hash but the comparison requires a text version the index
cannot be used. You would need to manually cast the result of the md5
function call to character in order to get the index usage; or convert the
filter_hash column to text, the latter option probably being preferred.
It is not a bug, in cases of uncertainty the types of the value and the
indexed field must be the same, but it could possibly be more user-friendly.
I'll leave it to other to comment on whether this is different in more
recent versions. Text-character are binary compatible and so it is not be
unreasonable to assume, like you did, that indexes of one should be usable
by the other.
David J.
Re: [GENERAL] Backup Restore a database in PostgreSQL
Siva Palanisamy wrote: However, I get the same list of errors as below. The weird thing is, it appears to be working fine. I could not able to comprehend the error list! I don't have any clue about it! For your information, I don't think am running using the superuser account! But just seem like a normal user account.. Could this lead to few issues on privileges? The messages you get are to be expected if you try to restore a database without the necessary permissions. It worked for the objects that the user owns. If you want to backup just a few tables or a certain schema in the database, use the -t or -n flags. If you want to backup the whole database, make sure you have appropriate permissions when you restore. 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] Russian Language Spam
they come from b*yo(@n!.com so purchasing one of their domains and you'll get Russian Brides for sale preconfigured for that site! August is black August month for Russia..all bad things that could happen have happened to Russia in the month of August. http://www.theworld.org/2011/08/why-russians-fear-august/ Validimir is up to his old tricks. Martin Gainty __ Trust ...but verify..Ronald Reagan Date: Sun, 7 Aug 2011 23:00:53 -0700 From: pie...@hogranch.com To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Russian Language Spam On 08/07/11 4:06 PM, Noel Cosgrave wrote: Can someone please cancel the taxation/legal spam? None of the Russian-language posts I've read here recently have anything whatsoever to do with Postgresql. I haven't seen any russian here? -- john r pierceN 37, W 122 santa cruz ca mid-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] Query with rightmost function does not use index
Hi,
On Monday, August 08, 2011 15:40:20 - - wrote:
explain analyze select * from filter_item where filter_hash = MD5('');
QUERY PLAN
--- Seq Scan on filter_item
(cost=0.00..424644.96 rows=86108 width=49) (actual time=8177.807..12421.921
rows=77 loops=1) Filter: ((filter_hash)::text =
'd41d8cd98f00b204e9800998ecf8427e'::text) Total runtime: 12421.959 ms
(3 rows)
explain analyze select * from filter_item where filter_hash =
'd41d8cd98f00b204e9800998ecf8427e'
QUERY PLAN
-- Bitmap Heap Scan
on filter_item (cost=77.92..6609.02 rows=3534 width=49) (actual
time=0.055..0.100 rows=77 loops=1) Recheck Cond: (filter_hash =
'd41d8cd98f00b204e9800998ecf8427e'::bpchar) - Bitmap Index Scan on
filter_item__filter_hash (cost=0.00..77.04 rows=3534 width=0) (actual
time=0.049..0.049 rows=77 loops=1) Index Cond: (filter_hash =
'd41d8cd98f00b204e9800998ecf8427e'::bpchar) Total runtime: 0.130 ms
(5 rows)
The problem is that your filter_hash columns seems to be of type char(n). Thats
not directly compatible with text (which is the type returned by the md5
function).
So either change the column type or cast the return type of md5 to char(n).
I do have to admit that this is somewhat strange.
Greetings,
Andres
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with planner
Hi, we have 8.3.11 installation on client site, with table, which looks like this: $ \d objects Table public.objects Column | Type | Modifiers -+--+--- ... state | text | ... ending_tsz | timestamp with time zone | default (now() + '4 mons'::interval) ... Indexes: objects_stat_user_id_creation_tsz btree (state, user_id, creation_tsz) objects_ending_tsz_active btree (ending_tsz) WHERE state = 'active'::text objects_ending_tsz_idx btree (ending_tsz) ... and we have a query: select count(*) from objects where state='active'::text and ending_tsz = ( select now() - '1 day'::interval ); Normally this query has been getting plan, using objects_ending_tsz_active, which is sane and fast. But today, without any sensible reason, it switched to: QUERY PLAN - Aggregate (cost=6719810.62..6719810.63 rows=1 width=0) InitPlan - Result (cost=0.00..0.01 rows=1 width=0) - Bitmap Heap Scan on objects (cost=1289719.58..6711422.56 rows=3355219 width=0) Recheck Cond: ((state = 'active'::text) AND (ending_tsz = $0)) - BitmapAnd (cost=1289719.58..1289719.58 rows=3355219 width=0) - Bitmap Index Scan on objects_stat_user_id_creation_tsz (cost=0.00..334318.95 rows=10065657 width=0) Index Cond: (state = 'active'::text) - Bitmap Index Scan on objects_ending_tsz_idx (cost=0.00..953722.77 rows=24986738 width=0) Index Cond: (ending_tsz = $0) (10 rows) running analyze objects 2 times in a row fixed the issue, but hour later - the problem came back. what can be wrong? Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JDBC driver throwing error while connecting to the slave server for partitioned table
We are facing below issue after creating slony replication of partitioning table in edb server 8.3. This issue persist only on slave one not the primary one. Below logs we found in tomcats. Error in postgresql driver initialization: com.edb.util.PSQLException: The connection attempt failed. at com.edb.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:176) at com.edb.core.ConnectionFactory.openConnection(ConnectionFactory.java:64) at com.edb.jdbc2.AbstractJdbc2Connection.init(AbstractJdbc2Connection.java:153) at com.edb.jdbc3.AbstractJdbc3Connection.init(AbstractJdbc3Connection.java:30) at com.edb.jdbc3.Jdbc3Connection.init(Jdbc3Connection.java:24) at com.edb.Driver.makeConnection(Driver.java:385) at com.edb.Driver.connect(Driver.java:260) at java.sql.DriverManager.getConnection(DriverManager.java:582) Pls help 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] Problem with planner
hubert depesz lubaczewski dep...@depesz.com writes: and we have a query: select count(*) from objects where state='active'::text and ending_tsz = ( select now() - '1 day'::interval ); Try getting rid of the sub-select. There might have been a reason to do it like that ten years ago, but these days it's a guaranteed pessimization. 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] Problem with planner
On Mon, Aug 08, 2011 at 12:51:40PM -0400, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: and we have a query: select count(*) from objects where state='active'::text and ending_tsz = ( select now() - '1 day'::interval ); Try getting rid of the sub-select. There might have been a reason to do it like that ten years ago, but these days it's a guaranteed pessimization. looks like even worse plan: QUERY PLAN --- Aggregate (cost=9168023.89..9168023.90 rows=1 width=0) - Bitmap Heap Scan on objects (cost=336429.41..9147670.19 rows=8141478 width=0) Recheck Cond: (state = 'active'::text) Filter: (ending_tsz = (now() - '1 day'::interval)) - Bitmap Index Scan on objects_stat_user_id_creation_tsz (cost=0.00..334394.04 rows=10064868 width=0) Index Cond: (state = 'active'::text) (6 rows) Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] table / query as a prameter for PL/pgSQL function
2011/8/7 Ondrej Ivanič ondrej.iva...@gmail.com: Hi, It is possible to pass query result (or cursor?) as function parameter? I need a function which emits zero or more rows per input row (map function from mapreduce paradigm). Function returns record (or array): (value1, value2, value3) I've tried the following: 1) create or replace function test (r record) returns setof record as $$ ... Doesn't work: PL/pgSQL functions cannot accept type record 2) pass query as text parameter and open no scroll cursor inside the function It works but it's ugly. 3) hardcode the query inside function Similar to (2) and looks better but I need several functions with different queries inside: ... for r in (query) loop ... end loop; ... 4) use function in select clause: select my_map_func(col1, col2, col3, col4) from ... -- the rest of the query In this case I wasn't able figure out how to access record members returned by the function: select ?, ?, ?, count(*) from ( select my_map_func(col1, col2, col3, col4) as map_func_result from ... ) as map group by 1, 2, 3 The '?' should be something like map.map_func_result.value1 (both map.value1 and map_func_result.value1 doesn't not work). If function returns array then I can access value1 by using map_func_result[1] Is there a better way how to solve this? I'm kind of satisfied with 4 (maybe 3) but it is little bit cumbersome You have a few of different methods for passing sets between functions. 1) refcursor as David noted. reasonably fast. however, I find the 'FETCH' mechanic a little inflexible. 2) stage data to TABLE/TEMP TABLE; extremely flexible, but can be a headache because a non temp table can get thrashed pretty hard a and a 'TEMP' can cause severe function plan invalidation issues if you're not careful 3) arrays of composites -- the most flexible and very fast for *small* amounts of records (say less than 10,000): #3 is my favorite method unless the data being passed is very large. Here is an example of it in use: CREATE TYPE foo_t as (a int, b text); CREATE FUNCTION get_foos() RETURNS SETOF foo_t AS $$ BEGIN RETURN QUERY SELECT 1, 'abc' UNION ALL SELECT 2, 'def'; END; $$ LANGUAGE PLPGSQL; CREATE FUNCTION do_foos(_foos foo_t[]) returns VOID AS $$ DECLARE f foo_t; BEGIN FOR f in SELECT * FROM UNNEST(_foos) LOOP RAISE NOTICE '% %', f.a, f.b; END LOOP; END; $$ LANGUAGE PLPGSQL; postgres=# SELECT do_foos(ARRAY(SELECT (a,b)::foo_t FROM get_foos())); NOTICE: 1 abc NOTICE: 2 def do_foos - (1 row) Also, if you are deploying vs 9.1, be sure to check out Pavel's for-in-array which is better method to do the unnest() which expands the array. merlin -- 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] Problem with planner
Try to change index: objects_endings_tsz_active(state,endings_tsz) where state='active'. 2011/8/8, hubert depesz lubaczewski dep...@depesz.com: Hi, we have 8.3.11 installation on client site, with table, which looks like this: $ \d objects Table public.objects Column | Type | Modifiers -+--+--- ... state | text | ... ending_tsz | timestamp with time zone | default (now() + '4 mons'::interval) ... Indexes: objects_stat_user_id_creation_tsz btree (state, user_id, creation_tsz) objects_ending_tsz_active btree (ending_tsz) WHERE state = 'active'::text objects_ending_tsz_idx btree (ending_tsz) ... and we have a query: select count(*) from objects where state='active'::text and ending_tsz = ( select now() - '1 day'::interval ); Normally this query has been getting plan, using objects_ending_tsz_active, which is sane and fast. But today, without any sensible reason, it switched to: QUERY PLAN - Aggregate (cost=6719810.62..6719810.63 rows=1 width=0) InitPlan - Result (cost=0.00..0.01 rows=1 width=0) - Bitmap Heap Scan on objects (cost=1289719.58..6711422.56 rows=3355219 width=0) Recheck Cond: ((state = 'active'::text) AND (ending_tsz = $0)) - BitmapAnd (cost=1289719.58..1289719.58 rows=3355219 width=0) - Bitmap Index Scan on objects_stat_user_id_creation_tsz (cost=0.00..334318.95 rows=10065657 width=0) Index Cond: (state = 'active'::text) - Bitmap Index Scan on objects_ending_tsz_idx (cost=0.00..953722.77 rows=24986738 width=0) Index Cond: (ending_tsz = $0) (10 rows) running analyze objects 2 times in a row fixed the issue, but hour later - the problem came back. what can be wrong? Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Update with ORDER BY and LIMIT
Two tables: 1) cust (one record each customer) contains: a) lpmtdt (date = last payment date) b) lpmtamt (numeric = last payment amount) c) custno (varchar(6) = customer string) 2) cashh (one record each income/cash transaction) contains a) custno (varchar(6) = customer string) b) rcptamt (numeric = amount of receipt) c) rcptdt (date = date of receipt) For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes lacking values and shouldn't be. I want to update the customer table to update these values from the cashh table. I don't want to use an internal function. The PG version is 8.X. I can get the proper updating record with: SELECT rcptamt, rcptdt FROM cashh WHERE custno = 'COL1' ORDER BY rcptdt DESC LIMIT 1; (This gives me the latest cash receipt for this customer.) But I can't seem to merge this with an UPDATE cust ... query so the update happens in one step. Any help? Paul -- Paul M. Foster http://noferblatz.com http://quillandmouse.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] Update with ORDER BY and LIMIT
For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes lacking values and shouldn't be. I want to update the customer table to update these values from the cashh table. I don't want to use an internal function. The PG version is 8.X. -- No such version. All PostgreSQL released versions use the numbers 0-9 and periods only; no letters. The general form for an UPDATE is: UPDATE table SET field = table2.field FROM table2 WHERE table.field = table2.field; SO: UPDATE customer SET lpmtdt = rcpt.rcptdt, lpmtamt = rcpt.rcptamt FROM (SELECT custno, rcptdt, rcptamt FROM cashh WHERE ... ORDER BY ... LIMIT 1) rcpt WHERE customer.custno = rcpt.custno AND customer.lptmdt IS NULL OR customer.lpmtamt IS NULL NOT TESTED You WILL need to work on the sub-query if you hope to be able to do more than 1 customer at a time. In particular the use of WINDOW is very handy in solving this particular but your non-existent version of PostgreSQL may not have them available since they were introduced during the 8 series of releases. However, you can still write the sub-query to give you the necessary lookup table but going a couple of levels deeper with sub-queries. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] upgrade from 8.3 to 8.4
I am going to upgrade our PostgresSQL server from 8.3 to 8.4. Is there any application code change required? I did some google searches and can't find any. thanks,Claire
Re: [GENERAL] upgrade from 8.3 to 8.4
Your question is impossible to answer. Look here (and in the release docs for the point releases): http://www.postgresql.org/docs/8.4/static/release-8-4.html and then ask more specific questions if you are still concerned. Google is a good tool if you know how to write a good query (did you try postgresql 8.4 release notes) but you are also advised to browse the PostgreSQL site (namely the section on the homepage that provides links to the notes for the most recent four releases). It is also recommended that you TEST your application against the new database version in a non-production environment. And the implicit question of whether it is worth the effort to upgrade to 8.4 (or higher.) also cannot be answered by someone who has never seen your code. Backward compatibility is not guaranteed between versions BUT it is not always that case that you are actually using the things that break. David J. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Claire Chang Sent: Monday, August 08, 2011 7:12 PM To: pgsql-general@postgresql.org Subject: [GENERAL] upgrade from 8.3 to 8.4 I am going to upgrade our PostgresSQL server from 8.3 to 8.4. Is there any application code change required? I did some google searches and can't find any. thanks, Claire
Re: [GENERAL] Update with ORDER BY and LIMIT
On Mon, Aug 08, 2011 at 05:34:14PM -0400, David Johnston wrote: For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes lacking values and shouldn't be. I want to update the customer table to update these values from the cashh table. I don't want to use an internal function. The PG version is 8.X. -- No such version. All PostgreSQL released versions use the numbers 0-9 and periods only; no letters. 8.X in this context means 8 point something, but I can't recall which something. Could be 8.2, 8.3 or 8.4. Thus, in effect, asking those replying to restrict themselves to 8 series features, as opposed to 9 series features. The general form for an UPDATE is: UPDATE table SET field = table2.field FROM table2 WHERE table.field = table2.field; SO: UPDATE customer SET lpmtdt = rcpt.rcptdt, lpmtamt = rcpt.rcptamt FROM (SELECT custno, rcptdt, rcptamt FROM cashh WHERE ... ORDER BY ... LIMIT 1) rcpt WHERE customer.custno = rcpt.custno AND customer.lptmdt IS NULL OR customer.lpmtamt IS NULL NOT TESTED Works well enough as a starting point. Thanks. Paul -- Paul M. Foster http://noferblatz.com http://quillandmouse.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] upgrade from 8.3 to 8.4
On 08/08/11 4:11 PM, Claire Chang wrote: I am going to upgrade our PostgresSQL server from 8.3 to 8.4. Is there any application code change required? I did some google searches and can't find any. to what David already said, I will add this observation... 8.4 is pickier about implied type conversions... if you have code that assumes it can mix types like text and numeric operations, it may require adding some explicit type casting, or it will throw errors. thats the only major thing I've seen that impacts applications. I assume you know that you need to do a pg_dumpall of your 8.3 data then load it onto your new 8.4 database cluster, right? -- john r pierceN 37, W 122 santa cruz ca mid-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] Update with ORDER BY and LIMIT
8.X in this context means 8 point something, but I can't recall which something. Could be 8.2, 8.3 or 8.4. Thus, in effect, asking those replying to restrict themselves to 8 series features, as opposed to 9 series features. There are a lot of features added between 8.0 and 8.4; WITH and WINDOW being two major ones, that just saying 8 is not helpful. In the future please take the time to issue a SELECT pg_version() before asking others to take time to help. It is for your own benefit and makes it easier for those wanting to help to give useful advice. David J. -- 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] table / query as a prameter for PL/pgSQL function
Hi,
2011/8/9 Merlin Moncure mmonc...@gmail.com:
You have a few of different methods for passing sets between functions.
I do not want to pass data between functions. The ideal solution
should look like this:
select * from my_map_func(select query)
1) refcursor as David noted. reasonably fast. however, I find the
'FETCH' mechanic a little inflexible.
I've came across this but manual example wasn't (isn't) clear to me:
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
What is the funccursor?
Anyway, I will try to use something like this
CREATE FUNCTION my_map_func(refcursor) returns setof text[] as $$
...
$$ LANGUAGE 'plpgsql';
BEGIN;
DECLARE my_cursor NO SCROLL CURSOR FOR query;
SELECT * FROM my_map_func(my_cursor);
COMMIT;
I'll keep you posted.
2) stage data to TABLE/TEMP TABLE; extremely flexible, but can be a
headache because a non temp table can get thrashed pretty hard a and a
'TEMP' can cause severe function plan invalidation issues if you're
not careful
I'm not familiar with this issue (function plan invalidation issues).
Could you please provide more details/links about it?
3) arrays of composites -- the most flexible and very fast for *small*
amounts of records (say less than 10,000):
My data set is huge: between 1 and 5 mil rows and avg row size is 100
- 400 bytes
--
Ondrej Ivanic
(ondrej.iva...@gmail.com)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
