"Justin Pasher" <[EMAIL PROTECTED]> writes:
> Perfect. Just was I was looking for. So is it safe to actually run an update
> on the pg_catalog.pg_type.typowner column to change the user id from 101 to
> another existing user id without causing any other database weirdness?
Should work. In recent
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 17, 2007 12:51 AM
> To: Justin Pasher
> Cc: 'Richard Huxton'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Fixing broken permissions for deleted user
>
> "Justin Pasher" <[EMAIL PROTECTED]> write
Anyone have an implementation of the Jaro Winkler fuzzy string matching algo
for Postgres?
* http://en.wikipedia.org/wiki/Jaro-Winkler
* http://forums.oracle.com/forums/thread.jspa?threadID=385950&tstart=0
It's rumored to (sometimes?) work better than the Levenshtein algo included
in contrib/fuz
"Dave Golombek" <[EMAIL PROTECTED]> writes:
> Is there a way I can reformulate the query to help the planner use the
> indices?
Use 8.2. Also put an index on the base table, not only the children ---
the forced seqscan on the base weighs down the cost estimate for the
plan you would like to have.
I have a query which runs a join between a table with multiple child tables
and a second table. The planner is doing a sequential scan of the children,
then doing the join on the result, despite having indices that are relevant.
If I do the join between any of the children and the second table, the
I have installed postgresql 8.2 on redhat enterprise release WS 4. I
need to use the cube operator which I guess resides in the contrib
modules. Please may I know the link to install the appropricate contrib
module for my linux machine.
1) If you have an automatic updater like yum configured, yo
On 5/17/07, Ron Johnson <[EMAIL PROTECTED]> wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/17/07 16:49, Michael Nolan wrote:
> I don't know if my database is typical (as there probably is no such
> thing), but to restore a full dump (pg_dumpall) takes over 4 hours on my
> backup ser
Thanks all for the information. Summary is:
- 8.0 wasn't very strict, and allowed the illegal values in, instead
of mapping them over into UTF-8 space
- the values can be stripped with iconv -c
- 8.2 should be more strict
I'm in the midst of my upgrade to 8.2 now, hopefully the LATIN1->UTF8
no and no. a script looping through all tables (using
information_schema/pg_catalog info) is not that difficult to write
though.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Robert James
> Sent: Thursday, May 17, 2007 4:23 PM
> To: pgsql-general@
1. Is there anyway to give a role privs on a certain table, delete that
table, recreate it, and have those privs persist?
2. Is there anyway to give a role privs to all tables in a database, without
having to name each one individually? Can I use this to apply to tables
created later on?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/17/07 16:49, Michael Nolan wrote:
> I don't know if my database is typical (as there probably is no such
> thing), but to restore a full dump (pg_dumpall) takes over 4 hours on my
> backup server, but to restore a low level backup (about 35GB)
On May 17, 2007, at 16:47 , PFC wrote:
and put that in the form. Instead of being mapped to 2-byte UTF8
high-bit equivalents, they are going into the database directly as
one-byte values > 127. That is, as illegal UTF8 values.
Sometimes you also get HTML entities in the mix. Who kn
Yes, it can be done "semi-easily". Here's an example:
Ah!! Thank you. Now that someone else has done the leg work it'll be
better than "semi-easy" for me :D
-Glen
---(end of broadcast)---
TIP 6: explain analyze is your friend
I don't know if my database is typical (as there probably is no such thing),
but to restore a full dump (pg_dumpall) takes over 4 hours on my backup
server, but to restore a low level backup (about 35GB) and then process 145
WAL files (since Tuesday morning when the last low level backup was run)
On Wed, 2007-05-16 at 15:40 -0700, Glen Parker wrote:
> Is there a semi-easy way vacuum all tables in a database *except*
> those that are clustered?
Yes, it can be done "semi-easily". Here's an example:
select N.nspname,relname
from pg_class C
join pg_namespace N on C.r
Joshua D. Drake wrote:
Glen Parker wrote:
2. Autovacuum should effectively do this, assuming the clustered
table isn't being updated.
These are heavily updated tables. Plain vacuum isn't enough, and the
autovacuum facility isn't functional enough for me yet.
Can you elaborate on this?
I have a small database (PgSQL 8.0, database encoding UTF8) that folks
are inserting into via a web form. The form itself is declared
ISO-8859-1 and the prior to inserting any data, pg_client_encoding is
set to LATIN1.
Wouldn't it be simpler to have the browser submit the form in
Richard Broersma Jr <[EMAIL PROTECTED]> writes:
> Does anyone know if logic operations in plpgsql are short circuited in 8.2?
They are often optimized, but if you expect "short circuiting" following
exactly the rules of, say, C, you are likely to be disappointed. See
the manual:
http://www.postg
Aren't there PCI heartbeat cards that are independent of the load on
the host machine?
But, if the machine is fork-bombed, or drowning in swap, or generally
slowly committing suicide, it's not shall we say "available" anymore, so
you might want to finish it off...
--
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Yes, but that's not always a valid assumption.
And still PITR must update the index at each "insert", which is much
slower than the "bulk load then create index" of pg_dump.
On 05/17/07 16:01, Ben wrote:
> Yes, but the implication is that large datab
Paul Ramsey wrote:
> I have a small database (PgSQL 8.0, database encoding UTF8) that folks
> are inserting into via a web form. The form itself is declared
> ISO-8859-1 and the prior to inserting any data, pg_client_encoding is
> set to LATIN1.
>
> Most of the high-bit characters are correctly tr
Yes, but the implication is that large databases probably don't update
every row between backup periods.
On Thu, 17 May 2007, Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/17/07 11:04, Jim C. Nasby wrote:
[snip]
Ultimately though, once your database gets past a certa
I have a small database (PgSQL 8.0, database encoding UTF8) that folks
are inserting into via a web form. The form itself is declared
ISO-8859-1 and the prior to inserting any data, pg_client_encoding is
set to LATIN1.
Most of the high-bit characters are correctly translated from LATIN1 to
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/17/07 09:35, Andrew Sullivan wrote:
[snip]
>
> The problems come when you get a false detection of machine failure.
> Consider a case, for instance, where the machine A gets overloaded,
> goes into swap madness, or has a billion runaway processe
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/17/07 11:04, Jim C. Nasby wrote:
[snip]
>
> Ultimately though, once your database gets past a certain size, you
> really want to be using PITR and not pg_dump as your main recovery
> strategy.
But doesn't that just replay each transaction? It
On 5/17/07, Ian Harding <[EMAIL PROTECTED]> wrote:
I did have to change one a bit, I hope the breaks aren't in bad spots.
Did you just split it into 3 portions, or did you include the TOC and index
in each one, and drop the Release notes/appendixes?
This could be done so much better if ther
hello,
it depends on SQL. plpgsql hasn't own arithmetic unit. You can test it simply:
postgres=# create or replace function a() returns bool as $$ begin
raise notice 'a'; return true; end$$ language plpgsql;
CREATE FUNCTION
postgres=# create or replace function b() returns bool as $$ begin
rai
Does anyone know if logic operations in plpgsql are short circuited in 8.2?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
>> Hang on - did the US not alter the daylight-saving date recently? I'll
>> bet that's the issue.
>
> We're well past both the old and new change dates though, so for any
> combination of OS and Postgres knowing/not knowing about the l
Glen Parker wrote:
2. Autovacuum should effectively do this, assuming the clustered table
isn't being updated.
These are heavily updated tables. Plain vacuum isn't enough, and the
autovacuum facility isn't functional enough for me yet.
Can you elaborate on this?
Joshua D. Drake
--
usually contrib is installed along with postgresql.
Look for
/usr/lib/pgsql/contrib or /usr/lib/postgresql/contrib
and maybe it resides in
/usr/share/pgsql/contrib
or just type
locate contrib
Uwe
On Thursday 17 May 2007, ABHANG RANE wrote:
> Hi,
> I have installed postgresql 8.2 on redhat ent
Richard Huxton wrote:
Perhaps "VACUUM unclustered" or something?
A couple of thoughts:
1. You shouldn't routinely be running VACUUM FULL on a recent installation.
In my experience, some tables still must be VACUUM FULL'd from time to
time. I switched to clustering because it's much more ef
Robert James wrote:
Is there anyway to tell if the records in a union came from the 1st
query or the second?
Can I modify the individual queries to let me know this?
You can do something like:
select 'set1' as identifier, field1, field2 from table1
union
select 'set2', field1, field2 from tabl
You can install it into template1, which will make it installed on any
database created in the future (so long as you don't inherit from some
other, non-default database)
As for previously created database, I believe you have to install all
those manually. But that should be easy enough to
Andreas wrote:
Hi,
I've got an Ubuntu 7.04 (Feisty Fawn) set up and it has Ubuntu's binary
PG 8.2.4 running.
If I connect with a pgAdmin3 1.6.3 from Windows
Tools --> Server Status
pgAdmin complains that "The server lacks instrumentation functions.
pgAdmin III uses some support funtions ...
Hi,
I have installed postgresql 8.2 on redhat enterprise release WS 4. I
need to use the cube operator which I guess resides in the contrib
modules. Please may I know the link to install the appropricate contrib
module for my linux machine.
Thanks
Abhang
---(end of b
You could modify the queries and then do a union all... but then you may
see the same result from each part of the union. Which may or may not be a
problem for you.
On Thu, 17 May 2007, Robert James wrote:
Is there anyway to tell if the records in a union came from the 1st query or
the second
Anyone can set up anything that is either not copyright protected, or
freely distributable, or they create. I can (and will) modify these
existing tomes to your revised format or any better ones that come
along! I am pleased to see that a couple have sold already.
I also think it would be great
Is there anyway to install contrib (fuzzystr) so it's available to all
databases (even ones created previously)?
thanks
Hi,
I've got an Ubuntu 7.04 (Feisty Fawn) set up and it has Ubuntu's binary
PG 8.2.4 running.
If I connect with a pgAdmin3 1.6.3 from Windows
Tools --> Server Status
pgAdmin complains that "The server lacks instrumentation functions.
pgAdmin III uses some support funtions ..."
I though tho
Is there anyway to tell if the records in a union came from the 1st query or
the second?
Can I modify the individual queries to let me know this?
I can't wrap BEGIN/COMMIT around the INSERT in the trigger. Is there
another way of achieving this?
- Savepoints (won't work with your trigger approach)
- dblink would allow you to open another connection concurrently
---(end of broadcast)
Andrew Sullivan wrote:
> On Thu, May 17, 2007 at 12:50:56PM -0400, John D. Burger wrote:
>> Shoot The Other Node In The Head:
>>
>> http://www.linux-ha.org/STONITH
>
> Right. I have heard people tell me this works as advertised, but
> I've never used it. I can tell you that a certain large, well
Henka wrote:
> Is it possible to (somehow) commit a specific statement in a trigger
> function if the function itself is rolled back because of an error (eg, for a
> unique index error)?
No. You can use savepoints (or, in PL/pgSQL functions, EXCEPTION
blocks) to inhibit the uniqueness error from
Hi there,
I'm using PG 8.2.3.
Is it possible to (somehow) commit a specific statement in a trigger
function if the function itself is rolled back because of an error (eg, for a
unique index error)?
For example:
create table tab1 (col1 int unique);
create table tab2 (col1 int);
CREATE OR REPLAC
Excellent!
Two questions:
1. Might it be better to include the index and TOC (in both volumes), and do
something like this:
http://www.postgresql.org/files/documentation/pdf/8.2/postgresql-8.2.1-US.pdf
Vol I: 1-621, 1674-end
Vol II: 1-38, 622-1395, 1672-end
(Dropped some other things, I think...)
On Thu, May 17, 2007 at 12:50:56PM -0400, John D. Burger wrote:
>
> Shoot The Other Node In The Head:
>
> http://www.linux-ha.org/STONITH
Right. I have heard people tell me this works as advertised, but
I've never used it. I can tell you that a certain large, well-known
blue company has a simi
Hi,
But now having 12 columns and multicolumn index, wont this slow down
the search process. I mean in general retrieving 12 columns using a
multicolumn index is slower or faster compared to an index on a 12 size
array?
Thanks
Abhang
Quoting "John D. Burger" <[EMAIL PROTECTED]>:
ABHANG RANE
Andrew Sullivan wrote:
Now, if B mounts the disk and starts
the postmaster, but doesn't have a way to make _sure_ tha A is
completely disconnected from the disk, then it's entirely possible A
will flush buffers out to the still-mounted data area. Poof!
Instant data corruption.
Shoot The Other
Hannes Dorbath wrote:
> So I just keep what I always had -- lots of swap and overcommit off.
Ehrm, overcommit on I mean.
--
Best regards,
Hannes Dorbath
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Martijn van Oosterhout wrote:
> Make lots and lots of swap. You'll probably never use it, but at least
> it won't get in your way. I'd say 1-1.5 times your memory at least if
> you want overcommit off.
Thanks for your detailed explanations. I indeed misunderstood overcommit
with shared memory.
So
On Thu, May 17, 2007 at 08:19:08AM -0500, Lee Keel wrote:
> I am restoring a 51GB backup file that has been running for almost 26 hours.
> There have been no errors and things are still working. I have turned fsync
> off, but that still did not speed things up. Can anyone provide me with the
> op
Hannes Dorbath wrote:
Scott Marlowe wrote:
What distro / kernel version of linux are you running? We have a
similar issue with late model hardware and RHEL4 recently here at work,
where our workstations are running out of memory. They aren't running
postgresql, they're java dev workstations
On Thu, May 17, 2007 at 03:46:59PM +0200, Hannes Dorbath wrote:
> On a clone box I disabled overcommit, lowered PG's memory settings a
> bit, disabled swap, mirrored production load to it and monitored it how
> it would behave. As I more or less expected, it got into trouble after
> about 6 hours.
On Thu, May 17, 2007 at 09:45:18AM -0400, Kenneth Downs wrote:
> The last one left that I have is the sticky issue of a paypal IPN
> transaction coming in. I believe it applies generally to financial
> transactions. The user is sent by our application to the Paypal site.
> When they pay, payp
Scott Marlowe wrote:
> What distro / kernel version of linux are you running? We have a
> similar issue with late model hardware and RHEL4 recently here at work,
> where our workstations are running out of memory. They aren't running
> postgresql, they're java dev workstations and it appears to b
Has anybody pondered this and come up with anything?
Well, I've done e-commerce stuff although it was with banks, not paypal.
There are a few levels of safety :
- Level 0 : Total stupidity (osCommerce)
Bank redirects to your site. Order is marked as paid with "paid=1" in the
UR
Hannes Dorbath wrote:
As probably many people I've been running PostgreSQL on Linux with
default overcommit settings and a lot of swap space for safety, though
disabling overcommit is recommend according to recent documentation.
PG's memory usage is not exactly predictable, for settings like wor
"Thiago Ventura" <[EMAIL PROTECTED]> writes:
> The problem is when I do some query. NEVER its call the 'beginscan',
> 'gettuple' or another function that is related with a query, that is, in the
> querys my index do not being used.
> What it can be causing this?
Have you created operator classes t
Hi,
I am trying create a new access method for the PostgreSQL.
Reading the manual, I learned that must implement 12 functions (build,
insert, gettuple, beginscan, ...).
In the begin its OK. As soon as I type the command 'CREATE INDEX' the
function 'build' is call and the index is create. When I ca
On Mon, May 14, 2007 at 10:42:13AM -0500, John Gateley wrote:
> Thanks very much to all who responded, the replies were very helpful.
One thing I will mention, that seems not to have come out in a number
of the replies: the details _really really_ count when you set up
this sort of mutli-machine h
Kenneth Downs wrote:
> The last one left that I have is the sticky issue of a paypal IPN
> transaction coming in. I believe it applies generally to financial
> transactions. The user is sent by our application to the Paypal site.
> When they pay, paypal sends a POST with various information that
Kenneth Downs wrote:
The last one left that I have is the sticky issue of a paypal IPN
transaction coming in. I believe it applies generally to financial
transactions. The user is sent by our application to the Paypal site.
When they pay, paypal sends a POST with various information that we
On Thu, 17 May 2007 09:45:18 -0400
Kenneth Downs <[EMAIL PROTECTED]> wrote:
> The last one left that I have is the sticky issue of a paypal IPN
> transaction coming in. I believe it applies generally to financial
> transactions. The user is sent by our application to the Paypal site.
> When
Hi!
First of all, be sure you have PostgreSQL ODBC driver installed. To
do this - follow:
start ->control panel -> administrative tools -> data sources (ODBC)
-> drivers
If you see in a list "PostgreSQL ANSI" or "PostgreSQL Unicode" -
this means driver is installed, otherwise - you s
As probably many people I've been running PostgreSQL on Linux with
default overcommit settings and a lot of swap space for safety, though
disabling overcommit is recommend according to recent documentation.
PG's memory usage is not exactly predictable, for settings like work_mem
I always monitored
I am seeking to have a system in which it is never necessary for
application code to "go root" w/respect to the database server, where
all commands issued to a server are as a regular logged in user with
their privileges.
There are two holes I know of here. Thanks to Tom I've got the answer
Marc Balmer <[EMAIL PROTECTED]> writes:
> I have two login roles, user_A and admin_A, and a group role, group_A.
> user_A is member of group_A
> I create a table t_data, owned by admin_A
> I create a view v_data, owned by admin_A
> Now I grant SELECT privilege on view v_data to group_A
> I would
I am restoring a 51GB backup file that has been running for almost 26 hours.
There have been no errors and things are still working. I have turned fsync
off, but that still did not speed things up. Can anyone provide me with the
optimal settings for restoring a large database?
Thanks in advan
Richard Huxton <[EMAIL PROTECTED]> writes:
> Hang on - did the US not alter the daylight-saving date recently? I'll
> bet that's the issue.
We're well past both the old and new change dates though, so for any
combination of OS and Postgres knowing/not knowing about the law change,
they ought to b
Jamie Deppeler <[EMAIL PROTECTED]> writes:
> We currently running Windows 2003 issues with time zone set to
> (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have
> with Postgresql 8.1.5 is that time zone is being reported with a
> incorrect off set 4 not 5 and this is causin
Richard Huxton wrote:
Jamie Deppeler wrote:
Hi,
We currently running Windows 2003 issues with time zone set to
(GMT-05:00) Eastern Time (US & Canada). Problem that we currently
have with Postgresql 8.1.5 is that time zone is being reported with a
incorrect off set –4 not –5 and this is caus
Dave Page wrote:
[EMAIL PROTECTED] wrote:
Can you confirm that you don't provide support for Windows Vista for any
release of Postgres. I'm dumbfounded an it appears that you don't
support Vista. If so, are you planning any releases. I have a major
project and was hoping to use Postgres.
Postg
I am having a hard time here with PostgreSQL 8.2.4... What I want to do
is rather simple:
I have two login roles, user_A and admin_A, and a group role, group_A.
user_A is member of group_A
I create a table t_data, owned by admin_A
I create a view v_data, owned by admin_A
Now I grant SELECT privi
Ray Stell wrote:
On Wed, May 16, 2007 at 08:21:59AM -0700, bruce wrote:
hey.. .
can someone point me to the cmds that i'd use in order to see what databases
are created in my postgres app. i need to see what's here, and then i need
> to know the cmd to then delete a given database, and the unde
Hi again!
It seems that the problem was the max_fsm_relations parameter which was
increased
I still do not understand why in the past I received always the message:
ERROR: out of shared memory
Is this an appropriate message for the need for increasing this parameter?
With best rega
Jamie Deppeler wrote:
Richard Huxton wrote:
Jamie Deppeler wrote:
Hi,
We currently running Windows 2003 issues with time zone set to
(GMT-05:00) Eastern Time (US & Canada). Problem that we currently
have with Postgresql 8.1.5 is that time zone is being reported with a
incorrect off set –4
Jamie Deppeler wrote:
Hi,
We currently running Windows 2003 issues with time zone set to
(GMT-05:00) Eastern Time (US & Canada). Problem that we currently have
with Postgresql 8.1.5 is that time zone is being reported with a
incorrect off set –4 not –5 and this is causing incorrect time to be
[EMAIL PROTECTED] wrote:
Can you confirm that you don't provide support for Windows Vista for any release
of Postgres. I'm dumbfounded an it appears that you don't support Vista. If so,
are you planning any releases. I have a major project and was hoping to use
Postgres.
I too am dumbfounded
[EMAIL PROTECTED] wrote:
> Can you confirm that you don't provide support for Windows Vista for any
> release of Postgres. I'm dumbfounded an it appears that you don't
> support Vista. If so, are you planning any releases. I have a major
> project and was hoping to use Postgres.
PostgreSQL will ru
Hi,
We currently running Windows 2003 issues with time zone set to
(GMT-05:00) Eastern Time (US & Canada). Problem that we currently have
with Postgresql 8.1.5 is that time zone is being reported with a
incorrect off set –4 not –5 and this is causing incorrect time to be set.
Below is exampl
81 matches
Mail list logo
