Friday, May 9, 2008

Re: [PATCHES] Verified fix for Bug 4137

Simon Riggs wrote:
> I've extended the patch without introducing another new status variable,
> which was my original concern with what you suggested previously.

Ok, that'll work. Committed, thanks. I changed the sanity check that
xlogfname > restore point into an Assert, though, because that's a sign
that something's wrong.

I noted that there's no reason why the truncation point calculation had
to be moved outside the case-statement, but it does look better that way
so I did apply that change.

--
Heikki Linnakangas
EnterpriseDB

http://www.enterprisedb.com

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [HACKERS] constraint exclusion analysis caching

On Fri, 2008-05-09 at 08:47 -0400, Andrew Dunstan wrote:

> Yesterday a client and I were sad to discover that the overhead of
> constraint exclusion is apparently O(n) in the number of partitions, and
> that where we had ~180 partitions each with a simple constraint (check
> (field = nnn)) the overhead appeared to amount to about 0.25s on some
> quite performant hardware, which is way too high for our application.
> Actual execution of the query in question was talking one tenth of that
> time.
>
> For now we're going to work around this by directing the queries
> directly to the child tables, although this does involve fairly large
> application changes.
>
> However, I wondered if we couldn't mitigate this by caching the results
> of constraint exclusion analysis for a particular table + condition. I
> have no idea how hard this would be, but in principle it seems silly to
> keep paying the same penalty over and over again.

I think the only way forward is to put an index across the constraints,
to allow the exclusion time to be O(logN).

Currently the constraints are all independent of each other and can even
overlap. So we would need a way of

* confirming that the partitions are non-overlapping
* defining some structure to them, to allow them to be organised in a
sequence that allows either a bsearch or an index to exist

The latter requires some kind of top-down definition, which hopefully is
on the way from Gavin.

This can then allow exclusion to take place dynamically within the
executor, to allow a form of nested join.

My other requirements are noted here...
http://wiki.postgresql.org/wiki/Image:Partitioning_Requirements.pdf

I'm not working on this at all at the moment.

--
Simon Riggs
2ndQuadrant

http://www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [DOCS] .backup files not needed?

Simon Riggs <simon@2ndquadrant.com> writes:
> Just had questions from a replication user about why the .backup file is
> described as "can ordinarily be ignored" and is considered optional by
> recovery also even when pg_start_backup() was used.

What it says is that the second part of the filename can ordinarily
be ignored. I don't know why neither he nor you managed to parse the
sentence correctly. Feel free to propose a rewording, but removing
information doesn't sound like a solution.

regards, tom lane

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

[COMMITTERS] pgsql: Fix incorrect archive truncation point calculation in the %r

Log Message:
-----------
Fix incorrect archive truncation point calculation in the %r recovery_command
parameter. This fixes bug 4137 reported by Wojciech Strzalka, where a WAL
file is deleted too early when starting the recovery of a warm standby server.

Also add a sanity check in pg_standby so that it will refuse to delete anything
earlier than the file being restored, and improve the debug message in case
nothing is deleted.

Simon Riggs. Backpatch to 8.3, which is where %r was introduced.

Tags:
----
REL8_3_STABLE

Modified Files:
--------------
pgsql/contrib/pg_standby:
pg_standby.c (r1.10 -> r1.10.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_standby/pg_standby.c?r1=1.10&r2=1.10.2.1)
pgsql/src/backend/access/transam:
xlog.c (r1.292.2.1 -> r1.292.2.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.292.2.1&r2=1.292.2.2)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

[COMMITTERS] pgsql: Fix incorrect archive truncation point calculation in the %r

Log Message:
-----------
Fix incorrect archive truncation point calculation in the %r recovery_command
parameter. This fixes bug 4137 reported by Wojciech Strzalka, where a WAL
file is deleted too early when starting the recovery of a warm standby server.

Also add a sanity check in pg_standby so that it will refuse to delete anything
earlier than the file being restored, and improve the debug message in case
nothing is deleted.

Simon Riggs. Backpatch to 8.3, which is where %r was introduced.

Modified Files:
--------------
pgsql/contrib/pg_standby:
pg_standby.c (r1.10 -> r1.11)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_standby/pg_standby.c?r1=1.10&r2=1.11)
pgsql/src/backend/access/transam:
xlog.c (r1.300 -> r1.301)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.300&r2=1.301)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [pgsql-www] IM

Am Donnerstag, 8. Mai 2008 schrieb Dave Page:
> It is documented on PMT, but unfortunately not in a particularly
> useful way (it basically says - jabber(.postgresql.org) is a jabber
> server!!).
>
> I'm happy for us to take a more active role managing it, but we could
> use some details on how it all works on PMT.

I see this PMT thing being mentioned on occasion, but it is itself not
documented any place I could find. Could you shed some light on it?

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [ADMIN] Error during Installation of postgresql-nls-string-8.02.

"Praveen Kumar \(TUV\)" <praveen.k@renaissance-it.com> writes:
> When I try to install postgresql-nls-string-8.02 in postgresql-8.2.5 or
> in higher version ,it give the following error.

> ERROR: incompatible library "/home/local/pgsql/lib/nls_string.so":
> missing magic block

You need an up-to-date version of postgresql-nls-string. In general,
loadable modules always have to be recompiled (at least) for each new
major PG release.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches

KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
> Some of the test fails contains minor differences from expected results, like:

