Re: [GENERAL] Unable to reload postgresql.conf without restarting
On Thursday, January 03, 2013 9:56 PM Jose Martinez wrote: Hi, I made some changes to postgresql.conf and I want them to take effect without having to restart the server. I tried select pg_reload_conf(); /usr/pgsql-9.1/bin/pg_ctl reload but when I do 'show all', I see no changes take effect. There settings I tried to change are: -effective_cache_size -work_mem Tried in latest HEAD code(Windows OS) and its working, after select pg_reload_conf(), the changed value is shown. Can you try just with SET command and verify the behavior, as these parameters are USERSET parameters, so no need to change config file and reload or restart. With Regards, Amit Kapila. -- 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] Large number of rows in pg_type and slow gui (pgadmin) refresh
-Original Message- From: Thomas Kellerer [mailto:spam_ea...@gmx.net] Sent: Thursday, January 03, 2013 12:31 PM To: pgsql-general@postgresql.org Subject: Re: Large number of rows in pg_type and slow gui (pgadmin) refresh Robert Klaus wrote on 03.01.2013 16:50: We have 36,000+ rows returned by SELECT oid, format_type(oid, typtypmod) AS typname FROM pg_type. My manager says this is only a small number compared to what is expected by next summer. Why do you need so many types? That sounds like something in your design is not right. Thomas Probably those are not the types Robert created explicitly. There must be lots of tables/views (m.b. lots of partitions) in the database. Every table/view adds couple records to pg_type: one type for table/view record and one type the set (array) of table/view records. 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] Monitoring streaming replication from standby on Windows
2012/12/14 Karl Denninger k...@denninger.net If it's whether the replication is caught up, I have a small C program that will do that and have posted it before (I can do that again if you'd like.) Hi Karl, I am interested to have your C program. I searched in the archives but could not find it. Can you send it again ? Thanks a lot Best regards Ali
[GENERAL] recasting to timestamp from varchar
I am trying to re-cast a column as a timestamp ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp; ERROR: column time2 cannot be cast to type timestamp without time zone The column time2 is currently a varchar. I actually do not want to mess with time zones, but it seems that postgres will not let me do this. The actual values in the column time2 look like this: 7/15/08 12:00 Is this possible?
Re: [GENERAL] recasting to timestamp from varchar
Hello 2013/1/4 Kirk Wythers kirk.wyth...@gmail.com I am trying to re-cast a column as a timestamp ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp; ERROR: column time2 cannot be cast to type timestamp without time zone The column time2 is currently a varchar. I actually do not want to mess with time zones, but it seems that postgres will not let me do this. The actual values in the column time2 look like this: 7/15/08 12:00 Is this possible? yes. it is possible postgres=# create table foo(a timestamp); CREATE TABLE postgres=# insert into foo values(now()); INSERT 0 1 postgres=# postgres=# alter table foo alter column a type varchar; ALTER TABLE postgres=# alter table foo alter column a type timestamp; ERROR: column a cannot be cast automatically to type timestamp without time zone HINT: Specify a USING expression to perform the conversion. postgres=# alter table foo alter column a type timestamp using a::timestamp; ALTER TABLE postgres=# Regards Pavel Stehule
Re: [GENERAL] Curious unnest behavior
Hello
2013/1/3 Jeff Trout thres...@real.jefftrout.com:
I just ran into an interesting thing with unnest and empty arrays.
create table x (
a int,
b int[]
);
insert into x(a,b) values (1, '{}');
insert into x(a,b) values (1, '{}');
insert into x(a,b) values (1, '{}');
select a, b from x;
select a, unnest(b) from x;
insert into x(a,b) values (2, '{5,6}');
select a, unnest(b) from x;
drop table x;
gives me:
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
a | b
---+
1 | {}
1 | {}
1 | {}
(3 rows)
a | unnest
---+
(0 rows)
INSERT 0 1
a | unnest
---+
2 | 5
2 | 6
(2 rows)
DROP TABLE
I can understand the likely reasoning behind the behavior but perhaps a note
in the documentation about it might be of use for others that may get bit by
this functionality. (especially given the structure of the query, had I been
doing select * from unnest(arr) that would be more intuitive, but given the
query structure of select with no where the results can be surprising.)
thanks
this behave (and it is really strange) is related to using SRF
function in target list - in column list. This functionality is
strange and if you can, don't use it.
originaly this functionality looks like good idea, because anybody can
play like me (or
http://www.mentby.com/Group/pgsql-general/set-returning-functions-in-select-column-list.html
)
postgres=# select unnest(array[1,2]),unnest(array[1,2]);
unnest │ unnest
┼
1 │ 1
2 │ 2
(2 rows)
but it usually doesn't working like people expected
postgres=# select unnest(array[1,2]),unnest(array[1,2,3]);
unnest │ unnest
┼
1 │ 1
2 │ 2
1 │ 3
2 │ 1
1 │ 2
2 │ 3
(6 rows)
postgres=# select unnest(array[1,2]),unnest(array[1,2,3,4]);
unnest │ unnest
┼
1 │ 1
2 │ 2
1 │ 3
2 │ 4
(4 rows)
so result is - don't use SRF (set returning funtion) in column list
if you don't need.
9.3 will support LATERAL clause, and I hope so we can drop this
functionality (one day)
Regards
Pavel Stehule
--
Jeff Trout j...@jefftrout.com
--
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] Permission for relationship but not for select is possible?
Edson Richter wrote: I would like to have two schemas: MyDB.sales MyDB.security Users that have rights in sales schema should be able to select/insert/update/delete. The same users must have rights to check foreign keys against users table (but they are now allowed to execute select on this table). Is that possible? Yes. It's pretty straightforward. You don't need permissions on a related table for the constraints to be enforced. Can you help me to find relevant page in manuals for 9.1 or 9.2? http://www.postgresql.org/docs/9.2/static/sql-createtable.html http://www.postgresql.org/docs/9.2/static/sql-grant.html http://www.postgresql.org/docs/9.2/static/sql-revoke.html -Kevin -- 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] Large number of rows in pg_type and slow gui (pgadmin) refresh
Igor Neyman wrote: Thomas Kellerer wrote: Why do you need so many types? Probably those are not the types Robert created explicitly. There must be lots of tables/views (m.b. lots of partitions) in the database. Every table/view adds couple records to pg_type: one type for table/view record and one type the set (array) of table/view records. Also, I think use of domains can help make a database more self-documenting and aid in integrity enforcement, and it's not too hard to get to a few thousand of those in a complex database. Anyway, for problems with pgadmin, your best bet would be to post to the pgadmin-support list. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgpool2 load balancing not working
Sorry if this is the wrong list, but I've been stuck for a couple days now. I tried pgpool-general but that list appears to not like me. I'm not getting any posts and my post hasn't shown up in the archives. I have a Python/Django app that will require database load balancing at some point in the near future. In the meantime I'm trying to learn to implement pgpool on a local virtual machine setup. I have 4 Ubuntu 12.04 VMs: 192.168.1.80 - pool, pgppool2 installed and accessible 192.168.1.81 - db1 master 192.168.1.82 - db2 slave 192.168.1.83 - db3 slave I have pgpool-II version 3.1.1 and my database servers are running PostgreSQL 9.1. I have my app's db connection pointed to 192.168.1.80: and it works fine. The problem is when I use Apache ab to throw some load at it, none of SELECT queries appear to be balanced. All the load goes to my db1 master. Also, very concerning is the load on the pool server itself, it is really high compared to db1, maybe an average of 8-10 times higher. Meanwhile my db2 and db3 servers have a load of nearly zero, they appear to only be replicating from db1, which isn't very load intensive for my tests with ab. ab -n 300 -c 4 -C 'sessionid=80a5fd3b6bb59051515e734326735f80' http://192.168.1.17/contacts/ That drives the load on my pool server up to about 2.3. Load on db1 is about 0.4 and load on db2 and db3 is nearly zero. Can someone take a look at my pgpool.conf and see if what I'm doing wrong? http://pastebin.com/raw.php?i=wzBc0aSp I'm starting to think maybe it has something to do with Django wrapping every request in a transaction by default, but when the transaction only has SELECTs, shouldn't that be load balanced just fine? Makes my stomach hurt to think I may have to turn off auto-commit and manually commit transactions all throughout my code :( Still hoping it's a pgpool setup issue, since it's my first time setting it up and all. Thanks. -- Greg Donald -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] recasting to timestamp from varchar
I am trying to re-cast a column as a timestamp ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp; ERROR: column time2 cannot be cast to type timestamp without time zone The column time2 is currently a varchar. I actually do not want to mess with time zones, but it seems that postgres will not let me do this. The actual values in the column time2 look like this: 7/15/08 12:00 Is this possible?
[GENERAL] recasting to timestamp from varchar
I am trying to re-cast a column as a timestamp ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp; ERROR: column time2 cannot be cast to type timestamp without time zone The column time2 is currently a varchar. I actually do not want to mess with time zones, but it seems that postgres will not let me do this. The actual values in the column time2 look like this: 7/15/08 12:00 Is this possible? Thanks in advance.
[GENERAL] DbLink connection with SSPI authentication fails
Hello,
I have installed v9.2 on Windows XP and configured SSPI authentication for
a Windows user Administrateur in pg_hba.conf
Using psql or Pgadmin, authentication with the user Administrateur works
without problem.
However I couldn't create a connection using DbLink and the Windows user, I
tried the following queries but got always the same error.
Queries:
SELECT dblink_connect('host=localhost dbname=PortefeuilleValeurs
user=Administrateur')
SELECT dblink_connect('host=localhost dbname=PortefeuilleValeurs
user=Administrateur password=')
SELECT dblink_connect_u('host=localhost dbname=BdTest user=Administrateur')
SELECT dblink_connect_u('host=localhost dbname=BdTest user=Administrateur
password=')
Error Message:
ERREUR: could not establish connection
DETAIL: FATAL: authentification SSPI échouée pour l'utilisateur «
Administrateur »
** Erreur **
ERREUR: could not establish connection
État SQL :08001
Remark : Using postgres user, DbLink works.
Could anyone tell me how to configure SSPI authentication using DbLink ?
Thanks a lot !
--
Florent Mazzone
Re: [GENERAL] pgpool2 load balancing not working
On Fri, Jan 4, 2013 at 3:42 PM, Greg Donald gdon...@gmail.com wrote: Sorry if this is the wrong list, but I've been stuck for a couple days now. I tried pgpool-general but that list appears to not like me. I'm not getting any posts and my post hasn't shown up in the archives. Specifically which address are you sending to? I'm on the official list, and it seems to work fine for me. I have a Python/Django app that will require database load balancing at some point in the near future. In the meantime I'm trying to learn to implement pgpool on a local virtual machine setup. I have 4 Ubuntu 12.04 VMs: 192.168.1.80 - pool, pgppool2 installed and accessible 192.168.1.81 - db1 master 192.168.1.82 - db2 slave 192.168.1.83 - db3 slave I have pgpool-II version 3.1.1 and my database servers are running PostgreSQL 9.1. I have my app's db connection pointed to 192.168.1.80: and it works fine. The problem is when I use Apache ab to throw some load at it, none of SELECT queries appear to be balanced. All the load goes to my db1 master. Also, very concerning is the load on the pool server itself, it is really high compared to db1, maybe an average of 8-10 times higher. Meanwhile my db2 and db3 servers have a load of nearly zero, they appear to only be replicating from db1, which isn't very load intensive for my tests with ab. ab -n 300 -c 4 -C 'sessionid=80a5fd3b6bb59051515e734326735f80' http://192.168.1.17/contacts/ That drives the load on my pool server up to about 2.3. Load on db1 is about 0.4 and load on db2 and db3 is nearly zero. Can someone take a look at my pgpool.conf and see if what I'm doing wrong? http://pastebin.com/raw.php?i=wzBc0aSp Nothing is jumping out at me as blatantly wrong, although it seems kinda weird that each of your database servers is listening on a different port #. I'm starting to think maybe it has something to do with Django wrapping every request in a transaction by default, but when the transaction only has SELECTs, shouldn't that be load balanced just fine? Makes my stomach hurt to think I may have to turn off auto-commit and manually commit transactions all throughout my code :( Still hoping it's a pgpool setup issue, since it's my first time setting it up and all. I've never done anything with Django, but this seems like a good possibility that the transactions are causing pgpool to get confused and assume that every query requires write access. What might be more useful is for you to post your actual pgpool log somewhere, as that might contain a clue of what is going wrong. -- 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] pgpool2 load balancing not working
Sorry if this is the wrong list, but I've been stuck for a couple days now. I tried pgpool-general but that list appears to not like me. I'm not getting any posts and my post hasn't shown up in the archives. I am the admin of the list. Please contact me directly if you need help on this. I have a Python/Django app that will require database load balancing at some point in the near future. In the meantime I'm trying to learn to implement pgpool on a local virtual machine setup. I have 4 Ubuntu 12.04 VMs: 192.168.1.80 - pool, pgppool2 installed and accessible 192.168.1.81 - db1 master 192.168.1.82 - db2 slave 192.168.1.83 - db3 slave I have pgpool-II version 3.1.1 and my database servers are running PostgreSQL 9.1. I have my app's db connection pointed to 192.168.1.80: and it works fine. The problem is when I use Apache ab to throw some load at it, none of SELECT queries appear to be balanced. All the load goes to my db1 master. Also, very concerning is the load on the pool server itself, it is really high compared to db1, maybe an average of 8-10 times higher. Meanwhile my db2 and db3 servers have a load of nearly zero, they appear to only be replicating from db1, which isn't very load intensive for my tests with ab. ab -n 300 -c 4 -C 'sessionid=80a5fd3b6bb59051515e734326735f80' http://192.168.1.17/contacts/ That drives the load on my pool server up to about 2.3. Load on db1 is about 0.4 and load on db2 and db3 is nearly zero. Can someone take a look at my pgpool.conf and see if what I'm doing wrong? http://pastebin.com/raw.php?i=wzBc0aSp I'm starting to think maybe it has something to do with Django wrapping every request in a transaction by default, but when the transaction only has SELECTs, shouldn't that be load balanced just fine? Makes my stomach hurt to think I may have to turn off auto-commit and manually commit transactions all throughout my code :( Still hoping it's a pgpool setup issue, since it's my first time setting it up and all. Yes, your guess is correct. In replication mode, the SELECTs should not be in an explicit transaction if you want them load balanced (if you run pgpool-II in streaming replication mode, the restriction is not applied). Is it possible to turn off auto-start-transaction of Django? Pgpool-II already wraps every write SQL statements in a trasaction. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] recasting to timestamp from varchar
On Sat, Jan 5, 2013 at 4:28 AM, Kirk Wythers wythe...@umn.edu wrote: I am trying to re-cast a column as a timestamp ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp; ERROR: column time2 cannot be cast to type timestamp without time zone The column time2 is currently a varchar. I actually do not want to mess with time zones, but it seems that postgres will not let me do this. What exactly do you mean about your time zones? Are all your times in UTC? If they're all in local time, then you'll want to record time zones, if only to prevent confusion around DST. It's really much MUCH safer to incorporate tz. ChrisA -- 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 run as process in windows
On Wednesday, January 2, 2013, John R Pierce wrote: On 1/2/2013 7:45 PM, Craig Ringer wrote: You really need to put a connection pool in place to limit the number of concurrent workers. Look at PgBouncer or PgPool-II. As far as I know neither of them runs on Windows; you might want to think about a Linux box as a front-end. 2nd and 3rd the emotion. of course, a pooler only works right if the client applications are modified to open a connection, do a transaction, and close the connection. if the clients continue to hold idle connections, the pooler won't do anything useful for you. If you can get away with pooling at the transaction level rather than the session level, then you should still get a benefit even if the connections are persistent. (If each of the 500 connections is as connected as a different PG role, you wouldn't get a benefit from transaction pooling, but in that case you probably wouldn't get a benefit from session pooling, either.) Cheers, Jeff
Re: [GENERAL] recasting to timestamp from varchar
On 1/4/2013 9:28 AM, Kirk Wythers wrote: ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp; ERROR: column time2 cannot be cast to type timestamp without time zone you need to give it some hints as to how to do that casting. ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp USING to_timestamp(time2, '-MM-DD HH24-MI-SS'); I would use timezone with time zone as the type here. Effectively, this type converts all input times to UTC from either the specified or client's default TZ, and converts all time output to either the specified or client's TZ.it behaves properly, while 'timestamp' without timezone doesn't.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
