Hi all, I’d like to use to_char function to return an already
formatted result set. The it_IT locale (correctly) specifies a numeric
group separator as “. ”, but I’d really want to use just “.”; not
standard but much more common.
Anyway, the problem is that I didn’t find enough about how to c
On 8/29/08, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
>
> The latter should be in effect if your database encoding is UTF-8.
> What's the database encoding?
My database encoding on BOTH the sides is UTF8. The dumped DB is UTF8.
The one I am importing into is also UTF8. So why is it showing me t
"Raymond O'Donnell" <[EMAIL PROTECTED]> writes:
> On 28/08/2008 22:26, Bill wrote:
>> someone confirm that currval() returns the the value for the connection
>> from which it is called?
> Yes, see here:
> http://www.postgresql.org/docs/8.3/static/functions-sequence.html
> and specifically a litt
Bill wrote:
Tino Wildenhain wrote:
Hi Bill,
Bill wrote:
The SQL database servers I have worked with cannot use and index for
a SELECT of the form
SELECT * FROM ATABLE
WHERE AFIELD LIKE ?
because there is no way to know the location of the wild card until
the parameter value is known. Inter
On Thu, 21 Aug 2008, Glen Parker wrote:
So you advocate archiving the WAL files from a small xlog volume, to a larger
local volume. Why not just make the xlog volume large enough to handle
overruns, since you obviously have the space? Copying each WAL from one
place to another on the local m
Matthew Dennis wrote:
> The cases about taking a string and sending it via execute don't seem to fit
> here for 1) cases where it is impossible to track the dependencies can be
> trivially constructed and 2) the very nature of the execute statement makes
> it obvious that it I shouldn't expect it
On Thu, Aug 28, 2008 at 8:51 PM, Alvaro Herrera
<[EMAIL PROTECTED]>wrote:
> There's no way at all in the general case (a function name could be
> passed as a parameter, for example). I think Matthew is suggesting to
> track dependencies at run time, but that seems a recipe for burnt
> fingers and
On Aug 28, 2008, at 7:22 PM, Matthew Dennis wrote:
Yes, but in the case of pluggable languages, you still load
something that constitutes the "source". In the case of PL/Java,
the jar for example.
This would mean that, for example, if you changed any single function
(no matter how distan
On Thu, Aug 28, 2008 at 9:09 PM, Christophe <[EMAIL PROTECTED]> wrote:
>
> On Aug 28, 2008, at 7:04 PM, Matthew Dennis wrote:
>
>> The plpgsql execute statement, as I understand it, means "take this string
>> and execute like a client sent it to you".
>>
>
> Of course, the string could come from a
On Thu, Aug 28, 2008 at 9:01 PM, Christophe <[EMAIL PROTECTED]> wrote:
>
> On Aug 28, 2008, at 6:51 PM, Alvaro Herrera wrote:
>
>> Also, you have to keep in mind that we support pluggable languages. The
>> function's source code is just an opaque string.
>>
>
> Oh, ouch, right.
>
> I think that t
On Wed, 20 Aug 2008, Tom Lane wrote:
Greg Smith <[EMAIL PROTECTED]> writes:
You also don't want to be the guy who has to explain why the database is
taking hours to come back up again after it crashed and has 4000 WAL
segments to replay, because archiving failed for a long time and prevented
pr
On Aug 28, 2008, at 7:04 PM, Matthew Dennis wrote:
The plpgsql execute statement, as I understand it, means "take this
string and execute like a client sent it to you".
Of course, the string could come from anywhere. There's no inherent
reason that I can think of (except good taste) that yo
On Thu, Aug 28, 2008 at 8:15 PM, Christophe <[EMAIL PROTECTED]> wrote:
>
> On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote:
>
>> I'm not sure I follow. Couldn't you track which statements were prepared
>> that called a function and either reprepare (just like reindex, recheck,
>> etc) or in the
On Aug 28, 2008, at 6:51 PM, Alvaro Herrera wrote:
Also, you have to keep in mind that we support pluggable
languages. The
function's source code is just an opaque string.
Oh, ouch, right.
I think that this is one of those cases where it's better that we
simply advertise: BE AWARE OF THI
Christophe wrote:
>
> On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote:
>> I'm not sure I follow. Couldn't you track which statements were
>> prepared that called a function and either reprepare (just like
>> reindex, recheck, etc) or in the case of dropping a function, refuse to
>> drop it
On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote:
I'm not sure I follow. Couldn't you track which statements were
prepared that called a function and either reprepare (just like
reindex, recheck, etc) or in the case of dropping a function,
refuse to drop it because something depends on it
On Thu, Aug 28, 2008 at 7:52 PM, Christophe <[EMAIL PROTECTED]> wrote:
>
> On Aug 28, 2008, at 5:49 PM, Matthew Dennis wrote:
>
>> Yes, I can see that would indeed be a problem. Are there future plans to
>> start tracking such dependencies? It seems like it would be a good idea in
>> general.
>>
On Aug 28, 2008, at 5:49 PM, Matthew Dennis wrote:
Yes, I can see that would indeed be a problem. Are there future
plans to start tracking such dependencies? It seems like it would
be a good idea in general.
I believe the EXECUTE statement would thwart such plans.
--
Sent via pgsql-gener
On Thu, Aug 28, 2008 at 5:32 PM, Christophe <[EMAIL PROTECTED]> wrote:
> On Aug 28, 2008, at 3:21 PM, Matthew Dennis wrote:
>
>> I have no doubt that someone would complain about it, but I think it's
>> better than the alternative.
>>
>
> Determining if changing any function will cause an index to
On Thu, Aug 28, 2008 at 6:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Matthew Dennis" <[EMAIL PROTECTED]> writes:
> > On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> >> (Changing the behavior of an allegedly IMMUTABLE function has a number
> >> of other pitfalls besides th
On Thursday 28 August 2008 3:14:01 pm Albretch Mueller wrote:
> On Thu, Aug 28, 2008 at 5:40 PM, Raymond O'Donnell <[EMAIL PROTECTED]> wrote:
> > You have to create the table first before you can COPY into it.
>
> ~
> Well, based on how the statement reads I made the wrong assumption of
> thinking
"Matthew Dennis" <[EMAIL PROTECTED]> writes:
> On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> (Changing the behavior of an allegedly IMMUTABLE function has a number
>> of other pitfalls besides that one, btw.)
> I'm interested in knowing what they are - could you point me
On Thu, 28 Aug 2008 16:46:19 -0600
"Scott Marlowe" <[EMAIL PROTECTED]> wrote:
> On Thu, Aug 28, 2008 at 4:18 PM, Joshua Drake <[EMAIL PROTECTED]>
> wrote:
> > On Thu, 28 Aug 2008 16:06:14 -0600
> > "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
> >> No, setval, currval, and lastval all require as an a
On Thu, Aug 28, 2008 at 4:18 PM, Joshua Drake <[EMAIL PROTECTED]> wrote:
> On Thu, 28 Aug 2008 16:06:14 -0600
> "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
>> No, setval, currval, and lastval all require as an argument a sequence
>> name. So the real issue is you have to know the sequence name to u
On Thu, 28 Aug 2008 15:29:51 -0700
Bill <[EMAIL PROTECTED]> wrote:
> The PostgresSQL 8.3 help file clearly shows that lastval() does not
> take a sequence as a parameter and the description i is "Return the
> value most recently returned by |nextval| in the current session.
> This function is iden
On Aug 28, 2008, at 3:21 PM, Matthew Dennis wrote:
I have no doubt that someone would complain about it, but I think
it's better than the alternative.
Determining if changing any function will cause an index to break is
not a straight-forward problem. I don't believe that PG right now
kee
On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> (Changing the behavior of an allegedly IMMUTABLE function has a number
> of other pitfalls besides that one, btw.)
>
I'm interested in knowing what they are - could you point me in the right
direction (I've read the docs on im
Scott Marlowe wrote:
On Thu, Aug 28, 2008 at 3:38 PM, Bill <[EMAIL PROTECTED]> wrote:
I am new to PostgreSQL but it seems to me that lastval() will only work if
the insert does not produce side effects that call nextval(). Consider the
case where a row is inserted into a table that has an aft
On Aug 28, 2008, at 3:23 PM, D. Dante Lorenso wrote:
I use RETURNING for all my insert and UPDATE statements now.
Usually I'll return the primary key for the table, but sometimes I
return a column that is created by one of my triggers. It's
awesome to be able to do this in one query.
On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> If it did that, you (or someone) would complain about the enormous
> overhead imposed on trivial updates of the function. Since determining
> whether the function actually did change behavior is Turing-complete,
> we can't rea
Scott Marlowe wrote:
On Thu, Aug 28, 2008 at 3:38 PM, Bill <[EMAIL PROTECTED]> wrote:
I am new to PostgreSQL but it seems to me that lastval() will only work if
the insert does not produce side effects that call nextval(). Consider the
case where a row is inserted into a table that has an after
Russ Brown escribió:
> Masis, Alexander (US SSA) wrote:
> >
> >"SELECT CURRVAL(
> > pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>
> Any reason why you can't just do this?
>
> CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
> SELECT lastval();
> $$ LANGUAGE SQL VOLATILE;
I
On Thu, 28 Aug 2008 16:06:14 -0600
"Scott Marlowe" <[EMAIL PROTECTED]> wrote:
> On Thu, Aug 28, 2008 at 3:38 PM, Bill <[EMAIL PROTECTED]> wrote:
> > I am new to PostgreSQL but it seems to me that lastval() will only
> > work if the insert does not produce side effects that call
> > nextval(). Cons
On Thu, Aug 28, 2008 at 5:40 PM, Raymond O'Donnell <[EMAIL PROTECTED]> wrote:
> You have to create the table first before you can COPY into it.
~
Well, based on how the statement reads I made the wrong assumption of
thinking that PG would be smart enough to make ends-meat with the
data that it ge
On Thu, Aug 28, 2008 at 3:38 PM, Bill <[EMAIL PROTECTED]> wrote:
> I am new to PostgreSQL but it seems to me that lastval() will only work if
> the insert does not produce side effects that call nextval(). Consider the
> case where a row is inserted into a table that has an after insert trigger
> a
Hi, I've changed the setting log_statement to mod, in order to log data
modifications, and it's working fine with sentences sent by the client
application (psql included), but insert/update/delete sentences executed
inside functions are not logged. Functions are called in a select query.
I've relo
On 28/08/2008 22:29, Albretch Mueller wrote:
> jpk=# COPY j2sdk_1_4_2_18_binfls_md5sum FROM
> '/media/hda3/prjx/JPack/REF/LANDMARKS/PROFILES/20080828104627_j2sdk-1_4_2_18-linux-i586.binfls.md5sum.txt'
> CSV HEADER;
> ERROR: relation "j2sdk_1_4_2_18_binfls_md5sum" does not exist
> ~
> What is goi
Steve Atkins wrote:
On Aug 28, 2008, at 12:09 PM, Scott Marlowe wrote:
On Thu, Aug 28, 2008 at 10:14 AM, Masis, Alexander (US SSA)
<[EMAIL PROTECTED]> wrote:
I was mapping C++ application code that works with mySQL to work with
Postgres.
There were a number of articles on line regarding the c
On 28/08/2008 22:26, Bill wrote:
> someone confirm that currval() returns the the value for the connection
> from which it is called?
Yes, see here:
http://www.postgresql.org/docs/8.3/static/functions-sequence.html
and specifically a little further down the page on "currval":
Return the va
Tino Wildenhain wrote:
Hi Bill,
Bill wrote:
The SQL database servers I have worked with cannot use and index for
a SELECT of the form
SELECT * FROM ATABLE
WHERE AFIELD LIKE ?
because there is no way to know the location of the wild card until
the parameter value is known. InterBase and Fire
Hi,
~
I created a number of csv files which I need to import into PG
tables. On the them looks like this:
~
sh-3.1# head -n 3
/media/hda3/prjx/JPack/REF/LANDMARKS/PROFILES/20080828104627_j2sdk-1_4_2_18-linux-i586.binfls.md5sum.txt
"md5sum","fl"
"d41d8cd98f00b204e9800998ecf8427e",".systemPrefs/.sy
Masis, Alexander (US SSA) wrote:
I was mapping C++ application code that works with mySQL to work with
Postgres.
There were a number of articles on line regarding the conversion from
mySQL to Postgres like:
http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL
http://groups.dru
Steve Atkins wrote:
On Aug 28, 2008, at 12:27 PM, Tino Wildenhain wrote:
Hi Bill,
Bill wrote:
The SQL database servers I have worked with cannot use and index for
a SELECT of the form
SELECT * FROM ATABLE
WHERE AFIELD LIKE ?
because there is no way to know the location of the wild card unti
On Thu, Aug 28, 2008 at 1:56 PM, Steve Atkins <[EMAIL PROTECTED]> wrote:
>
> Or lastval() if you want something bug-compatible with MySQL.
Not exactly. LAST_INSERT_ID is transactionally safe in that one
connection doesn't see another connections. However, it has it's own
special brand of bug tha
On Aug 28, 2008, at 12:27 PM, Tino Wildenhain wrote:
Hi Bill,
Bill wrote:
The SQL database servers I have worked with cannot use and index
for a SELECT of the form
SELECT * FROM ATABLE
WHERE AFIELD LIKE ?
because there is no way to know the location of the wild card until
the parameter va
On Aug 28, 2008, at 12:09 PM, Scott Marlowe wrote:
On Thu, Aug 28, 2008 at 10:14 AM, Masis, Alexander (US SSA)
<[EMAIL PROTECTED]> wrote:
I was mapping C++ application code that works with mySQL to work with
Postgres.
There were a number of articles on line regarding the conversion from
mySQL
On Thu, Aug 28, 2008 at 3:35 PM, John T. Dow <[EMAIL PROTECTED]> wrote:
> However, it would really be nice if the WAL files could be used to make the
> restored data more current, even if not everything can be restored. Are we
> certain that useful information can't be gleaned from them to apply
Masis, Alexander (US SSA) wrote:
>
>"SELECT CURRVAL(
> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>
Any reason why you can't just do this?
CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
SELECT lastval();
$$ LANGUAGE SQL VOLATILE;
--
Sent via pgsql-general mailing list
On Thursday 28 August 2008, "John T. Dow" <[EMAIL PROTECTED]> wrote:
> B - To protect against permanent server failure (such as physical
> destruction of the server's hard drives), do a pg_dump backup regularly.
> The only data loss is data inserted or updated since the last pg_dump.
> Use pg_dumpa
Alan Hodgson wrote:
On Thursday 28 August 2008, Shane Ambler <[EMAIL PROTECTED]> wrote:
If you have a filesystem backup from 6 hours ago, then the WAL
files as they are now can be used to update the backup to match the
db as it is now. This makes the filesystem backup have two points
of interest
I understand that WAL files can only be used with the database files in use at
the time the WAL was written, therefore they are of no use to a database
reconstructed from a pg_dump file.
Let me see if I have this right.
A - To protect against temporary server failure (such as a loss of power),
Hi Bill,
Bill wrote:
The SQL database servers I have worked with cannot use and index for a
SELECT of the form
SELECT * FROM ATABLE
WHERE AFIELD LIKE ?
because there is no way to know the location of the wild card until the
parameter value is known. InterBase and Firebird allow
SELECT * FR
On Thu, Aug 28, 2008 at 7:23 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> (It might be a good idea to fix pgAdmin so it ignores other sessions'
> temp schemas, though.)
It doesn't use them itself so normally you'll never see any. It will
show all of them if the Show System Objects option is turned o
On Thu, Aug 28, 2008 at 10:14 AM, Masis, Alexander (US SSA)
<[EMAIL PROTECTED]> wrote:
> I was mapping C++ application code that works with mySQL to work with
> Postgres.
> There were a number of articles on line regarding the conversion from
> mySQL to Postgres like:
SNIP
> Well, in MySQL it's eas
On Tue, Aug 26, 2008 at 2:24 PM, <[EMAIL PROTECTED]> wrote:
> I need to copy some data that includes an date as /MM/DD. Is there
> a method I can do this and replace the "/" with "-" on the fly in the
> copy command string?
You shouldn't need to. Pgsql should accept /mm/dd as valid date
I was mapping C++ application code that works with mySQL to work with
Postgres.
There were a number of articles on line regarding the conversion from
mySQL to Postgres like:
http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL
http://groups.drupal.org/node/4680
http://jmz.iki.f
I need to copy some data that includes an date as /MM/DD. Is there
a method I can do this and replace the "/" with "-" on the fly in the
copy command string?
thx,
tf
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgr
I'm trying to build a SQL script that will create a database if it
doesn't already exist. I've looked everywhere and haven't been able to
find anything. Am I missing something? Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
Postgres works fine in solaris zones. We've been using solaris with
postgresql running in zones for about a year.
Is there a more specific concern you have?
-Jovan
Hi,
Anybody has an experience in running postgres database server from a
solaris zone? Do you see any problems with such a set
Hello
2008/8/28 Bill <[EMAIL PROTECTED]>:
> The SQL database servers I have worked with cannot use and index for a
> SELECT of the form
>
> SELECT * FROM ATABLE
> WHERE AFIELD LIKE ?
>
> because there is no way to know the location of the wild card until the
> parameter value is known. InterBase a
"Roberts, Jon" <[EMAIL PROTECTED]> writes:
> I am noticing a large number of temp schemas in my database. We use
> temp tables but it doesn't appear that the schemas get dropped for some
> reason.
That's intentional. There doesn't seem a lot of value in dropping a
catalog entry that'll just have
The SQL database servers I have worked with cannot use and index for a
SELECT of the form
SELECT * FROM ATABLE
WHERE AFIELD LIKE ?
because there is no way to know the location of the wild card until the
parameter value is known. InterBase and Firebird allow
SELECT * FROM ATABLE
WHERE AFIELD
Jeff Davis <[EMAIL PROTECTED]> writes:
> On Thu, 2008-08-28 at 00:42 -0400, Tom Lane wrote:
>> The reason that these statements are not inconsistent is that the
>> Sort is the inner relation for a mergejoin. In the presence of
>> duplicate keys in the outer relation, a mergejoin will "rewind" and
On Thursday 28 August 2008, Shane Ambler <[EMAIL PROTECTED]> wrote:
> If you have a filesystem backup from 6 hours ago, then the WAL files as
> they are now can be used to update the backup to match the db as it is
> now. This makes the filesystem backup have two points of interest. First
> the ent
I am noticing a large number of temp schemas in my database. We use
temp tables but it doesn't appear that the schemas get dropped for some
reason. This greatly slows down how long it takes pgAdmin to connect
because it retrieves thousands of pg_temp_% schemas.
We mainly use "drop on commit" and
On Thu, Aug 28, 2008 at 07:51:57AM +0800, Phoenix Kiula wrote:
> system-provided way of checking for rows that may not be UTF8? Or to
> even have a constraint to this effect, to disallow any non-UTF8 data
> from getting in there?
The latter should be in effect if your database encoding is UTF-8.
Thanks to everyone for for the myriad of informative replies on this.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
John T. Dow wrote:
Douglas
You can't blame me for being confused. Here's from section 23.3 of
the 8.2 manual.
"At all times, PostgreSQL maintains a write ahead log (WAL) in the
pg_xlog/ subdirectory of the cluster’s data directory. The log
describes every change made to the database’s data file
On Thu, Aug 28, 2008 at 12:35 PM, John T. Dow <[EMAIL PROTECTED]> wrote:
> You can't blame me for being confused. Here's from section 23.3 of the 8.2
> manual.
>
> "At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/
> subdirectory of the cluster's
> data directory. The log
William Garrison wrote:
We have a SAN volume, and we created a tablespace that that points to
that SAN volume (Z: drive). This put all the table files on Z:. It
was our assumption that the table files + the archived transaction
would now be on the Z: drive, and that was enough to restore the
In response to Joao Ferreira gmail <[EMAIL PROTECTED]>:
>
> When autovacuum was introduced, I kept the weekly VACUUM FULL because it
> effectively brings disk occupation down, despite it grows back after a few
> hours. It's just re-assuring to me to make sure that at least one of the
> vacuums it'
Douglas
You can't blame me for being confused. Here's from section 23.3 of the 8.2
manual.
"At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/
subdirectory of the clusters
data directory. The log describes every change made to the databases data
files. This log exists
On Thu, 2008-08-28 at 00:42 -0400, Tom Lane wrote:
> The reason that these statements are not inconsistent is that the
> Sort is the inner relation for a mergejoin. In the presence of
> duplicate keys in the outer relation, a mergejoin will "rewind" and
> rescan duplicate keys in the inner relatio
On Thu, Aug 28, 2008 at 05:15:06PM +0100, Joao Ferreira gmail wrote:
> When autovacuum was introduced, I kept the weekly VACUUM FULL because it
> efectively brings disk ocupatio down, dispite it grows back after a few
> hours. It's just re-assuring to me to make sure that at least one of the
> vac
We have a table "entry" that is partitioned into two sub-tables named
"entry_part_new" and "entry_part_old", as described here:
http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
We find that in some cases, when joining to a partitioned table, the planner
makes wilding inaccurate
Hello,
I'dd like to apologise about my short knowledge of VACUUM FULL and
REINDEX.
I'm just stating what I do in my case. I don not know if it is a corner
case or not.
I've been dealing with this specific application which is very demanding
for Postgres for about 2 years.
When autovacuum was in
"=?ISO-8859-1?Q?Laura_Del_Ca=F1o?=" <[EMAIL PROTECTED]> writes:
> I am issuing the following command:
> pg_dump -c -o -s -n distributed -f backups/schema.sql syslog
> which apparently works fine. It is only when I tried to restore it in
> a fresh created database using:
> psql syslog < backups/sche
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> For the record:
>
> Bill Moran escribió:
>
> > The naptime at 600 is probably a bad idea. If you only have one user
> > database on this system, then it only gets investigated by autovac once
> > every 40 minutes (template0 ... template1 ...
On Thursday 28 August 2008, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> Ow Mun Heng wrote:
> > I posed this question to the Slony List as well, but no response yet.
>
> It's actually in the Slony docs:
> http://www.slony.info/documentation/partitioning.html
>
> I haven't actually used it in produc
For the record:
Bill Moran escribió:
> The naptime at 600 is probably a bad idea. If you only have one user
> database on this system, then it only gets investigated by autovac once
> every 40 minutes (template0 ... template1 ... postgres ... yourdb)
> Consider that autovac uses very little reso
[EMAIL PROTECTED] writes:
> Thus, when piping the output (from legacy host 192.168.2.2) to
> populate the newly initialized cluster, by way of running (on the new
> host 192.168.2.3)
> pg_dumpall -h 192.168.2.2|psql
> an error occurs in that first section when the script attempts to
> se
Tom Lane <[EMAIL PROTECTED]> writes:
> Since determining whether the function actually did change behavior is
> Turing-complete, we can't realistically try to determine that in software.
> So we leave it up to the user to reindex if he makes a behavioral change in
> an indexed function.
Another
In response to Joao Ferreira gmail <[EMAIL PROTECTED]>:
>
> On Thu, 2008-08-28 at 19:53 +0800, Phoenix Kiula wrote:
> > On our database of about 5GB we vaccuum all of our 12 tables (only one
> > is huge, all others have about 100,000 rows or so) every hour or so.
>
> if you refer to manual VACUUM
On Thu, Aug 28, 2008 at 10:57 AM, John T. Dow <[EMAIL PROTECTED]> wrote:
> BACKGROUND INFO BEGINS
>
> Recently I had some questions about doing backups and received very helpful
> replies. I have now put together a BAT file to do a routine backup, using
> pg_dumpall with the -g option to get the
On Thu, Aug 28, 2008 at 9:47 AM, Joao Ferreira gmail
<[EMAIL PROTECTED]> wrote:
>
> http://www.postgresql.org/docs/8.3/static/maintenance.html
>
> you'll find that once in a while (start at once/week and build up or
> down from there) you can/should:
>
> - vacuum full
> - reindex your tables
> - re
I have table sort of like this:
create table snoz (
a bool default false,
b bool default false,
c bool default false
);
I want to set up a trigger so that any time a row is inserted or
updated, the value of c is set to true if a and/or b is true.
How can I do this?
Thanks!
Matt
-
I've run into a problem while migrating an existing 8.2.7 data base
to a new server running 8.3.3 (although I think the version numbers
may not matter -- I think I've seen this problem in the past and just
lived with it since so much of Postgresql is so great!).
The problem stems from the f
On Thu, 2008-08-28 at 10:53 +0100, Joao Ferreira gmail wrote:
> shell> su postgres -c "/usr/bin/postmaster -D /var/pgsql/data"
> FATAL: database files are incompatible with server
> DETAIL: The data directory was initialized by PostgreSQL version 8.1,
> which is not compatible with this version 8
On Thursday 28 August 2008 4:32:56 am Laura Del Caño wrote:
> Hi,
> I am having a problem with pg_dump and I could not find any reference
> in the archive mailing lists.
> I am issuing the following command:
>
> pg_dump -c -o -s -n distributed -f backups/schema.sql syslog
>
> which apparently works
Steve Clark <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Consider testing the conditions on A at the top level, instead of
>> redundantly checking them inside the sub-query on B.
> Thanks for the response Tom, I am a SQL neophyte, so I'll try to
> rework the query.
What I meant to suggest was
BACKGROUND INFO BEGINS
Recently I had some questions about doing backups and received very helpful
replies. I have now put together a BAT file to do a routine backup, using
pg_dumpall with the -g option to get the roles, and pg_dump with the custom
format to get all the data.
I am now testing
On Thu, 2008-08-28 at 19:53 +0800, Phoenix Kiula wrote:
> On our database of about 5GB we vaccuum all of our 12 tables (only one
> is huge, all others have about 100,000 rows or so) every hour or so.
if you refer to manual VACUUM or VACUUM FULL every hour is probably too
much. You should aim your
"Richard Broersma" <[EMAIL PROTECTED]> writes:
> UPDATE Foo
>SET ( a, b, c, ..., n ) = ( SELECT newA, newB, newC, ..., newN
> FROM Bar WHERE foo.id = Bar.id )
> Any thoughts on this?
It's been on the TODO list for quite some time:
http://wiki.postgresql
"Matthew Dennis" <[EMAIL PROTECTED]> writes:
> Given table T(c1 int) and function F(arg int) create an index on T using
> F(c1). It appears that if you execute "create or replace function F" and
> provide a different implementation that the index still contains the results
> from the original impl
> Date: Wed, 27 Aug 2008 06:58:33 -0700
> From: [EMAIL PROTECTED]
> Subject: Re: [ADMIN] [GENERAL] PITR - base backup question
> To: pgsql-general@postgresql.org; [EMAIL PROTECTED]; [EMAIL PROTECTED]
>
>
>
>
> --- On Tue, 8/26/08, Richard Broersma wro
On Thu, Aug 28, 2008 at 07:53:17PM +0800, Phoenix Kiula wrote:
> On our database of about 5GB we vaccuum all of our 12 tables (only one
> is huge, all others have about 100,000 rows or so) every hour or so.
>
> But we also have autovaccuum enabled. Is this okay? Do the two vaccuum
> processes cont
On Thu, Aug 28, 2008 at 02:44:08PM +0300, Devrim GÜNDÜZ wrote:
> After restart, OS and PostgreSQL caches are cleaned up -- it might also
> slow down PostgreSQL a bit.
I'll bet this is the right answer -- before, you were mostly getting
things out of cache (memory), and right now everything has to
On our database of about 5GB we vaccuum all of our 12 tables (only one
is huge, all others have about 100,000 rows or so) every hour or so.
But we also have autovaccuum enabled. Is this okay? Do the two vaccuum
processes contradict each other, or add unnecessary load to the
system?
The reason we
On Thu, 2008-08-28 at 15:23 +0800, Phoenix Kiula wrote:
> But I upgraded to 8.2.9 this morning and have had a major slowdown of
> the DB processes. How do I begin to test what is going wrong?
After restart, OS and PostgreSQL caches are cleaned up -- it might also
slow down PostgreSQL a bit.
Regar
On 8/28/08, Andreas Kretschmer <[EMAIL PROTECTED]> wrote:
>
> Please show us a EXPLAIN ANALYSE for this query. Btw, why
> random_page_cost=2? (your other post)
The EXPLAIN ANALYZE shows that it's using an INDEX and getting one
row! So I know the SQL is right.
Could it be that the SQL queries be
1 - 100 of 111 matches
Mail list logo