> | SELECT '' AS "xxx", *
> | FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
> | xxx | a | b | c | d
> | -----+---+---+------+---
> | - | 0 | | zero |
> | | 2 | 3 | two | 2
> | | 4 | 1 | four | 2
> | + | 0 | | zero |
> | (3 rows)

Yeah, I remember those. What needs to be looked at here is *why* the
output is changing. For a patch that allegedly does not touch the
planner, it's fairly disturbing that you don't get the same results.

> and, some of them are trivial ones, like:

> | SELECT p1.oid, p1.typname
> | FROM pg_type as p1
> | WHERE p1.typtype in ('b','e') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
> | (SELECT 1 FROM pg_type as p2
> | WHERE p2.typname = ('_' || p1.typname)::name AND
> | p2.typelem = p1.oid and p1.typarray = p2.oid);
> | - oid | typname
> | ------+---------
> | - 210 | smgr
> | - 705 | unknown
> | -(2 rows)
> | + oid | typname
> | +------+----------------
> | + 210 | smgr
> | + 705 | unknown
> | + 3403 | security_label
> | +(3 rows)

Are you sure that the security_label type should not have an array type?
I do not offhand see a good argument for that. If it really shouldn't,
we can change the expected output here, but you've got to make that
case first.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] reproducible database crash with simple sql command on postgres 8.3.1

<Christian.Strobl@dlr.de> writes:
> PANIK: ERROR_STACK_SIZE exceeded

> the rest of the message is unfortunately in german then i have to
> restart the postgres-service manually this error is very easy
> reproducible at my environment

What this typically means is that you've got an encoding/locale
configuration problem. The thing is trying to report some error
or other, and it discovers that the error text is badly encoded
according to the current database encoding, and it tries to
report that, only *that* error text is badly encoded too, so
it tries to report that, getting another error, etc, until the
error recovery stack overflows.

The hope of getting rid of this scenario is one reason why we locked
down encoding/locale combinations in 8.3, so I'm disappointed that
you're still able to make it happen. You didn't say what settings
you are using though. Could we see

show lc_messages;
show lc_ctype;
show server_encoding;
show client_encoding;

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] constraint exclusion analysis caching

"Andrew Dunstan" <andrew@dunslane.net> writes:

> Actual execution of the query in question was talking one tenth of that
> time.
>...
> but in principle it seems silly to keep paying the same penalty over and
> over again.

I would think constraint_exclusion only really makes sense if you're spending
a lot more time executing than planning queries. Either that means you're
preparing queries once and then executing them many many times or you're
planning much slower queries where planning time is insignificant compared to
the time to execute them.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgus-board] Fwd: Fw: Expenses for budget

For non-profit, do all the figures have to zero out, or can a
(positive) balance be carried over?

---Michael Brewer
mbrewer@gmail.com

--
Sent via pgus-board mailing list (pgus-board@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgus-board

Re: [PERFORM] "append" takes a lot of time in a query

Frank Dekervel <frank.dekervel@smartlounge.be> writes:
> this turns this query in an append of a whole lot of seq scan/ index scan's.
> These scans are predictably quick, but the "append" takes 5 seconds (and the
> numbers of the scans do not add up to the append actual time)

It says 5 milliseconds, not 5 seconds.

> Does someone have an explanation / possible solution for this performance ?

Rethink your schema --- this is pushing the inheritance feature far
beyond what it's designed to do.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [pgsql-www] IM

On Friday 09 May 2008 08:37:34 Alvaro Herrera wrote:
> Magnus Hagander wrote:
> > Josh Berkus wrote:
> > > Bizarre. It's pretty regular. Happens to me pretty much every day
> > > -- I get kicked off and can't login again for 20min or so.
> >
> > It doesn't happen to me either. What does happen, is that if I'm for
> > some reason kicked off the network itself (say the WWAN drops or
> > something like that), I get kicked off for 20 mins or so (error saying
> > something about "conflict"). But as long as my own local network
> > connection is stable, I don't get kicked off.
>
> I have seen this "conflict" error too. I think it happens when the
> server thinks your previous connection is still open. My guess is that
> those 20 minutes are the time it takes to notice it's gone -- maybe a
> keepalive timeout or something like that.
>

Yes, conflict mean you are logged onto the network already (or at least it
thinks you are). Now I *think* there is a way to configure the server to boot
off existing connections when you attempt to login again rather than refuse
the new attempt; not sure of that, but it might be worth looking into.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [GENERAL] Using Epoch to save timestamps in 4 bytes?

"Merlin Moncure" <mmoncure@gmail.com> writes:
> There is nothing wrong with storing int4 epoch in your tables to save a
> little space if that suits your application.

... Just make sure you're safely away from the scene of the crime
before 2038 ...

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] constraint exclusion analysis caching

Andrew Dunstan <andrew@dunslane.net> writes:
> Yesterday a client and I were sad to discover that the overhead of
> constraint exclusion is apparently O(n) in the number of partitions, and
> that where we had ~180 partitions each with a simple constraint (check
> (field = nnn)) the overhead appeared to amount to about 0.25s on some
> quite performant hardware, which is way too high for our application.

I would think that any sort of formal partitioning feature would fix the
problem, because the planner would understand directly about
partitioning instead of having to prove the correctness of not scanning
each one of the other 179 partitions. The existing feature is cool in
the sense of obtaining useful behavior from generalized spare parts,
but it was never designed or expected to give great planning speed
with large numbers of partitions. TFM points out that constraint
exclusion cannot scale beyond perhaps a hundred partitions ...

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[PATCHES] New flex warnings

