Heyho!
On Wednesday 25 August 2010 09.15:33 wstrzalka wrote:
> I'm currently playing with very large data import using COPY from
> file.
>
> As this can be extremely long operation (hours in my case) the nice
> feature would be some option to show operation progress - how many
> rows were already
Heyho!
On Thursday 19 August 2010 01.32:06 Benjamin Smith wrote:
> This way we can be sure that either all the databases are in synch, or
> that we need to rollback the program patch/update.
I guess this might be more a hack than a solution: do the updates in batches
and use 2pc: first connect
Heyho!
[[ logging from postgres ]]
I think you can use dblink to do this - everything you do via dblink happens
in a separate db session and so it shouldn't be affected by what happens in
your transaction.
Depending on your requirements, system resources may be an issue though
(you're opening
Heyho!
On Friday 13 August 2010 10.57:13 Daniel Migowski wrote:
> A small investigation showed to me that bzip2 compressed sql files take
> only 60% of the space of gz compressed files.
But bzip2 is very slow. I think if there should be changes to the data
compression, xz is probably the futur
Heyho!
On Friday 13 August 2010 08.52:30 Craig Ringer wrote:
[ ... ORMs ... ]
I wonder if it were worthwhile to collect information on various ORMs on the
postgres wiki. Not to duplicate the ORM's documentation, but to show the
most important highlights and pitfalls when using this or that ORM
On Wednesday 11 August 2010 07.31:24 Samantha Atkins wrote:
> There is also the interesting case of dynamic OO languages where
> technically the object fields do not have a defined type to start
> with.
I'm not sure what you want to say here. If you apply this to databases, my
answer is: if yo
Heyho!
On Wednesday 07 July 2010 13.42:59 Martin Below wrote:
> I'm facing a strange problem where the optimizer does pick the wrong
> index.
What's not clear to me is why you want pg to chose a particular index. Are
you having performance issues? (The case you show is a table with 200k rows
-
Heyho!
On Sunday 27 June 2010 08.22:09 RP Khare wrote:
> I downloaded PostgreSQL plus advanced server from EnterpriseDB website.
> Should I go with the original community PGSQL edition or EnterpriseDB
> edition?
If you work on a Linux/BSD/... OS distribution with a sane package manager,
I always
Heyho!
On Friday 25 June 2010 00.41:08 Wang, Mary Y wrote:
> I personally think it's not possible to convert even a simple database
> from MySQL to Postgres in less than 40 hours.
The problem is not the database, the problem is the application.
Converting the database takes a few hours at most (
On Tuesday 22 June 2010 10.49:00 Dimitri Fontaine wrote:
> > I estimate the table will hold around 5,000 rows, never any more.
>
> It could be that you're better off without any index, depending on the
> size of rows you put in there, and the overall memory usage patterns you
> have.
I agree: d
On Sunday 20 June 2010 12.52:22 Thomas Kellerer wrote:
> I don't believe in "database agnostic code".
Using a db abstraction may be the right way to write "database agnostic
code".
I have quite a good impression of SQLAlchemy, for example: it is quite
generic, and it is still possible to write
Hi,
On Wednesday 16 June 2010 18.42:25 sunpeng wrote:
> hi,i write a function in postgresql source code, how to register this
> function?
> it is not an aggregate function.
> i don't use 34.3"User-Defined Functions" described in
> http://www.postgresql.org/docs/8.4/interactive/xfunc.html, i just w
Heyho!
On Wednesday 16 June 2010 00.56:14 Adrian Klaver wrote:
> > My question is: why do I get information about too long value before
> > trigger fires?
> > Can I change this behavior?
I firmly feel friendly error messages like this firmly beong into the
application and not into the DB. Next
[continuous backup]
On Tuesday 15 June 2010 21.42:52 Oliver Kohll - Mailing Lists wrote:
> 1) Continuously ship the WAL records to somewhere on the test server
> unknown to Postgres but run the test machine as a normal database
> completely separately. If a backup is needed, delete the test databa
On Tuesday 15 June 2010 18.56:46 Craig Ringer wrote:
[IMMUTABLE columns]
> Column privs may be bypassed by a superuser
To be fair, constraints can be removed via ALTER TABLE, so this is not an
argument.
For me, the compelling reason to propose this is that it's much more
readable than either
On Tuesday 15 June 2010 08.03:49 Craig Ringer wrote:
> AFAIK, at this point only FOREIGN KEY constraints may be deferred.
I think you didn't understand what I wrote. 9.0 allows to defer UNIQUE as
well, but not NOT NULL, which is why I wrote a derred constraint trigger to
implement it, which be
Heyho!
(Ok, seems to be feature wish day ...)
I was wondering if others would find an IMMUTABLE (or whatever) column
constraint useful as well. Semantics would (obviously?) be to disallow
changing the value of this column after insert.
I realize that this is possible via triggers, and with th
Heyho!
I was trying to implement a deferred NOT NULL constraint using a deferred
constraint trigger (on update and insert of this row) because some values
would be filled in later during the transaction, after the initial part of
the record has been filled.
I asked myself if a type of deferred
On Friday 11 June 2010 09.27:15 Michal Politowski wrote:
[email address local part is case sensitive]
> In practice I've yet to see a system having both smith and Smith
> and them being different, but still it is theoretically posible.
I routinely modify email addresses I store to my addressbook
Heyho!
On Thursday 10 June 2010 22.50:23 Mike Christensen wrote:
> 2) Every time the user updates or saves their email, store it in
> lowercase, and every time I lookup an email, pass in a lowercase
> email.
I'd do it this way. Plus either a CHECK condition on the table (email =
lowercase(email
Heyho!
On Thursday 10 June 2010 23.49:58 Aleksey Tsalolikhin wrote:
> 3. We ARE disk bound. Disk utilization goes to 94% during
> the dump. There is an order of magnitude more reads than
> writes.
Just speculation, I've not tried this. Perhaps pipe the output of pg_dump
through a software tha
On Thursday 03 June 2010 20.03:19 J. Bagg wrote:
> because I tend to skim
> over the PostgreSQL related ones, assuming they're connected with the
> ready-built version.
I'm just curious: why are you compiling your own?
If you want to stick with lenny and need 8.4: It's in backports.org (package
On Monday 31 May 2010 10.29:22 Michal Szymanski wrote:
> for Debian sometimes we had to wait many weeks for
> official packages.
FWIW: I've just noticed that Debian experimental carries pg 9.0beta1, it was
uploaded 3.5., so thats not bad, taking into account that it was released
only a few days
On Wednesday 02 June 2010 13.46:47 Stephen Frost wrote:
> * raghavendra t (raagavendra@gmail.com) wrote:
> > How do i get the client time and server time. I am connecting remotely.
> > If i give SELECT CURRENT_TIME;,it shows the server time. How do we get
> > the client time ?
>
> uh, \! date
On Wednesday 02 June 2010 07.51:50 Bret S. Lambert wrote:
> Getting the wrong answer fast is not an
> improvement over the right answer slow.
Doesn't match reality. Listened to any politicians lately?
(sorry, couldn't resist.)
-- vbi
--
In seiner mit Hochspannung erwarteten Rede zur Lage der
Heyho!
On Tuesday 01 June 2010 06.01:02 Greg Smith wrote:
> Put a little time into learning how to build
> your own packages instead, to work around this one perceived flaw, and
> you'll be way ahead of the mess that comes with switching distributions
> altogether.
Note that we can always use
On Tuesday 01 June 2010 03.08:06 Clemens Schwaighofer wrote:
> Besides the fact that new versions come in quite fast (after the wait
> phase from unstable to testing)
... and you can always mix testing and unstable. If your testing
installation is not too old, usually not much fiddling with depe
Heyho!
On Monday 31 May 2010 04.18:04 Dennis Gearon wrote:
> One thing, can prepared statements be done, including the 'execute',
> inside of a transaction, and what are the side effects?
Semantically, the statement is exactly like a "not prepared" statement: it
happens at execute time, the rest
On Thursday 13 May 2010 17.50:31 Wang, Mary Y wrote:
> http://www.postgresql.org/docs/8.3/static/backup-dump.html#BACKUP-DUMP-A
> LL (I only found the documentation for 8.3.10), and it looks like
> pg_dumpall > outfile is the best choice.
pg_dumpall or by-database pg_dump ha the advantage that it
On Tuesday 27 April 2010 19.12:31 Steve Atkins wrote:
[...]
> BIRT
[...]
FWIW, my cow-orkers are quite happy with BIRT (especially with the designer
environment in Eclipse) after having used Crystal and Actuate previously.
I've no idea how BIRT compares with the other OSS tools, but compared
On Tuesday 27 April 2010 11.17:42 Cédric Villemain wrote:
> > Anyone had this kind of design problem and how did you solve it?
>
> store your files in a filesystem, and keep the path to the file (plus
> metadata, acl, etc...) in database.
... and be careful that db and file storage do not go out
On Thursday 22 April 2010 23.36:51 Bjørn T Johansen wrote:
> E.g I have two fields in a table that I want indexed, is it best to
> create one index combining the two fields or creating one for each
> field?
This depends on the queries you run against the table. It's not possible to
give a genera
On Friday 23 April 2010 03.27:29 Craig Ringer wrote:
> insert into test (a,b) values ('fred',NULL);
> insert into test (a,b) values ('fred',NULL);
>
>
> ... and will succeed:
Hmm. Perhaps not as ugly as "none" placeholders:
create unique index on test (b) where a is null;
create unique index o
On Tuesday 20 April 2010 19.53:34 Glus Xof wrote:
> Could I use the \set statements
\set is a feature of the "psql" commandline frontend. These values are
never even seen by the database and are not preserved anywhere. Also, if
you develop applications, you'll usually not use the psql frontend
Hi,
On Tuesday 20 April 2010 15.50:35 Glus Xof wrote:
> I know that can use one-row tables but,
>
> I'd like to know if it's possible to define variables attached to one
> specific database,
Yes, one row tables :-)
What I usually do for those random values is a table like "GlobalParameters"
On Thursday 15 April 2010 15.56:20 Jan Krcmar wrote:
> i'm doing one big insert per day, and one big delete per day
>
> anyway, i've found, this article
> http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html
>
> could the partitioning be helpfull for this situation?
Yes, I'm quit
On Wednesday 14 April 2010 16.01:39 Jan Krcmar wrote:
> the documentation says, one should run VACUUM if there are many
> changes in the database, but the vacuumdb never finishes sooner than
> the new data should be imported.
>
> is there any technique that can solve this problem?
-> vacuum can
On Wednesday 24 March 2010 17.28:37 Tony Webb wrote:
> #revoke create on schema public from public;
> REVOKE
Note that this will *only* revoke the create right from "PUBLIC" and not
from any individual role that may have the right. PostgreSQL unfortunately
does not have a "revoke ... from ALL".
On Sunday 21 March 2010 02.01:27 Scott Mead wrote:
> On Sat, Mar 20, 2010 at 5:24 PM, Adam Seering wrote:
> > Hi,
> >
> >I'm trying to set up an internal general-purpose PostgreSQL
> >server
> >
> > installation. I want most users with login access to the server to be
> > able t
On Sunday 21 March 2010 21.11:56 Lew wrote:
> In at least some jurisdictions, if one party to a contract writes the
> language without input or emendation from the other party, that allows
> the other party to impose any reasonable interpretation on the wording.
> IOW, ambiguity is resolved in fa
Hyho!
On Saturday 13 March 2010 13.48:14 Manlio Perillo wrote:
> Usually when I need to restore a database to its initial state, what I
> do is to simply drop it, and then re-create it.
>
> However on a shared hosting this is not possible.
Create a schema, modify your default search path so tha
Heyho!
As far as I understand, REVOKE .. FROM PUBLIC revokes rights that were given
to everybody. Is there a way to revoke rights given to *any*body? (all
users)? Or do I misunderstand PUBLIC?
cheers
-- vbi
--
featured product: ClamAV Antivirus - http://www.clamav.net/
signature.asc
Desc
Hi,
The documentation about DROP COLUMN is a bit unclear:
| The DROP COLUMN form does not physically remove the column, but simply
| makes it invisible to SQL operations. Subsequent insert and update
| operations in the table will store a null value for the column. Thus,
| dropping a column is qu
On Sunday 07 March 2010 02.49:29 Bruce Momjian wrote:
> Manlio Perillo wrote:
> > I think the following behaviour is not intuitive:
> >
> > manlio=> DROP TABLE IF EXISTS foo.bar;
> > ERROR: schema "foo" does not exist
> >
> > The statement should not fail if the schema does not exist
>
> Hmm.
Heyho!
On Friday 05 March 2010 20.18:46 Greg Smith wrote:
> The short version is that ext3 combined with regular hard drives has
> never been safe for database use by default, [...]
> The change in ext4 [...] eliminating the source for that cheat.
Tangentially related: how is the behaviour [1]
On Friday 05 March 2010 02.27:39 Thomas wrote:
> sigh,I didn't find a book with enough internal topics.
>
I found the official documentation very good, for everything else ask here
or (for the gory details) on -hackers. Or, of course, read the source,
Luke.
cheers
-- vbi
--
The following exp
Heyho!
Disclaimer: I don't have much experience with big databases, so this is
based on my understanding of the theory.
On Thursday 04 March 2010 20.32:46 Roger Tannous wrote:
> I have a database table that is growing too big (few hundred million
> rows) that needs to be optimized, but before
Heyho!
On Monday 01 March 2010 17.04:46 Tom Lane wrote:
table
> > values ( ts timestamp, source integer, value float );
> > [...] partial index on "(source, ts) where ts > '2009-01-01'"?
> The planner is reasonably smart about deductions involving combinations
> of btree-indexable operators. It
Hi Terry,
On Sunday 28 February 2010 22.56:41 Terry wrote:
> I am looking for a way to copy all the data from one table to another
> on a regular basis, every 5 minutes let's say.
>
> INSERT INTO table2 SELECT * FROM table1;
Why do you want this? Is it necessary for the data in table2 to appear
Heyho!
Given a (big [1]) table
values ( ts timestamp, source integer, value float );
[under what conditions] will the opitmizer be smart enough to make use of a
partial index on "(source, ts) where ts > '2009-01-01'"? (Queries will have
a date restriction but not necessarily the exact "> 2009
Heyho!
I'm writing a little toy project, part of it involves scripts that pre-
process sql and then feed it to psql.
Is it possible to set the input filename / line number in psql?
(Similar to how cpp includes this information so that the compiler can
report the filename/line number of the C fi
On Friday 29 January 2010 12.51:20 Joe Kramer wrote:
> So this means random()+random() is not random even within 2,000,000
> iterations!
>
Exactly the issue I wrote about: random() apparently doesn't deliver enough
randomness.
Even if it did: quote_literal(random() + random()) is ca. 14 to 16
On Friday 29 January 2010 11.21:00 Joe Kramer wrote:
> We have bunch of servers running the app and rebuilding postgres with
> support for ossp_uuid on all servers is time consuming.
> Is there a way of doing it without third party dependency like
> ossp_uuid? Should I just run md5(random number),
Hi,
On Friday 29 January 2010 09.20:33 Joe Kramer wrote:
> I need to generate unique id which is not guessable unlike
> serial(integer) type. I need an id in format like md5 hash of random
> number.
> On top of that I need this id to be unique across multiple tables.
Have a look at http://www.po
On Monday 25 January 2010 08.25:30 John R Pierce wrote:
> > My question is, which is more efficient? Performance-wise, does it
> > matter whether unchanged fields are included or omitted on UPDATE
> > statements
>
> my first order guess is, sending and having to parse the additional
> unchanged f
On Thursday 21 January 2010 13.01:29 Magnus Hagander wrote:
> > Really? With ONLY 2Gb? Why? What is the performance improvement, with
> > 64Bit all pointers and so on needs more memory so i'm expecting lesser
> > memory for the data.
>
I'm not in any way a performance expert, but IIRC 32 bit Linu
On Wednesday 20 January 2010 15.42:14 Ivan Sergio Borgonovo wrote:
> I'd also appreciate some suggestion about dev environment and best
> practices on Debian, something that could help me to compile,
> install, test easily on Debian.
(Disclaimer: Haven't done any postgres related programming so fa
Hi,
On Wednesday 20 January 2010 11.57:37 Vincenzo Romano wrote:
> 2010/1/20 Adrian von Bidder :
> > [ creating db partitions on demand ]
> >
> > On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
> >> In case 1 I need to inspect the catalog with at least
Hi,
[ creating db partitions on demand ]
On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
> In case 1 I need to inspect the catalog with at least a select, while
> in case 2 I need to trap errors.
> In my (little) experience trapping errors is slow, so I would go for
> option 1.
Tra
On Friday 08 January 2010 11.28:15 Ivan Sergio Borgonovo wrote:
> It would be enough just knowing which part of the file is being
> restored (without causing too much extra IO, that will definitively
> put my notebook on its knee).
Highly platform dependent, but has helped me a lot recently with v
Heyho!
On Wednesday 06 January 2010 18.26:05 Joshua D. Drake wrote:
...
> > With this in mind: what can we tune to minimize diskspace (RAM-disk
> > space) usage?
[thanks to all for your input!]
> Wow, here are some things but honestly Pg may not be the DB you are
> looking for.
Well, parts of
Howdy!
I'm currently in a MySQL -> PostgreSQL migration project (Go, go, go, ...
shall I cc: slashdot, too? ;-)
Part of this is in embedded context, where a (diskless) embedded computer
runs from flash. Since we don't want to stress the flash too much, the db
is actually loaded from a dump at
On Saturday 07 July 2007 14.16:56 Gabriele wrote:
> I know this is a delicate topic which must be approached cautiously.
>
> Let's have a server which feed data to multiple slaves, usually using
> direct online connections. Now, we may want to allow those client to
> sync the data to a local replic
On Saturday 07 July 2007 11.34:04 Евгений Кононов wrote:
> Hello !
>
> How to force POSTGRES to use all virtual processors at included
> Hyper-Trading ?
If your operating system is able to schedule the threads/processes across
CPUs, PostgreSQL will use them. Often, the limit is disk, not CPU, s
Hi,
I want to tighten down my db schema as much as possible against accidential
corruption. For this, I'd like to have fields that can only inserted and
not later changed (think some sort of "id", account names, etc., which are
often not only stored in the database but also in external places
Hi,
Is there any way to do tree traversal with only one SQL query (no
procedure/function)?
CREATE TABLE foo (
node INTEGER,
parent INTEGER REFERENCES foo(node)
);
Ideally the output would be a depth-first tree traversal starting at root
(marked by parent == node, for example.)
Obviously,
66 matches
Mail list logo
