Re: [GENERAL] Visualize database schema
Can anyone advice about a tool to visualize a database schema? Ideally, I would like something that takes the SQL definition of a schema or database (essentially the output of pg_dump) and produces a graphical representation of the tables, constraints and indexes which can be moved around for ease of visualization (something like the Graphical query builder that comes with pgAdmin =1.14). A simple method for this, open source cross-platform and work for various RDBMS, is the following: 1. Download SQL Power Architect at https://code.google.com/p/power-architect/downloads/list (it's a java app so you need a JVM installed and JDBC drivers for the RDBMS to use) 2. Run it, right click in left pane and choose Add source connection - New connection... to create a connection for your db 3. Expand the connection and the database and then simply drag and drop the schema to the right pane. 4. Click on Automatic layout in the toolbar (it's the icon that looks like three connected boxes and a green triangle) From there you can examine the database. There are some limitations, constraints for example, so for more complicated tools look into ERD-tools as suggested by John in another reply. Regards, roppert -- 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] Draw Model from existing DB
I have an existing PostgreSQL DB and I would like to draw a model of the DB structure. Is there some software (Freeware) around that can read the existing structure of my DB and automatically draw e.g. an ER-model of that? The software that I have found so far (e.g. Open System Architect) seems to be able to build models from scratch and convert them into a DB, but not the other way around like what I need. I have found SQL Power Architect to work pretty nice in some situation. To get a model you simply create a new source connection to your db, connect to it and drag and drop it on to the playpen area (the right hand side in the UI). There's both a community edition and a commercial edition. Project page is http://code.google.com/p/power-architect/ Regards roppert Best- Stefan -- View this message in context: http://postgresql.1045698.n5.nabble.com/Draw-Model-from-existing-DB- tp5715605.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] Question: How do you manage version control?
-Ursprungligt meddelande- Från: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] För Ralf Schuchardt Skickat: den 1 juni 2012 18:24 Till: pgsql-general Ämne: Re: [GENERAL] Question: How do you manage version control? I was using a very simplistic approach a while back. I kept the DDL to create current version from scratch in one file, the DDL to upgrade from last schema version in another and then a small shell script to run the upgrade. I kept the version number in a table for the shell script to check and abort if schema version didn't match and then update after successful upgrade. All files were kept under version control and if I needed older versions of the upgrade file I just browsed the history and got it from there. Very simple, no special tools and easy to run and understand for all developers (even for the I won't go near sql-ones). Working with branches makes it a bit harder since the database patch has to be manually handled when merging. Regards, roppert Hello, am 01.06.2012 um 17:28 schrieb Bryan Montgomery: So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc. we use a very basic system since a few years, consisting mainly of shell scripts and sql scripts processed by psql. I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. However, how do you handle dependancies? The code for every recreatable object (i.e. views, functions, maybe types) is stored in its own file. It includes also a drop statement for these objects and formal comments to declare dependencies between the files. The files are processed with a small script that extracts the dependency declarations and writes a create and drop script for all objects while maintaining the correct order. We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter table add column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue? Our database has a versions table, containing the version (and date) of applied patch files. Every patch file checks the current version in the database and throws an exception, when it does not match its expected version. The directory with the recreatable objects is versioned along the patch files. Regards Ralf -- 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] recommended schema diff tools?
(sorry for top posting but I'm using a less than sane email client) I came across SQL Power Architect not long ago and it might be something you could use. http://code.google.com/p/power-architect/ I haven't had much time to look at it though. Regards, roppert Från: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] För Welty, Richard Skickat: den 12 april 2012 16:58 Till: pgsql-general@postgresql.org Ämne: [GENERAL] recommended schema diff tools? can anyone recommend an open source tool for diffing schemas? (it should go without saying that i'm looking for ddl to update production and QA DBs from development DBs, but i'll say it, just in case.) thanks, richard
Re: [GENERAL] Possible Bug
On 2011-02-09 10.51, Kaloyan Iliev Iliev wrote:
Hi,
I think I found something strange in PostgreSQL behavior. Here is an
example:
testdb=# CREATE TABLE test1 (test2 text, test3 text);
CREATE TABLE
testdb=# SELECT A.name FROM test1 A;
name
--
(0 rows)
testdb=# INSERT INTO test1 (test2,test3) VALUES ('1','2');
INSERT 0 1
testdb=# INSERT INTO test1 (test2,test3) VALUES ('3','4');
INSERT 0 1
testdb=# SELECT A.name FROM test1 A;
name ---
(1,2)
(3,4)
(2 rows)
customer.20080408=# SELECT name FROM test1;
ERROR: column name does not exist
LINE 1: SELECT name FROM test1;
testdb=# SELECT version();
version
--
PostgreSQL 8.4.5 on amd64-portbld-freebsd8.1, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 64-bit
(1 row)
Obviously there is no column with name name, but the SELECT query
(ONLY WHEN I USE ALIASES) returns result for it.
And if I have a column with name name_en and by mistake write it
name, instead of error I receive strange data (ARRAY from all
columns), that I don't expect.
Could you tell me if this is a bug or some feature I haven't heard of.
Look up functional notation in
http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#AEN46042
These are the same thing:
select name(test1) from test1;
select test.name from test1;
Best regards,
Kaloyan Iliev
--
Regards,
Robert roppert Gravsjö
--
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] Privileges of Users
On 2011-01-21 16.16, Andy Colson wrote: On 1/20/2011 11:56 PM, Adarsh Sharma wrote: Dear all, I want to know ACl of different users on different tables and databases in Postgresql. Is there any command as we do in mysql as : select * from mysql.user\G; I am researching but cannot able to find. Thanks Regards Adarsh Sharma use \dp in psql And regarding the \G, it's for vertical output in mysql if I remember correctly. The closest to this would be \x in psql. -Andy -- Regards, Robert roppert Gravsjö -- 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] My DataBase can't accept conntecion
On 2011-01-12 13.13, zab08 wrote: we have e test db server, we use jdbc to contect. but after a whie, The test db server can not accept connect. useps aux | grep postgrescommand : postgres 16904 0.0 0.0 46036 3948 ?S17:03 0:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data postgres 16906 0.0 0.0 46144 2192 ?Ss 17:03 0:00 postgres: writer process postgres 16907 0.0 0.0 46036 1088 ?Ss 17:03 0:00 postgres: wal writer process postgres 16908 0.0 0.0 46840 2072 ?Ss 17:03 0:00 postgres: autovacuum launcher process postgres 16909 0.0 0.0 14248 1012 ?Ss 17:03 0:00 postgres: stats collector process postgres 16910 0.0 0.0 46972 2992 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40608) idle postgres 16911 0.0 0.0 46708 2656 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40609) idle postgres 16912 0.0 0.0 46708 2628 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40610) idle postgres 16913 0.0 0.0 46708 2628 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40611) idle postgres 16914 0.0 0.0 46708 2632 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40612) idle postgres 16915 0.0 0.0 46708 2636 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40613) idle postgres 16916 0.0 0.0 46708 2632 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40614) idle postgres 16917 0.0 0.1 47088 4136 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40615) idle postgres 16918 0.0 0.1 47060 4236 ?Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40616) idle postgres 16919 0.0 0.1 47052 4136 ?Ss 17:03 0:02 postgres: postgres edu 172.29.0.40(40617) idle postgres 21619 0.0 0.0 46708 2636 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38803) idle postgres 21620 0.0 0.0 46708 2636 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38804) idle postgres 21621 0.0 0.0 46708 2640 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38805) idle postgres 21622 0.0 0.0 46708 2644 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38806) idle postgres 21623 0.0 0.0 46708 2644 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38807) idle postgres 21624 0.0 0.0 46708 2636 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38808) idle postgres 21625 0.0 0.0 46708 2640 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38809) idle postgres 21626 0.0 0.0 46708 2644 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38810) idle postgres 21627 0.0 0.0 46708 2640 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38811) idle postgres 21628 0.0 0.1 47104 4836 ?Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38812) INSERT Seems like your application never disconnect. Continously creating new connections without disconnecting previously made ones will eventually exhaust the number of connections configured. ... kill -9 pidcommand to kill all of them in the past? and the test db server is shut down. how to analyze the reason of these? and how to solve? -- Regards, Robert roppert Gravsjö -- 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 restore from backup to 8.4.3 server using 9.0 dump/restore
On 2010-12-16 09.16, Andrus Moor wrote: Another requirement is to clone existing database in server with data. I posted question about it and it seems that PostgreSql does not have any capability to do this in server side in plpgsql fast. I'm probably misunderstanding but CREATE DATABASE foo TEMPLATE bar will clone bar as foo including data. Of course this only works within the same cluster. So we need to use slow and unsafe dump/restore over internet for this also. Andrus. -- Regards, Robert roppert Gravsjö -- 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 restore from backup to 8.4.3 server using 9.0 dump/restore
On 2010-12-16 11.12, Andrus Moor wrote: Robert, I'm probably misunderstanding but CREATE DATABASE foo TEMPLATE bar will clone bar as foo including data. Of course this only works within the same cluster. Than you. You are genious I haven't never tought about this. Will this work if database bar is accessed by other users ? Probably it fails, so it cannot used. Correct. Will this command create exact copy ? Yes. Andrus. -- Regards, Robert roppert Gravsjö -- 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 restore from backup to 8.4.3 server using 9.0 dump/restore
On 2010-12-16 11.21, Jayadevan M wrote: Hello, I don't know for sure, but I don't see why it should fail - it's only reading it, not writing data to it or making any changes. Probably it will fail... http://www.postgresql.org/docs/9.0/static/sql-createdatabase.html Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose COPY DATABASE facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; Are there any other side effects to this besides failing CREATE DATABASE command? -- Regards, Robert roppert Gravsjö -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: RES: [GENERAL] Using regexp_replace to remove small words
On 2010-12-13 13.24, Henrique de Lima Trindade wrote:
Hi Peter,
Your example works perfectly. But, I need Your help with on another situation.
We're trying to create a plpgsql function with the expression. But, I'm getting
a syntax error:
-
create or replace function sp_remove_small_words( ptext text ) returns text
immutable as
$$
begin
return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' );
end;
$$ language plpgsql
;
-
ERRO: erro de sintaxe em ou próximo a \
LINE 6: return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' );
You're ending the function declaration with the $$. Using $fun$ as For
example, the following works:
create or replace function sp_remove_small_words( ptext text ) returns
text immutable as
$fun$
begin
return regexp_replace( ptext,
$$\y\w{1,3}\y$$, '', 'g' ); end;
$fun$
language plpgsql;
For details on dollar quoting see:
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
Regards,
roppert
^
** Error **
ERRO: erro de sintaxe em ou próximo a \
SQL state: 42601
Character: 138
Thanks again!
-Mensagem original-
De: Peter Eisentraut [mailto:pete...@gmx.net]
Enviada em: sexta-feira, 10 de dezembro de 2010 20:59
Para: Henrique de Lima Trindade
Cc: pgsql-general@postgresql.org
Assunto: Re: [GENERAL] Using regexp_replace to remove small words
On fre, 2010-12-10 at 10:47 -0200, Henrique de Lima Trindade wrote:
I'm trying to find a regular expression that removes all small (length N)
words from a string. But, until now I've not been successful.
Here is a start:
select regexp_replace('Tommy Lee Jones', $$\y\w{2,3}\y$$, ' ', 'g' );
If you want to normalize the spaces after the removal and handle
beginning and end of the word, you will need to expand this to cover
those cases, but the example should contain the key ingredients.
--
Regards,
Robert roppert Gravsjö
--
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] Asynchronous query execution
On 2010-12-08 14.35, c k wrote: Hello, I would like to know how can we execute the queries asynchronously? If we use and execute plpgsql functions they just completes the execution or throws an error on error. In between next sql statement waits for the previous one to complete the execution. But in few situations it is required not to wait for getting the completion of previous sql statement. How can this e achieved? Is this what you're looking for: http://www.postgresql.org/docs/current/interactive/libpq-async.html Waiting for you response. CPK -- Regards, Robert roppert Gravsjö -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find correct locale name for CREATE DATABASE
On 2010-12-01 09.26, Andrus wrote: Thom, You can list available locales on your system with: locale -a Thank you. How to resolve this issue if only 5432 port is open in server ? Maybe asking the sysadmin of that host to return the result of locale -a? Btw, are you sure et_EE.UTF-8 is installed on the host? Based on my limited knowledge, postgres does not have command to run locale -a How to get list of available locale names from Postgres ? Why Postgres does not have command which returns available locale names ? How to use same locale names in every platform? Andrus. -- Regards, Robert roppert Gravsjö -- 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] Looking for auto starting procedures
On 2010-12-01 16.16, ma...@manfbraun.de wrote: Hello ! I am coming from Sql Server right now and have to learn about the infrastructure. What I missed first, is, to execute procedures regularly/repeatedly on a given time. I want to prevent my to write a lot external programs und use cron :-( cron is the standard way of scheduling reoccurring jobs on *nix systems. It's preferable to having each daemon implement scheduling on its own. The othing thing is, that I need some internally running procedures, which do some work. On Sql Server, I can use auto-start stored procedures. Is there anything like this in postgresql, or what can I do? Is this long running processes, i.e daemons, or is it scheduled processes? For long running I would recommend writing a proper daemon. For scheduled I'd recommend cron or at. And, is it just possible, to put a message to the syslog and with my own identification string? I'm pretty sure you can accomplish this in configuration if you're using syslog-ng. Other syslog alternatives probably has similar possibilities. -- Regards, Robert roppert Gravsjö -- 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] techniques for bulk load of spatial data
On 2010-11-30 14.29, Mario Corchero wrote: Hi, I'm a student of Computer Science, I know diffrents techniques of bulk load, but I need to know how specifically postgreSQL make a bulk load of spatial data, could anyone Suggestions when loading large amount of data: http://www.postgresql.org/docs/current/static/populate.html Not specific to spatial data but you might find it helpful. -- Regards, Robert roppert Gravsjö -- 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] Debug advice when postgres connection maxing out
On 2010-11-23 20.56, anypossibility wrote: Thank you for your advice.I reviewed the query and it is the most simple one column value update with primary key query. I would like to share this with you and would like to receive advice as to whether I am on the right track. Facts: the connection maxed out and i could not even terminate postgres with SIGINT. I was afraid to do SIGQUIT so restarted the server itself (not sure if this was better decision) the server has been running just fine until a few days ago. No hardware update. Other servers that has exactly same spec (code, version, hardware) is having no issue... from this facts, I am leaning towards hardware issue.. though I have no idea where to start... This started to happen on one server a few days ago. So far this happens once a day. No pattern what's so ever in terms of client request, time of the day... Anything interesting in postgresql.log? Maybe you have to increase logging to find anything. Perhaps enable log_min_duration_statement to see if there are any long running statements that could give you a hint. For details see http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html My observation: From the look of output from the ps -ef | grep postgres (pasted below) what was happening is that postgres is not returning result or waiting for something. and that one process is holding up the rest of the process - basically nothing is processed yet postgres continue to receive request until it reaches to max connection. Where can I start to diagnose this issue? Any advice would be appreciated. --- please note: Actual IP was replaced with Client_IP SQL:~ root# ps -ef | grep postgres 502 891 1 0 0:05.61 ?? 0:06.54 /Library/PostgresPlus/8.3/bin/postgres -D /data 502 892 891 0 0:01.41 ?? 0:01.98 postgres: logger process 502 894 891 0 0:17.91 ?? 0:27.16 postgres: writer process 502 895 891 0 0:05.43 ?? 0:06.88 postgres: wal writer process 502 896 891 0 0:01.59 ?? 0:03.26 postgres: autovacuum launcher process 502 897 891 0 1:09.83 ?? 1:35.88 postgres: stats collector process 502 1007 891 0 2:10.40 ??33:38.91 postgres: DBA DB_Name Client_IP(60096) UPDATE I would be curious about this process since it stands out by the amount of time it been running. I would watch for a similar long running process and try to see what pg_stat_activity and pg_lock says about it. What hardware are you running on and what size database? Could it be you have a very large table on slow hardware and some client is trying to update all of that table? Any other services running on the same host? Could it be a shared storage used by some other host? /r 502 1008 891 0 0:00.82 ?? 0:20.91 postgres: DBA DB_Name Client_IP(60097) UPDATE 502 45397 891 0 0:00.01 ?? 0:00.11 postgres: DBA DB_Name Client_IP(64007) SELECT 502 45398 891 0 0:00.06 ?? 0:00.59 postgres: DBA DB_Name Client_IP(64008) idle 502 45399 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64009) SELECT 502 45400 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64012) SELECT 502 45401 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64013) SELECT 502 45402 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64014) SELECT 502 45403 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64015) SELECT 502 45404 891 0 0:00.01 ?? 0:00.03 postgres: DBA DB_Name Client_IP(64016) SELECT 502 45405 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64017) SELECT 502 45406 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64018) UPDATE 502 45407 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64019) SELECT 502 45408 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64020) SELECT 502 45409 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64021) SELECT 502 45410 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64022) SELECT 502 45411 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64023) SELECT 502 45412 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64024) SELECT 502 45413 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64025) SELECT 502 45414 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64026) SELECT 502 45415 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64027) UPDATE 502 45416 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64028) SELECT 502
Re: [GENERAL] alter table add column - specify where the column will go?
On 2010-11-24 10.43, Thomas Kellerer wrote: Grzegorz Jaśkiewicz, 24.11.2010 10:37: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. SELECT * also makes the code harder to read since you have to lookup the table definition to see what it'll return. You'll also be wasting resources to handle the data you'll never use. Maybe it doesn't matter for one resultset in one call but it quickly adds up. (This is something ORMs usually are very bad at.) I always try to convince people of this as well, but when they ask me under which circumstances this could happen, I can't think of a proper example. select * from ta join tb on ta.id=tb.aid; Add another column to ta and you get a different resultset. In general if you do any changes to your schema you need to go watch out for code using SELECT * since it easily breaks. For example if I do something like this in Python it will break if I add another column: a, b, c = resultset.next() Had I used SELECT a, b, c it wouldn't. It's a lousy example but not that uncommon. Does anybody have an example that would show this? I still don't have an example of when the internal ordering of a tables column could change. Anyhow, SELECT * is bad practice leading to error prone code and wasting resources. My 2c, /r Regards Thomas -- Regards, Robert roppert Gravsjö -- 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] Debug advice when postgres connection maxing out
On 2010-11-22 20.41, anypossibility wrote: Server: OS X 10.5 PostgreSQL version: 8.3 We experience this connection maxing out once in the full moon. The request from client reaches to the server but client never receive response back. The queries are very simple update on one record or select one record using primary key (checked current_query from pg_stat_activity). Once this started, I normally disconnect all the client (quit client programs) however, the processes don't die on postgres server. So I tried to quit PostgreSQL server using pg_ctl, no response. The only way to terminate is to restart the server (I will try kill -s INT next time.. i didn't know better). Is there anything you can tell me from this symptoms or anything I can do to generate useful log to analyze? If track_activities is on in your postgresql.conf you can check pg_stats_activity to see what query creates the problem. Maybe http://www.postgresql.org/docs/8.3/static/monitoring-stats.html can give you some more ideas. -- Regards, Robert roppert Gravsjö -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] upgrade from postgres 8.x to 9.x problem
On 2010-11-18 03.57, Ray wrote: On Nov 17, 8:42 pm, Rayrui.va...@gmail.com wrote: -- snip --- figured out. the begin is keyword and need to double quoted. This is one strong reason why you should avoid using keywords as object names. Better to fix that early in the design since names has a tendency to stick with you a long time. -- Regards, Robert roppert Gravsjö -- 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] The first dedicated PostgreSQL forum
On 2010-11-17 15.09, Tony Caduto wrote: On 11/15/2010 5:53 PM, Lee Hachadoorian wrote: If anyone's interested, I've started accessing the postgres list through gmane.org (along with several other mailing lists I subscribe to). It's gives you the choice of reading the list as a threaded archive, a blog, or through an NNTP newsreader or an RSS feed. Everyone chooses their preferred interface, the community is not fractured by interface preference. Honestly those options suck. The mailing lists etc are fine, but they are kind of old school, people coming from other databases expect a web based forum plain and simple. To attract more users the forums are a GREAT idea. Stop this ridiculous complaining about interface fracture etc . I can tell you for SURE that many people who are not OLD SCHOOL hate mailing lists, A web based forum gives everyday users more of a chance of interacting with the community. It would be a good idea to stop the bickering and just implement the forums. It's not bickering. It's a discussion and it is what makes the PostgreSQL community keep such a high standard on code and documentation. Sorry if people don't like my honest answer :-) -- Regards, Robert roppert Gravsjö -- 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
On 2010-11-04 16.00, Michael Gould wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. We're running Gentoo which is kind of unortodox but we're using the gentoo portage system for deploy of our own software and have extensive in-house experience with Gentoo. I wouldn't recommend it as a first time linux install though. -- Regards, Robert roppert Gravsjö -- 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] Replication Poll
On 2010-10-28 22.50, Joshua D. Drake wrote: Hey, Based on the discussion here: http://www.commandprompt.com/blogs/joshua_drake/2010/10/users_versus_customers_-_you_dont_need_no_stinking_replication/ http://thebuild.com/blog/2010/10/28/small-postgresql-installations-and-9-0-replication/ http://thebuild.com/blog/2010/10/27/users-want-functionality-not-features/ I have created a Replication Poll. Curious was the general populous thinks: https://www.postgresqlconference.org/content/replication-poll You don't have to log in, but that would obviously help with validity of results. Where (and when?) would one find the results? -- Regards, Robert roppert Gravsjö -- 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] Any advice on debugging hanging postgresql-8.1.21 (many postmaster's)
Alexander Farber skrev 2010-10-04 11.48: I wish I could see those hanging queries, what SQL do they try to execute: To see the query you need to enable stats_command_string in your postgresql.conf. See: http://www.postgresql.org/docs/8.1/interactive/runtime-config-statistics.html You might also benefit from chapter 24: http://www.postgresql.org/docs/8.1/interactive/monitoring.html Regards, roppert pref= select * from pg_stat_activity; datid | datname | procpid | usesysid | usename |current_query | query_start | backend_start | client_addr | client_port ---+-+-+--+-+--+-+---+-+- 16384 | pref|2681 |16385 | pref|command string not enabled | | 2010-10-04 10:22:53.051483+02 | | -1 . I have usually just one Postgres connection from my game (a perl script running as daemon). And then I have several connections from phpBB. But when I restart httpd, the spinning postmaster's don't disappear: top - 11:48:11 up 1:28, 1 user, load average: 9.85, 8.68, 6.25 Tasks: 126 total, 12 running, 114 sleeping, 0 stopped, 0 zombie Cpu0 : 36.1%us, 0.5%sy, 0.0%ni, 63.0%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 37.1%us, 0.8%sy, 0.0%ni, 61.6%id, 0.1%wa, 0.0%hi, 0.5%si, 0.0%st Cpu2 : 61.4%us, 1.3%sy, 0.0%ni, 35.7%id, 1.2%wa, 0.0%hi, 0.5%si, 0.0%st Cpu3 : 26.6%us, 0.6%sy, 0.0%ni, 72.1%id, 0.1%wa, 0.0%hi, 0.5%si, 0.0%st Mem: 4019028k total, 1428256k used, 2590772k free,22324k buffers Swap: 2104496k total,0k used, 2104496k free, 1199036k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 3263 postgres 16 0 122m 14m 9m R 116.0 0.4 16:16.83 postmaster 3208 postgres 16 0 122m 14m 9m R 112.0 0.4 35:16.08 postmaster 3275 postgres 16 0 121m 13m 9m R 110.6 0.4 11:34.32 postmaster 3315 postgres 16 0 121m 13m 9m R 100.5 0.4 7:16.93 postmaster 3193 postgres 16 0 122m 14m 9m R 96.4 0.4 38:44.78 postmaster 3233 postgres 16 0 122m 14m 9m R 68.8 0.4 29:28.90 postmaster 3243 postgres 16 0 122m 14m 9m R 53.3 0.4 25:13.96 postmaster 3256 postgres 16 0 122m 14m 9m R 34.4 0.4 19:23.93 postmaster -- 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] Any advice on debugging hanging postgresql-8.1.21 (many postmaster's)
Alexander Farber skrev 2010-10-04 11.20: Hello Postgres users, I have a Linux website with phpBB serving a small Facebook game: # uname -a Linux X 2.6.18-194.17.1.el5 #1 SMP Wed Sep 29 12:50:31 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux # cat /etc/redhat-release CentOS release 5.5 (Final) # rpm -qa | grep -i postgres postgresql-devel-8.1.21-1.el5_5.1 postgresql-8.1.21-1.el5_5.1 postgresql-server-8.1.21-1.el5_5.1 postgresql-devel-8.1.21-1.el5_5.1 postgresql-libs-8.1.21-1.el5_5.1 postgresql-libs-8.1.21-1.el5_5.1 postgresql-docs-8.1.21-1.el5_5.1 It works generally ok and with little load since June. (My game has less than 100 users and I have AMD/Quad+4GB) On Friday I've installed a cronjob (full source code at the bottom): 3 3 * * * psql -a -f $HOME/bin/clean-phpbb-forum.sql and on Monday I've found out, that the site is overloaded: top - 10:16:56 up 3 days, 23:56, 1 user, load average: 20.55, 21.38, 22.92 Tasks: 157 total, 24 running, 133 sleeping, 0 stopped, 0 zombie Cpu(s): 98.9%us, 1.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 4019028k total, 3073968k used, 945060k free,50604k buffers Swap: 2104496k total, 132k used, 2104364k free, 2316196k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 28974 postgres 16 0 122m 14m 10m R 73.3 0.4 20:56.06 postmaster 28727 postgres 16 0 121m 13m 10m R 54.7 0.3 37:58.58 postmaster 28714 postgres 16 0 122m 14m 10m R 50.4 0.4 38:38.98 postmaster 29412 postgres 16 0 121m 13m 10m R 46.5 0.4 8:03.96 postmaster 28542 postgres 16 0 122m 14m 10m R 46.2 0.4 58:49.38 postmaster 28482 postgres 16 0 122m 14m 10m R 45.8 0.4 61:53.37 postmaster 28468 postgres 16 0 122m 14m 10m R 44.2 0.4 62:46.17 postmaster 29017 postgres 16 0 122m 14m 10m R 43.9 0.4 19:17.06 postmaster 28929 postgres 15 0 122m 14m 10m R 42.2 0.4 22:01.43 postmaster 28500 postgres 16 0 122m 14m 10m R 41.3 0.4 59:40.23 postmaster 28460 postgres 16 0 122m 14m 10m R 40.6 0.4 64:17.16 postmaster 28894 postgres 16 0 122m 14m 10m R 38.6 0.4 23:35.53 postmaster 28489 postgres 16 0 122m 14m 10m R 36.0 0.4 60:32.59 postmaster 28719 postgres 15 0 121m 13m 10m R 25.2 0.3 38:10.33 postmaster 29496 postgres 16 0 121m 13m 10m R 22.9 0.4 4:20.32 postmaster 28556 postgres 15 0 122m 14m 10m R 17.7 0.4 57:32.62 postmaster 28735 postgres 15 0 122m 14m 10m R 15.7 0.4 36:09.45 postmaster 29602 postgres 15 0 119m 11m 9680 S 8.2 0.3 0:00.25 postmaster 28457 postgres 17 0 122m 14m 9m R 3.6 0.4 64:34.38 postmaster 26092 apache15 0 238m 16m 3740 S 0.3 0.4 0:03.38 httpd 29596 afarber 15 0 12744 1116 800 R 0.3 0.0 0:00.09 top 1 root 15 0 10352 700 592 S 0.0 0.0 0:01.69 init I understand, that I probably supply not enough info, but how would you approach debugging this problem? I would connect to the db with psql and query the pg_stat_activity and the pg_locks views. I've run my cronjob numerous times from CLI - it works ok and takes only few seconds. I've installed it as an every-3 mins cronjob on my development VM - it works ok there too. My biggest problem is, that I don't see any information from Postgres at the production site - why did it have to start so many postmaster's (whatever those processes do). Those are backends running queries. The only log file I've found has been /var/lib/pgsql/pgstartup.log and there is nothing suspicious there. (Also nothing related to Postgres in messages, mcelog (empty), audit.log, access_log, error_log). You might want to increase logging. Take a look at http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html for details. Regards /roppert Please give me some hints Alex # cat bin/clean-phpbb-forum.sql start transaction; delete from phpbb_users where user_type=1 and user_inactive_reason=1 and user_lastvisit=0 and age(to_timestamp(user_regdate))interval '3 days'; create temp table old_topics (topic_id integer) on commit delete rows; create temp table old_posts (post_id integer) on commit delete rows; insert into old_topics select topic_id from phpbb_topics where forum_id=5 and topic_poster=1 and age(to_timestamp(topic_time))interval '7 days'; -- select count(topic_id) as old topics: from old_topics; insert into old_posts select p.post_id from phpbb_posts p, old_topics t where p.forum_id=5 and p.poster_id=1 and p.topic_id=t.topic_id; -- select count(post_id) as old posts: from old_posts; delete from phpbb_posts where post_id in (select post_id from old_posts); delete from phpbb_topics where topic_id in (select topic_id from old_topics); update phpbb_config set config_value = (select count(topic_id) from phpbb_topics) where config_name = 'num_topics'; update phpbb_config set config_value = (select count(post_id) from phpbb_posts) where config_name =
Re: [GENERAL] Analyze tool?
Rob Sargent skrev 2010-10-01 15.43: Then to get all statements would one simply set log_min_duration to some arbitrarily small value? From default postgresql.conf comments: -1 is disabled, 0 logs all statements and their durations, 0 logs only statements running at least this number of milliseconds Also see 18.7.2 in docs: http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html Regards /roppert On 10/01/2010 04:30 AM, Thom Brown wrote: 2010/10/1 Bjørn T Johansenb...@havleik.no: We are using both DB2 and PostgreSQL at work and DB2 has a nice tool, i5 Navigator, where one can enable logging of SQL statements and then it will recommed indexes that should/could be created to increase speed... Does there exist a similar tool for PostgreSQL? You can set log_min_duration_statement to log statements which take over a certain amount of time, and then use pgFouine to read the log files and identify the most frequently run queries, and the longest queries. You can also use the auto_explain contrib module (http://www.postgresql.org/docs/9.0/static/auto-explain.html) to log the plans of queries which take too long. However, I don't think pgFouine can use those outputs.. at least not yet. But to find out what indexes you'll need, getting used to reading query plans will help as it will show you more than just where sequentials scans are taking place. It will also show you what the planner believes a query will cost compared to how much it actually costs, which can provide insight into tables which require vacuuming, indexes which might need clustering, or table stats which require modifying to match you data. There might be a tool out there for PostgreSQL like you describe, although I'm not personally aware of it. -- 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] Cognitive dissonance
Leif Biberg Kristensen skrev 2010-06-10 17.33: On Thursday 10. June 2010 17.24.00 Tom Lane wrote: Alvaro Herreraalvhe...@commandprompt.com writes: Excerpts from Peter Eisentraut's message of jue jun 10 02:50:14 -0400 2010: As I said back then, doing this is straightforward, but we kind of need more than one user who asks for it before we make it part of a regular service, which comes with maintenance costs. Hey, count me as another interested person in a single-file plain-text doc output format. Well, there are two separate things here: * providing a Makefile target to build plain-text output. * shipping prebuilt plain text docs in standard distributions. I am for #1, not so much for #2, mainly on the grounds of size. But given #1 it would be possible for packagers to make their own choices about whether to include plain-text docs. Wouldn't it suffice to make it downloadable, like the pdf doc? And/or make the HTML version downloadable side by side with the PDF. There are good reasons for wanting access to the complete document when being offline. PDF is not such a bad format but it do have some limitations as have been previously mentioned. As for building the docs I don't think everyone, not even all developers, has the tool chain installed (or even wants to). Regards, roppert regards, -- 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] Pre-calculate hash join
Dimitri Fontaine skrev 2010-01-12 12.01: war...@warrenandrachel.com writes: When joining two large tables [common in warehousing], a hash join is commonly selected. Calculating hash values for the merge phase is CPU intensive. Is there any way to pre-calculate value hashes to save that time? Would it even grant any performance to skip the build phase of the hash join? Maybe maintaining a materialized view then using it in the queries would be sufficient to solve your problem? Depending on your case creating a temporary table in the beginning of the session might save you a lot of time if multiple similar queries is done. Regards, roppert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Curious index selection when using a date range
I encountered a curious thing today. Simple select queries against a fairly large, ~60M rows, and active, both in reading and writing, suddenly were aweful slow, from milliseconds into 10th of seconds. Looking a bit closer revealed that on a date condition having a between 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was choosen while if the year was switched to 2009 a composed index making use of the other condition parameters as well was choosen. After this we ran vacuum analyze on the table which solved the issue with the composed index getting used for the current year as well. Does anyone of you guys have a good explanation for this behaviour? I'm only guessing about why the planner made this choice. I'm well aware of not supplying enough details about our scenario but I'm disconnected at the moment and I'm asking just out of curiousity (and hope I wont meet the same faith as the cat did). Sincerly, roppert -- 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] Curious index selection when using a date range
Scott Marlowe skrev 2010-01-03 22.03: On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjörob...@blogg.se wrote: I encountered a curious thing today. Simple select queries against a fairly large, ~60M rows, and active, both in reading and writing, suddenly were aweful slow, from milliseconds into 10th of seconds. Looking a bit closer revealed that on a date condition having a between 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was choosen while if the year was switched to 2009 a composed index making use of the other condition parameters as well was choosen. After this we ran vacuum analyze on the table which solved the issue with the composed index getting used for the current year as well. Assuming the analyze part is what fixed this, then the problem is you're analyzing often enough. Got autovac on? What version of pgsql are you running? We're using autovaccum and running PostgreSQL 8.4.1, compiled with GCC 4.3.4, on Linux kernel 2.6.31 on x86_64 arch. /r -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table partitioning
While reading through the docs on Partitioning, http://www.postgresql.org/docs/current/static/ddl-partitioning.html, I got to wonder about the example given. The text in 5.9.2 states, item 1 in the first section, that the master table will contain no data. This is later confirmed by the example code where the trigger function, measurement_insert_trigger, redirects inserts to child tables and, failing to do so, raises an exception. Is there a reason to avoid populating the master table other that the cost of the data always being included when querying? My concern is that I would like to have the ELSE clause in measurement_insert_trigger just raise a log message and then return NEW. Am I missing some huge drawback? Regards, roppert -- 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 select temp table
On 2009-05-06 14.34, liuzg4 liuzg4 wrote: ver 8.4 i create two table with same name named 'testtable' one is temp table i select * from testtable then this table is a public or a temp ??? Temp. To access public use select * from public.testtable. Temporary tables exist in a special schema, so a schema name cannot be given when creating a temporary table. See http://www.postgresql.org/docs/8.4/static/sql-createtable.html for details. /r drop table testtable then is this table a public or a temp? i test ,it drop temp table fisrst! so am i must show public or pg_temp_1 when i has two table with same name !!! -- 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 size of your PostgreSQL Database?
Mark Roberts wrote: 1. 2.5-3TB, several others that are of fractional sisize. ... 5. They do pretty well, actually. Our aggregate fact tables regularly join to metadata tables and we have an average query return time of 10-30s. We do make some usage of denormalized mviews for chained/hierarchical metadata tables. Just out of curiosity, how do you replicate that amount of data? ... A few notes: our database really can be broken into a very typical ETL database: medium/high input (write) volume with low latency access required. I can provide a developer's view of what is necessary to keep a database of this size running, but I'm under no illusion that it's actually a large database. I'd go into more details, but I'd hate to be rambling. If anyone's actually interested about any specific parts, feel free to ask. :) I'd be very interested in a developers view of running and maintaining a database this size. Mostly what choices is made during development that might have been different on a smaller database. I'm also curious about the maintenance needed to keep a database this size healthy over time. Regards, /roppert Also, if you feel that we're doing something wrong, feel free to comment there too. :) -Mark -- 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] Is this good spec for a PostgreSQL server?
Bjørn T Johansen wrote: It's a Dell server with the following spec: PE2950 Quad-Core Xeon E5335 2.0GHz, dual 4GB 667MHz memory 3 x 73GB SAS 15000 rpm disk PERC 5/I Integrated controller card (8 ports, 256MB cache, battery backup) x 6 backplane Is this ok to run PostgreSQL 8.2.x and Tomcat on? And does anyone know if this PERC controller is supported under Linux (not heard of it before...) I've been running Gentoo Linux on a PE2950 with PERC 5 controller, so yes Linux runs on it. (Not sure about the I... not sure in what flavor the PERC 5 exists.) Regards, Roppert Regards, BTJ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Table description
Livia Santos wrote: Hi. Is there any command that describe a table, such as desc table_name as in Oracle? Not sure how desc table_name works in Oracle, but from psql you can use: \dt table_name Issue \? in psql for more information. Regards, roppert Thanks in advance. -- Lívia Silva Santos ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Partitioning
Julio Cesar Sánchez González wrote: Hi guys, It's natural what master table in the partitioning table contain data (http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html) ? or to be empty. Hi, the master table should be empty when doing partitioning. Regards, roppert Thanks for all. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
