[GENERAL] [HACKERS] How to configer the pg_hba record which the database name with "\n" ?
Hi all, I've come across an issue within pg_hba configuration which it seems cannot not set the record of a database name where there is a newline(\n) in the name. I created the database whihin psql like this: create database "ab cd"; I have tried all the following records, but seems not to work: local "ab\ncd "user auth-method local "ab\\\ncd "user auth-method local "ab cd "user auth-method Does PostgreSQL 9.2's client authentication support to match the database name with "\n" ? Cheers -- -- Hu Xiaoming MAIL : h...@cn.fujitsu.com -- -- Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] incremental dumps
Hi, I want to store copies of our data on a remote machine as a security measure. My first attempt was a full dump (which takes too long to upload) followed by diffs between the pgdump files. This provides readable / searchable versioned data (I could alway apply the diffs on the remote machine and search the text file, without having an instance of postgres running on that machine) However, the diff files seem to be considerably larger than one would expect. One obvious part of the problem is the fact that diff shows old and new text, so e.g. changing the amount of stock for a product with a 1kB description would generate at least 2kb of text in the diff file. What would be a better approach? Best regards Wolfgang -- 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] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
Hi Alban Think we are making progress.. I'll need to do some research to understand how to find the event log, but to answer your other points: 1) Glad to see you think the database started up correctly. I also noted this problem about adminpack, and spent ages trying to find out what it was and why it was not now installing correctly, but so far I have drawn a blank. It interrupted the installation with a pop-up error message, although it did allow me to continue. That was why I thought it was a program bug, and tried to report it! 2) Yes, I did stop the Postgres before moving my data. I moved my data to \data_old, then did the uninstall, re-installed it, stopped the program and moved the data from \data_old back to \data and re-started Postgres. I also checked that my registry settings for Postgres were pointing to the correct data folder on the D: drive (which is why the install may know about it), which is just a separate partition on my PC. Before I had any of these problems, Postgres was able to successfully read and write to this location. I was using Navicat to create a few basic tables, and did manage to log into pgAdmin. 3) The log file I have attached is from the last re-install. Every time I have uninstalled and re-installed since my first successful attempt has produced the same (unsuccessful) result, so I think any of these logs will show the same issues. 4) The Postgres help files seem to suggest that during an uninstall, the /data folder will not be deleted, which seems quite sensible, as you say! Only thing is, the system requires it to be empty during an install, as folk are expected to either install Postgres for the first time, or perform an upgrade which uses a different 9.x folder, and not have to reinstall the same version. This is why I have to shift my data out of the way to /data_old and shift it back. Assuming I can do this without using dump/restore (another question). 5) I typed netstat -an, and found port 5432 was shown as: Local Address: [::]:5432, Foreign Address: [::]:0, State: LISTENING I don't think I chose this port, I think the install defaulted to it. I don't know if this is the right one I should choose. According to Wikipedia, this is the default port for Postgres on TCP/UDP, although my system does not seem to be listening on UDP, but I think just listening on TCP is probably fine. 6) This question about connecting -I think- could be what the problem is. To connect, I start pgAdmin and double-click on Server groups|Servers|myLocalHost (localhost:5432) which has a red 'X' on it (doesn't look good) to which I get a popup 'Error connecting to the server: FATAL: password authentication failed for user "SDB"'. You mention the pg_hba.conf file -but since I am asked to enter a new password during installation, this is reset every time I try to do a reinstall? I am thinking that this is not occurring, and I have entered to many wrong passwords. For security, it would make sense that you can't reset it just by performing a reinstall. Could you elaborate on how to get back in if I have locked myself out please, as I think this could..be it? Many thanks again Stephen -Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: 31 July 2013 14:16 To: Stephen Brearley Cc: pgsql-general@postgresql.org; 'Adrian Klaver' Subject: Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! On Jul 31, 2013, at 14:07, "Stephen Brearley" wrote: > Hi Alban > > Much thanks for getting back to me! > > The event file gets written to (as attached): > C:\Users\SDB\AppData\Local\Temp That's the installation log, not the postgres log. I suspect the postgres log is either in the Windows Event Log (available from the Control Panel, perhaps under Administrative Tools or something similar) or in a file somewhere in the Postgres installation directory, most likely in a directory named log. I don't know how EnterpriseDB sets things up though, certainly not in Windows. Nevertheless, from the installation log it can be seen that initdb was run and that after that the database started up successfully. It also shows that some module named "adminpack" failed to install. I have no idea what that is though, I don't have it on my UNIX system. See: Script output: Installing the adminpack module in the postgres database... Executing 'C:\Users\SDB\AppData\Local\Temp\rad5E7BA.bat'... Couldn't find the output file... Failed to install the 'adminpack' module in the 'postgres' database loadmodules.vbs ran to completion Script stderr: Program ended with an error exit code Error running cscript //NoLogo "C:\Program Files\PostgreSQL\9.2\installer\server\loadmodules.vbs" "postgres" "" "C:\Program Files\PostgreSQL\9.2" "D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data" 5432 : Program ended with an error exit code So, whatever causes your issue is most likely due to something that happened after the install. > I c
Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
Hi Nur Yes, I'm almost sure this is the thing that is causing the main problem, despite other apparent installation bug red herrings. I have had Alban Hertroys pick up on the same thing. But...how do I do this please? Please can you give me some basic instructions. Thanks Stephen From: hidayat...@gmail.com [mailto:hidayat...@gmail.com] Sent: 31 July 2013 14:53 To: Stephen Brearley; pgsql-general-ow...@postgresql.org Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! Hi Stephen, As per my experience, installing postgresql on windows machine automatically create postgres user. When you uninstall it, the postgres user doesn't automatically removed, you must remove it manually. So, when you install postgres for the second time, it will use existing postgres user which already exist therefore uses the password which you have forgotten. Try uninstall postgresql and remove postgres user, or alternatively, reset postgres user password from Computer Management. Regards, Nur Hidayat . Sent from my BlackBerryR powered by Sinyal Kuat INDOSAT _ From: "Stephen Brearley" Sender: pgsql-general-ow...@postgresql.org Date: Wed, 31 Jul 2013 12:28:57 +0100 To: Cc: Subject: Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! Hi Thomas **Thanks for getting back to me** In answer to your points: 1) I could not get Postgresql to run correctly, so I assumed it could be a bug. I checked the documentation for bug reports, and this seemed to suggest that anything that appears to be a bug should be reported. If a decision is made that this is not a bug, it would be helpful if someone sent a short message to say so, and tell me to report my problem elsewhere. Otherwise I just sit there waiting and nothing happens!! 5) There does not appear to be a specific 'Uninstall' option on the Windows Start menu, so I went to Control Panel, Programs and features, PostgreSQL 9.2 Uninstall/Change option. Is this correct? 6) I have not performed an upgrade of Postgres. Inititially, I had no Postgres, then I installed postgresql-9.2.4-1-windows.exe, then I forgot my password as was using Navicat that remembers it automatically and tried to use pgAdmin which doesn't, so I tried to reinstall (then of course remembered my password!). I have not been able to get Postgres to work correctly/connect since. -Thank-you for confirming there are no hidden users -I spent many web searches trying to work out how to remove/reset these (whew)!! No, I did not verify that Postgres was running, but have since checked in the Task Manager and it is running when I fire up pgAdmin. However, I should not get any error message during the install process, and to me this clearly suggests something is wrong, as the log I attached shows. Any ideas what is wrong please? I would not normally do anything in the Registry, as I am aware that is asking for trouble. However, I hadn't noted that I could specify a data location on my original install, so I checked the web and this appeared to be the only way to do this for some programs. I edited PostgreSQL|Installations|postgresql-9.2|Data Directory and changed the default from the C: drive to my folder on the D: drive as D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data. This worked fine until I had the password problems I mentioned above, and tried to do a reinstall, so I don't think this registry edit is causing the problem. I have only recently found the postgresql.conf option on pgAdmin. Where/what is init.db? I don't have any instructions for this file. Not sure what you mean. I too am now puzzled about pg.log. I think I believed this was related to 'hidden' Windows users which you say are not now used, so please disregard this point now. I realise that new versions of Postgres go into a different file system on the C: drive, and that I can't just move the system files across or rename directories. However, in this case I am trying to reinstall exactly the same version, so everything should be the same, so surely it should be okay to move my old data out of the way, remove the folder it was in, perform the reinstall and move the data back? Maybe it isn't. I was not aware of this pg_dump/pg_restore, as these instructions are intended for upgrades, which of course I would not have read, as I am trying to perform a reinstall of the same version. Therefore I will try to perform a dump, then I guess remove all my Postgres folders in D:, then reinstall Postgres and run pg_restore to bring everything back in (though this doesn't explain the errors in the installation log). Regarding the password, I have typed in the same password which I used everytime I tried to install Postgres, so I started to think there was an install problem as listed in the installation log. The in
[GENERAL] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
Alban Hertroys, 31.07.2013 15:16: > I suspect the postgres log is either in the Windows Event Log > (available from the Control Panel, perhaps under Administrative Tools > or something similar) or in a file somewhere in the Postgres > installation directory, most likely in a directory named log. It's in a directory named pg_log in the *data* directory, not the installation directory The relevant line in the installer log is: processed file: D:\_SDB\Database\RDBMS\PostgreSQL\9.2\data\pg_log Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
hidayat...@gmail.com, 31.07.2013 15:52: > As per my experience, installing postgresql on windows machine > automatically create postgres user. When you uninstall it, the > postgres user doesn't automatically removed, you must remove it > manually. Not any more. Since 9.1 (or was it 9.2?) Postgres uses a pre-defined Windows account (the "Network account") for this. -- 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] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
On 01/08/2013 10:27, Stephen Brearley wrote: > 6) This question about connecting -I think- could be what the problem is. To > connect, I start pgAdmin and double-click on Server > groups|Servers|myLocalHost (localhost:5432) which has a red 'X' on it > (doesn't look good) to which I get a popup 'Error connecting to the server: > FATAL: password authentication failed for user "SDB"'. You mention the > pg_hba.conf file -but since I am asked to enter a new password during > installation, this is reset every time I try to do a reinstall? I am > thinking that this is not occurring, and I have entered to many wrong > passwords. For security, it would make sense that you can't reset it just by > performing a reinstall. Could you elaborate on how to get back in if I have > locked myself out please, as I think this could..be it? Hi there, If you're locked out, you don't have to reinstall - you can do the following: (i) Locate pg_hba.conf - this should be in your data directory (not the installation directory). (ii) Edit it, and change the connection method for the relevant user and/or host from "md5" to "trust". - This will let that user connection without a password. (iii) Restart the server so the connection method change takes effect. (iv) Connect, and reset your password (ALTER ROLE). (v) Edit pg_hba.conf once more and change the connection method back to "md5". (vi) Restart the server once more. HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] incremental dumps
On Thu, Aug 1, 2013 at 10:59 AM, wrote: > However, the diff files seem to be considerably larger than one would expect. > One obvious part of the problem is the fact that diff shows old and new text, > so e.g. changing the amount of stock for a product with a 1kB description > would generate at least 2kb of text in the diff file. > > What would be a better approach? I suppose wal archiving or PITR would be better, but assuming you want text files I guess you need to change your database structure to either: 1) include a watermark on data and dump only data since the last dump (to do manually); 2) partition your tables and backup specific tables/partitions depending on the timing. Hope this helps. Luca -- 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] incremental dumps
> suppose wal archiving or PITR would be better +1, never re-invent the wheel, unless you really need to. Bèrto On 1 August 2013 14:14, Luca Ferrari wrote: > On Thu, Aug 1, 2013 at 10:59 AM, wrote: > > > However, the diff files seem to be considerably larger than one would > expect. > > One obvious part of the problem is the fact that diff shows old and new > text, > > so e.g. changing the amount of stock for a product with a 1kB description > > would generate at least 2kb of text in the diff file. > > > > What would be a better approach? > > I suppose wal archiving or PITR would be better, but assuming you want > text files I guess you need to change your database structure to > either: > 1) include a watermark on data and dump only data since the last dump > (to do manually); > 2) partition your tables and backup specific tables/partitions > depending on the timing. > > Hope this helps. > Luca > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [GENERAL] demystifying nested loop vs. merge join query plan choice
@Jeff : Thanks for pointing this out. Turns out that was the case. @Tom: Thank you for the reference to random_page_cost parameters. It would be very useful for us. Would go through the rest of the documentation as well. On Wed, Jul 31, 2013 at 3:55 PM, Tom Lane wrote: > Sandeep Gupta writes: > > details regarding buffer usage: > > [ 100% buffer hit rate ] > > Your database is evidently fully cached in memory. If that's the > operating mode you expect, you need to change the planner's cost > parameters, in particular reduce random_page_cost to equal seq_page_cost. > There is plenty of material about this on the PG wiki or in the > pgsql-performance archives. > > regards, tom lane >
Re: [GENERAL] Why are stored procedures looked on so negatively?
Neil Tiffin wrote: > Some Developer wrote: > >> I've done quite a bit of reading on stored procedures recently and the >> consensus seems to be that you shouldn't use them unless you really must. > > Application architecture is a specific software engineering discipline. These > types of generalizations come from coders who don't really understand > application architecture and/or databases. There are specific reasons to put > code in the database server, application middleware, or the application. To > make this decision, much more must be known that what has been presented in > this > thread. +1 > For example, if you want to maintain data integrity, then you really want to > use > very specific table definitions with foreign keys, defaults, and constraints. > While this is not related to stored procedures, application coders try to shy > away from these (like they do stored procedures) because it makes working with > the database harder. It forces the data to be correct before it comes into > the > database. When foreign keys, defaults, and constraints are not enough to > ensure > data integrity then stored procedures should be used. The question is, how > important is your data and how much time do you want to spend correcting it > after it enters the database? Agreed. > The next reason is performance. I'm going to skip the rest of this well-reasoned and well-written response to give just a couple data points on this. When working as a consultant, one client was doing everything client-side and engaged me to fix some performance problems. In one case a frequently run query was taking two minutes. As a stored procedure the correct results were returned in two seconds. This same client had a report which ran for 72 hours. A stored procedure was able to return the correct data in 2.5 minutes, although it took another 10 minutes for the client side to process it into the output format. Stored procedures are not a panacea, however. Writing in a declarative format is, in my experience, much more important. I saw one case where a SQL procedure written in imperative form, navigating through linkages a row at a time, was on pace to complete in over a year. Rewritten in declarative form it ran in a few minutes. As a side benefit, the declarative form is usually 10% to 20% the number of lines of code, and less buggy. For retrieval of complex data sets, the big thing is to learn to write SQL which specifies *what you want* rather then trying to specify *how to get it*. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 are stored procedures looked on so negatively?
On 02/08/13 08:24, Kevin Grittner wrote: [...] When working as a consultant, one client was doing everything client-side and engaged me to fix some performance problems. In one case a frequently run query was taking two minutes. As a stored procedure the correct results were returned in two seconds. This same client had a report which ran for 72 hours. A stored procedure was able to return the correct data in 2.5 minutes, although it took another 10 minutes for the client side to process it into the output format. Stored procedures are not a panacea, however. Writing in a declarative format is, in my experience, much more important. I saw one case where a SQL procedure written in imperative form, navigating through linkages a row at a time, was on pace to complete in over a year. Rewritten in declarative form it ran in a few minutes. As a side benefit, the declarative form is usually 10% to 20% the number of lines of code, and less buggy. For retrieval of complex data sets, the big thing is to learn to write SQL which specifies *what you want* rather then trying to specify *how to get it*. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Trust the Planner, Luke! (Apologies to Star Wars) Very informative, learnt more in the above, and omitted text, than I have for a long while - certainly clarified my ideas on the subject. Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Add a NOT NULL column with default only during add
When I want to add a new column with a NOT NULL constraint, I need to specify a DEFAULT to avoid violations. However, I don't always want to keep that DEFAULT; going forward after the initial add, I want an error to occur if there are inserts where this data is missing. So I have to DROP DEFAULT on the column. See this SQL Fiddle for a demonstration: http://sqlfiddle.com/#!12/58750/1/0. I could leave off the NOT NULL constraint, fill the new column with an UPDATE, and then add the NOT NULL constraint afterwards, but that, in my opinion, seems to be a somewhat messier alternative. By comparison, if I change data types, I can take advantage of the very useful USING clause to specify how to calculate the new value. As near as I can tell, there is no similar functionality for ADD COLUMN to specify a value (or means of calculating a value) only during the execution of the ALTER. I can understand why that might be the case. Without USING, changing the data type would force the creation of a new column instead in many cases, which is a much bigger hardship and makes the data type changing command far less useful. Am I missing something, or are the ways I mentioned the only ways to accomplish this with ADD COLUMN? It's true that neither possibility is particularly difficult to implement, but it doesn't seem like I should have to create a constraint I don't want or leave off a constraint I do want to add the column. I suppose in some cases, the fact that "fully creating" the column is non-atomic may be a problem. If I'm correct that this feature is not currently present, would adding it be a reasonable feature request? How would I go about making a feature request? (My apologies if there is a how-to on feature requests somewhere; my searching didn't turn it up.) Thank you.
Re: [GENERAL] Add a NOT NULL column with default only during add
Notice : http://www.postgresql.org/docs/9.3/static/sql-altertable.html After you add a column to your table, you can latter *alter* this column to add, change, or remove the default expression. There's no need add temporary columns to manage this kind of change. In fact, all of the DDL that you've described can be achieved in one SQL command. On Thu, Aug 1, 2013 at 3:49 PM, BladeOfLight16 wrote: > When I want to add a new column with a NOT NULL constraint, I need to > specify a DEFAULT to avoid violations. However, I don't always want to keep > that DEFAULT; going forward after the initial add, I want an error to occur > if there are inserts where this data is missing. So I have to DROP DEFAULT > on the column. See this SQL Fiddle for a demonstration: > http://sqlfiddle.com/#!12/58750/1/0. I could leave off the NOT NULL > constraint, fill the new column with an UPDATE, and then add the NOT NULL > constraint afterwards, but that, in my opinion, seems to be a somewhat > messier alternative. > > By comparison, if I change data types, I can take advantage of the very > useful USING clause to specify how to calculate the new value. As near as I > can tell, there is no similar functionality for ADD COLUMN to specify a > value (or means of calculating a value) only during the execution of the > ALTER. I can understand why that might be the case. Without USING, changing > the data type would force the creation of a new column instead in many > cases, which is a much bigger hardship and makes the data type changing > command far less useful. > > Am I missing something, or are the ways I mentioned the only ways to > accomplish this with ADD COLUMN? It's true that neither possibility is > particularly difficult to implement, but it doesn't seem like I should have > to create a constraint I don't want or leave off a constraint I do want to > add the column. I suppose in some cases, the fact that "fully creating" the > column is non-atomic may be a problem. If I'm correct that this feature is > not currently present, would adding it be a reasonable feature request? How > would I go about making a feature request? (My apologies if there is a > how-to on feature requests somewhere; my searching didn't turn it up.) > > Thank you. > -- Regards, Richard Broersma Jr.
Re: [GENERAL] Add a NOT NULL column with default only during add
On Thu, Aug 1, 2013 at 7:10 PM, Richard Broersma wrote: > Notice : > http://www.postgresql.org/docs/9.3/static/sql-altertable.html > After you add a column to your table, you can latter *alter* this column > to add, change, or remove the default expression. There's no need add > temporary columns to manage this kind of change. In fact, all of the DDL > that you've described can be achieved in one SQL command. > I think there has been a misunderstanding. I was describing the use of "add column with default" and "drop default" commands; please see my SQL Fiddle. It's only 2 ALTER commands; it doesn't use any temporary columns. It does use a temporary constraint, but not a temporary column. I'm not clear how you could do this in a single command. Are you suggesting I could do something like this? ALTER TABLE x ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo', ALTER COLUMN data2 DROP DEFAULT; At least in the 9.2.4 SQL Fiddle uses, that fails with this error: ERROR: column "data2" of relation "x" does not exist. Has something changed in 9.3, or am I misreading you? A sample command of what you're suggesting might be helpful. (Doesn't have to be perfect syntax or anything; just to give me the gist.) Thank you.
Re: [GENERAL] demystifying nested loop vs. merge join query plan choice
On Thu, Aug 1, 2013 at 10:25 AM, Sandeep Gupta wrote: > @Jeff : Thanks for pointing this out. Turns out that was the case. > > @Tom: Thank you for the reference to random_page_cost parameters. It would > be very useful for us. Would go through the rest of the documentation as > well. > I can't say what Jeff mentioned; maybe he didn't reply to the user list. Anyhow, sorry if this is repeating information. I cannot help but point something glaring out in the EXPLAIN, though: database 1: Index Only Scan using tc_did_idx on tc (cost=0.00..1298125.32 rows=49987616 width=4) database 2: Index Only Scan using tc_did_idx on tc (cost=0.00..70.44 rows=3 width=4) Maybe I just don't know how to read EXPLAIN plans, but it would appear that the estimated rows from the index only scan in the two plans is different by a factor of about 16.7 million. database 1 also processes about 7.7 million rows before the aggregate, where database 2 only processes about 1.3 million. For some reason, it appears that database 2 is able to eliminate far more rows more quickly, resulting in a faster query. Have both databases had VACUUM ANALYZE run on them? Are the statistics collection settings the same?
Re: [GENERAL] Add a NOT NULL column with default only during add
On 08/01/2013 04:25 PM, BladeOfLight16 wrote: On Thu, Aug 1, 2013 at 7:10 PM, Richard Broersma mailto:richard.broer...@gmail.com>> wrote: Notice : http://www.postgresql.org/docs/9.3/static/sql-altertable.html After you add a column to your table, you can latter *alter* this column to add, change, or remove the default expression. There's no need add temporary columns to manage this kind of change. In fact, all of the DDL that you've described can be achieved in one SQL command. I think there has been a misunderstanding. I was describing the use of "add column with default" and "drop default" commands; please see my SQL Fiddle. It's only 2 ALTER commands; it doesn't use any temporary columns. It does use a temporary constraint, but not a temporary column. I'm not clear how you could do this in a single command. Are you suggesting I could do something like this? ALTER TABLE x ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo', ALTER COLUMN data2 DROP DEFAULT; At least in the 9.2.4 SQL Fiddle uses, that fails with this error: ERROR: column "data2" of relation "x" does not exist. Has something changed in 9.3, or am I misreading you? A sample command of what you're suggesting might be helpful. (Doesn't have to be perfect syntax or anything; just to give me the gist.) It fails because ALTER TABLE x ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo', end in , instead of ; You have to add the column before you can alter it. Thank you. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add a NOT NULL column with default only during add
On Thu, Aug 1, 2013 at 7:53 PM, Adrian Klaver wrote: > It fails because > > ALTER TABLE x > ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo', > > end in , instead of ; > > You have to add the column before you can alter it. > =/ That's the way I have it in the SQL Fiddle sample I provided. I was asking what Mr. Broersma was suggesting. I appreciate the effort by both of you, but it seems my points aren't getting across. Is there something more I can do to clarify?
Re: [GENERAL] Add a NOT NULL column with default only during add
On 08/01/2013 04:59 PM, BladeOfLight16 wrote: On Thu, Aug 1, 2013 at 7:53 PM, Adrian Klaver mailto:adrian.kla...@gmail.com>> wrote: It fails because ALTER TABLE x ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo', end in , instead of ; You have to add the column before you can alter it. =/ That's the way I have it in the SQL Fiddle sample I provided. I was asking what Mr. Broersma was suggesting. I appreciate the effort by both of you, but it seems my points aren't getting across. Is there something more I can do to clarify? What you want is a default that only works during ALTER ADD COLUMN. At that point though, there is no data added and DEFAULT only works with INSERTS. Your example of USING with ALTER data_type works because there actually may be rows already existing and you are not creating a column. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Snapshot backups
Thank you Tom! This is what I was after! So, to get this straight in my head. - pg_start_backup forces a checkpoint and writes the information from this checkpoint to the backup_label file - - pg_stop_backup removes the backup_label file - - - Database starts and determines where to start WAL replay from the backup_label NOT from pg_control (as usual) Cheers, James James Sewell PostgreSQL Team Lead / Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png] Level 2, 50 Queen St, Melbourne, VIC, 3000 P: 03 8370 8000 F: 03 8370 8099 W: www.lisasoft.com On Wed, Jul 31, 2013 at 10:24 PM, Tom Lane wrote: > Alban Hertroys writes: > > That begs the question what happens in case of a crash or (worse) a > partial crash when multiple file systems are involved. > > As long as the OS+hardware honors the contract of fsync(), everything's > fine. If the storage system loses data that it claims to have fsync'd to > stable storage, there's not much we can do about that, except recommend > that you have a backup plan. > > In practice, the more complicated your storage infrastructure is, the more > likely it is to have bugs ... > > regards, tom lane > -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence. <>
Re: [GENERAL] Why are stored procedures looked on so negatively?
Here's my $0.02 Stored procedures have a bunch of problems historically. Part of this is because the interface traditionally is pretty spartan, and partly because some people take them too far. The first issue is that if you have a stored procedure which takes 2 arguments and you need to extend it to three, then you have to change every call in the calling application. This can create a maintenance problem. Variadic functions help somewhat but there are limits to what a variadic function can do here. The programs and frameworks I write rely very heavily on argument name and data type detection to rewrite calls dynamically, but that has tradeoffs as well. In general though I think that those tradeoffs are worth it and stored procedures are very, very useful. The second issue is simply, just because something can go in the database doesn't mean it should. In general people start doing things like sending email from the backend and this usually creates more problems than it solves. The best approach is to see stored procedures as a way to encapsulate the data behind a service-oriented API (like the NoSQL folks advocate ;-) ). Hope this helps. -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