diff --git a/src/backend/bootstrap/bootscanner.l b/src/backend/bootstrap/bootscanner.l
index 89772f8..f82e0e9 100644
--- a/src/backend/bootstrap/bootscanner.l
+++ b/src/backend/bootstrap/bootscanner.l
@@ -52,6 +52,7 @@ static int yyline = 1; /* line number for error reporting */
%option 8bit
%option never-interactive
%option nodefault
+%option noinput
%option nounput
%option noyywrap
%option prefix="boot_yy"
diff --git a/src/backend/parser/scan.l b/src/backend/parser/scan.l
index 99f8546..012b120 100644
--- a/src/backend/parser/scan.l
+++ b/src/backend/parser/scan.l
@@ -101,6 +101,7 @@ static unsigned char unescape_single_char(unsigned char c);
%option 8bit
%option never-interactive
%option nodefault
+%option noinput
%option nounput
%option noyywrap
%option prefix="base_yy"
diff --git a/src/backend/utils/misc/guc-file.l b/src/backend/utils/misc/guc-file.l
index 3a4b63b..706da59 100644
--- a/src/backend/utils/misc/guc-file.l
+++ b/src/backend/utils/misc/guc-file.l
@@ -59,6 +59,7 @@ static char *GUC_scanstr(const char *s);
%option 8bit
%option never-interactive
%option nodefault
+%option noinput
%option nounput
%option noyywrap
%option prefix="GUC_yy"
diff --git a/src/bin/psql/psqlscan.l b/src/bin/psql/psqlscan.l
index 02c7f40..965c41b 100644
--- a/src/bin/psql/psqlscan.l
+++ b/src/bin/psql/psqlscan.l
@@ -125,6 +125,7 @@ static void emit(const char *txt, int len);
%option 8bit
%option never-interactive
%option nodefault
+%option noinput
%option nounput
%option noyywrap

diff --git a/src/interfaces/ecpg/preproc/pgc.l b/src/interfaces/ecpg/preproc/pgc.l
index bd0a7d2..9b6feb7 100644
--- a/src/interfaces/ecpg/preproc/pgc.l
+++ b/src/interfaces/ecpg/preproc/pgc.l
@@ -76,6 +76,7 @@ static struct _if_value
%option 8bit
%option never-interactive
%option nodefault
+%option noinput
%option noyywrap

%option yylineno
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index 0ec8d53..fb9ef4b 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -47,6 +47,7 @@ bool plpgsql_SpaceScanned = false;
%option 8bit
%option never-interactive
%option nodefault
+%option noinput
%option nounput
%option noyywrap
%option prefix="plpgsql_base_yy"
With GCC 4.3, I get warnings from every flex scanner that 'input' is defined
but not used. This can be solved by adding %option noinput. I tested this
option with a current flex and with the old 2.5.4a; both accept it. See
attached patch. Does anyone see problems with this?

Re: [HACKERS] Table inheritance surprise

On May 7, 2008, at 4:52 PM, David Fetter wrote:
> When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't
> include foreign key constraints (8.3.1). I believe this is surprising
> behavior, but maybe not a bug, so I'd like to propose another bit of
> syntactic sugar, namely
>
> LIKE [INCLUDING FOREIGN KEYS]
>
> which would do what it looks like it does.


It's surprising to me that INCLUDING CONSTRAINTS doesn't include FK
constraints... is there a reason not to? Perhaps we should just
change INCLUDING CONSTRAINTS to do that...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [GENERAL] statistics collector process is thrashing my cpu

William Temperley wrote:
> On Thu, May 8, 2008 at 6:14 PM, Magnus Hagander <magnus@hagander.net>
> wrote:
> > William Temperley wrote:
> >> > >
> >> > > Any ideas why this might be happening, and how I can stop it?
> >> >
> >> > It'd be interesting to know what the stats collector is actually
> >> > doing. Could you, using Process Explorer or a debugger, get a
> >> > stack trace from that process while it's in the trashing state?
> >> >
> >> > //Magnus
> >> >
> >>
> >> Certainly, but I'll have to wait 'til it does it again, it doesn't
> >> happen all the time.
> >> What would you like to know from Process Explorer?
> >
> > Get the backtrace from hung process. Find the process in the list,
> > open it. Go to the tab "Threads", find the thread that's using a
> > lot of CPU (or at least has a lot of ocntext switchs), and click
> > the Stack button. That should give you a window with a backtrace.
> >
> > //Magnus
> >
>
> Ok, got the little blighter. Below are 4 stack traces taken at random
> times. Doesn't seem to be doing much I/O (16 reads and 167 other in 3
> hours). The memory usage seems to be static (3.7MB) even with high
> database usage (vacuum, read, update).

Hmm. They all show the same function, but it's not a function being
used in the stats collector. I think I missed a step in the
instructions - you need to download the symbols for the server (make
sure you get the same version!) and configure process explorer to use
those (IIRC, it can use detached symbols), then do the same things
again. Sorry for missing that in the first instructions!

//Magus

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Is this a bug? (changing sequences in default value)

