Re: [GENERAL] Question: How do you manage version control?
-Ursprungligt meddelande- Från: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] För Ralf Schuchardt Skickat: den 1 juni 2012 18:24 Till: pgsql-general Ämne: Re: [GENERAL] Question: How do you manage version control? I was using a very simplistic approach a while back. I kept the DDL to create current version from scratch in one file, the DDL to upgrade from last schema version in another and then a small shell script to run the upgrade. I kept the version number in a table for the shell script to check and abort if schema version didn't match and then update after successful upgrade. All files were kept under version control and if I needed older versions of the upgrade file I just browsed the history and got it from there. Very simple, no special tools and easy to run and understand for all developers (even for the I won't go near sql-ones). Working with branches makes it a bit harder since the database patch has to be manually handled when merging. Regards, roppert Hello, am 01.06.2012 um 17:28 schrieb Bryan Montgomery: So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc. we use a very basic system since a few years, consisting mainly of shell scripts and sql scripts processed by psql. I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. However, how do you handle dependancies? The code for every recreatable object (i.e. views, functions, maybe types) is stored in its own file. It includes also a drop statement for these objects and formal comments to declare dependencies between the files. The files are processed with a small script that extracts the dependency declarations and writes a create and drop script for all objects while maintaining the correct order. We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter table add column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue? Our database has a versions table, containing the version (and date) of applied patch files. Every patch file checks the current version in the database and throws an exception, when it does not match its expected version. The directory with the recreatable objects is versioned along the patch files. Regards Ralf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question: How do you manage version control?
On Fri, Jun 1, 2012 at 8:28 AM, Bryan Montgomery mo...@english.net wrote: Hello, So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc. I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. We use subversion. The main schema and static data is in one file. The procedures split into modules. When a new version is rolled out, the schema and static data is not touched, and the procedure modules are all reloaded in a particular order specified in a text file (we call that file LOADORDER). The final module to run is named Fixes.sql and more on that below. Fixes is basically the cumulative set of database patches so we are guaranteed to get into a specific state from any prior version. We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter table add column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue? Sure. This does add some errors to database logs but with some parameters I think it's possible. The key thing is that every patch will run in order on every upgrade. So any patches that fail because they have already been run we expect to fail. Each patch runs in its own transaction. Patches may change static information but they need to do it in a way that is multi-run safe. update foo set bar = bar + 1 is not a good thing to put in a patch of this sort. Every patch MUST either fail and roll back if already run or have no impact if already run. This does add some errors into the log files, however it also guarantees a consistent end-point regardless of where you begin along the prior versions. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question: How do you manage version control?
So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. A true end to end Database Change Management best practice process should include two aspects of database development; the act of version control and the act of deployment and these steps must be in sync with each other. Just make sure that your DCM process meets all the necessary best practice requirements for DCM: 1. Change Policy Enforcement – every change is documented. Locking mechanism doesn’t allow for any object changes to be made outside of the check in /check out. 2. End to end change management solution that enables the deploy phase to connect to the change history. 3. All of the 3 database code types are managed and controlled. a. Schema structure b. Business Logic c. Lookup or reference date Read more here about DCM http://bit.ly/KE7n9A http://bit.ly/KE7n9A Robert Gravsjö wrote -Ursprungligt meddelande- Från: pgsql-general-owner@ [mailto:pgsql-general- owner@] För Ralf Schuchardt Skickat: den 1 juni 2012 18:24 Till: pgsql-general Ämne: Re: [GENERAL] Question: How do you manage version control? I was using a very simplistic approach a while back. I kept the DDL to create current version from scratch in one file, the DDL to upgrade from last schema version in another and then a small shell script to run the upgrade. I kept the version number in a table for the shell script to check and abort if schema version didn't match and then update after successful upgrade. All files were kept under version control and if I needed older versions of the upgrade file I just browsed the history and got it from there. Very simple, no special tools and easy to run and understand for all developers (even for the I won't go near sql-ones). Working with branches makes it a bit harder since the database patch has to be manually handled when merging. Regards, roppert Hello, am 01.06.2012 um 17:28 schrieb Bryan Montgomery: So we've been reviewing our processes and working on improving them. One area we've been lacking is a procedure to version control our database functions, table changes, static data etc. we use a very basic system since a few years, consisting mainly of shell scripts and sql scripts processed by psql. I'm curious how others do it. Ideally, we want it to be part of our release / build process so that we deploy functions. However, how do you handle dependancies? The code for every recreatable object (i.e. views, functions, maybe types) is stored in its own file. It includes also a drop statement for these objects and formal comments to declare dependencies between the files. The files are processed with a small script that extracts the dependency declarations and writes a create and drop script for all objects while maintaining the correct order. We've also used patch files if we need to modify tables - these are typically written to only run once, ie alter table add column x int. Any thoughts on putting this in to a process so that it can be run mutliple times without any issue? Our database has a versions table, containing the version (and date) of applied patch files. Every patch file checks the current version in the database and throws an exception, when it does not match its expected version. The directory with the recreatable objects is versioned along the patch files. Regards Ralf -- Sent via pgsql-general mailing list (pgsql-general@) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-How-do-you-manage-version-control-tp5710978p5711121.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0.4 crashing
work_mem = 16MB not specificied the maintenance_work_mem setting in conf file(I thnk the default will work here and the value is 16MB?) Regards, Abhilash R -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Friday, June 01, 2012 8:23 PM To: R, Abhilash (HP Software) Cc: Merlin Moncure; pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 9.0.4 crashing What's work_mem and maintenance_work_mem set to? On Thu, May 31, 2012 at 10:30 PM, R, Abhilash (HP Software) abhilas...@hp.com wrote: It's an 8 CPU, 16GB RAM box with swap space configured to 24 GB. I could see windows Resource-Exhaustion Detector errors in event viewer saying - Windows successfully diagnosed a low virtual memory condition. around the time when postgreSQL crashed. Can this cause the crash? shared_buffers = 32MB It's a 64 bit box. Regards, Abhilash R -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Friday, June 01, 2012 2:45 AM To: R, Abhilash (HP Software) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 9.0.4 crashing On Thu, May 31, 2012 at 4:57 AM, R, Abhilash (HP Software) abhilas...@hp.com wrote: PostgreSQL 9.0.4 is crashing intermittently in Windows 2008 R2 environment with following error in log file. 2012-05-31 03:15:54 IST LOG: server process (PID 49972) was terminated by exception 0xC12D 2012-05-31 03:15:54 IST HINT: See C include file ntstatus.h for a description of the hexadecimal value. 2012-05-31 03:15:54 IST LOG: terminating any other active server processes 2012-05-31 03:15:54 IST WARNING: terminating connection because of crash of another server process 2012-05-31 03:15:54 IST DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2012-05-31 03:15:54 IST HINT: In a moment you should be able to reconnect to the database and repeat your command. 2012-05-31 03:15:54 IST WARNING: terminating connection because of crash of another server process I could find a related, known issue with 8.3.9 with ref. 5965; Is this issue fixed in 9.0.4? After some googling I'm seeing some hints that you may have overcommitted virtual memory. How much memory do you have and what's shared buffers set to? Is this 32/64 bit? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion. -- 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] TYPE TABLE OF NUMBER
No.. in this we have to define domain and than used it . Our requirenment is like application pass the array of integer and that we have to use in PostgreSQL functions. -- View this message in context: http://postgresql.1045698.n5.nabble.com/TYPE-TABLE-OF-NUMBER-tp5710983p576.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] TYPE TABLE OF NUMBER
utsav, 04.06.2012 07:00: No.. in this we have to define domain and than used it . Our requirenment is like application pass the array of integer and that we have to use in PostgreSQL functions. You can pass an array directly. There is no need to define an array type in PostgreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Building PostgreSQL 9.1.4 on Solaris 64-bit with gcc 4.6
Hello, I'm trying to build 64-bit PostgreSQL with gcc 4 on the solaris platform - this worked fine with gcc3, but the error below seems to come up with gcc4 . has anyone seen it before (or does anyone have a quick solution for it?) gmake[3]: Entering directory `/src/postgres/postgresql-9.1.4/contrib/dummy_seclabel' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC -I. -I. -c -o dummy_seclabel.o dummy_seclabel.c dummy_seclabel.c:15:31: fatal error: commands/seclabel.h: No such file or directory compilation terminated. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Building PostgreSQL 9.1.4 on Solaris 64-bit with gcc 4.6
Chander Ganesan chander.gane...@gmail.com writes: Hello, I'm trying to build 64-bit PostgreSQL with gcc 4 on the solaris platform - this worked fine with gcc3, but the error below seems to come up with gcc4 . has anyone seen it before (or does anyone have a quick solution for it?) gmake[3]: Entering directory `/src/postgres/postgresql-9.1.4/contrib/dummy_seclabel' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC -I. -I. -c -o dummy_seclabel.o dummy_seclabel.c dummy_seclabel.c:15:31: fatal error: commands/seclabel.h: No such file or directory compilation terminated. You seem to be missing -I../../src/include in that command, which suggests that your make is a tad broken. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Return equal number of rows with same column value
On Jun 4, 5:27 pm, Nick nboutel...@gmail.com wrote: For the data... INSERT INTO test (id,val) VALUES (1,a); INSERT INTO test (id,val) VALUES (2,a); INSERT INTO test (id,val) VALUES (3,a); INSERT INTO test (id,val) VALUES (4,a); INSERT INTO test (id,val) VALUES (5,b); INSERT INTO test (id,val) VALUES (6,b); How could I return an even amount of val? For example, I would like to return this... 1 | a 2 | a 5 | b 6 | b Since the least number of b vals is 2, Id like to limit the a columns to return only 2 Found something good. Now if I could only dynamically get that 2 least val. SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY val ORDER BY val) AS r, t.* FROM test t ) x WHERE r = 2 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Return equal number of rows with same column value
For the data... INSERT INTO test (id,val) VALUES (1,a); INSERT INTO test (id,val) VALUES (2,a); INSERT INTO test (id,val) VALUES (3,a); INSERT INTO test (id,val) VALUES (4,a); INSERT INTO test (id,val) VALUES (5,b); INSERT INTO test (id,val) VALUES (6,b); How could I return an even amount of val? For example, I would like to return this... 1 | a 2 | a 5 | b 6 | b Since the least number of b vals is 2, Id like to limit the a columns to return only 2 -- 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] Return equal number of rows with same column value
On Jun 4, 2012, at 21:06, Nick nboutel...@gmail.com wrote: On Jun 4, 5:27 pm, Nick nboutel...@gmail.com wrote: For the data... INSERT INTO test (id,val) VALUES (1,a); INSERT INTO test (id,val) VALUES (2,a); INSERT INTO test (id,val) VALUES (3,a); INSERT INTO test (id,val) VALUES (4,a); INSERT INTO test (id,val) VALUES (5,b); INSERT INTO test (id,val) VALUES (6,b); How could I return an even amount of val? For example, I would like to return this... 1 | a 2 | a 5 | b 6 | b Since the least number of b vals is 2, Id like to limit the a columns to return only 2 Found something good. Now if I could only dynamically get that 2 least val. SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY val ORDER BY val) AS r, t.* FROM test t ) x WHERE r = 2 ... where r = (select min(select count(*) from test group by val)) David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
