On Fri, Feb 02, 2007 at 12:00:27PM -0500, Merlin Moncure wrote:
> On 2/1/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> >Have you thought about something like ~
> >'(first_string|second_string| third_string)'? Obviously your
> >example would be more complex, but I believe that with careful
> >crafting,
I think you need delete, update, and insert rules for warehouse.
Subtract out the old on delete & update. Add the new in update and
insert. Aggregates would be an incredible waste of processor time.
pgsql-user wrote:
Hi,
I got stuck with the following problem and strangely couldn't find
a
On Fri, Feb 02, 2007 at 17:18:39 +0100,
Alban Hertroys <[EMAIL PROTECTED]> wrote:
>
> You can do this:
> INSERT INTO tbl_email (option_public, agency, id)
> SELECT $1, $2, MAX(id) + 1
> FROM xyz;
>
> I just realize you don't so much need a lock, you need a serialized
> transaction. I ca
Hi,
I got stuck with the following problem and strangely couldn't find
anything similar in the list:
Assume two tables:
table: warehouse, columns: id, incoming, outgoing
table: articles, columns: id, articles_in_stock
All I want is to create a rule, that everytime when articles leave or
get in t
Postgres 8.1
Sample test table:
(all queries done on fresh vacuumed analyzed table with statistics on
rub_id and news_dtime set to 1000)
(all table in memory and server do not doing anything other)
media=> \d test_table
Table "public.test_table"
Column |Type
Magnus Hagander wrote:
>>> Obviously edb
>>> doesn't feel the need, since Dave's not been ordered to :-)
>> I'm left to my own devices as far as community work is concerned. Which
>> is nice :-)
>
> Well, if edb had customers *using* it, I'm sure they would tell you to
> do it even if you didn't w
Dave Page wrote:
> Magnus Hagander wrote:
>> Dave Page wrote:
>>> Magnus Hagander wrote:
> I do not intend to rewrite this code (which has been in pgAdmin for
> years) on the basis of a single complaint from the author of an
> equivalent, but commercial tool. If you do not like the effe
Magnus Hagander wrote:
> Dave Page wrote:
>> Magnus Hagander wrote:
I do not intend to rewrite this code (which has been in pgAdmin for
years) on the basis of a single complaint from the author of an
equivalent, but commercial tool. If you do not like the effects you see
when a
>> Obviously edb
>> doesn't feel the need, since Dave's not been ordered to :-)
>
> I'm left to my own devices as far as community work is concerned. Which
> is nice :-)
Well, if edb had customers *using* it, I'm sure they would tell you to
do it even if you didn't want to :-)
//Magnus
Magnus Hagander wrote:
> Richard Huxton wrote:
>> Dave Page wrote:
>>> Also, three just seems like a sensible number to maintain. I kinda
>>> like Magnus' idea to put older releases into a sort of 'retired' mode
>>> though, and build only the binaries for PostgreSQL itself.
>> The other option woul
Magnus Hagander wrote:
> Richard Huxton wrote:
>> Dave Page wrote:
>>> Also, three just seems like a sensible number to maintain. I kinda
>>> like Magnus' idea to put older releases into a sort of 'retired' mode
>>> though, and build only the binaries for PostgreSQL itself.
>> The other option woul
At 09:36 AM 2/2/2007, Ron Johnson wrote:
>
> OTOH, I still take a full base backup every night and keep ten days
> worth of WAL files on our backup server, so I guess maybe I don't
> *completely* trust it :-)
Or you don't trust tape to be 100% reliable.
Well so far tapes get chewed up by drive
Dave Page wrote:
> Magnus Hagander wrote:
>>> I do not intend to rewrite this code (which has been in pgAdmin for
>>> years) on the basis of a single complaint from the author of an
>>> equivalent, but commercial tool. If you do not like the effects you see
>>> when a user has a pgpass file, you sh
Magnus Hagander wrote:
>> I do not intend to rewrite this code (which has been in pgAdmin for
>> years) on the basis of a single complaint from the author of an
>> equivalent, but commercial tool. If you do not like the effects you see
>> when a user has a pgpass file, you should warn them in your
>>> Nowadays I also wonder about the restoration times of say 200GB or even
>>> TBs of data from backups. More fun if there are Very Important and
>>> Influential People popping in every 15 minutes to ask whether it's done
>>> yet.
>
> That's a problem with pg. pg_dump is single-threaded and can
Richard Huxton wrote:
> Dave Page wrote:
>>
>> Also, three just seems like a sensible number to maintain. I kinda
>> like Magnus' idea to put older releases into a sort of 'retired' mode
>> though, and build only the binaries for PostgreSQL itself.
>
> The other option would be for one or more Win
> I do not intend to rewrite this code (which has been in pgAdmin for
> years) on the basis of a single complaint from the author of an
> equivalent, but commercial tool. If you do not like the effects you see
> when a user has a pgpass file, you should warn them in your own code,
> not expect us t
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/02/07 12:07, Lincoln Yeoh wrote:
> At 09:36 AM 2/2/2007, Ron Johnson wrote:
>> >
>> > OTOH, I still take a full base backup every night and keep ten days
>> > worth of WAL files on our backup server, so I guess maybe I don't
>> > *completely* tru
Dave Page wrote:
Also, three just seems like a sensible number to maintain. I kinda like
Magnus' idea to put older releases into a sort of 'retired' mode though,
and build only the binaries for PostgreSQL itself.
The other option would be for one or more Windows users to step forward
and sa
On Fri, Feb 02, 2007 at 11:27:38PM +0530, Rohit Prakash Khare wrote:
> If I have a backup of pgsql database and I take this dump file to
> another machine where postgresql is freshly installed with standard
> settings. Will I be able to restore the database?
How did you backup? pg_dump? Sure, ou
>>> Right. and my original point in starting this thread is that it would
>>> be valuable to the community if all this information were gathered up
>>> and documented somewhere.
>> Go for it! :)
>
> I suppose I should have seen that coming ... :)
Yes I believe that would have been pretty obviou
If I have a backup of pgsql database and I take this dump file to another
machine where postgresql is freshly installed with standard settings. Will I be
able to restore the database?
Have you checked out the new-look www.indiatimes.com yet?
---(end of broadcast)--
On 2/1/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
Have you thought about something like ~ '(first_string|second_string|
third_string)'? Obviously your example would be more complex, but I
believe that with careful crafting, you can get regex to do a lot
without resorting to multiple passes.
that
In response to "Joshua D. Drake" <[EMAIL PROTECTED]>:
>
> >> But it's also important to point out that a number of community
> >> members are on the hook to support old versions due to their day
> >> jobs; with Tom/Red Hat/7.3 (or is it 7.4?) probably being the best
> >> example. IIRC Sun's
Andrew Sullivan wrote:
> On Wed, Jan 31, 2007 at 06:19:33AM -0800, Rich Shepard wrote:
>> There is also ZohoCRM, which _is_ postgres based, but comes with version
>> 8.0.something and no ability to use what we already have installed. That's
>> another poor development decision. Why would I -- or
>> But it's also important to point out that a number of community
>> members are on the hook to support old versions due to their day
>> jobs; with Tom/Red Hat/7.3 (or is it 7.4?) probably being the best
>> example. IIRC Sun's support policy is 5 years, so presumably someone
>> will have
On Tue, Jan 30, 2007 at 11:36:52AM -0800, Rich Shepard wrote:
> However, what puzzles me is this statement: "PostgreSQL has continued to
> fall behind other database engines in both performance and features, so I
> don't see compelling reason to work on it in my very limited free time."
While th
On Wed, Jan 31, 2007 at 06:19:33AM -0800, Rich Shepard wrote:
> There is also ZohoCRM, which _is_ postgres based, but comes with version
> 8.0.something and no ability to use what we already have installed. That's
> another poor development decision. Why would I -- or anyone else -- want to
> int
> So - since there is at least one user who's suprised by this behaviour,
> we probably need two things:
> 1. A message that tells users the password is being remembered for
> *them* rather than for pgAdmin.
Well that is up to the pgAdmin team but I would agree that if pgAdmin is
going to continu
In response to Jim Nasby <[EMAIL PROTECTED]>:
> On Jan 27, 2007, at 3:41 AM, Dave Page wrote:
> >> Does the PostgreSQL project have any similar policy about EoLs?
> >> Even just
> >> a simple statement like, "it is our goal to support major branches
> >> for 2
> >> years after release" or some
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/01/07 21:14, Jim Nasby wrote:
> On Jan 27, 2007, at 10:45 AM, Ron Johnson wrote:
>> Using slony or "piped pg_dump" requires that you have *double* the
>> amount of disk space. Having a *very large* database and double
>> capacity of SCSI disks (
On Feb 1, 2007, at 12:09 PM, Harpreet Dhaliwal wrote:
For inserting the id, i need to query a table xyz, fetch the
maximum id in it, increment it by 1 and store it in tbl_email.
Right after BEGIN in my function I have a commnet where in I need
to query the xyz table, fetch the max id and store
On Jan 25, 2007, at 11:46 AM, Kai Schlamp wrote:
What is the best way to find out the relationship of two columns in a
table?
I want to know if it is 1:1, 1:N, or M:N. Is there an easy way to do
this with a SQL statement?
If both tables have the same rowcount, and SELECT count(*) FROM a
JOIN
On Jan 29, 2007, at 3:14 PM, [EMAIL PROTECTED] wrote:
Never mind.
I found "vacuum_cost_delay" in the docs, I had it set to 70. I set it
to 0 and watched CPU and I/O% peg to 100%.
FWIW, my experience is that if you're going to use that, a number
between 10 and 20 is usually best.
--
Jim Nasb
Is there no way to change the index code to allow for moving index
tuples from one page to another? If we could do that then presumably
we could free up substantially more pages.
On Jan 30, 2007, at 10:18 PM, Bruce Momjian wrote:
Added to TODO:
* Add REINDEX CONCURRENTLY, like CRE
On Jan 27, 2007, at 3:41 AM, Dave Page wrote:
Does the PostgreSQL project have any similar policy about EoLs?
Even just
a simple statement like, "it is our goal to support major branches
for 2
years after release" or some such?
I've been considering only maintaining the current and previou
On Jan 27, 2007, at 8:57 PM, Timasmith wrote:
What query can I run to get the comments for my table columns.
i.e. the ones on my 8.1 database added with this command:
COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the
addresses table';
There's a function that allows you to d
On Jan 27, 2007, at 10:45 AM, Ron Johnson wrote:
Using slony or "piped pg_dump" requires that you have *double* the
amount of disk space. Having a *very large* database and double
capacity of SCSI disks (including storage controllers, shelves, etc,
etc) is expensive, and might not be available.
On Jan 25, 2007, at 10:30 AM, Inoqulath wrote:
CREATE TABLE foo(
id serial,
a_name text,
CONSTRAINT un_name UNIQUE (a_name));
Obviously, inserting a string twice results in an error (as one
would expect). But: is there any known possibility to ingnore an
errorneous INSERT like SQLite's "conf
On Jan 26, 2007, at 9:06 AM, Merlin Moncure wrote:
I am receiving a large (300k+_ document from an external agent and
need to reduce a few interesting bits of data out of the document on
an insert trigger into separate fields.
regex seems one way to handle this but is there any way to avoid
resc
Tony Caduto wrote:
> Dave Page wrote:
> >
> > What you are saying is that because you don't believe in the pgpass
> > design, you are going to summarily delete them - which I know for
> > absolute sure would *really* annoy some pgAdmin users that I know for
> > a fact have a whole heap of passwo
Tom Lane wrote:
> Richard Huxton writes:
>> So - since there is at least one user who's suprised by this behaviour,
>> we probably need two things:
>> 1. A message that tells users the password is being remembered for
>> *them* rather than for pgAdmin.
>> 2. A simple way to allow an application
On Thu, Feb 01, 2007 at 10:10:57AM -0600, Hiltibidal, Robert wrote:
>
> >Also, I see you're using the IBM "make". Don't do that. You need to
> >use gmake.
>
> # make -v
> GNU Make 3.80
> Copyright (C) 2002 Free Software Foundation, Inc.
> This is free software; see the source for copying con
Richard Huxton writes:
> So - since there is at least one user who's suprised by this behaviour,
> we probably need two things:
> 1. A message that tells users the password is being remembered for
> *them* rather than for pgAdmin.
> 2. A simple way to allow an application to act independently of
Harpreet Dhaliwal wrote:
> About the concurrency control, if i have both Select Max(id) and insert
> (id)
You know, if you don't top-post you don't need to tell me what you're
talking about ;)
> in the same function, then would it be
> a nice idea to put both these statements in the same function
Tony Caduto wrote:
> Dave Page wrote:
>>
>> What you are saying is that because you don't believe in the pgpass
>> design, you are going to summarily delete them - which I know for
>> absolute sure would *really* annoy some pgAdmin users that I know for
>> a fact have a whole heap of passwords stor
>> Leave the use of pgpass to the user for use with command line tools.
>>
>> I am not being unreasonable here. What I have brought to your attention
>> is a valid concern that NEEDS to be addressed.
>> In the past you have asked me to make changes to my website and I always
>> tried to accommodat
Tony Caduto wrote:
If pgAdmin III stored it's own passwords in the registry it would be up
to the user (as it should be) to use pgpass.
If they chose to use pgpass, libpq would override the passwords stored
in the registry anyway, which is what pgAdmin III is doing
automatically to my applicati
Tony Caduto <[EMAIL PROTECTED]> writes:
> You are correct on that fact Dave, however it does force them to store
> the password in a pgpass file without their knowledge.
This seems about on par with arguing that pgAdmin forces users to store
data in a database without their knowledge. Worse yet,
Tony Caduto wrote:
> Dave Page wrote:
>>
>> Actually, no, it's using it *exactly as it's documented* - which is
>> not unlike any other win32 app. For example, if you use the IE ActiveX
>> control to display web pages in your app, it will remember passwords
>> you save there for reuse in Internet E
Peter <[EMAIL PROTECTED]> writes:
>> Try SELECT count(*) FROM ... instead and see if the times are closer.
> Correct. That executes in 300ms flat:
So your problem is data transfer, not the query per se.
How old a pgAdmin are you using?
regards, tom lane
>> I'm not sure if I'm heading up the right alley - seems too simple!,
but here is my issue.
>>
>> I have about 3000 records in 'mytable', and simple
>
>Number of rows is not the most important thing here - the number of
>occupied disc pages is (you can have a lot of small rows or a small
>nub
Thanks Tom.
That did it.
Theirs always something to learn, when i read you.
Best regards
Hakan
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 02, 2007 4:04 PM
> To: Hakan Kocaman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Fu
I am wondering if anybody can suggest a good sql formatting tool. I
searched for the obvious things on google and can't find a good
solution. The requirements are:
* linux version strongly preffered
* pl/pgsql preffered, but not required
* must be able to be run from script (bash, etc)
* must be
RPK wrote:
How is FireBird rated when compared with PostgreSQL?
I used to be a big time user of Firebird, but then I discovered
PostgreSQL and have never looked back.
Firebird has only a few built in functions even the simple ones are
missing. To get the built in
functionality of PostgreSQL'
About the concurrency control, if i have both Select Max(id) and insert (id)
in the same function, then would it be
a nice idea to put both these statements in the same function or differenct
functions and then put the insert in a transaction and lock the table for
any further query till insert co
Dave Page wrote:
What you are saying is that because you don't believe in the pgpass
design, you are going to summarily delete them - which I know for
absolute sure would *really* annoy some pgAdmin users that I know for
a fact have a whole heap of passwords stored in theirs. Doing that
woul
Dave Page wrote:
pgAdmin doesn't force them to store any passwords at all. That's pure
FUD.
You are correct on that fact Dave, however it does force them to store
the password in a pgpass file without their knowledge.
When you check that box "Store Password" it is not warning the user that
"Hakan Kocaman" <[EMAIL PROTECTED]> writes:
>> Try this way instead:
>> select * from public.check_notice(2,'hello');
> so i call the function now like this (obfuscated):
> select
> public.check_notice(t1.a,t1.b,t2.c)
> from
> public.tab1 t1,
> public.tab2 t2
> I'm not clear h
On Feb 2, 2007, at 04:48 , Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/01/07 21:31, RPK wrote:
How is FireBird rated when compared with PostgreSQL?
Rated?
Clearly, Firebird is a 2.0 and PostgreSQL is an easy 8.2.
Alexander.
---(end of b
2007/2/2, George Weaver <[EMAIL PROTECTED]>:
And the .bat file script?
The .bat script is of no importance: it's a windows-related problem
(or, to be more precise, the problem of my ignorance when it comes to
windows scripting). The most trivial .bat scripts containing only a
simple echo state
Query returned successfully with no result in 600 ms.
Why SELECT takes 3+ second to execute? Is it something to do with my
Postgres server optimization, or PgAdmin does not show correct data
retrieval runtime (leaks over into query runtime or something)?
Data transfer and display time usually
Actually in theory it is possible to completely secure a database by
putting all your business logic in stored procedures/functions and
allowing only raw database access to administrators. Plenty of people
do this. In fact if I were designing something that had lots of users
who had relativel
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/02/07 07:22, Richard Huxton wrote:
> Peter wrote:
>>
>> Query returned successfully with no result in 600 ms.
>>
>> Why SELECT takes 3+ second to execute? Is it something to do with my
>> Postgres server optimization, or PgAdmin does not show cor
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/02/07 06:01, Tomi N/A wrote:
> I'm trying to set up an automatic backup mechanism and have a number
> of questions about issues I've encountered.
> First of all, I tried to install pgAgent (the server is running win2k3
> and pgsql 8.1.5). Trying
Richard Huxton wrote:
Peter wrote:
Query returned successfully with no result in 600 ms.
Why SELECT takes 3+ second to execute? Is it something to do with my
Postgres server optimization, or PgAdmin does not show correct data
retrieval runtime (leaks over into query runtime or something)?
<[EMAIL PROTECTED]> wrote:
This is a special case of (2,2) secret sharing: http://
en.wikipedia.org/wiki/Secret_sharing Here the secret is the actual
password, a+b, shared into two parts, a and b. The above scheme
suffers from the problem that the user now knows quite a lot about
the secre
From: "Tomi N/A"
> At this point, I decided to try a much more primitive approach: using
> a windows scheduled task running a .bat or .cmd script which in turn
> calls pg_dump.
It might be helpful if you provided the exact pg_dump command you are
trying
to use, as well as the text of the .ba
Tomi N/A wrote:
2007/2/2, George Weaver <[EMAIL PROTECTED]>:
From: "Tomi N/A"
> At this point, I decided to try a much more primitive approach: using
> a windows scheduled task running a .bat or .cmd script which in turn
> calls pg_dump.
It might be helpful if you provided the exact pg_dump c
Peter wrote:
Query returned successfully with no result in 600 ms.
Why SELECT takes 3+ second to execute? Is it something to do with my
Postgres server optimization, or PgAdmin does not show correct data
retrieval runtime (leaks over into query runtime or something)?
Data transfer and displ
Wilton Wonrath wrote:
Is it possible to do a backup only with index ?
You could backup the whole schema and then restore only the schema. See
the --use-list option on pg_restore.
Is it possible to do a backup only with users, login roles or group roles ?
You can backup the roles with "pg
I'm not sure if I'm heading up the right alley - seems too simple!, but
here is my issue.
I have about 3000 records in 'mytable', and simple
select * from mytable where x=1
is timed as:
Total query runtime: 2933 ms.
Data retrieval runtime: 791 ms.
EXPLAIN says it's Seq Scan, but the actual f
2007/2/2, George Weaver <[EMAIL PROTECTED]>:
From: "Tomi N/A"
> At this point, I decided to try a much more primitive approach: using
> a windows scheduled task running a .bat or .cmd script which in turn
> calls pg_dump.
It might be helpful if you provided the exact pg_dump command you are tr
Hi.
Is it possible to do a backup only with index ?
Is it possible to do a backup only with users, login roles or group roles ?
regards,
Wilton Ruffato Wonrath
[EMAIL PROTECTED]
__
Fale com seus amigos de graça com o novo Yahoo! Messen
From: "Tomi N/A"
At this point, I decided to try a much more primitive approach: using
a windows scheduled task running a .bat or .cmd script which in turn
calls pg_dump.
It might be helpful if you provided the exact pg_dump command you are trying
to use, as well as the text of the .bat file
I'm trying to set up an automatic backup mechanism and have a number
of questions about issues I've encountered.
First of all, I tried to install pgAgent (the server is running win2k3
and pgsql 8.1.5). Trying to start the pgagent service results in an
"error 193", one google knows very little abou
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 5:26 PM
> To: Hakan Kocaman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Functions, composite types and Notice
>
>
> "Hakan Kocaman" <[EMAIL PROTECTED]> writes:
> > If you
roopa perumalraja wrote:
Thanks a lot for your reply. To make it more clear will the be no
loss of data or data corruption when taking a base backup while there
is inserts & updates happening in the database?
Updates to the database continue uninterrupted.
The base backup alone is *not* enough
Harpreet Dhaliwal wrote:
> I have a function like the follwoing:
>
> CREATE OR REPLACE FUNCTION sp_insert_raw_email(bool, text, text, text,
> int4,text,text,text,text,text,text,text,timestamp)
> RETURNS void AS
> $BODY$
> BEGIN
> -- SELECT STATEMENT GOES HERE--
> INSERT INTO tbl_email(option_publ
Tony Caduto wrote:
Dave Page wrote:
I am sorry, but I believe the way pgAdmin III is using the pgpass file
is TOTALLY WRONG, sorry but it just is.
Actually, no, it's using it *exactly as it's documented* - which is not
unlike any other win32 app. For example, if you use the IE ActiveX
cont
On Fri, Feb 02, 2007 at 07:20:04 +0900,
Paul Lambert <[EMAIL PROTECTED]> wrote:
> How?
Use a debugger.
> If it is encrypted within the source code then the only way to steal the
> credentials would be to reverse engineer the application. And if someone
> is going to do that then you can be re
81 matches
Mail list logo
