Re: [GENERAL] Bidirectional replication
On 05/03/2011 09:15 AM, tushar nehete wrote: Hi, Is there any way to do bidirectional replication for Postgresql Plus Advance Server 8.4.5? I tried SLONY-I but its master-slave asynchronous replication. Can we configure master-master replication by slony? Or is there any trusted tool to do it? Regards, Tushar I have heard good things about Bucardo, though I haven't tried it myself yet. I was warned that it would be risky to have 2 masters that have the same tables modified in both because of issues such as delayed sync, race conditions and other such goodies that may corrupt the meaning of the data. -- 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] Bidirectional replication
On 05/02/11 11:15 PM, tushar nehete wrote: Hi, Is there any way to do bidirectional replication for Postgresql Plus Advance Server 8.4.5? PostgreSQL Plus Advanced Server is a commercial product sold by EntepriseDB, you probably should ask them I tried SLONY-I but its master-slave asynchronous replication. Can we configure master-master replication by slony? Or is there any trusted tool to do it? In general, master-master replication is not easy to do efficiently and correctly. every implementation on any database suffers from issues with either very poor performance due to global synchronous locking and 2 phase commits, or it suffers from data collisions, which can only be avoided with careful application design and programming, not easily enforced at the database server. AFAIK, the only postgres replication systems that even pretend to support master-master are things like Bucardo that do the replication at the SQL layer, by sending all update/insert/delete commands to both servers, and under certain sequences of concurrent queries, you could end up with different results on the two servers. -- 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] Seg Fault in backend after beginning to use xpath (PG 9.0, FreeBSD 8.1)
On Mon, May 2, 2011 at 10:39 PM, Tom Lane wrote: > alan bryan writes: >> Checking out postgres.core and we see: > >> (gdb) bt >> #0 0x0008f5f19afd in pthread_mutex_lock () from /lib/libthr.so.3 >> #1 0x000800d22965 in xmlRMutexLock () from /usr/local/lib/libxml2.so.5 >> #2 0x000800d717e1 in xmlDictReference () from >> /usr/local/lib/libxml2.so.5 >> #3 0x000800d74ba5 in xmlSAX2StartDocument () >> from /usr/local/lib/libxml2.so.5 >> #4 0x000800ccee5f in xmlParseDocument () from >> /usr/local/lib/libxml2.so.5 >> #5 0x000800ccef85 in xmlDoRead () from /usr/local/lib/libxml2.so.5 >> #6 0x0076b58d in xpath () >> #7 0x005880e4 in GetAttributeByNum () >> #8 0x00588e91 in GetAttributeByName () >> #9 0x005850a3 in ExecProject () >> #10 0x0058c5e4 in ExecScan () >> #11 0x00584a2d in ExecProcNode () >> #12 0x0059bfc8 in ExecLimit () >> #13 0x005848f5 in ExecProcNode () >> #14 0x00583049 in standard_ExecutorRun () >> #15 0x0067630d in PostgresMain () >> #16 0x00677921 in PortalRun () >> #17 0x00672ea4 in pg_parse_and_rewrite () >> #18 0x00675354 in PostgresMain () >> #19 0x00626afb in ClosePostmasterPorts () >> #20 0x00627a8e in PostmasterMain () >> #21 0x005bbea7 in main () >> (gdb) > >> Ideas? Need more info? > > Well, the first thing that you should consider is rebuilding both PG and > libxml with debug symbols enabled, so you can get a stack trace that's > worth the electrons it's written on. That one has enough laughers in > the PG part to make me not trust the libxml part too much. That would > also help you find out what SQL command is being executed, which'd > possibly lead to being able to create a reproducible test case. > > regards, tom lane > Thanks Tom - I'll see what I can do. We just removed that new code and did it in our PHP code instead as a workaround. I'll try to spend some time getting a reproducible test case and come back with a better trace if possible. Appreciate the quick response. --Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Bidirectional replication
Hi, Is there any way to do bidirectional replication for Postgresql Plus Advance Server 8.4.5? I tried SLONY-I but its master-slave asynchronous replication. Can we configure master-master replication by slony? Or is there any trusted tool to do it? Regards, Tushar
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
John R Pierce wrote: otoh, there's plenty of places where natural keys are optimal. my company makes widgets, and we make damn sure our serial #s and part numbers are unique, and we use them as PK's for the various tables. Sure; what I was commenting on is that you normally can't ever trust external sources for identifiers. If you want to come up with your own, internally unique keys for things, great. But one of the goals of using a natural key is often to avoid the overhead of storing both that ID and some made up internal number, too. And whether the number is made up by the computer (the classic SERIAL or similar surrogate key), or you make one up yourself, it's still another chunk of data that gets stored for every item. It's just one that means something more useful in your case. Probably going to take up more space in the process and possibly be slower though--part number strings can easily end up longer than SERIAL-like integers. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] Seg Fault in backend after beginning to use xpath (PG 9.0, FreeBSD 8.1)
alan bryan writes: > Checking out postgres.core and we see: > (gdb) bt > #0 0x0008f5f19afd in pthread_mutex_lock () from /lib/libthr.so.3 > #1 0x000800d22965 in xmlRMutexLock () from /usr/local/lib/libxml2.so.5 > #2 0x000800d717e1 in xmlDictReference () from /usr/local/lib/libxml2.so.5 > #3 0x000800d74ba5 in xmlSAX2StartDocument () >from /usr/local/lib/libxml2.so.5 > #4 0x000800ccee5f in xmlParseDocument () from /usr/local/lib/libxml2.so.5 > #5 0x000800ccef85 in xmlDoRead () from /usr/local/lib/libxml2.so.5 > #6 0x0076b58d in xpath () > #7 0x005880e4 in GetAttributeByNum () > #8 0x00588e91 in GetAttributeByName () > #9 0x005850a3 in ExecProject () > #10 0x0058c5e4 in ExecScan () > #11 0x00584a2d in ExecProcNode () > #12 0x0059bfc8 in ExecLimit () > #13 0x005848f5 in ExecProcNode () > #14 0x00583049 in standard_ExecutorRun () > #15 0x0067630d in PostgresMain () > #16 0x00677921 in PortalRun () > #17 0x00672ea4 in pg_parse_and_rewrite () > #18 0x00675354 in PostgresMain () > #19 0x00626afb in ClosePostmasterPorts () > #20 0x00627a8e in PostmasterMain () > #21 0x005bbea7 in main () > (gdb) > Ideas? Need more info? Well, the first thing that you should consider is rebuilding both PG and libxml with debug symbols enabled, so you can get a stack trace that's worth the electrons it's written on. That one has enough laughers in the PG part to make me not trust the libxml part too much. That would also help you find out what SQL command is being executed, which'd possibly lead to being able to create a reproducible test case. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On May 2, 2011, at 10:53 PM, Rob Sargent wrote: > ...and you're at risk of having to reformat them when you buy out your > competitor. The scheme described was awfully similar to one that a client of mine used, product family prefix, identifiers within the family. And guess what? The scheme, which had been stable for 20+ years, had to change when a new variant of product was introduced which cut across family & product. I don't remember the details. I do remember that I hadn't used the supposedly stable product ids as PKs ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Help with database recovery ...
Craig Ringer writes: > This message is very weird: "could not read from file "pg_clog/02CD" at > offset 73728: Success". Probably indicates an attempted read from beyond EOF. The main relation-access code paths have been fixed to give a more intelligible error message about that case, but it doesn't look like slru.c has been. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Seg Fault in backend after beginning to use xpath (PG 9.0, FreeBSD 8.1)
Our developers started to use some xpath features and upon deployment we now have an issue where PostgreSQL is seg faulting periodically. Any ideas on what to look at next would be much appreciated. FreeBSD 8.1 PostgreSQL 9.0.3 (also tried upgrading to 9.0.4) built from ports Libxml2 2.7.6 (also tried upgrading to 2.7.8) built from ports pgsql logs show: May 1 17:51:13 192.168.20.100 postgres[11862]: [94-1] LOG: server process (PID 62112) was terminated by signal 11: Segmentation fault syslog shows: May 2 20:29:16 db3 kernel: pid 49956 (postgres), uid 70: exited on signal 11 (core dumped) May 2 21:06:37 db3 kernel: pid 39086 (postgres), uid 70: exited on signal 10 (core dumped) Checking out postgres.core and we see: (gdb) bt #0 0x0008f5f19afd in pthread_mutex_lock () from /lib/libthr.so.3 #1 0x000800d22965 in xmlRMutexLock () from /usr/local/lib/libxml2.so.5 #2 0x000800d717e1 in xmlDictReference () from /usr/local/lib/libxml2.so.5 #3 0x000800d74ba5 in xmlSAX2StartDocument () from /usr/local/lib/libxml2.so.5 #4 0x000800ccee5f in xmlParseDocument () from /usr/local/lib/libxml2.so.5 #5 0x000800ccef85 in xmlDoRead () from /usr/local/lib/libxml2.so.5 #6 0x0076b58d in xpath () #7 0x005880e4 in GetAttributeByNum () #8 0x00588e91 in GetAttributeByName () #9 0x005850a3 in ExecProject () #10 0x0058c5e4 in ExecScan () #11 0x00584a2d in ExecProcNode () #12 0x0059bfc8 in ExecLimit () #13 0x005848f5 in ExecProcNode () #14 0x00583049 in standard_ExecutorRun () #15 0x0067630d in PostgresMain () #16 0x00677921 in PortalRun () #17 0x00672ea4 in pg_parse_and_rewrite () #18 0x00675354 in PostgresMain () #19 0x00626afb in ClosePostmasterPorts () #20 0x00627a8e in PostmasterMain () #21 0x005bbea7 in main () (gdb) Ideas? Need more info? Thanks, Alan -- 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] setting connection/ query timeout
Thanks Daniele -Original Message- From: Daniele Varrazzo [mailto:daniele.varra...@gmail.com] Sent: Wed 4/20/2011 1:15 AM To: tamanna madaan Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] setting connection/ query timeout On Tue, Apr 19, 2011 at 8:09 PM, tamanna madaan wrote: > Sometimes this query gets stuck for unknown reason . So, for this particular > query I want to set a timeout > . I dont want to change "statement_timeout" in postgresql.conf as this would > affect all the connections/queries. > I just want to set timeout for above mentioned query . How can I do that ?? You can set a timeout only valid for the session using SET: => set statement_timeout to 1000; SET => select pg_sleep(2); ERROR: canceling statement due to statement timeout -- Daniele
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On 03/05/11 12:57, Rob Sargent wrote: > Hm.. Virtual machines as assets. Mortgage backed securities, anyone. Well, sure ... but the software running on them is tracked as part of licensing compliance efforts, whether or not the virtual hardware its self is an "asset" its self. The DB designer chose to use the host's MAC address to identify the host, and the tracking software can't tell the difference between a VM and a physical host. The other problem with that is that it doesn't deal well with multi-homed hosts. All in all, it's just a dumb design, and was fixed a while ago, but it's an illustration of how something that seems like an obvious natural key doesn't always stay one. -- Craig Ringer -- 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] pervasiveness of surrogate (also called synthetic) keys
Craig Ringer wrote: On 03/05/11 11:07, Greg Smith wrote: That doesn't mean you can't use them as a sort of foreign key indexing the data; it just means you can't make them the sole unique identifier for a particular entity, where that entity is a person, company, or part. Classic case: a database here has several tables indexed by MAC address. It's used for asset reporting and software inventory. Problem: VMs generate random MAC addresses by default. They're not guaranteed to be globally unique. Collisions have happened and will probably happen again. In this case, it wasn't a big deal, but it just goes to show that even the "obviously" globally unique isn't necessarily so. -- Craig Ringer Hm.. Virtual machines as assets. Mortgage backed securities, anyone. -- 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] pervasiveness of surrogate (also called synthetic) keys
John R Pierce wrote: otoh, there's plenty of places where natural keys are optimal. my company makes widgets, and we make damn sure our serial #s and part numbers are unique, and we use them as PK's for the various tables. further, the PN has a N digit prefix which is unique to a part family, then a M digit suffix which identifies a specific version of that PN. we use the N digit PN for the family tables, and the full N+M digit PN for the full PN tables. serial # is globally unique across all PNs so its the PK of any table related directly to a widget. I'm surprised to see, as a defence of natural keys, an arbitrary identifier composed of references to arbitrary identifiers elsewhere in the system described. How many ways is this just wrong? That you're assigning the serial numbers does not destinguish them from a UUID generator, oh except you've put semantics into the value. oh and you're at risk of having to reformat them when you buy out your competitor. Starts to look like the database-in-a-name scheme of which I'm oh so fond. I thought a natural key for a part would be more like "bolt=geometry:head-mm:head-depth:thread-per-inch:etc". And for something as describable as bolt indeed the complete record could well qualify as a natural key especially if none of the attributes are null-able (which is a believable concept for bolt). But bolt is not nut and both are parts. Gets messy quickly without arbitary simple keys. I think you're short on the "simple" part in your defn of serial number. And interestingly you're dealing with types. What does one do when one has to track instances of bolts. Given that all bolts of a certain natural key are identical, save for the fact that one was made before the other. The job is to register every bolt against its (current) deployment. Naturally one assigns each bolt a non-upc barcode aka UUID. -- 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] pervasiveness of surrogate (also called synthetic) keys
On 03/05/11 11:07, Greg Smith wrote: > That doesn't mean you can't use > them as a sort of foreign key indexing the data; it just means you can't > make them the sole unique identifier for a particular entity, where that > entity is a person, company, or part. Classic case: a database here has several tables indexed by MAC address. It's used for asset reporting and software inventory. Problem: VMs generate random MAC addresses by default. They're not guaranteed to be globally unique. Collisions have happened and will probably happen again. In this case, it wasn't a big deal, but it just goes to show that even the "obviously" globally unique isn't necessarily so. -- Craig Ringer -- 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] pervasiveness of surrogate (also called synthetic) keys
otoh, there's plenty of places where natural keys are optimal. my company makes widgets, and we make damn sure our serial #s and part numbers are unique, and we use them as PK's for the various tables. further, the PN has a N digit prefix which is unique to a part family, then a M digit suffix which identifies a specific version of that PN. we use the N digit PN for the family tables, and the full N+M digit PN for the full PN tables. serial # is globally unique across all PNs so its the PK of any table related directly to a widget. -- 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] pervasiveness of surrogate (also called synthetic) keys
On 05/02/2011 10:06 PM, Rob Sargent wrote: You would be surprise how many "bob smith"s where born on the same day. But then they weren't all born in a hospital etc etc etc. I wouldn't be surprised. I once lived in a mile-square town (Hoboken, that's it's nickname). In that town were 40K residents and three gyms. I forgot my ID card one day when going to mine, and they took my name and street name as alternate proof of identity. Some designer along the line figured that was unique enough. Number of Greg Smiths living on that street who were members of that one gym? Three. I see this whole area as being similar to SQL injection. The same way that you just can't trust data input by the user to ever be secure, you can't trust inputs to your database will ever be unique in the way you expect them to be. So if you build a so-called "natural key" based on them, expect that to break one day. That doesn't mean you can't use them as a sort of foreign key indexing the data; it just means you can't make them the sole unique identifier for a particular entity, where that entity is a person, company, or part. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] pervasiveness of surrogate (also called synthetic) keys
On 03/05/11 10:06, Rob Sargent wrote: > My wife works (at the sql level) with shall we say "records about > people". Real records, real people. Somewhere around 2 million unique > individuals, several million source records. They don't all have ssn, > they don't all have a drivers license. They don't all have an address, > many have several addresses (especially over time) and separate people > have at one time or another lived at the same address. ... and that's before we get into the horror of "what is someone's name". Which name? Which spelling? Do they even have a single canonical name? Is their canonical name - if any - expressable in the character set used by the service? Is it even covered by Unicode?!? Does it make any sense to split their name up into the traditional english-speaking-recent-western "family" and "given" name parts? Is there a single consistent way to do so for their name even if it does? etc. SSN? What if they don't live in the US or aren't a citizen? What if they have more than one SSN (yes, it happens)? Or there's one being shared by more than one person (again, this happens) and they can't get it fixed or don't want to? My mother's postal address - before street numbering was introduced a few years ago on her road - used to be "Rural Delivery Area 2, SomeTownName, New Zealand". You'd be amazed how many systems could not cope with that; she used to have to register all sorts of things to her parents' address in the nearby town. People decide to key a database on US Social Security number - because it's a "unique natural key" then discover the exciting problems with that. While they're battling those, business needs change and the database starts needing to accept people from other countries, who don't have a US SSN and don't know what one is or what it looks like. Tracking people/companies in databases are ideal candidates for synthetic keying with a solid split/merge procedure to handle duplicates, overlapping identity records, etc. -- Craig Ringer -- 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] pervasiveness of surrogate (also called synthetic) keys
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Rob Sargent > Sent: Monday, May 02, 2011 7:07 PM > To: Jeff Davis > Cc: Greg Smith; pgsql-general@postgresql.org > Subject: Re: [GENERAL] pervasiveness of surrogate (also called > synthetic) keys > > > > Jeff Davis wrote: > > On Mon, 2011-05-02 at 11:10 -0400, Greg Smith wrote: > >> The position Merlin > >> has advocated here, that there should always be a natural key > available > >> if you know the data well enough, may be true. But few people are > good > >> enough designers to be sure they've made the decision correctly, and > the > >> downsides of being wrong can be a long, painful conversion process. > >> Easier for most people to just eliminate the possibility of making a > >> mistake by using auto-generated surrogate keys, where the primary > >> problem you'll run into is merely using more space/resources than > you > >> might otherwise need to have. It minimizes the worst-case--mistake > make > >> in the model, expensive re-design--by adding overhead that makes the > >> average case more expensive. > > > > Once you really try to define "natural" and "surrogate" keys, I think > a > > lot of the arguments disappear. I wrote about this a few years back: > > > > http://thoughts.j-davis.com/2007/12/11/terminology-confusion/ > > > > In particular, I think you are falsely assuming that a natural key > must > > be generated from an outside source (or some source outside of your > > control), and is therefore not reliably unique. > > > > You can generate your own keys, and if you hand them out to customers > > and include them on paperwork, they are now a part of the reality > that > > your database models -- and therefore become natural keys. Invoice > > numbers, driver's license numbers, etc., are all natural keys, > because > > they are known about, and used, in reality. Usernames are, too, the > only > > difference is that you let the user choose it. > > > > In contrast, a pointer or a UUID typically does not represent > reality, > > because no humans ever see it and no computer systems outside yours > know > > about it. So, it's merely an implementation detail and should not be > a > > part of the model. > > > > Regards, > > Jeff Davis > > > My wife works (at the sql level) with shall we say "records about > people". Real records, real people. Somewhere around 2 million unique > individuals, several million source records. They don't all have ssn, > they don't all have a drivers license. They don't all have an address, > many have several addresses (especially over time) and separate people > have at one time or another lived at the same address. You would be > surprise how many "bob smith"s where born on the same day. But then > they weren't all born in a hospital etc etc etc. A person may present > on any of a birth record, a death record, a hospital record, a drivers > license, a medical registry, a marriage record and so on. There simply > is no natural key for a human. We won't even worry about the > non-uniqueness of ssn. And please don't get her started on twins. :) > > > I can only imagine that other equally complex entities are just as > slippery when it comes time to pinpoint the natural key. People are sometimes surprised to discover the social security numbers are not unique. There are fraudulent social security numbers: http://www2.nbc4i.com/news/2010/dec/06/2/study-finds-millions-duplicate-social-security-num-ar-316988/ There are numbers given out by the IRS by mistake: http://wnyt.com/article/stories/S1594530.shtml?cat=10115 -- 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] pervasiveness of surrogate (also called synthetic) keys
Jeff Davis wrote: On Mon, 2011-05-02 at 11:10 -0400, Greg Smith wrote: The position Merlin has advocated here, that there should always be a natural key available if you know the data well enough, may be true. But few people are good enough designers to be sure they've made the decision correctly, and the downsides of being wrong can be a long, painful conversion process. Easier for most people to just eliminate the possibility of making a mistake by using auto-generated surrogate keys, where the primary problem you'll run into is merely using more space/resources than you might otherwise need to have. It minimizes the worst-case--mistake make in the model, expensive re-design--by adding overhead that makes the average case more expensive. Once you really try to define "natural" and "surrogate" keys, I think a lot of the arguments disappear. I wrote about this a few years back: http://thoughts.j-davis.com/2007/12/11/terminology-confusion/ In particular, I think you are falsely assuming that a natural key must be generated from an outside source (or some source outside of your control), and is therefore not reliably unique. You can generate your own keys, and if you hand them out to customers and include them on paperwork, they are now a part of the reality that your database models -- and therefore become natural keys. Invoice numbers, driver's license numbers, etc., are all natural keys, because they are known about, and used, in reality. Usernames are, too, the only difference is that you let the user choose it. In contrast, a pointer or a UUID typically does not represent reality, because no humans ever see it and no computer systems outside yours know about it. So, it's merely an implementation detail and should not be a part of the model. Regards, Jeff Davis My wife works (at the sql level) with shall we say "records about people". Real records, real people. Somewhere around 2 million unique individuals, several million source records. They don't all have ssn, they don't all have a drivers license. They don't all have an address, many have several addresses (especially over time) and separate people have at one time or another lived at the same address. You would be surprise how many "bob smith"s where born on the same day. But then they weren't all born in a hospital etc etc etc. A person may present on any of a birth record, a death record, a hospital record, a drivers license, a medical registry, a marriage record and so on. There simply is no natural key for a human. We won't even worry about the non-uniqueness of ssn. And please don't get her started on twins. :) I can only imagine that other equally complex entities are just as slippery when it comes time to pinpoint the natural key. rjs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] We need you to beta-test PostgreSQL 9.1
As you've already read, PostgreSQL 9.1 Beta 1 is out. This means that the PostgreSQL developers have done most of the work, and it's now your turn. It's time for you to test PostgreSQL 9.1 and give us feedback. http://www.postgresql.org/about/news.1313 Given the record-setting number of new features in version 9.1, I'm sure you can find at least one you want to try out. So do it now, download PostgreSQL 9.1 beta, install it, and test the new feature. Then write up a test report or two. Beta Testing Guide: http://wiki.postgresql.org/wiki/HowToBetaTest How to Report Beta Tests: http://wiki.postgresql.org/wiki/HowToBetaTest#Reporting_Tests The webform for reporting tests is new. Right now it's a googledoc, but later it'll be a Django application. The idea is to make it as easy as possible for you to give us beta test reports. No authentication is required, and you don't have to subscribe to any mailing lists, and you don't need to harvest pg_controldata and log files. Every 5 minutes, the test reports grid is updated. Don't just report problems and failures. We want to know the successful tests too; that way we know how close we are to a release. Mind you, if you do want to join a mailing list, you can join the pgsql-testers list (http://archives.postgresql.org/pgsql-testers/) in order to discuss testing and ask questions. It's pretty low traffic. But the important thing is for you to get out and test. It's your turn. Both the quality and the speed of release for 9.1 depend on you. -- Josh Berkus PostgreSQL Core Team -- 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] pervasiveness of surrogate (also called synthetic) keys
On Mon, May 2, 2011 at 7:43 PM, Craig Ringer wrote: > I'm now strongly in favour of keeping an internal key that users never > see, and having separate user-visible identifiers. The users can demand > that those identifiers change format or generation method and it's an It's far easier to change a unique constraint on the fly than a PK in a busy production database. -- 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] pervasiveness of surrogate (also called synthetic) keys
On 03/05/11 08:25, Jeff Davis wrote: > You can generate your own keys, and if you hand them out to customers > and include them on paperwork, they are now a part of the reality that > your database models -- and therefore become natural keys. Invoice > numbers, driver's license numbers, etc., are all natural keys, because > they are known about, and used, in reality. Usernames are, too, the only > difference is that you let the user choose it. I've repeatedly run into situations where I generate a key that seems entirely sensible, making a generated primary key part of the business processes ... then external constraints force me to change the format of that key or start accepting keys from outside. "Oh, we need to move to 14-digit client IDs because that we interact with requires them". "We want invoice numbers to include a Luhn check digit, can you add that?". Etc. I'm now strongly in favour of keeping an internal key that users never see, and having separate user-visible identifiers. The users can demand that those identifiers change format or generation method and it's an easy change in only one place. Want two different keys? I can do that too. Record a key that matches some external system? That's easy. Want to be able to edit/override/rename keys? Yep, that's fuss free too, and it won't affect my audit history (which uses the real internal keys) or have to cascade to foreign key relationships in append-only ledger tables. I use a mix of surrogate and natural keys, depending on the situation. I see little point in surrogate keys for simple lookup tables, but find them invaluable in audited tables with lots of foreign key relationships that interact with other business systems. -- Craig Ringer -- 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] Help with database recovery ...
On 02/05/11 03:32, Iztok Stotl wrote: > My database crashed and server won't start ... > > -- > LOG: database system was interrupted while in recovery at 2011-05-01 > 19:31:37 CEST > HINT: This probably means that some data is corrupted and you will have > to use the last backup for recovery. > LOG: checkpoint record is at 2/D41C1350 > LOG: redo record is at 2/D41C1350; undo record is at 0/0; shutdown > FALSE > LOG: next transaction ID: 752151177; next OID: 50716 > LOG: next MultiXactId: 1; next MultiXactOffset: 0 > LOG: database system was not properly shut down; automatic recovery in > progress > LOG: redo starts at 2/D41C1394 > PANIC: could not access status of transaction 752150401 > DETAIL: could not read from file "pg_clog/02CD" at offset 73728: > Success > LOG: startup process (PID 3504) was terminated by signal 6 > LOG: aborting startup due to startup process failure > LOG: logger shutting down > --- > What can I do ? This shouldn't happen unless something broke badly on your system. Check your disks and file systems. Before trying to fix anything, make a complete copy of the entire postgresql directory, the one that contains the 'pg_xlog', 'pg_clog' folders etc. Store that copy on a removable hard drive or some other media that's not connected to the system you're recovering. This message is very weird: "could not read from file "pg_clog/02CD" at offset 73728: Success". What does 'ls -l pg_clog/' say? -- Craig Ringer -- 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] pervasiveness of surrogate (also called synthetic) keys
On Mon, 2011-05-02 at 11:10 -0400, Greg Smith wrote: > The position Merlin > has advocated here, that there should always be a natural key available > if you know the data well enough, may be true. But few people are good > enough designers to be sure they've made the decision correctly, and the > downsides of being wrong can be a long, painful conversion process. > Easier for most people to just eliminate the possibility of making a > mistake by using auto-generated surrogate keys, where the primary > problem you'll run into is merely using more space/resources than you > might otherwise need to have. It minimizes the worst-case--mistake make > in the model, expensive re-design--by adding overhead that makes the > average case more expensive. Once you really try to define "natural" and "surrogate" keys, I think a lot of the arguments disappear. I wrote about this a few years back: http://thoughts.j-davis.com/2007/12/11/terminology-confusion/ In particular, I think you are falsely assuming that a natural key must be generated from an outside source (or some source outside of your control), and is therefore not reliably unique. You can generate your own keys, and if you hand them out to customers and include them on paperwork, they are now a part of the reality that your database models -- and therefore become natural keys. Invoice numbers, driver's license numbers, etc., are all natural keys, because they are known about, and used, in reality. Usernames are, too, the only difference is that you let the user choose it. In contrast, a pointer or a UUID typically does not represent reality, because no humans ever see it and no computer systems outside yours know about it. So, it's merely an implementation detail and should not be a part of the model. Regards, Jeff Davis -- 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] Needs Suggestion
On 04/28/2011 02:11 AM, SUBHAM ROY wrote: I am using postgres 8.4.8, the above command explain(analyze,buffers) is not working. Is there a way to do that. Nope, the (BUFFERS) syntax was added in 9.0. Try the other suggestions about using table stats to determine what you want to know. -- Craig Ringer -- 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] wnat ot edit pg_hba.conf file from command prompt
On Mon, May 2, 2011 at 8:57 AM, Raymond O'Donnell wrote: > On 02/05/2011 15:51, Leif Biberg Kristensen wrote: >> >> On Monday 02 May 2011 16:28:48 Sim Zacks wrote: >> >>> sed for windows - http://gnuwin32.sourceforge.net/packages/sed.htm >> >> Whatever happened to edlin? > > Goodness, I'd forgotten all about that - been a long time... Wikipedia says > that it was removed from Windows at some point, though there are a couple of > ports of it knocking around still. In all seriousness though, use anything BUT notepad in windows. Any good text editor will do. -- 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] Pass age function to extract function
Try removing the keyword "interval" (i.e., EXTRACT(MINUTE FROM TestRunTime)). Since TestRunTime is a column name, I think if you wanted to cast it as an INTERVAL you'd specify EXTRACT(MINUTE FROM TestRunTime::INTERVAL), but since TestRunTime is already an INTERVAL, the cast is redundant. On Mon, May 2, 2011 at 4:16 PM, Adam Bruss wrote: > Hello, > > > > How can one pass a non string literal interval to the extract function? > > > > For example: > > > > SELECT starttime, *extract(minute from interval testruntime) as runtime > from ( select age(endtime, starttime) as testruntime*, > ref_testnames_serial, starttime, endtime, dense_rank() over (order by > starttime desc) dr from dtprfrm764.orion_tests where ref_testnames_serial = > 389 and pass = true) x WHERE dr <= 20 ORDER BY starttime asc > > > > Does ‘minute from interval’ only accept string literals? > > > > Thanks, > > Adam > > > > Adam Bruss > > Development Engineer > > AWR Corporation/Simulation Technology & Applied Research > > 11520 N. Port Washington Rd., Suite 201 > > Mequon, WI 53092 USA > > P: 1.262.240.0291 x104 > > F: 1.262.240.0294 > > E: abr...@awrcorp.com > > W: http://www.awrcorp.com > > > -- Rick Genter rick.gen...@gmail.com
[GENERAL] Pass age function to extract function
Hello, How can one pass a non string literal interval to the extract function? For example: SELECT starttime, extract(minute from interval testruntime) as runtime from ( select age(endtime, starttime) as testruntime, ref_testnames_serial, starttime, endtime, dense_rank() over (order by starttime desc) dr from dtprfrm764.orion_tests where ref_testnames_serial = 389 and pass = true) x WHERE dr <= 20 ORDER BY starttime asc Does 'minute from interval' only accept string literals? Thanks, Adam Adam Bruss Development Engineer AWR Corporation/Simulation Technology & Applied Research 11520 N. Port Washington Rd., Suite 201 Mequon, WI 53092 USA P: 1.262.240.0291 x104 F: 1.262.240.0294 E: abr...@awrcorp.com W: http://www.awrcorp.com
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On Mon, May 2, 2011 at 10:10 AM, Greg Smith wrote: > On 05/01/2011 06:12 PM, Karsten Hilbert wrote: >> >> Good to know since I'm only a lowly medical doctor not >> having much schooling in database matters beyond this list, >> the PostgreSQL docs, and the Celko book. >> > > This debate exists at all levels of experience, and the only thing that > changes as you get more experienced people involved is an increase in > anecdotes on each side. The sole time I ever found myself arguing with Joe > Celko is over an article he wrote recommending natural keys, using an > example from the automotive industry. Problem was, the specific example he > gave was flat out wrong. I was working in automotive MIS at the time, and > the thing he was saying would never change did, in fact, change every > year--in only a fraction of a percent of cases, in an extremely subtle way > that snuck up on people and wreaked much confusion. That's typical for an > early natural key design: you get it working fine in V1.0, only to discover > months or years down the road there's a case you never considered you don't > model correctly, and it may take some sort of conversion to fix. > > The reason why there's a strong preference for surrogate keys is that they > always work and you can avoid ever needing to come up with a better design. > if you just use them and forget about it. The position Merlin has advocated > here, that there should always be a natural key available if you know the > data well enough, may be true. But few people are good enough designers to > be sure they've made the decision correctly, and the downsides of being > wrong can be a long, painful conversion process. Easier for most people to > just eliminate the possibility of making a mistake by using auto-generated > surrogate keys, where the primary problem you'll run into is merely using > more space/resources than you might otherwise need to have. It minimizes > the worst-case--mistake make in the model, expensive re-design--by adding > overhead that makes the average case more expensive. Software design > usually has enough risks that any time you can eliminate one just by > throwing some resources at it, that's normally the right thing to do. There are many practical arguments advocating the use of surrogate keys. Faster updates, easier schema changes, performance, maintenance costs down the line, better tools integration, etc. These arguments basically involve trade-offs that can be justifiably be used to push you one way or the other. That's all well and good. However, I tend to disagree with arguments that you are better off not identifying natural keys at all. To my mind, any database that has a table with no discernible key but whose records are referred to via another table's foreign key has a schema that is in a State of Error. A surrogate key is just added information to the database -- why does it play that record X out of N identical candidates should be the one mapped? Is that logic repeatable? What are the ramifications for joins that do not flow through the ID columns? Typically what it means is that the rules that guard against duplicate information entry are not, in fact in the database at all but in the application, and bad data can now get into your database by a much broader array of causes. The last and best defense against a nasty and common class of data errors has been removed. The more complex and your database is, the more it tends to be used a by a large array of clients, possibly even spanning multiple computer languages -- thus the need for a root system of constraint checking that is declarative and easily understood. Sure, requirements change, models change, but at any particular point and time a model with as little as possible (read: none) outside inputs should be able to provably demonstrate verifiable facts. With a natural key database (or a surrogate key database with defined keys that are not used for RI) you have inherent constraint checking that a purely surrogate database simply doesn't have. Whatever the software maintenance costs are, which is itself a complex and debatable topic, I'll go with a strategy that gives a better defense against bad or ambiguous data. merlin -- 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] pervasiveness of surrogate (also called synthetic) keys
I've been carefully reading all of the comments with great interest. Thanks very much for the thoughtful responses - very enlightening. - Jim (the topic originator) Jim Irrer ir...@umich.edu (734) 647-4409 University of Michigan Hospital Radiation Oncology 519 W. William St. Ann Arbor, MI 48103 On Mon, May 2, 2011 at 11:10 AM, Greg Smith wrote: > On 05/01/2011 06:12 PM, Karsten Hilbert wrote: > >> Good to know since I'm only a lowly medical doctor not >> having much schooling in database matters beyond this list, >> the PostgreSQL docs, and the Celko book. >> >> > > This debate exists at all levels of experience, and the only thing that > changes as you get more experienced people involved is an increase in > anecdotes on each side. The sole time I ever found myself arguing with Joe > Celko is over an article he wrote recommending natural keys, using an > example from the automotive industry. Problem was, the specific example he > gave was flat out wrong. I was working in automotive MIS at the time, and > the thing he was saying would never change did, in fact, change every > year--in only a fraction of a percent of cases, in an extremely subtle way > that snuck up on people and wreaked much confusion. That's typical for an > early natural key design: you get it working fine in V1.0, only to discover > months or years down the road there's a case you never considered you don't > model correctly, and it may take some sort of conversion to fix. > > The reason why there's a strong preference for surrogate keys is that they > always work and you can avoid ever needing to come up with a better design. > if you just use them and forget about it. The position Merlin has advocated > here, that there should always be a natural key available if you know the > data well enough, may be true. But few people are good enough designers to > be sure they've made the decision correctly, and the downsides of being > wrong can be a long, painful conversion process. Easier for most people to > just eliminate the possibility of making a mistake by using auto-generated > surrogate keys, where the primary problem you'll run into is merely using > more space/resources than you might otherwise need to have. It minimizes > the worst-case--mistake make in the model, expensive re-design--by adding > overhead that makes the average case more expensive. Software design > usually has enough risks that any time you can eliminate one just by > throwing some resources at it, that's normally the right thing to do. > > -- > Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us > "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books > > > > -- > 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] pipe line error (psql command)
I had a similar problem. When executing my shp2pgsql command within a bash shell script it failed and provided me with the valid options. Adrian Klaver, with a couple of emails back and forth, identified my error. I had not been using single and double quotes to assign my 'command' to a variable and then tried 'executing' the contents of the variable. This was done for my beginner level progress monitoring and logging. Adrian noticed a tick (`) or back quote is needed instead of the single or double quote. My scripts are now singing along nicely. Thank you Adrian. Jerry -- View this message in context: http://postgresql.1045698.n5.nabble.com/pipe-line-error-psql-command-tp4337702p4365141.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] auto-reconnect: temp schemas, sequences, transactions
Marek =?utf-8?q?Wi=C4=99ckowski?= writes: > But what happens with a db transaction upon disconnect? If I have (say, in > c++ > code or a script): > begin; > query1; > query2; > query3; > query4; > query5; > commit; > (with possibly some extra c++ or script code in between queries), and > somewhere at the time when query2 is being executed some other backend > crashes; session gets disconnected and automatically connected: what would > happen to next queries which would be executed by the external code (query3, > 4 > and so on)? They would not be executed outside of db transaction, wouldn't > they? I would hope that they all keep failing up until next commit/rollback > or > something similar... Well, there will be no memory on the server side of any uncompleted queries. If the client-side logic tries to re-issue these queries after re-connecting, it would be up to that logic to be careful about what to reissue or not. Possibly this is a question for the author of your client library. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] auto-reconnect: temp schemas, sequences, transactions
Hi, On Monday 02 May 2011 16:43:54 Tom Lane wrote: > Marek Wieckowski writes: > > If I understand correctly, Tom's reply in: > > http://archives.postgresql.org/pgsql-general/2007-06/msg01668.php > > suggests that temp schemas are kept when a session gets disconnected > > because connections get automatically re-established with the same > > backend id, and if this succeeds the old temp tables get picked up by the > > new connection as if there was no disconnection at all. > > Uh, no, surely not. The schema itself is re-used if it exists, but all > the contained tables get flushed by the new session (if for some reason > the old session failed to do that, as it would in case of a crash). Clear. Thanks for your answer. But what happens with a db transaction upon disconnect? If I have (say, in c++ code or a script): begin; query1; query2; query3; query4; query5; commit; (with possibly some extra c++ or script code in between queries), and somewhere at the time when query2 is being executed some other backend crashes; session gets disconnected and automatically connected: what would happen to next queries which would be executed by the external code (query3, 4 and so on)? They would not be executed outside of db transaction, wouldn't they? I would hope that they all keep failing up until next commit/rollback or something similar... Best, ~Marek -- 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] pervasiveness of surrogate (also called synthetic) keys
On 05/01/2011 06:12 PM, Karsten Hilbert wrote: Good to know since I'm only a lowly medical doctor not having much schooling in database matters beyond this list, the PostgreSQL docs, and the Celko book. This debate exists at all levels of experience, and the only thing that changes as you get more experienced people involved is an increase in anecdotes on each side. The sole time I ever found myself arguing with Joe Celko is over an article he wrote recommending natural keys, using an example from the automotive industry. Problem was, the specific example he gave was flat out wrong. I was working in automotive MIS at the time, and the thing he was saying would never change did, in fact, change every year--in only a fraction of a percent of cases, in an extremely subtle way that snuck up on people and wreaked much confusion. That's typical for an early natural key design: you get it working fine in V1.0, only to discover months or years down the road there's a case you never considered you don't model correctly, and it may take some sort of conversion to fix. The reason why there's a strong preference for surrogate keys is that they always work and you can avoid ever needing to come up with a better design. if you just use them and forget about it. The position Merlin has advocated here, that there should always be a natural key available if you know the data well enough, may be true. But few people are good enough designers to be sure they've made the decision correctly, and the downsides of being wrong can be a long, painful conversion process. Easier for most people to just eliminate the possibility of making a mistake by using auto-generated surrogate keys, where the primary problem you'll run into is merely using more space/resources than you might otherwise need to have. It minimizes the worst-case--mistake make in the model, expensive re-design--by adding overhead that makes the average case more expensive. Software design usually has enough risks that any time you can eliminate one just by throwing some resources at it, that's normally the right thing to do. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] wnat ot edit pg_hba.conf file from command prompt
On 02/05/2011 15:51, Leif Biberg Kristensen wrote: On Monday 02 May 2011 16:28:48 Sim Zacks wrote: sed for windows - http://gnuwin32.sourceforge.net/packages/sed.htm Whatever happened to edlin? Goodness, I'd forgotten all about that - been a long time... Wikipedia says that it was removed from Windows at some point, though there are a couple of ports of it knocking around still. 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] wnat ot edit pg_hba.conf file from command prompt
On Monday 02 May 2011 16:28:48 Sim Zacks wrote: > sed for windows - http://gnuwin32.sourceforge.net/packages/sed.htm Whatever happened to edlin? regards, Leif -- 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] auto-reconnect: temp schemas, sequences, transactions
Marek =?utf-8?q?Wi=C4=99ckowski?= writes: > If I understand correctly, Tom's reply in: > http://archives.postgresql.org/pgsql-general/2007-06/msg01668.php suggests > that temp schemas are kept when a session gets disconnected because > connections get automatically re-established with the same backend id, and if > this succeeds the old temp tables get picked up by the new connection as if > there was no disconnection at all. Uh, no, surely not. The schema itself is re-used if it exists, but all the contained tables get flushed by the new session (if for some reason the old session failed to do that, as it would in case of a crash). Re-use of the schema object is just a minor implementation optimization --- there's no expectation that temp tables would ever survive into another session. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] wnat ot edit pg_hba.conf file from command prompt
On 05/02/2011 02:24 PM, sm wrote: hi, i want ot modify the pg_hba.conf file from command prompt i tried this command C:\Program Files\Postgresql\8.4\bin>pgadmin3/ch:"C:/program files/postgresql/8.4/data/pg_hba.conf but with this a window gets open and there is option to change methos md5 to trust, is ther any parameters to pass with this command to edit “pg_hba.conf” file silently without user interaction sed for windows - http://gnuwin32.sourceforge.net/packages/sed.htm -- 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] wnat ot edit pg_hba.conf file from command prompt
On 02/05/2011 12:24, sm wrote: hi, i want ot modify the pg_hba.conf file from command prompt i tried this command C:\Program Files\Postgresql\8.4\bin>pgadmin3/ch:"C:/program files/postgresql/8.4/data/pg_hba.conf but with this a window gets open and there is option to change methos md5 to trust, is ther any parameters to pass with this command to edit “pg_hba.conf” file silently without user interaction Well, pg_hba.conf is just a plain text file, if that helps. 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
[GENERAL] wnat ot edit pg_hba.conf file from command prompt
hi, i want ot modify the pg_hba.conf file from command prompt i tried this command C:\Program Files\Postgresql\8.4\bin>pgadmin3/ch:"C:/program files/postgresql/8.4/data/pg_hba.conf but with this a window gets open and there is option to change methos md5 to trust, is ther any parameters to pass with this command to edit “pg_hba.conf” file silently without user interaction -- View this message in context: http://postgresql.1045698.n5.nabble.com/wnat-ot-edit-pg-hba-conf-file-from-command-prompt-tp4364462p4364462.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On Thursday, April 28, 2011 08:31:09 PM Scott Ribe wrote: > Well, natural keys are quite obviously the way to go, when they exist. The > problem is, they usually don't really exist. What's usually proposed as a > natural key, will upon further investigation, either not be guaranteed > unique, or not guaranteed to be unchanging, or both. There is no fundamental problem with changing primary keys. Sure, there are challenges and situations where thats annoying and problematic, but it's not as bad as often assumed. Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] auto-reconnect: temp schemas, sequences, transactions
Hi, If I understand correctly, Tom's reply in: http://archives.postgresql.org/pgsql-general/2007-06/msg01668.php suggests that temp schemas are kept when a session gets disconnected because connections get automatically re-established with the same backend id, and if this succeeds the old temp tables get picked up by the new connection as if there was no disconnection at all. However, it seems that the same does not happen for currval of sequences? This is quite inconvenient, I must say (in some situations our re-established connection is basically useless... and it even does not "know" about it...). For my information: what happens if the disconnect (due to another client process dying) happened during a transaction? After reconnect are we again in the middle of the previous transaction? (Or does something else happen, e.g. the transaction continues until "commit" and only then the session actually disconnects?) I'm anxiously assuming that it's not like the next queries after reconnection will happily be executed outside of a transaction, but I haven't found much (recent) info on the subject. Best, ~Marek =# select version(); version --- PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.4, 64-bit (1 row -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql, PSN hack and table limits
Greg Smith wrote: On 05/01/2011 01:50 AM, Mark Morgan Lloyd wrote: Somebody is making a very specific claim that Postgres can support a limited number of rows Did you find this via http://www.reversecurity.com/2011/04/new-details-from-psn-hack.html ? That was the only Google-indexed source leading to it I found. I just left a note there about the silliness of these claims. I could run more than a 10M row PostgreSQL instance on my phone. Unless there's a new 16-bit only Vic 20 port of PostgreSQL available or something, it's seems unlikely the data had to be partitioned due to any hard limit. Yes, via Google. I was digging around for any information about what sort of access and APIs the network made available to end-user (or ersatz developer) systems. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
