Re: [GENERAL] bi-directional syncing help request

On Fri, Aug 9, 2013 at 8:12 AM, Bèrto ëd Sèra wrote: > >> or you can just take a full dump from one box and import it on the other >> any time you switch. If it's not a big db it should probably be quicker >> than any alternative approach. >> > > A pg_dump f

Re: [GENERAL] bi-directional syncing help request

or you can just take a full dump from one box and import it on the other any time you switch. If it's not a big db it should probably be quicker than any alternative approach. On 9 August 2013 16:10, Bèrto ëd Sèra wrote: > not sure having to write stuff any time you do even the smalle

Re: [GENERAL] bi-directional syncing help request

who can create data. On 9 August 2013 16:06, bricklen wrote: > On Fri, Aug 9, 2013 at 7:36 AM, Bèrto ëd Sèra wrote: > >> --all you need to do is switch master and slave so that "master" is the >> one box you are currently on >> > > That probably isn

Re: [GENERAL] bi-directional syncing help request

sorry, half asleep and typing rubbish. --all you need to do is switch master and slave so that "master" is the one box you are currently on On 9 August 2013 15:35, Bèrto ëd Sèra wrote: > if it's only you using it, all you need to do is switch master and server > so that &q

Re: [GENERAL] bi-directional syncing help request

if it's only you using it, all you need to do is switch master and server so that "server" is the one box you are currently on. If both boxes produce data at the same time you need a lot of work to manage row versioning. On 9 August 2013 15:27, Paula Kirsch wrote: > Hi. I'm looking for suggesti

Re: [GENERAL] incremental dumps

> suppose wal archiving or PITR would be better +1, never re-invent the wheel, unless you really need to. Bèrto On 1 August 2013 14:14, Luca Ferrari wrote: > On Thu, Aug 1, 2013 at 10:59 AM, wrote: > > > However, the diff files seem to be considerably larger than one would > expect. > > One

Re: [GENERAL] Why are stored procedures looked on so negatively?

Hi, >the whole design of this application is asynchronous in nature. Then you'll be MUCH better off with SPs, from an architectural POV, as you can basically design "building blocks" by initially just making SPs that deliver a mock result, and have the entire development of the app server being in

Re: [GENERAL] Why are stored procedures looked on so negatively?

Hi, > In other words an API in the database. +1. People code apps and then disappear, because once the development is over they are not available in the company any more. And each thing you hardwire in the app becomes a stopper. Meanwhile, every company will have at least one DBA, who can manage/u

Re: [GENERAL] Success stories of PostgreSQL implementations in different companies

The Greater London Authority is also ditching Oracle in favour of PG. I consulted them while they kick started their transition and the first new PG/PostGIS only project is already delivered. The number of companies ditching Oracle is probably much larger than it seems, giving the dynamics in salar

Re: [GENERAL] table spaces

Hi Gregg yes, keep the indexes on a separate channel. Much depends on how the data is mapped and accessed, sometimes even distributing the data itself onto different spaces may do good. If you use a lot of logging (say you feed a massive pgFouine activity), you would want to have that on yet anot

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi > This restriction is specified in the SQL standard. Thanks! This is the kind of thing one CAN sell to customers :) "Your thing is out of standards, Sir" sounds much better than "But I really hate that, Sir". Which has, however, a terrible impact on the ORM that use circular FKs. Will have to

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi > also a > deferrable primary key/unique constraint can't be used as the target > for a foreign key. ehr, why? I mean, I'm positive it cannot be used before an actual value is in the record, but what would be the problem, apart from that? Cheers Bèrto -- == If Pa

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi > I still don't see how that's any better than a stored procedure that > directly does the INSERT. You can conceal the code every bit as > easily. Guys I DO NOT write the customers' security guidelines. I get asked to produce a design in which "party X will make plain INSERTs and ignore the ve

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi > You've hidden nothing from INSERT-RETURNING. ?? Or from a select, if the final value is what you mean. What we hide is the way values are made, clearly not the final value. That bit is accessible to anyone who can select the table, obviously. Bèrto -- == If Pac

Re: [GENERAL] tablespace directories

Hi Laurenz > That's the catalog version. > You can't get it from the catalogs AFAIK, but > the pg_controldata server utility will show > the information. Thanks! Much appreciated. I'm in a bash script, so it's fine. Cheers Bèrto -- == If Pac-Man had affected us as k

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi, > That sounds a bit contrived, but you could create a view > and hide the processing in an INSTEAD OF INSERT trigger. Yes, there are ways to hack it anyway. The thing is about keeping it simple and having it come out clear of a \d, when you ask info about the table from within psql. It is def

Re: [GENERAL] tablespace directories

