[GENERAL] CMS - portal server Question
Hi, i am thinking of deploying a CMS system for our corporate web server. I have seen/installed/tested : Jboss Portal : Seeems good and standards complying but too unstable Apache Lenya : Very unstable - could not get it to any useful work Php-nuke : (despite our favor towards java, this seems stable but not what we want) OpenCMS : Very stable but not so open source - some one has to pay to get anything more than the basic Practically from just browsing and using google one could conclude that there are 10ths of open source tools that do content management. Also it is impractical to install even 10% of them. Moreover web articles/reviews/comparisons rarely give anything but biased views. So since i think that our type of need falls in the same big family as the ones working in the unix/PostgreSQL/java world i would like to ask you about your experience in this field. Since we dont consider interfering with the DB backend in our immediate future intentions, MySQL will be just as good for us. Ofcourse i prefer postgresql for our applications, but if lets say opencms started supporting postgresql just a month ago, then postgresql will not be as strong a candidate in that case. In the same sense java is prefered over PHP, since we dont intent to interfere with CMS code, but if some killer PHP app does the job, then it will be the one selected. Thanx Achilleas Mantzios. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Postgresql 7.4 migration to (partially) new disks
Hi, Our main postgresql/jboss/lotus notes server is configured as follows. OS : Debian GNU linux 3.0 PgSQL: 7.4.13 The FS structure of the system has as follows: Filesystem 1k-blocks Used Available Use% Mounted on /dev/sda1 9614116 6528132 2597612 72% / /dev/sdf 10321208 5801628 3995292 60% /raid2 /dev/sdg 6956424 4720060 1882988 72% /raidlog where /dev/sda1 is the boot SCSI disk, while /dev/sdf and /dev/sdg reside on two external EMC logical disks connected with qlogic interfaces. PgSQL is installed at the default location /usr/local/pgsql, data is on the ~ of postgres user : /var/lib/pgsql/data My main DB's (dynacom) data are held in $PGDATA2 location at /raid2/var/lib/postgres-data also the commit log and transaction log directories are linked to: pg_clog - /raidlog/sma/var/lib/pgsql/data/pg_clog and pg_xlog - /raidlog/sma/var/lib/pgsql/data/pg_xlog We have planned to do the following task on this Sunday: Migrate from Debian GNU linux 3.0 to SUSE SLES 9 (thats just a wierd lotus notes requirement), and the sysadms have decided to do that on the same HW, EMC disk arrays, by only replacing the root (/) disk. The normal (safe) way to do that would be by following the normal backup/install/configure/restore path. However (just with any upgrade, and with lotus notes things get really scary at times), there is always the possibility that the whole upgrade procedure holds untill monday morning, when there would be an order from our boss to rollback to the old system, or maybe repeat the same procedure every night of the next days of the week until we succeed in Lotus Notes upgrade! In this scenario,If my new suse pgsql installation was some hours alive at the meantime, i would have to do the whole reverse backup/restore procedure again, and this normally takes several minutes to comlete. The DB is something about 2.5 Gbytes on .sql dump and 6 Gbytes on disk. So one thought passing thru was to keep the alive postgresql dirs without dumps/restores. That is to just retain the whole pgsql directory /var/lib/pgsql/data on both systems, by copying back and fourth, while leaving data $PGDATA2 (/raid2/var/lib/postgres-data) on the same EMC disks. That is no backup restore at all. (Providing ofcourse that the /var/lib/pgsql/data owner/group are also to be setup correctly). Does any one have done anything similar with (long term success), or does anyone sees any potential problems with the later approach? Thanx a lot for any thoughts. -- Achilleas Mantzios ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL 9.0 or 9.1 ?
Hello again! (i got my traditional email-address back!) we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little radical and go a little optimistic and greedy about it. I have been using 9.0 as a test system with no major flaws for quite some time as well. (but unfortunately without exploiting any of its new features) Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) So i am asking what would be better from your perspective to do? Go for 9.1? or stick to 9.0 and try to deploy it and take the most out of it? When is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.0 or 9.1 ?
Hello, we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little radical and go a little optimistic and greedy about it. I have been using 9.0 as a test system with no major flaws for quite some time as well. Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) So i am asking what would be better by your perspective to do? Go for 9.1? or stick to 9.0? Where is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 Achilleas Mantzios
Re: Fw: [GENERAL] PostgreSQL 9.0 or 9.1 ?
Thanx, i think i'll just stick with 9.0 and try to take full advantage of it and when we are comfortable with all those features then move to 9.1 From: Merlin Moncure mmonc...@gmail.com To: Achilleas Mantzios mantzios.ach...@yahoo.com Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Thursday, June 16, 2011 7:12 PM Subject: Re: [GENERAL] PostgreSQL 9.0 or 9.1 ? On Thu, Jun 16, 2011 at 2:47 AM, Achilleas Mantzios mantzios.ach...@yahoo.com wrote: Hello, we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little radical and go a little optimistic and greedy about it. I have been using 9.0 as a test system with no major flaws for quite some time as well. Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) So i am asking what would be better by your perspective to do? Go for 9.1? or stick to 9.0? Where is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 Are you looking for any features that 9.1 has to offer?� If you aren't, it may make your decision easier.� Unfortunately there are several 9.1 features that are just awesome.� So, where you go from here is going to depend on your risk tolerance and (more importantly) your availability of testing resources.� Testing of production-ish workloads during the beta period are very much appreciated by the community, so feel free to give it a shot as long as you understand the risk involved (which are substantial). One big risk with 9.1 early adoption is that you run the risk of having to dump/reload if you go production while in the before the build hits release candidate status (and sometimes, even then).� So, if you are running a 24x7 duty cycle that's something to think about. merlin -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 or 9.1 ?
Thanx brad, i think 9.0 would be the most wise decision for the time being. Στις Thursday 16 June 2011 18:29:16 γράψατε: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz Sent: Thursday, June 16, 2011 11:05 AM To: Achilleas Mantzios Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 9.0 or 9.1 ? It could be worth considering 9.1. Probably by the time you get production ready version, 9.1 will be already stable (few months I guess). The usual answer to that question is - it will be ready when its ready. I would also ask, what is your (and your managements) tolerance for risk, and do you actually need any of the new features and/or performance benefits in 9.1? Postgres does have an excellent track record for quality and stability with new releases, but a couple of months in the field isn't really considered stable in most places. Brad. -- Achilleas Mantzios -- 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] An amusing MySQL weakness--not!
Στις Sunday 26 June 2011 17:11:06 ο/η Vincent Veyron έγραψε: Le dimanche 26 juin 2011 � 00:05 -0700, Darren Duncan a �crit : Michael Nolan wrote: Having real BOOLEAN is just one of the reasons I like Postgres the most. Would you mind giving an example of where a boolean field would be a win over an integer one? I'm asking this because I frequently wonder what is best for my use; I normally query postgres via Perl modules, which don't care about boolean (the driver converts t/f to 0/1), but I like to tune my fields properly. Alright, but in other enterprise-level platforms, like java, there is a boolean data type and a Boolean class wrapper. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique -- Achilleas Mantzios -- 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] Choosing primary key type: 64 or 52 bit primary keys?
bigint by all means. floating point arithmetic is somewhat more bloated/fuzzy/straight forward than integer, and even if postgresql was perfect regarding floating point comparisons, no one can claim the same for client languages. So define your PK as bigint. Στις Friday 22 July 2011 10:01:58 ο/η Antonio Vieiro έγραψε: Hi all, I'd like to use an integer number for my primary key. I need it to be bigger than 32 bits. As far as I understand I have two options: a) use all the 64 bits of a 'bigint' b) use the 52 mantissa bits of a 'double precision' My question is, which would be faster for indexing? I assume the bigint wins here, right? Thanks in advance, Antonio -- Achilleas Mantzios -- 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] Choosing primary key type: 64 or 52 bit primary keys?
Στις Friday 22 July 2011 13:25:21 ο/η Achilleas Mantzios έγραψε: bigint by all means. floating point arithmetic is somewhat more bloated/fuzzy/straight forward than integer, ^^ oops sorry i mean less straight forward and even if postgresql was perfect regarding floating point comparisons, no one can claim the same for client languages. So define your PK as bigint. Στις Friday 22 July 2011 10:01:58 ο/η Antonio Vieiro έγραψε: Hi all, I'd like to use an integer number for my primary key. I need it to be bigger than 32 bits. As far as I understand I have two options: a) use all the 64 bits of a 'bigint' b) use the 52 mantissa bits of a 'double precision' My question is, which would be faster for indexing? I assume the bigint wins here, right? Thanks in advance, Antonio -- Achilleas Mantzios -- Achilleas Mantzios -- 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] Disallow access from psql, or allow access only from specific client app
Στις Monday 25 July 2011 16:08:53 ο/η Sim Zacks έγραψε: A lot of applications don't actually have a database role per user. There is an application user who logs into the database and the application handles application logins through a users table in the database. That way the only thing that the user has access to is the application and not the database. That was the dominating fashion for some 10+ years, but if you think of it, it is totally pathetic to have broken the link from the actual user to the underlying DB. We have reverted the whole security setup to using distinct user credentials/user, which are delegated by the application (jboss). So the user logs in once, and gets access with the same credential to both the app server and the DB. Now we can see exactly who is in, what is he doing, etc... what app is he/she running, etc... If you access the database through an application server, or server side script, you can limit access to only that (and other specific) IP addresses in the pg_hba.conf. I would support the same thing. Just enable only localhost access, prevent users from loging in this machine (by not making them any accounts) and you are done. This password hacking thing, has some problems, 1st it relies on security via obscurity, and second, it needs the authentication to be done programmatically from user code. What if the connections are in pools managed by pool managers? How easy would it be to hack the passwords/userids this way? Sim On 07/25/2011 02:56 PM, Mario Puntin wrote: Thanks a lot to everybody for the replies. Kurt: by the term client I meant an application, like psql, from which users could connect to the database, as they have a user/password, and manipulate data. I want them to access from certain, specific application, but I don't want them to install psql, for example, and have also access through it. I liked Chris Carver idea of modifing user/password: quite simple and efective. But thank you all people, you gave me some fine ideas. Best regards, Mario -- Achilleas Mantzios -- 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] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
Just a thought, why don't you deploy your setup in your home computer, and make it accesible from the inet via your home router? Just make a software raid, get a nice UPS unit, try to harden your OS a little bit, and run your apps there. It could prove a nice free alternative until anything that looks like a free HAAS/PAAS shows up. I know it sounds old fashioned, now in the days of the cloud frenzy, but there must be some value in doing all this by yourself, even if it goes against the trends. Στις Sunday 07 August 2011 17:50:44 ο/η Fernando Pianegiani έγραψε: On Sun, Aug 7, 2011 at 4:22 PM, David Johnston pol...@yahoo.com wrote: this is very difficult, but it is exactly what I am doing in environments different from this one. Even if this risks to be considered (not so positively) as a request of charity... :-) At that point, unless you have confidentiality requirements, why not just tell everyone what it is you are working on and see if anyone responds favorably? It woul normally be deemed off-topic but at this point one more non-Postgresql post isn't going to make a big difference on this thread. Fundraising for a cause is quite a bit different than asking for a personal gift and it sound like your request falls into the former category. Dear David, thank you for your post. I have not posted exactly a non-PostgreSQL post, in fact I asked for information about possible services of free hosting platforms with PostgreSQL installed. I repeat that I didn't ask for a hosting platform but for information about possible inherent free services. The item of research focuses on the remote detection of events of health hazard, like in particular the cardiac atrial fibrillation, by wireless sensors installed on the body of the patient and a phone that forwards the data towards the hosting. If somebody can be interested I pray him to ask me for more information writing just to my email address. Thanks a lot! David J. -- Achilleas Mantzios -- 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 get to know the current user account is superuser or not?
Στις Monday 08 August 2011 12:36:44 ο/η Siva Palanisamy έγραψε: Hi All, I was provided with a user account to access postgresql. I want to process some high-level operations that might need a superuser/admin privileged access. How to get to know the user account I was provided having what kind of privileges? It looks like I don't have super user account! And I want to confirm this at the earliest. SELECT * from pg_user; 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. --- -- Achilleas Mantzios -- 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] md5 of table
md5 has size limitations, the second approach seems more practical. Στις Thursday 01 September 2011 12:30:45 ο/η Karsten Hilbert έγραψε: On Thu, Sep 01, 2011 at 11:47:24AM +0300, Sim Zacks wrote: Is there a way to get an md5 or other hash of an entire table? I want to be able to easily compare 2 tables in different databases. I thought about using dblink and the EXCEPT query, but then I need to know the field list of each query result, which is a pain in the butt. If I could return an md5 of the entire table, then I could check if the tables have the same hash and be confident enough that the tables were identical. One option might be to pg_dump in an appropriate format and md5-compare the output ? Another option might be to - cross-check columns/column types - query from information_schema - compare row counts - may need a lock - compare table sizes - may need vaccum ? If all three match that may be good enough ? Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Achilleas Mantzios -- 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] md5 of table
Στις Thursday 01 September 2011 15:50:21 ο/η Scott Marlowe έγραψε: On Thu, Sep 1, 2011 at 3:48 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: md5 has size limitations, the second approach seems more practical. Really? I was not aware of size limits of md5, what are they? sorry, i was wrong. i dont know why i had this impression, just checked with a 43GB table on a freebsd machine and went fine. -- Achilleas Mantzios -- 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] conditional insert
Στις Monday 05 September 2011 12:38:34 ο/η Pau Marc Muñoz Torres έγραψε: Hi follk i trying to performe a conditional insert into a table, indeed, what i'm trying to do is not insert a record into the table if that record exist thats why primary/unique keys are for. isolate the columns which you consider to be a correct unique key and create a unique key on them. thereis no notion of conditional insert that i know of. googleling i found something like insert into XX values (1,2,3) where not exist (select ); but i'm having and error near where... anyone knows how do i can perfome this insert? thanks p -- Achilleas Mantzios -- 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 a PostgreSQL book
The cook book is indeed helpful, but i doubt if it was ever properly reviewed. Many typos, many apparent errors, code for the cache utils pg_cacheutils is mentioned to exist somewhere but it's not there, and many more Στις Thursday 29 September 2011 08:07:34 ο/η Venkat Balaji έγραψε: Thanks Adam ! Regards, VB On Thu, Sep 29, 2011 at 12:03 AM, Adam Cornett adam.corn...@gmail.comwrote: The same publisher (Packt) has a book *PostgreSQL 9 Administration Cookbook* by Simon Riggs and Hannu Krosing that is equally useful as Greg's *High Performance* book On Wed, Sep 28, 2011 at 1:14 PM, Venkat Balaji venkat.bal...@verse.inwrote: Hello Everyone, I have been working on PostgreSQL for quite a while (2 yrs) now. I have got PostgreSQL 9.0 High Performance book and quite excited to go through it. Please let me know any source where i can get more books on PG, I am especially looking for books on PG internals, architecture, Backup Recovery and HA. Looking forward for the information. Regards, VB -- Adam Cornett adam.corn...@gmail.com (678) 296-1150 -- Achilleas Mantzios -- 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] I don't understand something...
Alexander, that's a classic one,
rewrite your last query as :
SELECT count(employee_id) from employees where employee_id not in
(select manager_id from employees WHERE manager_id IS NOT NULL);
NULLS semantics are sometimes not so obvious.
Στις Monday 03 October 2011 09:33:12 ο/η Alexander Pyhalov έγραψε:
Hello.
I was asked a simple question. We have table employees:
\d employees
Table public.employees
Column |Type |
Modifiers
+-+-
employee_id| integer | not null default
nextval('employees_employee_id_seq'::regclass)
first_name | character varying(20) |
last_name | character varying(25) | not null
email | character varying(25) | not null
phone_number | character varying(20) |
hire_date | timestamp without time zone | not null
job_id | character varying(10) | not null
salary | numeric(8,2)|
commission_pct | numeric(2,2)|
manager_id | integer |
department_id | integer |
Indexes:
employees_pkey PRIMARY KEY, btree (employee_id)
emp_email_uk UNIQUE, btree (email)
emp_department_ix btree (department_id)
emp_job_ix btree (job_id)
emp_manager_ix btree (manager_id)
emp_name_ix btree (last_name, first_name)
Check constraints:
emp_salary_min CHECK (salary 0::numeric)
Foreign-key constraints:
employees_department_id_fkey FOREIGN KEY (department_id)
REFERENCES departments(department_id)
employees_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(job_id)
employees_manager_id_fkey FOREIGN KEY (manager_id) REFERENCES
employees(employee_id)
Referenced by:
TABLE departments CONSTRAINT dept_mgr_fk FOREIGN KEY
(manager_id) REFERENCES employees(employee_id)
TABLE employees CONSTRAINT employees_manager_id_fkey FOREIGN
KEY (manager_id) REFERENCES employees(employee_id)
TABLE job_history CONSTRAINT job_history_employee_id_fkey
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
Now we want to select count of all employees who doesn't have any
subordinates (query 1):
SELECT count(employee_id) from employees o where not exists (select 1
from employees where manager_id=o.employee_id);
count
---
89
(1 row)
We can select count of all managers (query 2):
SELECT count(employee_id) from employees where employee_id in (select
manager_id from employees);
count
---
18
(1 row)
But if we reformulate the first query in the same way, answer is
different (query 3):
SELECT count(employee_id) from employees where employee_id not in
(select manager_id from employees) (query 3);
count
---
0
(1 row)
I don't understand why queries 1 and 3 give different results. They
seems to be the same... Could someone explain the difference?
--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University
--
Achilleas Mantzios
--
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] Savepoint or begin
You might scrap all BEGIN/COMMIT/ROLLBACK stmts, and run your upgrade_all.sql as psql --single-transaction -f upgrade_all.sql Στις Monday 03 October 2011 12:36:58 ο/η Anssi Kääriäinen έγραψε: I am having the following problem: I have upgrade scripts which are runnable one-by-one. I will also want to run all of them together. Example: table1.sql: begin; alter table table1 add column new_col1; alter table table1 add column new_col2; commit; table2.sql: begin; alter table table2 add column new_col1; alter table table2 add column new_col2; commit; upgrade_all.sql: begin; \i table1.sql \i table2.sql commit; If I run upgrade_all.sql, it will not be atomic, as table1.sql's COMMIT will commit half of the work and table2.sql's COMMIT will commit another half of the work. If there is an error when running table2.sql, this would commit half of the work and rollback half of the work. What I would like to do is something like: table1.sql: savepoint or begin s1; ... commit s1; If run outside transaction, this would be equivalent to table1.sql, that is SAVEPOINT OR BEGIN would create a new transaction, and COMMIT s1 would commit it. If run inside a transaction, this would create a savepoint and commit would not do anything. The syntax could of course be much better, but I hope this is enough to show what I am after. Is this doable already somehow? Am I doing my upgrade script structuring wrong? - Anssi Kääriäinen -- Achilleas Mantzios -- 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 Data directory Issue
Sorry to be rude, but you are in urgent need of a PostgreSQL DBA, (if you feel you somehow value your data). Anyways, 8.3 catalog is not compatible with 8.4. You need to dump-upgrade-restore if you just start 8.3 server with /usr/local/pgsql/bin/postgres -D path_to_your_data_dir what happens? Στις Wednesday 05 October 2011 15:05:26 ο/η Adarsh Sharma έγραψε: Dear all, I have a database server ( 10 databases near about 110 GB) running Postgresql-.8.3 ) Today I need to format that system but I an facing the below issues :- 1. I am trying to use the previous data directory (/opt/PostgresPlus/8.3/data) in new Postgresql-8.4 installation but it results in below error while installing :- Data Directory [/media/a519e535-62a8-43a2-af93-2323690a3177/opt/PostgresPlus/8.3/data]: Error: The existing data directory (catalog version: 200711281) is not compatible with this server (catalog version: 200904091). Press [Enter] to continue : So i think I need to install Postgresql-8.3 to use that data. 2. I installed Postgresql-8.3 in new server and trying to use the data directory but it faces the below error :- Existing data directory is not empty and it cannot able to use it . Please note that I am not able to take the complete databases backup because the database server is down and restarts when we start backups. Any help will be appreciated. Thanks -- Achilleas Mantzios -- 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] null values in a view
try: CREATE OR REPLACE VIEW view1 AS SELECT name as a1, null::text as a2, 'test'::text as a3 FROM some_table; Στις Wednesday 05 October 2011 17:22:21 ο/η Lauri Kajan έγραψε: Hi all, How could I create a view that returns null values among all other values. Here is a sample that i want to achieve: CREATE VIEW view1 AS SELECT attribute1 as a1, null as a2 FROM table; Now the problem is that I got an warning: column a2 has type unknown I know that I should define a data type for a field a2. But how? This works with other values but not with nulls: CREATE VIEW view1 AS SELECT attribute1 as a1, text null as a2, text 'test' as a3 FROM table; Thanks -Lauri Kajan -- Achilleas Mantzios -- 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] Global Variables?
It would be interesting if the parameters/settings framework could be extended to provide session/table/user/database level custom settings, accessible via the SET/SHOW/RESET commands. Is there anything like this ever been considered/discussed ? Στις Tuesday 11 October 2011 17:06:50 ο/η Eric Radman έγραψε: When writing unit tests it's sometimes useful to stub functions such as the current date and time -- define mock functions CREATE OR REPLACE FUNCTION _now() RETURNS timestamp with time zone AS $$ BEGIN RETURN '2011-10-10 10:00'; END; $$ LANGUAGE plpgsql; -- define tables accounts CREATE TABLE accounts (username varchar, expiration timestamp); -- populate with sample data COPY accounts FROM '/home/eradman/sample_accounts.txt'; -- define view expired_accounts CREATE OR REPLACE VIEW expired_accounts AS SELECT * FROM accounts WHERE expiration _now(); -- test views SELECT assert(0, (SELECT count(*) FROM expired_accounts)::integer); Is it possible to declare a global variable that can be referenced from the user-defined function _now()? I'm looking for a means of abstraction that allows me to avoid issuing CREATE OR REPLACE FUNCTION ... before each assert() current_time := '2012-01-01'::timestamp SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer); -- Eric Radman | http://eradman.com -- Achilleas Mantzios -- 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] installing tsearch2
tsearch2 support is included in the 9.1 distribution. No need for any additional packages. However, if you need to use the old functions, then you will have to install the Backwards-compatibility package for old contrib/tsearch2 API in path_to_your_source/contrib/tsearch2/ gmake gmake install then psql -f path_to_your_source/contrib/tsearch2/sql/tsearch2.sql Στις Wednesday 19 October 2011 12:15:34 ο/η robert έγραψε: Hi there, this is my first post to this list.. I have been a casual user of postgres for the past 2-3 years but was never involved in installation and such. Now I must help the migration of a 8.3 system to 9.1. The problem we face is that the actual system is using tsearch-2 which was allready posted from an 8.1 installation. My questions now are: - how do I install tsearch2 I compiled the 9.1 source an executed make all / install in the contrib directory now I find there a tsearch2--1.0.sql and tsearch2--unpackaged--1.0.sql file. Do I have to execute them? Both ? - in the dump of the old database there are references to gtsvector_in and gtsvector_out and similar of which I find nothing in gtsvector_out are these _in/_out objects needed anymore? can I overlook the errors when importing the old dump? thanks for your time robert -- Achilleas Mantzios -- 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] installing tsearch2
Στις Wednesday 19 October 2011 16:46:55 ο/η Tom Lane έγραψε: Achilleas Mantzios ach...@matrix.gatewaynet.com writes: tsearch2 support is included in the 9.1 distribution. No need for any additional packages. However, if you need to use the old functions, then you will have to install the Backwards-compatibility package for old contrib/tsearch2 API in path_to_your_source/contrib/tsearch2/ gmake gmake install then psql -f path_to_your_source/contrib/tsearch2/sql/tsearch2.sql I think part of the OP's problem is that that advice is obsolete. In 9.1 you'd do CREATE EXTENSION tsearch2, not feed a file to psql manually. In his case, i think the dump from 8.3 will carry over those functions (the sql part) regardless. But the bigger picture is that pre-8.3 tsearch2 is obsolete and you should be trying to get rid of it not port it. See http://www.postgresql.org/docs/9.1/static/textsearch-migration.html regards, tom lane -- Achilleas Mantzios -- 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] specifying multiple ldapserver in pg_hba.conf
Στις Thursday 27 October 2011 11:00:10 ο/η Magnus Hagander έγραψε: On Wed, Oct 26, 2011 at 23:00, Darin Perusich darin.perus...@ctg.com wrote: Are you able to specify multiple ldapservers in pg_hba.conf and if so what is the format? I'd like to be able to build some redundancy incase one of the ldap servers goes down. This is unfortunately currently not possible. To do this, you need to set up some IP level redundancy for your LDAP. Thats true. We had the same issue, and ended up doing the redundancy via DNS and a cron job which checks all LDAP servers, choses an alive server, and modifies bind's config files accordingly. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Achilleas Mantzios -- 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] Optimize the query, help me please.
Στις Wednesday 23 November 2011 12:56:23 ο/η Капралов Александр έγραψε: Query is: SELECT * FROM a UNION SELECT * FROM b ORDER BY time DESC LIMIT 100 how can i get only last 100 row from a and b and then do union. Explain of select said that all recond selected from a and b. In order to get 100 last rows from a and 100 last rows from b do: (SELECT * FROM a ORDER BY time LIMIT 100) UNION ALL (SELECT * FROM b ORDER BY time LIMIT 100); omitting the ALL modifier, you may end up with less rows, since UNION normally returns only distinct rows. thanks. -- Achilleas Mantzios -- 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 DBA in SPAAAAAAAACE
lol! Στις Tuesday 06 December 2011 20:02:40 ο/η Bèrto ëd Sèra έγραψε: +1 say hello to Laika, if she's still there :) Bèrto On 6 December 2011 20:33, Torello Querci tque...@gmail.com wrote: 2011/12/6 Merlin Moncure mmonc...@gmail.com: On Tue, Dec 6, 2011 at 10:56 AM, Joe Miller joe.d.mil...@gmail.com wrote: You may have seen this, but RedGate software is sponsoring a contest to send a DBA on a suborbital space flight. And there is a PostgreSQL representativeme! https://www.dbainspace.com/finalists/joe-miller Voting is open for 7 days. Don't let one of those Oracle or SQL Server punks win :p so jealous -- I didn't make the cut. Well, you'll have my vote. me too :) merlin -- 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 -- Achilleas Mantzios -- 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] Pgsql problem
On Πεμ 12 Ιαν 2012 12:51:00 pasman pasmański wrote: Hi. I write function in pgsql. This function needs to execute other functions by name. I do it using loop: declare r record; begin for r in execute 'select ' || $1 || '()' loop end loop; But I can't convert a record to array of text. How to do it ? Maybe you should a look at array_agg. It is an aggregate function operating on a set of values and returning the resulting array. http://www.postgresql.org/docs/9.0/interactive/functions-aggregate.html pasman -- Achilleas Mantzios IT DEPT -- 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] Incomplete startup packet help needed
We have it too. I think it might be samba related, or just some SNMP software running. I had indentifed the root of the situation some years ago, do not remember it now. It is 100% harmless. On Τρι 24 Ιαν 2012 10:26:25 Florian Weimer wrote: * David Johnston: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. Like this? 2012-01-23 10:42:55.245 UTC 11545 LOG: database system is ready to accept connections 2012-01-23 10:42:55.245 UTC 11549 LOG: autovacuum launcher started 2012-01-23 10:42:55.268 UTC 11551 [unknown] [unknown] LOG: incomplete startup packet I think it's harmless, it's been there for years. It might be related to the init script that starts the database server. -- Achilleas Mantzios IT DEPT -- 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] Don't Thread On Me (PostgreSQL related)
On Τρι 07 Φεβ 2012 07:05:00 John R Pierce wrote: On 02/03/12 5:53 PM, Rodrigo E. De León Plicet wrote: Author's followup: http://drcoddwasright.blogspot.com/2012/02/damn-you-damocles.html his links hardly seem related to his proclamations. From the guy's blog: Shameless Plug I know a little something about SQL, DB2 (preferably LUW, z/OS if the money's right), Oracle, SQL Server, Postgres, and database design in general. z/OS? It is apparent that this person has a mainframe background, and the rest of the keywords he mentions are just buzzwords he has little idea about... Believe me, I was an ex- IBM MVS sysprog (+10 yrs ago) and struggled to get back into the BSD/linux world. One cannot think unix/mainframe at the same time, the concepts are so different, they are nearly mutually exclusive. -- Achilleas Mantzios IT DEPT -- 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] phantom long-running query (check_postgres.pl reports a long-running query, but the database logs don't show it)
On Πεμ 09 Φεβ 2012 02:32:37 Aleksey Tsalolikhin wrote: I got an alert from check_postgres.pl today on a long-running query on our production database, but our PostgreSQL 8.4.9 server log, which is configured to log queries over 5 seconds long (log_min_duration_statement = 5000) does not show the query. check_postgres.pl showed: Date/Time: Wed Feb 8 08:41:36 PST 2012 POSTGRES_QUERY_TIME WARNING: (host:xxx) longest query: 264s (database:xxx PID:xxx port:xxx address:xxx username:xxx) postgres log showed other long-running queries, but nothing over 48s. It's really interesting why the database server log does not show this query. Can you think of some scenario where check_postgres.pl would see a long-running query but it would not get logged by the database server? I checked the server log and there is nothing there for 08:41 at all. 08:40 and 08:42, yes, some queries over 5 secs, but nothing even close to 264 secs. I've added the verbose switch to my check_postgres.pl script so we'll have a record of what was the long-running query. You should either look at the source of this perl script to see what it is doing, or enable log_statement = 'all' in postgresql.conf and send the postmaster the -HUP signal, and then watch the postgresql log for the queries that the perl script is issueing. Then replay those queries by hand and examine them. Also you can always check pg_stat_activity table. Best, -at -- Achilleas Mantzios IT DEPT -- 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 erase transaction logs on PostgreSQL
one ultra dummy way would be to dump, back up, destroy the data dirs, and any human /var/log files and then re-initdb and restore. On Πεμ 08 Μαρ 2012 12:18:17 Frank Church wrote: How do you purge the postgresql transaction log? I am creating a virtual machine image and I want to erase any transaction logs that got built up during development. What is the way to do that? I am currently using 8.3 and 8.4. Is there the possibility that the logs saved in /var/log also contain security details? /voipfc -- Achilleas Mantzios IT DEPT -- 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] Fine-grained replication?
On Τρι 19 Ιουν 2012 13:09:28 Guillaume Lelarge wrote: On Mon, 18 Jun 2012 14:49:32 -0700 (PDT), Paul Jones p...@cmicdo.com wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? If you want fine grained replication, it means you won't be able to use log shipping and streaming replication. If you don't want trigger based replication, you won't have many options still available. pgPool comes to mind but, AFAIK, it isn't fine grained (and, to be honest, I would say it isn't really replication). So, nope, sorry. BTW, what's the issue with trigger-based replication? it really helps a lot in many cases. Totally agree, for us, a heavily modified version of (what initially was) DBmirror has made the job more than well. Also, our replication is the most fine-grained situation i can think of. (even rows being lazily replicated based on the FK dependency graph, and many other nice tricks) - Achilleas Mantzios IT DEPT -- 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] Fine-grained replication?
On Τρι 19 Ιουν 2012 12:00:49 Sergey Konoplev wrote: On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones p...@cmicdo.com wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? Binary Replication? http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial Alright, but the OP wrote about fine-grained. IMHO log-based and fine-grained seem a liitle but mutually conflicting. Thanks, Paul Jones -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general - Achilleas Mantzios IT DEPT -- 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] Where should I start for learn development
No!! You just install it as package. Read the docs from the official site. Try to find your way through the docs. Reading the docs should prevent asking such trivial questions in the first place. Why do you want to involve Eclipse in this? On Τρι 03 Ιουλ 2012 17:50:44 AI Rumman wrote: Hello, I have been working with Postgresql for the last 3 years. Before that I worked with Oracle, Mysql and other databases. Now, its time to learn the internals of Postgresql system. I downloaded the source code and imported it in my eclipse environment. But I have very limited knowledge on C programming. Could you guys please guide me from where I should start? Thanks. - Achilleas Mantzios IT DEPT
Re: [GENERAL] Different results from view and from its defintion query [w/ windowing function]
first_value refers to the first row from the window frame. Unless you force some kind of ordering, you cannot expect consistent results out of this. See the PARTITION BY ... ORDER BY syntax in http://www.postgresql.org/docs/9.1/static/tutorial-window.html On Δευ 20 Αυγ 2012 01:55:38 Thalis Kalfigkopoulos wrote: Sorry for the lack of a more appropriate title. The summary of my problem is: i run a query and I get some results; then I create a view using this query, and I run the same query on the view, and get different results. Details follow. On the original table the analytical data is as follows: # SELECT id,experiment,insertedon,score FROM data WHERE id=1160; id |experiment| insertedon | score +--+-+ 1160 | alpha | 2012-08-19 01:01:12 | 220.69 1160 | alpha | 2012-08-19 01:01:22 | 220.69 1160 | beta | 2012-08-19 01:01:31 | 220.7 1160 | beta | 2012-08-19 01:01:42 | 220.7 1160 | beta | 2012-08-19 01:01:54 | 220.7 My query of interest using windowing functions is: # SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY score, id) AS first_insertedon, score FROM data WHERE id=1160; id |experiment| first_insertedon | score | +--+-++-- 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | beta | 2012-08-19 01:01:31 | 220.7 | 1160 | beta | 2012-08-19 01:01:31 | 220.7 | 1160 | beta | 2012-08-19 01:01:31 | 220.7 | So far so good. I then create the view on this last query without the WHERE condition: # CREATE VIEW clustered_view AS SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY score, id) AS first_insertedon, score FROM data; I see the view created correctly and its definition is according to the mentioned SQL query. I now select from the view adding the WHERE condition: # SELECT * from clustered_view WHERE id=1160; id |experiment | first_insertedon | score | +--+-++ 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | beta | 2012-08-19 01:01:54 | 220.7 | 1160 | beta | 2012-08-19 01:01:54 | 220.7 | 1160 | beta | 2012-08-19 01:01:54 | 220.7 | As you see, the 'first_insertedon' timestamp for the experiment 'beta' is no longer the first of the timestamps i.e. '2012-08-19 01:01:31' as the original query's results gave correctly, but it's now the last one i.e. '2012-08-19 01:01:54' Any ideas? Missing the obvious? TIA, Thalis K. - Achilleas Mantzios IT DEPT
Re: [GENERAL] psql unix env variables
I have found useful the use of variable assignment in psql, e.g. #!/bin/sh # lets say you have some var with a value, or even populate some var with a value from # psql as shown below somevar=`psql -P pager=off -q -t -c SELECT foo from bar | head -1 | sed -e 's/ //g'` # now use that variable in psql, (what you want to achieve), but in more tight manner # than simple shell substitution (see -v switch and : notation) psql -P pager=off -q -v somevar=$somevar -c select foo2 from bar2 where var=:somevar On Î¤ÎµÏ 29 ÎÏ Î³ 2012 15:00:29 Little, Douglas wrote: Thanks -Original Message- From: Ryan Kelly [mailto:rpkell...@gmail.com] Sent: Wednesday, August 29, 2012 12:41 PM To: Little, Douglas Cc: PostgreSQL General (pgsql-general@postgresql.org) Subject: Re: [GENERAL] psql unix env variables On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote: Is there a method for having unix env variables incorporated into a psql sql statement? Ie Export var='dev' Psql =c 'select count(*) from $var.customer;' export FOO=bar psql -c select count(*) from $FOO.customer; Note the double quotes. That allows your shell to interpolate the string into your query. Note that the interpolation is done by your shell, and not psql. Doug Little Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ -Ryan Kelly - Achilleas Mantzios IT DEPT -- 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] psql unix env variables
On Î Î±Ï 31 ÎÏ Î³ 2012 09:19:26 Chris Angelico wrote: On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios ach...@smadev.internal.net wrote: I have found useful the use of variable assignment in psql, e.g. #!/bin/sh # lets say you have some var with a value, or even populate some var with a value from # psql as shown below somevar=`psql -P pager=off -q -t -c SELECT foo from bar | head -1 | sed -e 's/ //g'` # now use that variable in psql, (what you want to achieve), but in more tight manner # than simple shell substitution (see -v switch and : notation) psql -P pager=off -q -v somevar=$somevar -c select foo2 from bar2 where var=:somevar At this point, I have to ask: Why not switch to a language with actual Postgres bindings? Try Python, or Pike, or something; I'm sure it's going to be easier than doing everything through shell scripts. or perl, or php, or java, etc... actually we switched to java some 11 years ago to build our infrastructure, but occasionally (or not so occasionally, but rather being part of the architecture) still sh/bach/tcsh/perl are heavily used and have their place. Its all about taste/preference and not easily jumping into overkill mode. OTOH the OP asked for doing exactly what -v (--set) var assignments are supposed to do. (lift shell substitution ambiguities about escaping and var expansion) ChrisA - Achilleas Mantzios IT DEPT -- 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] psql unix env variables
On Παρ 31 Αυγ 2012 09:37:05 Craig Ringer wrote: On 08/30/2012 02:42 PM, Achilleas Mantzios wrote: I have found useful the use of variable assignment in psql, e.g. If you're going to to that, why not drive psql as a coprocess: because it is completely irrelevant with what the OP asked for. http://stackoverflow.com/a/8305578/398670 or if at all possible, use a language with sane PostgreSQL bindings. -- Craig Ringer - Achilleas Mantzios IT DEPT -- 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] psql unix env variables
On Î Î±Ï 31 ÎÏ Î³ 2012 09:19:26 Chris Angelico wrote: On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios ach...@smadev.internal.net wrote: I have found useful the use of variable assignment in psql, e.g. #!/bin/sh # lets say you have some var with a value, or even populate some var with a value from # psql as shown below somevar=`psql -P pager=off -q -t -c SELECT foo from bar | head -1 | sed -e 's/ //g'` # now use that variable in psql, (what you want to achieve), but in more tight manner # than simple shell substitution (see -v switch and : notation) psql -P pager=off -q -v somevar=$somevar -c select foo2 from bar2 where var=:somevar At this point, I have to ask: Why not switch to a language with actual Postgres bindings? Try Python, or Pike, or something; I'm sure it's going to be easier than doing everything through shell scripts. or perl, or php, or java, etc... actually we switched to java some 11 years ago to build our infrastructure, but occasionally (or not so occasionally, but rather being part of the architecture) still sh/bach/tcsh/perl are heavily used and have their place. Its all about taste/preference and not easily jumping into overkill mode. OTOH the OP asked for doing exactly what -v (--set) var assignments are supposed to do. (lift shell substitution ambiguities about escaping and var expansion) ChrisA - Achilleas Mantzios IT DEPT -- 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] psql unix env variables
On Παρ 31 Αυγ 2012 09:37:05 Craig Ringer wrote: On 08/30/2012 02:42 PM, Achilleas Mantzios wrote: I have found useful the use of variable assignment in psql, e.g. If you're going to to that, why not drive psql as a coprocess: because it is completely irrelevant with what the OP asked for. http://stackoverflow.com/a/8305578/398670 or if at all possible, use a language with sane PostgreSQL bindings. -- Craig Ringer - Achilleas Mantzios IT DEPT -- 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] Too far out of the mainstream
On Î¤ÎµÏ 05 ΣεÏÏ 2012 10:51:49 Ivan Sergio Borgonovo wrote: On Tue, 4 Sep 2012 19:14:28 -0700 Chris Travers chris.trav...@gmail.com wrote: So people are using PostgreSQL in roles that aren't very visible anyway, DBA's are usually coming to PostgreSQL from other RDBMS's, and few applications are really distributed for PostgreSQL. I know a bunch of people working for huge sites that love Postgres but use MySQL. The main reason is they build what Postgres is famous for at a higher level and in a more specialized way with their own glue. Postgresql has more meaning in the enterprise than in a web site. Web Content is never critical. The world will keep turning even if some CSS file or some article gets lost. They are meant to be transient any way. They are not part of anything bigger. Postgresql shines whenever data matters. I cannot imagine running our app (single master, 80+ slaves in 80+ vessels in the 7 seas (80+ = 80 and growning)) in mysql. We have not lost a single transaction. We have not had a single integrity issue. All problems were due to our own fault and never postgresql's. Runing a variaty of 7.4 / 8.3 mixture (unfortunately upgrading to 9+ is a very hard task to manage) (now all are on 8.3) we never had any issues. And the servers run unattended, in almost military (marine) conditions, with frequent blackouts, hardware failures due to vibration, disk failures, mother board failures, CPU failures, memory failures. Postgresql just delivered. And the thing is that postgresql really has no rivals either. No competitor when it comes to full-featured OSS RDBMS. There are OSS rdbms (mysql) and full featured rdbms (DB2/Oracle) but none besides pgsql which combines both worlds. Also, as far as extensibility is concerned, postgresql is clearly the king. It's easy to get visibility if you're on the internet and you're huge. But not everyone can rebuild eg. transactions at a higher level and need as much specialized solutions. On the other hand for historical reasons MySQL and PHP have nearly monopolized the hosting space and for many web sites it's hard to appreciate the difference between Postgres and MySQL (unless your DB crash and burn). That's what most people perceive as the mainstream if you don't have a big marketing dept lying. - Achilleas Mantzios IT DEPT -- 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] Too far out of the mainstream
On Î¤ÎµÏ 05 ΣεÏÏ 2012 23:44:08 Chris Angelico wrote: On Wed, Sep 5, 2012 at 7:40 PM, Achilleas Mantzios ach...@smadev.internal.net wrote: (single master, 80+ slaves in 80+ vessels in the 7 seas (80+ = 80 and growning)) Cool!! How do your nodes communicate with each other? Is it an off-line resynchronization, or do you maintain long-range (satellite?) comms? Hello, our topology is star-like. The system is based on good ol' UUCP running on top of either ISDN lines or (as of late) over TCP/IP. It is asynchronous and off-line by design. Vessels connect to the central master server and get all their data, receive replication updates, and also send their data to the office (central master cerver). UUCP does the management of the queues (for the unitiated, think of UUCP as something like JMS or AMQP or even better like JMS (API)+AMQP (wire protocol)) The comms (ISDN and TCPIP) are all done of course over a satellite service (very expensive, so compression and minimal data replication were/are and will be major concern) In the case of ISDN, the billing is by time, so clearly this had to fit in the off-line category. In the case of TCPIP, the billing is by data size, but we use that under UUCP just like the ISDN off-line asynchronous mode. Vessels can operate without connection to the office, and vice versa. The system I'm setting up at work kinda pales in comparison to that. It's designed to scale to infinity and beyond (and that quote is kinda appropriate, since we run this all on Debian Linux), but at the moment, all the testing I've done has been on a half-dozen off-the-shelf Dell laptops. But the same applies; we want absolute guaranteed reliability, so we NEED a good database. Postgres all the way! (Plus we need bindings for C++, Pike, and PHP, and I'm a lot happier with Postgres than several other options in that area.) ChrisA - Achilleas Mantzios IT DEPT -- 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] Storing small image files
why not bytea? much more control, much more information, IMHO. In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, we have been storing everything binary in bytea's. There are downsides in both solutions, you just have to have good reasons to not use bytea. On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: Good morning list, I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Storing small image files
Take a look here first : http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: why not bytea? Hi Achilleas, Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. much more control, much more information, IMHO. In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, we have been storing everything binary in bytea's. There are downsides in both solutions, you just have to have good reasons to not use bytea. On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: Good morning list, I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Comunication protocol
That would be postgresql:5432/TCP On Παρ 17 Μαΐ 2013 10:54:02 Karel Riveron Escobar wrote: Hi everyone: I have a question. I think it's so simple to answer but I don't know anything about that. I want to know what is the comunication protocol among PostgreSQL database server and an application server like Apache. I have to know that because I'm designing a simple deployment diagram and I just need it for finish. Thanks in advance. Regards, Karel Riverón Student Scientific Council Informatics Science University - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Comunication protocol
That would be postgresql:5432/TCP On Παρ 17 Μαΐ 2013 10:54:02 Karel Riveron Escobar wrote: Hi everyone: I have a question. I think it's so simple to answer but I don't know anything about that. I want to know what is the comunication protocol among PostgreSQL database server and an application server like Apache. I have to know that because I'm designing a simple deployment diagram and I just need it for finish. Thanks in advance. Regards, Karel Riverón Student Scientific Council Informatics Science University - Achilleas Mantzios IT DEV IT DEPT
Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu
On 23/10/2013 16:44, Tom Lane wrote: Patrick Dung patrick_...@yahoo.com.hk writes: By default, FreeBSD ports does not build postgresql with icu (http://www.icu-project.org/). Postgres does not have any option to use ICU, default or otherwise. Nor is it likely to happen in future, judging from previous discussions of the idea. Hi Tom, Patrick FreeBSD indeed has a config option to build with ICU, just # /usr/ports/databases/postgresql93-server # make config and you will be able to see this. The relevant README is here : http://people.freebsd.org/~girgen/postgresql-icu/README.html Patrick also you may build postgresql by hand and apply the patch manually from : /usr/ports/databases/postgresql93-server Although being in a non-english speaking company, i have not tried this neither at work or at home. Hope that helps. regards, tom lane -- Achilleas Mantzios -- 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] Bus error in libxml2 in postgresql 9.2.x on FreeBSD
Hello Steve, this reminds me the problems i had when trying to install pl/java in postgresql 9.2 FreeBSD : http://lists.freebsd.org/pipermail/freebsd-java/2013-February/010019.html http://lists.freebsd.org/pipermail/freebsd-java/2013-February/010021.html http://lists.freebsd.org/pipermail/freebsd-java/2013-February/010022.html The solution was to explicit link postgresql with /usr/lib/libpthread.so in src/backend/Makefile postgres: $(OBJS) $(CC) $(CFLAGS) $(LDFLAGS) $(LDFLAGS_EX) $(export_dynamic) $(call expand_subsys,$^) $(LIBS) -lpthread -o $@ Just an advice, you are also likely to find answers for similar problems in FreeBSD lists. On 16/12/2013 16:41, Steve McCoy wrote: Hello, I found some semi-recent messages related to this in 9.0.x, but wanted to confirm for anybody searching that the same issue exists in 9.2.x. It crashes for me with a similar backtrace as [Laurentius Purba’s][1]. The same fix/workaround applies as well: Compile libxml2 without the “threads” option. If I’m understanding the explanations correctly, the issue is that libxml2+threads expects something to be initialized by the calling program (postgres), but since the program isn’t multithreaded, this doesn’t happen. It sounds like something that neither side can really fix, but if I’m wrong and any devs would like a core file, I can provide one. [1] http://www.postgresql.org/message-id/camflbnhq6641ymbij2-7ozksgmgfne5v3ggfkvyxojy8umr...@mail.gmail.com -- Achilleas Mantzios -- 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 with ZFS on Linux
http://www.unix-experience.fr/2013/2451/ FreeBSD is also a very mature platform for ZFS/postgresql. On 16/01/2014 11:57, Sébastien Lorion wrote: On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion s...@thestrangefactory.com mailto:s...@thestrangefactory.com wrote: Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien FYI, a recent (Sept. 2013) presentation I found about using ZoL in production (albeit, not with PostgreSQL) and the current status of the project: http://lanyrd.com/2013/linuxcon-north-america/scqmfb/ -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Moving from Java 1.5 to Java 1.6
The appropriate list for this is the jdbc list: pgsql-j...@postgresql.org - Achilleas Mantzios IT DEPT
Re: [GENERAL] Memory issue on FreeBSD
ipcs in FreeBSD is a little ... tricky. ipcs -M ipcs -m ipcs -am could be your friends On Δευ 05 Νοε 2012 11:22:46 Frank Broniewski wrote: Hi, I am running a PostgreSQL server on FreeBSD. The system has 32GB memory. Usually I use top to examine the memory usage of the system. After a while, a part, approximately 5GB, vanish from top, so that the memory rounds up to 27GB. After restarting PostgreSQL, I have all 32GB again available, but then it's already slightly decreasing. It's a standalone database server. It has an OpenStreetMap world database running with 353GB data (with indices). Some system information: # uname -r 9.0-RELEASE-p3 # pg_ctl --version pg_ctl (PostgreSQL) 9.1.6 # cat /boot/loader.conf ... kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256 kern.ipc.semumr=200 vm.pmap.shpgperproc=400 vm.pmap.pv_entry_max=50331648 ... # cat /pgdata/data/postgresql.conf ... default_statistics_target = 50 # pgtune wizard 2012-04-04 maintenance_work_mem = 4GB # pgtune wizard 2012-04-04 constraint_exclusion = on # pgtune wizard 2012-04-04 checkpoint_completion_target = 0.9 # pgtune wizard 2012-04-04 effective_cache_size = 24GB # pgtune wizard 2012-04-04 work_mem = 768MB # pgtune wizard 2012-04-04 wal_buffers = 16MB # pgtune wizard 2012-04-04 checkpoint_segments = 60 # 20 shared_buffers = 8GB # pgtune wizard 2012-04-04 max_connections = 100 synchronous_commit = off So any help finding out why my system looses some RAM is greatly appreciated :-) If more information is needed I will gladly provide it. Frank - Achilleas Mantzios IT DEPT -- 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] Memory issue on FreeBSD
(scrap my previous internal email (hence fake) address this one is correct : sorry for that) You can stop pgsql, start it and then watch out for the increase in SEGSZ values. I pretty much think they are in bytes. I am pretty confident that this value depicts the shared_buffers size in bytes. On Δευ 05 Νοε 2012 13:14:37 Achilleas Mantzios wrote: ipcs in FreeBSD is a little ... tricky. ipcs -M ipcs -m ipcs -am could be your friends On Δευ 05 Νοε 2012 11:22:46 Frank Broniewski wrote: Hi, I am running a PostgreSQL server on FreeBSD. The system has 32GB memory. Usually I use top to examine the memory usage of the system. After a while, a part, approximately 5GB, vanish from top, so that the memory rounds up to 27GB. After restarting PostgreSQL, I have all 32GB again available, but then it's already slightly decreasing. It's a standalone database server. It has an OpenStreetMap world database running with 353GB data (with indices). Some system information: # uname -r 9.0-RELEASE-p3 # pg_ctl --version pg_ctl (PostgreSQL) 9.1.6 # cat /boot/loader.conf ... kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256 kern.ipc.semumr=200 vm.pmap.shpgperproc=400 vm.pmap.pv_entry_max=50331648 ... # cat /pgdata/data/postgresql.conf ... default_statistics_target = 50 # pgtune wizard 2012-04-04 maintenance_work_mem = 4GB # pgtune wizard 2012-04-04 constraint_exclusion = on # pgtune wizard 2012-04-04 checkpoint_completion_target = 0.9 # pgtune wizard 2012-04-04 effective_cache_size = 24GB # pgtune wizard 2012-04-04 work_mem = 768MB # pgtune wizard 2012-04-04 wal_buffers = 16MB # pgtune wizard 2012-04-04 checkpoint_segments = 60 # 20 shared_buffers = 8GB # pgtune wizard 2012-04-04 max_connections = 100 synchronous_commit = off So any help finding out why my system looses some RAM is greatly appreciated :-) If more information is needed I will gladly provide it. Frank - Achilleas Mantzios IT DEPT - Achilleas Mantzios IT DEPT -- 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] Memory issue on FreeBSD
How do you measure that smth is missing from top? What values do you add? I am currently running 8.3 but we shouldn't be so far apart top-wise. What is the reading under SIZE and RES in top for all postgresql processes? Take note that shared mem should be recorded for each and every postmaster running. On Δευ 05 Νοε 2012 14:36:44 Frank Broniewski wrote: Hi, thank you for your feedback. I had a look at those commands and their output, especially in conjunction with the SEGSZ value from icps -am Here's an example output: # ipcs -am Shared Memory: T ID KEY MODEOWNERGROUPCREATOR CGROUP NATTCHSEGSZ CPID LPID ATIME DTIMECTIME m 262144 5432001 --rw--- pgsqlpgsqlpgsqlpgsql 12 88139939844551245512 13:49:28 14:31:29 13:49:28 but frankly this tells me nothing. I can tell that the value SEGSZ is right from the start 8813993984 and it doesn't change anymore. The only value that changes is the NATTCH value, I observed a range from 8 to 36 there. I agree that the SEGSZ value matches the 8GB shared buffer, but how can I make the connection of my 5GB missing in top? I wonder if this might be the maintenance_work_mem, which is set to 4GB? Many thanks, Frank Am 2012-11-05 12:14, schrieb Achilleas Mantzios: ipcs in FreeBSD is a little ... tricky. ipcs -M ipcs -m ipcs -am could be your friends On Δευ 05 Νοε 2012 11:22:46 Frank Broniewski wrote: Hi, I am running a PostgreSQL server on FreeBSD. The system has 32GB memory. Usually I use top to examine the memory usage of the system. After a while, a part, approximately 5GB, vanish from top, so that the memory rounds up to 27GB. After restarting PostgreSQL, I have all 32GB again available, but then it's already slightly decreasing. It's a standalone database server. It has an OpenStreetMap world database running with 353GB data (with indices). Some system information: # uname -r 9.0-RELEASE-p3 # pg_ctl --version pg_ctl (PostgreSQL) 9.1.6 # cat /boot/loader.conf ... kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256 kern.ipc.semumr=200 vm.pmap.shpgperproc=400 vm.pmap.pv_entry_max=50331648 ... # cat /pgdata/data/postgresql.conf ... default_statistics_target = 50 # pgtune wizard 2012-04-04 maintenance_work_mem = 4GB # pgtune wizard 2012-04-04 constraint_exclusion = on # pgtune wizard 2012-04-04 checkpoint_completion_target = 0.9 # pgtune wizard 2012-04-04 effective_cache_size = 24GB # pgtune wizard 2012-04-04 work_mem = 768MB # pgtune wizard 2012-04-04 wal_buffers = 16MB # pgtune wizard 2012-04-04 checkpoint_segments = 60 # 20 shared_buffers = 8GB # pgtune wizard 2012-04-04 max_connections = 100 synchronous_commit = off So any help finding out why my system looses some RAM is greatly appreciated :-) If more information is needed I will gladly provide it. Frank - Achilleas Mantzios IT DEPT - Achilleas Mantzios IT DEPT -- 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] Memory issue on FreeBSD
Since the top reporting goes back to normal when postgresql is stopped , and since postgresql is special due to the use of IPC, i would be inclined to think that the culprit here is the shared memory. I don't know where maintenance_work_mem really lives (process normal address space or IPC shared mem) and if that makes any difference. If it is possible you might play with those two values and see if anything changes. Currently i have : maintenance_work_mem = 960MB # pgtune wizard 2012-11-01 shared_buffers = 3840MB # pgtune wizard 2012-11-01 top: last pid: 74896; load averages: 0.02, 0.08, 0.08 up 4+06:20:31 18:14:19 187 processes: 1 running, 172 sleeping, 14 zombie CPU: % user, % nice, % system, % interrupt, % idle Mem: 4064M Active, 8111M Inact, 2014M Wired, 322M Cache, 1645M Buf, 1106M Free Swap: 8000M Total, 608K Used, 7999M Free hw.physmem: 17144205312 hw.usermem: 15028662272 hw.realmem: 17985175552 top (excluding Buf) amounts to 15617 Megs while physmem shows as 16349 Megs but as i said i run 8.3 on AMD64 and pgsql 9.2.1 On Δευ 05 Νοε 2012 16:11:39 Frank Broniewski wrote: Hi, I just add the different memory values together (minus the buffers). Usually this sums up (+/-) to the installed memory size, at least on my other machines. I found a thread similar to my problem here [1], but no solution. I don't mind top showing false values, but if there's a larger problem behind this, then I really want to solve it. Top is really just an indicator for this issue, it's also visible in my munin stats [2] Below is a output _without_ postgresql running: Mem: 59M Active, 17G Inact, 3953M Wired, 1325M Cache, 3283M Buf, 8663M Free Swap: 4096M Total, 828K Used, 4095M Free and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4096M Total, 828K Used, 4095M Free some memory related sysctl values: hw.realmem: 34879832064 hw.physmem: 34322804736 hw.usermem: 30161108992 # sysctl vm.vmtotal vm.vmtotal: System wide totals computed every five seconds: (values in kilobytes) === Processes:(RUNQ: 1 Disk Wait: 0 Page Wait: 0 Sleep: 70) Virtual Memory: (Total: 1084659688K Active: 10400940K) Real Memory: (Total: 1616176K Active: 1349052K) Shared Virtual Memory:(Total: 60840K Active: 14132K) Shared Real Memory: (Total: 11644K Active: 8388K) Free Memory Pages:7263972K [1] http://lists.freebsd.org/pipermail/freebsd-stable/2011-January/061247.html [2] http://www.gis-hosting.lu/monitor/munin/metrico/bilbo.metrico/memory.html Am 2012-11-05 15:21, schrieb Achilleas Mantzios: How do you measure that smth is missing from top? What values do you add? I am currently running 8.3 but we shouldn't be so far apart top-wise. What is the reading under SIZE and RES in top for all postgresql processes? Take note that shared mem should be recorded for each and every postmaster running. On Δευ 05 Νοε 2012 14:36:44 Frank Broniewski wrote: Hi, thank you for your feedback. I had a look at those commands and their output, especially in conjunction with the SEGSZ value from icps -am Here's an example output: # ipcs -am Shared Memory: T ID KEY MODEOWNERGROUPCREATOR CGROUP NATTCHSEGSZ CPID LPID ATIME DTIMECTIME m 262144 5432001 --rw--- pgsqlpgsqlpgsqlpgsql 12 88139939844551245512 13:49:28 14:31:29 13:49:28 but frankly this tells me nothing. I can tell that the value SEGSZ is right from the start 8813993984 and it doesn't change anymore. The only value that changes is the NATTCH value, I observed a range from 8 to 36 there. I agree that the SEGSZ value matches the 8GB shared buffer, but how can I make the connection of my 5GB missing in top? I wonder if this might be the maintenance_work_mem, which is set to 4GB? Many thanks, Frank Am 2012-11-05 12:14, schrieb Achilleas Mantzios: ipcs in FreeBSD is a little ... tricky. ipcs -M ipcs -m ipcs -am could be your friends On Δευ 05 Νοε 2012 11:22:46 Frank Broniewski wrote: Hi, I am running a PostgreSQL server on FreeBSD. The system has 32GB memory. Usually I use top to examine the memory usage of the system. After a while, a part, approximately 5GB, vanish from top, so that the memory rounds up to 27GB. After restarting PostgreSQL, I have all 32GB again available, but then it's already slightly decreasing. It's a standalone database server. It has an OpenStreetMap world database running with 353GB data (with indices). Some system information: # uname -r 9.0-RELEASE-p3 # pg_ctl --version pg_ctl (PostgreSQL) 9.1.6 # cat /boot/loader.conf
[GENERAL] SPI function varchar difference between 9.0 and 9.2
();
// ^^ this is normal NULL terminated C char *, no varlena header
cpKeyData_tmp = palloc(VARHDRSZ+strlen(cpKeyData));
memcpy((cpKeyData_tmp+VARHDRSZ), cpKeyData, strlen(cpKeyData));
SET_VARSIZE(cpKeyData_tmp, VARHDRSZ+strlen(cpKeyData));
planData[0] = PointerGetDatum(cpKeyData_tmp);
iRetValue = SPI_execp(pplan, planData, NULL, 1);
if (cpKeyData != 0)
pfree(cpKeyData);
if (cpKeyData_tmp != 0)
pfree(cpKeyData_tmp);
if (iRetValue != SPI_OK_INSERT)
{
elog(NOTICE, Error inserting row in storeData);
return -1;
}
return 0;
The above seems to work ok on PostgreSQL 9.2.1.
I just think that :
- the changes in the semantics of NAMEOID could be somewhere documented in the
docs (postgresql-9.2.1/HISTORY)
- i believe that the documentation on how to store C strings as varchar is
almost absent, i just followed the comments
in postgres.h
-
Achilleas Mantzios
IT DEPT
Re: [GENERAL] Memory issue on FreeBSD
Vick, fantastic script, thanx! FreeBSD sysctl system is awesome! On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote: On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote: and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4096M Total, 828K Used, 4095M Free For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7. The data + indexes are about 240GB on disk. This server only runs postgres aside from the basic system processes. Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free Swap: 4096M Total, 272K Used, 4096M Free I agree with the conclusion that the shared memory segments are confusing the output of top. There are no memory leaks, and FreeBSD doesn't lose any memory. There are some scripts floating around that read values from sysctl vm.stats.vm and format them nicely to tell you how much memory is used up and free. Try the one referenced here: http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/ - Achilleas Mantzios IT DEPT
Re: [GENERAL] Memory issue on FreeBSD
On Τετ 07 Νοε 2012 09:42:47 Frank Broniewski wrote:
Hey, this is really cool. I directly tried the script and there's a line
from the output that caught my eye:
mem_gap_vm: + 8812892160 ( 8404MB) [ 26%] Memory gap: UNKNOWN
is this the shared buffers? I guess so, but I want to confirm my guess ...
Hmm, that would be ideal, (from an understanding perspective) but at least in
my system (FreeBSD-8.3), no.
psql -q -t -c show shared_buffers | grep -v -e '^$' | awk '{print $1}'
3840MB
SYSTEM MEMORY INFORMATION:
mem_gap_vm: +996843520 (950MB) [ 5%] Memory gap: UNKNOWN
$mem_gap_vm = $mem_all - ($mem_wire + $mem_active + $mem_inactive + $mem_cache
+ $mem_free);
mem_all is some rounded and more rationalized version less than hw.physmem :
$mem_all = $sysctl-{vm.stats.vm.v_page_count} * $sysctl-{hw.pagesize};
Anyway, this is not so postgresql related at the moment. The correct thing to
do (since you run production servers on FreeBSD) is to post to the relevant
FreeBSD list and/or forum. freebsd-questi...@freebsd.org and
freebsd-sta...@freebsd.org would be a good start.
Also the forums : http://forums.freebsd.org/forumdisplay.php?f=3
Only after gathering substantial info from there, would it make sense to come
back here and maybe ask more questions.
And since we are observing different percentages of gaps (mine is 5%, yours is
26%), i think maybe you should look into it on the FreeBSD camp.
Please drop the link to the relevant thread there, if you decide to do so.
I would like to follow this.
Thanx!
Frank
Am 2012-11-07 09:26, schrieb Achilleas Mantzios:
Vick,
fantastic script, thanx! FreeBSD sysctl system is awesome!
On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote:
On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote:
and this is after a few hours of running:
Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free
Swap: 4096M Total, 828K Used, 4095M Free
For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with
Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7. The
data + indexes are about 240GB on disk. This server only runs postgres
aside from the basic system processes.
Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free
Swap: 4096M Total, 272K Used, 4096M Free
I agree with the conclusion that the shared memory segments are confusing
the output of top. There are no memory leaks, and FreeBSD doesn't lose
any memory.
There are some scripts floating around that read values from sysctl
vm.stats.vm and format them nicely to tell you how much memory is used up
and free. Try the one referenced here:
http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/
-
Achilleas Mantzios
IT DEPT
-
Achilleas Mantzios
IT DEPT
Re: [GENERAL] Memory issue on FreeBSD
Vick, fantastic script, thanx! FreeBSD sysctl system is awesome! On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote: On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote: and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4096M Total, 828K Used, 4095M Free For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7. The data + indexes are about 240GB on disk. This server only runs postgres aside from the basic system processes. Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free Swap: 4096M Total, 272K Used, 4096M Free I agree with the conclusion that the shared memory segments are confusing the output of top. There are no memory leaks, and FreeBSD doesn't lose any memory. There are some scripts floating around that read values from sysctl vm.stats.vm and format them nicely to tell you how much memory is used up and free. Try the one referenced here: http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/ - Achilleas Mantzios IT DEPT
Re: [GENERAL] Memory issue on FreeBSD
Thanx for the link.
I just think that it would be a good idea, instead of posting the links at this
list, to include a
dense but detailed summary of the situation in your machine, and give as much
data as possible.
In short, you might do a quantitative compilation of this thread, and present
it in a nice way
in order to gain more attention.
Also, i think posting to -stable would be a better idea, -questions is for
noobs.
On Παρ 09 Νοε 2012 09:37:14 Frank Broniewski wrote:
FYI
http://freebsd.1045724.n5.nabble.com/Postgresql-related-memory-question-td5759467.html
Am 2012-11-07 10:28, schrieb Achilleas Mantzios:
On Τετ 07 Νοε 2012 09:42:47 Frank Broniewski wrote:
Hey, this is really cool. I directly tried the script and there's a line
from the output that caught my eye:
mem_gap_vm: + 8812892160 ( 8404MB) [ 26%] Memory gap: UNKNOWN
is this the shared buffers? I guess so, but I want to confirm my guess ...
Hmm, that would be ideal, (from an understanding perspective) but at least
in my system (FreeBSD-8.3), no.
psql -q -t -c show shared_buffers | grep -v -e '^$' | awk '{print $1}'
3840MB
SYSTEM MEMORY INFORMATION:
mem_gap_vm: +996843520 (950MB) [ 5%] Memory gap: UNKNOWN
$mem_gap_vm = $mem_all - ($mem_wire + $mem_active + $mem_inactive +
$mem_cache + $mem_free);
mem_all is some rounded and more rationalized version less than hw.physmem
: $mem_all = $sysctl-{vm.stats.vm.v_page_count} *
$sysctl-{hw.pagesize};
Anyway, this is not so postgresql related at the moment. The correct thing
to do (since you run production servers on FreeBSD) is to post to the
relevant
FreeBSD list and/or forum. freebsd-questi...@freebsd.org and
freebsd-sta...@freebsd.org would be a good start.
Also the forums : http://forums.freebsd.org/forumdisplay.php?f=3
Only after gathering substantial info from there, would it make sense to
come back here and maybe ask more questions.
And since we are observing different percentages of gaps (mine is 5%, yours
is 26%), i think maybe you should look into it on the FreeBSD camp.
Please drop the link to the relevant thread there, if you decide to do so.
I would like to follow this.
Thanx!
Frank
Am 2012-11-07 09:26, schrieb Achilleas Mantzios:
Vick,
fantastic script, thanx! FreeBSD sysctl system is awesome!
On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote:
On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote:
and this is after a few hours of running:
Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free
Swap: 4096M Total, 828K Used, 4095M Free
For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server,
with Postgres 9.0.7 running since June 10, and is heavily pounded on
24x7. The data + indexes are about 240GB on disk. This server only runs
postgres aside from the basic system processes.
Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M
Free
Swap: 4096M Total, 272K Used, 4096M Free
I agree with the conclusion that the shared memory segments are confusing
the output of top. There are no memory leaks, and FreeBSD doesn't lose
any memory.
There are some scripts floating around that read values from sysctl
vm.stats.vm and format them nicely to tell you how much memory is used up
and free. Try the one referenced here:
http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/
-
Achilleas Mantzios
IT DEPT
-
Achilleas Mantzios
IT DEPT
-
Achilleas Mantzios
IT DEPT
--
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 and a clustered file system
Hello Ivan, this sounds so mainframe-ish, i recall, in IBM MVS (circa 1990+) we used to attach two systems to the same DASDie storage, and then employ disk serialization provided by the OS to achieve some integrity to the data. (do not get me wrong i had adequate Unix/SUNOS/Ultrix experience before i had to go through all that MVS stuff, which made it even more painful!). On ÎÎµÏ 12 Îοε 2012 11:03:14 Ivan Voras wrote: Hello, Is anyone running PostgreSQL on a clustered file system on Linux? By clustered I actually mean shared, such that the same storage is mounted by different servers at the same time (of course, only one instance of PostgreSQL on only one server can be running on such a setup, and there are a lot of other precautions that need to be satisfied). - Achilleas Mantzios IT DEPT -- 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 list all schema names inside a PostgreSQL database through SQL
On Πεμ 15 Îοε 2012 20:31:05 Xiaobo Gu wrote: Hi, How can I list all schema names inside a PostgreSQL database through SQL, especially thoese without any objects created inside it. 1st solution : select catalog_name,schema_name from information_schema.schemata ; 2nd solution : select * from pg_namespace ; Regards, Xiaobo Gu - Achilleas Mantzios IT DEPT -- 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] Experiences with pl/Java
Hello Peter, glad to meet you again after http://2012.pgconf.eu ! On ÎÎµÏ 19 Îοε 2012 16:26:56 you wrote: On 19 November 2012 08:02, Thomas Hill thomas.k.h...@t-online.de wrote: was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. I'd caution you against using pl/java in production. I came up against a problem with it that I could not find a satisfactory solution for. Essentially, each Postgres backend (connection process) must start its own JVM, and does so using the JNI. If native memory runs out (I realise that this may not actually be due to malloc() returning NULL, but the effect is about the same), this failure is handled rather poorly. It results in an OutOfMemoryError due to native memory exhaustion. This is the situation in J2EE app servers as well, once OutOfMemoryError is thrown there is not much the admin can do besides killing/restarting the app server. This results in a segfault of the Postgres backend, originating from within libjvm.so. There is a workaround - which is to set the maximum JVM heap size to a sufficiently low value - Wouldn't that just make the problem manifest itself earlier? but in general the need to do so left me with a very low opinion of pl/java as a project. However, in most cases this error denotes a system (jvm itself) memory leak, an insufficient garbage collector operation or a poorly designed application. In the java 7, i heard good stories about the new G1 garbage collector. We have never tried pl/java, despite being a postgresql/java house, and this is definitely something we are looking forward to explore, but i agree with Thomas that the traffic on the list is low which points to what Peter is suggesting about being scared to use this in production environments. That would be very cool if it was much more active and stable. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger based replication with ENABLE REPLICA triggers and session_replication_role best practice(s)
Hello, we have based all our replication infrastructure on a heavily hacked version of DBMirror, which now runs in a single master (office DB) - multiple slaves (vessels DBs) mode for 80+ slaves and about 300 tables and in multiple masters (the same vessels DBs as above) (having partitions of the data) - single slave (the same office db as above) mode for just two tables. Now we are in the process of designing a new solution which requires to have some form of multi-master functionality, without being so much concerned about conflict resolution at this stage. The issue that we are facing is to prevent replication data originating from a vessel DB and consumed into the office DB, (or replication data originating from the office DB and consumed into a vessel DB) to be bounced back to the originating server because of the invocation of the DBMirror trigger. We have thought of : Solution 1) explicitly disabling the triggers at the start of the transaction and re-enabling them, but that would require knowledge of the name of table in question prior to execution of the replication command, but in our case this not known unless parsing the SQL file which came from the originating server. Since the number of those multi-master tables is not large, we could explicit insert the ALTER TABLE tblname DISABLE TRIGGER tblname_dbmirror_trig commands prior to actual SQL execution for each table involved and then insert the respective ALTER TABLE tblname ENABLE TRIGGER tblname_dbmirror_trig commands after the SQL execution. However this would require hardcoding those commands into the code which runs the replication SQL, and this has many and obvious disadvantages. Then i looked upon : Solution 2) the ENABLE REPLICA TRIGGER in combination with session_replication_role. Setting default ALTER DATABASE dbname SET session_replication_role TO REPLICA (single master + multi-master ones) in combination with ALTER TABLE ENABLE REPLICA TRIGGER tblname_dbmirror_trig for *all* tables involved in replication would allow the default behavior of INSERTS/UPDATES/DELETES to result in firing the DBmirror trigger, which would work similar to the default way it has been running for the single direction replicated tables. Then in the code which plays the replication SQLs for the multi-master tables we would simply set smth like : BEGIN ; SET local session_replication_role TO origin; execute SQL here END; preventing the trigger to be called, and thus eliminating the bounce-back effect. One thing that worries me is setting the database-wide session_replication_role to smth different than the default. In our case, it would be ideal to be able to set session_replication_role to some value which would have the complimentary effect of REPLICA, smth like e.g. NOREPLICA which would result in on-demand temporary disablement of the triggers when leaving session_replication_role to non-REPLICA (such as the default value=origin). One third solution would be to : Solution 3) make the code updating those multi-master tables, replication-aware by putting SET local session_replication_role TO REPLICA; inside the affecting transactions. But this also has the obvious disadvantage of making application and system logic blend together, and also making the app programmer prone to errors. The most elegant solution IMHO is the 2nd but i am concerned that setting the database-wide session_replication_role to smth different than the default might just hide some future risks. What are your thoughts on that? Thank you a lot for any input. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Leaking disk space on FreeBSD servers
Did you do a detailed du during the supposed problem and after the reboot and
make a diff of those
to fimd any invlolved files/dirs?
That said, i think you might consider posting on freebsd-[questions|stable] as
well.
On Τετ 20 Μαρ 2013 11:49:07 Dan Thomas wrote:
Hi Guys,
We're seeing a problem with some of our FreeBSD/PostgreSQL servers leaking
quite significant amounts of disk space:
df -h /usr/local/pgsql/
Filesystem SizeUsed Avail Capacity Mounted on
/dev/mfid1s1d1.1T772G222G78%/usr/local/pgsql
du -sh /usr/local/pgsql/
741G/usr/local/pgsql/
Stopping Postgres doesn't fix it, but rebooting does which points at the OS
rather than PG to me. However, the leak is only apparent in the dedicated pgsql
partition, and only on our database servers, so PostgreSQL seems to at least be
involved. The partition itself is a relatively standard UFS partition:
grep /usr/local/pgsql /etc/fstab
/dev/mfid1s1d /usr/local/pgsqlufs rw 2 2
tunefs -p /usr/local/pgsql/
tunefs: POSIX.1e ACLs: (-a)disabled
tunefs: NFSv4 ACLs: (-N) disabled
tunefs: MAC multilabel: (-l) disabled
tunefs: soft updates: (-n) enabled
tunefs: gjournal: (-J) disabled
tunefs: trim: (-t) disabled
tunefs: maximum blocks per file in a cylinder group: (-e) 2048
tunefs: average file size: (-f)16384
tunefs: average number of files in a directory: (-s) 64
tunefs: minimum percentage of free space: (-m) 8%
tunefs: optimization preference: (-o) time
tunefs: volume label: (-L)
LSOF isn't showing any open files:
lsof +L /usr/local/pgsql/ | awk '{ print $8 }' | grep 0 | wc -l
0
We're not creating filesystem snapshots:
find /usr/local/pgsql/ -flags snapshot
Not all of our servers are leaking space, it's only the more recently-installed
systems. Here's a quick breakdown of versions:
FreeBSD PostgreSQL Leaking?
8.0 8.4.4no
8.2 9.0.4no
8.3 9.1.4yes
8.3 9.2.3yes
9.1 9.2.3yes
Each of these servers is configured with a warm standby, so we've been
switching them over to the standby to reclaim the space (rebooting the primary
is too much downtime). The standby does *not* demonstrate this problem while
it's being used as a standby, but it starts leaking space once it's been made
the primary.
Initially I thought this might be related to WAL files, however the pg_xlog dir
is symlinked outside of the /usr/local/pgsql partition that is demonstrating
this problem:
ll /usr/local/pgsql/data/pg_xlog
lrwxr-xr-x 25B Oct 19 10:48 pg_xlog - /usr/local/pglog/pg_xlog/
I've exhausted everything I can think of to try to solve this one. Has anyone
got any ideas on how to go about debugging this?
Thanks,
Dan
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
Re: [GENERAL] Leaking disk space on FreeBSD servers
On Τετ 20 Μαρ 2013 12:47:39 Dan Thomas wrote:
Did you do a detailed du during the supposed problem and after the reboot
and make a diff of those to fimd any invlolved files/dirs?
du doesn't show the space in question (du -s shows the actual usage on
disk, df is showing a much higher number), so I doubt this will show
anything up. However, next reboot I'll certainly do that.
du (without -s) shows the whole hierarchy, du -s behaves like du -d 0,
so at this point diff the output of (plain) su is definitely somth worth doing.
That said, i think you might consider posting on freebsd-[questions|stable]
as well.
Yes I think that might be a good plan :)
Dan
On 20 March 2013 12:30, Achilleas Mantzios ach...@matrix.gatewaynet.com
wrote:
Did you do a detailed du during the supposed problem and after the reboot
and make a diff of those
to fimd any invlolved files/dirs?
That said, i think you might consider posting on freebsd-[questions|stable]
as well.
On Τετ 20 Μαρ 2013 11:49:07 Dan Thomas wrote:
Hi Guys,
We're seeing a problem with some of our FreeBSD/PostgreSQL servers leaking
quite significant amounts of disk space:
df -h /usr/local/pgsql/
Filesystem SizeUsed Avail Capacity Mounted on
/dev/mfid1s1d1.1T772G222G78%/usr/local/pgsql
du -sh /usr/local/pgsql/
741G/usr/local/pgsql/
Stopping Postgres doesn't fix it, but rebooting does which points at the OS
rather than PG to me. However, the leak is only apparent in the dedicated
pgsql partition, and only on our database servers, so PostgreSQL seems to at
least be involved. The partition itself is a relatively standard UFS
partition:
grep /usr/local/pgsql /etc/fstab
/dev/mfid1s1d /usr/local/pgsqlufs rw 2 2
tunefs -p /usr/local/pgsql/
tunefs: POSIX.1e ACLs: (-a)disabled
tunefs: NFSv4 ACLs: (-N) disabled
tunefs: MAC multilabel: (-l) disabled
tunefs: soft updates: (-n) enabled
tunefs: gjournal: (-J) disabled
tunefs: trim: (-t) disabled
tunefs: maximum blocks per file in a cylinder group: (-e) 2048
tunefs: average file size: (-f)16384
tunefs: average number of files in a directory: (-s) 64
tunefs: minimum percentage of free space: (-m) 8%
tunefs: optimization preference: (-o) time
tunefs: volume label: (-L)
LSOF isn't showing any open files:
lsof +L /usr/local/pgsql/ | awk '{ print $8 }' | grep 0 | wc -l
0
We're not creating filesystem snapshots:
find /usr/local/pgsql/ -flags snapshot
Not all of our servers are leaking space, it's only the more
recently-installed systems. Here's a quick breakdown of versions:
FreeBSD PostgreSQL Leaking?
8.0 8.4.4no
8.2 9.0.4no
8.3 9.1.4yes
8.3 9.2.3yes
9.1 9.2.3yes
Each of these servers is configured with a warm standby, so we've been
switching them over to the standby to reclaim the space (rebooting the
primary is too much downtime). The standby does *not* demonstrate this
problem while it's being used as a standby, but it starts leaking space once
it's been made the primary.
Initially I thought this might be related to WAL files, however the pg_xlog
dir is symlinked outside of the /usr/local/pgsql partition that is
demonstrating this problem:
ll /usr/local/pgsql/data/pg_xlog
lrwxr-xr-x 25B Oct 19 10:48 pg_xlog - /usr/local/pglog/pg_xlog/
I've exhausted everything I can think of to try to solve this one. Has
anyone got any ideas on how to go about debugging this?
Thanks,
Dan
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
--
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] Leaking disk space on FreeBSD servers
regarding journaling, there is the counter argument that you do not need to do the same job twice, in the sense that we already spend a considerable amount of time retaining the WAL in postgresql, no need to redo the same on FS level. Crush-intensive systems (for lack of a better word) might benefit from FS journaling, but the best option here is try and find the cause. FreeBSD systems are supposed to not crush, that's why ppl use them in the first place. On Τετ 20 Μαρ 2013 10:11:58 Vick Khera wrote: On Wed, Mar 20, 2013 at 7:49 AM, Dan Thomas godd...@gmail.com wrote: Not all of our servers are leaking space, it's only the more recently-installed systems. Here's a quick breakdown of versions: FWIW, I do not observe this behavior. My database has very heavy write load, and old data is purged after it is aged about 7 months, so I do get lots of fragmentation. However, I do not have any disk space phantom loss. How long does it take for you to accumulate this leak? My first instinct is that you have unlinked files still referenced by some application. That is really the only way you get these discrepancies. lsof *should* have showed them to you. Try fstat in case there's some bug in lsof. Also, your tunefs output seems to be not from FreeBSD 9.1. Specifically, it is not emitting this line: tunefs: soft update journaling: (-j) disabled It is a very useful option to turn on for large file systems. I can recover a 6TB file system in about 5 seconds on a crash reboot with that on. [root@d04]# ps axuw34214 USERPID %CPU %MEM VSZRSS TT STAT STARTEDTIME COMMAND pgsql 34214 0.0 0.5 5426964 154484 0- S28Feb13 1:30.66 /usr/local/bin/postgres -D /u/data/postgres [root@d04]# df -h /u/data Filesystem SizeUsed Avail Capacity Mounted on /dev/ufs/ramdisk707G137G513G21%/u/data [root@d04]# du -sh /u/data 137G/u/data [root@d04]# uname -a FreeBSD d04.m1e.net 9.1-RELEASE FreeBSD 9.1-RELEASE #1 r243808: Mon Dec 3 09:56:27 EST 2012 vi...@lorax.kcilink.com:/usr/obj/u/lorax1/usr9/src/sys/KCI64 amd64 [root@d04]# uptime 9:50AM up 74 days, 17:36, 1 user, load averages: 0.21, 0.18, 0.17 [root@d04]# psql --version psql (PostgreSQL) 9.2.3 [root@d04]# - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Leaking disk space on FreeBSD servers
Of course, but does it make sense for you to pay the ~ 5%/day performance penalty for the ~0.5%/year chance of having your system crush? Unless your FreeBSD server is stuffed with exotic gamer hardware, i don't see the likehood of crush getting larger than that. On Τετ 20 Μαρ 2013 10:39:58 Vick Khera wrote: On Wed, Mar 20, 2013 at 10:34 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: regarding journaling, there is the counter argument that you do not need to do the same job twice, in the sense that we already spend a considerable amount of time retaining the WAL in postgresql, no need to redo the same on FS level. There's a difference in the file system integrity and the DB integrity. PG will keep the DB integrity just fine without the file system journaling. The journaling just makes recovery from crash that much faster. ie, running fsck on 6TB of disk storage takes a LONG time, sometimes hours, but with the journal enabled, it takes a few seconds. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Leaking disk space on FreeBSD servers
On Ôåô 20 Ìáñ 2013 15:15:23 Dan Thomas wrote: We actually have another FreeBSD8.3/PG9.1 machine under different (but similar) load that *doesn't* demonstrate this behaviour. There's nothing obvious in the differences in usage patterns that we can see (we're not using any exotic features or anything), but it certainly suggests that it's *something* related to PG or our usage of it. Any difference in the architecture of the two systems? (x86, amd64, etc..) Any difference in the respective output of % pg_config ? - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- 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] session_replication_role `replica` behavior
Point taken, thanx, however from the docs, it is far from explicit that setting session_replication_role to 'replica' can disable FK constraints (RI) and finally result in an incosistent database. It might be that RI in postgres is implemented via triggers, but to the user, that is just an implementation detail, and in any case this is not reflected in the docs. Furthermore from the docs, same page : Simply enabled triggers will fire when the replication role is origin (the default) or local. Triggers configured as ENABLE REPLICA will only fire if the session is in replica mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode. In the second sentence above the word only is used, and the meaning is precisely delivered. However this same word is missing from the first sentence, and might confuse quite a lot of users. IMHO this section needs some modifications in order to express the whole behavior correctly. On Ðåì 25 Áðñ 2013 15:49:55 Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 manos tsahakis wrote: In our application we are enabling session_replication_role TO 'replica' in certain situations so that triggers will not fire in a table during DML operations. However, we observed that when setting session_replication_role TO 'replica' referential integrity constraints will not fire on a table either. ... Shouldn't non-user triggers *not* be affected by session_replication_role ? No. The design of session_replication_role was to enable quick disabling of *all* triggers and rules, including system ones. When you enter that mode, it is assumed that you know what you are doing enough to not create an inconsistency. With Slony and Bucardo, for example, all tables affected by the triggers (e.g. a cascaded delete from a FK) are changed together. 2. Is there any way to just find the name of the FK constraint trigger and convert it to ENABLE ALWAYS? I think you are approaching this in the wrong way. If you want the constraint triggers to fire, but not other user triggers, your best bet is to do: ALTER TABLE foo DISABLE TRIGGER USER; This has a heavy table locking cost, but does exactly what you want: disables all non-system/FK triggers. Your next best bet is probably to emulate the effects of the FK trigger yourself, e.g. deleting from the child table while in 'replica' mode. A further option may be to give your user functions some brains, such that they will not execute when session_replication_role is set to 'local', for example. While I do think session_replication_role needs some more granularity, it's also a little hard to say more without knowing your exact requirements. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201304251145 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlF5UHAACgkQvJuQZxSWSsjm+ACeOT2v7EF90tFr7K892UxIAqnl WpwAoKPkIMC7HTTtvOMj/XbtOVGXe0Fl =2bjH -END PGP SIGNATURE- - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Linux vs FreeBSD
FreeBSD is OK if you are experienced. As a system it requires much more maturity by the admin than lets say Ubuntu which is targeted at a larger user base. I'd say, explore your other Linux options first, since you already have experience with Linux. FreeBSD requires a much bigger learning curve. On 04/04/2014 07:03, François Beausoleil wrote: Hi all! Does PG perform that much better on FreeBSD? I have some performance issues on a Ubuntu 12.04 which I'd like to resolve. iowait varies a lot, between 5 and 50%. Does FreeBSD better schedule I/O, which could alleviate some of the issues, or not at all? I have no experience administering FreeBSD, but I'm willing to learn if I'll get some performance enhancements out of the switch. Our workload is lots of data import, followed by many queries to summarize (daily and weekly reports). Our main table is a wide table that represents Twitter and Facebook interactions. Most of our reports work on a week's worth of data (table is partitioned by week), and the tables are approximately 25 GB plus 5 GB of indices, per week. Of course, while reports are ongoing, we're also importing next week's data. The host is a dedicated hardware machine at online.fr: 128 GB RAM, 2 x 3TB disk in RAID 1 configuration. I started thinking of this after reading PostgreSQL pain points at https://lwn.net/Articles/591723/. In the comments, bronson says FreeBSD does not exhibit the same problems (slow fsync, double buffering). On the list here, I've read about problems with certain kernel versions on Ubuntu. I'm not expecting anything magical, just some general guidelines and hints. Did anybody do the migration and was happier after? Thanks for any hints! François Beausoleil $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux $ psql -U postgres -c select version() version - PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit /proc/cpuinfo says: 8 CPUs, identified as Intel(R) Xeon(R) CPU E5-2609 0 @ 2.40GHz -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 vs FreeBSD
As a side note, when we migrated the exact same pgsql 8.3 system from linux kernel 2.6 to 3.6, we experienced an almost dramatic slowdown by 6 times. Linux Kernel's were known to have issues around those dates, i recall. We had to set synchronous_commit to off, this gave a huge boost , but this was no longer apples vs apples. On 04/04/2014 07:03, François Beausoleil wrote: Hi all! Does PG perform that much better on FreeBSD? I have some performance issues on a Ubuntu 12.04 which I'd like to resolve. iowait varies a lot, between 5 and 50%. Does FreeBSD better schedule I/O, which could alleviate some of the issues, or not at all? I have no experience administering FreeBSD, but I'm willing to learn if I'll get some performance enhancements out of the switch. Our workload is lots of data import, followed by many queries to summarize (daily and weekly reports). Our main table is a wide table that represents Twitter and Facebook interactions. Most of our reports work on a week's worth of data (table is partitioned by week), and the tables are approximately 25 GB plus 5 GB of indices, per week. Of course, while reports are ongoing, we're also importing next week's data. The host is a dedicated hardware machine at online.fr: 128 GB RAM, 2 x 3TB disk in RAID 1 configuration. I started thinking of this after reading PostgreSQL pain points at https://lwn.net/Articles/591723/. In the comments, bronson says FreeBSD does not exhibit the same problems (slow fsync, double buffering). On the list here, I've read about problems with certain kernel versions on Ubuntu. I'm not expecting anything magical, just some general guidelines and hints. Did anybody do the migration and was happier after? Thanks for any hints! François Beausoleil $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux $ psql -U postgres -c select version() version - PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit /proc/cpuinfo says: 8 CPUs, identified as Intel(R) Xeon(R) CPU E5-2609 0 @ 2.40GHz -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 vs FreeBSD
Basically it goes beyond what ppl would describe as OS holly wars. If one chooses to go by FreeBSD, then he better be prepared to handle the burden, both the part that is imposed by the OS administration itself, as well as the part that is a side effect of the different base system. Example of admin part : Generally, compiling postgresql from source gives more freedom than be stuck on the OS's ports or PKGng system. (the later being a very handy and welcome addition to FreeBSD). Now what if e.g. the user wants pgsql software X (e.g. pgadmin3, p5-Pg, etc...) only to find out that most of those ports need postgresql client as a dependency. He/she must be prepared to work his way through : - manual installations (gmake config gmake gmake install) - /usr/ports - PKG binary installations in decreasing order of freedom but increasing order of easiness, and in many cases work through a combination of the above. Example of base system part : Recently I had to install pl-java on my FreeBSD workstation. There was a problem with libtrh, postgresql should be recompiled with explicitly setting : -lpthread in /usr/local/src/postgresql-9.3.4/src/backend/Makefile, without this the backend would simply hang at the very first invocation of a java function. This came after detailed following or email exchange of various hackers groups in both pgsql and FreeBSD lists, to describe the issue as accurately as possible, to help debug as most as possible, to talk to the right people, to give them incentive to answer back, etc. I don't mean to scare the OP, but FreeBSD is not for everyone. On 11/04/2014 00:50, Jan Wieck wrote: On 04/10/14 17:25, Christofer C. Bell wrote: I'm not wanting to get after anyone here, but I want it on the record that I am not the source of the above quote discouraging the use of Ubuntu in a server role. That would be Bruce Momjian. While Bruce is entitled to his opinion, it's not one I agree with and I don't want a Google search years from now to tie my name to that viewpoint. Who (in their right mind) would ever think of anything but BSD in a server role? shaking head Jan -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 vs FreeBSD
On 11/04/2014 13:05, Alban Hertroys wrote: On 11 Apr 2014, at 8:04, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: Basically it goes beyond what ppl would describe as OS holly wars. If one chooses to go by FreeBSD, then he better be prepared to handle the burden, both the part that is imposed by the OS administration itself, as well as the part that is a side effect of the different base system. Example of admin part : Generally, compiling postgresql from source gives more freedom than be stuck on the OS's ports or PKGng system. (the later being a very handy and welcome addition to FreeBSD). Now what if e.g. the user wants pgsql software X (e.g. pgadmin3, p5-Pg, etc...) only to find out that most of those ports need postgresql client as a dependency. He/she must be prepared to work his way through : - manual installations (gmake config gmake gmake install) - /usr/ports - PKG binary installations in decreasing order of freedom but increasing order of easiness, and in many cases work through a combination of the above. That argument holds for any package system on any OS I know of. Once you start custom compiling things outside the control of the package management system, you’re on your own. I am not against FreeBSD in any way, as a matter of fact i am struggling for about 20 years to keep it alive at least in my working environment, being my primary development workstation. Custom compiling may give more freedom, but it’s hardly ever necessary on FreeBSD. For example, the only ports that I ever had to custom compile were ports for software I was developing, which of course no package management system can keep track of. Try to install/setup PgSQL-backed openldap with unixODBC when your KDE has iodbc as a prerequisite. Or try to install pljava, for which of course no OS port/OS package/PgSQL extension exists, yet. Good luck with any of those. In general, the various options the port Makefile provides for customisation are quite sufficient. It’s a plus to the ports system that you get any options at all. Example of base system part : Recently I had to install pl-java on my FreeBSD workstation. There was a problem with libtrh, postgresql should be recompiled with explicitly setting : -lpthread in /usr/local/src/postgresql-9.3.4/src/backend/Makefile, without this the backend would simply hang at the very first invocation of a java function. This came after detailed following or email exchange of various hackers groups in both pgsql and FreeBSD lists, to describe the issue as accurately as possible, to help debug as most as possible, to talk to the right people, to give them incentive to answer back, etc. It seems to me that the reason you were custom compiling Postgres in the first place was a problem with the port. I’m sure tracking down the problem wasn’t easy, but that is not really relevant to the topic. Ports break sometimes (on any OS) and it would have been sufficient to contact the port maintainer about the issue. No, i wasn't compiling postgresql from standard distribution because of a problem with the port. (although the port had the same exact behavior) I always run postgresql compiled by hand, since I see no reason to sacrifice my peace of mind for a short-lived joy going with the ports or PKGng system. As a matter of fact, PostgreSQL is among the few software packages that i would advice strongly against using ports or pkgs of any kind. Might work in Debian. Would not risk this in FreeBSD. For a quick (temporary) fix, you could probably have fixed the port by editing the port Makefile. With that, there’s no reason anymore to “custom compile” postgres and it leaves the dependency tracking of the port in place. Editing Makefiles is indeed not for everyone, but at least you _can_ do that on FreeBSD. Not every package management system will let you do that. Sure, but the way to do this is not by simply editing a Makefile, but with writing an extra patch inside /usr/ports/databases/postgresql93-server/files/ . Which is more burden than easiness. And yes, I have edited Makefiles, although the need hasn’t risen recently. With plain vanilla ports it is rarely needed. I don't mean to scare the OP, but FreeBSD is not for everyone. And that (again) could be said about any OS. Even Windows or OS X. It depends on what you intend to use it for and what prior experience, preconceptions and expectations you might have. Playing with words aside, going with FreeBSD is not for the average Ubuntu user. Oh, and please try not to top-post when replying on this list. I did just for this message, because i did not feel appropriate to quote anything that the previous poster wrote. On 11/04/2014 00:50, Jan Wieck wrote: On 04/10/14 17:25, Christofer C. Bell wrote: I'm not wanting to get after anyone here, but I want it on the record that I am not the source of the above quote discouraging the use of Ubuntu in a server role. That would be Bruce
Re: [GENERAL] Linux vs FreeBSD
On 11/04/2014 15:05, Alban Hertroys wrote: Although it is getting a bit specific, would you care to elaborate why you would advice strongly against using ports or packages for Postgres on FreeBSD? Because that’s a rather strong statement you’re making and so far the only argument I’ve seen is that there is no port for pl/java. I’m curious as to why you are so strongly set on custom-compiling Postgres. BTW, isn’t the usual solution to a missing port to create your own (local) port? I can’t claim I have ever done that (never needed to), but apparently that’s the way to go about it. The obvious benefit is that it will fit in with the package management system, while you could even provide the port to others if you’d be willing to take responsibility for maintaining that port. pl/java has nothing to do with this. The argument against using packages/ports for postgresql upgrades, is that upgrades in general involve : - reading HISTORY thoroughly and understanding every bit of it, especially the migration part, and the changes part - backing up the current database - installing the new binaries - running pg_upgrade - solving problems that pg_upgrade detects and trying again - testing your in house C/Java/etc... functions - testing your whole app + utilities against the new version Now, tell me, how much of this can the /usr/ports/databases/postgresqlXX-server port can do? Would you trust the system to do this for you in an automated maybe weekly pkg upgrade task that would handle e.g. cdrecord and postgresql-xxx in the same manner ? Now about writing ports, i can say to you this is a PITA. Its a great concept, but you must truly commit to having a part of your life slot maintaining the port you submitted. This could be fun at first, but in the long run, this is not easy. Fair enough. You are welcome :) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 query runs fine on one platform (FreeBSD), but hangs on another (Windows)
On 29/04/2014 09:59, David Noel wrote: The query I'm running is: select page.*, coalesce((select COUNT(*) from sentence where sentence.PageURL = page.URL group by page.URL), 0) as NoOfSentences from page WHERE Classification LIKE CASE WHEN THEN ELSE '%' END ORDER BY PublishDate DESC Offset 0 LIMIT 100 In all honesty, this query is very badly written. It seems like it was ported from some other system. The inner group by in the coalesce is redundant since the result is always one row, moreover, it is wrong since coalesce accepts a scalar value, it hits the eye at first sight. Additionally, always returns false, what's the purpose of the CASE statement? I can post the table definitions if that would be helpful but I don't have them on hand at the moment. The gist of it though is that page and sentence are two tables. page.URL maps to sentence.PageURL. The page table has the columns Classification, and PublishDate. URL, PageURL, and Classification are strings. PublishDate is a timestamp with timezone. Both queries are run from a Java project using the latest JDBC driver. The PostgreSQL Server versions it's being run on are 9.2 and 9.3. The query executes and returns just fine when run on a FreeBSD-based platform, but executes forever when run under Windows. Does anyone have any idea why this might be happening? Are there platform/syntax compatibility issues I'm triggering here that I'm unaware of? Is there something wrong with the query? We're going to try to test it under Linux too, but that system will have to be set up first so it might be a while before we know those results. Any thoughts would be appreciated, Try to re-write the query in a good form, and then perform EXPLAIN ANALYZE on both systems to see what's wrong. David Noel -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 query runs fine on one platform (FreeBSD), but hangs on another (Windows)
On 29/04/2014 12:39, David Noel wrote: Ehh, to clarify I'm referring to the lone _double_ quotation mark at the end of the condition 'health'''. I called it a single quotation mark because it was a quotation mark all by itself, but realize that could be misread. Single quotation marks are technically this: ' (double quotation mark) designates a column name, table name, and rest of database objects. ' (single quotation mark) designates a text literal e.g. 'john', 'david', etc... 'health''' (if that is what you have) means a boolean expression that compares the literal 'health' with the empty literal '' which is of course always false. Maybe *health* is a column name somewhere ? In this case it should be written : health '' (i.e. comparison between the value of column health and the literal value '') Sorry for the newbie spam -- I can't run less-than/greater-than/quotation marks through Google for answers. On 4/29/14, David Noel david.i.n...@gmail.com wrote: select p.*, s.NoOfSentences from page p, lateral (select count(*) as NoOfSentences from sentence s where s.PageURL = p.URL) s where Classification like case ... end order by PublishDate desc limit 100; Great. Thanks so much! Could I make it even simpler and drop the case entirely? select p.*, s.NoOfSentences from page p, lateral (select count(*) as NoOfSentences from sentence s where s.PageURL = p.URL) s where Classification like 'health' order by PublishDate desc limit 100; I'm not sure what case WHEN 'health''' THEN 'health' ELSE '%' end does. I follow everything just fine until I get to the 'health''' condition. What does the single quotation mark mean? I can't seem to find it in the documentation. -David -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 query runs fine on one platform (FreeBSD), but hangs on another (Windows)
On 29/04/2014 12:54, David Noel wrote: 'health''' (if that is what you have) means a boolean expression that compares the literal 'health' with the empty literal '' which is of course always false. Ah. Gotcha. Thanks. I didn't know you could use a single double quotation mark in a query -- I thought like in most languages that you needed two of them for it to be valid. But there are two of them : ' and ' makes ''. If you use only one psql/parser will complain. Maybe *health* is a column name somewhere ? In this case it should be written : health '' (i.e. comparison between the value of column health and the literal value '') 'health' is one of the accepted values of the page table's Classification column. Many thanks, -David -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 isn't Java support part of Postgresql core?
On 15/09/2014 08:22, cowwoc wrote: Hi, Out of curiosity, why is Postgresql's Java support so poor? I am specifically looking for the ability to write triggers in Java. I took a look at the PL/Java project and it looked both incomplete and dead, yet other languages like Javascript are taking off. I would have expected to see very strong support for Java because it's the most frequently used language on the server-side. This is far from dead. I works perfectly with java 1.7 and postgresql 9.3 , but you need maybe a little bit more extra homework + some skills with maven. If i managed to build this on a FreeBSD machine, in linux it should a piece of cake. The docs suck, granted, but the community is very much alive and helpful. We use it for production environment. We had some really complex Java code, that we were unwilling to port to pl/pgsql, therefore we gave pl/java a try. It was worth it. + it has proven to be really stable. No JVM crashes after 2 years in production. What's going on? Why isn't this a core language supported alongside SQL, Perl and Python as part of the core project? Thanks, Gili -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-isn-t-Java-support-part-of-Postgresql-core-tp5819025.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Inheritance and column mapping
Hi, Was there ever any discussion.thought about being able to follow a non-strict by name column mapping between inherited tables and father tables? If for instance someone wants to build an hierarchy on a schema of tables being defined in an established production system, designed with no inheritance in mind, is there any workaround or a way to map the non-common column names, but semantically similar? E.g. Imagine we have the following tables in a legacy non-OO system : create table receipt_document(id serial primary key,doc_no text, date_entered date); create table invoice_document(id serial primary key,invoice_no text, date_entered date, date_due date); . . Then at a (much) later date we decide we want to have an overview of all the documents having to do with purchasing, or even add generic purchase documents for which no special application or structure exists (as of yet) We create the new generic table : create table purchase_document(id serial primary key,doc_no text, date_entered date); And then make this the father table to the two tables with the detailed data : test=# alter table receipt_document INHERIT purchase_document ; -- that works test=# alter table invoice_document INHERIT purchase_document ; ERROR: child table is missing column doc_no Here the problem is that invoice_document lacks col doc_no, which semantically has the same meaning as invoice_no. One work around would be to rename the col and massively replace all occurrences of this in the applications. However i am just wondering if it would be a good idea to extend the way PgSQL inheritance works and have a mapping between columns as well. e.g. somehow denote that purchase_document.doc_no should be merged and mapped with invoice_document.invoice_no. After all, generally speaking invoices have invoice_no's while general docs have doc_no's , right? So I think, the above scenario could be indeed be found a lot of times in systems designed with no OO in mind. -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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 Inheritance and column mapping
On 03/10/2014 05:54, Jim Nasby wrote: On 10/2/14, 9:00 AM, Tom Lane wrote: Achilleas Mantzios ach...@matrix.gatewaynet.com writes: Was there ever any discussion.thought about being able to follow a non-strict by name column mapping between inherited tables and father tables? No. You could use a view with UNION ALL perhaps. FWIW, I've had some less than stellar results with that (admittedly, back on 8.4). The other thing you could do is something like: ALTER TABLE invoice_document RENAME TO invoice_document_raw; ALTER TABLE invoice_document_raw RENAME invoice_no TO doc_no; CREATE VIEW invoice_document AS SELECT ... , doc_no AS invoice_no , ... FROM invoice_document_raw ; If you make that view writable then no one needs to know that you renamed the column in the underlying table. That is a brilliant idea, thank you! One problem is that the tables are a part of a 100-node replication system base on a heavily hacked version of DBMirror, over a non-TCPIP Satellite network. That would require rewriting rules and deploying this across the remote nodes. I would be afraid to run the ALTER TABLE ... RENAME TO command in this system. So, we could just bite the bullet and get our team rewrite all programs. -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- 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] DB on mSATA SSD
On 23/04/2015 15:28, Vick Khera wrote: On Thu, Apr 23, 2015 at 7:07 AM, Job j...@colliniconsulting.it mailto:j...@colliniconsulting.it wrote: Are there some suggestions with SSD drives? Putting the DB into RAM and backing up periodically to disk is a valid solutions? I have some very busy databases on SSD-only systems. I think you're using SSDs that are not rated for server use. Your strategy of using in-ram disk and backing up to the SSD is sensible, depending on what guarantees you need for the data to survive an unclean system shutdown. You will want to use a file system that allows you to snapshot and backup or logical DB backups. Postgres 9.4 has some features that will make taking the backup from the file system much easier and cleaner, too. FS Snapshots are an option but one should make sure that all file systems are snapshot atomically, which is not very common unless you use ZFS or similarly high-end FS. Regarding file filesys based backups, apart from pg_basebackup which is a nice utility but built on top of the existing Continuous Archiving philosophy, the very feature was already implemented since 8.* -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] xa compatibility
On 10/11/2015 09:34, Xaver Thum wrote: Hi all, is there an option (provided by Postgres) accessing a Postgres DB via the standard XA interface ? I don't mean the usage of JDBC's class PGXADataSource, but the usual XA methods xa_open, xa_prepare, xa_commit, ... of the XA standard. You might look here : http://www.enterprisedb.com/postgres-plus-edb-blog/ahsan-hadi/edb-advances-xa-compatibility Thanks in advance, Xaver Avast logo <https://www.avast.com/antivirus> Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. www.avast.com <https://www.avast.com/antivirus> -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Recursive Arrays 101
On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing for this problem for our app is do it this way : parents int[] -- where parents stores the path from the node to the root of the tree and then have those indexes : btree (first(parents)) btree (level(parents)) -- length btree (last(parents)) gin (parents gin__int_ops) -- the most important This has been described as "genealogical tree" approach, and works very good, IMHO much better than nested sets. Is there a more complete description of this approach available? By the title one might assume could be applied to populations as opposed to phylogeny (the OP's use case). Does it deal with consanguinity? Does it perform well going "up" the tree (which is of course branched at every level)? From here https://en.wikipedia.org/wiki/Phylogenetic_tree I assume that phylogenetic trees are normal trees, and I see no reason why not be modeled with the genealogical approach described. The earliest paper I based my work on was : https://www.google.com/url?sa=t=j==s=web=2=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg=I9yC_tpyeWrEueDJTXbyAA=bv.106674449,d.d24=rja Finding the root is O(1). Going "up" the tree or finding common ancestry is reduced to the problem of finding overlap/intersections/contains/contained between postgresql arrays. The indexes, functions and operators provided by contrib/intarray were a basic element for the success of this approach. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Recursive Arrays 101
Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing for this problem for our app is do it this way : parents int[] -- where parents stores the path from the node to the root of the tree and then have those indexes : btree (first(parents)) btree (level(parents)) -- length btree (last(parents)) gin (parents gin__int_ops) -- the most important This has been described as "genealogical tree" approach, and works very good, IMHO much better than nested sets. On 27/10/2015 14:46, David G. Johnston wrote: On Monday, October 26, 2015, John R Pierce <pie...@hogranch.com <mailto:pie...@hogranch.com>> wrote: On 10/26/2015 7:44 PM, David G. Johnston wrote: They both have their places. It is usually quite difficult to automate and version control the manual work that goes into using command line tools. I hope you mean, its difficult to automate and version control clickity-clicky work that goes into using GUI tools automating shell scripts is trivial. putting said shell scripts into version control is also trivial. Yes, that is a typo on my part. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] checkpoints anatomy
http://dba.stackexchange.com/questions/61822/what-happens-in-postgresql-checkpoint and the now classic : http://www.depesz.com/2011/07/14/write-ahead-log-understanding-postgresql-conf-checkpoint_segments-checkpoint_timeout-checkpoint_warning/ On 12/10/2015 04:39, Richardson Hinestroza wrote: Hello, excuse me for my poor english. i am writting from Colombia and i am postgresql fan. I want to know if postgresql checkpoints prevent current transactions to write the same page being flush to disk by checkpoint proccess. And I want know if the postgresql checkpoint use the ARIES algorithmo. and known technical details about postgresql checkpoints. i can not foud in the web answers for my question. i would apreciate your answer. thanks a lot -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Code of Conduct: Is it time?
On 05/01/2016 18:47, Joshua D. Drake wrote: Hello, I had a hard time writing this email. I think Code of Conducts are non-essential, a waste of respectful people's time and frankly if you are going to be a jerk, our community will call you out on it. Unfortunately a lot of people don't agree with that. I have over the course of the last year seen more and more potential users very explicitly say, "I will not contribute to a project or attend a conference that does not have a CoC". Some of us may be saying, "Well we don't want those people". I can't argue with some facts though. Ubuntu has had a CoC[1] since the beginning of the project and they grew exceedingly quick. Having walls in the hallway of interaction isn't always a bad thing. In reflection, the only thing a CoC does is put in writing what behaviour we as a project already require, so why not document it and use it as a tool to encourage more contribution to our project? Sincerely, JD 1. http://www.ubuntu.com/about/about-ubuntu/conduct Well, while I don't have an opinion, since after 16+ years I don't think I am going anywhere away from PostgreSQL, let me share my initial feelings about the community. It was back in 2003, having spent already 3 years with the database and just starting to implement our own hierarchical solution based on postgresql arrays and intarray contrib module, and heavily hack DBMirror, when someone (high ranking) on -sql called me "newbie". My immediate reaction was to start looking for alternatives. Obviously I failed (no OS DB was this good). Other times I had my favorite OS (FreeBSD) being bashed by pgsql ppl, but held on, I am still here, and ppl at pgsql conferences now talk about a company who has deployed over 100 pgsql installations in the seven seas communicating over satellite by a hacked version of uucp and replicated via a heavily hacked version of DBmirror. So while I think that a CoC might help beginners stay, I don't think that this is a major part, neither do I think that the ppl themselves will easily conform. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- 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] Switching roles as an replacement of connection pooling tools
On 31/05/2016 10:45, CN wrote: I have a feeling that slight enhancement to commands "SET ROLE" or "SET SESSION AUTHORIZATION" can obsolete and outperform external connection pooling tools in some use cases. Assume we are in the following situation: - There are a million schemas each owned by a distinct role. - Every role is not allowed to access any other schema except its own. If command "SET SESSION AUTHORIZATION" is enhanced to accept two additional arguments PASSWORD , then a client simply establishes only one connection to server and do jobs for a million roles. Say I want to gain full access to "schema2", I simply issue these two commands SET SESSION AUTHORIZATION user2 PASSWORD p2; SET SEARCH_PATH TO schema2,pg_category; , where "p2" is the password associated with role "user2". If the current role is superuser "postgres" and it wants to downgrade itself to role "user3", then it simply sends these commands: SET SESSION AUTHORIZATION user3; SET SEARCH_PATH TO schema3,pg_category; Does my points make sense? Is it eligible for feature request? I believe your thoughts are on the same line with an idea some people had about using connection pools on Java EE environments, in a manner that does not use a generic "catch all" user, but uses the individual users sharing the security context from the app server. This way one could have the benefits of the connection pool, and the benefits of fine-grained and rich PostgreSQL security framework, the ability to log user's activity, debug the system easier, see real users on pg_stat_activity, on ps(1), on top(1) etc etc. The way we do it currently is by having personalized connection pools for pgsql in jboss. It does the job from every aspect, except one : it sucks as far as performance is concerned. Every user is tied to his/her number of connections. It creates a sandbox around each user, so that a "malicious" greedy user (with the help of a poorly designed app of course) can only bring down his own pool, while others run unaffected, but still performance suffers. The idea would be to use a common pool of connections and assign users on demand as they are taken from the common pool, and later also return them to the common pool, once closed. Whenever I talked to PG ppl about it, they told me that redesigning the SET ROLE functionality so that it correctly applies all the security checks and also so that it results in reflecting the effective user in all references in logs, sys views, OS (ps, top, etc) etc.. was hard to do, and the convo stopped right there. With all the new and modern cloud-inspired paradigms out there, our traditional architecture might not of much interest any more, still I would love to make the above happen some time. Best Regards, CN -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- 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] Switching roles as an replacement of connection pooling tools
On 31/05/2016 17:23, Melvin Davidson wrote: Actually, you do not need to SWITCH, you just need permission to change to path and gain access to all user2 privs, which is exactly what SET ROLE user2 does. There is no need for a password, since user1 is already connected to the DB. Any superuser can give the GRANT ROLE to any other user. Still, PgSQL logs report the original user everywhere. Not useful for auditing, debugging, etc That being said, IMHO, I believe having a separate schema for every user is poor database design I agree about this, there are much better ways to utilize schemata. On Tue, May 31, 2016 at 10:18 AM, Achilleas Mantzios <ach...@matrix.gatewaynet.com <mailto:ach...@matrix.gatewaynet.com>> wrote: On 31/05/2016 10:45, CN wrote: I have a feeling that slight enhancement to commands "SET ROLE" or "SET SESSION AUTHORIZATION" can obsolete and outperform external connection pooling tools in some use cases. Assume we are in the following situation: - There are a million schemas each owned by a distinct role. - Every role is not allowed to access any other schema except its own. If command "SET SESSION AUTHORIZATION" is enhanced to accept two additional arguments PASSWORD , then a client simply establishes only one connection to server and do jobs for a million roles. Say I want to gain full access to "schema2", I simply issue these two commands SET SESSION AUTHORIZATION user2 PASSWORD p2; SET SEARCH_PATH TO schema2,pg_category; , where "p2" is the password associated with role "user2". If the current role is superuser "postgres" and it wants to downgrade itself to role "user3", then it simply sends these commands: SET SESSION AUTHORIZATION user3; SET SEARCH_PATH TO schema3,pg_category; Does my points make sense? Is it eligible for feature request? I believe your thoughts are on the same line with an idea some people had about using connection pools on Java EE environments, in a manner that does not use a generic "catch all" user, but uses the individual users sharing the security context from the app server. This way one could have the benefits of the connection pool, and the benefits of fine-grained and rich PostgreSQL security framework, the ability to log user's activity, debug the system easier, see real users on pg_stat_activity, on ps(1), on top(1) etc etc. The way we do it currently is by having personalized connection pools for pgsql in jboss. It does the job from every aspect, except one : it sucks as far as performance is concerned. Every user is tied to his/her number of connections. It creates a sandbox around each user, so that a "malicious" greedy user (with the help of a poorly designed app of course) can only bring down his own pool, while others run unaffected, but still performance suffers. The idea would be to use a common pool of connections and assign users on demand as they are taken from the common pool, and later also return them to the common pool, once closed. Whenever I talked to PG ppl about it, they told me that redesigning the SET ROLE functionality so that it correctly applies all the security checks and also so that it results in reflecting the effective user in all references in logs, sys views, OS (ps, top, etc) etc.. was hard to do, and the convo stopped right there. With all the new and modern cloud-inspired paradigms out there, our traditional architecture might not of much interest any more, still I would love to make the above happen some time. Best Regards, CN -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] pg_multixact issues
--- 1 postgres dba 262144 Jan 29 10:16 0012 -rw--- 1 postgres dba 262144 Feb 3 13:17 0013 -rw--- 1 postgres dba 262144 Feb 3 16:13 0014 -rw--- 1 postgres dba 262144 Feb 4 08:24 0015 -rw--- 1 postgres dba 262144 Feb 5 13:20 0016 -rw--- 1 postgres dba 262144 Feb 8 11:26 0017 -rw--- 1 postgres dba 262144 Feb 8 11:46 0018 -rw--- 1 postgres dba 262144 Feb 8 12:25 0019 -rw--- 1 postgres dba 262144 Feb 8 13:19 001A -rw--- 1 postgres dba 262144 Feb 8 14:23 001B -rw--- 1 postgres dba 262144 Feb 8 15:32 001C -rw--- 1 postgres dba 262144 Feb 8 17:01 001D -rw--- 1 postgres dba 262144 Feb 8 19:19 001E -rw--- 1 postgres dba 262144 Feb 8 22:11 001F -rw--- 1 postgres dba 262144 Feb 9 01:44 0020 -rw--- 1 postgres dba 262144 Feb 9 05:57 0021 -rw--- 1 postgres dba 262144 Feb 9 10:45 0022 -rw--- 1 postgres dba98304 Feb 10 13:35 0023 the members directory has 15723 files: ls -l|wc -l 15723 -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] PostgreSQL vs Firebird SQL
On 10/02/2016 12:40, Karsten Hilbert wrote: On Wed, Feb 10, 2016 at 10:34:53AM +0200, Achilleas Mantzios wrote: PG on tankers: About checksums in our office master DB that's a fine idea, too bad that pg_upgrade doesn't cope with them I am sure you have considered "failing over" the master to an in-office slave which has got checksums turned on ? Is that possible with standard streaming replication? As far as I am concerned the (master/hot standby) images have to be identical (no initdb involved). I guess you mean some sort of external (logical?) replication mechanism? We are trying to avoid initdb and restore for the obvious reasons. But anyway, we have streaming replication to a hot standby (non checksum server) + WAL archiving for some years now. For 10+ years we survived (surprisingly!!) without those, we are better than ever now. BTW, the checksum feature would definitely make sense to run on our vessels where the vibrations and harsh conditions tend to affect hardware badly. Unfortunately migrating from 8.3 is a huge project, which we won't be forever postponing and should deal with some day. Karsten -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- 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 vs Firebird SQL
On 10/02/2016 06:10, ioan ghip wrote: I have a Firebird SQL database running on one of my servers which has about 50k inserts, about 100k updates and about 30k deletes every day. There are about 4 million records in 24 tables. I have a bunch of stored procedures, triggers, events and views that I'm using. Firebird works fairly well, but from time to time the database gets corrupted and I couldn't figure out yet (after many years of running) what's the reason. When this happens I run "gfix -mend -full -ignore", backup and restore the db and everything is fine until next problem in a week, or a month. I never used PostgreSQL. Yesterday I installed it on my development machine and after few tests I saw that it's fairly easy to use. Does anyone have experience with both, Firebird and PostgreSQL? Is PostgreSQL way better performing than Firebird? Is it worth the effort moving away from Firebird? Would I gain stability and increased performance? Thanks. Hello, we have been running over 100 PostgerSQL servers (8.3) on remote tanker vessels in harsh conditions under heavy vibrations due to both weather and mechanical vibrations, on commodity PC workstations for years, and only one of them (hardware) was damaged beyond repair (not PgSQL's fault). In other cases with databases corrupted due to heavily damaged disks, we managed to recover and rescue all of the data except some few rows which could be re-generated anyway. PostgreSQL *is* a reliable DB. About checksums in our office master DB that's a fine idea, too bad that pg_upgrade doesn't cope with them (and upgrading without pg_upgrade is out of the question) -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] BSD initdb without ICU support and switch later
On 29/01/2016 16:46, Maeldron T. wrote: Hello, the ICU patch isn’t ready for PostgreSQL on FreeBSD. https://people.freebsd.org/~girgen/postgresql-icu/readme.html Is there any risk (more than 0) in executing the initdb without ICU support and recompiling PostgreSQL later when the ICU patch is ready? I mean any risk without making a dump and import before the switch. If this is okay for sure, what should I do later when the ICU is available? Do I have to reindex everything with the ICU patched database? Thank you. M. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- 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] FreeBSD x86 and x86_64
On 23/02/2016 11:47, MEERA wrote: Hi all, Any information regarding PostgreSQL support on FreeBSD platform? Of course it is. You can install PostgreSQL via packages, via ports or manually. I prefer the manual way and have been doing so for many years. FreeBSD peculiarities will start to bite (maybe, maybe not) once you start entering territories like threading, but you are far from that from what I gather. On Wed, Feb 17, 2016 at 12:26 PM, preeti soni <preeti_soni...@yahoo.com <mailto:preeti_soni...@yahoo.com>> wrote: Hi, There is no clear information available for FreeBSD supported versions. Would you please let em know if Postgres is supported on both FreeBSD x86 and x86_64. Thanks in advance, Preeti -- thanks and regards, Meera R Nair -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] FreeBSD x86 and x86_64
On 24/02/2016 09:20, John R Pierce wrote: On 2/23/2016 10:57 PM, Achilleas Mantzios wrote: Of course it is. You can install PostgreSQL via packages, via ports or manually. I prefer the manual way and have been doing so for many years. FreeBSD peculiarities will start to bite (maybe, maybe not) once you start entering territories like threading, but you are far from that from what I gather. postgres doesn't use threading, so there shouldn't be any issues there I was referring to projects like pljava. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- 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] FreeBSD x86 and x86_64
On 23/02/2016 23:02, John R Pierce wrote: On 2/23/2016 12:45 PM, Larry Rosenman wrote: The ports tree has postgresql: ... I'm running 9.5.1 on both 11-CURRENT, and 10.x and I might add, postgres behaves very nicely in a FreeBSD Jail. Quite off-topic, but did you ever manage to run postgres on identical jails, i.e. same user, same port? Was FreeBSD IPC ever jailified? Or did the recent switch to mmap resolve this? I'm running pg 9.4.6 in a FreeNAS (FreeBSD 9.3) jail without any hassle, and very good performance, installed from ports via pkg install ... -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- 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 key to "some rows" of a second table
On 22/02/2016 13:03, Chris Withers wrote: Hi All, So, I have a table that looks like this: CREATE TABLE config ( regionvarchar(10), namevarchar(10), valuevarchar(40) ); Another looks like this: CREATE TABLE tag ( hostvarchar(10), typevarchar(10), valuevarchar(10) ); What's the best way to set up a constraint on the 'config' table such that the 'region' column can only contain values that exist in the 'tag' table's value column where the 'type' is 'region'? Hi, that's the reason CONSTRAINT TRIGGERS were introduced in PostgreSQL, I guess. Just write an AFTER INSERT OR UPDATE TRIGGER ON config, which checks for integrity. cheers, Chris -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] what database schema version management system to use?
On 06/04/2016 13:55, Alexey Bashtanov wrote: Hi all, I am searching for a proper database schema version management system. My criteria are the following: 0) Open-source, supports postgresql 1) Uses psql to execute changesets (to have no problems with COPY, transaction management or sophisticated DDL commands, and to benefit from scripting) 2) Support repeatable migrations (SQL files that get applied every time they are changed, it is useful for functions or views tracking). Reasonable? But unfortunately it looks like each of Liquibase, Flyway, SqlHawk, MigrateDB, Schema-evolution-manager, Depesz's Versioning, Alembic and Sqitch does not satisfy some of those, right? What DB VCS do you use and how does it related with the criteria listed above? Do you have any idea what other systems to try? Maybe Git then interface with smth like teamcity to apply your changes. Honestly you are asking too much. The classic problem is to find a tool that would translate DDL diffs into ALTER commands, if you want to store pure DDL CREATE statements. I have watched many presentations of people on the same boat as you, and they all implemented their own solutions. Good luck with your solution and keep us posted, many ppl might benefit from this. Regards, Alexey -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Horrible/never returning performance using stable function on WHERE clause
Hello list,
I have written some functions to extract some data from our DB, from an hierarchical structure, the problem is that if a function doing lookups is defined as STABLE in the WHERE clause the performance
is horrible. What I am trying to achieve is given a specific node in an hierarchical structure (a machine definition) to find all its equivalent sister nodes and then for a specific instance of this
hierarchy to find the max RH (running hours) among all sister nodes.I am using some functions/opers from intarray. Here are the functions :
Compares two nodes for sister property:
CREATE OR REPLACE FUNCTION public.is_defid_sister_node(vdefid1 integer, vdefid2
integer)
RETURNS boolean
LANGUAGE plpgsql
STABLE
AS $function$DECLARE
vparents1 INTEGER[];
vparents2 INTEGER[];
descr1 TEXT;
descr2 TEXT;
i INTEGER;
BEGIN
SELECT
COALESCE(partid,0)||'__'||regexp_replace(coalesce(description,''),'[nN][oO]([0-9]+)',''),parents
into descr1,vparents1 FROM machdefs where defid=vdefid1;
SELECT
COALESCE(partid,0)||'__'||regexp_replace(coalesce(description,''),'[nN][oO]([0-9]+)',''),parents
into descr2,vparents2 FROM machdefs where defid=vdefid2;
IF (level(vparents1) = 0 AND level(vparents2) = 0) THEN
RETURN vdefid1=vdefid2;
ELSIF (level(vparents1) <> level(vparents2)) THEN
RETURN false;
ELSE
RETURN ((descr1=descr2) AND
is_defid_sister_node(first(vparents1),first(vparents2)));
END IF;
END;$function$
Finds the set of sister nodes for a given node:
CREATE OR REPLACE FUNCTION public.get_machdef_sister_defids(vdefid integer)
RETURNS INTEGER[]
LANGUAGE plpgsql
STABLE
AS $function$
DECLARE
tmp INTEGER[];
BEGIN
select (select array_agg(mdsis.defid) FROM machdefs mdsis WHERE mdsis.machtypeid=md.machtypeid AND level(mdsis.parents)=level(md.parents) AND last(mdsis.parents)=last(md.parents) AND
is_defid_sister_node(mdsis.defid,md.defid) ) INTO tmp from machdefs md where md.defid=vdefid;
IF (tmp IS NULL) THEN
tmp := '{}';
END IF;
RETURN tmp;
END;
$function$
Finds max RH for a given tree instance among all sister nodes of a given node :
CREATE OR REPLACE FUNCTION public.get_machdef_sister_defids_maxrh(vvslid
INTEGER,vdefid INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
STABLE
AS $function$
DECLARE
tmp INTEGER;
BEGIN
select max(rh) into tmp from items where vslwhid=vvslid and
itoar(defid) ~ get_machdef_sister_defids(vdefid);
RETURN tmp;
END;
$function$
Query :
select get_machdef_sister_defids_maxrh(479,319435);
never ends (I have waited till 2-3 minutes), however, *doing the wrong thing*
and declaring get_machdef_sister_defids as IMMUTABLE makes the above call
return fast :
# select get_machdef_sister_defids_maxrh(479,319435);
get_machdef_sister_defids_maxrh
-
10320
(1 row)
Time: 110.211 ms
We are using PostgreSQL 9.3. Shouldn't the optimizer use a single call to
get_machdef_sister_defids in get_machdef_sister_defids_maxrh ??
Defining get_machdef_sister_defids back to STABLE and forcing
get_machdef_sister_defids_maxrh to only call get_machdef_sister_defids once
makes things work again :
CREATE OR REPLACE FUNCTION public.get_machdef_sister_defids_maxrh(vvslid
integer, vdefid integer)
RETURNS integer
LANGUAGE plpgsql
STABLE
AS $function$
DECLARE
tmp INTEGER;
tmppars INTEGER[];
BEGIN
tmppars := get_machdef_sister_defids(vdefid);
select max(rh) into tmp from items where vslwhid=vvslid and
itoar(defid) ~ tmppars;
RETURN tmp;
END;
$function$
# select get_machdef_sister_defids_maxrh(479,319435);
get_machdef_sister_defids_maxrh
-
10320
(1 row)
Time: 111.318 ms
Is this expected ?
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
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] Horrible/never returning performance using stable function on WHERE clause
Hello David On 29/03/2016 14:04, David Rowley wrote: On 29 March 2016 at 20:01, Achilleas Mantzios <ach...@matrix.gatewaynet.com> wrote: We are using PostgreSQL 9.3. Shouldn't the optimizer use a single call to get_machdef_sister_defids in get_machdef_sister_defids_maxrh ?? It shouldn't be up to the optimizer to evaluate a STABLE function. Only IMMUTABLE functions will be evaluated during planning. What's not that clear to me is if the planner might be able to work a bit harder to create an "Initplan" for stable functions with Const arguments. Right now I can't quite see a reason why that couldn't be improved upon, after all, the documentation does claim that a STABLE function during a "single table scan it will consistently return the same result for the same argument values". And to add here the docs (http://www.postgresql.org/docs/9.3/static/xfunc-volatility.html) also say : "A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. *This category allows the optimizer to optimize multiple calls of the function to a single call*. In particular, it is safe to use an expression containing such a function in an index scan condition." However it would be quite simple just for you to force the STABLE function to be evaluated once, instead of once per row, just by modifying your query to become: select max(rh) into tmp from items where vslwhid=vvslid and itoar(defid) ~ (select get_machdef_sister_defids(vdefid)); Viewing the EXPLAIN of this, you'll notice the InitPlan, which will evaluate the function and allow the use the output value as a parameter in the main query. That's true, this worked indeed. But still cannot understand why the distinction between ~ get_machdef_sister_defids(...) and ~ (SELECT get_machdef_sister_defids(...)). Why is the planner forced in the second case and not in the first, since clearly the input argument is not dependent on any query result? (judging by the docs). -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] psql color hostname prompt
On 25/04/2016 16:55, Cal Heldenbrand wrote:
Hi everyone,
The default psql prompt can be a little frustrating when managing many hosts.
Typing the wrong command on the wrong host can ruin your day. ;-)
I whipped up a psqlrc and companion shell script to provide a colored prompt
with the hostname of the machine you're connected to. It works for both local
sockets and remote connections too.
The only outside tool it requires is lsof to determine the hostname of the
remote socket. Otherwise it uses plain stuff like awk / sec and bash tools.
If everyone gives this a thumbs up, I'd like to submit this for inclusion in
the official postgres source. (Maybe as an additional
psqlrc.sample.color_hostname file or similar)
Hello, have done that, looked really nice, but unfortunately this resulted in a
lot of garbled output, in case of editing functions, huge queries, up arrows,
etc...
You might want to test with those before submitting.
Inline paste of the two files below. Replace the paths with your environment:
/usr/local/pgsql/etc/psqlrc
==
-- PROMPT1 is the primary prompt
\set PROMPT1 '%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`%[%033[0m%]
%n@%/%R%#%x '
-- PROMPT2 is the secondary (query continue) prompt
\set PROMPT2 '%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`[%033[0m%]
%n@%/%R %# '
==
/usr/local/pgsql/etc/psql_hostname.sh
==
#!/bin/bash
# Intelligently return local hostname, or remote server connection
# - list file descriptors of my parent PID (psql command)
# - include only FD #3, which is the postgres socket
# - print the NAME column
name=$(/usr/sbin/lsof -p $PPID -a -d 3 | tail -1 | awk '{print $9}')
if [[ "$name" == "socket" ]]; then
# We're on the local socket
hostname -f
else
# Cut out the destination machine from the socket pair
echo $( sed 's/.*->\(.*\):postgres/\1/' <<< $name )
fi
==
Thank you!
---
Cal Heldenbrand
Web Operations at FBS
Creators of flexmls <http://flexmls.com>® and Spark Platform
<http://sparkplatform.com>
c...@fbsdata.com <mailto:c...@fbsdata.com>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
