[GENERAL] Foreign data wrappers and indexes on remote side
Hi. Is there any way to take use of indexes on foreign tables? Currently (at least with tds_fdw, that I was testing) the planner just does a dumb full sequential scan in all cases. That is SELECT drink FROM foreignbar; -- takes as much time as SELECT drink FROM foreignbar where drink_key = 3210; Thanks! -- 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] Foreign data wrappers and indexes on remote side
Is WHERE clause push-down implemented in any known fdw? Thank you. On Fri, Jun 26, 2015 at 6:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: =?UTF-8?Q?Filip_Rembia=C5=82kowski?= filip.rembialkow...@gmail.com writes: Is there any way to take use of indexes on foreign tables? Currently (at least with tds_fdw, that I was testing) the planner just does a dumb full sequential scan in all cases. That would be something to discuss with the author of tds_fdw. It's mostly on the head of each individual FDW how smart plans for it will be. 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] SQL Server access from PostgreSQL
Hi. I will be happy to hear your opinion which one is better - odbc_fdw or tds_fdw? In terms of performance / stability / convenience. (Pg on OpenSuse, MS SQL on Win2008 ) Thanks! -- 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] SQL Server access from PostgreSQL
Thank you Geoff. Actually I have a problem - maybe you can point me in the right direction? CREATE EXTENSION tds_fdw; CREATE EXTENSION CREATE SERVER ms FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'ms'); CREATE SERVER CREATE USER MAPPING FOR postgres SERVER ms OPTIONS (username 'bzzt', password 'blurp'); CREATE USER MAPPING CREATE FOREIGN TABLE test ( id integer ) SERVER ms OPTIONS ( database 'MyApp', query 'select 1' ); CREATE FOREIGN TABLE SELECT * FROM test; NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'master'., Server: ms, Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: ms, Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 40508, Msg state: 1, Msg: USE statement is not supported to switch between databases. Use a new connection to connect to a different database., Server: ms, Process: , Line: 1, Level: 16 ERROR: DB-Library error: DB #: 40508, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: (null), Level: 16 Thanks, Filip On Mon, May 18, 2015 at 6:23 PM, Geoff Montee geoff.mon...@gmail.com wrote: Hi Filip, On Mon, May 18, 2015 at 7:52 AM, Filip Rembiałkowski filip.rembialkow...@gmail.com wrote: Hi. I will be happy to hear your opinion which one is better - odbc_fdw or tds_fdw? In terms of performance / stability / convenience. (Pg on OpenSuse, MS SQL on Win2008 ) Thanks! I'm the developer of tds_fdw. I originally developed tds_fdw because I wasn't able to get odbc_fdw working with FreeTDS. If you try out odbc_fdw, hopefully you'll have better luck than I did! If you decide to try out tds_fdw and you have any problems or run into any bugs, feel free to ask for help on the GitHub page: https://github.com/GeoffMontee/tds_fdw Good luck! Thanks, Geoff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0 hot standby, consistent recovery state question
Hi. I have Pg 9.0 wal shipping hot standby secondary server. The primary is under constant stream of updates (avg 20 TXID/s). There are many lengthy COPY FROM operations in the primary. After every restart of secondary postgres I observe that it takes a fair amount of time (sometimes few minutes, sometimes much more) to replay new WAL logs achieve consistent state and start serving R/O queries. Even when R/O queries were served directly before the restart. Why does postgres take so longto reach consistent state? Can I ever try to impose a time limit on this? How? What prevents postgres from using last consistent restart point from before the cluster restart? This way I would not have to wait so long after restart to serve R/O traffic. Is this issue any different in 9.2? Is this issue mitigated in any way if I switch on streaming replication? thanks for any answers suggestions. Filip -- 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] Why does slony use a cursor? Anyone know?
On Tue, Mar 5, 2013 at 3:51 PM, Shaun Thomas stho...@optionshouse.comwrote: Hey everyone, Frankly, I'm shocked at what I just found. We did a delete last night of a few million rows, and come back this morning to find that slony is 9-hours behind. After some investigation, it became apparent that slony opens up a cursor and orders it by the log_actionseq column. Then it fetches 500 rows, and closes the cursor. So it's fetching several million rows into a cursor, to fetch 500, and then throw the rest away. Why do you assume that opening a cursor with ORDER BY is equivalent to fetching all rows? It is not. Just curious what made you think so, thanks
Re: [GENERAL] update performance of degenerate index
Just some questions, which might be helpful. What size is this index? What is underlying table size? Is ANALYZE running regularly (autovacuum or manual)? What are stats for exported_when column (pg_stats)? Did you look at pg_locks during this lengthy update? Do you have many concurrent statements which involve on this table? Did you cross out CPU and I/O contention? On Mon, Jan 28, 2013 at 2:15 PM, Scott Ribe scott_r...@elevated-dev.comwrote: I'm seeing occasional simple-looking updates take way longer than I think they should, and if my theory about it is correct, it's not actually a problem. Consider this index, intended to provide extremely quick access to a small number of items from a much larger table: create index not_exported on exports(id) where exported_when is null My guess is that if instead of a very small number of items, there are 1000s or 10s of 1000s of items, and a process is updating them one at a time, then occasionally there will be an expensive update of that index that involves touching writing a lot of pages? If that's what's happening, great. (The processing is normally triggered by notify, and happens much faster than the rate at which these come in, so the number of items in that index should be 0 most of the time, occasionally 1 for a second, and possibly but rarely 2 or 3 for a second. The current situation of lots of entries in it has to do with 1-time processing of legacy data.) If that can't be what's happening, then I would want to investigate further why an update of a smallish row with 3 small indexes sometimes takes 600ms. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] nonexistent user in pg_class.relacl messing pg_dump output
Hi all, I observed this strange data error: In pg_dump output, I found such line, which is causing error upon restore: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE addresshistory TO 158755274; It appears that pg_class.relacl column has this literally typed in: dev=# select count(*) from pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace where c.relkind='r' and n.nspname='userdata' and array_to_string(c.relacl,',') ~ '158755274=arwd/'; count --- 5 (1 row) If I try to clean things up by hand, with REVOKE - errors dev=# revoke all on userdata.addresshistory from 158755274; ERROR: role 158755274 does not exist Basically, my questions are 1) was there any bug in 9.0 that could cause this? 2) can you get such mess by playing with system catalogs? Version tag: PostgreSQL 9.0.10 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.5.1 20101208 [gcc-4_5-branch revision 167585], 64-bit Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question on foreign key lock
Hello.
Why adding FK creates AccessExclusiveLock on referenced tabble?
{{{
CREATE TABLE A ( id integer, idb integer );
INSERT INTO A (id,idb) SELECT x, x%4 FROM generate_series(1,100) x;
CREATE TABLE B ( id int primary key );
INSERT INTO B VALUES (0),(1),(2),(3);
BEGIN;
ALTER TABLE A ADD CONSTRAINT a_idb_fkey FOREIGN KEY (idb) REFERENCES b;
SELECT * FROM pg_locks l, pg_class c WHERE l.pid = pg_backend_pid() AND
l.locktype='relation' AND l.mode ilike '%exclusive%' AND l.relation=c.oid;
ROLLBACK;
}}}
Last SELECT is showing AccessExclusive on B.
Why not Exclusive?
Thanks,
Filip
--
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] Improve MMO Game Performance
On Sat, Oct 13, 2012 at 1:52 PM, Arvind Singh arvin...@hotmail.com wrote: To understand my query, please consider the following scenario we store game progress in a postgres table. A tournament starts with four players and following activity Each player starts with 100hitpoints player 1 makes a strike (we refer to a chart to convert blows to hitpoints with random-range %) player 2 has 92HP, and returns a light blow, so player1 has 98hp The above two round will now be in Game Progress Table, as ROW Player1HP Player2HP Strikefrom StrikeTo ReturnStrikeHP Round TimeStamp StrikeMethod 1 100 100 000 0 2 9892P1P2 2 1 There is a tremendous flow of sql queries, There are average/minimum 100 tournaments online per 12 minutes or 500 players / hour In Game Progress table, We are storing each player move a 12 round tourament of 4 player there can be 48 records plus around same number for spells or special items a total of 96 per tourament or 48000 record inserts per hour (500 players/hour) that's below 15 insert/s ... not something to worry about, on recent hardware. Are there any particular settings or methods available to improve Just insert_table operations - avoid too many unused indexes - keep your model normalized - keep pg_xlog on separate device - follow tuning advices from wiki http://wiki.postgresql.org/wiki/Performance_Optimization thanks arvind -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] avoiding CONTEXT messages
Hi, I am using PostgreSQL 9.0 and I want to suppress CONTEXT part of error message for selected user defined Pl/PgSQL functions. Is this possible? http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html#GUC-LOG-ERROR-VERBOSITYsuggests it is... My function is running on superuser account. I tried log_error_verbosity but it seems to not affect client messages. create or replace function test() returns void language plpgsql as 'begin drop table if exists daddyiamnothere; end' ; select test(); set log_error_verbosity to terse; select test(); Thanks
Re: [GENERAL] avoiding CONTEXT messages
On Fri, May 11, 2012 at 9:41 AM, hubert depesz lubaczewski dep...@depesz.com wrote: On Fri, May 11, 2012 at 09:09:46AM -0500, Filip Rembiałkowski wrote: I am using PostgreSQL 9.0 and I want to suppress CONTEXT part of error message for selected user defined Pl/PgSQL functions. Is this possible? it's up to application to hide it. for example - in psql, you do it by setting: \set VERBOSITY terse oh, great, this one helped (we run psql from bash scripts) - thanks.
Re: [GENERAL] empty role names in pg_dumpall output
On Wed, Apr 25, 2012 at 8:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: =?UTF-8?Q?Filip_Rembia=C5=82kowski?= filip.rembialkow...@gmail.com writes: PostgreSQL 9.0.4 I have this in pg_dumpall -g output (non-empty role names changed): GRANT TO a GRANTED BY postgres; GRANT TO b GRANTED BY c; GRANT TO b GRANTED BY c; GRANT TO b GRANTED BY c; GRANT TO b GRANTED BY c; GRANT TO GRANTED BY c; GRANT TO GRANTED BY postgres; GRANT TO GRANTED BY postgres; Hmm. A look at the code in pg_dumpall suggests that the problem is unmatched entries in pg_auth_members, ie this query: SELECT ur.rolname AS roleid FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid is returning some null results. Yes that is the case: SELECT ur.rolname AS roleid, member, grantor FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid WHERE ur.oid IS NULL; roleid | member | grantor ++- NULL | 21468 | 19553 NULL | 21468 | 19553 NULL | 18332 | 19553 NULL | 21468 | 19553 NULL | 18332 | 10 NULL | 20615 | 10 NULL | 18332 | 10 NULL | 21468 | 19553 (8 rows) You might look into that catalog and see if you can figure out what happened. Could it be (theoretically) caused by human-made insertions into pg_auth_members? Maybe you remember some bug which could have caused this in the past? Thanks alot for help, Filip -- 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] empty role names in pg_dumpall output
On Fri, Apr 27, 2012 at 10:23 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ah-ha. How about the member and grantor OIDs in those rows --- do they correspond to still-existing roles? (I believe 10 would be the bootstrap superuser, so that should certainly still exist, but those other numbers are for user-made roles.) The grantors still exists, the members - some yes, some not. Could it be (theoretically) caused by human-made insertions into pg_auth_members? Well, perhaps ... are you in the habit of hacking that catalog directly? No, I dont have this bad habit, but the database is several years old, and I can't rule it out. I wonder if this case can be taken as catalog corruption, and maybe postgres should guard users against it. When you delete a role, its memberships are deleted. Maybe there should be a NOT NULL FK constraint on member and grantor? I'm not that much into pg_catalog, maybe there is a good reason for NOT having it. Anyway, on my side the problem is closed, we just delete them and the pg_dumpall output is fixed. Thank you -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] empty role names in pg_dumpall output
Hi, PostgreSQL 9.0.4 I have this in pg_dumpall -g output (non-empty role names changed): GRANT TO a GRANTED BY postgres; GRANT TO b GRANTED BY c; GRANT TO b GRANTED BY c; GRANT TO b GRANTED BY c; GRANT TO b GRANTED BY c; GRANT TO GRANTED BY c; GRANT TO GRANTED BY postgres; GRANT TO GRANTED BY postgres; There is no CREATE ROLE ... in the output. select * from pg_authid where length(rolname)1 -- returns no rows I would like to get rid of these roles, but how? I appreciate any hints / ideas. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DROP and re-CREATE a table, and ERROR: could not open relation with OID xyz
Hi all, I have a program which replaces (DROP + CREATE) some frequently-used table. Sometimes, just after the table is replaced, I get ERROR: could not open relation with OID xyz from concurrent clients accessing the table. One could say this behaviour breaks transaction isolation... Does it? I understand this is caused by system catalogs being cached by postgres backend (which is normally a good thing). Am I right on it? Is there any way to force a backend to forget table OIDs (DISCARD does not help)? I know it will help if I switch to DELETE and INSERT pattern instead of DROP and CREATE pattern. But this is some legacy program and we do not want to change it unless absolutely needed. I will be grateful for any suggestions how to get rid of this problem. The goal is to rotate the table transparently for other clients. Here is the minimal test case that I reduced the problem to: A. This is the client (multiple clients will run this SELECT in parallel). $ echo SELECT num FROM x; pgbench.minimal.sql B. This is the rotator. $ cat rotate.x.sql EOF BEGIN; DROP TABLE x; CREATE TABLE x ( num integer ); COMMIT; EOF C. create empty database and table x. $ createdb dev $ psql dev -c CREATE TABLE x ( num integer ) D. Start 30 clients. $ pgbench -c 30 dev -T 60 -n -f pgbench.minimal.sql E. (in other terminal) Run the rotator. $ psql dev -f rotate.x.sql F. observe what happens to the clients (just after the rotator COMMITs). Client 4 aborted in state 1: ERROR: could not open relation with OID 170429513 LINE 1: SELECT num FROM x; ^ Client 0 aborted in state 1: ERROR: could not open relation with OID 170429513 LINE 1: SELECT num FROM x; ^ (...and so on - every client fails) What's interesting - even if the clients work in new connection for each transaction mode (add -C option to pgbench), same error occurs. Not always, and not for every client, but it does. Thanks, Filip -- 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] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Thu, Mar 15, 2012 at 6:43 AM, Aleksey Tsalolikhin
atsaloli.t...@gmail.com wrote:
Is there any way to consolidate the pages on the slave without taking
replication offline?
maybe CLUSTER?
filip@dev= create table foobar (id serial primary key, load text);
CREATE TABLE
filip@dev= insert into foobar(load)select md5(random()::text) from
generate_series(1,10);
INSERT 0 10
filip@dev= delete from foobar where id%43; -- delete 75% of the table
DELETE 75000
filip@dev= select pg_relation_size('foobar');
pg_relation_size
--
6832128
(1 row)
filip@dev= CLUSTER foobar using foobar_pkey;
CLUSTER
filip@dev= select pg_relation_size('foobar');
pg_relation_size
--
1712128
(1 row)
Of course events destined to this table will be queued by Slony while the
table is locked.
Filip
Re: [GENERAL] Correct way for locking a row for long time without blocking another transactions (=nowait)?
On Wed, Feb 29, 2012 at 8:18 AM, Durumdara durumd...@gmail.com wrote: 2012/2/28 Filip Rembiałkowski plk.zu...@gmail.com: A way to force error when any statement takes more than 200 msec: SET statement_timeout TO '200ms'; As I see that is not needed here. Only for normal updates. And how I can reset statement_timeout after this command to default value? SET statement_timeout TO DEFAULT; The waiting that you observed is normal - there is no way in PostgreSQL to force _other_ transactions into NOWAIT mode. All transactions that do not want to wait, should use explicit locking with NOWAIT option. If I understand it well, I must follow NOWAIT schema for update to avoid long updates (waiting for error). 1.) I starting everywhere with select for update nowait 2.) Next I make update command 3.) Commit So if I starting with point 2, I got long blocking because of waiting for release row lock? Yes, you _can_ get into long waiting siutuation this way. May the solution is if PGSQL support that: create temporary table tmp_update as select id from atable where ... select * from atable for update nowait where id in (select id from tmp_update) update atable set value = 1 where id in (select id from tmp_update) Is this correct? yes I think so. -- 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] Correct way for locking a row for long time without blocking another transactions (=nowait)?
On Tue, Feb 28, 2012 at 10:26 AM, Durumdara durumd...@gmail.com wrote:
Hi!
In FireBird I can set the transaction to nowait.
When I want to protect a row for long time (showing a dialog, and on
closing I release the row), I need to do this:
trans.StartTransaction();
sql.execute('update thetable set afield = afield where idfield = anyvalue');
This is locking the row with id anyvalue.
If anyone trying to so something with this row (update, delete) from
another transaction, the FireBird generates an error to show: the row
is locked.
On the dialog closing I simply do commit or rollback what is
eliminates the lock on row.
I search for same mechanism in PG...
But: as I see the Lock Table (where I can set nowait) is for only
short transactions, because it is not define the row, it is holding
the table fully.
Another way is when I starting a transaction and update a row, and
waiting, but then all other transactions are trying to update this row
are waiting for me... (they are blocked!).
I want to protect the row, but other transactions mustn't blocked on
this, they rather generate an error after 200 msec (for example), when
they are saw the row locked.
Maybe the solution is the usage of advisory locks, but advisory locks
are don't preventing the changes on the real record, if a procedure or
sql statement don't checking this adv lock existance, it is is simply
overwrite my data...
Or we must use beforeupdate and beforedelete trigger what first
everytime checking the existence of advisory lock by ID?
Just some loose comments.
http://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-ROWS
A way to explicitly lock given row without updating it:
SELECT whatever FROM thetable WHERE id=123 FOR UPDATE NOWAIT;
A way to force error when any statement takes more than 200 msec:
SET statement_timeout TO '200ms';
The waiting that you observed is normal - there is no way in
PostgreSQL to force _other_ transactions into NOWAIT mode. All
transactions that do not want to wait, should use explicit locking
with NOWAIT option.
Naturally, you wil have to catch all kinds of locking /timeout errors
in application code (or in a stored procedure).
HTH,
Filip
--
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] Let-bindings in SQL statements
On Thu, Jan 26, 2012 at 3:37 PM, Jon Smark jon.sm...@yahoo.com wrote: Hi, Is it possible to do the equivalent of let-bindings in a pure SQL function? I have a SELECT that invokes now multiple times. It would be nicer to do it only once and reuse the value. Something like this: LET right_now = SELECT now () IN SELECT * FROM my_table WHERE right_now = start AND ... In PL/pgSQL this is easy, but I wonder about SQL... WITH param AS ( select now() as p_start, somefunc() as p_something ) SELECT * FROM param,my_table WHERE right_now = param.p_start AND ...
Re: [GENERAL] ESET NOD32 Antivirus interference with PostgreSQL
On Sat, Jan 21, 2012 at 1:45 AM, Bruce Duncan bdun...@visualmining.comwrote: Just wanted to give a heads up to anyone who might be having a similar problem. We had an installation on a customer machine that had the AV product ESET NOD32 installed. We quickly started having problems when there were two or more concurrent queries against the same tables in PostgreSQL v8.2.21 (over multiple connections via JDBC). Specifically we were getting errors like: ERROR: could not open relation 1663/27267/27472: Permission denied We checked the file permissions (which checked out), we used sysinternals tools to monitor the file system to make sure the file permsisions werent being changed by the backup software running on the system (they weren't), and finally uninstalled the ESET NOD32 AV software. We have been running without issue now that the AV software has been uninstalled. The filters they use are apparently not very happy with the multi-process, highly-concurrent nature of PostgeSQL. Thought this might be of help to anyone else out there who comes across this AV software during deployment and starts encountering strange behavior. Short: see top-rated answer here: http://serverfault.com/questions/329990/should-we-run-anti-virus-software-on-our-dedicated-sql-server Long and serious: Resident AV monitors register special hook somewhere near the windows kernel, which intercepts all read/write requests and pipes the data through AV scanner, delaying I/O until they think it's clean. During that delay, you may get such error. I've seen it several times (with and without postgres). It happens only with concurrent access (the process that initiated the I/O does not get error - only delay) For most office/home applications it's OK - the delay is small enough and most I/O is via single process/thread. But not for DBMS. So, when running PostgreSQL on a Windows box together with resident on-access AV software, it's rather a must to take following actions (any combination of following): * uninstall the AV software completely * disable the realtime monitor completely (leave on-demand scanning, it does not hurt) * use an exclusion mechanism (most AV have this) to ignore I/O actions on postgres data directory * use an exclusion mechanism (most AV have this) to ignore I/O actions taken postgres binaries cheers, Filip
Re: [GENERAL] string = any()
W dniu 10 stycznia 2012 22:52 użytkownik David Johnston
pol...@yahoo.com napisał:
If you are going to supply one parameter per possible value anyway skip the
whole ANY and ARRAY and just say 'bob' IN ($1, $2 [,...]) ;
true :-)
?php
$names = array( joe, bob );
$placeholders = implode( ,, array_map( function($x){return ?}, $names ) );
$sql = select 'bob' IN ($placeholders);
$stmt = $dbh-prepare($sql);
$stmt-execute($names);
?
PHP from several years cries for postgres array support in PDO. And
personally I would rather discourage programmers from constructing
delimited array string in code - more complicated than first option,
needs careful character escaping, etc.
select array['a',null,'','tab is ','quote is ','comma is ,'];
array
---
{a,NULL,,tab is ,quote is \,comma is ,}
--
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] string = any()
maybe try to use ARRAY constructor instead?
http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
2012/1/10 Andy Colson a...@squeakycode.net:
Hi all.
I am writing PHP where it prepares a statement like:
$sql = 'select * from aTable where id = any($1)';
then in php I create a string:
$args = {1,2,3};
And run it:
$q = pg_query_params($db, $sql, $args);
This is not actual code, just a sample. And it works great for integers. I
cannot get it to work with strings.
Just running this in psql does not work either:
select 'bob' = any( '{''joe'', ''bob'' }' )
But this does:
select 'bob' = any( array['joe', 'bob'] )
But I can't seem to prepare and execute:
$sql = select 'bob' = any( $1 );
$args = array['joe', 'bob'];
$q = pg_query_params($db, $sql, $args);
Running on 9.0.4 on Slackware 64.
Any hits would be appreciated.
-Andy
--
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] string = any()
W dniu 10 stycznia 2012 16:17 użytkownik Andy Colson
a...@squeakycode.net napisał:
2012/1/10 Andy Colsona...@squeakycode.net:
I am writing PHP where it prepares a statement like:
$sql = 'select * from aTable where id = any($1)';
then in php I create a string:
$args = {1,2,3};
And run it:
$q = pg_query_params($db, $sql, $args);
On 1/10/2012 9:11 AM, Filip Rembiałkowski wrote:
maybe try to use ARRAY constructor instead?
http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
As I mentioned, I cannot get it to work:
clayia=# prepare x as select 'bob' = any($1);
PREPARE
Time: 0.665 ms
clayia=# execute x( 'array[''joe'', ''bob'']' );
ERROR: array value must start with { or dimension information
LINE 1: execute x( 'array[''joe'', ''bob'']' );
no, I meant array constructor with reserved word ARRAY:
prepare x as select 'bob' = any( ARRAY[$2,$3] );
execute x( 'joe', 'bob' );
and in PHP:
?php
$names = array( joe, bob );
$placeholders = implode( ,, array_map( function($x){return ?},
$names ) ); #I love Perl
$sql = select 'bob' = any( ARRAY[$placeholders] );
$stmt = $dbh-prepare($sql);
$stmt-execute($names);
?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] please help understand freeing shared buffers
Among following queries, only THREE runs fast enough for me.
I can't understand the logic behind this.
I once thought that shared buffers replacement is independent from
whether the backend which used it is still alive. But is it true?
Seems like shared buffers are returned to the pool or freed just
after client disconnects?
$ cat test.sh
sql=explain (analyze,buffers) select getlocationid_faster2('New
York','10003','NY','US',40.73,-73.99);
psql=psql -X dev postgres
echo ONE
echo $sql | $psql
echo TWO THREE
echo $sql $sql | $psql
echo FOUR
echo $sql | $psql
$ . test.sh
ONE
QUERY PLAN
Result (cost=0.00..0.26 rows=1 width=0) (actual time=3.733..3.735
rows=1 loops=1)
Buffers: shared hit=294
Total runtime: 3.769 ms
(3 rows)
TWO THREE
QUERY PLAN
Result (cost=0.00..0.26 rows=1 width=0) (actual time=3.717..3.719
rows=1 loops=1)
Buffers: shared hit=294
Total runtime: 3.754 ms
(3 rows)
QUERY PLAN
Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.521..0.523
rows=1 loops=1)
Buffers: shared hit=4
Total runtime: 0.540 ms
(3 rows)
FOUR
QUERY PLAN
Result (cost=0.00..0.26 rows=1 width=0) (actual time=3.740..3.742
rows=1 loops=1)
Buffers: shared hit=294
Total runtime: 3.777 ms
(3 rows)
--
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] please help understand freeing shared buffers
2012/1/6 Tom Lane t...@sss.pgh.pa.us: =?UTF-8?Q?Filip_Rembia=C5=82kowski?= filip.rembialkow...@gmail.com writes: Among following queries, only THREE runs fast enough for me. I can't understand the logic behind this. I'm not sure why you'd expect real answers when you haven't shown us what the query is doing, it is an UDF, encapsulating a single SELECT where a=$1 and b=$2 and c=$3 but my first thought is that the discrepancy comes from additional buffer touches in the first execution of a query in a given backend; which is not exactly surprising because that backend has to load up its system catalog caches. IOW, the excess touches represent accesses to system catalogs not user tables. In general, if you're annoyed by query execution times measured in milliseconds, you'd be best advised not to start a fresh connection for each one. A new connection not only involves a process launch but a fair amount of loading of local caches, and a large part of the latter work happens during the first few queries it processes. thank you, that explains a lot. I misinterpreted the number of buffer hits as true buffer reads. sure, using persistent connections is what I will do (we have pgbouncer here) Filip -- 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] Show parameter in psql does any calculations?
so as long as actual logic is buried in the guts of psql, best you can do in SQL to get human-readable value is SELECT name, setting, unit, case when unit='kB' then pg_size_pretty(setting::int*1024) when unit='8kB' then pg_size_pretty(setting::int*1024*8) else coalesce(setting||' '||unit,setting) end AS setting_human_readable, context FROM pg_settings WHERE category like '%Resource Usage / Memory%' ORDER BY name; Filip 2011/12/27 Raghavendra raghavendra@enterprisedb.com: On Tue, Dec 27, 2011 at 3:16 PM, Guillaume Lelarge guilla...@lelarge.info wrote: On Tue, 2011-12-27 at 14:56 +0530, Raghavendra wrote: Respected, Am in PG 9.1. See below ouputs. *By query:* postgres=# SELECT name, setting, unit,context FROM pg_settings WHERE category like '%Resource Usage / Memory%' ORDER BY name; name | setting | unit | context ---+-+--+ maintenance_work_mem | 16384 | kB | user max_prepared_transactions | 0 | | postmaster max_stack_depth | 2048 | kB | superuser shared_buffers | 4096 | 8kB | postmaster temp_buffers | 1024 | 8kB | user track_activity_query_size | 1024 | | postmaster work_mem | 2048 | kB | user (7 rows) *By session command:* postgres=# show work_mem ; work_mem -- 2MB (1 row) postgres=# show shared_buffers ; shared_buffers 32MB (1 row) By the query, its clear that setting value is multiplied with unit to get the sesssion level command value which is displayed here. However, I am trying to get the query of it which is executed at session-level with SHOW command. I tried psql -E option tooo.. -bash-3.2$ psql -E psql.bin (9.1.0) Type help for help. postgres=# show work_mem ; work_mem -- 2MB (1 row) Why the query is not displayed with -E option ? Am I missing something here, please help me in clarifying it. -E works only for meta-commands, ie commands which start with \ Moreover, SHOW is an actual SQL command. So I'm not sure what you're trying to find with the -E command line switch. Hey, Many thanks for your quick response. I am looking how SHOW command value is converted into MB's or GB's ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.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] segfault with plproxy
W dniu 20 grudnia 2011 15:36 użytkownik Marko Kreen mark...@gmail.com napisał: Is schema a part of function signature? Yes. Thanks again, that explains everything. In the meantime, depesz has a solution basing on application_name, not on username+schema as I tried. http://www.depesz.com/index.php/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/ - many shards within the same database. -- 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] segfault with plproxy
W dniu 19 grudnia 2011 10:39 użytkownik Marko Kreen mark...@gmail.com napisał:
On Sat, Dec 17, 2011 at 10:25:40PM +0100, Filip Rembiałkowski wrote:
Following scrip causes segmentation fault. Any ideas why / how to diagnose?
create table part0.users( check(id%2=0) ) inherits (public.users);
create table part1.users( check(id%2=1) ) inherits (public.users);
create or replace function public.list_users(condition text)
select * from public.list_users('%xyz%'); -- crash with segfault
It seems you are making plproxy call public.list_users() recursively.
Postgres probably OOM-s somewhere then.
Either move plproxy function to some other db, or use
TARGET/SELECT to pick different target function.
Thanks Marko,
So is this single-database, schemas mimic nodes setup possible to
achieve at all?
My intention was:
#1. client calls func()
#2. plproxy calls func() on part0. part0 is defined as 'user=part0' so
it directs to part0.func() thanks to current_schema setting.
#3. plproxy calls func() on part1 (paralell to #2). logic same as #2.
#4. plproxy combines result and sends it to client.
Is schema a part of function signature?
regards,
Filip
--
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] segfault with plproxy
W dniu 19 grudnia 2011 10:39 użytkownik Marko Kreen mark...@gmail.com napisał:
It seems you are making plproxy call public.list_users() recursively.
Postgres probably OOM-s somewhere then.
I have log_statement='all' and the function is called only once:
2011-12-19 13:15:11 CET 20416 [local] testdb filip LOG: statement:
select * from list_users('%xyz%');
2011-12-19 13:15:11 CET 20400LOG: server process (PID 20416) was
terminated by signal 11: Segmentation fault
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] segfault with plproxy
Hello,
I have a problem with PL/Proxy
(sorry for not posting to plproxy-users, but I have some problem
subscribing there).
I try to use it to achieve single node paralellism
- as MattK nicely put it on
http://dba.stackexchange.com/questions/9097/single-node-parallelism-with-pl-proxy
My setup is:
-- Ubuntu 10.04
-- PostgreSQL 9.1.2 + PL/Proxy 2.3, both compiled from source and
installed to $HOME/pg91/
-- Data directory in $HOME/pgdata91/
-- config all default except port=5910 plus logging
Following scrip causes segmentation fault. Any ideas why / how to diagnose?
drop database if exists testdb;
create database testdb;
drop user if exists part0;
create user part0;
drop user if exists part1;
create user part1;
\c testdb
-- master table
create table public.users(id serial primary key, name text not null);
-- part0
create schema authorization part0;
create table part0.users( check(id%2=0) ) inherits (public.users);
create or replace function part0.list_users(condition text)
returns table(id int,name text) language sql as $$
select id,name from part0.users where name like $1;
$$;
grant all on all tables in schema part0 to part0;
grant all on all functions in schema part0 to part0;
-- part1 (identical to part0)
create schema authorization part1;
create table part1.users( check(id%2=1) ) inherits (public.users);
create or replace function part1.list_users(condition text)
returns table(id int,name text) language sql as $$
select id,name from part1.users where name like $1;
$$;
grant all on all tables in schema part1 to part1;
grant all on all functions in schema part1 to part1;
\i /home/filip/pg91/share/postgresql/contrib/plproxy.sql
--router
CREATE SERVER testplproxy FOREIGN DATA WRAPPER plproxy OPTIONS (
connection_lifetime '1800',
p0 'dbname=testdb host=127.0.0.1 port=5901 user=part0',
p1 'dbname=testdb host=127.0.0.1 port=5901 user=part1'
);
CREATE USER MAPPING FOR PUBLIC SERVER testplproxy;
GRANT USAGE ON FOREIGN SERVER testplproxy TO public;
-- router
create or replace function public.list_users(condition text)
returns table(id int,name text) language plproxy as $$
cluster 'testplproxy';
run on all;
$$;
select * from public.list_users('%xyz%'); -- crash with segfault
--
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] What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?
Do not fall too easilly into hstore :-) while it sometimes good and extremely easy to setup, it has some drawbacks - querying and searching has some limitations (keys/values not easily indexable, horrible syntax) - storage not optimised (one hstore field = several dozens of boolean columns) - only text values, no data type validation I'd recommend option (4) - normalize! FR 2011/12/5 Mike Christensen m...@kitchenpc.com I have a database full of recipes, one recipe per row. I need to store a bunch of arbitrary flags for each recipe to mark various properties such as Gluton-Free, No meat, No Red Meat, No Pork, No Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium and Low Carb. Users need to be able to search for recipes that contain one or more of those flags by checking checkboxes in the UI. I'm searching for the best way to store these properties in the Recipes table. I'd use hstore to store them as tags. You can then use hstore's GiST index support to get quick lookups. 1. Have a separate column for each property and create an index on each of those columns. I may have upwards of about 20 of these properties, so I'm wondering if there's any drawbacks with creating a whole bunch of BOOL columns on a single table. It'll get frustrating as you start adding new categories, and will drive you insane as soon as you want to let the user define their own categories - which you will land up wanting to do in your problem space. I'd avoid it. 2. Use a bitmask for all properties and store the whole thing in one numeric column that contains the appropriate number of bits. Create a separate index on each bit so searches will be fast. Same as above, it'll get annoying to manage when you want user tagging. 3. Create an ENUM with a value for each tag, then create a column that has an ARRAY of that ENUM type. I believe an ANY clause on an array column can use an INDEX, but have never done this. Same again. 4. Create a separate table that has a one-to-many mapping of recipes to tags. Each tag would be a row in this table. The table would contain a link to the recipe, and an ENUM value for which tag is on for that recipe. When querying, I'd have to do a nested SELECT to filter out recipes that didn't contain at least one of these tags. I think this is the more normal way of doing this, but it does make certain queries more complicated - If I want to query for 100 recipes and also display all their tags, I'd have to use an INNER JOIN and consolidate the rows, or use a nested SELECT and aggregate on the fly. That'll get slow. It'll work and is IMO better than all the other options you suggested, but I'd probably favour hstore over it. The hstore module sounds fantastic! I'm curious as to how these columns are serialized back through the driver, such as Npgsql. Do I get the values as strings, such as a comma delimited key/value pair list? Or would I need to do some custom logic to deserialize them? Right now, I'm using Npgsql as a driver, and NHibernate/Castle ActiveRecord as an ORM. Mike -- 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] Limiting number of connections to PostgreSQL per IP (not per DB/user)?
no easy, standard way of doing this in postgres. before we go into workarounds - what's the underlying OS? 2011/11/29 Heiko Wundram modeln...@modelnine.org: Hello! Sorry for that subscribe post I've just sent, that was bad reading on my part (for the subscribe info on the homepage). Anyway, the title says it all: is there any possibility to limit the number of connections that a client can have concurrently with a PostgreSQL-Server with on-board means (where I can't influence which user/database the clients use, rather, the clients mostly all use the same user/database, and I want to make sure that a single client which runs amok doesn't kill connectivity for other clients)? I could surely implement this with a proxy sitting in front of the server, but I'd rather implement this with PostgreSQL directly. I'm using (and need to stick with) PostgreSQL 8.3 because of the frontend software in question. Thanks for any hints! -- --- Heiko. -- 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] Query Optimizer makes a poor choice
2011/11/29 Tyler Hains tha...@profitpointinc.com: # explain analyze select * from cards where card_set_id=2850 order by card_id limit 1; QUERY PLAN - Limit (cost=0.00..105.19 rows=1 width=40) (actual time=6026.947..6026.948 rows=1 loops=1) - Index Scan using cards_pkey on cards (cost=0.00..2904875.38 rows=27616 width=40) (actual time=6026.945..6026.945 rows=1 loops=1) Filter: (card_set_id = 2850) Total runtime: 6026.985 ms do you have autovacum enabled? does the plan change when you push stats target for this column? ALTER TABLE cards ALTER card_set_id SET STATISTICS 500; ANALYZE cards ( card_set_id ); what happens when you do: select * from ( select * from cards where card_set_id=2850 ) order by card_id limit 1 ? -- 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] initdb failure on Windows 2003
what is the output when you run this in command line: ...\path\to\initdb.exe c:\testpgdata ? 2011/11/29 Mike Wylde mwy...@thoughtworks.com: I’m trying to install Postgres 8.4.9.1 on a Windows 2003 SP2 64bit operating system. The download has installed previously (to a windows 7 box) so I know it should be OK but under W2003 the initdb command seems to be failing. It doesn’t return an error message but non of the actions are performed, the data directory remains empty. Any ideas, or extra logging that I can switch on to try and tie this down. I can’t reproduce it on a W2003 box running the 32bit OS and getting hold of another 64 bit box is complicated. The install log looks like: Called CreateDirectory(D:\Mingle\PostgreSQL\8.4\data)... Called CreateDirectory(D:\Mingle\PostgreSQL\8.4)... WScript.Network initialized... Called IsVistaOrNewer()... 'winmgmts' object initialized... Version:5.2 MajorVersion:5 Ensuring we can write to the data directory (using cacls): Executing batch file 'rad38351.bat'... Executing batch file 'rad38351.bat'... Reading: objConfFile is nothing... Writing: objConfFile is nothing... We’ve also tried with 8.3 and 9.1 installers and get exactly the same result. Any help of hints would be most appreciated. Regards, Mike Wylde -- 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] Extending the volume size of the data directory volume
here's what I would do to analyze this first: - vmstat 1 - iostat -k -x 3 - look into system logs, maybe something actually happened there... - look at the process list. find if some of Pg processes are in D state - strace -f -v PID of the hanging writer process 2011/11/29 panam pa...@gmx.net: Hi, as I am importing gigabytes of data and the space on the volume where the data dictionary resides just became to small during that process, I resized it dynamically (it is a LVM volume) according to this procedure: http://www.techrepublic.com/blog/opensource/how-to-use-logical-volume-manager-lvm-to-grow-etx4-file-systems-online/3016 Everything went without any problems and the import continued. Now, it is suddenly stuck (pgAdmin shows it as idle (piped connection)) and there is a good chance (as estimated from the space used) it just started using one of the added LE-Blocks (HDD space that was added to the volume). The db imported so far can be accessed just fine. So from the postmaster architecture, is there something that would explain this behaviour based on the hypothesis that newly added space was used? Any chance to revive the import somehow? Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Extending-the-volume-size-of-the-data-directory-volume-tp5030663p5030663.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query Optimizer makes a poor choice
2011/11/29 Tyler Hains tha...@profitpointinc.com:
I haven't had a chance to experiment with the SET STATISTICS, but that
got me going on something interesting...
Do these statistics look right?
# SELECT attname, n_distinct, most_common_vals, histogram_bounds FROM
pg_stats WHERE tablename = 'cards';
...
card_set_id 905
{5201,3203,3169,5679,5143,5204,5655,4322,5236,4513}
{4,3080,3896,4349,4701,5179,5445,5706,6003,6361,6784}
This looks promising, because n_distinct is low enough that you can
cover almost all values with statistics.
raise the statistics and ANALYZE. should help.
(NOTE NOTE NOTE: assuming that the distribution is even)
...
but one thing we see for sure is that you have not tuned your
PostgreSQL instance :-)
I would recommend pgtune, - pgfoundry.org/projects/pgtune/
it covers most important stuff, *including* default_statistics_target.
Filip
--
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] Limiting number of connections to PostgreSQL per IP (not per DB/user)?
W dniu 29 listopada 2011 23:18 użytkownik Heiko Wundram modeln...@modelnine.org napisał: Okay, that's too bad that there's no standard way for this. The underlying OS is Linux (Gentoo, to be exact), and I'd already thought about setting up some form of iptables firewalling, but there's no real framework for this (i.e., count the number of connected TCP-sockets that originate from a single client) in iptables, only for connection throttling from the same source (which won't cut it, as there are spikes in connection setup where many connections are created almost at once, meaning that hashlimit or recent and the likes are simply not suited to the task at hand. I just need/want to give a hard upper limit on the number of simultaneous connections from a single client as an Anti-DoS-measure - the clients aren't hostile, but their programming is broken...). did you look at connlimit? http://www.netfilter.org/projects/patch-o-matic/pom-external.html#pom-external-connlimit AFAIK, it applies only to ESTABLISHED state, so maybe it suits you. I'm not sure how do you want to allow many connections being created almost at once and limit number of connections from same IP at the same time? anyway, we are going offtopic here... regards Filip -- 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] 9.1 got really fast ;)
2011/10/15 Chris Travers chris.trav...@gmail.com On Sat, Oct 15, 2011 at 1:33 PM, Grzegorz Jaskiewicz gryz...@gmail.comwrote: On 15 Oct 2011, at 21:20, Thomas Kellerer wrote: Total runtime: -2.368 ms this is amazing ;) This is with 9.1.1 on a Windows XP machine Are you saying that Windows XP is the ultimate server OS for high performance PostgreSQL installations? Are there optimizations that this platform can take advantage of, perhaps extending Pg timelines into actual time travel that are not available on other platforms? In some way, time travel is possible - system clock can be adjusted, think: ntpd That triggers another question: Is PostgreSQL internal timing somehow immune to system clock backward step (slew should be safe) issued by ntpd? Can it be fixed so it at least does not return nagative time deltas? For definition of step vs slew, see http://osr507doc.sco.com/en/NetAdminG/ntpC.ntp_terms.html
Re: [GENERAL] Drill-downs and OLAP type data
Mondrian (which is a part of Pentaho BI stack) is an open source OLAP engine with MDX. See http://community.pentaho.com/projects/bi_platform/ 2011/10/12 Anthony Presley anth...@resolution.com Hi there! We have a typical data-warehouse type application, and we'd like to set up a star-schema type data analysis software product (which we'll be programming), on top of PG. The goal is to do fast roll-up, drill-down, and drill-through of objects / tables like locations, inventory items, and sales volume. After a few weeks of searching around, we're running into dead-ends on the front-end, and the back-end. PG doesn't support OLAP / MDX and the GUI tools that do this, for the most part, require MDX / OLAP (SPSS and DB2, MS SQL Analytics, etc...). What's the PG route here? Are there some secrets / tips / tricks / contrib modules for handling this? -- Anthony Presley
Re: [GENERAL] could not create file base/16384/11500: File exists
Hello, You gave much too little information. Please see http://wiki.postgresql.org/wiki/Guide_to_reporting_problems Especially: - server version, platform - vacuum-related settings It *might* be OID wraparaound. 2011/10/10 Harshitha S hershe...@gmail.com Hi, We are the following msg in the postgres logs. could not create file base/16384/11500: File exists Some links indicate that the issue is related to OIDs. Please help me in understanding why the message is appearing and how the issue can be resolved. Regards, Harshitha
Re: [GENERAL] could not create file base/16384/11500: File exists
Is it fresh install of PostgreSQL server? What OS, what installer? Did you run initdb (or pg_ctl initdb), or the installer did it for you? If you want valuable replies you will really need to describe your full story. see http://wiki.postgresql.org/wiki/Guide_to_reporting_problems W dniu 10 października 2011 12:41 użytkownik Harshitha S hershe...@gmail.com napisał: Also, this is the case of first startup of the postgres server. This msg is shown in the startup logs. 2011/10/10 Harshitha S hershe...@gmail.com Hi, The postgres server version is 9.0.4 on the Linux platform. The vaccum - related settings have default values. If it is OID wrap around, how to resolve it? Regards, Harshitha 2011/10/10 Filip Rembiałkowski plk.zu...@gmail.com Hello, You gave much too little information. Please see http://wiki.postgresql.org/wiki/Guide_to_reporting_problems Especially: - server version, platform - vacuum-related settings It *might* be OID wraparaound. 2011/10/10 Harshitha S hershe...@gmail.com Hi, We are the following msg in the postgres logs. could not create file base/16384/11500: File exists Some links indicate that the issue is related to OIDs. Please help me in understanding why the message is appearing and how the issue can be resolved. Regards, Harshitha
Re: [GENERAL] Best PostGIS function for finding the nearest line segment to a given point
2011/10/8 René Fournier renefourn...@gmail.com
Thanks. Based on some further reading, this is what I came up with, in
order to hopefully use the GiST index to greatest benefit:
gc3=# SELECT datasetnam, r_hnumf, r_hnuml, r_stname_c, r_placenam,
ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
AS distance
gc3-# FROM nrn_ab_8_0_roadseg
gc3-# WHERE the_geom SetSRID('BOX3D(-114.1 49.9,-113.9
51.1)'::box3d,4269)
gc3-# ORDER BY distance ASC LIMIT 5;
datasetnam | r_hnumf | r_hnuml | r_stname_c | r_placenam |
distance
+-+-+--++--
Alberta| 407 | 459 | 19 Avenue North-east | Calgary|
5.74515867479735e-05
Alberta|2004 |2004 | 4 Street North-east | Calgary|
0.000663366090673065
Alberta| 0 | 0 | 4 Street North-east | Calgary|
0.000667603774783403
Alberta| 425 | 425 | 18 Avenue North-east | Calgary|
0.000835708003512673
Alberta| 407 | 449 | 20 Avenue North-east | Calgary|
0.000981910679856406
(5 rows)
gc3=# EXPLAIN SELECT datasetnam, r_hnumf, r_hnuml, r_stname_c, r_placenam,
ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
AS distance
gc3-# FROM nrn_ab_8_0_roadseg
gc3-# WHERE the_geom SetSRID('BOX3D(-114.1 49.9,-113.9
51.1)'::box3d,4269)
gc3-# ORDER BY distance ASC LIMIT 5;
QUERY PLAN
--
Limit (cost=33632.15..33632.16 rows=5 width=480)
- Sort (cost=33632.15..33693.00 rows=24341 width=480)
Sort Key:
(st_distance('010120AD10F5BEF1B567835CC06A2E3718EA884940'::geometry,
the_geom))
- Bitmap Heap Scan on nrn_ab_8_0_roadseg (cost=812.99..33227.85
rows=24341 width=480)
Recheck Cond: (the_geom
'010320AD100100050066865CC033F3484066865CC0CD8C49409A795CC0CD8C49409A795CC033F3484066865CC033F34840'::geometry)
- Bitmap Index Scan on nrn_ab_8_0_roadseg_the_geom_gist
(cost=0.00..806.91 rows=24341 width=0)
Index Cond: (the_geom
'010320AD100100050066865CC033F3484066865CC0CD8C49409A795CC0CD8C49409A795CC033F3484066865CC033F34840'::geometry)
(7 rows)
Does this appear optimal to you?
I think it's closer to optimal. The real question is: is this fast enough
for your application?
Can you show EXPLAIN (ANALYZE on,BUFFERS on) result?
Re: [GENERAL] Best PostGIS function for finding the nearest line segment to a given point
2011/10/8 René Fournier renefourn...@gmail.com
Wow, have to say, I love Postgresql and PostGIS. Just awesome.
So I have a table with ~400,000 rows, each representing a road or street
(multi line segment). I want to select the row whose line segment is closest
the a given point. The following query...
gc3=# SELECT r_stname_c, r_placenam,
ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
AS distance FROM nrn_ab_8_0_roadseg ORDER BY distance ASC LIMIT 1;
...works and produces...
r_stname_c | r_placenam | distance
--++--
19 Avenue North-east | Calgary| 5.74515867479735e-05
…but seems a little slow (yes, there is a GIST index on the_geom). Explain
shows:
gc3=# explain SELECT r_stname_c, r_placenam,
ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
AS distance FROM nrn_ab_8_0_roadseg ORDER BY distance asc limit 1;
QUERY PLAN
---
Limit (cost=128520.06..128520.06 rows=1 width=464)
- Sort (cost=128520.06..129493.57 rows=389404 width=464)
Sort Key:
(st_distance('010120AD10F5BEF1B567835CC06A2E3718EA884940'::geometry,
the_geom))
- Seq Scan on nrn_ab_8_0_roadseg (cost=0.00..126573.04
rows=389404 width=464)
(4 rows)
Any suggests how to speed it up? Coming from MySQL, I'm brand-new to
PostGIS (and Postgresql FWIW) and all the awesome spatial functions it has.
I would think that maybe selecting a bounding box of rows, and then finding
the one with the closest distance?
Yes exactly. That's how people do it now, in pre-PostGIS-2.0 era :-)
Make a search by bounding boxes, starting with some arbitraly selected
radius. Increase the radius until you have at least N=1 result found, than
sort these results by ST_Distance and select nearest neighbour.
PostGIS 2.0 solution: see
http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/
Re: [GENERAL] Restoring 2 Tables From All Databases Backup
2011/10/6 Adarsh Sharma adarsh.sha...@orkash.com ** Thanks to all, the problem is solved now. But Still I donot know how to use existing data directory (near about 110GB) in a new Postgres Installation. I ask this in the list yesterday but still no clue on this. Please guide if it is possible. You need to learn more about postgres server mechanics... especially initdb, pg_ctl, starting and stopping server and so on. See http://www.postgresql.org/docs/9.1/static/runtime.html, http://www.postgresql.org/docs/9.1/static/runtime-config-file-locations.html. To use existing data directory in new installation, you can just stop the server, replace data_directory, and start the server. Remember about file permissions - data_directory must be owned by server process owner (postgres) and chmod 700. Filip
Re: [GENERAL] Strange primary key error on insertion
2011/10/6 Rory Campbell-Lange r...@campbell-lange.net
I have a strange issue (postgres 8.4) trying to insert old rows back
into the s_tbls table. A check on the primary key (n_id) between
s_tbl_import and s_tbls yields no matches, yet the index on s_tbls.n_id
yields an error when attempting to insert:
= select n_id from s_tbl_import where n_id IN (
select n_id from s_tbls);
n_id
--
(0 rows)
= insert into s_tbls (select * from s_tbl_import);
ERROR: duplicate key value violates unique constraint s_tbls_pkey
Looks like you had duplicates in s_tbl_import. Try this:
SELECT * FROM s_tbl_import WHERE n_id IN (
SELECT n_id from s_tbl_import group by n_id HAVING count(*)1
);
Table s_tbls
Column |Type |
Modifiers
--+-+
n_id | integer | not null default
nextval('s_tbls_n_id_seq'::regclass)
dt_created | timestamp without time zone | default now()
dt_modified | timestamp without time zone | default now()
t_node | text|
...
Indexes:
s_tbls_pkey PRIMARY KEY, btree (n_id)
Re: [GENERAL] Restoring 2 Tables From All Databases Backup
follow the other thread that you started. there are some suggestions for you. 2011/10/6 Adarsh Sharma adarsh.sha...@orkash.com ** That's the bottleneck I need to solve:- Previous data Os Version :- Postgresql-8.3 and Suse Enterprise Linux New Data OS Version :- Postgresql-8.4 and Ubuntu 10.04 What to do know? Thanks Raymond O'Donnell wrote: On 06/10/2011 11:34, Filip Rembiałkowski wrote: To use existing data directory in new installation, you can just stop the server, replace data_directory, and start the server. Remember about file permissions - data_directory must be owned by server process owner (postgres) and chmod 700. BUT - and this is a big but - the data *must* be from the same Postgres major version, same architecture, etc. This might have been mentioned upthread, but it's worth repeating. Ray.
Re: [GENERAL] gaps/overlaps in a time table : current and previous row question
2011/10/5 thomas veymont thomas.veym...@gmail.com hello, let's say that each rows in a table contains a start time and a end time (timeinterval type), there is no such type ( no result for select * from pg_type where typname ~ 'timeinterval' ). can you show exact table structure (output of psql \d or better, CREATE TABLE command)? but the index are not ordered nor consecutive, e.g : $ select * from T order by starttime index | starttime| endtime -+-+- 3| t1 | t2 1| t3 | t4 18 | t5 | t6 12 | t7 | t8 I want a result that shows time gaps and overlaps in this table, that is : delta -+ t3 - t2 | t5 - t4 | t7 - t6 | how would I do that ? I guess this could be done with window function and lag() function but I don't know exactly how. Any suggestion ? -- assuming that you actually want lag compared to previous starttime - try this: select index, starttime, endtime, starttime - lag(endtime) over(order by starttime asc) as delta from test; PS. this question should probably go to pgslq-sql mailing list more than pgsql-general. also please give more details next time. Thanks.
Re: [GENERAL] Restoring 2 Tables From All Databases Backup
2011/10/5 Adarsh Sharma adarsh.sha...@orkash.com
Dear all,
About 1 month ago, I take a complete databases backup of my Database server
through pg_dumpall command.
Today I need to extract or restore only 2 tables in a database.
Is it possible or I have to restore complete Databases again. Size of
backup is 10 GB in .sql.gz format.
Please let me know how to extract the tables from this 10Gb backup file
since this is a plaintext file, not a custom format backup,
you unfortunately need to extract portions of text using some editor or
program...
for this kind of work I would recommend Perl or Awk.
below is my first shot - thats incomplete (no restoration of
indexes/sequences):
gunzip -cd all.sql.gz | awk '/^CREATE TABLE mytable /,/^$/ { print }; /^COPY
mytable /,/^$/ { print };'
which does print all lines from CREATE TABLE mytable to next empty line, and
all lines from COPY mytable to next empty line.
Re: [GENERAL] How to create database link and synonym in postgresql 9.0
There is no CREATE SYNONYM in PostgreSQL and it's not planned for
implementation.
There is also no direct support for foreign data wrapper. But it's planned
for 9.2.
Nearest that you can get with PostgreSQL 9.0 is cautious use of dblink and
views and rules.
here's a sample script to show what I mean:
create database db1;
create database db2;
\c db2;
create table emp ( id integer primary key, name text);
insert into emp values (1,'Joe'), (2,'Mary');
\c db1
\i /usr/share/postgresql/9.0/contrib/dblink.sql
SELECT dblink_connect( 'db2', 'dbname=db2' );
-- remote SELECT;
CREATE VIEW emp AS SELECT id,name FROM dblink('db2','SELECT id,name FROM
emp') AS emp(id integer, name text);
SELECT * FROM emp ORDER BY name;
-- remote INSERT:
CREATE RULE emp_ins AS ON INSERT TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
'INSERT INTO emp(id,name) VALUES(' || quote_nullable(NEW.id) || ',' ||
quote_nullable(NEW.name) || ')', true );
INSERT INTO emp VALUES(3,'Phil');
-- remote UPDATE:
CREATE RULE emp_upd AS ON UPDATE TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
'UPDATE emp SET id=' || quote_nullable(NEW.id) || ', name=' ||
quote_nullable(NEW.name)
|| ' WHERE id=' || quote_nullable(OLD.id), true );
UPDATE emp SET name = 'Philip' WHERE id = 3;
2011/10/5 tushar nehete tpneh...@gmail.com
Hi,
In one of the migration porject want to access and update some tables from
other database on same postgresql server.
The question is how to access tables from other database on the same
postgres server.
If it is by database link like Oracle then what is the syntax.
And how to create synonym?
Say i have a postgres server on RHEL5 and it has 2 databases db1 and db2.
db2 has table emp which i want to access from db1.
so i create a dblink in oracle named dblnk_emp and access the table in db1
by
select * from emp@dblnk_emp;
so i create synonym for emp@dblnk_emp as emp in db1.
In postgres I can access table from other database by dblink but cannot
update it. Also there is
support for synonym.
Please help..
Re: [GENERAL] Help needed in Search
Siva,
in addition to what others said, please note that underscore matches any
character. to change it use escape char.
http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-LIKE
2011/9/28 Siva Palanisamy siv...@hcl.com
Hi All,
** **
I am trying to retrieve the contact names based on the keyed search string.
It performs good for the English alphabets and behaves strangely for special
chars such as _,/,\,%
** **
My query in the function is similar to
** **
SELECT contact_name FROM contacts WHERE LOWER(contact_name) LIKE
LOWER('_McDonald%') ORDER BY LOWER(contact_name) ASC LIMIT 1;
** **
It looks like, during searching, it retrieves all the contact names instead
of the desired. The similar bizarre happens for the above mentioned special
chars. I need to support these. How do I educate postgres to consider these
chars? Please guide me.
** **
Thanks and Regards,
Siva.
--
::DISCLAIMER::
---
The contents of this e-mail and any attachment(s) are confidential and
intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect
the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of
this message without the prior written consent of the author of this e-mail
is strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. Before opening any mail and
attachments please check them for viruses and defect.
---
Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
Edson, 1. PostgreSQL IS able to use btree index to execute this query. More generally, it is able to use btree index for all PREFIX search. 2. You will need a special (NOT spatial) index for it CREATE INDEX notafiscal_numeroctc_tpo_idx ON notafiscal (numeroctc text_pattern_ops); ( see http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.htmlfor explanation). Hope this helped. 2011/9/26 Edson Carlos Ericksson Richter rich...@simkorp.com.br Dear experts, ** ** I have the following query: ** ** select * from notafiscal where numeroctc like ‘POA%34345’; ** ** Prefix is normally 3 characters, suffix varyies. ** ** Is Postgresql 9.0.4 able to use an BTREE index on notafiscal.numeroctc to execute this query? ** ** Should I create GIST index or something else to speed up the query? ** ** ** ** Thanks, ** ** *Edson Carlos Ericksson Richter* *SimKorp Infomática Ltda * Fone: (51) 3366-7964 Celular: (51) 8585-0796 [image: Embedded Image] www.simkorp.com.br ** **
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
2011/9/27 Diego Augusto Molina diegoaugustomol...@gmail.com Hi, I had to implement a logging system for some DBs in work. It's generic and implemented using plperl. I've seen tons of mails on the list from newbies asking for something like this using plpgsql, but no specific solution is pointed for them. I think this may discourage some of them. The system is implemented using a separate schema with whatever name you want, and has some really nice features: relevant tables can be rotated to facilitate auditing, each logged action refers to the modified tuple by pk, which you don't even have to (but can) specify when reating the trigger, and some more. Performance is very acceptable (test cases and suggestions are welcome), and never had a problem since the about 8 months it's been working. In the wiki some points are mentioned, but all is _too_ general and for a vanilla pg-8.4 you won't have the hstore facility (note that upgrading is not always a choice). Will it worth pasting the little code in here or that'll be ignored? If you can share it - do it, maybe someone will have use of your work - it's always welcome. Publishing it on the web and linking here instead of pasting - will be even better. Filip
Re: [GENERAL] Quick-and-Dirty Data Entry with LibreOffice3?
2011/9/26 Rich Shepard rshep...@appl-ecosys.com Rather than writing an application right now to enter data into a table I thought of trying LibreOffice as a front end. But, it doesn't seem to work as OO.o did. It does, albeit you will need libreoffice-base which is not always installed by default (not in my Ubuntu). And of course the JDBC driver. 1) Can someone show me how to use LO as a front end to a postgres table? http://www.postgresonline.com/journal/archives/8-Using-OpenOffice-Base-2.3.1-with-PostgreSQL.html cheers, Filip
Re: [GENERAL] pg_dump compress
2011/9/23 Gregg Jaskiewicz gryz...@gmail.com can you pipe things on windows ? Yes you can. It surprised me positively several years ago. http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/redirection.mspx?mfr=true It's a desktop system after all, :-) what a nice dose of condescending irony. You know, they are selling a server OS on top of that. Quite a lot of copies. cheers, Filip
Re: [GENERAL] Help - corruption issue?
Phoenix, how large (in total) is this database)? can you copy (cp -a) the data directory somewhere? I would do this just in case :-) regarding the manual recovery process: 1. you'll have to isolate corrupted table. you can do this by dumping all tables one-by-one (pg_dump -t TABLE) until you get the error. 2. find the record which is corupted... approach like this might work: select count(*) from the_corrupted_table where PK_column = some_value. 3 .you should try to dump the table by chunks - skipping the corrupted row(s) if possible 4. if above method does not work, you can try manually hex-editing (zeroing) some bytes (with postgres shut down) to make dump work again. PS. obligatory note: 8.2.9 Release Date: 2008-06-12; 8.2.21 Release Date: 2011-04-18 seems like you were running almost three years without bugfixes. aside from fixing your current problem, I would first do the upgrade to avoid more corruption. 2011/4/18 Phoenix Kiula phoenix.ki...@gmail.com While doing a PG dump, I seem to have a problem: ERROR: invalid memory alloc request size 4294967293 Upon googling, this seems to be a data corruption issue! ( Came about while doing performance tuning as being discussed on the PG-PERFORMANCE list: http://postgresql.1045698.n5.nabble.com/REINDEX-takes-half-a-day-and-still-not-complete-td4005943.html ) One of the older messages suggests that I do file level backup and restore the data. http://archives.postgresql.org/pgsql-admin/2008-05/msg00191.php How does one do this -- should I copy the data folder? What are the specific steps? I'm on PG 8.2.9, CentOS 5, with 8GB of RAM. The disks are four SATAII disks on RAID 1. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I get the list of index(or other objects) which depends on a function
2011/1/5 flying eagle eaglein...@gmail.com I want to get all the dependencies of a table, I know how to get the index list using sql, but I don't know how to get the list of objects who using a function, for example: CREATE OR REPLACE FUNCTION reverse_last_64(TEXT) RETURNS TEXT AS $$ SELECT array_to_string( ARRAY ( SELECT substring($1, s.i,1) FROM generate_series(length($1), greatest(length($1) - 64 + 1, 1), -1) AS s(i) ), ''); $$ LANGUAGE SQL IMMUTABLE CREATE TABLE Test2(id BIGSERIAL PRIMARY KEY, name varchar(100)) CREATE INDEX idx_test2_name ON test2(reverse_last_64(name) varchar_pattern_ops) drop function reverse_last_64(TEXT) ; will show ERROR: cannot drop function reverse_last_64(text) because other objects depend on it DETAIL: index idx_test2_name depends on function reverse_last_64(text) HINT: Use DROP ... CASCADE to drop the dependent objects too. what I want to get is the list of index(or maybe other function) which using the function using sql. I hope this will help you: select * from pg_depend where refobjid = (select oid from pg_proc where proname='reverse_last_64'); http://www.postgresql.org/docs/9.0/interactive/catalog-pg-depend.html greets, Filip
Re: [GENERAL] WAL Archiving Stopped
W dniu 4 stycznia 2011 14:55 użytkownik Norberto Delle betode...@gmail.comnapisał: Em 3/1/2011 18:39, Filip Rembiałkowski escreveu: archiver process will retry later; it never stops trying, sleep time is just longer. 2011/1/3, Norberto Dellebetode...@gmail.com: Hi all I have a PostgreSQL 9.0.1 instance, with WAL Archiving. Today, after some failed tries to archive a WAL file, it stopped trying to archive the files, but the number of logfiles in the pg_xlog directory keep growing. Any ideas of what is going on? Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi Filip It was taking too long to retry, a matter of hours. I had to restart the service to it start trying to archive the wal files. that's strange; from the source code I would think that it will retry indefinitely. ( http://doxygen.postgresql.org/pgarch_8c-source.html ) I have also tested this with archive_command = /bin/false and it works as expected. archiver does 3 retries, then sleep for 60 seconds hardcoded timeout. 2011-01-04 18:11:41.407 CET [2375]WARNING: transaction log file 00010008 could not be archived: too many failures 2011-01-04 18:12:41.474 CET [2375]LOG: archive command failed with exit code 1 2011-01-04 18:12:41.474 CET [2375]DETAIL: The failed archive command was: /bin/false 2011-01-04 18:12:42.478 CET [2375]LOG: archive command failed with exit code 1 2011-01-04 18:12:42.478 CET [2375]DETAIL: The failed archive command was: /bin/false 2011-01-04 18:12:43.484 CET [2375]LOG: archive command failed with exit code 1 2011-01-04 18:12:43.484 CET [2375]DETAIL: The failed archive command was: /bin/false 2011-01-04 18:12:43.484 CET [2375]WARNING: transaction log file 00010008 could not be archived: too many failures (and so on...) Norberto
Re: [GENERAL] WAL Archiving Stopped
archiver process will retry later; it never stops trying, sleep time is just longer. 2011/1/3, Norberto Delle betode...@gmail.com: Hi all I have a PostgreSQL 9.0.1 instance, with WAL Archiving. Today, after some failed tries to archive a WAL file, it stopped trying to archive the files, but the number of logfiles in the pg_xlog directory keep growing. Any ideas of what is going on? Norberto -- 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] Constraining overlapping date ranges
that's really interesting; looks like a bug if point constructor is really mutable? can you prepare a test case with non-overlapping ranges which does not satisfy exclusion constraint? regarding your last comment about unnecessary f_point function: I tried id WITH = but postgres complained about lack of proper gist opclass 2010/12/22, McGehee, Robert robert.mcge...@geodecapital.com: Filip, The period type + exclusion constraint was exactly the avenue I was taking, and I was also perplexed that period was not defined as immutable. Your solution, to force period() to be immutable, seemed like a reasonable one (though it didn't work, see below). I tried implementing this on my existing table: ALTER TABLE tbl ADD EXCLUDE USING gist (f_point(id) WITH ~=, f_period(start_date, stop_date) WITH ); and the index correctly identified all of the overlapping periods in my table by failing with details on the improper key. HOWEVER, after fixing all of the offending data, the index still failed to create: ERROR: failed to re-find tuple within index tbl_f_point_f_period_excl HINT: This may be because of a non-immutable index expression. (What does this mean?) The index seems to work on an empty table (per your example), but not on my populated table, and the HINT seems to indicate that period() is not, in fact, immutable, which makes me nervous about using this solution. Hopefully someone knows what's going on here. So I think my other options are to use period data columns (rather than start_date and end_date), then no coercion is needed in the EXCLUDE clause; try to CAST the date periods to boxes (as you suggested); use a rule, per Vincent's suggestion; or not check for overlap as it may be uncommon. Also, I found Jeff Davis's summary of the exclusion constraint helpful in case anyone's interested: http://thoughts.j-davis.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/ Thanks, Robert PS. I don't think the f_point function is necessary. Something like: ... EXCLUDE USING gist (id WITH =, f_period(start_date, end_date) WITH ) seems equivalent to your suggestion: ... EXCLUDE USING gist (f_point(id) WITH ~=, f_period(start_date, end_date) WITH ) -Original Message- From: Filip Rembiałkowski [mailto:filip.rembialkow...@gmail.com] Sent: Wednesday, December 22, 2010 8:28 AM To: McGehee, Robert Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Constraining overlapping date ranges 2010/12/21 McGehee, Robert robert.mcge...@geodecapital.com: PostgreSQLers, I'm hoping for some help creating a constraint/key on a table such that there are no overlapping ranges of dates for any id. Specifically: Using PostgreSQL 9.0.1, I'm creating a name-value pair table as such this: CREATE TABLE tbl (id INTEGER, start_date DATE, stop_date DATE, value REAL); For a given id, I'd like to enforce that there is only one valid value on a given date. For instance, this would be acceptable: id start_date stop_date value 2 2010-11-01 2010-12-01 3 2 2010-12-02 2010-12-15 4 3 2010-10-15 2010-12-15 -3 But this would not: (notice start_date of line 2 is before stop_date of line 1). id start_date stop_date value 2 2010-11-01 2010-12-01 3 2 2010-11-30 2010-12-15 4 3 2010-10-15 2010-12-15 -3 I'd also appreciate it if anyone can provide any indexing hints on this table to optimize queries like: SELECT value FROM tbl WHERE id=2 and '2010-12-02' BETWEEN start_date AND stop_date; Thanks in advance, and sorry if I overlooked any obvious documentation! No one has mentioned exclusionn constraints yet... :-) I have combined it with period contrib (see http://pgfoundry.org/projects/temporal/) to do what you want. Note: you don't have to use this contrib; equivalently, you can CAST date periods to boxes, just make sure the function is injective. -- index requires immutable function, and for some unknown reason (anybody?) point and period constructors are not immutable... CREATE OR REPLACE FUNCTION f_point(integer) RETURNS point LANGUAGE sql AS 'SELECT point($1,$1)' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION f_period(date,date) RETURNS period LANGUAGE sql AS 'SELECT period($1,$2)' IMMUTABLE STRICT; CREATE TABLE tbl ( id integer NOT NULL, start_date date NOT NULL, end_date date, CONSTRAINT tbl_exclude_overlaps EXCLUDE USING gist ( f_point(id) WITH ~=, f_period(start_date,end_date) WITH ) ); INSERT INTO tbl SELECT 1, '2010-01-01', '2010-12-31'; INSERT 0 1 INSERT INTO tbl SELECT 1, '2011-01-01', '2011-03-31'; INSERT 0 1 INSERT INTO tbl SELECT 2, '2010-01-01', '2010-12-21'; INSERT 0 1 INSERT INTO tbl SELECT 2, '2010-12-22', '2010-12-31'; INSERT 0 1 INSERT INTO tbl SELECT 2, '2010-12-25', '2010-12-26'; ERROR: conflicting key value violates exclusion constraint tbl_exclude_overlaps
Re: [GENERAL] Constraining overlapping date ranges
2010/12/21 McGehee, Robert robert.mcge...@geodecapital.com: PostgreSQLers, I'm hoping for some help creating a constraint/key on a table such that there are no overlapping ranges of dates for any id. Specifically: Using PostgreSQL 9.0.1, I'm creating a name-value pair table as such this: CREATE TABLE tbl (id INTEGER, start_date DATE, stop_date DATE, value REAL); For a given id, I'd like to enforce that there is only one valid value on a given date. For instance, this would be acceptable: id start_date stop_date value 2 2010-11-01 2010-12-01 3 2 2010-12-02 2010-12-15 4 3 2010-10-15 2010-12-15 -3 But this would not: (notice start_date of line 2 is before stop_date of line 1). id start_date stop_date value 2 2010-11-01 2010-12-01 3 2 2010-11-30 2010-12-15 4 3 2010-10-15 2010-12-15 -3 I'd also appreciate it if anyone can provide any indexing hints on this table to optimize queries like: SELECT value FROM tbl WHERE id=2 and '2010-12-02' BETWEEN start_date AND stop_date; Thanks in advance, and sorry if I overlooked any obvious documentation! No one has mentioned exclusionn constraints yet... :-) I have combined it with period contrib (see http://pgfoundry.org/projects/temporal/) to do what you want. Note: you don't have to use this contrib; equivalently, you can CAST date periods to boxes, just make sure the function is injective. -- index requires immutable function, and for some unknown reason (anybody?) point and period constructors are not immutable... CREATE OR REPLACE FUNCTION f_point(integer) RETURNS point LANGUAGE sql AS 'SELECT point($1,$1)' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION f_period(date,date) RETURNS period LANGUAGE sql AS 'SELECT period($1,$2)' IMMUTABLE STRICT; CREATE TABLE tbl ( id integer NOT NULL, start_date date NOT NULL, end_date date, CONSTRAINT tbl_exclude_overlaps EXCLUDE USING gist ( f_point(id) WITH ~=, f_period(start_date,end_date) WITH ) ); INSERT INTO tbl SELECT 1, '2010-01-01', '2010-12-31'; INSERT 0 1 INSERT INTO tbl SELECT 1, '2011-01-01', '2011-03-31'; INSERT 0 1 INSERT INTO tbl SELECT 2, '2010-01-01', '2010-12-21'; INSERT 0 1 INSERT INTO tbl SELECT 2, '2010-12-22', '2010-12-31'; INSERT 0 1 INSERT INTO tbl SELECT 2, '2010-12-25', '2010-12-26'; ERROR: conflicting key value violates exclusion constraint tbl_exclude_overlaps DETAIL: Key (f_point(id), f_period(start_date, end_date))=((2,2), [2010-12-25 00:00:00+01, 2010-12-26 00:00:00+01)) conflicts with existing key (f_point(id), f_period(start_date, end_date))=((2,2), [2010-12-22 00:00:00+01, 2010-12-31 00:00:00+01)). greets, Filip -- 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] Constraining overlapping date ranges
2010/12/22 Thomas Kellerer spam_ea...@gmx.net: I'm curious why you use this syntax as you have fixed values and could use the standard VALUES construct without problems: INSERT INTO tbl VALUES (1, '2010-01-01', '2010-12-31'); no particular reason; just two keystrokes less :-) The SQL code is postgres-specific anyway. -- 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 use pgbouncer
2010/12/21 Adarsh Sharma adarsh.sha...@orkash.com: Dear all, I am not able to find any useful document regarding Configuration and Running Pgbouncer with Postgres-8.4.2. that's strange, there are several good pages on the web; there is also my mini-howto: http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/ How it helps and is it able to boost some performance ? PgBouncer is a connection pooler. I wonder why do you want to use it, if you don't know what it is? it can help in many ways, two most obvious points: - reduce number of postgres backends (more resources for the server) - reduce cost of single connection from the application to postgres Or if there is another useful tool available for Connection Pooling. Please guide me for this. yes there are some; see http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling it depends on what you need. pgbouncer is the smallest and most lightweight pooler available. FR -- 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 use pgbouncer
2010/12/21 Andreas Kretschmer akretsch...@spamfence.net: I'm looking for a solution to split read and write access to different servers (streaming replication, you know ...). Can i do that with pgpool? (setting backend_weightX=0 or 1)? I have read the doc, but i'm not sure if pgpool the right solution, maybe you can enlighten me? (or someone else ...) did you see http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#master_slave_mode ? I think it answers your question :-) warning: it works by detecting which queries are read-only, which does not always work out of the box. you will sometimes have to modify driver settings or worse, modify SQL queries including hints for pgpool. see: http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#white_function_list http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#black_function_list greets, Filip -- 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] Maximum size for char or varchar with limit
2010/12/19 Jasen Betts ja...@xnet.co.nz I have some values of perhaps 20Mb that I might want to store samples of in a partitioned table. (so I can delete them easily) what's the right way? 20 Mbits or 20 MBytes? how big samples? answer to your question depends on what you want to do with these samples in SQL. if you could give more background information... I guess I could just keep them as disk files and rotate the directories as I rotate partitions. if only reason is to store - then files are probably best idea. PS. why did you post a reply to this thread? It does not seem related
Re: [GENERAL] Postgres Installation
2010/12/16 Adarsh Sharma adarsh.sha...@orkash.com Dear all, Is there are any useful links for Installing Postgres_9.1 (recent version ) in CentOS from its binaries. most recent stable version is 9.0.2. here you will find Yum instructions and links to RPM packages http://yum.pgrpms.org/howtoyum.php see also http://people.planetpostgresql.org/devrim/index.php?/archives/50-Upgrading-from-8.4-to-9.0-on-Fedora-Red-Hat-CentOS-using-RPMs.html I see there was some temporary problem with 9.0.1 packages so if you can, wait for 9.0.2 RPMs (should be available soon, if not yet)
Re: [GENERAL] Changing WAL Directory in Postgres
in short: sudo su - postgres pg_ctl stop -m fast cd $PGDATA mv pg_xlog /another/device ln -s /another/device/pg_xlog . pg_ctl start 2010/12/15 Adarsh Sharma adarsh.sha...@orkash.com Hi all, From the last 2 days I am researching on Performance Tuning in Postgres. For this , I have to change my WAL directory to other disk than the data directory. But I am not able to find the correct way to do this. Please be kind to advice. Thanks Regards Adarsh Sharma -- 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] crosstab function
please show - source data structures (in form of CREATE TABLE please) - actual whole query that creates duplicates 2010/12/14 Sim Zacks s...@compulab.co.il postgres 8.2.17 I am trying out the crosstab function (tablefunc contrib) for reporting needs and I'm having a problem. I have customers and products and the data is the quantity purchased. I am grouping by customername, productname in the source sql. My category sql depends if I want the products or customers to be the columns. When I make customers the rows and products the columns, it works fine. But when I make customers the columns and products the rows, there are duplicate product rows. Is there a way to group the product rows so that the data results come back correct? -- 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] crosstab function
http://www.postgresql.org/docs/8.4/interactive/tablefunc.html says:
The crosstab function produces one output row for each consecutive group
of input rows with the same row_name value. It fills the output valuecolumns,
left to right, with the
value fields from these rows. If there are fewer rows in a group than
there are output value columns, the extra output columns are filled with
nulls; if there are more rows, the extra input rows are skipped.
In practice the SQL query *should always specify ORDER BY 1,2* to ensure
that the input rows are properly ordered, that is, values with the same
row_name are brought together and correctly ordered within the row. Notice
that crosstab itself does not pay any attention to the second column of
the query result; it's just there to be ordered by, to control the order in
which the third-column values appear across the page.
I don't have time to verify this but I guess this is your problem, try and
test
group by customername,productname order by customername, productname
instead of
group by customername,productname order by productname
HTH
Filip
W dniu 14 grudnia 2010 11:45 użytkownik Sim Zacks s...@compulab.co.ilnapisał:
I rechecked and with products as columns it has duplicate customers. My
goal is one row per customer with the sum of quantity filled in for each
product they purchased.
create table customers(customerid serial primary key, customername text);
create table products(productid serial primary key, productname text);
create table quotations(quotationid serial primary key, customerid int,
orderdate timestamp);
create table quotationitems(quotationitemid serial primary key, quotationid
int, productid int, quantity int, unitprice numeric(9,2));
select * from crosstab('
select customername,productname as bucket,sum(quantity) as bucketvalue
from quotationitems a join quotations b using(quotationid)
join customers c using(customerid)
join sales.products d using (productid)
where orderdate between ''1/1/2009'' and ''1/1/2010''
and producttypeid=1
group by customername,productname order by productname',
'select productname from sales.products where producttypeid=1 order by
productname')
as rpt(customername text,ATX int,
CM-A510 int,
CM-F82 int,
CM-i586 int,
CM-i686B int,
CM-i686M int,
CM-iAM int,
CM-iGLX int,
CM-iPM int,
CM-iTC int,
CM-T3530 int,
CM-X255 int,
CM-X270 int,
CM-X300 int,
CM-XAM int
)
order by customername
On 12/14/2010 10:27 AM, Filip Rembiałkowski wrote:
please show
- source data structures (in form of CREATE TABLE please)
- actual whole query that creates duplicates
2010/12/14 Sim Zacks s...@compulab.co.il
postgres 8.2.17
I am trying out the crosstab function (tablefunc contrib) for reporting
needs and I'm having a problem.
I have customers and products and the data is the quantity purchased. I am
grouping by customername, productname in the source sql. My category sql
depends if I want the products or customers to be the columns.
When I make customers the rows and products the columns, it works fine.
But when I make customers the columns and products the rows, there are
duplicate product rows.
Is there a way to group the product rows so that the data results come
back correct?
--
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] pg_dump order of rows
2010/12/11 jan j...@dafuer.de we are seeking for a solution to make the dumop (e.g. the backup) more random Can you please explain why? ( just curious, this seems a very strange requirement ) you _could_ _try_ to use postgres feature of synchronised seqscans to achieve this, but I'm not sure if it will work. (after a minute of googling ... no it will not work - http://archives.postgresql.org/pgsql-committers/2008-01/msg00358.php ) cheers, Filip
Re: [GENERAL] Group by and lmit
2010/11/2 Bill Reynolds bill.reyno...@ateb.com:
I’m using postgres 8.3.7.
that's a pity because in 8.4 we have window functions which make this
possible in one query:
select * from (
select x, y, count(*) as counter,
row_number() over(partition by x order by count(*)) rn
from mytable
group by x, y order by x, count(*), y
) subq where subq.rn = 5;
in 8,3 you will have to use some tricks... for example, temporary
sequence for every group.
CREATE LANGUAGE plpgsql;
create or replace function exec(text) returns text as 'begin execute
$1;return $1;end' language plpgsql;
select exec('create temp sequence tmpseq'||x) from (select distinct x
from mytable) q;
select x,y,counter from (select x, y, count(*) as counter from mytable
group by x, y order by x, counter, y) subq where
nextval(quote_ident('tmpseq'||x))=5;
--
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/
--
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] Linux x Windows LOCALE/ENCODING compatibility
2010/11/1 Carlos Henrique Reimer carlos.rei...@opendb.com.br Hi, I currently have my PostgreSQL server running in a windows box and now we're migrating it to a Linux operational system. Current windows configuration: pg_controldata shows the LC_COLLATE and LC_CTYPE are Portuguese_Brasil.1252 psql \l command shows we have databases with encoding WIN1252 and UTF8 New Linux box: Which --locale and --encoding options should I use for the INITDB command to have a compatible locale and encoding cluster? Which --encoding option should I use in the CREATEDB command to have a compatible encoding database? from your description I assume you have 8.3 on Windows, but you did not tell us which PostgreSQL version you are planning to deploy on Linux. I'd recommend 8.4. main difference is that on Windows, UTF-8 encoding can be used with any locale. on Unix, not. generally, it is safe to do ``initdb --locale=pt_BR.UTF-8'' and create fresh databases with default settings. then, use Linux pg_dump and psql to clone Windows databases. like this: pg_dump -h windowsbox -p 5432 -U postgres DBNAME | psql DBNAME UTF-8 is compatible with all client encodings. you will get compatible bahaviour for your existing apps with SET client_encoding TO 'whatever'; /* this can be entered as pre-SQL on every client connection */ see http://www.postgresql.org/docs/8.4/static/multibyte.html, 22.2.3. Automatic Character Set Conversion Between Server and Client HTH -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] Why so many xlogs?
2010/11/1 hubert depesz lubaczewski dep...@depesz.com: as I understand, max number of xlog files in pg_xlog should be ( 1 + 2 * checkpoint_segments ). why? for a server overloaded with R/W transactions, it's possible to go beyond this. checkpoints just do not keep up. right now I have an 8.3 with checkpoint_segments=3, constantly running pgbench and I see 8 WAL segments. cheers Filip -- 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] JDBC Transactions
2010/11/1 Jonathan Tripathy jon...@abpni.co.uk: On 01/11/10 18:08, Andy Colson wrote: On 11/1/2010 12:37 PM, Jonathan Tripathy wrote: Hi Everyone, I'm trying to create a server for a database system which will be used by multiple clients. Of course, table locking is very important. Reading the Postgresql docs, locking occurs on a transaction-by-transaction basis. In our java code, we are doing this: //Start Code Block Connection con = ... con.setAutoComitt(false); //Insert SQL here to lock table String qry1 = ... pst1 = con.prepareStatement(qry1) //Insert code here to add values to prepared statement pst1 pst1.executequery(); String qry2 = ... pst2 = con.prepareStatement(qry2) //Insert code here to add values to prepared statement pst2 pst2.executequery(); con.comitt(); //End Code Block My question is, would the above block of code be classed as a single transaction, and would the locking work correctly? Thanks Jonny Table locking is very bad for concurrent access. When a table is locked, its one user at a time. PG usually does not need any locks at all. As long as you use transactions as they were meant to be used (as an atomic operation), things usually work really well, with no locking at all. You could read up on MVCC is you were interested. Without knowing what sql you are running, I can _totally guarantee_ it'll work perfectly with NO table locking. :-) -Andy Hi Andy, Thanks for your reply. Would the above code be classed as a single transaction then? Yes, assuming there's no explicit transaction control (COMMIT/ROLLBACK/END) in your queries. And if so, I could just simple leave out the line which says //Insert SQL here to lock table? In PostgreSQL, locking is done automatically depending on actual isolation level and SQL queries. You can use explicit locking but most of the time it's not needed. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] Restore referencial integrity
I remember when I handled such situations without downtime, in 24/7 HA setup, to avoid large transactions - You could try SELECT FROM A LEFT JOIN B WHERE B.ID IS NULL LIMIT 10 -- and use this as a base for DELETE statement... 2010/8/30, George H george@gmail.com: On Mon, Aug 30, 2010 at 5:30 AM, Carlos Henrique Reimer carlos.rei...@opendb.com.br wrote: Hi, We had by mistake dropped the referencial integrety between two huge tables and now I'm facing the following messages when trying to recreate the foreign key again: alter table posicoes_controles add CONSTRAINT protocolo FOREIGN KEY (protocolo) REFERENCES posicoes (protocolo) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE; ERROR: insert or update on table posicoes_controles violates foreign key constraint protocolo DETAIL: Key (protocolo)=(338525035) is not present in table posicoes. ** Erro ** ERROR: insert or update on table posicoes_controles violates foreign key constraint protocolo SQL state: 23503 Detalhe: Key (protocolo)=(338525035) is not present in table posicoes. As the error message tells, the table posicoes_controles has values in column protocolo that are not present in column protocolo of table posicoes. This happened because some programs removed rows from table posicoes while the referencial integrity was dropped. Now I need to remove all rows from table posicoes_controles that has not corresponding row in table posicoes. As these are huge tables, almost 100GB each, and the server hardware restricted (4GB RAM) I would like a suggestion of which command or commands should be used from the performance perspective. Column protocolo is posicoes table primary key but is not in any index colum of table posicoes_controles. Thank you very much for any help! -- Reimer 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br Hi, I guess you could consider the following strategy: Halt the server or lock the table or something so no program is allowed to delete any rows on the affected tables. Run a PL/SQL script that will remove rows from posicoes_controles whose foreign key is not present in table posics. Then re-issue the foreign key constraint. Then unlock the table or whatever it is you have to do get programs to be able to use the tables again. I hope this helps somewhat. -- George H george@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 -- Wysłane z mojego urządzenia przenośnego Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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 performance
Yes. Look at the pg_statio% views in system catalog. 2010/8/30, Valoo, Julian julian.va...@fnb.co.za: Hi Are there any performance tuning tools for PostgreSQL, besides explain. Any system management views to find out missing indexes or indexes that are not being used. Thanks Julian Valoo SQL Database Administrator Corporate and Transactional Banking IT BankCity e-mail julian.va...@fnb.co.za www.fnb.co.za www.shine2010.co.za To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser: https://www.fnb.co.za/disclaimer.html If you are unable to access the Disclaimer, send a blank e-mail to firstrandbankdisclai...@fnb.co.za and we will send you a copy of the Disclaimer. -- Wysłane z mojego urządzenia przenośnego Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] searchable book database
You have plenty other FTS options: postgres has built-in FTS (tsearch), and if you need something more lightweight than Solr, you can use Sphinx. 2010/8/20 Miguel Vaz pagong...@gmail.com Thank you all for your replies. I already had read about Lucene in its general flavour and eventually caught up about it being used with zend framework, but it seems theres a lot more out there. Will plan the second option. Have the books as files and build some search/index/hash/super-power-ninja engine to do all the hard work behind the scenes and only deliver the pretty bits to the users. This wont be merely a search and find project, as it will have the search, find, analyse/treat results, etc. and then display analysis. Apache Solr..nice one, seems very interesting. Has an API also, that maybe will allow me to plug to the Flex side of the interface. Again, than you all for the great information. MV On Fri, Aug 20, 2010 at 12:09 PM, Eduardo emor...@xroff.net wrote: On Thu, 19 Aug 2010 20:35:50 +0100 Miguel Vaz pagong...@gmail.com wrote: Hi, I need to make a database of books. Several specific subject books that are to be searchable. Is it viable to have the complete book text on a database and search inside it? Or should i consider keeping only its metadata (name, author, filename, etc) on the DB, keep the book file on the HD and use some sort of search algorithm on the file? If you agree on the second option, what would you guys suggest for text file searching? Its for a web project, so how could i go about doing this? (PHP, python...) Thanks. MV Don't knopw if that's what you need but you can setup a DocManager site. Check it at http://wiki.docmgr.org/index.php/DocMGR_-_Document_Management and see if it fills your needs. HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] Monitoring activities of PostgreSQL
2010/6/15 Allan Kamau kamaual...@gmail.com I do have a PL/SQL function that gets executed called many times but with different parameter values each of these times. For most invocations of this function run in a couple of seconds however some invocations of the same function run (on the same dataset) for hours with very little disk activity but high CPU. How can I monitor the actual DB activities during such times so I may better understand what the situation truly is. You can monitor system parameters (CPU, disk IO, memory) with standard OS-specific tools (on Unix: top, ps iostat,vmstat) I have seen some users on this list posting some complex log/outputs, this are the kind of outputs I would like to capture and view. Where are they? they are mostly in log files or output from EXPLAIN ANALYZE command for general info see http://www.postgresql.org/docs/current/static/runtime-config-logging.html http://www.postgresql.org/docs/8.4/static/using-explain.html http://wiki.postgresql.org/wiki/Performance_Optimization and this list archives. Filip
Re: [GENERAL] Connection's limit in SCO OpenServer 5.0.7 and pg 8.3.11 (no more than 94 connections)
1. What kind of error you get when client tries to establish 95th connection? 2. Did you try to consult sco expert? The problem seems to be not caused by postgres... 2010/6/4, erobles erob...@sensacd.com.mx: Hello! I have postgres running on SCO OpenServer 5.0.7 and I've noticed that only accepts up to 94 connections, no more ... I modified the values in postgresql.conf max_connections from 100 to 128 shared_buffers from 24 to 48 MB postgres is started with: su - postgres -c /usr/local/pgsql83/bin/postmaster -i -D /usr/local/pgsql83/data I rebuilt the SCO kernel with these values: NODEsrvr83 EVDEVS96 EVQUEUES88 NSPTTYS64 NUMSP256 NSTREAM4352 NHINODE1024 GPGSLO2000 GPGSHI6000 NSTRPAGES6000 NAIOPROC50 NAIOREQ400 NAIOBUF400 NAIOHBUF100 NAIOREQPP400 NAIOLOCKTBL50 MAX_PROC1 MAXUMEM1048576 NCALL256 NCLIST512 NSTREVENT14848 NUMTIM1888 NUMTRW1888 SDSKOUT64 TTHOG4096 SECLUID0 SECSTOPIO1 SECCLEARID1 by the way the following variables have been established to the maximum SEMMAP8192 SEMMNI8192 SEMMNS8192 SEMMSL300 SEMMNU100 SHMMAX2147483647 Any reply will be highly appreciated! regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Wysłane z mojego urządzenia przenośnego Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] Formatted reports
2010/5/7 Sorin Schwimmer sx...@yahoo.com: Hi All, Is there in PostgreSQL an equivalent of Oracle's BREAK/COMPUTE reporting feature? No, there isn't. You need some procedural language function, or use external tool to accomplish this. There are free reporting engines. jaspersoft, pentaho, BIRT, ... [[ note: BREAK and COMPUTE are features of Oracle's SQL*Plus (client) not their SQL dialect. ]] cheers, Filip -- 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] Indexing queries with bit masks
2010/4/30 Mike Christensen m...@kitchenpc.com: Ok I've been blatantly lying, err, purposely simplifying the problem for the sake of the original email :) I've read over the responses, and am actually now considering just not using any index at all. Here's why: First, this actually isn't the only thing on the WHERE clause. It will only query for users who are friends with you so it can notify them of your activities. That's done via a weird JOIN on a table that holds all the friend relationships. So in reality, it will only load maybe a hundred rows, or maybe a thousand every once in a while if you're way popular. If I'm not mistaken, it should use the index to narrow it down to the list of friends, and then use a sequential scan to weed out the ones who subscribe to that type of notification. Second, the only thing /ever/ that will do this query is the queue service whose job it is to process notifications (which are files dropped on the file system) and email people all day long. This service handles one job at a time, and could potentially run on its own machine with its own read-only copy of the database. Thus, even if it was a fairly slow query, it's not gonna bring down the rest of the site. Regarding the idea of putting an index on each bit, I thought about this earlier as well as just kinda cringed. The users table gets updated quite a bit (last logon, session id, any time they change their profile info, etc).. Too many indexes is bad. I could just put the data in another table of course, which lead me to another idea. Have a table called Subscriptions and have each row hold a user id and a notification type. I could index both, and join on (Subscriptions.UserId = Users.UserId AND Subscriptions.Type = 8). This would be pretty dang fast, however updates are kinda a royal pain. When the user changes which types of subscriptions they want (via a list of checkboxes), I'd have to figure out which rows to delete and which new ones to insert. However, I think I have an idea in mind for a PgSQL function you pass in the bitmask to and then it translates it to conditional deletes and inserts. A third idea I'm tossing around is just not worry about it. Put the bitmask in the DB, but not filter on it. Every friend would be loaded into the dataset, but the queue processor would just skip rows if they didn't subscribe to that event. In other words, move the problem down to the business layer. The drawback is potentially large number of rows are loaded, serialized, etc into memory that will just be ignored. But of course the DB is probably a read-only copy and it's not even close to the bottle neck of the email queue under heavy load, so it's probably a non-issue. If mailing is slow, I just add more queue services.. I'm exploring all these ideas. I predict using the bitwise AND on the where clause isn't gonna be the worst design ever, and it's sure easier to implement than a table of subscriptions. What do you guys think? I would say normalize. Which means that I like your separate table idea best. It's clear, obvious, and 3NF - conforming solution. Changing the set of subscriptions with delete-update-insert combo is not so bad as you would think. Encapsulating it in some kind of functional API looks nice too. Filip -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] lc_ctype does not work on windows ?
Hi I have a problem with locale on windows. postgresql 8.4. this does not work as expected: filip=# select 'A' ~ '\w', 'Ą' ~ '\w'; ?column? | ?column? --+-- t| f (1 row) -- investigating postgres settings: filip=# select name, setting, context, source, boot_val, reset_val from pg_settings where name ~* '(lc|encoding|locale|char|text|version)'; name| setting | context | source | boot_val | reset_val +---+---++---+--- client_encoding| win1250 | user | session | SQL_ASCII | UTF8 default_text_search_config | pg_catalog.simple | user | configuration file | pg_catalog.simple | pg_catalog.simple lc_collate | Polish, Poland| internal | override | C | Polish, Poland lc_ctype | Polish, Poland| internal | override | C | Polish, Poland lc_messages| Polish, Poland| superuser | configuration file | | Polish, Poland lc_monetary| Polish, Poland| user | configuration file | C | Polish, Poland lc_numeric | Polish, Poland| user | configuration file | C | Polish, Poland lc_time| Polish, Poland| user | configuration file | C | Polish, Poland server_encoding| UTF8 | internal | override | SQL_ASCII | UTF8 server_version | 8.4.2 | internal | default | 8.4.2 | 8.4.2 server_version_num | 80402 | internal | default | 80402 | 80402 (11 rows) -- and database settings: filip=# select * from pg_database where datname='filip'; -[ RECORD 1 ]-+--- datname | filip datdba| 2650623 encoding | 6 datcollate| Polish, Poland datctype | Polish, Poland datistemplate | f datallowconn | t datconnlimit | -1 datlastsysoid | 11563 datfrozenxid | 649 dattablespace | 1663 datconfig | datacl| -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] Accessing Windows install of PostgreSQL via cygwin
You can see that python is trying to connect to postgres via unix socket. In cygwin that's rather not possible. Just switch the client to TCP mode. There should be host/port in connection settings. 2010/4/1, nerdydork dustin.da...@gmail.com: I have had a development environment set up in Windows for Django. Recently I moved all my python/django setup to cygwin because I got tired of the windows command line. Everything seems to be working except the PostgreSQL connection. I understand that I can install postgres inside of cygwin and access it there, but is it possible to access my windows install of postgres from within cygwin? If so, how? I get errors trying to connect to localhost:54 File /usr/lib/python2.5/site-packages/django/db/backends/ postgresql_psycopg2/base.py, line 98, in _cursor self.connection = Database.connect(**conn_params) psycopg2.OperationalError: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432?32 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Wysłane z mojego urządzenia przenośnego Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] optimizing import of large CSV file into partitioned table?
2010/3/28 Thom Brown thombr...@gmail.com: The problem here is that you appear to require an index update, trigger firing and constraint check for every single row. First thing I'd suggest is remove the indexes. Apply that after your import, otherwise it'll have to update the index for every single entry. +1 And the trigger won't help either. Import into a single table and split it out into further tables after if required. note: partitioning could help if there were multiple physical volumes / spindles for data directory. for maximizing performance, I would rather split the CSV input (with awk/perl/whatever) before loading, to have one backend for each partition loader. And finally the constraint should probably be applied after too, so cull any violating rows after importing. +1 -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] Creating a view: ERROR: rules on SELECT must have action INSTEAD SELECT
David, You cannot mix CREATE TEMP TABLE and SELECT in one CREATE VIEW statement I really recommend looking at WITH statement http://www.postgresql.org/docs/8.4/static/queries-with.html Adjusting to your example: CREATE VIEW test_view AS WITH q AS ( SELECT 2 as two, 3 as three ) SELECT *, two+three as five FROM q; 2010/3/19 David Waddy da...@littleriver.ca: I get the following error when trying to create a view with the following trivial example. SQL error: ERROR: rules on SELECT must have action INSTEAD SELECT In statement: CREATE VIEW nutrition.test_view AS SELECT 2 as two, 3 as three INTO TEMP temp_table; SELECT *,two+three as five FROM temp_table; If I rewrite the statement to maybe make more sense: SQL error: ERROR: temporary tables cannot specify a schema name In statement: SELECT 2 as two, 3 as three INTO TEMP nutrition.temp_table; CREATE VIEW nutrition.test_view AS SELECT *,two+three as five FROM nutrition.temp_table; So the question is how do I use the powerful feature of temporary tables with views? And if this is not possible, how do I do something similar for optimization reasons (ie pretend two+three as five is some very expensive operation and two and three were also expensive operations)? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] strange
For the record, I've recently observed such behaviour on non-cheap 64bit server harware. That was Pg 8.4.0. hardware specs available on request. EXPLAIN ANALYZE SELECT was over 2 times slower that SELECT. repeatedly. Answering an obligatory question: NO virtualization (vmware/xen/other) there. Question: Is there anything as normal, accepted level of performance degradation when using EXPLAIN ANALYZE compared to plain query? 2010/3/22 Tom Lane t...@sss.pgh.pa.us: Szymon Guz mabew...@gmail.com writes: I've got a simple query. When I use explain analyze it lasts 7 times slower. Why? You've got a machine where gettimeofday() is really slow. This is common on cheap PC hardware :-( 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 -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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 remove super user
what operating system? did you try to remove postgres system account before installing new version? 2010/3/11 Jowad Bouzian jowadbouzi...@hotmail.com: I uninstall an older version and tried installing 8,4 but now it keeps asking for a password from Superuser account... Any ideas how to remove this? Thanks for reading. BE-langrijk nieuws! Nu ook @hotmail.BE-adressen in België. Klik en creëer -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] error migrating database from 8.4 to 8.3
2010/2/8 Marc Lustig m...@marclustig.com Due to a server issue we needed a reinstallation of Ubuntu along with a downgrade to Ubuntu Hardy. So this is what we did: - copied all from /var/lib/postgresql/8.4/main/ to the new server /var/lib/postgresql/8.3/main/ - edited /var/lib/postgresql/8.3/main/postmaster.opts to correct the path No, that will not work. On-disk formats are not binary compatible. You have to make backups from 8.4 and restore them on empty 8.3. See Notes in http://www.postgresql.org/docs/8.4/static/app-pgdump.html Now trying to start the server results in * Error: The server must be started under the locale : which does not exist any more. I googled and found that people ran into this problem due to different architectures (32 vs 64 bit). In this case, the architecture is definiately the same. The only difference is that the database comes from a 8.4 installation and the server is 8.3 Please help solving this locale issue. thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] problems maintaining boolean columns in a large table
2010/2/10 Ben Campbell b...@scumways.com I settled on: CREATE TABLE needs_indexing ( article_id integer REFERENCES article(id) PRIMARY KEY ); The primary key-ness enforces uniqueness, and any time I want to add an article to the queue I just make sure I do a DELETE before the INSERT. Bound to be more efficient ways to do it, but it works. better use INSERT INTO needs_indexing (article_id) SELECT NEW.id WHERE NOT EXISTS ( SELECT 42 FROM needs_indexing WHERE article_id = NEW.id ); -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL]
2010/2/8 jehanzeb mirza jehanzeb.mirz...@gmail.com hi im facing a problem of installing postgre sql in my windows based system please guide me the way i have downloaded the application but i cannot find any setup file in it. which installer have you downloaded? you should use this one: http://www.enterprisedb.com/products/pgdownload.do#windows secondly has anyone had experience on working both on geoserver and postgresql(POSTGIS) how do i interrelate them no experience here, but http://docs.geoserver.org/2.0.x/en/user/ seems helpful. jehanzeb -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] About partitioning
W dniu 20 stycznia 2010 12:01 użytkownik Grzegorz Jaśkiewicz gryz...@gmail.com napisał: please use search before asking. please use search before advising to use search; it's not so easy to find. can you share a link to archived post? Filip
Re: [GENERAL] What happens when you kill the postmaster?
2010/1/13 Ralf Schuchardt r...@gmx.de Hi, on one of our Mac servers an update (Remote Desktop Client) killed yesterday the postmaster process. Apparently this did not have any influence on existing connections and therefore was not detected until some time later, when no connection for a backup could be made. I have then closed all apps with connections to the database. This brought the whole cluster down. It restarted then with some transaction log rollback messages and seems to be running fine since then. Can I now expect that the database is in a consistent state, or must I assume the database is corrupted? I could run a dump-all without problems and there are rows created after the death the postmaster. It depends on the signal which was sent to the postgres process. AFAIK, only SIGKILL (unconditional kill) can make some damage to the database. see http://www.postgresql.org/docs/8.4/static/app-postgres.html forfull explanation Thanks. Ralf Schuchardt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
[GENERAL] log_temp_files confusion
I would like to log usage of temporary files for sort/join operations, but only when size of these files exceeds some threshold. So I set in postgresql.conf (this is 8.4.2) log_temp_files = 4MB But then I got these messages in log file 2010-01-12 13:24:49 CET 24899 fi...@la_filip LOG: temporary file: path base/pgsql_tmp/pgsql_tmp24899.1, size 162464 2010-01-12 13:24:49 CET 24899 fi...@la_filip CONTEXT: SQL statement insert into foo ( ... ) select ... PL/pgSQL function la_foo line 51 at SQL statement 2010-01-12 13:24:49 CET 24899 fi...@la_filip STATEMENT: Select * From la_foo(31968) 2010-01-12 13:24:49 CET 24899 fi...@la_filip LOG: temporary file: path base/pgsql_tmp/pgsql_tmp24899.0, size 153152 2010-01-12 13:24:49 CET 24899 fi...@la_filip CONTEXT: SQL statement insert into foo ( ... ) select ... PL/pgSQL function la_foo line 51 at SQL statement 2010-01-12 13:24:49 CET 24899 fi...@la_filip STATEMENT: Select * From la_foo(31968) 2010-01-12 13:24:49 CET 24899 fi...@la_filip LOG: temporary file: path base/pgsql_tmp/pgsql_tmp24899.2, size 152128 2010-01-12 13:24:49 CET 24899 fi...@la_filip CONTEXT: SQL statement insert into foo ( ... ) select ... PL/pgSQL function la_foo line 51 at SQL statement 2010-01-12 13:24:49 CET 24899 fi...@la_filip STATEMENT: Select * From la_foo(31968) The combined size of temporary files named in these three warnings is far from 4 MB. Why is postgres logging these operations? How is this threshold calculated at run time? TIA -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] log_temp_files confusion
W dniu 13 stycznia 2010 19:52 użytkownik Andrej andrej.gro...@gmail.comnapisał: 2010/1/14 Filip Rembiałkowski plk.zu...@gmail.com: I would like to log usage of temporary files for sort/join operations, but only when size of these files exceeds some threshold. So I set in postgresql.conf (this is 8.4.2) log_temp_files = 4MB Just a wild guess... the DOCU says it's an integer, not an INT STRING. postgresql.conf.sample has these lines: #log_temp_files = -1# log temporary files equal or larger # than the specified size in kilobytes; # -1 disables, 0 logs all temp files I've set it in the config file to log_temp_files = 4096 which shows as fi...@postgres=# show log_temp_files ; log_temp_files 4MB (1 row) Try log_temp_files = 4194304 I will try this, but this would mean that config system is somehow broken. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] annahensjerry has [OT]
2010/1/12 annahensjerry no-re...@weebly.com To: pgsql-general@postgresql.org From: annahensjerry no-re...@weebly.com Hello, (...) waiting for the love.I am Miss anna This miss is going to love quite a bunch of fellows, isn't she? And this is good: Weebly values your privacy. I guess they won't gain many fans of Weebly here :-)
Re: [GENERAL] pg.dropped
Full test case, reproduced in 8.4.2 on two different hosts create table test (id serial primary key, t1 text, t2 text); create function myhash(test) returns text as 'select md5($1::text)' language sql immutable; create index myhash on test( myhash(test) ); alter table test add t3 text; alter table test drop t3; insert into test(t1,t2) select 'foo', 'bar'; PS. I realise that marking of CAST (rowtype as text) as immutable may be not safe. But this behaviour is probably a bug anyway. 2010/1/7 Tom Lane t...@sss.pgh.pa.us =?UTF-8?Q?Filip_Rembia=C5=82kowski?= plk.zu...@gmail.com writes: INSERT INTO thetable ( ... ) VALUES ( ... ); ERROR: table row type and query-specified row type do not match If you want any help with this you need to show a *complete* example of how to produce this failure. regards, tom lane -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] Server name in psql prompt
2010/1/8 Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk Is there any way of getting psql to display the name of the currently-connected server in its prompt, and perhaps a custom string identifying e.g. a disc set, without having to create a psqlrc file on every client system that's got a precompiled psql installed? No. I've just come close to dropping a table that would have been embarrassing because I couldn't see which server an instance of psql was talking to. Now obviously that's due to lackwittedness on my part and it could be cured by installing psqlrc files- but this might not be a viable option since it means chasing down every psql binary that's been installed on the LAN in an attempt to protect users from self-harm: far nicer if the default psql prompt could be loaded from the server. Not every binary; every user profile. If you need it, maybe employ some company-wide user profile scripts. That's not so hard if you use Linux/Unix environment; just use /etc/rpofile to enforce a common policy. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
[GENERAL] pg.dropped
Hi all, I have a deja vu or I had this very problem before. Now I use 8.4.2 and it happened again. After dropping a column from table, there is still entry in pg_attribute fi...@la_dev=# select * from pg_attribute where attrelid = (select oid from pg_class where relname='thetable') order by attnum desc limit 1; -[ RECORD 1 ]-+-- attrelid | 4753849 attname | pg.dropped.69 atttypid | 0 attstattarget | 0 attlen| 1 attnum| 69 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage| p attalign | c attnotnull| f atthasdef | f attisdropped | t attislocal| t attinhcount | 0 attacl| NULL And of course this makes my INSERT not working... INSERT INTO thetable ( ... ) VALUES ( ... ); ERROR: table row type and query-specified row type do not match DETAIL: Physical storage mismatch on dropped attribute at ordinal position 69. Any clues / hint how to NEVER get into this again? TIA. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] using a function
2010/1/5 Andy Colson a...@camavision.com I have a function that's working for what I needed it to do, but now I need to call it for every id in a different table... and I'm not sure what the syntax should be. Here is an example: create or replace function test(uid integer, out vhrs integer, out phrs integer, out fhrs integer) returns setof record as $$ begin vhrs := uid + 1; phrs := uid + 2; fhrs := uid + 3; return next; end; $$ language 'plpgsql'; I currently use it once, I know the id, and just call: select * from test(42); all is well. But now I need to call it for every record in my employee table. I tried: select id, vhrs, phrs, fhrs from employee, test(id) I also tried an inner join, but neither work. Any hints how I might do this? # select id, test(id) from ids; id | test +- 1 | (2,3,4) 2 | (3,4,5) 3 | (4,5,6) (3 rows) is this what you want? if not, maybe # select id, (select vhrs from test(id)) as vhrs, (select phrs from test(id)) as phrs, (select fhrs from test(id)) as fhrs from ids; note: declare your function volatility - see http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html note: in above example, a VIEW would be enough. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] Large tables, ORDER BY and sequence/index scans
2010/1/5 Milan Zamazal p...@brailcom.org My problem is that retrieving sorted data from large tables is sometimes very slow in PostgreSQL (8.4.1, FWIW). I typically retrieve the data using cursors, to display them in UI: BEGIN; DECLARE ... SELECT ... ORDER BY ...; FETCH ...; ... On a newly created table of about 10 million rows the FETCH command takes about one minute by default, with additional delay during the contingent following COMMIT command. This is because PostgreSQL uses sequence scan on the table even when there is an index on the ORDER BY column. When I can force PostgreSQL to perform index scan (e.g. by setting one of the options enable_seqscan or enable_sort to off), FETCH response is immediate. PostgreSQL manual explains motivation for sequence scans of large tables and I can understand the motivation. Nevertheless such behavior leads to unacceptably poor performance in my particular case. It is important to get first resulting rows quickly, to display them to the user without delay. My questions are: - What is your experience with using ORDER BY + indexes on large tables? Without a WHERE clause postgres will almost always choose a sequential scan. - Is there a way to convince PostgreSQL to use index scans automatically in cases where it is much more efficient? I tried using ANALYZE, VACUUM and SET STATISTICS, but without success. By using cursors you take some responsibility away from the planner. It has no idea that you want first 100 rows quickly. It just tries to optimize the whole operation. - Is it a good idea to set enable_seqscan or enable_sort to off globally in my case? Or to set them to off just before working with large tables? My databases contain short and long tables, often connected through REFERENCES or joined into views and many of shorter tables serve as codebooks. Can setting one of the parameters to off have clearly negative impacts? IMHO, no, no and yes. - Is there a recommended way to keep indexes in good shape so that the performance of initial rows retrievals remains good? The large tables are typically append-only tables with a SERIAL primary key. Use partitioning. If that's not possible, REINDEX periodically to avoid sub-optimal btree layout. But that's just a half-solution. Thanks for any tips. tips: 1. get rid of cursors, unless you have a strong need for them (eg. seeking back and forth and updating). 2. switch to chunked processing, like this: SELECT * FROM bigtable ORDER by idxcol LIMIT 1000; (process the records) SELECT * FROM bigtable WHERE idxcol [last idxcol from previous fetch] ORDER by idxcol LIMIT 1000; ... and so on. pozdrawiam, Filip -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