Hi again, > /PG_9.2_201204301/417519 This should really have been written /PG_9.2_201204301/ the mystery is around the version_date (or so it would seem to be) part. Cheers Bèrto -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened ro

[GENERAL] tablespace directories

Hi all, I have to monitor a number of pg physical files based on writing access time. All fine, stats does it. I do, however, have a problem in finding out where the files are. To get a list I'm using select oid, spcname, pg_tablespace_location(oid) from pg_tablespace however, the locat

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi Chris, > Why do that as a trigger, then? Why not simply call a procedure that > generates the value and inserts it? Because this must be unknown to whoever makes the call and I'm not supposed to expose any detail of what's going on behind the scenes. Outsourcing part of sensitive apps also mea

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi Chris, > I don't see > any reason to create a record with a NULL and then replace that NULL > before committing. Sort out program logic first; then look to the > database. I beg to differ here. Say you have a set of business rules that rigidly defines how that field must be made AND the data o

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi, > The value of having NOT NULL deferrable is, well, to not check for NULL > until the tx commits. When working with ORMs this often is the case, > especially with circular FKs. +1000 here. Cheers Bèrto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes t

Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records

righto. You need a CTE to do that create table deletable ( id bigint not null primary key); create table condition ( id bigint not null primary key); insert into deletable select generate_series(1,500); insert into condition select generate_series(1,50); WITH target AS ( select id from dele

Re: [GENERAL] Deleting 173000 records takes forever, blocks async queries for unrelated records

Hi, What Albe said about PKs. I'm also not very fond of people using text in keys (even if it's a varchar or a char field). Test the same thing with a numeric key and you are likely to see a difference. Apart from making sure your design is ok, you might want to keep this stuff well in the backgr

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

Hi again, > I understand it and for this reason I said to "use some strategy to purge > old historical data *OR* make your audit tables partitioned"... yes, prepare to scale up in any case, even if it seems to be a remote chance ATM. If the "untouched" nature of this data is so critical, you have

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

Hi Rich, if it's a strict legal requirement you may want to enforce it with a trigger system, so that each time a record is inserted/updated/deleted you create an exact copy of it in a historical table, that has the original record plus data about who performed the operation, when, from which IP,

Re: [GENERAL] Database level Replication

Hi, Slony will do that. Have a look here, any way: http://www.postgresql.org/docs/9.2/static/different-replication-solutions.html Cheers Bèrto On 28 November 2012 14:52, Ranjeet Dhumal wrote: > Hi , > > Am running near about 10 databases on one cluster , out of this i want to > replicate one d

Re: [GENERAL] Terminating a rogue connection

Hi all, in elderly versions, where pg_terminate_backend is missing, you'd issue a kill -15 from the command line. Bèrto On 27 July 2012 09:33, Chris Angelico wrote: > On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd > wrote: >> Assuming a *nix server: if a monitoring program determines that

Re: [GENERAL] Updating pg_attribute to change field's data type from integer to bigint on very large table

Hi Jeff, read carefully, he said it does stuff ON DISK, which means that all of your fields must be physically remade (so it a lot more than telling it "it's an int4, Sheila", which is what just modifying the catalog would do). Since you are not a dba be aware that you are producing a new record f

Re: [GENERAL] Searchable chess positions in a Postgress DB

> > Hi! > > As a hobby project, I am toying around with a database containing > about 5 million chess games. On average, these games have about 80 > positions (~ 40 moves by both black and white), which means there are > about 400 million chess positions in there. > What happens if you think of

Re: [GENERAL] Backups

me we are nowhere > near this causing performance issues for users? > Thanks > Rich > > > > > > On 15/03/12 12:21, Bèrto ëd Sèra wrote: > > Hi Richard, > > it's no easy answer. If your server has plenty of free resources there > won't be trouble, b

Re: [GENERAL] Backups

Hi Richard, it's no easy answer. If your server has plenty of free resources there won't be trouble, but I do have customers who cannot even imagine of launching a dump in normal traffic hours. How loaded is your box, currently? Cheerio Bèrto On 15 March 2012 12:15, Richard Harley wrote: > **

Re: [GENERAL] duplicate key errors when restoring 8.4.0 database dump into 9.1.2

Hi! > Are you sure there is just one record? What happens if you grep the backup file for "653009"? If you do have more than one such record, the quickest way out is to manually clean it. Bèrto -- == If Pac-Man had affected us as kids, we'd all be running around

Re: [GENERAL] Error while loading sql file

> > HI Ardash! > > INSERT INTO conceptnet_frame > VALUES(3884,'ja','{1}は{2}を持っている。',16,3,2140,NULL,NULL,NULL); > > Can you still access the database that produced the dump? If so, you may want to produce a number of dumps for distinct language values. Japanese, in particular, is a very compli