On Fri, May 9, 2008 at 8:55 AM, Fernando Schapachnik
<fernando@mecon.gov.ar> wrote:
> En un mensaje anterior, Merlin Moncure escribió:
>> On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
>> <fschapachnik@mecon.gov.ar> wrote:
>> > Pg 8.1.11, I try to change sequences as default value of a table, then
>> > remove old sequence:
>> >
>> > # \d table1
>> > Table "table1"
>> > Column | Type | Modifiers
>> > --------+---------+---------------------------------------------------------------
>> > id | integer | not null default nextval('table1_id_seq'::regclass)
>> > nombre | text | not null
>> > Indexes:
>> > "table1_pkey" PRIMARY KEY, btree (id)
>> >
>> > # ALTER TABLE table1 alter column id set default nextval('newseq_id_seq');
>> > ALTER TABLE
>> >
>> > # \d table1
>> > Table "table1"
>> > Column | Type | Modifiers
>> > --------+---------+---------------------------------------------------------------
>> > id | integer | not null default nextval('newseq_id_seq'::regclass)
>> > nombre | text | not null
>> > Indexes:
>> > "table1_pkey" PRIMARY KEY, btree (id)
>> >
>> > # drop SEQUENCE table1_id_seq ;
>> > ERROR: cannot drop sequence table1_id_seq because table
>> > table1 column id requires it
>> > HINT: You may drop table table1 column id instead.
>> >
>> > Am I doing something wrong?
>>
>> yes and no when you created the table initially you probably made it
>> a 'serial' column which set up the ownership that prevents the drop
>> operation. that ownership did not go away when you altered the
>> default to the new serial.
>>
>> to fix this,
>> alter sequence sequence table1_id_seq owned by none; -- now you can drop
>
> Hi, Merlin. Thanks for the tip, but it doesn't work. Every variation
> of this syntax I tried gives me error as, apparently, it should:
>
> \h ALTER SEQUENCE
> Command: ALTER SEQUENCE
> Description: change the definition of a sequence generator
> Syntax:
> ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
> [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
> MAXVALUE ]
> [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

oop, you are using 8.1 :-). This was added in a later version. drop
sequence ... cascade should probably work. you can try it out in a
transaction to be sure.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[COMMITTERS] libpqtypes - libpqtypes: added stdarg.h to libpqtypes.h and remove it

Log Message:
-----------
added stdarg.h to libpqtypes.h and remove it from libpqtypes-int.h

Modified Files:
--------------
libpqtypes/src:
libpqtypes-int.h (r1.3 -> r1.4)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/libpqtypes-int.h.diff?r1=1.3&r2=1.4)
libpqtypes.h (r1.6 -> r1.7)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/libpqtypes.h.diff?r1=1.6&r2=1.7)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [HACKERS] Auto-updated fields

On Thu, May 08, 2008 at 08:44:46AM +0200, Martijn van Oosterhout wrote:
> On Wed, May 07, 2008 at 03:04:49PM -0700, David Fetter wrote:
> > 1. Create a generic (possibly overloaded) trigger function,
> > bundled with PostgreSQL, which sets a field to some value. For
> > example, a timestamptz version might set the field to now().
>
> Doesn't the SQL standard GENERATED BY functionality work for this?
> Or won't that handle updates?

It appears to, at least according to 6WD2_02_Foundation_2007-12.pdf :)

4.14.8 Base columns and generated columns

A column of a base table is either a base column or a generated
column. A base column is one that is not a generated column. A
generated column is one whose values are determined by evaluation
of a generation expression, a <value expression> whose declared
type is by implication that of the column. A generation expression
can reference base columns of the base table to which it belongs
but cannot otherwise access SQL data. Thus, the value of the field
corresponding to a generated column in row R is determined by the
values of zero or more other fields of R. A generated column GC
depends on each column that is referenced by a <column reference>
in its generation expression, and each such referenced column is a
parametric column of GC.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-es-ayuda] Aumentar el desempeƱo del servidor

On Sun, May 4, 2008 at 4:22 PM, Antonio Salas Mena
<antoniosm1966@hotmail.com> wrote:
> Hola,
>
> Estoy con un problema grave de desempeño del servidor.
>
...
> usuarios.....: aproximadamente 140

> max_connections = 500 # (change requires restart)

y si son 140 usuarios (mas adelante indicas que son max 20
concurrentes) porque pones maximo de conexiones a 500?


> temp_buffers = 56MB # min 800kB
...
> work_mem = 512MB # min 64kB

soy el unico al que se le erizaron los cabellos por estos valores?

--
Atentamente,
Jaime Casanova
Soporte de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [HACKERS] constraint exclusion analysis caching

On Fri, 2008-05-09 at 08:47 -0400, Andrew Dunstan wrote:
> However, I wondered if we couldn't mitigate this by caching the results
> of constraint exclusion analysis for a particular table + condition. I
> have no idea how hard this would be, but in principle it seems silly to
> keep paying the same penalty over and over again.

This would be a perfect candidate for the plan-branch based on actual
parameters capability, in association with globally cached plans
mentioned here:

http://archives.postgresql.org/pgsql-hackers/2008-04/msg00920.php

Cheers,
Csaba.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] Is this a bug? (changing sequences in default value)

