[GENERAL] can I show number of records returned by a query in a log?
Hello, I'm trying to capture amount of data moving from client app to the server back. The client is executable (c#) gue on windows, server is lunix centOS, PostgreSQL 8.4. We see serious performance difference between execution via LAN VPN. I enabled the logging and used pgFouine to analyzed the logs and it looks very strange - 2000-3000 queries in 10 min. In one case I see the client (user is the client dbuser) query select * from vw_abc sent 10 times in 1 sec ( timestamp is the same), is it realy 10 times or it was logged 10 times ? I see 900 queries sent by 1 client in 7 min with 1 click on the screen - does the log show the real thing? Is it possible to log the number of records returned by that query? thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/can-I-show-number-of-records-returned-by-a-query-in-a-log-tp4999630p4999630.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] can I show number of records returned by a query in a log?
Thank you all, I did look at the log , I enabled pg_stat_statements , however it showed the buffers, scans and other info about the query execution - not the records number, any idea how can I get it? I agree that the problem is in qty of the queries, will investigate the client. thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/can-I-show-number-of-records-returned-by-a-query-in-a-log-tp4999630p4999782.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Advice on HA option
Thank you, I will look at skype's walmgr. Also could you explain what makes it hands free administration 9.0? Is the shipping of the wal file from the master to HA instance automated ? Any error checking /self recovery? thank you much for the suggestion. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-HA-option-tp4775605p4779672.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Advice on HA option
Hello, I need to implement HA for the appliance that is sold to the customer - our application has no DBA and must be very stable and self recoverable. We are running PostgreSQL 8.4. Is the warm standby a good solution for us? Thank you for all the suggestions. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-HA-option-tp4775605p4775605.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Concurrent read from a partition table.
Hi Chetan, I'm not sure how I can create a test case, I'm running queryes on 50g of data to see this. My general questions are about locking children when select from a parent, I have not seen any documentation on it. Does any parameter like 'concurrent read' exist? I'm still new to postgres, came from oracle mysql. Thanks. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-read-from-a-partition-table-tp4577154p4579785.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Concurrent read from a partition table.
Hello, I’m having a problem with concurrent processing. 2 queries are accessing the same parent table that have 24 partitions. I see “shared lock is not granted “ for one of them on one of the children while the other query is running. Does the “ select from a parent table” make a lock on the children? How I can change it? The one of the queries runs hourly ( 8 min) on the server , the other one can be run by a user , and a few users can run the same query. Thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-read-from-a-partition-table-tp4577154p4577154.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
thank you Greg, here is what I get, I createed view as you suggested. I'm not sure why tablespace column is empty profiler1=# select * from pg_tables where schemaname ='public' limit 10; schemaname |tablename| tableowner | tablespace | hasindexes | hasrules | hastri ers +-++++--+--- public | ttt | postgres | | f | f| f public | summ_hrly_1514609 | postgres | | t | f| f public | summ_5min_1514610 | postgres | | t | f| f public | exp_cnt | postgres | | f | f| f public | auth_type| postgres | | t | f| f public | druid_mapping | postgres | | t | f| f public | application_category | postgres | | t | f| f public | application_risk | postgres | | t | f| f public | policy_history| postgres | | t | f| f public | datasource | postgres | | t | f| f (10 rows) thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507266.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
well,
here is the query :
profiler1=# select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));
relname | reltablespace
-+---
why it shows no records?
profiler1=# select * from pg_catalog.pg_tables where
tablename='application_category';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastrigge
rs
+--++++--+--
---
public | application_category | postgres || t |
f| f
(1 row)
and that query show empty for the tablespace...
thank you
Helen
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507624.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
here it is : profiler1=# select oid,* from pg_tablespace; oid | spcname | spcowner | spclocation | spcacl ---++--+--+ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 19955 | profiler1 | 10 | /data/psql/profiler1 | (3 rows) profiler1=# select relname,reltablespace from pg_class where reltablespace=19955; relname | reltablespace -+--- (0 rows) thanks Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508020.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
profiler1=# select relname,reltablespace from pg_class where relname='application_category'; relname| reltablespace --+--- application_category | 0 (1 row) -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508040.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
that is exactly the case - application_category table is in profiler1 tablespace as well as all tables in my profilre1 database. I'm not sure how to update catalog... vacuum ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508315.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
I've got it now - it will be 0/empty for a table in default tablespace, if the table has been created in a different tablespace - it will show the name. thank you!. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508750.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
hello, I'm looking into pg_tables view and only one tablespace is displayed is pg_global. All my tables are created in my custom tablespace and that column is empty for them. select * from pg_tablespace show my tablespace, pgAdmin shows that tablespace for each table. I need to query the data dictionary to find the tablespace for the table in my proc. select t.spcname, c.relname AS tablename from pg_class c , pg_tablespace t where t.oid = c.reltablespace does not show my tables, only the dd tables. SELECT COALESCE(tbs.spcname, '*') AS tbsname FROM pg_catalog.pg_class AS t JOIN pg_catalog.pg_namespace AS s ON (s.oid = t.relnamespace) LEFT OUTER JOIN pg_catalog.pg_tablespace AS tbs ON (tbs.oid = t.reltablespace) WHERE t.relname like 'summ%' AND s.nspname = 'public'; returns * please help. thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4500200.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] constraint partition issue
sorry, my table has many columns and missed the datex when make it look smaller. datex is the column in the table. This is the driving constraint. My task is to have 30 days of data with 5mln to 10 mln rec in 1 hour table, so I partition it by hour table and combine them into days, which are combined into month total table. My query will have where datex between a and b , for 2-5 days ,across the day boundaries , so that partition exclusion is so much important. I don't undertsand why the optimizer goes to the children when it hits the constraint on the parent that shows the date range on that parent ? thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/constraint-partition-issue-tp4258004p4259142.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] constraint partition issue
You are right to the point, Tom. The datex is timestamp with time zone and the constraints were created as timestamp without time zone. As soon as I fixed that , it all started working. thank you! Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/constraint-partition-issue-tp4258004p4259853.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
