Re: [HACKERS] pg_hba.conf alternative
how? is there some kernel patch to completely to enable you to deny access to root? Tino Wildenhain pointed out SELinux has a feature like that. Rick Gigger wrote: But why do they need access to the files in the file system? Why not put them on the local box but don't give them permissions to edit the pg_hba file? They should still be able to connect. On Feb 9, 2006, at 5:56 PM, Q Beukes wrote: I did consider that, but the software we use (which again uses postgresql) atm only supports local connection to the database. I am the database admin, the other admins just manage stuff like user accounts, checking logs, etc... Unfortunately there is no other way to set it up, and like I mentioned government security is not required. I did however statically code the pg_hba.conf file into pg binaries. The only way I found to access the db now would be to replace the binary and possibly sniffing traffic. But we're not worried about that. They not really criminally minded people. thx for everyones help anyway ; korry wrote: Why would you not simply set this up on a seperate machine to which only the trusted admins had access? Most data centers I am familiar with use single purpose machines anyway. If someone is trusted as root on your box they can screw you no matter what you do. Pretending otherwise is just folly. Agreed - that would be a much better (easier and more secure) solution where practical. -- Korry ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_hba.conf alternative
Q Beukes schrieb: how? is there some kernel patch to completely to enable you to deny access to root? Tino Wildenhain pointed out SELinux has a feature like that. I still dont get your problem (apart from that you can always google for SELinux) Why arent the other admins not trustworthy? And why do you have many of them? If they only check logs and create users, why do they have to be admins? They could use carefully configured sudo as well to fullfill their tasks w/o full access to the system. I'd say, grep your problem at the root (literally spoken) Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_hba.conf alternative
how? is there some kernel patch to completely to enable you to deny access to root? Tino Wildenhain pointed out SELinux has a feature like that. I still dont get your problem (apart from that you can always google for SELinux) Why arent the other admins not trustworthy? And why do you have many of them? If they only check logs and create users, why do they have to be admins? They could use carefully configured sudo as well to fullfill their tasks w/o full access to the system. I'd say, grep your problem at the root (literally spoken) Yes. Exactly. I guess I misunderstood the situation. Admin is vague word. It could mean db admins, it could mean a system administrator for that computer etc. I apologize if that was specified earlier in the discussion. I just assumed that if you didn't want them to be able to edit the conf file that they wouldn't have root because well... that just seems obvious. I realize though that you don't need real security but rather a small barrier to give the management the warm fuzzies. I'm sure that you have your reasons but if you could make them non- root users and give them privileges to do what they need to do with sudo or something but not give them perms on the hba file then that would seem to be a better solution all around than compiling your own custom postgres. Just a suggestion. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed from CVS-Unknown failure to Make failure
And another failure on Snake /D -Original Message- From: PG Build Farm [mailto:[EMAIL PROTECTED] Sent: 13 February 2006 02:10 To: [EMAIL PROTECTED] Subject: PGBuildfarm member snake Branch HEAD Status changed from CVS-Unknown failure to Make failure The PGBuildfarm member snake had the following event on branch HEAD: Status changed from CVS-Unknown failure to Make failure The snapshot timestamp for the build that triggered this notification is: 2006-02-13 02:00:00 The specs of this machine are: OS: Windows / Server 2003 SP1 Arch: i686 Comp: gcc / 3.4.2 For more information, see http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snakebr=HEAD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] psql readline win32
Would the easiest solution be to make a patch to readline for Win32, and only allow Win32 to link to readline if that patch is in readline, and spit out a compile error if readline doesn't have that patch. What would we patch it with? I don't think anybody has found a problem there, this is a separate file that you ship along with it. As far as the license, psql spits out a copyright notice as it starts. It would be a shame to have to mention GPL in there. Even that may not be enough. This is the GPL we're talking about. Can we get any companies to fund a port of libedit to Win32? That would be nice. Takers? What does readline have that Win32 native editing does not? tab completion, for one. Some editing keys, IIRC. I thought history, but it does seem we have history workign on native :) //Magnus ---(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
[HACKERS] what's stored in pg_tblspc
I am confused, I thought that there were only supposed to be links to the actual data in pg_tblspc ? I have a db defined in a tablspace, but in pg_tblspc there is 1.2G of data corrresponding to it? Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Added to TODO: o Allow pg_hba.conf to specify host names along with IP addresses Host name lookup could occur when the postmaster reads the pg_hba.conf file, or when the backend starts. Another solution would be to reverse lookup the connection IP and check that hostname against the host names in pg_hba.conf. We could also then check that the host name maps to the IP address. I'm not so sure you need to be paranoid about it. The scenario is, at startup or HUP, names are looked up and stored as IP addresses. Then hba works as it is supposed too. spoofing is not really a problem, IMHO, because there should be a fire wall between PostgreSQL (most services really) and the raw internet, *and* the admin MUST have control over the authenticity of the name resolver. If someone is in the position to spoof name resolution, they are probably also in a position to spoof IP addresses. While I do see a need for this, but not in a sense that any old name would be used. I see it more like a data center wide hosts file use to dedicate various IP addresses to various services, i.e. freedb, streetmap, session, web0, web1, .., webn etc. What also may be handy in this scenario is that the names must fall within a range of acceptable addresses. Range: 192.168.245.1 ... 192.168.254.254 joey (192.168.254.55) -- OK joey (10.1.1.0) -- Not OK. This would be useful to declare a range of addresses as having some level of trust, and specific names within that range as having more (or less). In this scenario, think of a VPN, joey may be a laptop, and while he is on the VPN he is trusted, and when he is not on the vpn he is not trusted. This is especially important with regards to cyber security. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed
Hello, That's caused by small error in recent implementation of dealing with multi-line queries. I already have sent the patch to pgsql-patches Regards, Sergey On Mon, 13 Feb 2006, Dave Page wrote: And another failure on Snake /D -Original Message- From: PG Build Farm [mailto:[EMAIL PROTECTED] Sent: 13 February 2006 02:10 To: [EMAIL PROTECTED] Subject: PGBuildfarm member snake Branch HEAD Status changed from CVS-Unknown failure to Make failure The PGBuildfarm member snake had the following event on branch HEAD: Status changed from CVS-Unknown failure to Make failure The snapshot timestamp for the build that triggered this notification is: 2006-02-13 02:00:00 The specs of this machine are: OS: Windows / Server 2003 SP1 Arch: i686 Comp: gcc / 3.4.2 For more information, see http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snakebr=HEAD ---(end of broadcast)--- TIP 6: explain analyze is your friend * Sergey E. Koposov Max Planck Institute for Astronomy Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect, union
I just wanted to check if this has been fixed in any recent v8.1.x release, since I'm using v8.1.0 now. Backtrace: Program received signal SIGSEGV, Segmentation fault. 0x08152448 in qual_is_pushdown_safe () (gdb) bt #0 0x08152448 in qual_is_pushdown_safe () #1 0x08151e47 in set_subquery_pathlist () #2 0x08151a3c in set_base_rel_pathlists () #3 0x08151960 in make_one_rel () #4 0x0815dcaf in query_planner () #5 0x0815ea19 in grouping_planner () #6 0x0815e2e4 in subquery_planner () #7 0x0815dfaa in planner () #8 0x08197b7c in pg_plan_query () #9 0x08197c39 in pg_plan_queries () #10 0x08197e3d in exec_simple_query () #11 0x0819a6fe in PostgresMain () #12 0x08176356 in BackendRun () #13 0x08175c77 in BackendStartup () #14 0x08173ee2 in ServerLoop () #15 0x08173723 in PostmasterMain () #16 0x08139f90 in main () #17 0x400dc14f in __libc_start_main () from /lib/libc.so.6 The crashing query is below, if I remove the not is null test it doesn't crash. How to reproduce: create table snicker_whatever( id SERIAL primary key ); create table snicker ( idSERIAL primary key, name_singular text not null unique, name_plural text not null unique ); create table snicker_group ( id SERIAL primary key, title varchar(64) not null, snicker_id integer not null references snicker_whatever(id) ); create table snicker_group_mapping ( id SERIAL primary key, snicker_group_id integer not null references snicker_group(id), snicker_id integer references snicker(id) ); SELECT DISTINCT * FROM ( SELECT vtgm.snicker_id FROM snicker_group_mapping vtgm WHERE exists ( SELECT * FROM snicker_group vtg WHERE vtgm.snicker_group_id = vtg.id AND lower(vtg.title) ~* 'test' ) UNION SELECT snicker.id FROM snicker WHERE lower(snicker.name_singular) ~* 'test' OR lower(snicker.name_plural) ~* 'test' ) AS vt_id WHERE vt_id is not null; Regards, Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] what's stored in pg_tblspc
Dave Cramer [EMAIL PROTECTED] writes: I am confused, I thought that there were only supposed to be links to the actual data in pg_tblspc ? I have a db defined in a tablspace, but in pg_tblspc there is 1.2G of data corrresponding to it? Are you on a system that has symlinks? Are you sure that whatever tool you're using to count the space doesn't traverse symlinks? IIRC, there is a corner case during replay-from-WAL where we'll create a plain directory under pg_tblspc to substitute for a symlink (if the symlink isn't there and we don't have the information to recreate it). I don't believe it's easy to get into that state though. regards, tom lane ---(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
Re: [HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect, union
Magnus Naeslund(f) [EMAIL PROTECTED] writes: SELECT DISTINCT * FROM ( SELECT vtgm.snicker_id FROM snicker_group_mapping vtgm WHERE exists ( SELECT * FROM snicker_group vtg WHERE vtgm.snicker_group_id = vtg.id AND lower(vtg.title) ~* 'test' ) UNION SELECT snicker.id FROM snicker WHERE lower(snicker.name_singular) ~* 'test' OR lower(snicker.name_plural) ~* 'test' ) AS vt_id WHERE vt_id is not null; While the crash is certainly a bug, the answer is going to be don't do that. Testing a whole record for null-ness is not meaningful. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Mark Woodward wrote: Added to TODO: o Allow pg_hba.conf to specify host names along with IP addresses Host name lookup could occur when the postmaster reads the pg_hba.conf file, or when the backend starts. Another solution would be to reverse lookup the connection IP and check that hostname against the host names in pg_hba.conf. We could also then check that the host name maps to the IP address. I'm not so sure you need to be paranoid about it. The scenario is, at startup or HUP, names are looked up and stored as IP addresses. Then hba works as it is supposed too. If you do it like that you destroy the only real use case I can see for this that has much value, namely to handle cases where the address can change dynamically. spoofing is not really a problem, IMHO, because there should be a fire wall between PostgreSQL (most services really) and the raw internet, *and* the admin MUST have control over the authenticity of the name resolver. If someone is in the position to spoof name resolution, they are probably also in a position to spoof IP addresses. While I do see a need for this, but not in a sense that any old name would be used. I see it more like a data center wide hosts file use to dedicate various IP addresses to various services, i.e. freedb, streetmap, session, web0, web1, .., webn etc. What also may be handy in this scenario is that the names must fall within a range of acceptable addresses. Range: 192.168.245.1 ... 192.168.254.254 joey (192.168.254.55) -- OK joey (10.1.1.0) -- Not OK. This would be useful to declare a range of addresses as having some level of trust, and specific names within that range as having more (or less). In this scenario, think of a VPN, joey may be a laptop, and while he is on the VPN he is trusted, and when he is not on the vpn he is not trusted. This is especially important with regards to cyber security. We have address ranges now; are you proposing to have those IN ADDITION to hostname parameters (as opposed to being an alternative)? We can over-egg this pudding massively. I suggest we start with a simple implementation and see what needs it leaves unfilled. I would vote for allowing a hostname (or list of hostnames?) to replace the address/mask params, and that at connect time we do a forward lookup trying for a match with the connecting address. If we get a match then that's the hba line that applies. Frankly, any auth mechanism based on the name or address of the client is insecure. If you have people connecting across possibly insecure networks you should use SSL with client certificates signed by your own CA, or a similar approach. cheers andrew ---(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
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Mark Woodward wrote: Added to TODO: o Allow pg_hba.conf to specify host names along with IP addresses Host name lookup could occur when the postmaster reads the pg_hba.conf file, or when the backend starts. Another solution would be to reverse lookup the connection IP and check that hostname against the host names in pg_hba.conf. We could also then check that the host name maps to the IP address. I'm not so sure you need to be paranoid about it. The scenario is, at startup or HUP, names are looked up and stored as IP addresses. Then hba works as it is supposed too. If you do it like that you destroy the only real use case I can see for this that has much value, namely to handle cases where the address can change dynamically. How dynamically are you talking about? If you are using a DNS server, what is your TTL on the records? A simple -HUP once every half hour is more than sufficient. If you are using ssh to update the hosts file, adding a simple -HUP tp the script is not a big deal. spoofing is not really a problem, IMHO, because there should be a fire wall between PostgreSQL (most services really) and the raw internet, *and* the admin MUST have control over the authenticity of the name resolver. If someone is in the position to spoof name resolution, they are probably also in a position to spoof IP addresses. While I do see a need for this, but not in a sense that any old name would be used. I see it more like a data center wide hosts file use to dedicate various IP addresses to various services, i.e. freedb, streetmap, session, web0, web1, .., webn etc. What also may be handy in this scenario is that the names must fall within a range of acceptable addresses. Range: 192.168.245.1 ... 192.168.254.254 joey (192.168.254.55) -- OK joey (10.1.1.0) -- Not OK. This would be useful to declare a range of addresses as having some level of trust, and specific names within that range as having more (or less). In this scenario, think of a VPN, joey may be a laptop, and while he is on the VPN he is trusted, and when he is not on the vpn he is not trusted. This is especially important with regards to cyber security. We have address ranges now; are you proposing to have those IN ADDITION to hostname parameters (as opposed to being an alternative)? We can over-egg this pudding massively. I suggest we start with a simple implementation and see what needs it leaves unfilled. I would vote for allowing a hostname (or list of hostnames?) to replace the address/mask params, and that at connect time we do a forward lookup trying for a match with the connecting address. If we get a match then that's the hba line that applies. Frankly, any auth mechanism based on the name or address of the client is insecure. If you have people connecting across possibly insecure networks you should use SSL with client certificates signed by your own CA, or a similar approach. It isn't so much an auth issue because I'm not assuming a hacker so much as the stream of data that across the network. Within the firewall == safe, outside of the firewall can be snooped. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Mark Woodward wrote: Mark Woodward wrote: Added to TODO: o Allow pg_hba.conf to specify host names along with IP addresses Host name lookup could occur when the postmaster reads the pg_hba.conf file, or when the backend starts. Another solution would be to reverse lookup the connection IP and check that hostname against the host names in pg_hba.conf. We could also then check that the host name maps to the IP address. I'm not so sure you need to be paranoid about it. The scenario is, at startup or HUP, names are looked up and stored as IP addresses. Then hba works as it is supposed too. If you do it like that you destroy the only real use case I can see for this that has much value, namely to handle cases where the address can change dynamically. How dynamically are you talking about? If you are using a DNS server, what is your TTL on the records? A simple -HUP once every half hour is more than sufficient. If you are using ssh to update the hosts file, adding a simple -HUP tp the script is not a big deal. If I am a road warrior I want to be able to connect, run my dynamic dns client, and go. HUPing the postmaster every 30 minutes sounds horrible, and won't work for what strikes me as the scenario that needs this most. And we surely aren't going to build TTL logic into postgres. I repeat - let's do this the simple way. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect,
Tom Lane wrote: Magnus Naeslund(f) [EMAIL PROTECTED] writes: SELECT DISTINCT * FROM ( SELECT vtgm.snicker_id FROM snicker_group_mapping vtgm WHERE exists ( SELECT * FROM snicker_group vtg WHERE vtgm.snicker_group_id = vtg.id AND lower(vtg.title) ~* 'test' ) UNION SELECT snicker.id FROM snicker WHERE lower(snicker.name_singular) ~* 'test' OR lower(snicker.name_plural) ~* 'test' ) AS vt_id WHERE vt_id is not null; While the crash is certainly a bug, the answer is going to be don't do that. Testing a whole record for null-ness is not meaningful. Yep, my workaround (or bugfix) was to push that null test infront of the exists. Also I might not need the surrounding distinct either, doesn't union make the result distinct? So if I would like to do the test after the union, I should add AS xxx on both union queries and then vt_id.xxx is not null, right ? Regards, Magnus ---(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
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
On Mon, Feb 13, 2006 at 10:00:34AM -0500, Andrew Dunstan wrote: Mark Woodward wrote: I'm not so sure you need to be paranoid about it. The scenario is, at startup or HUP, names are looked up and stored as IP addresses. Then hba works as it is supposed too. If you do it like that you destroy the only real use case I can see for this that has much value, namely to handle cases where the address can change dynamically. *nod* Addresses change, and for a stable PostgreSQL server, this would hopefully mean that PostgreSQL has uptime across these changes. :-) We have address ranges now; are you proposing to have those IN ADDITION to hostname parameters (as opposed to being an alternative)? I like in addition. For example, at work, saying a.blah.com and 47.* would give me an inch more of comfort, as the organization is large, and there are numerous channels to having the name changed - but at least if I know that the name is within 47.*, I know that it isn't somebody in another partner company connecting directly from their network. Not bullet proof, but slightly more difficult to manipulate. We can over-egg this pudding massively. I suggest we start with a simple implementation and see what needs it leaves unfilled. I would vote for allowing a hostname (or list of hostnames?) to replace the address/mask params, and that at connect time we do a forward lookup trying for a match with the connecting address. If we get a match then that's the hba line that applies. Yes. Frankly, any auth mechanism based on the name or address of the client is insecure. If you have people connecting across possibly insecure networks you should use SSL with client certificates signed by your own CA, or a similar approach. Yes. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
[EMAIL PROTECTED] writes: On Mon, Feb 13, 2006 at 10:00:34AM -0500, Andrew Dunstan wrote: We can over-egg this pudding massively. I suggest we start with a simple implementation and see what needs it leaves unfilled. I would vote for allowing a hostname (or list of hostnames?) to replace the address/mask params, and that at connect time we do a forward lookup trying for a match with the connecting address. If we get a match then that's the hba line that applies. Yes. The original proposal to change this required little more than removing the AI_NUMERICHOST flag restricting pg_getaddrinfo_all's lookup. I thought all along that anything more than that was massive overdesign... regards, tom lane ---(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
Re: [HACKERS] what's stored in pg_tblspc
Tom, Thanks, this was driver error Dave On 13-Feb-06, at 9:26 AM, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: I am confused, I thought that there were only supposed to be links to the actual data in pg_tblspc ? I have a db defined in a tablspace, but in pg_tblspc there is 1.2G of data corrresponding to it? Are you on a system that has symlinks? Are you sure that whatever tool you're using to count the space doesn't traverse symlinks? IIRC, there is a corner case during replay-from-WAL where we'll create a plain directory under pg_tblspc to substitute for a symlink (if the symlink isn't there and we don't have the information to recreate it). I don't believe it's easy to get into that state though. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Backslashes in string literals
This patch doesn't leave the standard_conforming_strings entry in guc.c with the GUC_REPORT flag, which it needs for psql to work right. Should I submit one last patch with this fix and the proper expected regression file? If so, where should I send it? (The hackers list won't take a file as big as that patch.) -Kevin On Sun, Feb 12, 2006 at 3:17 pm, in message [EMAIL PROTECTED], Bruce Momjian pgman@candle.pha.pa.us wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi- bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect, union
Magnus Naeslund(f) [EMAIL PROTECTED] writes:
I just wanted to check if this has been fixed in any recent v8.1.x
release, since I'm using v8.1.0 now.
Here's the fix if you need it.
regards, tom lane
Index: allpaths.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.137.2.1
diff -c -r1.137.2.1 allpaths.c
*** allpaths.c 22 Nov 2005 18:23:10 - 1.137.2.1
--- allpaths.c 13 Feb 2006 16:07:30 -
***
*** 793,803
* it will work correctly: sublinks will already have been transformed into
* subplans in the qual, but not in the subquery).
*
! * 2. The qual must not refer to any subquery output columns that were
* found to have inconsistent types across a set operation tree by
* subquery_is_pushdown_safe().
*
! * 3. If the subquery uses DISTINCT ON, we must not push down any quals that
* refer to non-DISTINCT output columns, because that could change the set
* of rows returned. This condition is vacuous for DISTINCT, because then
* there are no non-DISTINCT output columns, but unfortunately it's fairly
--- 793,806
* it will work correctly: sublinks will already have been transformed into
* subplans in the qual, but not in the subquery).
*
! * 2. The qual must not refer to the whole-row output of the subquery
! * (since there is no easy way to name that within the subquery itself).
! *
! * 3. The qual must not refer to any subquery output columns that were
* found to have inconsistent types across a set operation tree by
* subquery_is_pushdown_safe().
*
! * 4. If the subquery uses DISTINCT ON, we must not push down any quals that
* refer to non-DISTINCT output columns, because that could change the set
* of rows returned. This condition is vacuous for DISTINCT, because then
* there are no non-DISTINCT output columns, but unfortunately it's fairly
***
*** 805,811
* parsetree representation. It's cheaper to just make sure all the Vars
* in the qual refer to DISTINCT columns.
*
! * 4. We must not push down any quals that refer to subselect outputs that
* return sets, else we'd introduce functions-returning-sets into the
* subquery's WHERE/HAVING quals.
*/
--- 808,814
* parsetree representation. It's cheaper to just make sure all the Vars
* in the qual refer to DISTINCT columns.
*
! * 5. We must not push down any quals that refer to subselect outputs that
* return sets, else we'd introduce functions-returning-sets into the
* subquery's WHERE/HAVING quals.
*/
***
*** 834,839
--- 837,849
Assert(var-varno == rti);
+ /* Check point 2 */
+ if (var-varattno == 0)
+ {
+ safe = false;
+ break;
+ }
+
/*
* We use a bitmapset to avoid testing the same attno more than
once.
* (NB: this only works because subquery outputs can't have
negative
***
*** 843,849
continue;
tested = bms_add_member(tested, var-varattno);
! /* Check point 2 */
if (differentTypes[var-varattno])
{
safe = false;
--- 853,859
continue;
tested = bms_add_member(tested, var-varattno);
! /* Check point 3 */
if (differentTypes[var-varattno])
{
safe = false;
***
*** 855,861
Assert(tle != NULL);
Assert(!tle-resjunk);
! /* If subquery uses DISTINCT or DISTINCT ON, check point 3 */
if (subquery-distinctClause != NIL
!targetIsInSortList(tle, subquery-distinctClause))
{
--- 865,871
Assert(tle != NULL);
Assert(!tle-resjunk);
! /* If subquery uses DISTINCT or DISTINCT ON, check point 4 */
if (subquery-distinctClause != NIL
!targetIsInSortList(tle, subquery-distinctClause))
{
***
*** 864,870
break;
}
! /* Refuse functions returning sets (point 4) */
if (expression_returns_set((Node *) tle-expr))
{
safe = false;
--- 874,880
break;
}
! /* Refuse functions returning sets (point 5) */
if (expression_returns_set((Node *) tle-expr))
{
safe = false;
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
If I am a road warrior I want to be able to connect, run my dynamic dns client, and go. HUPing the postmaster every 30 minutes sounds horrible, and won't work for what strikes me as the scenario that needs this most. And we surely aren't going to build TTL logic into postgres. I repeat - let's do this the simple way. While I would certainly agree with you on this one in most cases, DNS is a sticky and anoying system. Simple solutions typically fail to accomplish anything. Ask anyone who has implemented DNS based load balancing. And then, don't trust Windows to act accordingly to TTL values in host records. Maybe I'm not sure what you envision, but there are two options, a host file on the postgresql server, or a DNS server the that postgresql server interacts with. Your dynamic dns system may push a DNS entry up to some shared DNS server, but you still need to mind the whole TTL issue. I think what bothers me is that DNS is intended to be a directory for clients to implement an outward connection by finding an IP address that is routable. In your scenario of working as a road warrior, you are almost certainly not going to be able to have a workable DNS host name unless you have a raw internet IP address. More than likely you will have an IP address (known to your laptop) as a 192 or 10 address. If you set your address in some dynamic DNS system, your reported originating IP address (to PostgreSQL) will most likely be wrong. It will be the public IP address of your router that PostgreSQL will see. The more I think about it the uglier it is, I would say an SSH tunnel would be more secure and less problematic. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] User Defined Types in Java
Thomas Hallgren [EMAIL PROTECTED] writes: Ok, so there are two 'optional' arguments. Following my suggestion, the input and receive function would always take 3 arguments. Then, it's up to the function as such if it makes use of them or not. Do you see any problem with that? (1) backwards compatibility (2) inability to ever add a fourth optional argument without creating a flag day for everyone I'm all for cleaning up the handling of shell types (and in fact have had that on my personal TODO list for ages). But I see zero if not negative usefulness in these ideas about changing CREATE TYPE. The certain outcome of that is to import all the complications of CREATE FUNCTION into CREATE TYPE, and for what gain? So which is it? CREATE TYPE complex; CREATE TYPE complex AS SHELL; DECLARE TYPE complex; I'd go with the first. regards, tom lane ---(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
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Mark Woodward wrote: If I am a road warrior I want to be able to connect, run my dynamic dns client, and go. In your scenario of working as a road warrior, you are almost certainly not going to be able to have a workable DNS host name unless you have a raw internet IP address. More than likely you will have an IP address (known to your laptop) as a 192 or 10 address. Nonsense. There is a dynamic DNS client that is quite smart enough to find out and use the gateway address. See: http://ddclient.sourceforge.net/ I'm sure there are others, including some for Windows. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed
Just fixed in CVS. Thanks. --- Dave Page wrote: And another failure on Snake /D -Original Message- From: PG Build Farm [mailto:[EMAIL PROTECTED] Sent: 13 February 2006 02:10 To: [EMAIL PROTECTED] Subject: PGBuildfarm member snake Branch HEAD Status changed from CVS-Unknown failure to Make failure The PGBuildfarm member snake had the following event on branch HEAD: Status changed from CVS-Unknown failure to Make failure The snapshot timestamp for the build that triggered this notification is: 2006-02-13 02:00:00 The specs of this machine are: OS: Windows / Server 2003 SP1 Arch: i686 Comp: gcc / 3.4.2 For more information, see http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snakebr=HEAD ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql readline win32
Magnus Hagander wrote: Would the easiest solution be to make a patch to readline for Win32, and only allow Win32 to link to readline if that patch is in readline, and spit out a compile error if readline doesn't have that patch. What would we patch it with? I don't think anybody has found a problem there, this is a separate file that you ship along with it. Well, the problem is that it handles backslash incorrectly. We could patch that in the readline source rather than playing with a configuaration file. As far as the license, psql spits out a copyright notice as it starts. It would be a shame to have to mention GPL in there. Even that may not be enough. This is the GPL we're talking about. At that point, psql becomes GPL, no question. Can we get any companies to fund a port of libedit to Win32? That would be nice. Takers? What does readline have that Win32 native editing does not? tab completion, for one. Some editing keys, IIRC. I thought history, but it does seem we have history workign on native :) I think what we don't have is saving history between psql uses. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Use cases
On Sun, Feb 12, 2006 at 06:29:21PM -0500, Andrew Dunstan wrote: Frankly - supplying more sample configs is likely to be fairly fruitless. A much better thing would be a really good tuning tool that would take stats and logs and other stuff from a running server and suggest improvements (e.g. add an index on fields (foo,bar) on baz, try doubling work_mem, increase stats buckets on blurfl ...) I disagree. Many people have gotten used to the idea of having multiple config files to choose from, thanks to MySQL. Database benchmarks are things that many years of study have gone into - this sort of homegrown effort is rather like a backyard attempt to construct a Maserati. The lack of any testing of concurrency is very telling. True, but in this case the lack of any kind of a reasonable config was a much bigger issue. Except for test 8, the numbers improved once he made a few config tweaks that I suggested (see the email thread I posted about a day or two ago for details). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Backslashes in string literals
Kevin Grittner wrote: This patch doesn't leave the standard_conforming_strings entry in guc.c with the GUC_REPORT flag, which it needs for psql to work right. Should I submit one last patch with this fix and the proper expected regression file? If so, where should I send it? (The hackers list won't take a file as big as that patch.) Oh, I was just going to add the GUC_REPORT when I applied the patch. I put that email in the patch queue so I would not forget. I you want, send a mega patch to the patches list, [EMAIL PROTECTED] One large patch is usually safest to apply. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] psql readline win32
Bruce Momjian pgman@candle.pha.pa.us writes: Magnus Hagander wrote: What would we patch it with? I don't think anybody has found a problem there, this is a separate file that you ship along with it. Well, the problem is that it handles backslash incorrectly. We could patch that in the readline source rather than playing with a configuaration file. Do the readline developers agree that it's incorrect? I could see shipping a patch as a short-term band-aid, but not if the patch isn't going to be accepted upstream. Even that may not be enough. This is the GPL we're talking about. At that point, psql becomes GPL, no question. Which means it's not happening, no? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql readline win32
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Magnus Hagander wrote: What would we patch it with? I don't think anybody has found a problem there, this is a separate file that you ship along with it. Well, the problem is that it handles backslash incorrectly. We could patch that in the readline source rather than playing with a configuaration file. Do the readline developers agree that it's incorrect? I could see shipping a patch as a short-term band-aid, but not if the patch isn't going to be accepted upstream. No idea. We need to develop the patch and submit it. Even that may not be enough. This is the GPL we're talking about. At that point, psql becomes GPL, no question. Which means it's not happening, no? To clearify, I meant the psql binary becomes GPL. When we build psql with readline, which is our default on many platforms, we are already be GPL'ing psql, at least according to the copyright holders, FSF. We are dynamic linking on many platforms, but according to the FSF, it makes it GPL. I do think that adding readline features to the Win32 psql doesn't warrant the license change for the psql binary. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql readline win32
What would we patch it with? I don't think anybody has found a problem there, this is a separate file that you ship along with it. Well, the problem is that it handles backslash incorrectly. We could patch that in the readline source rather than playing with a configuaration file. Do the readline developers agree that it's incorrect? I could see shipping a patch as a short-term band-aid, but not if the patch isn't going to be accepted upstream. I have seen no such agreement. The ability to reconfigure the keys is definitly a feature, so it could perhaps be argued to be that. In general, I don't think they care too much about win32 :-( Which is another thing that makes libedit a lot more encouraging - if it could be made working. Bruce wrote: I think what we don't have is saving history between psql uses. We do keep history if the new psql is startede in the same commandprompt. If you start a new cmd, history gets reset. (Which is what happens if you start it from the start menu) //Magnus //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql readline win32
Magnus Hagander wrote: What would we patch it with? I don't think anybody has found a problem there, this is a separate file that you ship along with it. Well, the problem is that it handles backslash incorrectly. We could patch that in the readline source rather than playing with a configuaration file. Do the readline developers agree that it's incorrect? I could see shipping a patch as a short-term band-aid, but not if the patch isn't going to be accepted upstream. I have seen no such agreement. The ability to reconfigure the keys is definitly a feature, so it could perhaps be argued to be that. In general, I don't think they care too much about win32 :-( Which is another thing that makes libedit a lot more encouraging - if it could be made working. I would love us to use libedit more, and our configure flags for libedit are improved in 8.2. I think what we don't have is saving history between psql uses. We do keep history if the new psql is startede in the same commandprompt. If you start a new cmd, history gets reset. (Which is what happens if you start it from the start menu) Ah, OK, I was testing from the start menu. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql readline win32
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: At that point, psql becomes GPL, no question. Which means it's not happening, no? To clearify, I meant the psql binary becomes GPL. There is no such thing as the binary becomes GPL. GPL applies to the source code. When we build psql with readline, which is our default on many platforms, we are already be GPL'ing psql, at least according to the copyright holders, FSF. No, we are NOT doing that, not even according to FSF. Our usage of a pre-installed readline library falls under this exception in the standard GPL terms: However, as a special exception, the source code distributed need not include anything that is normally distributed (in either source or binary form) with the major components (compiler, kernel, and so on) of the operating system on which the executable runs, unless that component itself accompanies the executable. When we link to a readline library that is normally present on the target system, we do not become covered by the GPL, because of this exception. But shipping readline in our package would be a flat violation of the GPL unless we are willing to relicense. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] User Defined Types in Java
I'm not suggesting that we remove the current way of doing things. I understand that if we did that, it would cause problem for everyone that has created scalar types up to this day. What I'm proposing is an alternative way of doing this, not a replacement. And as things stand today, I'd be happy if this alternative way was the only way to create a type that didn't use C functions (hence, no need for a special construct to create a shell type). That wouldn't break anything. What I'm proposing should be an addition that also can be seen as the beginning of a path to migrate the CREATE TYPE construct to conform with the SQL 2003 standard. Regards, Thomas Hallgren Tom Lane wrote: Thomas Hallgren [EMAIL PROTECTED] writes: Ok, so there are two 'optional' arguments. Following my suggestion, the input and receive function would always take 3 arguments. Then, it's up to the function as such if it makes use of them or not. Do you see any problem with that? (1) backwards compatibility (2) inability to ever add a fourth optional argument without creating a flag day for everyone I'm all for cleaning up the handling of shell types (and in fact have had that on my personal TODO list for ages). But I see zero if not negative usefulness in these ideas about changing CREATE TYPE. The certain outcome of that is to import all the complications of CREATE FUNCTION into CREATE TYPE, and for what gain? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] User Defined Types in Java
Thomas Hallgren [EMAIL PROTECTED] writes: What I'm proposing should be an addition that also can be seen as the beginning of a path to migrate the CREATE TYPE construct to conform with the SQL 2003 standard. I'd be interested to see where in the SQL2003 spec the syntax you are proposing can be found. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql readline win32
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: At that point, psql becomes GPL, no question. Which means it's not happening, no? To clearify, I meant the psql binary becomes GPL. There is no such thing as the binary becomes GPL. GPL applies to the source code. OK. When we build psql with readline, which is our default on many platforms, we are already be GPL'ing psql, at least according to the copyright holders, FSF. No, we are NOT doing that, not even according to FSF. Our usage of a pre-installed readline library falls under this exception in the standard GPL terms: However, as a special exception, the source code distributed need not include anything that is normally distributed (in either source or binary form) with the major components (compiler, kernel, and so on) of the operating system on which the executable runs, unless that component itself accompanies the executable. When we link to a readline library that is normally present on the target system, we do not become covered by the GPL, because of this exception. But shipping readline in our package would be a flat violation of the GPL unless we are willing to relicense. Interesting, but that phrase is for what you need to distribute for an already-GPL source code. See the GPL-related disputes section: http://en.wikipedia.org/wiki/Gpl and an old email from me on the topic: http://archives.postgresql.org/pgsql-general/2003-08/msg01811.php -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Using stats_command_string for xact statistics
I know we said we don't want to add an additional GUC variable just to
control xact statistics, but I am thinking that using
stat_command_string isn't a logical variable to use because it is
unrelated to commutative statistics.
I am thinking using row and block-level statistics to turn on xact
statistics makes sense, but not to use stat_command_string for that
purpose.
---
Bruce Momjian wrote:
Does anyone know why we test for pgstat_collect_querystring in routines
that obviously dump only block and row-level statistics and database
commit/rollback total? Is it a copy/paste error?
Patch attached for review. The inclusion of pgstat_collect_querystring
in these tests seems like a bug.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: src/backend/postmaster/pgstat.c
===
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.115
diff -c -c -r1.115 pgstat.c
*** src/backend/postmaster/pgstat.c 31 Dec 2005 19:39:10 - 1.115
--- src/backend/postmaster/pgstat.c 1 Jan 2006 03:31:24 -
***
*** 810,817
int i;
if (pgStatSock 0 ||
! !(pgstat_collect_querystring ||
! pgstat_collect_tuplelevel ||
pgstat_collect_blocklevel))
{
/* Not reporting stats, so just flush whatever we have */
--- 810,816
int i;
if (pgStatSock 0 ||
! !(pgstat_collect_tuplelevel ||
pgstat_collect_blocklevel))
{
/* Not reporting stats, so just flush whatever we have */
***
*** 1224,1231
void
pgstat_count_xact_commit(void)
{
! if (!(pgstat_collect_querystring ||
! pgstat_collect_tuplelevel ||
pgstat_collect_blocklevel))
return;
--- 1223,1229
void
pgstat_count_xact_commit(void)
{
! if (!(pgstat_collect_tuplelevel ||
pgstat_collect_blocklevel))
return;
***
*** 1256,1263
void
pgstat_count_xact_rollback(void)
{
! if (!(pgstat_collect_querystring ||
! pgstat_collect_tuplelevel ||
pgstat_collect_blocklevel))
return;
--- 1254,1260
void
pgstat_count_xact_rollback(void)
{
! if (!(pgstat_collect_tuplelevel ||
pgstat_collect_blocklevel))
return;
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql readline win32
Bruce Momjian wrote: When we build psql with readline, which is our default on many platforms, we are already be GPL'ing psql, at least according to the copyright holders, FSF. No, we are NOT doing that, not even according to FSF. Our usage of a pre-installed readline library falls under this exception in the standard GPL terms: However, as a special exception, the source code distributed need not include anything that is normally distributed (in either source or binary form) with the major components (compiler, kernel, and so on) of the operating system on which the executable runs, unless that component itself accompanies the executable. When we link to a readline library that is normally present on the target system, we do not become covered by the GPL, because of this exception. But shipping readline in our package would be a flat violation of the GPL unless we are willing to relicense. Interesting, but that phrase is for what you need to distribute for an already-GPL source code. See the GPL-related disputes section: http://en.wikipedia.org/wiki/Gpl and an old email from me on the topic: http://archives.postgresql.org/pgsql-general/2003-08/msg01811.php Let's just get off this track. We can easily tie ourselves up in knots over it. Moving to libedit everywhere would be a good way to go if it's achievable. Incidentally, the exception quoted probably doesn't apply to any closed source Unix any more than it does to Windows - last I looked none of them normally ship libreadline. So presumably it's desirable to make sure libedit works at least on those platforms. So what's needed to bring libedit up to scratch? Are there any platforms where it works as well as libreadline? On which platforms does it have reduced or no functionality? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Mark Woodward wrote: If I am a road warrior I want to be able to connect, run my dynamic dns client, and go. In your scenario of working as a road warrior, you are almost certainly not going to be able to have a workable DNS host name unless you have a raw internet IP address. More than likely you will have an IP address (known to your laptop) as a 192 or 10 address. Nonsense. There is a dynamic DNS client that is quite smart enough to find out and use the gateway address. See: http://ddclient.sourceforge.net/ I'm sure there are others, including some for Windows. But then, there is another problem, if you don't have a real and true IP address, if you are on anonymous 192 or 10 net (most likely the case), then your dynamic DNS entry allows EVERYONE on your network the same access. I still say an SSH tunnel with port forwarding is more secure, besides you can even compress the data stream. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Defined Types in Java
Tom Lane wrote:
Thomas Hallgren [EMAIL PROTECTED] writes:
What I'm proposing should be an addition that also can be seen as the
beginning of a path to migrate the CREATE TYPE construct to conform with
the SQL 2003 standard.
I'd be interested to see where in the SQL2003 spec the syntax you are
proposing can be found.
The PostgreSQL specific semantics with input/output/receive/send is are
not in the SQL 2003 standard. But it does define how methods can be
declared on a type and to me, the input/output/receive/send are as close
to constructors and instance methods as you can get. I'm *not*
suggesting a full implementation of this. I am merely stating that
moving functions into the type and adding a column to the pg_proc table
that allows a function to be tied to a type is a step in that direction:
Here's an example using SQL 2003 syntax (from JRT-2003). The BNF is below:
CREATE TYPE addr EXTERNAL NAME 'address_classes_jar:Address'
LANGUAGE JAVA
AS (
street_attr CHARACTER VARYING(50) EXTERNAL NAME 'street',
zip_attr CHARACTER(10) EXTERNAL NAME 'zip'
)
CONSTRUCTOR METHOD addr ()
RETURNS addr SELF AS RESULT
EXTERNAL NAME 'Address',
CONSTRUCTOR METHOD addr (s_parm CHARACTER VARYING(50), z_parm
CHARACTER(10))
RETURNS addr SELF AS RESULT
EXTERNAL NAME 'Address',
METHOD to_string ()
RETURNS CHARACTER VARYING(255)
EXTERNAL NAME 'toString',
STATIC METHOD contiguous (A1 addr, A2 addr)
RETURNS CHARACTER(3)
EXTERNAL NAME 'contiguous';
user-defined type definition ::= CREATE TYPE user-defined type body
user-defined type body ::=
schema-resolved user-defined type name
[ subtype clause ]
[ AS representation ]
[ user-defined type option list ]
[ method specification list ]
...
method specification list ::=
method specification [ { comma method specification }... ]
method specification ::=
original method specification
| overriding method specification
original method specification ::=
partial method specification [ SELF AS RESULT ] [ SELF AS LOCATOR ]
[ method characteristics ]
overriding method specification ::= OVERRIDING partial method
specification
partial method specification ::=
[ INSTANCE | STATIC | CONSTRUCTOR ]
METHOD method name SQL parameter declaration list
returns clause
[ SPECIFIC specific method name ]
specific method name ::= [ schema name period ]qualified identifier
method characteristics ::= method characteristic...
method characteristic ::=
language clause
| parameter style clause
| deterministic characteristic
| SQL-data access indication
| null-call clause
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Using stats_command_string for xact statistics
Bruce Momjian pgman@candle.pha.pa.us writes: I know we said we don't want to add an additional GUC variable just to control xact statistics, but I am thinking that using stat_command_string isn't a logical variable to use because it is unrelated to commutative statistics. I am thinking using row and block-level statistics to turn on xact statistics makes sense, but not to use stat_command_string for that purpose. I don't see any strong logic to that, and changing the behavior just for the sake of change doesn't appeal to me... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql readline win32
Tom Lane [EMAIL PROTECTED] writes: To clearify, I meant the psql binary becomes GPL. There is no such thing as the binary becomes GPL. GPL applies to the source code. That's an odd thing to say. The binary is as much covered by copyright as the source and can't be distributed without satisfying the requirements of the license that covers it. The GPL requirements mean you can't distribute a binary that depends on readline without including the corresponding source code. I'm not sure that's really an onerous requirement. It just means if you're a commercial vendor selling a binary-only version of Postgres you can't link your binary-only version against readline and then distribute it. Which should be pretty obvious anyways. (The exception Tom points out might even make it legal to distribute a Linux compile of Postgres linked against readline since most Linux distributions include readline. That wasn't true when that exception was written though so you may want to check with your lawyer about that.) I think people are mixing this stuff up with the less obvious claim about programs like postgres being deemed derivative works of libraries like readline because they depend on them. Postgres doesn't really depend in any real sense on readline so I can't see that argument working in this case anyways. If there was some GPLed library that Postgres couldn't work usefully without then there might be a real need for a non-GPL'd version of that library. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Mark Woodward wrote: Mark Woodward wrote: If I am a road warrior I want to be able to connect, run my dynamic dns client, and go. In your scenario of working as a road warrior, you are almost certainly not going to be able to have a workable DNS host name unless you have a raw internet IP address. More than likely you will have an IP address (known to your laptop) as a 192 or 10 address. Nonsense. There is a dynamic DNS client that is quite smart enough to find out and use the gateway address. See: http://ddclient.sourceforge.net/ I'm sure there are others, including some for Windows. But then, there is another problem, if you don't have a real and true IP address, if you are on anonymous 192 or 10 net (most likely the case), then your dynamic DNS entry allows EVERYONE on your network the same access. I still say an SSH tunnel with port forwarding is more secure, besides you can even compress the data stream. And then you have to allow shell access. What's wrong with SSL with client certificates? Personally, I doubt there's any great use case for DNS names. Like Tom says, if it involves much more that removing the AI_NUMERICHOST hint then let's forget it. (I also agree with a point Jan sometimes makes - that end client s/w generally should not be talking to the db at all - that's what middleware is for. Then this whole discussion becomes moot.) cheers andrew ---(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
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Andrew Dunstan [EMAIL PROTECTED] writes: Personally, I doubt there's any great use case for DNS names. Like Tom says, if it involves much more that removing the AI_NUMERICHOST hint then let's forget it. Perhaps more to the point: let's do that and wait to see if the field demand justifies expending lots of sweat on anything smarter. Given that we've gone this long with only allowing numeric IPs in pg_hba.conf, I suspect we'll find that few people really care. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Personally, I doubt there's any great use case for DNS names. Like Tom says, if it involves much more that removing the AI_NUMERICHOST hint then let's forget it. Perhaps more to the point: let's do that and wait to see if the field demand justifies expending lots of sweat on anything smarter. Given that we've gone this long with only allowing numeric IPs in pg_hba.conf, I suspect we'll find that few people really care. Well as one of the people that deploys and managees many, many postgresql installations I can say I have never run into the need to have dns names and the thought of dns names honestly seems silly. It will increase overhead and dependencies that I just wouldn't want in my installations. Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] psql readline win32
On Mon, Feb 13, 2006 at 01:19:46PM -0500, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: When we build psql with readline, which is our default on many platforms, we are already be GPL'ing psql, at least according to the copyright holders, FSF. When we link to a readline library that is normally present on the target system, we do not become covered by the GPL, because of this exception. But shipping readline in our package would be a flat violation of the GPL unless we are willing to relicense. Umm, whatever happens, the licence on psql doesn't change. If we link compile and link psql with readline and distribute the result, all that means is that the combined work must be distributed under terms compliant with the GPL (eg source availability, etc). The code doesn't become GPL'd. The licence on psql remains unchanged and if someone took the result and deleted all the GPL stuff, the result would still be licenced as BSD. Only the copyright holder can change the licence of code. All the GPL does in a combined work is require that any parts have the at least the same freedoms as required by the GPL. Since BSD is compatable with (ie more free than) the GPL, it's all ok, but at no point is any licence changed. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
On 2/13/06, Joshua D. Drake [EMAIL PROTECTED] wrote: Well as one of the people that deploys and managees many, many postgresql installations I can say I have never run into the need to have dns names and the thought of dns names honestly seems silly. It will increase overhead and dependencies that I just wouldn't want in my installations. It is not uncommon for an environment that has already suffered through one forced renumbering to forbid the use of hard set IPs in application software. With IPv6 we will just see more and more of that. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Perhaps more to the point: let's do that and wait to see if the field demand justifies expending lots of sweat on anything smarter. Given that we've gone this long with only allowing numeric IPs in pg_hba.conf, I suspect we'll find that few people really care. Well as one of the people that deploys and managees many, many postgresql installations I can say I have never run into the need to have dns names and the thought of dns names honestly seems silly. It will increase overhead and dependencies that I just wouldn't want in my installations. If you don't want DNS names, you don't have to use 'em, so arguments of this sort seem rather irrelevant to me. They would be relevant if we were talking about expending any great amount of development manpower ... which is why I'd rather see us do something minimal first, and see if it gets used much. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] How to VACUUM this table? 998994633 estimated total rows
So this presents the fact that pg_stattuple should prevent and guess [taking a sample?] that a table needs an urgent lookup instead of ending the scan and presenting real numbers?g.- On 2/13/06, Chris Browne [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Guido Barosio) writes: quote: If you are quite sure it has few if any dead tuples, it might be something to try to avoid VACUUMing except as needed to evade the 2^31 transaction limit... You may use the pg_stattuple software, included in the /contrib . This will show you the current scenery, and whether you shall clean or not dead tuples.The trouble with pg_stattuple() is that running it is virtually as expensive as running the vacuum.For a bit table, you pay all the I/Ocost, and any costs of the super-long-running-transaction and don'teven get any cleanup for that cost.--let name=cbbrowne and tld= cbbrowne.com in name ^ @ ^ tld;;http://cbbrowne.com/info/lsf.htmlPhilosophy is a game with objectives and no rules. Mathematics is a game with rules and no objectives.---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org-- /\ ASCII Ribbon Campaign.\ / - NO HTML/RTF in e-mail. X- NO Word docs in e-mail ./ \ -
[HACKERS] Anyone want to admit to being presinet.com?
And if so, would you mind stopping your mail system from regurgitating
copies of pghackers traffic? It's especially bad that you're sending
the stuff with a fraudulent envelope From, ie, one not pointing back
at yourself.
Feb 13 16:55:12 sss2 sm-mta[5221]: k1DLtBSE005221: from=[EMAIL PROTECTED],
size=3848, class=-60, nrcpts=1, msgid=[EMAIL PROTECTED], proto=ESMTP,
daemon=MTA, relay=mail.presinet.com [209.53.156.1]
regards, tom lane
--- Forwarded Message
Return-Path: [EMAIL PROTECTED]
Delivery-Date: Mon Feb 13 16:55:12 2006
Received: from presinet.com (mail.presinet.com [209.53.156.1])
by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1DLtBSE005221
for [EMAIL PROTECTED]; Mon, 13 Feb 2006 16:55:11 -0500 (EST)
Received: from mail pickup service by presinet.com with Microsoft SMTPSVC;
Mon, 13 Feb 2006 13:51:01 -0800
PureMessageGuid: {E36E9D67-EA8E-4442-967E-4498D1B5B218}
thread-index: AcYw3I3TqJS4sUupQgat6sCKlhTSRQ==
X-Original-To: [EMAIL PROTECTED]
X-Greylist: domain auto-whitelisted by SQLgrey-
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;s=beta; d=gmail.com;
h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
b=icR9BrBytqB8dsAOy5PPJzcfCHQZHPUbun6svoYP1+38ySIlmhOInNmPyDSuRIWHsQF3yQmiCI2FyUCMV0yuethFeV6IlgoVO+ZQOCvmh8AZLYjGeVNwkXMGtd0hqeswX9ULnEOIyDyZI3nOy9YI/9LGiajHGfkEm4M7mnBop84=
Message-ID: [EMAIL PROTECTED]
Date: Mon, 13 Feb 2006 13:51:00 -0800
From: Gregory Maxwell [EMAIL PROTECTED]
X-Mailer: Microsoft CDO for Exchange 2000
To: Joshua D. Drake [EMAIL PROTECTED]
Subject: Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Content-Class: urn:content-classes:message
Importance: normal
Cc: Tom Lane [EMAIL PROTECTED], Andrew Dunstan [EMAIL PROTECTED],
Mark Woodward [EMAIL PROTECTED],
Bruce Momjian pgman@candle.pha.pa.us, [EMAIL PROTECTED],
Euler Taveira de Oliveira [EMAIL PROTECTED],
Jim C. Nasby [EMAIL PROTECTED],
Andreas Pflug [EMAIL PROTECTED],
Marc G. Fournier [EMAIL PROTECTED],
pgsql-hackers@postgresql.org
Priority: normal
In-Reply-To: [EMAIL PROTECTED]
MIME-Version: 1.0
Content-Type: text/plain;
charset=ISO-8859-1
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Content-Disposition: inline
References: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL
PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL
PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.027 required=5 tests=[AWL=0.027]
X-Spam-Score: 0.027
X-Spam-Level:
X-Mailing-List: pgsql-hackers
List-Archive: http://archives.postgresql.org/pgsql-hackers
List-Help: mailto:[EMAIL PROTECTED]
List-Id: pgsql-hackers.postgresql.org
List-Owner: mailto:[EMAIL PROTECTED]
List-Post: mailto:pgsql-hackers@postgresql.org
List-Subscribe: mailto:[EMAIL PROTECTED]
List-Unsubscribe: mailto:[EMAIL PROTECTED]
Precedence: bulk
Sender: [EMAIL PROTECTED]
X-unconfigured-debian-site-MailScanner: Found to be clean
X-unconfigured-debian-site-MailScanner-From: [EMAIL PROTECTED]
X-OriginalArrivalTime: 13 Feb 2006 20:32:05.0609 (UTC)
FILETIME=[8DC53D90:01C630DC]
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by sss.pgh.pa.us id
k1DLtBSE005221
On 2/13/06, Joshua D. Drake [EMAIL PROTECTED] wrote:
Well as one of the people that deploys and managees many, many
postgresql installations I can say I have never run into the need to
have dns names and the thought of dns names honestly seems silly. It
will increase overhead and dependencies that I just wouldn't want in my
installations.
It is not uncommon for an environment that has already suffered
through one forced renumbering to forbid the use of hard set IPs in
application software.
With IPv6 we will just see more and more of that.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--- End of Forwarded Message
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Anyone want to admit to being presinet.com?
Tom, And if so, would you mind stopping your mail system from regurgitating copies of pghackers traffic? It's especially bad that you're sending the stuff with a fraudulent envelope From, ie, one not pointing back at yourself. The really amusing thing is that presinet.com claim to be Network Security Experts. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Anyone want to admit to being presinet.com?
Tom Lane wrote: And if so, would you mind stopping your mail system from regurgitating copies of pghackers traffic? It's especially bad that you're sending the stuff with a fraudulent envelope From, ie, one not pointing back at yourself. That would be me. I've notified one of our admins about the problem. It appears we are testing some new software on our mail system, and obviously there is a misconfiguration. Thanks for the heads-up, and sorry about the noise. Where did you see the emails? In this list? I haven't seen any show up here, or I would have gotten on this earlier. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Anyone want to admit to being presinet.com?
Bricklen Anderson [EMAIL PROTECTED] writes: Where did you see the emails? In this list? I haven't seen any show up here, or I would have gotten on this earlier. No, delivered to me personally, as you can see from the headers. It just started recently --- I've only gotten two so far. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Strange messages from pgstats after backend crash
A few days ago, I noticed the following in the postmaster log while messing with a bug that led to a backend coredump: [ report of backend sig11, then normal recovery, ending with ] LOG: transaction ID wrap limit is 1073759685, limited by database regression LOG: invalid server process ID -1 LOG: invalid server process ID -1 LOG: invalid server process ID -1 LOG: invalid server process ID -1 The invalid server process ID messages could only have come from pgstat.c. Apparently something sent some pgstat messages before executing InitBackendSharedInvalidationState, but I don't see where, and I've not been able to reproduce it. Any ideas? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Anyone want to admit to being presinet.com?
On Monday 13 February 2006 14:27, Josh Berkus wrote: Tom, And if so, would you mind stopping your mail system from regurgitating copies of pghackers traffic? It's especially bad that you're sending the stuff with a fraudulent envelope From, ie, one not pointing back at yourself. The really amusing thing is that presinet.com claim to be Network Security Experts. Security through effective banning of incoming mail.. At least they don't claim to be email delevery experts ;-) -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Using stats_command_string for xact statistics
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I know we said we don't want to add an additional GUC variable just to control xact statistics, but I am thinking that using stat_command_string isn't a logical variable to use because it is unrelated to commutative statistics. I am thinking using row and block-level statistics to turn on xact statistics makes sense, but not to use stat_command_string for that purpose. I don't see any strong logic to that, and changing the behavior just for the sake of change doesn't appeal to me... OK, additional sentence added, and paragraph split into two: !Additionally, per-database transaction commit and abort statistics !are collected if any of these parameters are set. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Permissions vs SERIAL columns
Jim C. Nasby wrote:
If nothing else, this should at least be documented in
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
I looked at this URL and just didn't see a good place to talk about
SERIAL sequence permissions, so I added something to the GRANT manual
page, attached.
---
On Fri, Dec 30, 2005 at 07:32:18PM +0100, Magnus Hagander wrote:
Haven't seen this discussed in a while, but I do recall it being
mentioned sometime before...
The problem:
testdb=# create table mytable (id serial, txt text);
testdb=# grant insert on mytable to user2;
GRANT
testdb=# \connect testdb user2
You are now connected to database testdb as user user2.
testdb= insert into mytable (txt) values ('foobar');
ERROR: permission denied for sequence mytable_id_seq
What I'd like to happen is for the grant for INSERT on the table to
cascade into an UPDATE permission on the sequence (when associated with
a SERIAL column only, of course).
Coming from a different database, such as MSSQL, makes people forget
this very easily, and it becomes very annoying.
Is this something that can be done without too much work? Anything you
can do in current pg even, just me not knowing how?
//Magnus
---(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
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 6: explain analyze is your friend
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/grant.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.51
diff -c -c -r1.51 grant.sgml
*** doc/src/sgml/ref/grant.sgml 21 Jan 2006 02:16:18 - 1.51
--- doc/src/sgml/ref/grant.sgml 14 Feb 2006 03:30:42 -
***
*** 376,381
--- 376,388
/para
para
+ Granting permission on a table does not automatically extend
+ permissions to any sequences used by the table, including
+ sequences tied to typeSERIAL/ columns. Permissions on
+ sequence must be set separately.
+/para
+
+para
Currently, productnamePostgreSQL/productname does not support
granting or revoking privileges for individual columns of a table.
One possible workaround is to create a view having just the desired
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [DOCS] Online backup vs Continuous backup
I used your suggestion and renamed online backup to incremental backup, and added a mention that many database vendors call it online backup. Patch attached. --- Rick Gigger wrote: How about: use Online backup or Hot backup to refer to either method of back since they are both done while the system is online or hot. If you want to get specific refer to doing a sql dump etc for using pg_dump Then use Incremental backup to refer to the whole process of the WAL archival etc Refer to the actual log files themselves as transaction logs. That all seems to be pretty intuitive and non-ambiguous non-confusing to me. On Dec 26, 2005, at 11:44 AM, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I suggest the following patch to rename our capability Continuous Backup. This doesn't seem like an improvement. Online backup is the standard terminology AFAIK. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/backup.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v retrieving revision 2.76 diff -c -c -r2.76 backup.sgml *** doc/src/sgml/backup.sgml7 Nov 2005 17:36:44 - 2.76 --- doc/src/sgml/backup.sgml14 Feb 2006 04:00:50 - *** *** 19,25 itemizedlist listitemparaacronymSQL/ dump/para/listitem listitemparaFile system level backup/para/listitem !listitemparaOn-line backup/para/listitem /itemizedlist Each has its own strengths and weaknesses. /para --- 19,25 itemizedlist listitemparaacronymSQL/ dump/para/listitem listitemparaFile system level backup/para/listitem !listitemparaIncremental backup/para/listitem /itemizedlist Each has its own strengths and weaknesses. /para *** *** 372,382 /para /sect1 ! sect1 id=backup-online ! titleOn-line backup and point-in-time recovery (PITR)/title indexterm zone=backup !primaryon-line backup/primary /indexterm indexterm zone=backup --- 372,382 /para /sect1 ! sect1 id=backup-incremental ! titleIncremental backup and point-in-time recovery (PITR)/title indexterm zone=backup !primaryincremental backup/primary /indexterm indexterm zone=backup *** *** 452,458 /para para !To recover successfully using an on-line backup, you need a continuous sequence of archived WAL files that extends back at least as far as the start time of your backup. So to get started, you should set up and test your procedure for archiving WAL files emphasisbefore/ you take your --- 452,459 /para para !To recover successfully using an incremental backup (also called online !backup by many database vendors), you need a continuous sequence of archived WAL files that extends back at least as far as the start time of your backup. So to get started, you should set up and test your procedure for archiving WAL files emphasisbefore/ you take your *** *** 782,793 functionpg_start_backup/ or functionpg_stop_backup/, and you will therefore be left to your own devices to keep track of which backup dump is which and how far back the associated WAL files go. ! It is generally better to follow the on-line backup procedure above. /para /sect2 sect2 id=backup-pitr-recovery !titleRecovering with an On-line Backup/title para Okay, the worst has happened and you need to recover from your backup. --- 783,794 functionpg_start_backup/ or functionpg_stop_backup/, and you will therefore be left to your own devices to keep track of which backup dump is which and how far back the associated WAL files go. ! It is generally better to follow the incremental backup procedure above. /para /sect2 sect2 id=backup-pitr-recovery !titleRecovering with an Incremental Backup/title para Okay, the worst has happened and you need to recover from your backup. *** *** 1119,1129 /para /sect2 ! sect2 id=backup-online-caveats titleCaveats/title
Re: [HACKERS] [DOCS] Online backup vs Continuous backup
Bruce Momjian wrote: I used your suggestion and renamed online backup to incremental backup, and added a mention that many database vendors call it online backup. Consistency would then demand that the other two be renamed to full backup. I think we had better suggestions earlier. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
