PostgreSQL: May 11, 2008

Sunday, May 11, 2008

Re: [GENERAL] Scripting function definitions as SQL?

Yes, but I'm doing this from a Delphi program in Windows and that's
why I'm looking for a solution that's SQL-based.

It would be nice if one of the system catalog views handled it.

> hmmm .. "additional coding" seems a bit too much for a simple thing like
> this:
> pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'
>
> of course it would be cool to have switch to do it, but hey - it hardly
> even qualifies as one-liner. it's more "an expression" than code.

On Sun, May 11, 2008 at 11:43 AM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Sun, May 11, 2008 at 11:28:37AM -0700, Postgres User wrote:
> > Unfortunately I didn't see a way to tell pg_dump to dump only objects
> > of a specific type, like functions or sequences. It requires
> > additional coding to parse the output and that's less than ideal...
>
> hmmm .. "additional coding" seems a bit too much for a simple thing like
> this:
> pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'
>
> of course it would be cool to have switch to do it, but hey - it hardly
> even qualifies as one-liner. it's more "an expression" than code.
>
>
> --
> quicksil1er: "postgres is excellent, but like any DB it requires a
> highly paid DBA. here's my CV!" :)
> http://www.depesz.com/ - blog dla ciebie (i moje CV)
>

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

Re: [SQL] Difference in columns

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> On Sun, May 11, 2008 at 11:47 PM, Craig Ringer <craig@postnewspapers.com.au>
>> If you cannot assume that, you can use a subquery with limit and order
>> by to obtain the next record:
>>
>> SELECT
>> a.ts,
>> (SELECT b.size FROM x b WHERE b.ts > a.ts ORDER BY b.ts ASC LIMIT 1)
>> - a.size AS difference
>> FROM x a;
>>
>> ... but that'll be really slow for any significant number of entries.

> not really... if you have an index on the TS column.

The OP said this was a view, so it may well not have any easy way to
provide such an index.

regards, tom lane

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

Re: [HACKERS] bloated heapam.h

Alvaro Herrera <alvherre@commandprompt.com> writes:
> So here's a patch (includes the #ifndef FRONTEND hack in htup.h.)

I like this except for the #ifndef FRONTEND hack --- you're going to
need to fix that before applying. I'm good with doing that by pushing
the system attribute numbers into a separate header.

BTW, you didn't compress the patch, which likely explains why it didn't
show up on -hackers. If you want to post a shorter form, I think that
the diffs in the header files are the only interesting part; the ensuing
additions in .c files are just mechanical.

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

[GENERAL] How to create a function with multiple RefCursor OUT parameters

I have the following function:

CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT
o_user refcursor, OUT o_name refcursor)
RETURNS record AS
$BODY$
BEGIN
tcount := tcount + 1;
OPEN o_user FOR SELECT * FROM user_table;
OPEN o_name FOR SELECT * FROM name_table;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE

Question 1: The function is not working with Npgsql .NET data provider.
It did not return a valid .NET DataSet. But the INOUT parameter tcount
works fine. How could I test the above function with SQL in pgAdmin III?
I want to find out if problem is in the function or in the Npgsql.

Question 2: pgAdmin III automatically added "RETURNS record" in the
above function when RETURNS clause is not specified initially. Why is
that? Is this the problem since it returns only single data table with
the following value? How to fix it?

tcount o_user o_name
23 <unnamed portal 1> <unnamed portal 2>

Question 3: I want to return a single DataSet with each OUT RefCursor
map to a DataTable within the DataSet, plus extra OUT parameters for
individual OUT values. How could I create such a function?

Any help is appreciated.

--
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] Scripting function definitions as SQL?

On Sun, May 11, 2008 at 11:28:37AM -0700, Postgres User wrote:
> Unfortunately I didn't see a way to tell pg_dump to dump only objects
> of a specific type, like functions or sequences. It requires
> additional coding to parse the output and that's less than ideal...

hmmm .. "additional coding" seems a bit too much for a simple thing like
this:
pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'

of course it would be cool to have switch to do it, but hey - it hardly
even qualifies as one-liner. it's more "an expression" than code.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

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

Re: [SQL] Difference in columns

On Sun, May 11, 2008 at 11:47 PM, Craig Ringer <craig@postnewspapers.com.au> wrote:

Mag Gam wrote:

>
> ts | size| Diff
> -------------------+-----+------
> 2002-03-16 | 11 | 0
>
> 2002-03-17 | 15 | 4
> 2002-03-18 | 18 | 3
> 2002-03-19 | 12 | -6
>
>
> I need the first column to be 0, since it will be 11-11. The second
> colum is 15-11. The third column is 18-15. The fourth column is 12-18.
>
> Any thoughts about this?

Without making any comments on the advisability of the structure you're
trying to use, here are a few ideas.

The easy way is to use PL/PgSQL and FOR EACH .. SELECT . It's probably
going to be rather fast too as it can use a single sequential scan.

Otherwise (all examples use the following code):

CREATE TABLE x (ts timestamp, size int);
INSERT INTO x (ts, size) VALUES
('2002-03-16',11),
('2002-03-17',15),
('2002-03-18',18),
('2002-03-19',12);

If you can assume that there is always exactly 1 day between entries
then it's easy enough with a self join.

If you cannot assume that, you can use a subquery with limit and order
by to obtain the next record:

SELECT
a.ts,
(SELECT b.size FROM x b WHERE b.ts > a.ts ORDER BY b.ts ASC LIMIT 1)
- a.size AS difference
FROM x a;

... but that'll be really slow for any significant number of entries.

not really... if you have an index on the TS column.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] Scripting function definitions as SQL?

Unfortunately I didn't see a way to tell pg_dump to dump only objects
of a specific type, like functions or sequences. It requires
additional coding to parse the output and that's less than ideal...

> Does pg_dump not do what you want?