En un mensaje anterior, Merlin Moncure escribió:
> On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
> <fschapachnik@mecon.gov.ar> wrote:
> > Pg 8.1.11, I try to change sequences as default value of a table, then
> > remove old sequence:
> >
> > # \d table1
> > Table "table1"
> > Column | Type | Modifiers
> > --------+---------+---------------------------------------------------------------
> > id | integer | not null default nextval('table1_id_seq'::regclass)
> > nombre | text | not null
> > Indexes:
> > "table1_pkey" PRIMARY KEY, btree (id)
> >
> > # ALTER TABLE table1 alter column id set default nextval('newseq_id_seq');
> > ALTER TABLE
> >
> > # \d table1
> > Table "table1"
> > Column | Type | Modifiers
> > --------+---------+---------------------------------------------------------------
> > id | integer | not null default nextval('newseq_id_seq'::regclass)
> > nombre | text | not null
> > Indexes:
> > "table1_pkey" PRIMARY KEY, btree (id)
> >
> > # drop SEQUENCE table1_id_seq ;
> > ERROR: cannot drop sequence table1_id_seq because table
> > table1 column id requires it
> > HINT: You may drop table table1 column id instead.
> >
> > Am I doing something wrong?
>
> yes and no when you created the table initially you probably made it
> a 'serial' column which set up the ownership that prevents the drop
> operation. that ownership did not go away when you altered the
> default to the new serial.
>
> to fix this,
> alter sequence sequence table1_id_seq owned by none; -- now you can drop

Hi, Merlin. Thanks for the tip, but it doesn't work. Every variation
of this syntax I tried gives me error as, apparently, it should:

\h ALTER SEQUENCE
Command: ALTER SEQUENCE
Description: change the definition of a sequence generator
Syntax:
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

Thanks again!

Fernando.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Epoch to save timestamps in 4 bytes?

On Fri, May 9, 2008 at 3:15 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, May 8, 2008 at 10:00 PM, Bruce Momjian <bruce@momjian.us> wrote:
>>
>> Our timestamp has a much larger range than a 4-byte time_t, docs say:
>>
>> <entry>4713 BC</entry>
>> <entry>294276 AD</entry>
>
> Which is normally great. Doesn't it have greater precision in the
> modern era or something like that?
>
> If you compile for integer dates do they have the same range?

no. that's actually the integer range. the float range is 4713 BC to
5874897 AD. Of course, at the outer ranges of the scale, the
precision is going to be really lousy.

Anyways, to the OP, a 4 byte time_t is to small a type to be the
timestamp. There are just too many things that need greater
range/precision to make it the default. Also, postgresql does not
store epoch, but it's own custom type with its own bias, etc. There
is nothing wrong with storing int4 epoch in your tables to save a
little space if that suits your application.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[HACKERS] constraint exclusion analysis caching

Yesterday a client and I were sad to discover that the overhead of
constraint exclusion is apparently O(n) in the number of partitions, and
that where we had ~180 partitions each with a simple constraint (check
(field = nnn)) the overhead appeared to amount to about 0.25s on some
quite performant hardware, which is way too high for our application.
Actual execution of the query in question was talking one tenth of that
time.

For now we're going to work around this by directing the queries
directly to the child tables, although this does involve fairly large
application changes.

However, I wondered if we couldn't mitigate this by caching the results
of constraint exclusion analysis for a particular table + condition. I
have no idea how hard this would be, but in principle it seems silly to
keep paying the same penalty over and over again.

Thoughts?

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] Is this a bug? (changing sequences in default value)

On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
<fschapachnik@mecon.gov.ar> wrote:
> Pg 8.1.11, I try to change sequences as default value of a table, then
> remove old sequence:
>
> # \d table1
> Table "table1"
> Column | Type | Modifiers
> --------+---------+---------------------------------------------------------------
> id | integer | not null default nextval('table1_id_seq'::regclass)
> nombre | text | not null
> Indexes:
> "table1_pkey" PRIMARY KEY, btree (id)
>
> # ALTER TABLE table1 alter column id set default nextval('newseq_id_seq');
> ALTER TABLE
>
> # \d table1
> Table "table1"
> Column | Type | Modifiers
> --------+---------+---------------------------------------------------------------
> id | integer | not null default nextval('newseq_id_seq'::regclass)
> nombre | text | not null
> Indexes:
> "table1_pkey" PRIMARY KEY, btree (id)
>
> # drop SEQUENCE table1_id_seq ;
> ERROR: cannot drop sequence table1_id_seq because table
> table1 column id requires it
> HINT: You may drop table table1 column id instead.
>
> Am I doing something wrong?

yes and no when you created the table initially you probably made it
a 'serial' column which set up the ownership that prevents the drop
operation. that ownership did not go away when you altered the
default to the new serial.

to fix this,
alter sequence sequence table1_id_seq owned by none; -- now you can drop

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-www] IM

Tino Wildenhain wrote:

> personally I'm wondering whats really the use of IM when we already have
> IRC available? I mean, after all IM's capabilities are just a subset of
> IRC's. I use IM's to have contact with non IRC-People, but does this
> service really make sense for postgres?

Personally I never login on the IRC channel.

--
Alvaro Herrera

http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [PATCHES] [NOVICE] encoding problems

Bruce Momjian escribió:
> Guillaume Smet wrote:
> > On Thu, May 8, 2008 at 9:11 PM, Bruce Momjian <bruce@momjian.us> wrote:

> > As I mentioned it before, is there any chance for this fix to be
> > backported to 8.3 branch? IMHO it's a usability regression.
>
> No, we don't change behaviors in back branches unless we get lots of
> complaints, and we haven't in this case.

complaints++

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [pgsql-www] IM

Magnus Hagander wrote:
> Josh Berkus wrote:

