[GENERAL] permission denied
Hello, I am facing a weird permission problem with the views in our database. While the system is up and running, suddenly a certain view gets corrupted. When we try to query it, it returns permission denied. Although the permission has not been changed and if I check them using \z the system displays that all the permissions are still on. The only solution is to drop the view and create it back again. 3 views had been corrupted the past week. Until now, all the errors are on our SIT and UAT servers. I am afraid that soon we will be facing those same problems on our production database. We are using PostgreSQL 8.2.11 on Red Hat What seems to be the problem ? Can anyone help please ? Pascal TUFENKJI Service de Technologie de l'Information Université Saint-Joseph - Rectorat Tel: +961 1 421 132 Email: mailto:ptufen...@usj.edu.lb ptufen...@usj.edu.lb
Re: [GENERAL] Connection pooling
I have now installed the pgbouncer and it seemed to help a little but it did not bring the connections back down once the connections ended or people stopped using it. although it would fluctuate up and down on occasion but it still reached 100 connections. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776479.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] Connection pooling
I have never used pgbouncer myself. But my guess is you have to look at the Timeout parameters in the configuration file. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776481.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] problem with partitioned table and indexed json field
Hi,
I have a partitioned table events, with one partition for each month, eg
events_2013_03. The partition is done on the field timestamp, and
constraints are set, but insertion of data is done in the partition
directly (so not with a trigger on the events table)
The field event is of type json, and has a field '_id', which I can access:
= select event-'_id' from events limit 1;
?column?
--
4f9a786f44650105b50aafc9
I created an index on each partition of the table, but not on the events
table itself:
create index events_${y}_${m}_event_id_index on events_${y}_${m}
((event-'_id'));
Querying the max event_id from a partition works fine:
= select max(event-'_id') from events_2013_03;
max
--
5158cdfe4465012cff522b74
However, requesting on the parent table does return the whole json field,
and not only the '_id':
= select max(event-'_id') from events;
{_id:526eb3ad4465013e3e131a43,origin:. }
An explain returns an error:
= explain select max(event-'_id') from events;
ERROR: no tlist entry for key 2
This problem appeared when I created the indexes, and removing the index
make the explain work fine, but the plan implies a sequential scan on the
tables which is exactly what I wanted to avoid with the indexes.
Does someone have an explanation, and possibly a way to solve this problem?
thanks
Raph
Re: [GENERAL] Connection pooling
Isn'it a client problem? It should be client application closing connection when done with data retrieval, and not the other way around? Cheers, Rémi-C 2013/10/31 Jayadevan maymala.jayade...@gmail.com I have never used pgbouncer myself. But my guess is you have to look at the Timeout parameters in the configuration file. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776481.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] Connection pooling
currently my pg bouncer.ini looks like this : [databases] manifold = host=localhost port=5432 dbname=manifold user=postgrest password=123ert [pgbouncer] logfile = C:\Program Files\PostgreSQL\log\pgbouncer.log pidfile = C:\Program Files\PostgreSQL\log\pgbouncer.pid listen_addr = * listen_port = 6432 admin_users = test stats_users = test pool_mode = transaction server_reset_query = DISCARD ALL ignore_startup_parameters = application_name max_client_conn = 400 default_pool_size = 20 not to sure if I need to change any of these: ; how many additional connection to allow in case of trouble ;reserve_pool_size = 5 ; if a clients needs to wait more than this many seconds, use reserve pool ;reserve_pool_timeout = 3 ; log if client connects or server connection is made ;log_connections = 1 ; log if and why connection was closed ;log_disconnections = 1 ; log error messages pooler sends to clients ;log_pooler_errors = 1 ; If off, then server connections are reused in LIFO manner ;server_round_robin = 0 ;;; ;;; Timeouts ;;; ;; Close server connection if its been connected longer. ;server_lifetime = 1200 ;; Close server connection if its not been used in this time. ;; Allows to clean unnecessary connections from pool after peak. ;server_idle_timeout = 60 ;; Cancel connection attempt if server does not answer takes longer. ;server_connect_timeout = 15 ;; If server login failed (server_connect_timeout or auth failure) ;; then wait this many second. ;server_login_retry = 15 ;; Dangerous. Server connection is closed if query does not return ;; in this time. Should be used to survive network problems, ;; _not_ as statement_timeout. (default: 0) ;query_timeout = 0 ;; Dangerous. Client connection is closed if the query is not assigned ;; to a server in this time. Should be used to limit the number of queued ;; queries in case of a database or network failure. (default: 0) ;query_wait_timeout = 0 ;; Dangerous. Client connection is closed if no activity in this time. ;; Should be used to survive network problems. (default: 0) ;client_idle_timeout = 0 ;; Disconnect clients who have not managed to log in after connecting ;; in this many seconds. ;client_login_timeout = 60 ;; Clean automatically created database entries (via *) if they ;; stay unused in this many seconds. ; autodb_idle_timeout = 60 ;;; ;;; Low-level tuning options ;;; ;; buffer for streaming packets ;pkt_buf = 2048 ;; man 2 listen ;listen_backlog = 128 ;; networking options, for info: man 7 tcp ;; Linux: notify program about new connection only if there ;; is also data received. (Seconds to wait.) ;; On Linux the default is 45, on other OS'es 0. ;tcp_defer_accept = 0 ;; In-kernel buffer size (Linux default: 4096) ;tcp_socket_buffer = 0 ;; whether tcp keepalive should be turned on (0/1) ;tcp_keepalive = 1 ;; following options are Linux-specific. ;; they also require tcp_keepalive=1 ;; count of keepaliva packets ;tcp_keepcnt = 0 ;; how long the connection can be idle, ;; before sending keepalive packets ;tcp_keepidle = 0 ;; The time between individual keepalive probes. ;tcp_keepintvl = 0 ;; DNS lookup caching time ;dns_max_ttl = 15 ;; DNS zone SOA lookup period ;dns_zone_check_period = 0 I am currently running postgres 9.2 with geoserver 2.4.1 apache tomcat. I need to know how to close the connections that is being used or is the pgbouncer not setup correctly. I followed a pdf tutorial on how to set it up and all seemed to be working including in the adminatrator command section. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776489.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] Connection pooling
I'm not 100% sure I follow in that part of if its the client cause currently when I run it on my own computer it does the same thing. Only when I stop tomcat and start it again then i get the 3 default connection that postgres has set up. our server does the same thing. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776490.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] Connection pooling
Hey, I might be completly wrong, but when you say get the connections to close if they are not being used, I'd say that it is a bad client design to not close a connection when it doesn't need it anymore. The client should retrieve the data or close when not using after a certain amount of time. What you are trying to do is garbage collector. Cheers, Rémi-C 2013/10/31 si24 smrcoutt...@gmail.com I'm not 100% sure I follow in that part of if its the client cause currently when I run it on my own computer it does the same thing. Only when I stop tomcat and start it again then i get the 3 default connection that postgres has set up. our server does the same thing. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776490.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] Connection pooling
On 01/11/13 00:10, Rémi Cura wrote: Hey, I might be completly wrong, but when you say get the connections to close if they are not being used, I'd say that it is a bad client design to not close a connection when it doesn't need it anymore. The client should retrieve the data or close when not using after a certain amount of time. What you are trying to do is garbage collector. Cheers, Rémi-C 2013/10/31 si24 smrcoutt...@gmail.com mailto:smrcoutt...@gmail.com I'm not 100% sure I follow in that part of if its the client cause currently when I run it on my own computer it does the same thing. Only when I stop tomcat and start it again then i get the 3 default connection that postgres has set up. our server does the same thing. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776490.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general making breaking connections costs elapsed time processor activity etc. - which is why they should be pooled for reuse Cheers, Gavin
Re: [GENERAL] Connection pooling
for some reason it not always pooling the connections for reuse so I'm not sure what or if I have left something out. As I only have a 100 postgres connections and when you have six people working on it at the same time the connections neary go all the way. Which in turn starts making pink tiles from geoserver so then its not reading the data properly. Geoserver itself seems to use 17 connections from postgres which is reading the 17 different tables that each layer reads to create the map. has it some thing to do with geoserver do I need to change something there first. I can extend the max connections in postgres but if we ended up having alot more people looking at the map I'm not to sure where the balance would be. It seems for each person that they seem to get about +-20 or more connections each depending on there use of the map if they add the layers that are overlyed over the map like zones etc... -- View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776498.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] problem with partitioned table and indexed json field
On Thu, Oct 31, 2013 at 5:46 AM, Raphael Bauduin rbli...@gmail.com wrote:
Hi,
I have a partitioned table events, with one partition for each month, eg
events_2013_03. The partition is done on the field timestamp, and
constraints are set, but insertion of data is done in the partition directly
(so not with a trigger on the events table)
The field event is of type json, and has a field '_id', which I can access:
= select event-'_id' from events limit 1;
?column?
--
4f9a786f44650105b50aafc9
I created an index on each partition of the table, but not on the events
table itself:
create index events_${y}_${m}_event_id_index on events_${y}_${m}
((event-'_id'));
Querying the max event_id from a partition works fine:
= select max(event-'_id') from events_2013_03;
max
--
5158cdfe4465012cff522b74
However, requesting on the parent table does return the whole json field,
and not only the '_id':
= select max(event-'_id') from events;
{_id:526eb3ad4465013e3e131a43,origin:. }
An explain returns an error:
= explain select max(event-'_id') from events;
ERROR: no tlist entry for key 2
This problem appeared when I created the indexes, and removing the index
make the explain work fine, but the plan implies a sequential scan on the
tables which is exactly what I wanted to avoid with the indexes.
Does someone have an explanation, and possibly a way to solve this problem?
wow, that looks like a bug. Can you post the specific postgres version?
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 partitioned table and indexed json field
It's postgresql 9.3, from the pgdg apt repository:
9.3.0-2.pgdg10.4+1
Raph
On Thu, Oct 31, 2013 at 1:48 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Thu, Oct 31, 2013 at 5:46 AM, Raphael Bauduin rbli...@gmail.com
wrote:
Hi,
I have a partitioned table events, with one partition for each month, eg
events_2013_03. The partition is done on the field timestamp, and
constraints are set, but insertion of data is done in the partition
directly
(so not with a trigger on the events table)
The field event is of type json, and has a field '_id', which I can
access:
= select event-'_id' from events limit 1;
?column?
--
4f9a786f44650105b50aafc9
I created an index on each partition of the table, but not on the events
table itself:
create index events_${y}_${m}_event_id_index on events_${y}_${m}
((event-'_id'));
Querying the max event_id from a partition works fine:
= select max(event-'_id') from events_2013_03;
max
--
5158cdfe4465012cff522b74
However, requesting on the parent table does return the whole json field,
and not only the '_id':
= select max(event-'_id') from events;
{_id:526eb3ad4465013e3e131a43,origin:. }
An explain returns an error:
= explain select max(event-'_id') from events;
ERROR: no tlist entry for key 2
This problem appeared when I created the indexes, and removing the index
make the explain work fine, but the plan implies a sequential scan on the
tables which is exactly what I wanted to avoid with the indexes.
Does someone have an explanation, and possibly a way to solve this
problem?
wow, that looks like a bug. Can you post the specific postgres version?
merlin
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
Re: [GENERAL] permission denied
On 10/31/2013 12:53 AM, Pascal Tufenkji wrote: Hello, I am facing a weird permission problem with the views in our database. While the system is up and running, suddenly a certain view gets corrupted. When we try to query it, it returns “permission denied”. Although the permission has not been changed and if I check them using “\z” the system displays that all the permissions are still on. The only solution is to drop the view and create it back again. 3 views had been corrupted the past week. Until now, all the errors are on our SIT and UAT servers. I am afraid that soon we will be facing those same problems on our production database. We are using PostgreSQL 8.2.11 on Red Hat I might as well be the first to say it, version 8.2 is no longer supported. What seems to be the problem ? Hard to say. Can anyone help please ? What is the exact error message returned? How are the views being defined? Are the tables under the views being changed? *Pascal TUFENKJI *Service de Technologie de l'Information Université Saint-Joseph - Rectorat Tel: +961 1 421 132 Email: ptufen...@usj.edu.lb mailto:ptufen...@usj.edu.lb -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] permission denied
Pascal Tufenkji ptufen...@usj.edu.lb writes: I am facing a weird permission problem with the views in our database. While the system is up and running, suddenly a certain view gets corrupted. When we try to query it, it returns permission denied. Although the permission has not been changed and if I check them using \z the system displays that all the permissions are still on. The only solution is to drop the view and create it back again. That's pretty odd, but ... We are using PostgreSQL 8.2.11 on Red Hat ... you're not going to get a lot of interest in supporting a release series that's been EOL for nearly two years, especially when you're a dozen minor releases behind even in that series (the last 8.2 release was 8.2.23). You are *way* overdue for an update. If you can still reproduce this problem in a currently-supported release, we'll definitely take an interest. 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 partitioned table and indexed json field
Raphael Bauduin rbli...@gmail.com writes: An explain returns an error: = explain select max(event-'_id') from events; ERROR: no tlist entry for key 2 This is certainly a bug. Can we see a self-contained example that triggers that? 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
[GENERAL] Explanantion on pgbouncer please
Can some one please give me a bit more of a better explanation on how exactly the pgbouncer works as I am now lost. I'm not sure if it is pooling the connections cause surely if its not being used the connections should go down not up i.e i run the webpage which has my map running which is an open layers map reading geoserver all my data on geoserver is from a database in postgres. When you start the web page it goes to 46 connections and after moving around for a while and selecting the different overlays that I have on the map it goes up to 75 connections after not touching it for a while nothing happens the connections don't go up or down, but when I move the map around and zoom then the connections increase again to 84 connections. Please help I'm stuck -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515.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] Table with Field Serial - Problem
I have a table with only one Field ID type Serial Autonumeric and is a PK, i want insert a new record but it show me error Not null violation, but if i put a value the first INSERT work correctly but the next Insert it Show me error Unique violation, So i try adding a new field in this table and put a value null to this field, and the ID Autonumeric work . Exist a way to do it only with a field, i'm working with PHP???
Re: [GENERAL] Explanantion on pgbouncer please
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of si24 Sent: Thursday, October 31, 2013 10:25 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Explanantion on pgbouncer please Can some one please give me a bit more of a better explanation on how exactly the pgbouncer works as I am now lost. I'm not sure if it is pooling the connections cause surely if its not being used the connections should go down not up i.e i run the webpage which has my map running which is an open layers map reading geoserver all my data on geoserver is from a database in postgres. When you start the web page it goes to 46 connections and after moving around for a while and selecting the different overlays that I have on the map it goes up to 75 connections after not touching it for a while nothing happens the connections don't go up or down, but when I move the map around and zoom then the connections increase again to 84 connections. Please help I'm stuck First, when you are describing your situation, please be specific what connections you are talking about: client connections to PgBouncer or pool connections from PgBouncer to Postgres server. Second, you could learn a lot about status of your connections, when you connect as administrator to PgBouncer and use commands such as show pools, show clients, etc... Regards, Igor Neyman -- 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] Explanantion on pgbouncer please
Where are you measuring the connections? From your app to PGBouncer, or from PGBouncer to PostgreSQL? If it is from your app to PGBouncer, that sounds strange, and like the app is not properly releasing connections as it should. If it is from PGBouncer to PostgreSQL, that sounds normal. I haven't used PGBouncer, but i've used other connection poolers in the past. They would start out with a set number of minimum connections, and scale up to the max number specified as needed. The pooler wouldn't release a connection to the DB once it has made one, and just kept it available in the pool. On Thu, Oct 31, 2013 at 10:25 AM, si24 smrcoutt...@gmail.com wrote: Can some one please give me a bit more of a better explanation on how exactly the pgbouncer works as I am now lost. I'm not sure if it is pooling the connections cause surely if its not being used the connections should go down not up i.e i run the webpage which has my map running which is an open layers map reading geoserver all my data on geoserver is from a database in postgres. When you start the web page it goes to 46 connections and after moving around for a while and selecting the different overlays that I have on the map it goes up to 75 connections after not touching it for a while nothing happens the connections don't go up or down, but when I move the map around and zoom then the connections increase again to 84 connections. Please help I'm stuck -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515.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] Table with Field Serial - Problem
On 10/31/2013 07:31 AM, Yostin Vargas wrote:
I have a table with only one Field ID type Serial Autonumeric and is a
PK, i want insert a new record but it show me error Not null violation,
but if i put a value the first INSERT work correctly but the next
Insert it Show me error Unique violation,
So i try adding a new field in this table and put a value null to this
field, and the ID Autonumeric work .
Exist a way to do it only with a field, i'm working with PHP???
Some actual examples form you end would help:)
My guess is you are trying to insert a NULL value into the PK field
instead of just not inserting anything at all. An alternative is to use
the DEFAULT keyword. See below for example.
create table test_table(id_fld serial primary key, char_fld varchar);
test= \d test_table
Table public.test_table
Column | Type| Modifiers
--+---+-
id_fld | integer | not null default
nextval('test_table_id_fld_seq'::regclass)
char_fld | character varying |
Indexes:
test_table_pkey PRIMARY KEY, btree (id_fld)
test= INSERT INTO test_table (id_fld , char_fld) VALUES (NULL, 't');
ERROR: null value in column id_fld violates not-null constraint
test= INSERT INTO test_table (char_fld) VALUES ('t');
INSERT 0 1
test= INSERT INTO test_table (id_fld , char_fld) VALUES (DEFAULT, 't');
INSERT 0 1
--
Adrian Klaver
adrian.kla...@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
Re: [GENERAL] Explanantion on pgbouncer please
I'm not a 100% sure but when i type : select count (*) from pg_stat_activity; in postgres it give me the a number be 3 or 75 or higher depending on what is runnung at the time if its the webpage with the map or just postgres itself. I had thought that the client connections to PgBouncer and pool connections from PgBouncer to Postgres server was pretty much the same the same thing. that could also be a problem maybe i'm not understanding the full length of the connection term to well. I just know that I have been struggleing with this for a few days now and am still have not come to much closer to understanding it (i think). The pdf part that I had read that showed you how to set it up and shows you the command section on the show pools and all. I had did that. but will check it out again then. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776522.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] Table with Field Serial - Problem
My table is like this
Column | Type| Modifiers
--+---**+-**
--**--
id_fld | integer | not null default nextval('test_table_id_fld_
**seq'::regclass)
im using Yii Framework, How i can Put VALUES (DEFAULT) if i put DEFAULT
is like a string
2013/10/31 Adrian Klaver adrian.kla...@gmail.com
On 10/31/2013 07:31 AM, Yostin Vargas wrote:
I have a table with only one Field ID type Serial Autonumeric and is a
PK, i want insert a new record but it show me error Not null violation,
but if i put a value the first INSERT work correctly but the next
Insert it Show me error Unique violation,
So i try adding a new field in this table and put a value null to this
field, and the ID Autonumeric work .
Exist a way to do it only with a field, i'm working with PHP???
Some actual examples form you end would help:)
My guess is you are trying to insert a NULL value into the PK field
instead of just not inserting anything at all. An alternative is to use the
DEFAULT keyword. See below for example.
create table test_table(id_fld serial primary key, char_fld varchar);
test= \d test_table
Table public.test_table
Column | Type| Modifiers
--+---**+-**
--**--
id_fld | integer | not null default
nextval('test_table_id_fld_**seq'::regclass)
char_fld | character varying |
Indexes:
test_table_pkey PRIMARY KEY, btree (id_fld)
test= INSERT INTO test_table (id_fld , char_fld) VALUES (NULL, 't');
ERROR: null value in column id_fld violates not-null constraint
test= INSERT INTO test_table (char_fld) VALUES ('t');
INSERT 0 1
test= INSERT INTO test_table (id_fld , char_fld) VALUES (DEFAULT, 't');
INSERT 0 1
--
Adrian Klaver
adrian.kla...@gmail.com
Re: [GENERAL] Explanantion on pgbouncer please
I use a check in postgres to give the active connections being used. But what happens if the connection pooler goes all the way to 100 for example and say that 100 is your postgres maximum connections at the time. I know I can change the maximum connections in postgres but am not to sure on the right balance. If I have say 600 people looking at my map is that not then several calles to postgres to show them the map that each person is looking at on the computer? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776524.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] Explanantion on pgbouncer please
I don't know if this will help in terms of my problem? this came from the admin pgbouncer console. pgbouncer=# show pools; database | user| cl_active | cl_waiting | sv_active | sv_idle | sv_used| sv_tested | sv_login | maxwait ---+---+---++---+-+-+---+--+- pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0| 0 |0 | 0 (1 row) pgbouncer=# show pools; database | user| cl_active | cl_waiting | sv_active | sv_idle | sv_used| sv_tested | sv_login | maxwait ---+---+---++---+-+-+---+--+- pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0| 0 |0 | 0 (1 row) pgbouncer=# show pools; database | user| cl_active | cl_waiting | sv_active | sv_idle | sv_used| sv_tested | sv_login | maxwait ---+---+---++---+-+-+---+--+- pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0| 0 |0 | 0 (1 row) pgbouncer=# show clients; type | user | database | state | addr | port | local_addr | local_port | connect_time |request_time | ptr| link --+--+---++--+---+++-+-+--+-- C| postgres | pgbouncer | active | ::1 | 65126 | ::1| 6432 | 2013-10-31 16:52:44 | 2013-10-31 16:55:13 | 018507b0 | (1 row) pgbouncer=# show servers; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link --+--+--+---+--+--+++--+--+-+-- (0 rows) pgbouncer=# show databases; name| host| port | database | force_user | pool_size | reserve_poo ---+---+--+---++---+-- manifold | localhost | 5432 | manifold | postgrest |20 |0 pgbouncer | | 6432 | pgbouncer | pgbouncer | 2 |0 postgres | 127.0.0.1 | 5432 | postgres ||20 |0 (3 rows) pgbouncer=# show list; ERROR: invalid command 'show list;', use SHOW HELP; pgbouncer=# show lists; list | items ---+--- databases | 3 users | 2 pools | 1 free_clients |67 used_clients | 1 login_clients | 0 free_servers | 0 used_servers | 0 dns_names | 0 dns_zones | 0 dns_queries | 0 dns_pending | 0 (12 rows) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776530.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] Explanantion on pgbouncer please
For where you are measuring, everything looks normal to me. Your application will make connections to the pooler as needed, and the pooler will assign the application connection to a database connection it has available in it's pool. This gets rid of the overhead of creating a brand new connection to the DB right from the application for a small query, as connecting to the pooler is much less overhead. The pooler should eventually get to the maximum number of allowed connections. That is just how it's supposed to work. It is not supposed to any connections after your application doesn't need the them any more. It just keeps it open, and adds it to the available pool for your application to use. On Thu, Oct 31, 2013 at 10:49 AM, si24 smrcoutt...@gmail.com wrote: I use a check in postgres to give the active connections being used. But what happens if the connection pooler goes all the way to 100 for example and say that 100 is your postgres maximum connections at the time. I know I can change the maximum connections in postgres but am not to sure on the right balance. If I have say 600 people looking at my map is that not then several calles to postgres to show them the map that each person is looking at on the computer? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776524.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] Table with Field Serial - Problem
On 10/31/2013 07:55 AM, Yostin Vargas wrote:
My table is like this
Column | Type| Modifiers
--+---__+-__--__--
id_fld | integer | not null default
nextval('test_table_id_fld___seq'::regclass)
So you have a single field table, what is the purpose?
im using Yii Framework, How i can Put VALUES (DEFAULT) if i put
DEFAULT is like a string
That would seem to be a question for the Yii mailing list/forum.
--
Adrian Klaver
adrian.kla...@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
Re: [GENERAL] Table with Field Serial - Problem
Yes i have a single field because is related to another table that
contains, the name
it's for to do multilanguage
Example
Table1
Column | Type| Modifiers
--+---**+-**
--**--
id | integer | not null default nextval('test_table_id_fld_**
seq'::regclass)
Table2
Column | Type| related
--+---**+-**
--**--
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id
name | varchar
I solve it doin it $model-id=new CDbExpression('DEFAULT');
Thanks for your Helps
2013/10/31 Adrian Klaver adrian.kla...@gmail.com
On 10/31/2013 07:55 AM, Yostin Vargas wrote:
My table is like this
Column | Type| Modifiers
--+---**__+---**
--__--**__--
id_fld | integer | not null default
nextval('test_table_id_fld___**seq'::regclass)
So you have a single field table, what is the purpose?
im using Yii Framework, How i can Put VALUES (DEFAULT) if i put
DEFAULT is like a string
That would seem to be a question for the Yii mailing list/forum.
--
Adrian Klaver
adrian.kla...@gmail.com
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Wed, Oct 30, 2013 at 3:16 PM, Brian Crowell br...@fluggo.com wrote: Hello again! I've been setting up my PostgreSQL server by doing something I've never done before: I've joined a Linux server to a domain so I can use integrated Kerberos authentication from server to server. I've managed to make this work from Linux machine to Linux machine. On the client, I retrieve my Kerberos ticket with kinit, and then I can use psql with my username to connect to the server without a password over GSSAPI. So far, so good. However, the eventual goal was to connect to this same server from a .NET app running on Windows, and here I've run into a snag. The Npgsql library does not support GSSAPI—it only supports SSPI, which is nearly-but-not-enough-like the same thing to count in this situation, because I can't seem to configure my PostgreSQL on Linux to accept SSPI. If I try it, I get the error: invalid authentication method sspi: not supported by this build The docs say that SSPI is supported if GSSAPI is available (http://www.postgresql.org/docs/9.3/static/auth-methods.html#SSPI-AUTH). Is this just a matter of me running the wrong build? I'm using PostgreSQL 9.3 from the official builds for Debian 6. If the docs are wrong, and SSPI isn't available server-side on Linux, what are my other options? hm -- maybe use ODBC? 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] Table with Field Serial - Problem
On 10/31/2013 08:23 AM, Yostin Vargas wrote:
Yes i have a single field because is related to another table that
contains, the name
it's for to do multilanguage
Example
Table1
Column | Type| Modifiers
--+---__+-__--__--
id | integer | not null default
nextval('test_table_id_fld___seq'::regclass)
Table2
Column | Type| related
--+---__+-__--__--
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id http://lang.id
name | varchar
I may be having one of my dumb moments, but what does the above
accomplish that including the serial column in Table2 does not?
I solve it doin it $model-id=new CDbExpression('DEFAULT');
Great.
Thanks for your Helps
--
Adrian Klaver
adrian.kla...@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
Re: [GENERAL] Connection pooling
On 10/31/2013 5:34 AM, si24 wrote: It seems for each person that they seem to get about +-20 or more connections each depending on there use of the map if they add the layers that are overlyed over the map like zones etc... that sounds really broken. -- 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] Explanantion on pgbouncer please
On 10/31/2013 7:38 AM, Adam Brusselback wrote: If it is from your app to PGBouncer, that sounds strange, and like the app is not properly releasing connections as it should. If it is from PGBouncer to PostgreSQL, that sounds normal. I haven't used PGBouncer, but i've used other connection poolers in the past. They would start out with a set number of minimum connections, and scale up to the max number specified as needed. The pooler wouldn't release a connection to the DB once it has made one, and just kept it available in the pool. except, there's no way a single web page access should be using 70 or 80 connections. 2-3, ok, maybe. -- 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] Table with Field Serial - Problem
Adrian Klaver-3 wrote
Table1
Column | Type| Modifiers
--+---__+-__--__--
id | integer | not null default
nextval('test_table_id_fld___seq'::regclass)
Table2
Column | Type| related
--+---__+-__--__--
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id
lt;http://lang.idgt;
name | varchar
The PK for table 2 is composite: the serial key from table 1 + the language
id. The table 1 id has to be able to repeat since the same entity needs
multiple translations. Using a serial on table 2 is also possible but a
separate issue and probably not worth adding since you need a unique index
on (id_table1, id_lang) regardless.
The question is why isn't there some kind of identifier on table 1 that
gives you some idea of what the id/table record is for?
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Table-with-Field-Serial-Problem-tp5776516p5776546.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] Table with Field Serial - Problem
yes i can put other field for identifier , but i think that whit the name
of the table i can know it
2013/10/31 David Johnston pol...@yahoo.com
Adrian Klaver-3 wrote
Table1
Column | Type| Modifiers
--+---__+-__--__--
id | integer | not null default
nextval('test_table_id_fld___seq'::regclass)
Table2
Column | Type| related
--+---__+-__--__--
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id
http://lang.id
name | varchar
The PK for table 2 is composite: the serial key from table 1 + the language
id. The table 1 id has to be able to repeat since the same entity needs
multiple translations. Using a serial on table 2 is also possible but a
separate issue and probably not worth adding since you need a unique index
on (id_table1, id_lang) regardless.
The question is why isn't there some kind of identifier on table 1 that
gives you some idea of what the id/table record is for?
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Table-with-Field-Serial-Problem-tp5776516p5776546.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] Table with Field Serial - Problem
On 10/31/2013 09:32 AM, David Johnston wrote:
Adrian Klaver-3 wrote
Table1
Column | Type| Modifiers
--+---__+-__--__--
id | integer | not null default
nextval('test_table_id_fld___seq'::regclass)
Table2
Column | Type| related
--+---__+-__--__--
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id
lt;http://lang.idgt;
name | varchar
The PK for table 2 is composite: the serial key from table 1 + the language
id. The table 1 id has to be able to repeat since the same entity needs
multiple translations. Using a serial on table 2 is also possible but a
separate issue and probably not worth adding since you need a unique index
on (id_table1, id_lang) regardless.
The question is why isn't there some kind of identifier on table 1 that
gives you some idea of what the id/table record is for?
Exactly the id_table1 FK has no context, it is just a number generator,
so why make it separate? If want to just generate numbers why not just
use the sequence directly?
David J.
--
Adrian Klaver
adrian.kla...@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
Re: [GENERAL] Explanantion on pgbouncer please
si24 wrote Can some one please give me a bit more of a better explanation on how exactly the pgbouncer works as I am now lost. Working from theory here but: Pool (PGBouncer) Connections: 1 PostgreSQL Connections: 1 Container Threads: 2 [A, B] Thread A: get connection - OK Thread B: get connection - waiting for available connection Thread A: do something with connection Thread B: still waiting Thread A: ***RELEASE CONNECTION*** goes back into the pool Thread B: got a connection now Thread A: get connection - waiting for available connection Thread B: do something with connection Thread A: still waiting Thread B: ***RELEASE CONNECTION*** goes back into the pool Thread A: got a connection now Each pool connection maintains a persistent connection in PostgreSQL The number of pool connections can fluctuate between a minimum and maximum If a pool connection is checked out it will never go away. Only after the caller has released/closed the connection can it be potentially removed from the pool. If there is not activity for a long while, and all pool connections have been closed, the number of open connections should eventually go back down to the minimum. My guess is that a failure to close these connections is causing the pooler to think they are still in use and thus cannot release them from the pool and close the matching connection to PostgreSQL. I'd suggest you try some interactive use of your application by pretending that you are two users and there is only a single connection available for use. Only once that scenario works correctly should you go ahead and increase the number of available connections. These connections, generally, only aid in concurrency performance and should not be required to make you application work properly. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776554.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] Explanantion on pgbouncer please
On czw, paź 31, 2013 at 07:25:21 -0700, si24 wrote: Can some one please give me a bit more of a better explanation on how exactly the pgbouncer works as I am now lost. I'm not sure if it is pooling the connections cause surely if its not being used the connections should go down not up i.e i run the webpage which has my map running which is an open layers map reading geoserver all my data on geoserver is from a database in postgres. When you start the web page it goes to 46 connections and after moving around for a while and selecting the different overlays that I have on the map it goes up to 75 connections after not touching it for a while nothing happens the connections don't go up or down, but when I move the map around and zoom then the connections increase again to 84 connections. You probably are using session pooling mode in pgbouncer, and persistent connections in app. This is generally not so good idea. Reasons, explanation, and suggestions: http://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/ depesz signature.asc Description: Digital signature
Re: [GENERAL] Table with Field Serial - Problem
i really dont need a number generator, only a unique PK. but i want that
this PK be generate automatically
for example i have a Category calling Computer in English but i have the
same Category in Spanish (Computadora) i assigned the ID-1 for both
So if i put the Pk ID in the table2 number i have error for unique
violation when i want INSERT another name in a diferent language for the
same category
For that reason i declare ID in the table2 like a FK from ID in the table1
2013/10/31 Adrian Klaver adrian.kla...@gmail.com
On 10/31/2013 09:32 AM, David Johnston wrote:
Adrian Klaver-3 wrote
Table1
Column | Type| Modifiers
--+---**__+---**
--__--**__--
id | integer | not null default
nextval('test_table_id_fld___**seq'::regclass)
Table2
Column | Type| related
--+---**__+---**
--__--**__--
id_table1 | integer | FK of Table1.id
id_lang | integer | FK of lang.id
lt;http://lang.idgt;
name | varchar
The PK for table 2 is composite: the serial key from table 1 + the
language
id. The table 1 id has to be able to repeat since the same entity needs
multiple translations. Using a serial on table 2 is also possible but a
separate issue and probably not worth adding since you need a unique index
on (id_table1, id_lang) regardless.
The question is why isn't there some kind of identifier on table 1 that
gives you some idea of what the id/table record is for?
Exactly the id_table1 FK has no context, it is just a number generator, so
why make it separate? If want to just generate numbers why not just use the
sequence directly?
David J.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table with Field Serial - Problem
On 10/31/2013 11:12 AM, Yostin Vargas wrote: i really dont need a number generator, only a unique PK. but i want that this PK be generate automatically for example i have a Category calling Computer in English but i have the same Category in Spanish (Computadora) i assigned the ID-1 for both So table1 is the category table: id serial category varchar or Why not just make your PK a natural one (category, language)? In the end whatever works for you, works. I am just asking because I could not follow the logic and I needed guidance. So if i put the Pk ID in the table2 number i have error for unique violation when i want INSERT another name in a diferent language for the same category For that reason i declare ID in the table2 like a FK from ID in the table1 -- Adrian Klaver adrian.kla...@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
[GENERAL] postgresql-9.3.1-1-windows-x64.exe does not install correctly for me
postgresql-9.3.1-1-windows-x64.exe [cid:image001.png@01CED62F.CDB7E0F0] Problem running post-install step. Installation may not complete correctly The database cluster initialization failed. Is there an installation log I can examine to determine the problem more completely? System information: OS Name Microsoft Windows Server 2012 Standard Version 6.2.9200 Build 9200 Other OS Description Not Available OS Manufacturer Microsoft Corporation System NameDCORBIT System ManufacturerDell Inc. System Model Precision T3600 System Type x64-based PC System SKU ProcessorIntel(R) Xeon(R) CPU E5-1650 0 @ 3.20GHz, 3192 Mhz, 6 Core(s), 12 Logical Processor(s) BIOS Version/Date Dell Inc. A09, 5/3/2013 SMBIOS Version 2.6 Embedded Controller Version255.255 BIOS Mode Legacy BaseBoard Manufacturer Dell Inc. BaseBoard ModelNot Available BaseBoard Name Base Board Platform RoleWorkstation Secure Boot StateUnsupported PCR7 Configuration Not Available Windows Directory C:\windows System Directory C:\windows\system32 Boot Device\Device\HarddiskVolume2 Locale United States Hardware Abstraction LayerVersion = 6.2.9200.16442 User Name CORPORATE\dcorbit Time Zone Pacific Daylight Time Installed Physical Memory (RAM) 32.0 GB Total Physical Memory 31.9 GB Available Physical Memory 2.59 GB Total Virtual Memory 53.9 GB Available Virtual Memory 18.8 GB Page File Space 22.0 GB Page File C:\pagefile.sys A hypervisor has been detected. Features required for Hyper-V will not be displayed. inline: image001.png
Re: [GENERAL] Connection pooling
On 10/31/2013 11:15 AM, John R Pierce wrote: On 10/31/2013 5:34 AM, si24 wrote: It seems for each person that they seem to get about +-20 or more connections each depending on there use of the map if they add the layers that are overlyed over the map like zones etc... that sounds really broken. I'm not sure if geoserver is like openLayers/mapserver, but in the later case (which I use), you can set it up to have the browser (running openLayers) request multiple layers at the same time... and on top of that each layer can be requested in tiles. (That way when you drag the map it only has to load new tiles, and not the entire image, which makes the animations smooth) Anyway, one webclient * 5 layers * tiles = lot and lots and lots of web requests (which, would make lots of db hits). If geoserver is like openLayers/mapserver, that is. -Andy -- 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] Explanantion on pgbouncer please
On 10/31/2013 10:02 AM, si24 wrote: I don't know if this will help in terms of my problem? this came from the admin pgbouncer console. pgbouncer=# show pools; database | user| cl_active | cl_waiting | sv_active | sv_idle | sv_used| sv_tested | sv_login | maxwait ---+---+---++---+-+-+---+--+- pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0| 0 |0 | 0 (1 row) This looks wrong. There should be another pool listed for your actual database. I don't think geoserver is hitting pgbouncer. You changed the port number for the database connection in geoserver, right? -Andy -- 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] Connection pooling
On 10/31/2013 1:09 PM, andy wrote: I'm not sure if geoserver is like openLayers/mapserver, but in the later case (which I use), you can set it up to have the browser (running openLayers) request multiple layers at the same time... and on top of that each layer can be requested in tiles. (That way when you drag the map it only has to load new tiles, and not the entire image, which makes the animations smooth) Anyway, one webclient * 5 layers * tiles = lot and lots and lots of web requests (which, would make lots of db hits). sure, but those hits shouldn't each be using a new connection, no more than 2-4 of them should be processed concurrently following http 1.1 guidelines, so no more than 2-4 connections should be required. the behavior the OP descibed sounded like the tomcat applets aren't closing their connections, thats broken behavior. -- 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] Connection pooling
On 10/31/2013 3:24 PM, John R Pierce wrote: On 10/31/2013 1:09 PM, andy wrote: I'm not sure if geoserver is like openLayers/mapserver, but in the later case (which I use), you can set it up to have the browser (running openLayers) request multiple layers at the same time... and on top of that each layer can be requested in tiles. (That way when you drag the map it only has to load new tiles, and not the entire image, which makes the animations smooth) Anyway, one webclient * 5 layers * tiles = lot and lots and lots of web requests (which, would make lots of db hits). sure, but those hits shouldn't each be using a new connection, no more than 2-4 of them should be processed concurrently following http 1.1 guidelines, so no more than 2-4 connections should be required. the behavior the OP descibed sounded like the tomcat applets aren't closing their connections, thats broken behavior. Ah, yes, you're right. Lots and lots of requests, but not all of them concurrently. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] autovaccum task got cancelled
Hello, I'm running an application (with programs in Perl) through pgpool 3.1 with replication mode to two postgresql db servers (version 9.0.13). Recently, I noticed that the following messages repeatedly showed in postgres log files. As far as I know, the application programs do not make any specific lock on the 'file' table. I'm not sure if it is caused by the pgpool or something else. Thanks for any help in advance. Gary 2013-10-31 17:58:56 UTCDETAIL: Process 8580 waits for ShareRowExclusiveLock on relation 11959608 of database 596746. 2013-10-31 17:58:56 UTCSTATEMENT: LOCK TABLE file IN SHARE ROW EXCLUSIVE MODE 2013-10-31 17:58:56 UTCERROR: canceling autovacuum task 2013-10-31 17:58:56 UTCCONTEXT: automatic vacuum of table sd3ops1.public.file 2013-10-31 18:01:30 UTCLOG: sending cancel to blocking autovacuum PID 8614 2013-10-31 18:01:30 UTCDETAIL: Process 8677 waits for ShareRowExclusiveLock on relation 11959608 of database 596746. 2013-10-31 18:01:30 UTCSTATEMENT: LOCK TABLE file IN SHARE ROW EXCLUSIVE MODE 2013-10-31 18:01:30 UTCERROR: canceling autovacuum task 2013-10-31 18:01:30 UTCCONTEXT: automatic vacuum of table sd3ops1.public.file 2013-10-31 18:01:49 UTCLOG: could not receive data from client: Connection reset by peer 2013-10-31 18:01:49 UTCLOG: unexpected EOF within message length word 2013-10-31 18:02:04 UTCLOG: sending cancel to blocking autovacuum PID 8753 2013-10-31 18:02:04 UTCDETAIL: Process 8777 waits for ShareRowExclusiveLock on relation 11959608 of database 596746. 2013-10-31 18:02:04 UTCSTATEMENT: LOCK TABLE file IN SHARE ROW EXCLUSIVE MODE 2013-10-31 18:02:04 UTCERROR: canceling autovacuum task 2013-10-31 18:02:04 UTCCONTEXT: automatic vacuum of table sd3ops1.public.file 2013-10-31 18:03:09 UTCLOG: sending cancel to blocking autovacuum PID 8782 2013-10-31 18:03:09 UTCDETAIL: Process 8806 waits for ShareRowExclusiveLock on relation 11959608 of database 596746. 2013-10-31 18:03:09 UTCSTATEMENT: LOCK TABLE file IN SHARE ROW EXCLUSIVE MODE 2013-10-31 18:03:09 UTCERROR: canceling autovacuum task 2013-10-31 18:03:09 UTCCONTEXT: automatic vacuum of table sd3ops1.public.file 2013-10-31 18:04:04 UTCLOG: sending cancel to blocking autovacuum PID 8810 2013-10-31 18:04:04 UTCDETAIL: Process 8395 waits for ShareRowExclusiveLock on relation 11959608 of database 596746. 2013-10-31 18:04:04 UTCSTATEMENT: LOCK TABLE file IN SHARE ROW EXCLUSIVE MODE -- 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] autovaccum task got cancelled
On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu g...@sigmaspace.com wrote: Hello, I'm running an application (with programs in Perl) through pgpool 3.1 with replication mode to two postgresql db servers (version 9.0.13). Recently, I noticed that the following messages repeatedly showed in postgres log files. As far as I know, the application programs do not make any specific lock on the 'file' table. I'm not sure if it is caused by the pgpool or something else. Try setting your log_line_prefix so that more details are logged, that might help track down where the locks etc are coming from. Eg log_line_prefix = '%m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] ' and reload your config (eg. select pg_reload_conf() as the superuser)
[GENERAL] Hstore::TooBig ?
I am running a somewhat unfamiliar Ruby automation results app using ActiveRecord to manage the postgres 9.0 backend. During our automation runs we sometimes get bursts of HTTP 500 errors coming back at us, and the Ruby app log shows an Hstore::TooBig error: Hstore::TooBig (Hstore::TooBig): vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:3045:in `attributes_with_quotes' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:3036:in `each' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:3036:in `attributes_with_quotes' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:2934:in `update_without_lock' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/locking/optimistic.rb:81:in `update_without_dirty' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/dirty.rb:146:in `update_without_timestamps' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/timestamp.rb:64:in `update_without_callbacks' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/callbacks.rb:282:in `update' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:2927:in `create_or_update_without_callbacks' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/callbacks.rb:250:in `create_or_update' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:2594:in `save_without_validation!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/validations.rb:1100:in `save_without_dirty!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/dirty.rb:87:in `save_without_transactions!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:200:in `save!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `transaction' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:182:in `transaction' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:200:in `save!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:208:in `rollback_active_record_state!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:200:in `save!' app/helpers/logger_helper.rb:72:in `update_testrun' app/models/testrun.rb:267:in `transaction_with_isolation_level' ... I don't quite know where this error is coming from. The ActiveRecord source doesn't seem to have it, and I'm not familiar enough with Rails or ActiveRecord to track definitively whether the failing function is actually performing a DB operation. Moreso I cannot seem to find any mentions (post 9.0 at least) of Hstore size limitations, either key/val pairs (which 8.x docs said was 65k) or the hstore field in its entirety. So to piece out the questions, - is there a total size limitation of the hstore field? or is it theoretically large enough (1GB) that it really shouldn't matter? - is there a string size limitation of each key/val in 9.x, or is it still 65k from 8.x? - is Hstore::TooBig an error that is likely coming from postgres? or should I continue poking at ActiveRecord or other places? thanks, ~ john -- 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] Hstore::TooBig ?
On Oct 31, 2013, at 3:46 PM, john gale j...@smadness.com wrote: I don't quite know where this error is coming from. The ActiveRecord source doesn't seem to have it, and I'm not familiar enough with Rails or ActiveRecord to track definitively whether the failing function is actually performing a DB operation. Moreso I cannot seem to find any mentions (post 9.0 at least) of Hstore size limitations, either key/val pairs (which 8.x docs said was 65k) or the hstore field in its entirety. I should note there is no error thrown into my postgres log at all. ~ john
Re: [GENERAL] autovaccum task got cancelled
On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu g...@sigmaspace.com wrote: I'm running an application (with programs in Perl) through pgpool 3.1 with replication mode to two postgresql db servers (version 9.0.13). Recently, I noticed that the following messages repeatedly showed in postgres log files. As far as I know, the application programs do not make any specific lock on the 'file' table. I'm not sure if it is caused by the pgpool or something else. [...] 2013-10-31 18:01:30 UTCLOG: sending cancel to blocking autovacuum PID 8614 2013-10-31 18:01:30 UTCDETAIL: Process 8677 waits for ShareRowExclusiveLock on relation 11959608 of database 596746. 2013-10-31 18:01:30 UTCSTATEMENT: LOCK TABLE file IN SHARE ROW EXCLUSIVE MODE 2013-10-31 18:01:30 UTCERROR: canceling autovacuum task 2013-10-31 18:01:30 UTCCONTEXT: automatic vacuum of table sd3ops1.public.file From the release notes to 9.0.12 [1]: Fix performance problems with autovacuum truncation in busy workloads (Jan Wieck) Truncation of empty pages at the end of a table requires exclusive lock, but autovacuum was coded to fail (and release the table lock) when there are conflicting lock requests. Under load, it is easily possible that truncation would never occur, resulting in table bloat. Fix by performing a partial truncation, releasing the lock, then attempting to re-acquire the lock and continue. This fix also greatly reduces the average time before autovacuum releases the lock after a conflicting request arrives. [1]: http://www.postgresql.org/docs/9.0/static/release-9-0-12.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@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
Re: [GENERAL] Hstore::TooBig ?
On 10/31/2013 03:46 PM, john gale wrote: I am running a somewhat unfamiliar Ruby automation results app using ActiveRecord to manage the postgres 9.0 backend. During our automation runs we sometimes get bursts of HTTP 500 errors coming back at us, and the Ruby app log shows an Hstore::TooBig error: Hstore::TooBig (Hstore::TooBig): vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:3045:in `attributes_with_quotes' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:3036:in `each' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:3036:in `attributes_with_quotes' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:2934:in `update_without_lock' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/locking/optimistic.rb:81:in `update_without_dirty' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/dirty.rb:146:in `update_without_timestamps' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/timestamp.rb:64:in `update_without_callbacks' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/callbacks.rb:282:in `update' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:2927:in `create_or_update_without_callbacks' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/callbacks.rb:250:in `create_or_update' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/base.rb:2594:in `save_without_validation!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/validations.rb:1100:in `save_without_dirty!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/dirty.rb:87:in `save_without_transactions!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:200:in `save!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `transaction' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:182:in `transaction' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:200:in `save!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:208:in `rollback_active_record_state!' vendor/bundle/ruby/1.8/gems/activerecord-2.3.12/lib/active_record/transactions.rb:200:in `save!' app/helpers/logger_helper.rb:72:in `update_testrun' app/models/testrun.rb:267:in `transaction_with_isolation_level' ... I don't quite know where this error is coming from. The ActiveRecord source doesn't seem to have it, and I'm not familiar enough with Rails or ActiveRecord to track definitively whether the failing function is actually performing a DB operation. Moreso I cannot seem to find any mentions (post 9.0 at least) of Hstore size limitations, either key/val pairs (which 8.x docs said was 65k) or the hstore field in its entirety. So to piece out the questions, - is there a total size limitation of the hstore field? or is it theoretically large enough (1GB) that it really shouldn't matter? - is there a string size limitation of each key/val in 9.x, or is it still 65k from 8.x? - is Hstore::TooBig an error that is likely coming from postgres? or should I continue poking at ActiveRecord or other places? Two things I can think of. 1) Grep the above gems for the Hstore::TooBig message 2) Ask on a Rails list, in light of your second post where you say the error does not touch the Postgres log. Would seem to indicate a Ruby/Rails issue. thanks, ~ john -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] postgresql-9.3.1-1-windows-x64.exe does not install correctly for me
On 10/31/2013 11:53 AM, Dann Corbit wrote: postgresql-9.3.1-1-windows-x64.exe Problem running post-install step. Installation may not complete correctly The database cluster initialization failed. Is there an installation log I can examine to determine the problem more completely? http://www.enterprisedb.com/resources-community/pginst-guide#troubleshooting -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] Hstore::TooBig ?
On Oct 31, 2013, at 4:44 PM, Adrian Klaver adrian.kla...@gmail.com wrote: So to piece out the questions, - is there a total size limitation of the hstore field? or is it theoretically large enough (1GB) that it really shouldn't matter? - is there a string size limitation of each key/val in 9.x, or is it still 65k from 8.x? - is Hstore::TooBig an error that is likely coming from postgres? or should I continue poking at ActiveRecord or other places? Two things I can think of. 1) Grep the above gems for the Hstore::TooBig message 2) Ask on a Rails list, in light of your second post where you say the error does not touch the Postgres log. Would seem to indicate a Ruby/Rails issue. These were done in parallel, which is why I was asking the question on a postgres list. The further questions still apply though; are there documented size limitations for hstore? ~ john
Re: [GENERAL] Hstore::TooBig ?
On Oct 31, 2013, at 4:44 PM, Adrian Klaver adrian.kla...@gmail.com wrote: So to piece out the questions, - is there a total size limitation of the hstore field? or is it theoretically large enough (1GB) that it really shouldn't matter? - is there a string size limitation of each key/val in 9.x, or is it still 65k from 8.x? - is Hstore::TooBig an error that is likely coming from postgres? or should I continue poking at ActiveRecord or other places? Two things I can think of. 1) Grep the above gems for the Hstore::TooBig message 2) Ask on a Rails list, in light of your second post where you say the error does not touch the Postgres log. Would seem to indicate a Ruby/Rails issue. These were done in parallel, which is why I was asking the question on a postgres list. The further questions still apply though; are there documented size limitations for hstore? ~ john
Re: [GENERAL] Hstore::TooBig ?
On 10/31/2013 5:11 PM, john gale wrote: The further questions still apply though; are there documented size limitations for hstore? afaik only the practical limits of how big you want your tables to get, and the 1GB limit of the underlying text representation. having many 1000s of keys in a single hstore would likely make for some painful performance, and nasty memory requirements (returning all keynames, for instance, so you could enumerate them, ouch) -- 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] postgresql-9.3.1-1-windows-x64.exe does not install correctly for me
The PostgreSQL installer for Windows 64 appears to be broken for Microsoft Windows Server 2012 Standard. Even after uninstalling, removing the entire postgresql directory structure, and running the installer as administrator, I get this error: fixing permissions on existing directory C:/Program Files/PostgreSQL/9.3/data ... initdb: could not change permissions of directory C:/Program Files/PostgreSQL/9.3/data: Permission denied I was able to get it to install by giving permissions to the postgresql folder to applications and services, but I am not really sure what allowed it to complete. However, that sort of installation would be unsafe for regular users. I only use it for testing, so it does not really matter much in my case (though I would like it much better if I did not have to munge around to make the install work). -Original Message- From: Adrian Klaver [mailto:adrian.kla...@gmail.com] Sent: Thursday, October 31, 2013 5:03 PM To: Dann Corbit; 'pgsql-general@postgresql.org' Subject: Re: [GENERAL] postgresql-9.3.1-1-windows-x64.exe does not install correctly for me On 10/31/2013 11:53 AM, Dann Corbit wrote: postgresql-9.3.1-1-windows-x64.exe Problem running post-install step. Installation may not complete correctly The database cluster initialization failed. Is there an installation log I can examine to determine the problem more completely? http://www.enterprisedb.com/resources-community/pginst-guide#troubleshooting -- Adrian Klaver adrian.kla...@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