On Sun, May 11, 2008 at 6:49 AM, Martijn van Oosterhout
<kleptog@svana.org> wrote:
> On Sun, May 11, 2008 at 06:12:40AM -0700, Postgres User wrote:
> > Has anyone written a function that scripts out all the functions in a
> > database as full SQL statements (Create Function.....)
> >
> > I found the below SQL will return all the fields needed to build a SQL
> > statement, but it would take some work to combine the field values
> > correctly to get the right format. So does anyone know if the code
> > has already been written by someone else?

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

Re: [SQL] Difference in columns

Mag Gam wrote:

>
> ts | size| Diff
> -------------------+-----+------
> 2002-03-16 | 11 | 0
>
> 2002-03-17 | 15 | 4
> 2002-03-18 | 18 | 3
> 2002-03-19 | 12 | -6
>
>
> I need the first column to be 0, since it will be 11-11. The second
> colum is 15-11. The third column is 18-15. The fourth column is 12-18.
>
> Any thoughts about this?

Without making any comments on the advisability of the structure you're
trying to use, here are a few ideas.

The easy way is to use PL/PgSQL and FOR EACH .. SELECT . It's probably
going to be rather fast too as it can use a single sequential scan.

Otherwise (all examples use the following code):

CREATE TABLE x (ts timestamp, size int);
INSERT INTO x (ts, size) VALUES
('2002-03-16',11),
('2002-03-17',15),
('2002-03-18',18),
('2002-03-19',12);

If you can assume that there is always exactly 1 day between entries
then it's easy enough with a self join.

If you cannot assume that, you can use a subquery with limit and order
by to obtain the next record:

SELECT
a.ts,
(SELECT b.size FROM x b WHERE b.ts > a.ts ORDER BY b.ts ASC LIMIT 1)
- a.size AS difference
FROM x a;

... but that'll be really slow for any significant number of entries.

Another alternative if you can't assume each record is always exactly 1
day apart is to populate a temp table with the values and add a serial
column that guarantees a 1 offset between values, then do a self join. I
have no idea whether or not this might be faster, but thought I'd throw
it out there as an alternative:

CREATE TEMPORARY SEQUENCE x_seq;

SELECT nextval('x_seq') AS id, ts, size
INTO TEMPORARY TABLE x_temp
FROM x
ORDER BY ts ASC;

SELECT a.ts, a.size - b.size AS diff
FROM x_temp a, x_temp b
WHERE a.id = b.id + 1;

Note that this query doesn't give you the first record with zero
difference; it returns only true differences. Here's one possible way to
add your initial record:

SELECT a.ts, b.size - a.size AS diff
FROM x_temp a, x_temp b
WHERE b.id = a.id + 1
OR (b.id = (SELECT min(id) FROM x_temp) AND a.id = b.id);

--
Craig Ringer

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

Re: [pgsql-es-ayuda] Pasar arreglos como parametro en un Store Procedure

Puedes enviar el arreglo como parámetro de la función, siendo una cadena de texto con cierta estructura. Por ejemplo, '{1,2,3,4}' para un arreglo de enteros de una sola dimensión, o '{{1,2},{3,4},{5,6}}' que sería de dos dimensiones. Hay que crear esa cadena desde la aplicación cliente, pero con un ciclo por dimensión es sencillo hacerlo.

Saludos.

2008/5/11 Juan Manuel Fernandez <jmfernandez@unlu.edu.ar>:

Buenos dias Lista!

Mi inconveniente es el siguiente, tomo desde un formulario, el
programa aprobado de una o varias asignaturas, que a su vez pueden ser
de una o varias carreras y lo tomo desde un control select multiple,
mi deseo seria pasar ese array como parametro y insertar todo en la
misma transaccion, ahora bien, es posible esto?

Hay que tener en cuenta tambien que intervienen dos tablas, una
T_PROGRAMAS donde guardo los programas en tipo bytea y la otra
T_PROGRAMAS_X_ASIGNATURA donde guardo las relaciones entre un programa
y la o las asignaturas y carreras a las que pertenece.

Desde ya muchas gracias,

Saludos,

Juan
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [SQL] Difference in columns

On Sun, May 11, 2008 at 01:37:52PM -0400, Mag Gam wrote:
> Any thoughts about this?

1. will there be any gaps in between dates? if yes, what should be diff
be then?
2. can't you calculate it in client application?
3. is usage of pl/pgsql acceptable (i think this will be the fastest way
to do it in postgresql itself).

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

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

Re: [pgsql-www] [pgsql-advocacy] National community sites @ postgresql.org?

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

- --On Sunday, May 11, 2008 20:48:28 +0400 Nikolay Samokhvalov
<samokhvalov@gmail.com> wrote:

> BTW, I suspect that uniform infrastructure for national communities
> will help spread idea of weekly news translation (now it's not only
> decentralized but also is somewhat obscure -- it's not clear how to
> quickly find, say, German translation, and moreover Russian one).

We could setup a pwn.postgresql.org domain, managed by David (if he's up for
it) where all of them are archived, in the various translations? The main page
would show the most recent ... ?

- --
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)

iEYEARECAAYFAkgnNacACgkQ4QvfyHIvDvM2FACfakcxSOJ++6VkYiwJiivHjQvg
y3EAn3DJDNX/8qIiQEBtVj8V2Wji1syK
=kUbb
-----END PGP SIGNATURE-----

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

Re: [SQL] Difference in columns

On Sun, May 11, 2008 at 11:07 PM, Mag Gam <magawake@gmail.com> wrote:

Hi All,

I have a view that generates output similar to this.

select * from foo.view;
       ts          | size
-------------------+-----
 2002-03-16        | 11 
 2002-03-17        | 16 
   2002-03-18        | 18
 2002-03-19        | 12  
I am trying to find the difference between the size column. So the desired output would be
       ts          | size| Diff
-------------------+-----+------
 2002-03-16        | 11  | 0
  2002-03-17        | 15  | 4
 2002-03-18        | 18  | 3
 2002-03-19        | 12  | -6
I need the first column to be 0, since it will be 11-11. The second colum is 15-11. The third column is 18-15. The fourth column is 12-18.

Any thoughts about this?

Try this:

select   ts,
            size,
            t1.size - (select t2.size
                            from foo.view as t2
                            where t2.ts < t1.ts
                            order by ts desc
                            limit 1) as diff
from foo.view as t1
order by ts asc;

HTH,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [SQL] Difference in columns

--- Mag Gam <magawake@gmail.com> wrote:

> Hi All,
>
> I have a view that generates output similar to this.
>
> select * from foo.view;
>
> ts | size
> -------------------+-----
> 2002-03-16 | 11
> 2002-03-17 | 16
> 2002-03-18 | 18
> 2002-03-19 | 12
>
> I am trying to find the difference between the size column. So the
> desired
> output would be
>
> ts | size| Diff
> -------------------+-----+------
> 2002-03-16 | 11 | 0
> 2002-03-17 | 15 | 4
> 2002-03-18 | 18 | 3
> 2002-03-19 | 12 | -6
>
>
> I need the first column to be 0, since it will be 11-11. The second
> colum is
> 15-11. The third column is 18-15. The fourth column is 12-18.
>
> Any thoughts about this?
>

select cur.ts, cur.size,
cur.size - coalesce(
(select size from view next
where next.ts = cur.ts - '1 day'::interval),
cur.size ) as diff
from view cur;

alternately:

select cur.ts, cur.size,
case when cur.ts = '2002-03-16' then 0
else cur.size - coalesce(
(select size from view next
where next.ts = cur.ts - '1 day'::interval),
cur.size )
end as diff
from view;

____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.

http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

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

[SQL] Difference in columns

Hi All,

I have a view that generates output similar to this.

select * from foo.view;

       ts          | size
-------------------+-----
 2002-03-16        | 11 
 2002-03-17        | 16 
  2002-03-18        | 18
 2002-03-19        | 12

I am trying to find the difference between the size column. So the desired output would be

       ts          | size| Diff
-------------------+-----+------
 2002-03-16        | 11  | 0
  2002-03-17        | 15  | 4
 2002-03-18        | 18  | 3
 2002-03-19        | 12  | -6

I need the first column to be 0, since it will be 11-11. The second colum is 15-11. The third column is 18-15. The fourth column is 12-18.

Any thoughts about this?

TIA

Re: [ADMIN] Orphan TOAST object

"James Farrugia" <james.farrugia@gmail.com> writes:
> I'm running 8.2.1.

You really need to update to 8.2.latest. There are several known
data-corruption problems in 8.2.1, and it seems possible that one of
them ate the pg_depend row you needed.

> I cleanly forgot about pg_depend!
> Even after re-indexing I wasn't able to find an entry in pg_depend having
> the TOAST's OID. I guess that by creating foo again and linking
> pg_toast_xxx with foo in pg_depend by hand i can make it go away.

Yeah, that's probably the cleanest recovery strategy.

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] XIDs and big boxes again ...

Hans-Juergen Schoenig wrote:

>> regards, tom lane
>>
>
>
> overhead is not an issue here - if i lose 10 or 15% i am totally fine as
> long as i can reduce vacuum overhead to an absolute minimum.
> overhead will vary with row sizes anyway - this is not the point.

I am not buying this argument. If you have a 5TB database, I am going to
assume you put it on enterprise class hardware. Enterprise class
hardware can handle the I/O required to appropriately run vacuum.

We have a customer that is constantly running 5 autovacuum workers on
only 28 spindles. We are in the process of upgrading them to 50 spindles
at which point I will likely try 10 autovacuum workers.

>
> the point is that you don't want to potentially vacuum a table when only
> a handful of records has been changed.

Right, generally speaking 20% is reasonable, although I tend to be much
more aggressive and try to keep it at 10%.

Sincerely,

Joshua D. Drake

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

Re: [ADMIN] Orphan TOAST object

"James Farrugia" <james.farrugia@gmail.com> writes:
> I wonder whether any of you can help me out with this problem.

What PG version is this?

> To get vacuum the TOAST object we created a temporary table foo (col1
> char(1)) and assigned its reltoastrelid (up till now set to 0) to
> pg_toast_35027430's OID and then vacuumed foo. The plan worked and
> immediately age(datfrozenxid) in pg_database reflected a much younger XID.

> We then decided to get rid of pg_toast_35027430 by dropping foo. Foo
> disappeared but pg_toast_35027430 persisted.

Well, yeah, because you didn't create a pg_depend link.

We have seen a couple prior reports of toast tables not going away when
their parent was dropped, but nobody's been able to create a
reproducible case yet. The most likely idea is probably that pg_depend
got corrupted somehow, causing the toast table not to get found by DROP.

Can you find any rows in pg_depend having objid equal to the OID of
pg_toast_35027430? Try reindexing pg_depend and then see if you find
any.

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

[COMMITTERS] pgsnap - pgsnap: Change the position of the different parts of the index

Log Message:
-----------
Change the position of the different parts of the index page.

Modified Files:
--------------
pgsnap/www:
index.html (r1.9 -> r1.10)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/www/index.html.diff?r1=1.9&r2=1.10)

--
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] XIDs and big boxes again ...

Hans-Juergen Schoenig <postgres@cybertec.at> writes:
> overhead is not an issue here - if i lose 10 or 15% i am totally fine as
> long as i can reduce vacuum overhead to an absolute minimum.

I cannot see the sanity of taking a ~10% hit on all I/O activity
(especially foreground queries) to avoid having background vacuuming
going on --- at least assuming that we can keep the impact of vacuuming
below 10%, which I should hope that we could. What your problem sounds
like to me is that you need a smarter autovacuum scheduler. Some of the
map-fork ideas we've discussed would also help, by allowing vacuum to
skip pages that're known to contain only frozen tuples --- your large
low-turnover tables would probably have a lot of those.

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: [HACKERS] XIDs and big boxes again ...

Tom Lane wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
>
>> ... Keep in mind you're proposing to make everything run 3% slower instead of
>> using that 3% i/o bandwidth headroom to run vacuum outside the critical path.
>>
>
> I think that's actually understating the problem. Assuming this is a
> 64-bit machine (which it had better be, if you want XID to be 64 bits...)
> then the effective increase in tuple header size is not just 12 bytes
> but 16 bytes, due to alignment padding. Greg's 3% overhead number is
> only on-target if your average row width is presently about 530 bytes.
> It could easily be a whole lot less than that, and the overhead
> proportionally higher.
>
> regards, tom lane
>

overhead is not an issue here - if i lose 10 or 15% i am totally fine as
long as i can reduce vacuum overhead to an absolute minimum.
overhead will vary with row sizes anyway - this is not the point.

the point is that you don't want to potentially vacuum a table when only
a handful of records has been changed.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.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: [pgsql-www] [pgsql-advocacy] National community sites @ postgresql.org?

On Thu, May 8, 2008 at 4:04 PM, Marc G. Fournier <scrappy@hub.org> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> - --On Thursday, May 08, 2008 09:07:18 +0100 Dave Page <dpage@pgadmin.org> wrote:
> > Besides, we agreed long ago that <country code>.postgresql.org
> > hostnames were strictly reserved for mirror use.
>
> Good point ... so we'll need to come up with a similar naming convention to the
> pugs themselves ...
>
> But, to your point about 'more then one group', I though we were talking about
> national communities (ie. umbrellas), not individual user groups?
>
> For instance, with Italy, there would obviously be a link to the seperate user
> groups expected off of the site ...

In our -- Russian -- case we are talking about national community that
are being set up currently involving [supposedly] all Russian
PostgreSQL experts, including Oleg and Teodor and others. So, it's
really about community.

So, is it possible to handle the-most-straightforward name
ru.postgresql.org for HTTP requests separately from FTP ones?

Since there is a consistent objection to the idea of uniform
infrastructure, I think we'll better continue with our own stuff :-)
Most probably It will be at http://pgsql.ru soon.

BTW, I suspect that uniform infrastructure for national communities
will help spread idea of weekly news translation (now it's not only
decentralized but also is somewhat obscure -- it's not clear how to
quickly find, say, German translation, and moreover Russian one).

--
Best regards,
Nikolay

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

Re: [pgsql-www] [pgsql-advocacy] National community sites @ postgresql.org?

What about making a special handbook, like a "pre configured" drupal
site (I guess most of us are using drupal with pg-theme installed),
and a step by step manual of how to do the stuff or who to ask for
halp/hosting/services W.E

2008/5/12 Nikolay Samokhvalov <samokhvalov@gmail.com>:
> On Wed, May 7, 2008 at 7:05 PM, Josh Berkus <josh@agliodbs.com> wrote:
> > Selena, All,
> >
> > I don't think a policy is a good idea.
> >
> > I think for those communities who want to operate their own sites (Fr, DE,
> > Br, IT, etc) we should redirect to them.* For communities who don't want to
> > operate their own sites (as, I guess, RU doesn't), we should host them on
> > postgresql.org.
> >
>
> :-) I can't say that we are not able or do not want to run our own
> stuff, what I was talking about is just that I suppose that people
> from different communities are making similar things and would
> consolidate their efforts to build smth better.
>
> --
> Best regards,
> Nikolay
>
>
>
> --
> Sent via pgsql-advocacy mailing list (pgsql-advocacy@postgresql.org)
> To make changes to your subscription:
>

http://www.postgresql.org/mailpref/pgsql-advocacy
>

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

Re: [HACKERS] XIDs and big boxes again ...

Gregory Stark <stark@enterprisedb.com> writes:
> ... Keep in mind you're proposing to make everything run 3% slower instead of
> using that 3% i/o bandwidth headroom to run vacuum outside the critical path.

I think that's actually understating the problem. Assuming this is a
64-bit machine (which it had better be, if you want XID to be 64 bits...)
then the effective increase in tuple header size is not just 12 bytes
but 16 bytes, due to alignment padding. Greg's 3% overhead number is
only on-target if your average row width is presently about 530 bytes.
It could easily be a whole lot less than that, and the overhead
proportionally higher.

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

[ADMIN] Orphan TOAST object

Hi all,

I wonder whether any of you can help me out with this problem. We were performed a routine "lazy" VACUUM in order to reassign frozen XIDs and prevent data-loss.

After the VACUUM completed successfully, the command "SELECT datname, age(datfrozenxid) FROM pg_database" still showed an excess of 1,800,000,000 transactions from the cutoff XID of some table meaning that either the VACUUM somehow failed or else missed out a table.

Just to make sure, we re-ran the VACUUM but the result in the end was the same. We then decided to find out which table was causing this problem by running the following query:

select relname from pg_class where relfrozenxid = (select datfrozenxid from pg_database where datname = 'CDR')

Since datfrozenxid in pg_database stores the oldest XID, using this query we were able to home on the database object which was allegedly being missed by the VACUUM. The query returned a TOAST object: pg_toast_35027430. This was puzzling; as far as I know pg_toasts objects can't be vacuumed directly b

ut only when vacuuming their parent. This means that somehow this pg_toast object was orphaned, fact confirmed by the following query:

select relname from pg_class a where relname like 'pg_toast_3%' and relkind = 't' and not exists (

select 1 from pg_class b where a.oid = b.reltoastrelid and relkind = 'r')

To get vacuum the TOAST object we created a temporary table foo (col1 char(1)) and assigned its reltoastrelid (up till now set to 0) to pg_toast_35027430's OID and then vacuumed foo. The plan worked and immediately age(datfrozenxid) in pg_database reflected a much younger XID.

We then decided to get rid of pg_toast_35027430 by dropping foo. Foo disappeared but pg_toast_35027430 persisted. I'd like to get rid of it because in a few months' time we will bump into the same problem again. Does anyone have any idea how this can be removed manually without causing any unwarranted damage to the system catalogue?

Also, can one un-vacuumed database object cause dataloss in other unrelated tabled which have otherwise been VACUUMed and their respective FrozenXIDs reassigned?

Thank you in advance,

James.

[COMMITTERS] pgsnap - pgsnap: Fix download URL.

Log Message:
-----------
Fix download URL.

Modified Files:
--------------
pgsnap/www:
index.html (r1.8 -> r1.9)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/www/index.html.diff?r1=1.8&r2=1.9)

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

[COMMITTERS] pgsnap - pgsnap: Release 0.3.1.

Log Message:
-----------
Release 0.3.1.

Modified Files:
--------------
pgsnap/pgsnap:
ChangeLog (r1.28 -> r1.29)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/pgsnap/ChangeLog.diff?r1=1.28&r2=1.29)
pgsnap/pgsnap/lib:
getopt.php (r1.8 -> r1.9)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/pgsnap/lib/getopt.php.diff?r1=1.8&r2=1.9)
pgsnap/pgsnap/man:
pgsnap.1 (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/pgsnap/man/pgsnap.1.diff?r1=1.2&r2=1.3)
pgsnap/www:
index.html (r1.7 -> r1.8)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/www/index.html.diff?r1=1.7&r2=1.8)

--
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] [pgsql-advocacy] National community sites @ postgresql.org?

On Wed, May 7, 2008 at 7:05 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Selena, All,
>
> I don't think a policy is a good idea.
>
> I think for those communities who want to operate their own sites (Fr, DE,
> Br, IT, etc) we should redirect to them.* For communities who don't want to
> operate their own sites (as, I guess, RU doesn't), we should host them on
> postgresql.org.
>

:-) I can't say that we are not able or do not want to run our own
stuff, what I was talking about is just that I suppose that people
from different communities are making similar things and would
consolidate their efforts to build smth better.

--
Best regards,
Nikolay

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

[COMMITTERS] pgsnap - pgsnap: Last fix for the output dir.

Log Message:
-----------
Last fix for the output dir.

Modified Files:
--------------
pgsnap/pgsnap/lib:
getopt.php (r1.7 -> r1.8)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/pgsnap/lib/getopt.php.diff?r1=1.7&r2=1.8)

--
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] Setting a pre-existing index as a primary key

On Sat, May 10, 2008 at 10:41:56PM -0400, Andrew Sullivan wrote:
> On Sat, May 10, 2008 at 11:55:29AM -0400, Tom Lane wrote:
> > IMHO a utility command should do one easily-explained thing. The
> > fewer options the better.
>
> Sticking to that principle makes for a better-maintained system. I
> agree. If we want to point out, "You might rename your index
> afterwards to make it look like other default primary keys," I have
> no objection.

For convenience, it might be nice to include the generated name in the
notice.

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: [PATCHES] [badalex@gmail.com: Re: [BUGS] Problem identifying constraints which should not be inherited]

Nikhils <nikkhils@gmail.com> writes:
> ... One minor thing that myself and Alex discussed was
> the usage of "child tables" in tablecmds.c, especially in error messages.
> Again English is not my native language, but shouldn't that be worded as
> "children tables"? Admittedly even this does not sound any better than
> "child tables" though :).

No, "child tables" sounds better to me. English doesn't usually
pluralize adjectives.

regards, tom lane

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

[COMMITTERS] pgsnap - pgsnap: Fix website.

Log Message:
-----------
Fix website.

Modified Files:
--------------
pgsnap/www:
index.html (r1.6 -> r1.7)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/www/index.html.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

[COMMITTERS] pgsnap - pgsnap: Release 0.3.

Log Message:
-----------
Release 0.3.

Modified Files:
--------------
pgsnap/pgsnap:
ChangeLog (r1.27 -> r1.28)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/pgsnap/ChangeLog.diff?r1=1.27&r2=1.28)
pgsnap/pgsnap/lib:
getopt.php (r1.6 -> r1.7)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/pgsnap/lib/getopt.php.diff?r1=1.6&r2=1.7)
pgsnap/pgsnap/man:
pgsnap.1 (r1.1 -> r1.2)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/pgsnap/man/pgsnap.1.diff?r1=1.1&r2=1.2)
pgsnap/www:
index.html (r1.5 -> r1.6)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsnap/pgsnap/www/index.html.diff?r1=1.5&r2=1.6)

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

Re: [PATCHES] Database owner installable modules patch

On Sat, May 10, 2008 at 11:02 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> Where are we on this?

I haven't had time to do any work since the original patch. That patch
was fairly basic - it just ran install / uninstall scripts and created
catalog entries, and introduced some slightly exotic syntax to do it
(INSTALL/UNINSTALL vs CREATE/DROP). The next version is intended to
handle dependencies, which should make uninstallation straight forward
for most cases. I was intending to revert the syntax creativity and
make the commands CREATE/DROP too.

I'll get a bit of time to look at both this and the enum patch this week.

Cheers

Tom

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

[COMMITTERS] pgsnap - pgsnap: Complete UI Review.

Log Message:
-----------
Complete UI Review.

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

Re: [ADMIN] Require security ACCESS matrix in prostgresql !!

On Sun, May 11, 2008 at 09:29:01AM -0500, Potluri Srikanth wrote:

> can anyone provide me the security ACCESS matrix in prostgresql ?

Have a look at the fine manual:
http://www.postgresql.org/docs/8.3/static/privileges.html

HTH,

Tino.

--
"What we resist, persists." (Zen saying)

www.craniosacralzentrum.de
www.forteego.de

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

[ADMIN] Require security ACCESS matrix in prostgresql !!

Hi all,

can anyone provide me the security ACCESS matrix in prostgresql ?

security involving ROLES, access priviledges , etc ...

i am novice in the field of security implementation, hence i request you if you can provide me examples or PPT or MATRIX sheet to understand the security in postgresql.

Thanks in advance for your assistance and contribution.

Regards,
Srikanth k Potluri
+63 9177444783(philippines)

[pgsql-www] listing training courses by language ?

hi everyone,

http://www.postgresql.org/about/eventarchive

Looking at the training list in the main web site, a few questions come to my
mind :

1/ is it possible to specify in the header the language of the training ?
i see that most of them are in English, but some are in different language.
This information seems almost as important as the location and the date.