> > Bizarre. It's pretty regular. Happens to me pretty much every day
> > -- I get kicked off and can't login again for 20min or so.
>
> It doesn't happen to me either. What does happen, is that if I'm for
> some reason kicked off the network itself (say the WWAN drops or
> something like that), I get kicked off for 20 mins or so (error saying
> something about "conflict"). But as long as my own local network
> connection is stable, I don't get kicked off.

I have seen this "conflict" error too. I think it happens when the
server thinks your previous connection is still open. My guess is that
those 20 minutes are the time it takes to notice it's gone -- maybe a
keepalive timeout or something like that.

As for the kicks, I guess someone should check the logs.

--
Alvaro Herrera

http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Re: [pgsql-www] IM

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

- --On Thursday, May 08, 2008 20:13:49 -0700 Josh Berkus <josh@agliodbs.com>
wrote:

> All,
>
>> > > > stop kicking me off for 1/2 hour twice a day.
>> > >
>> > > I don't experience this behavior. Does anyone else?
>> >
>> > I don't.
>>
>> me niether.
>
> Bizarre. It's pretty regular. Happens to me pretty much every day -- I get
> kicked off and can't login again for 20min or so.

Is it consistent as far as time of day is concerned, or totally random?

- --
Marc G. Fournier Hub.Org Hosting Solutions S.A. (http://www.hub.org)
Email . scrappy@hub.org MSN . scrappy@hub.org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.8 (FreeBSD)

iEYEARECAAYFAkgkQJQACgkQ4QvfyHIvDvPqYACg1f+K1mo2b2bKyU1f7OrJV+9t
IqMAn3VGd1y3SWeM77FIQbIWd7MPHTNQ
=xKqw
-----END PGP SIGNATURE-----


--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

[COMMITTERS] libpqtypes - libpqtypes: added docs at top of error.c

Log Message:
-----------
added docs at top of error.c

Modified Files:
--------------
libpqtypes/src:
error.c (r1.3 -> r1.4)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/error.c.diff?r1=1.3&r2=1.4)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

[COMMITTERS] libpqtypes - libpqtypes: added source file name and small description at

Log Message:
-----------
added source file name and small description at top of every file

Modified Files:
--------------
libpqtypes/src:
array.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/array.c.diff?r1=1.2&r2=1.3)
datetime.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/datetime.c.diff?r1=1.2&r2=1.3)
error.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/error.c.diff?r1=1.2&r2=1.3)
exec.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/exec.c.diff?r1=1.2&r2=1.3)
geo.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/geo.c.diff?r1=1.2&r2=1.3)
getaddrinfo.h (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/getaddrinfo.h.diff?r1=1.2&r2=1.3)
handler.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/handler.c.diff?r1=1.2&r2=1.3)
hooks.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/hooks.c.diff?r1=1.2&r2=1.3)
libpqtypes-int.h (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/libpqtypes-int.h.diff?r1=1.2&r2=1.3)
libpqtypes.h (r1.5 -> r1.6)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/libpqtypes.h.diff?r1=1.5&r2=1.6)
misc.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/misc.c.diff?r1=1.2&r2=1.3)
network.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/network.c.diff?r1=1.2&r2=1.3)
numerics.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/numerics.c.diff?r1=1.2&r2=1.3)
param.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/param.c.diff?r1=1.2&r2=1.3)
port.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/port.c.diff?r1=1.2&r2=1.3)
record.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/record.c.diff?r1=1.2&r2=1.3)
utils.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/utils.c.diff?r1=1.2&r2=1.3)
varlena.c (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/varlena.c.diff?r1=1.2&r2=1.3)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [pgsql-es-ayuda] Problemas al arrancar postgres como usuario 'postgres'

tu primer problema se debe seguramente a que la cuenta 'postgres' este bloqueda
o en su defecto a caducado el password.

El día 9 de mayo de 2008 6:09, Gabriela Messner <gabmessner@gmail.com> escribió:
> Hola, tengo instalado un PostgreSQL 8.2 sobre Win XP con una cuenta de
> usuario 'postgres' asociada. Como es una máquina de escritorio al finalizar
> la jornada la apago. El problema surge cuando tras varios días arrancando la
> máquina, en uno de los arranques el servicio PostgreSQL Database Server no
> se inicia y al intentar iniciarlo como usuario 'postgres' me devuelve un
> error y la única opción que me queda es arrancarlo como cuenta del sistema
> local. ¿Cómo puedo corregir esto para que se incie sesión correctamente como
> 'postgres'?.
>
> Otra problema que tengo es al crear una tabla y poblarla posteriormente con
> el comando COPY. Una vez la tabla está poblada la abro con 'PGAdmin III Edit
> Data' pero no me permite editar manualmente los registros. Este problema
> sólo lo detecto con el comando COPY; cuando pueblo la tabla con el comando
> shp2pgsql (extensión Postgis) puedo editar los registros de forma correcta.
>
>
> Gracias por adelantado.
>

--
Atentamente,
Moisés Alberto Lindo Gutarra
Asesor - Desarrollador Java / Open Source
Linux Registered User #431131 - http://counter.li.org/
Cel: (511) 995081720
MSN: mlindo@tumisolutions.com
--
TIP 7: no olvides aumentar la configuración del "free space map"

[pgadmin-hackers] SVN Commit by dpage: r7285 - branches/REL-1_8_0_EDB/pgadmin3/pgadmin/debugger

Author: dpage

Date: 2008-05-09 12:22:29 +0100 (Fri, 09 May 2008)

New Revision: 7285

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7285&view=rev