Re: [GENERAL] How to create database with default system locale is set to et_EE.UTF-8

Hi! > using template1 requires exclusive access to cluster. > I cannot force all users to log out while creating new db. > So using template1 is not possible. > IMHO you really misunderstood the manual. The exclusive access is NOT to the entire cluster, but to the template. Which in turn means t

Re: [GENERAL] How to create database with default system locale is set to et_EE.UTF-8

> > Is it reasonable to use commands > > export LC_COLLATE='et_EE.UTF-8' > export LC_CTYPE='et_EE.UTF-8' > > apt-get -t squeeze-backports install postgresql-9.1 postgresql-common > postgresql-contrib > Hmmm no, not really. If your problem is that the system locale is wrong for your needs, you're g

Re: [GENERAL] How to create database with default system locale is set to et_EE.UTF-8

> > Hi! > > How to force command > > CREATE DATABASE TEMPLATE = template0 > > to use et_EE.UTF-8 locale by default ? > http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html (see last comment), I haven't checked it myself as I usually have a mix of locales in my installs (of

Re: [GENERAL] Server/Data Migration Advice

Hi! > Is there a way in psql client I can search my database tables for any > BYTEA columns? > What about just grepping your dump file for 'bytea'? :) Bèrto -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and l

Re: [GENERAL] Controlling complexity in queries

Hi, > Here the result of "SELECT * FROM test1v" depends on who issued the query. As a more general case, I sometimes load parameters into a utility table, and use them to dynamically restrict the view's output. Downside: it's a multistatement operation... however, when wrapping complex queries

Re: [GENERAL] Philosophical question

Hi! > To be fair, isn't that an argument against whatever the popular > programming language is of the day? I mean, we can all remember > Matt's Scripting Archive and Perl. > If I can add a personal opinion, my main reason to avoid PHP is not related to programmers at all. There are loads o

Re: [GENERAL]

FWIW, a couple of months ago the anti-spam mail filters on our server started to reject "anything yahoo" (including loads of legit stuff, obviously). And I still see a lot of messages announcing that Ive just been subscribed to this or that yahoo discussion group. However, this is all OT, I suppose

Re: [GENERAL]

spam, it has a redirect to a spam site. On 9 December 2011 02:15, Joshua D. Drake wrote: > > On 12/08/2011 03:12 PM, Raymond O'Donnell wrote: > >> >> On 08/12/2011 23:07, sharmi...@yahoo.com wrote: >> >>> http://**happenenstappengroningen.nl/**news.html?r5h1h5

Re: [GENERAL] PostgreSQL DBA in SPAAAAAAAACE

+1 say hello to Laika, if she's still there :) Bèrto On 6 December 2011 20:33, Torello Querci wrote: > 2011/12/6 Merlin Moncure : > > On Tue, Dec 6, 2011 at 10:56 AM, Joe Miller > wrote: > >> You may have seen this, but RedGate software is sponsoring a contest > >> to send a DBA on a suborbita

Re: [GENERAL] How to retrieve rows with empty value in numeric(12,8) columns

Hi, > As I understand it, your first query returned nothing because NULL is > simply "unknown" - and since you can never know what it's equal to, > looking for equality with NULL will never find anything. > Yes, you can basically think of NULLs in SQL as having the same role of zeros in division

Re: [GENERAL] recursive inner trigger call

Hi Red, I have the slight suspicion that you have not understood what NEW and OLD mean, in the context of a trigger, am I correct? See http://www.postgresql.org/docs/9.0/static/plpgsql-trigger.html Bèrto On 1 December 2011 22:17, Red Light wrote: > > Hi Dave, > > when i use before trigger , n

Re: [GENERAL] Optimize the query, help me please.

Hi, (SELECT * FROM a limit 10) union (SELECT * FROM b limit 10) is what you need Bèrto 2011/11/23 Капралов Александр > Query is: > SELECT * FROM a UNION SELECT * FROM b ORDER BY time DESC LIMIT 100 > > how can i get only last 100 row from a and b and then do union. Explain of > select said t

Re: [GENERAL] Using KNN for objects that have more than 2 dimensions?

Hi >Whats KNN ? http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1#K-Nearest-Neighbor_Indexing At least this is my understanding of the question. I'm unable to check if it will work on an array (it should, as far as my understanding goes). Bèrto -- == If Pa

Re: [GENERAL] how to adjust auto increment id offset?

Hi On 15 November 2011 11:44, Yan Chunlu wrote: > I would like to implement two master db with even-odd id sharding. in > mysql it is fairly easy by using the configuration: > > auto_increment_offset = 1 > auto_increment_increment = 2 > > > but I have searched a lot didn't find anything relate