2/ is it possible to write the training description in its "original" tongue ?
For instance, i see no point announcing a french training and presenting it
with an English description. This is rather confusing. At least that would
be great to have the ability the describe the training both in English and
the original language.

3/ is it possible the sort the training by languages ? Maybe just creating
sub-categories in the "training" section. My understanding is that when
people search for a training, they're searching in very specific language (
which is most of the time their mother tongue ) . If they don't find any
training in their mother tongue, then they'll search in another language. So
i think sorting training by language would be better ...

Here's a bit of my own experience : Dalibo has announced some french
PostgreSQL trainings on this page in the past... But we found it was rather
useless because the announce was in English and surrounded by other English
trainings. Very few people would see the announce and understand it was in
french.

I don't know if what i propose is easy to implement or if it's worth the
time.... But in a general way, i think the language information should be
more explicit on this page.

--
damien clochard
http://dalibo.org | http://dalibo.com

--
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] Scripting function definitions as SQL?

On Sun, May 11, 2008 at 06:12:40AM -0700, Postgres User wrote:
> Has anyone written a function that scripts out all the functions in a
> database as full SQL statements (Create Function.....)
>
> I found the below SQL will return all the fields needed to build a SQL
> statement, but it would take some work to combine the field values
> correctly to get the right format. So does anyone know if the code
> has already been written by someone else?

Does pg_dump not do what you want?

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.

[GENERAL] Scripting function definitions as SQL?

Has anyone written a function that scripts out all the functions in a
database as full SQL statements (Create Function.....)

I found the below SQL will return all the fields needed to build a SQL
statement, but it would take some work to combine the field values
correctly to get the right format. So does anyone know if the code
has already been written by someone else?

SELECT p.proname AS name, p.oid, p.proargtypes AS args, ds.description
, p.prorettype AS rettype,
p.proretset, p.probin, p.proisstrict AS strict, p.prosrc AS body,
l.lanname AS lang,
u.usename, p.prosecdef, p.provolatile, p.proisagg, n.nspname,
proargnames, p.proargmodes, p.proallargtypes
FROM pg_proc p
LEFT OUTER JOIN pg_description ds ON ds.objoid = p.oid
INNER JOIN pg_namespace n ON p.pronamespace = n.oid
INNER JOIN pg_language l ON l.oid = p.prolang
LEFT OUTER JOIN pg_user u ON u.usesysid = p.proowner
WHERE n.nspname = 'main'
ORDER BY p.proname, n.nspname

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

Re: [PATCHES] pg_dump lock timeout

*** pgsql/src/bin/pg_dump/pg_dump.c.orig 2008-05-11 03:23:06.000000000 -0700
--- pgsql/src/bin/pg_dump/pg_dump.c 2008-05-11 03:44:58.000000000 -0700
***************
*** 71,76 ****
--- 71,77 ----
bool schemaOnly;
bool dataOnly;
bool aclsSkip;
+ const char *lockWaitTimeout;

/* subquery used to convert user ID (eg, datdba) to user name */
static const char *username_subquery;
***************
*** 238,243 ****
--- 239,245 ----
{"column-inserts", no_argument, NULL, 'D'},
{"host", required_argument, NULL, 'h'},
{"ignore-version", no_argument, NULL, 'i'},
+ {"lock-wait-timeout", required_argument, NULL, 'l'},
{"no-reconnect", no_argument, NULL, 'R'},
{"oids", no_argument, NULL, 'o'},
{"no-owner", no_argument, NULL, 'O'},
***************
*** 278,283 ****
--- 280,286 ----
strcpy(g_opaque_type, "opaque");

dataOnly = schemaOnly = dumpInserts = attrNames = false;
+ lockWaitTimeout = NULL;

progname = get_progname(argv[0]);

***************
*** 299,305 ****
}
}

! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vWxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
--- 302,308 ----
}
}

! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:il:n:N:oOp:RsS:t:T:U:vWxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
***************
*** 350,355 ****
--- 353,362 ----
/* ignored, deprecated option */
break;

+ case 'l': /* lock wait time */
+ lockWaitTimeout = optarg;
+ break;
+
case 'n': /* include schema(s) */
simple_string_list_append(&schema_include_patterns, optarg);
include_everything = false;
***************
*** 755,760 ****
--- 762,769 ----
printf(_("\nGeneral options:\n"));
printf(_(" -f, --file=FILENAME output file name\n"));
printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
+ printf(_(" -l, --lock-wait-timeout=DELAY\n"
+ " timeout and fail after delay waiting for a table share lock\n"));
printf(_(" -v, --verbose verbose mode\n"));
printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
printf(_(" --help show this help, then exit\n"));
***************
*** 3191,3196 ****
--- 3200,3213 ----
i_reltablespace = PQfnumber(res, "reltablespace");
i_reloptions = PQfnumber(res, "reloptions");

+ if (lockWaitTimeout)
+ {
+ /* Abandon the dump instead of waiting forever for a table lock */
+ resetPQExpBuffer(lockquery);
+ appendPQExpBuffer(lockquery, "SET statement_timeout = ");
+ appendStringLiteralConn(lockquery, lockWaitTimeout, g_conn);
+ do_sql_command(g_conn, lockquery->data);
+ }
for (i = 0; i < ntups; i++)
{
tblinfo[i].dobj.objType = DO_TABLE;
***************
*** 3259,3264 ****
--- 3276,3285 ----
tblinfo[i].dobj.name);
}

+ if (lockWaitTimeout)
+ {
+ do_sql_command(g_conn, "SET statement_timeout = default");
+ }
PQclear(res);

/*
*** pgsql/doc/src/sgml/ref/pg_dump.sgml.orig 2008-05-11 03:38:05.000000000 -0700
--- pgsql/doc/src/sgml/ref/pg_dump.sgml 2008-05-11 03:38:56.000000000 -0700
***************
*** 302,307 ****
--- 302,320 ----
</varlistentry>

<varlistentry>
+ <term><option>-l <replaceable class="parameter">wait_time</replaceable></option></term>
+ <term><option>--lock-wait-timeout=<replaceable class="parameter">wait_time</replaceable></option></term>
+ <listitem>
+ <para>
+ Do not wait forever for table locks at the start of the dump. Instead
+ time out and abandon the dump if unable to lock a table within the
+ specified wait time. The wait time is specified with the same formats
+ as accepted for intervals by the SET command.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
<term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
<listitem>
On Sun, May 11, 2008 at 04:30:47AM -0700, daveg wrote:
>
> Attached is a patch to add a commandline option to pg_dump to limit how long
> pg_dump will wait for locks during startup.

Ooops, really attached this time.

-dg

--
David Gould daveg@sonic.net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Die Optimale Tabellenstruktur für Postgres 8.1-8.3 ?

rudi@je-more.de <rudi@je-more.de> schrieb:

>>
>> IIRC speichert MySQL ja alles in einem File, PG nutzt TOAST. Alleine
>> schon deswegen vermute ich einfach mal, daß PG hier einen Vorteil hat.
>>
> Hast Du da mal einen qualifizierten Link? Die beiden Begriffe in
> Zusammenhang mit SQL DB's
> habe ich noch nie gehört ;D

Welche Begriffe? Einer wohl sicher TOAST, oder?
http://www.postgresql.org/docs/current/static/storage-toast.html

Und der andere?

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

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

[pgsql-de-allgemein] Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Die Optimale Tabellenstruktur für Postgres 8.1-8.3 ?

>
> IIRC speichert MySQL ja alles in einem File, PG nutzt TOAST. Alleine
> schon deswegen vermute ich einfach mal, daß PG hier einen Vorteil hat.
>
Hast Du da mal einen qualifizierten Link? Die beiden Begriffe in
Zusammenhang mit SQL DB's
habe ich noch nie gehört ;D
> Dann müßte man einfach mal sehen, wie sich so eine Tabelle unter PG
> verhält, also INSERT und SELECT mal via EXPLAIN untersuchen.
>
Naja, ich habe ein LAMP Operativsystem und das PG basierte ist gerade in
der Entwicklung
und da will ich die alten Probleme halt nicht wieder haben. Richtige
Liveresultate bekomme ich
also erst im Betrieb mit dem neuen System, bis dahin heißt so gut wie
möglich schätzen.
> Man könnte auch darüber sinnieren, das nur im Filesystem zu speichern.
> Aber das ist eine andere Baustelle.
Mit Bildern habe ich das eigentlich auch vor.

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

Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Die Optimale Tabellenstruktur für Postgres 8.1-8.3 ?

rudi@je-more.de <rudi@je-more.de> schrieb:

> Ich habe eine Tabelle Messages, die aus den Feldern
>
> ID int, SENDER int , SUBJECT, varchar 240, MESSAGEBODY text, DATE datetime
> besteht.
>
> Das Problemfeld sind hier die SELECTS und INSERTS die Konkurierend von
> verschiedenen
> Usern gleichzeitig ausgeführt werden. Aufgrund der Zeichenlänge im Feld
> "Messagebody (text)"
> dauert ein Insert/Select ewig lange und führt auf der WebApplikation
> Oberfläche zu langen Wartezeiten
> für dier User (Warnung: das momentane System läuft auf MySQL5 mit PHP4.x
> unter Linux).

IIRC speichert MySQL ja alles in einem File, PG nutzt TOAST. Alleine
schon deswegen vermute ich einfach mal, daß PG hier einen Vorteil hat.

Dann müßte man einfach mal sehen, wie sich so eine Tabelle unter PG
verhält, also INSERT und SELECT mal via EXPLAIN untersuchen.

>
> Die Messagetable ist jetzt knapp 700 GBytes groß und wächst beständig
> weiter an. Wenn 10.000- 14.000
> USer täglich eingeloggt sind gibts schon mal 20 bis 30 Sek. für ein
> Insert und ca. 5 bis 20 Sek fürs
> Selects,

Wie schaut denn so ein Select aus? MySQL kann IIRC nur ein Index pro
Select und Table verwenden, PG hat solch Limitierung nicht.

Ohne handfeste Tatsachen, also ein Explain Analyse, raten wir ja nur
herum.

>
> Die Tabelle Messages istzwar nicht groß und Komplex aber das Feld
> Messageboy ist eine riesige
> Datentonne wo ich nicht recht weiß wie ich auf Postgres Seite so
> gestalten soll das ich keine Bauchschmerzen

Man könnte auch darüber sinnieren, das nur im Filesystem zu speichern.
Aber das ist eine andere Baustelle.

>
> ps: Nein, hab keine Kohle sondern will einfach nur meinen Job gut machen
> und möchte
> dafür Postgres verwenden :D

Löblich.

Andreas
--
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

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

[PATCHES] pg_dump lock timeout

Attached is a patch to add a commandline option to pg_dump to limit how long
pg_dump will wait for locks during startup.

The intent of this patch is to allow pg_dump to fail if a table lock cannot
be taken in a reasonable time. This allows the caller of pg_dump to retry or
otherwise correct the situation, without having locks held for long periods,
and without pg_dump having a long window during which catalog changes can
occur.

It works by setting statement_timeout to the user specified delay during
the startup phase where it is taking access share locks on all the tables.
Once all the locks are taken, it sets statement_timeout back to the default.
If a lock table statement times out, the dump fails with the statement timed
out error.

The orginal motivation was a client who runs heavy batch workloads and uses
truncate table and other DML in long transactions. This has created some
unhappy interaction scenarios with pg_dump:

- pg_dump ends up waiting hours on a DML table lock that is part of a long
transaction. Once the lock is released, pg_dump runs only to find
some table later in the list has been dropped. So pg_dump fails.

- pg_dump waits on a lock while holding access share locks on most of the
tables. Other processes that want to do DML wait on pg_dump. After a
while, large parts of the application are blocked while pg_dump waits
on locks. Eventually the operations staff notice that pg_dump is
blocking production and kill the dump.

Please have a look and consider it for merging.

Thanks

-dg

--
David Gould
If simplicity worked, the world would be overrun with insects.

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

[COMMITTERS] libpqtypes - libpqtypes: set ownership of resulting groff2html html

Log Message:
-----------
set ownership of resulting groff2html html pages to apache

Modified Files:
--------------
libpqtypes:
groff2html (r1.9 -> r1.10)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/groff2html.diff?r1=1.9&r2=1.10)

--
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] XIDs and big boxes again ...

"Hans-Juergen Schoenig" <postgres@cybertec.at> writes:

> my DB is facing around 600mio transaction a month. 85% of those contain at
> least some small modification so I cannot save on XIDs.

What's a "mio"? Assuming it's short for "million" I don't see the problem. The
transaction horizon is 2 *billion*. So as long as you can complete a vacuum of
every table once every 3 months you should be fine.

> my problem is that I cannot VACUUM FREEZE my 150k tables where most of the data
> is as I have a couple of thousand transactions a day modifying this data.

vacuum freeze doesn't take any locks. But in any case vacuum freeze would only
extend the vacuum horizon by 100k so it would hardly make any difference.

> but, i also have troubles to prevent myself from transaction wraparound as it
> is pretty boring to vacuum that much data under heavy load - with some useful
> vacuum delay it just takes too long.

How long is too long?

> i basically have to vacuum the entire database too often to get spare XIDs.

How often is too often?

> i suggest to introduce a --with-long-xids flag which would give me 62 / 64 bit
> XIDs per vacuum on the entire database.
> this should be fairly easy to implement.
> i am not too concerned about the size of the tuple header here - if we waste
> 500 gb of storage here i am totally fine.

Keep in mind you're proposing to make everything run 3% slower instead of
using that 3% i/o bandwidth headroom to run vacuum outside the critical path.

--
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

[pgsql-de-allgemein] Re: [pgsql-de-allgemein] Die Optimale Tabellenstruktur für Postgres 8.1-8.3 ?

Danke für die schnelle Antwort!

>> Mit der Normalform macht man seltenst Fehler.
>>
hmm, naja gibt andere DB's die nehmen einen die Normalform schon eher krum,
da muss man dann spezielle Optimierungen vornehmen, die nicht ganz
Standardkonform
sind. Aber aus deiner Antwort schließe ich das PG die erfreuliche
Ausnahme ist ;D

>> Das Tool der Wahl ist EXPLAIN ANALYSE, dazu das Loggen aller Statements,
>> die l�nger als X ms Ausf�hrungszeit haben.
>> Andreas
>>
Hmm, weiß nicht ob mein Problem zu Abstrakt für EXPLAIN ANALYSE gestrikt
ist.

Ich habe eine Tabelle Messages, die aus den Feldern

ID int, SENDER int , SUBJECT, varchar 240, MESSAGEBODY text, DATE datetime
besteht.

Das Problemfeld sind hier die SELECTS und INSERTS die Konkurierend von
verschiedenen
Usern gleichzeitig ausgeführt werden. Aufgrund der Zeichenlänge im Feld
"Messagebody (text)"
dauert ein Insert/Select ewig lange und führt auf der WebApplikation
Oberfläche zu langen Wartezeiten
für dier User (Warnung: das momentane System läuft auf MySQL5 mit PHP4.x
unter Linux).

Die Messagetable ist jetzt knapp 700 GBytes groß und wächst beständig
weiter an. Wenn 10.000- 14.000
USer täglich eingeloggt sind gibts schon mal 20 bis 30 Sek. für ein
Insert und ca. 5 bis 20 Sek fürs
Selects, je nach kokurierendem Userverhalten (sprich wie hoch die
Hotspots nun mal gerade sind).

Die Tabelle Messages istzwar nicht groß und Komplex aber das Feld
Messageboy ist eine riesige
Datentonne wo ich nicht recht weiß wie ich auf Postgres Seite so
gestalten soll das ich keine Bauchschmerzen
mehr damit habe. In Zukunft soll die gesammt db so mit ca. 1 Terrabyte
bis 1 Exobyte keine Probleme
haben, so dass sich der Wechsel auf Postgres auch wirklich rechnet und
nicht in ein paar Jahren die
nächste DB Migration ins Haus steht.

ps: Nein, hab keine Kohle sondern will einfach nur meinen Job gut machen
und möchte
dafür Postgres verwenden :D

G.R

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

[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;

[pgsql-de-allgemein] pgpool2 - DB-Loadbalancing beim Entwurf berücksichtigen?

Moin (2) ;d

Also wie manche schon bemerkt habe designe ich gerade Tabellen und
Tablespaces für eine
Webapplikation. Da ich stehts versuche alles Skalierbar zu halten stelle
ich mir gerade die Frage
wie das mit PGPool2 in der Praxis so funktioniert und ob es sinn macht
es in den Entwurf zu
integriegen b.z.w einfach by default statt gegen den Postmaster direkt
den ConnectionString
auf den PGPoolmanager auszurichten.

Bringt mir das etwas?`Oder kannich dabei etwas lernen?
Wer kennt sich mit PGPool einigermassen aus?

G.R

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

Re: [pgsql-de-allgemein] Die Optimale Tabellenstruktur für Postgres 8.1-8.3 ?

rudi@je-more.de <rudi@je-more.de> schrieb:

> Moin,
>
> Das ist bestimmt eine komische Frage, aber wie designe ich eine Tabelle
> für PG genauso,
> dass der Postmaster sie optimal verarbeiten kann? Ich will einfach eine
> Tabellenstruktur
> erstellen die Postgres soweit entgegene kommt wie nur möglich.

Mit der Normalform macht man seltenst Fehler.

> Mein Einsatzzweck:
> Maximale SELECT Ausleseperformance für die Darstellung auf dynamisch
> generieten Websites
> für eine Suchmaske.

Das Tool der Wahl ist EXPLAIN ANALYSE, dazu das Loggen aller Statements,
die l�nger als X ms Ausf�hrungszeit haben.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

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

Sunday, May 11, 2008

Blog Archive