Log:
Add missing c_str()

Modified:
branches/REL-1_8_0_EDB/pgadmin3/pgadmin/debugger/ctlCodeWindow.cpp

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

[pgadmin-hackers] SVN Commit by dpage: r7284 - branches/REL-1_8_0_PATCHES/pgadmin3/pgadmin/debugger

Author: dpage

Date: 2008-05-09 12:20:48 +0100 (Fri, 09 May 2008)

New Revision: 7284

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7284&view=rev

Log:
Add missing c_str()

Modified:
branches/REL-1_8_0_PATCHES/pgadmin3/pgadmin/debugger/ctlCodeWindow.cpp

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

[pgadmin-hackers] SVN Commit by dpage: r7283 - trunk/pgadmin3/pgadmin/debugger

Author: dpage

Date: 2008-05-09 12:19:24 +0100 (Fri, 09 May 2008)

New Revision: 7283

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7283&view=rev

Log:
Add missing c_str()

Modified:
trunk/pgadmin3/pgadmin/debugger/ctlCodeWindow.cpp

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

[pgsql-es-ayuda] Problemas al arrancar postgres como usuario 'postgres'

Hola, tengo instalado un PostgreSQL 8.2 sobre Win XP con una cuenta de usuario 'postgres' asociada. Como es una mĆ”quina de escritorio al finalizar la jornada la apago. El problema surge cuando tras varios dĆ­as arrancando la mĆ”quina, en uno de los arranques el servicio PostgreSQL Database Server no se inicia y al intentar iniciarlo como usuario 'postgres' me devuelve un error y la Ćŗnica opciĆ³n que me queda es arrancarlo como cuenta del sistema local. ¿CĆ³mo puedo corregir esto para que se incie sesiĆ³n correctamente como 'postgres'?.

Otra problema que tengo es al crear una tabla y poblarla posteriormente con el comando COPY. Una vez la tabla estĆ” poblada la abro con 'PGAdmin III Edit Data' pero no me permite editar manualmente los registros. Este problema sĆ³lo lo detecto con el comando COPY; cuando pueblo la tabla con el comando shp2pgsql (extensiĆ³n Postgis) puedo editar los registros de forma correcta.


Gracias por adelantado.

[ADMIN] Error during Installation of postgresql-nls-string-8.02.

Hello,

When I try to install postgresql-nls-string-8.02 in postgresql-8.2.5 or in higher version ,it give the following error.

 

ERROR:  incompatible library "/home/local/pgsql/lib/nls_string.so": missing magic block

HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.

ERROR:  incompatible library "/home/local/pgsql/lib/nls_string.so": missing magic block

HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.

 

Can anybody tell me what steps I should follow to avoid such type of error during installation of nls_string-8.02 in postgresql-8.2.5 or in higher version ?

 

 

[ODBC] [ psqlodbc-Bugs-1010376 ] GUID code missing in convert.c ("Unrecognized C_parameter ..") and fix

Bugs item #1010376, was opened at 2008-04-15 13:02
You can respond by visiting:

http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010376&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Jan-Willem Goossens (jgoossens)
Assigned to: Nobody (None)
Summary: GUID code missing in convert.c ("Unrecognized C_parameter ..") and fix

Initial Comment:
Hi,
Either I'm missing something, or there's the GUID (UUID) implementation missing in ResolveOneParam(..) in convert.c [08.03.0100], since I'm getting "Unrecognized C_parameter type in copy_statement_with_parameters" exceptions with param_ctype == -11 (=SQL_C_GUID).

I admit I dont really know what I'm doing, but adding the following lines seems to work well:

[convert.c, line 3745]
#if (ODBCVER >= 0x0350)
case SQL_C_GUID:
{
SQLGUID *g = (SQLGUID *) buffer;
sprintf (param_string,
"%08lX-%04X-%04X-%02X%02X-%02X%02X%02X%02X%02X%02X",
(unsigned long) g->Data1,
g->Data2, g->Data3,
g->Data4[0], g->Data4[1], g->Data4[2], g->Data4[3],
g->Data4[4], g->Data4[5], g->Data4[6], g->Data4[7]);
}
break;

Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches

Tom Lane wrote:
> KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
>> Tom Lane wrote:
>>> * It does not come close to passing the regression tests. I saw a lot of
>>> ! ERROR: unrecognized node type: 903
>>> which suggests that something's been screwed up about parse analysis
>>> (903 = T_A_Const, which shouldn't get further than parse analysis),
>
>> Could you tell me what queries hit these errors?
>
> I remember seeing it on some EXECUTEs, but you really ought to run the
> tests for yourself. A *minimum* requirement on any submitted patch
> is that it should pass the regression tests.

Some of the test fails contains minor differences from expected results, like:

| SELECT '' AS "xxx", *
| FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
| xxx | a | b | c | d
| -----+---+---+------+---
| - | 0 | | zero |
| | 2 | 3 | two | 2
| | 4 | 1 | four | 2
| + | 0 | | zero |
| (3 rows)

and, some of them are trivial ones, like:

| SELECT p1.oid, p1.typname
| FROM pg_type as p1
| WHERE p1.typtype in ('b','e') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
| (SELECT 1 FROM pg_type as p2
| WHERE p2.typname = ('_' || p1.typname)::name AND
| p2.typelem = p1.oid and p1.typarray = p2.oid);
| - oid | typname
| ------+---------
| - 210 | smgr
| - 705 | unknown
| -(2 rows)
| + oid | typname
| +------+----------------
| + 210 | smgr
| + 705 | unknown
| + 3403 | security_label
| +(3 rows)

Isn't it necessary to consider them as regressions?

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[SQL] execute plpgsl like "normal" sql

Hi

Is there a possibility to execute pl/pgsql syntax like normal sql simmilar to sql+ from oracle?

The question is: if I want to execute soemthing like this:
--File foo.sql:
begin
for id in select id from mytable loop
update myothertable set payed = true where fk_id = id;
end loop;
end;

Do I have to create a procedure or can I simply run the above statement via
psql -D foo < foo.sql

Reason is, that I create pq/psql procedures (more complex than this example) at runtime in my client app and do not want to store the procedures permanent in my database schema. I would like to avoid create function / drop function statements.

Thanks
Chris
--
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [GENERAL] timestamps-accuracy

On Fri, May 09, 2008 at 04:59:30AM -0400, Justin wrote:
> generail this don't hurt us but we have some needs with data coming from
> manufacturing testing applications that needs to keep the timestamps to
> .000,000,001 aka 1 nano second.
> what would be the easiest way to do this?

I would suggest storing it as epoch time (seconds since X) in a numeric
fields and create some helper functions to get data in and out in the
right format. Or a timestamp(0) column with an additional nano-second
column.

Depending on how often you use it it may be interesting to create a
specific type, but this may come close enough.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [pgadmin-support] Editing Views with EditGrid

Hi Dave,

You are right about the complexity of rules and triggers. But isn't that
the same with tables as well? Triggers and rules also work on plain
tables and may effect other columns in the same table and other records
as well. So in my opinion there isn't much difference between tables and
views in this respect. There are also the default values that currently
don't reflect immediately after one edited a record in the table.

I don't have a clean and easy solution as well. But one might think
about a GUI where one lets the user editing the table or view decide
whether he wants to reload the current record or the whole table/view
after each edit. Per default it wouldn't update, but if the user enables
updating of the just edited record or reloading the whole table/view it
would.

I can definitely see value where the record(s) just edited would
automatically reload after being inserted/updated into the table.
Reloading just these records would not be a performance problem. I agree
that reloading the whole table after each edit would be a performance
problem. Again - not much difference between a table and a view. Am I wrong?

Regarding the views: one could test if the view has at least the insert,
update and delete rules present before enabling the editing of the views.

What do you think?

Andreas

Dave Page wrote:
> On Tue, May 6, 2008 at 9:05 PM, Andreas Neumann <a.neumann@carto.net> wrote:
>> Hi Dave,
>>
>> Thank you for letting me know about the situation. Can you estimate how
>> much work it would be to implement editable views? Maybe based on the
>> experience with the editable tables? Maybe we can find a few interested
>> parties paying for the development of this feature?
>
> The problem with editing views is not the editing (the code for the
> tables will do the job just fine with a few minor changes in most
> cases). The problem comes where you have rules which implement
> non-trivial updates - for example; consider a view with a total
> column, an item price, and a quantity. If you update the quantity,
> there is no straightforward way for pgAdmin to understand that the
> total price will be automatically updated. In fact, it won't even know
> that the total column cannot be manually edited.
>
> This gets even worse, because the rules may easily affect different
> rows from that being edited, or even entirely unrelated data. The only
> safe way to manage that that I can see would be to refresh the entire
> data set following each edit - and that would be unusably slow.
>
> I'm happy to entertain proposals that might work, but this a
> non-trivial problem, caused largely by Postgres' superior flexibility
> compared to most other DBMSs. It isn't gonna be easy to fix.
>
>

--

--
Andreas Neumann
Böschacherstrasse 6
CH-8624 Grüt (Gossau ZH)
Switzerland
Phone: ++41-44-2736668
Email: a.neumann@carto.net

Web: http://www.carto.net/neumann/
SVG Examples: http://www.carto.net/papers/svg/samples/
SVG.Open: http://www.svgopen.org/

--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: [COMMITTERS] pgsql: Have numeric 0 ^ 4.3 return 1, rather than an error, and have 0 ^

On Thu, 2008-05-08 at 18:34 -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > momjian@postgresql.org (Bruce Momjian) writes:
> > > Have numeric 0 ^ 4.3 return 1, rather than an error, and have 0 ^ 0.0
> > > return 1, rather than error.
> >
> > This is wrongly described, and the implementation is still not correct
> > either, because it should throw an error for negative exponents.
> > Would you please *read* that wikipedia page you keep citing?
> > http://en.wikipedia.org/wiki/Exponentiation#Powers_of_zero
>
> I think this is fixed in the version I just committed:
>
> test=> select 0 ^ (-1);
> ERROR: invalid argument for power function
> test=> select 0 ^ (-1.0);
> ERROR: invalid argument for power function

Hopefully this only occurs for 0 ^ (n)? A negative exponent isn't a
problem for y ^ x when y <> 0 and x < 0. Just checking you don't just
throw out an error for any negative exponent, which is what "invalid
argument" sounds like, to me.

Wikipedia says that exponentiation of zero to a negative power implies
division by zero, so shouldn't we throw a "division by zero" error?

--
Simon Riggs
2ndQuadrant

http://www.2ndQuadrant.com


--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Ok, I've tried everything, and the planner keeps choosing index scans
when it shouldnt.

Is there a way to disable index scans?


Antoine

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance