PostgreSQL

Monday, July 7, 2008

Re: [PERFORM] How much work_mem to configure...

In response to "Scott Marlowe" <scott.marlowe@gmail.com>:

> On Sat, Jul 5, 2008 at 5:24 AM, Jessica Richard <rjessil@yahoo.com> wrote:
> > How can I tell if my work_mem configuration is enough to support all
> > Postgres user activities on the server I am managing?
> >
> > Where do I find the indication if the number is lower than needed.
>
> You kinda have to do some math with fudge factors involved. As
> work_mem gets smaller, sorts spill over to disk and get slower, and
> hash_aggregate joins get avoided because they need to fit into memory.
>
> As you increase work_mem, sorts can start happening in memory (or with
> less disk writing) and larger and larger sets can have hash_agg joins
> performed on them because they can fit in memory.
>
> But there's a dark side to work_mem being too large, and that is that
> you can run your machine out of free memory with too many large sorts
> happening, and then the machine will slow to a crawl as it swaps out
> the very thing you're trying to do in memory.
>
> So, I tend to plan for about 1/4 of memory used for shared_buffers,
> and up to 1/4 used for sorts so there's plenty of head room and the OS
> to cache files, which is also important for performance. If you plan
> on having 20 users accessing the database at once, then you figure
> each one might on average run a query with 2 sorts, and that you'll be
> using a maximum of 20*2*work_mem for those sorts etc...
>
> If it's set to 8M, then you'd get approximately 320 Meg max used by
> all the sorts flying at the same time. You can see why high work_mem
> and high max_connections settings together can be dangerous. and why
> pooling connections to limit the possibility of such a thing is useful
> too.
>
> Generally it's a good idea to keep it in the 4 to 16 meg range on most
> machines to prevent serious issues, but if you're going to allow 100s
> of connections at once, then you need to look at limiting it based on
> how much memory your server has.

I do have one thing to add: if you're using 8.3, there's a log_temp_files
config variable that you can use to monitor when your sorts spill over
onto disk. It doesn't change anything that Scott said, it simply gives
you another way to monitor what's happening and thus have better
information to tune by.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

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

[pgsql-www] The list of "Hosting Solutions" at postgresql.org

Is there some sort of procedure for keeping the lists at

http://www.postgresql.org/support/professional_hosting up to date?

I had a quick look through the list for Europe, an at least the
following seem to either not exist any more, or do not offer any
services related to PostgreSQL, as far as I can tell:

Canoeware (http://www.canoeware.com)
None of the links from the front page work.

FeelHosted (http://www.feelhosted.com)
Doesn't seem to exist anymore. Domain taken over by domain squatters.

Forma Srl (http://www.webforma.it/)
Couldn't find any reference to PostgreSQL

GenTi (http://www.genti.pl)
Non-working page

Kieser Consultancy Limited (http://www.kieser.net)
Seems not to work, couldn't find any reference to PostgreSQL

Smartlounge (http://www.smartlounge.be)
Couldn't find any reference to PostgreSQL

ZURIEL Kft. (http://www.zuriel.hu/)
Couldn't find any reference to PostgreSQL

cubic.ch (http://www.cubic.ch)
Couldn't find any reference to PostgreSQL

Because a lot of there are in languages that I understand, there could
be more than just these. In addition, many of the companies in this list
should probably just be listed in the "Professional Services" list,
and not the "Hosting Solutions" list.

--
Tommy Gildseth

--
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-de-allgemein] Pattern Matching

Danke für die Hinweise, besonders an Michael.

Postgres hat den negativen und positiven lookahead implementiert und
damit funktioniert es so wie es soll.

SELECT 'http://feeds.wordpress.com' ~
E'^http://(?!feeds)[^.]+\\.wordpress\\.com';
?column?
----------
f
(1 row)

Und das zweite Beispiel ist true:

SELECT 'http://asbojesus.wordpress.com/2007/03/02/14/' ~
E'^http://(?!feeds)[^.]+\\.wordpress\\.com';
?column?
----------
t
(1 row)

Gruß
Florian

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

[BUGS] unexpected RULE behavior

Hi,

I did the following:

CREATE TABLE data (id SERIAL, title VARCHAR);
CREATE TABLE data_copy(id INT4, title VARCHAR);
CREATE RULE make_copy AS ON INSERT TO data DO INSERT INTO data_copy
(id,title) VALUES (NEW.id, NEW.title);
INSERT INTO data (title) VALUES ('test');

database=# SELECT * FROM data;
id | title
----+-------
1 | test
(1 Zeile)

database=# SELECT * FROM data_copy;
id | title
----+-------
2 | test
(1 Zeile)

and wondered about the result in the table 'data_copy'. I expect the row
with the same values but 'id' is different although only 'data.id' is a
serial but not 'data_copy.id'.
My database is PostgreSQL 8.2.4 and runs on Windows Server 2003.

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

Re: [pgsql-de-allgemein] Pattern Matching

begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.markus@proventis.net
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard

hi,

versuchs doch mit mal so

select
a.c , a.c ~ '^http://(?!feeds)\..*'
from
(
select 'http://asbojesus.wordpress.com/2007/03/02/14/' as c
union all
select
'http://feeds.wordpress.com/1.0/goreddit/globolibro.wordpress.com/319/' as c
) a

aber das ilike vom michael ist eher anzuraten

gruss
Thomas

Florian Aumeier schrieb:
> Guten Morgen allerseits
>
> wie kann ich bei Postgres in einem Pattern eine Zeichenfolge
> ausschließen?
>
> Als Beispiel zwei unterschiedliche URL. Die erste URL soll gematched
> werden, die zweite nicht:
>
> a) 'http://asbojesus.wordpress.com/2007/03/02/14/'
> b)
> 'http://feeds.wordpress.com/1.0/goreddit/globolibro.wordpress.com/319/'
>
> Meine Idee war es mit diesem Pattern zu machen
>
> E'^http://[a-zA-Z0-9]+[^(feeds)]\.wordpress\.com'
>
> was leider nicht funktioniert, da dass [^(feeds)] nicht nur die
> Zeichenfolge 'feeds' ausschließt, sondern die einzelnen Zeichen 'f e d
> s'.
>
> Zum testen:
>
> SELECT * from
> regexp_matches('http://asbojesus.wordpress.com/2007/03/02/14/',
> E'^http://[a-zA-Z0-9]+[^(feeds)]\.wordpress\.com');
>
> SELECT * from
> regexp_matches('http://feeds.wordpress.com/1.0/goreddit/globolibro.wordpress.com/319/',
> E'^http://[a-zA-Z0-9]+[^(feeds)]\.wordpress\.com');
>
>
> Gruß
> Florian
>

[GENERAL] Altering a column type w/o dropping views

I'm going to alter a bunch a tables columns's data type and I'm being
forced to drop a view which depends on the the colum.

eg: ALTER TABLE xs.d_trh ALTER m_dcm TYPE character varying;
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v_hpp depends on column "m_dcm"

Is there an alternative method of doing this w/o dropping the existing
view?

--
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] How to find space occupied by postgres on harddisk

dipesh wrote:
> Hello,
> Myself Dipesh Mistry from Ahmedabad India.
> I want to know that if i dump the 5GB sql file then how many space does
> postgres occupy on harddisk.

Do you mean a 5GB database? If that's what you meant, then the size of
the resulting dump depends on the dump format, the FILLFACTOR of your
tables and indices, the number of indices you have, etc.

If you mean that you have a 5GB SQL dump and you want to know how big it
will be when loaded into PostgreSQL, well, the same applies but in
reverse. It depends on the table and index fillfactors, how many indexes
you have, etc.

My database is a bit less than 1GB on disk as stored by PostgreSQL,
including xlogs, indexes, etc. When I dump it in PostgreSQL's custom
compressed dump format (pg_dump -Fc) it uses 25MB of storage. It's
VACUUMed and REINDEXed regularly and has fillfactors of around 60% for
most tables/indices.

If I use the ordinary uncompressed SQL dump format it uses 140MB.

All this depends on your data. Some data types "expand" more than others
when converted from their SQL dump file representation to their
representation in PostgreSQL's storage. Some are stored smaller in Pg
than in an SQL dump. Additionally, indexes use space too, potentially
LOTS of space. Finally, your tables will "waste" some space with deleted
rows, padding for non-100% fillfactors, etc.

The best thing to do is load it into PostgreSQL and see (or dump it, if
that's what you meant). That'll tell you for sure. It's not like a 5GB
dump will take all that long to load.

--
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-de-allgemein] Pattern Matching

Florian Aumeier schrieb:
> Guten Morgen allerseits
>
> wie kann ich bei Postgres in einem Pattern eine Zeichenfolge ausschließen?
>
> Als Beispiel zwei unterschiedliche URL. Die erste URL soll gematched
> werden, die zweite nicht:
>
> a) 'http://asbojesus.wordpress.com/2007/03/02/14/'
> b) 'http://feeds.wordpress.com/1.0/goreddit/globolibro.wordpress.com/319/'
>
> Meine Idee war es mit diesem Pattern zu machen
>
> E'^http://[a-zA-Z0-9]+[^(feeds)]\.wordpress\.com'
>
> was leider nicht funktioniert, da dass [^(feeds)] nicht nur die
> Zeichenfolge 'feeds' ausschließt, sondern die einzelnen Zeichen 'f e d s'.

Um das so umzusetzen brauchst du einen negative lookbefore [1]; ich bin
mir nicht sicher ob das in den PG PCRE implementiert ist.

Zum allgemeinen regex-basteln eignen sich Regex Coach [2] oder ähnliche
Tools ziemlich gut (interaktives Testen).

Und als Lösungsansatz würd ich unter der Annahme, dass die URLs einzeln
in einer Column stehen, ein "not ilike 'http://feeds.wordpress.com/%'"
empfehlen, das sollt auch relativ flott sein.

lg,
michael

[1] http://www.regular-expressions.info/lookaround.html
[2] http://www.weitz.de/regex-coach/

Michael Renner
InQnet GmbH
Praterstraße 31
A-1020 Wien

Tel.: +43 1 212 7650 521
Fax.: +43 1 212 7650 610

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

[SQL] how to control the execution plan ?

Hi there,

I try to execute the following statement:

SELECT *
FROM (
SELECT "MY_FUNCTION_A"(bp."COL_A", NULL::boolean) AS ALIAS_A
FROM "TABLE_A" bp
JOIN "TABLE_B" pn ON bp."COL_B" = pn."PK_ID"
JOIN "TABLE_C" vbo ON bp."COL_C" = vbo."PK_ID"
WHERE pn."Editor"::text ~~ 'Some%'::text AND bp."COL_A" IS NOT NULL AND
bp."COL_A"::text <> ''::text
) x
WHERE (x.ALIAS_A::text ) IS NULL;

The problem is the excution plan first make Seq Scan on "TABLE_A", with
Filter: (("COL_A" IS NOT NULL) AND (("COL_A")::text <> ''::text) AND
(("MY_FUNCTION_A"("COL_A", NULL::boolean))::text IS NULL))". This way,
MY_FUNCTION_A crashes for some unsupported data provided by "COL_A".

I'd like to get an execution plan which is filtering first the desired rows,
and just after that compute te column value "MY_FUNCTION_A"(bp."COL_A",
NULL::boolean).

I made different combinations, including a subquery like:

SELECT *
FROM (
SELECT "MY_FUNCTION_A"(y."COL_A", NULL::boolean) AS ALIAS_A
FROM (
SELECT bp."COL_A"
FROM "TABLE_A" bp
JOIN "TABLE_B" pn ON bp."COL_B" = pn."PK_ID"
JOIN "TABLE_C" vbo ON bp."COL_C" = vbo."PK_ID"
WHERE pn."COL_E"::text ~~ 'Some%'::text AND bp."COL_A" IS NOT NULL
AND bp."COL_A"::text <> ''::text
) y
) x
WHERE (x.ALIAS_A::text ) IS NULL;

but postgres analyze is too 'smart' and optimize it as in the previous case,
with the same Seq Scan on "TABLE_A", and with the same filter.

I thought to change the function MY_FUNCTION_A, to support any argument
data, but the even that another performance problem will be rised when the
function will be computed for any row in join, even those that can be
removed by other filter.

Do you have a solution please ?

TIA
Sabin

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

[SQL] How to find space occupied by postgres on harddisk

Hello,
Myself Dipesh Mistry from Ahmedabad India.
I want to know that if i dump the 5GB sql file then how many space does
postgres occupy on harddisk.
Is there any calculation is available?
Or any postgres command can give us this type of information?
Thank you.

--
With Warm Regards,
Dipesh Mistry
Information Technology Dept.
GaneshaSpeaks.com

--
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] [Postgresql 8.2.3] autovacuum starting up even after disabling ?

Hi,

Tom Lane <tgl <at> sss.pgh.pa.us> writes:
> dushy <dushyanth <at> gmail.com> writes:
> > On Fri, Jul 4, 2008 at 10:56 PM, Adrian Klaver <aklaver <at> comcast.net>
wrote:
> >> One question? Did you do pg_ctl reload after changing the config file?
>
> > I did not change any config yet - autovacuum was always disabled since
> > the day PG was set up.
>
> A mistake here seems by far the most likely explanation.

I have rechecked the config multiple times till now :)

> Does "show autovacuum" confirm that it's off?

Yes.

# show autovacuum;
autovacuum
------------
off
(1 row)

# Below pocess tree is from todays process logs (i just logged `ps fax` output
every 5 mts to a file)

postgres 8951 0.0 0.1 2270284 60484 ? S Jun29 0:53
/usr/local/postgres/pgsql-8.2.3/bin/postgres -D /usr/local/p
ostgres/current/data -i
postgres 8989 4.8 0.0 57496 948 ? Ss Jun29 547:03 \_ postgres:
logger process
postgres 9002 0.0 6.4 2271532 2127764 ? Ss Jun29 4:06 \_ postgres:
writer process
postgres 9003 0.0 0.0 58564 1024 ? Ss Jun29 0:02 \_ postgres:
archiver process
postgres 9004 0.0 0.0 58448 832 ? Ss Jun29 0:00 \_ postgres:
stats collector process
postgres 10259 3.7 3.4 2293908 1143908 ? Ds 07:06 3:18 \_ postgres:
autovacuum process dbname

# complete postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 1200
superuser_reserved_connections = 5
shared_buffers = 262143
work_mem = 49152
max_fsm_pages = 6000000
checkpoint_segments = 9
archive_command = '/usr/local/postgres/WALLogs/copy_to_archive.sh %p %f'
effective_cache_size = 2752512
random_page_cost = 2.5
default_statistics_target = 50
log_destination = 'stderr'
redirect_stderr = true
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_size = 256000
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] '
log_statement = 'all'
stats_start_collector = on
stats_command_string = on
statement_timeout = 120000
deadlock_timeout = 1000
add_missing_from = on

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

Re: [ADMIN] Recommended RAID for Postgres

On Mon, Jun 30, 2008 at 3:30 PM, Thomas Bräutigam <thomas.braeutigam@nexustelecom.com> wrote:

Hello all,

I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra. What do you guys recommend on RAID Levels for this Database. Which does Postgres recommend, and with which do Postgres run very good or in the best way?

We are running one ~2 TB database, mainly read-only stuff except for at batch import process every night. We are using RAID5 (RAID10 would require too many hard disk drives).

What Backup Strategy do you think would be the best. Dump the DB once a week or work with the WAL`s? For your info, the data which is feeded to the database is available and could be feeded again but it would maybe take a couple of days. So what would be a solution to bring up the huge DB in about one day after a crash. This would be the target.

I'd say that backup (using filesystem tools) the database once a week or fortnight and archive wals everyday. That's what we are doing and it's been working just fine.

Regards

Mikko

[NOVICE] how to get dependancies of a table?

I want to delete a table and get the message

ERROR: cannot drop table dummyblabla because other objects
depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.

Cascading is a little bit risky so I want to avoid that.

Instead I would prefer to see the dependancies. I have no idea where to
look.

Any hints or ideas?

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

Re: [HACKERS] PATCH: CITEXT 2.0

David E. Wheeler napsal(a):
> Replying to myself, but I've made some local changes (see other
> messages) and just wanted to follow up on some of my own comments.
>
> On Jul 2, 2008, at 21:38, David E. Wheeler wrote:
>
>>> 4) Operator = citext_eq is not correct. See comment
>>> http://doxygen.postgresql.org/varlena_8c.html#8621d064d14f259c594e4df3c1a64cac

>>>
>>
>> So should citextcmp() call strncmp() instead of varst_cmp()? The
>> latter is what I saw in varlena.c.
>
> I'm guessing that the answer is "no," since varstr_cmp() uses strncmp()
> internally, as appropriate to the locale. Correct?

You have to use varstr_cmp in citextcmp. Your code is correct, because for
< <= >= > operators you need collation sensible function.

You need to change only citext_cmp function to use strncmp() or call texteq
function.

>>> There must be difference between equality and collation for example
>>> in Czech language 'láska' and 'laská' are different word it means
>>> that 'láska' != 'laská'. But there is no difference in collation
>>> order. See Unicode Universal Collation Algorithm for detail.
>>
>> I'll leave the collation stuff to the functions I call (*far* from my
>> specialty), but I'll add a test for this and make sure it works as
>> expected. Um, although, with what collation should it be tested? The
>> tests I wrote assume en_US.UTF-8.
>
> I added this test and is passes:
>
> SELECT isnt( 'láska'::citext, 'laská'::citext, 'Diffrent accented
> characters should not be equivalent' );

I'm think that this test will work correctly for en_US.UTF-8 at any time. I
guess the test make sense only when Czech collation (cs_CZ.UTF-8) is selected,
but unfortunately, you cannot change collation during your test :(.

I think, Best solution for now is to keep the test and add comment about
recommended collation for this test.

Zdenek

--
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] Pattern Matching

Guten Morgen allerseits

wie kann ich bei Postgres in einem Pattern eine Zeichenfolge ausschließen?

Als Beispiel zwei unterschiedliche URL. Die erste URL soll gematched
werden, die zweite nicht:

a) 'http://asbojesus.wordpress.com/2007/03/02/14/'
b) 'http://feeds.wordpress.com/1.0/goreddit/globolibro.wordpress.com/319/'

Meine Idee war es mit diesem Pattern zu machen

E'^http://[a-zA-Z0-9]+[^(feeds)]\.wordpress\.com'

was leider nicht funktioniert, da dass [^(feeds)] nicht nur die
Zeichenfolge 'feeds' ausschließt, sondern die einzelnen Zeichen 'f e d s'.

Zum testen:

SELECT * from
regexp_matches('http://asbojesus.wordpress.com/2007/03/02/14/',
E'^http://[a-zA-Z0-9]+[^(feeds)]\.wordpress\.com');

SELECT * from
regexp_matches('http://feeds.wordpress.com/1.0/goreddit/globolibro.wordpress.com/319/',
E'^http://[a-zA-Z0-9]+[^(feeds)]\.wordpress\.com');

Gruß
Florian

--
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: [PATCHES] [HACKERS] WITH RECURSIVE updated to CVS TIP

Hi,

From: Hans-Juergen Schoenig <postgres@cybertec.at>
Subject: Re: [PATCHES] [HACKERS] WITH RECURSIVE updated to CVS TIP
Date: Sat, 5 Jul 2008 10:43:57 +0200

> i did some quick testing with this wonderful patch.
> it seems there are some flaws in there still:
>
> test=# explain select count(*)
> test-# from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL
> SELECT DISTINCT n+1 FROM t )
> test(# SELECT * FROM t WHERE n < 5000000000) as t
> test-# WHERE n < 100;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !> \q
>
> this one will kill the planner :(
> removing the (totally stupid) distinct avoids the core dump.

Thanks. I've fixed on local repository.

> i found one more issue;
>
> -- broken: wrong result
> test=# select count(*) from ( WITH RECURSIVE t(n) AS (
> SELECT 1 UNION ALL SELECT n + 1 FROM t)
> SELECT * FROM t WHERE n < 5000000000) as t WHERE n < (
> select count(*) from ( WITH RECURSIVE t(n) AS (
> SELECT 1 UNION ALL SELECT n + 1 FROM t )
> SELECT * FROM t WHERE n < 5000000000) as t WHERE n < 100) ;

I've fixed. However, this query enters infinite loop.

WITH RECURSIVE t(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM t)
SELECT * FROM t WHERE n < 5000000000

The planner distributed WHERE-clause into WITH-clause with previous
recursive-patch.

WITH RECURSIVE t(n) AS (
SELECT 1 WHERE n < 5000000000
UNION ALL
SELECT n + 1 FROM t WHERE n < 5000000000)
SELECT * FROM t;

This optimization is in qual_is_pushdown_safe(). So, I've fixed not to
optimize WITH-clause in the function.

Regards,
--
Yoshiyuki Asaba
y-asaba@sraoss.co.jp

>
> if i am not totally wrong, this should give us a different result.
>
> i am looking forward to see this patch in core :).
> it is simply wonderful ...
>
> many thanks,
>
> hans
>
>
>
>
>
>
> On Jul 3, 2008, at 1:11 AM, David Fetter wrote:
>
> > Folks,
> >
> > Please find patch enclosed, including some documentation.
> >
> > Can we see about getting this in this commitfest?
> >
> > 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<recursive_query-7.patch.bz2>
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
>
>
> --
> Cybertec Schönig & Schönig GmbH
> PostgreSQL Solutions and Support
> Gröhrmühlgasse 26, 2700 Wiener Neustadt
> Tel: +43/1/205 10 35 / 340
> www.postgresql-support.de, www.postgresql-support.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] [PATCHES] WIP: executor_hook for pg_stat_statements

On Mon, 2008-07-07 at 11:03 +0900, ITAGAKI Takahiro wrote:
> Simon Riggs <simon@2ndquadrant.com> wrote:
>
> > > The attached patch (executor_hook.patch) modifies HEAD as follows.
> > >
> > > - Add "tag" field (uint32) into PlannedStmt.
> > > - Add executor_hook to replace ExecutePlan().
> > > - Move ExecutePlan() to a global function.
> >
> > The executor_hook.patch is fairly trivial and I see no errors.
> >
> > The logic of including such a patch is clear. If we have a planner hook
> > then we should also have an executor hook.
>
> One issue is "tag" field. The type is now uint32. It's enough in my plugin,
> but if some people need to add more complex structures in PlannedStmt,
> Node type would be better rather than uint32. Which is better?

I was imagining that tag was just an index to another data structure,
but probably better if its a pointer.

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and Support

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

Sunday, July 6, 2008

Re: [GENERAL] roll back to 8.1 for PyQt driver work-around

Sorry to drag this on further. Though I'm now able to start pg8.3
again (thanks!), I still can't launch pg8.1. Rolling back to 8.1 is
my goal in order to work around a driver issue in Qt.

Is there an example postgresql.conf file for pg 8.1 I can review?
Mine appears to be valid only for pg8.3.

Adding quotes to the shared_buffers value allows pg8.3 to start
successfully. Unfortunately, pg8.1 continues to have issues with it.
eg:

FATAL: syntax error in file "/Library/PostgreSQL8/data/
postgresql.conf" line 107, near token "kB"
FATAL: parameter "shared_buffers" requires an integer value
FATAL: unrecognized configuration parameter
"default_text_search_config"

Thanks again!
Scott

On Jul 6, 2008, at 10:48 AM, Tom Lane wrote:

> Scott Frankel <frankel@circlesfx.com> writes:
>> When I try to start 8.3, the log file lists a fatal error in the
>> postgresql.conf file. But there are no obvious errors in that file.
>> Line 107 reads: "shared_buffers = 1600kB".
>
> You need quotes, like
> shared_buffers = '1600kB'
>
>> FATAL: incorrect checksum in control file
>
> This looks like a version compatibility problem, though I'm surprised
> it wasn't complained of earlier.
>
> 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
>

Scott Frankel
President/VFX Supervisor
Circle-S Studios
510-339-7477 (o)
510-332-2990 (c)

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

Re: [sydpug] crash in psql unixodbc driver

2008/7/7 Charles Duffy <charles.duffy@gmail.com>:
> On Fri, Jul 4, 2008 at 8:50 PM, Amos Shapira <amos.shapira@gmail.com> wrote:
>
>>
>> We are having troubles with unixodbc postgresql driver on CentOS 5
>> x86_64 on a Xen DomU.
>>
>> Here is a sample program which demonstrates the problem:
>>
>
> If I get a bit of free time later today, I'll try and reproduce your
> problem here. In the meantime, I recommend you post on one of the main
> postgresql lists with your problem, if you haven't already. Be sure to
> include an actual description of the issue, rather than just some
> source for a program which exhibits it. Also include exact versions
> for every piece of software involved (postgres, odbc driver, etc).

Thanks for the advise. I'll try to provide more complete details if we
can't solve it.

>
> There is a specific postgresql-ODBC list, but there's very little
> traffic on it. You might be better off posting on -general...

I subscribed and posted there but haven't heard anything back.

In the meantime, we had some progress with a friend I contacted
outside the list.

Cheers,

--Amos

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

Re: [GENERAL] Quick way to alter a column type?

is there any quick hacks to do this quickly? There's around 20-30million
rows of data.

I want to change a column type from varchar(4) to varchar(5) or should I
just use text instead.


ALTER TABLE tablename ALTER COLUMN columnname TYPE VARCHAR(5);
 
HTH.

-
Eric

Re: [BUGS] error ' Client encoding Mismatch' with Version 8.1.4

Hi all,

I tried to look into the problem, still unable to solve the problem.

Problem details:

We have a custom application which installs Postgres 8.1.4. It provides option for selection of encoding.

Below is the line which does the same based on the encoding selection:

msiexec /i postgresql-8.1-int.msi /qn -l %LogFile% ADDLOCAL=server,psql,pgadmin INTERNALLAUNCH=1 DOSERVICE=1 DOINITDB=1 SERVICEDOMAIN="%COMPUTERNAME%" SERVICEACCOUNT="postgres" SERVICEPASSWORD="Password1*2*3" SUPERPASSWORD="postgres" CREATESERVICEUSER=1 ENCODING=%Lang% LISTENPORT=7432 PERMITREMOTE=1 NOSHORTCUTS=1 BASEDIR=%JM_ROOT_WIN%\StdyDb DATADIR=%JM_ROOT_WIN%\StdyDb\data

Where “Lang” is the selection done by user.

I changed it to ENCODING=”UTF8” installation in UTF8 format.

I installed the Postgres 8.1.4 version with UTF8 as encoding. This works properly when my regional settings are Japanese but the same installation fails as soon as I change my regional settings to Greek. When I try to connect to the database with Greek regional settings it throws the error "Client Encoding mismatch".

I feel there is some setting done at the installation time which makes it work correctly in Japanese regional language settings only. Hence it fails when I change them to Greek.

What can be the cause for this behavior? Do I need to change some settings on cygwin or pgaccess side at installation time?

I even tried changing the encoding for database to Unicode still the same error. (createdb –E UTF8 Xyz)

Note:-

I have even tried with direct installation of Postgres version 8.1.4. It behaves in the same way as our Custom installer is behaving. It seems there is problem in the version 8.1.4 itself.

Tom,

I think the error is thrown from the Postgres side only. I am getting the same error while working with direct installer of Postgres. I get this error with Greek and Thai regional language settings, not with Japanese.

Please help me on the same.

Regards,

Ashutosh

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 26, 2008 8:29 PM
To: Ashutosh Kumar S-TLS,Chennai
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] error ' Client encoding Mismatch' with Version 8.1.4

"Ashutosh Kumar S-TLS,Chennai" <ashutoshks@hcl.in> writes:

> Problem is whenever I am trying to open the database through ODBC

> connection it throws an error 'Client encoding mismatch'.

That phrase occurs noplace in Postgres 8.1, so the error must be coming

from something on the client side. We can't really help you here ---

you need to find out which bit of software is throwing the error and

ask its authors what is the cause.

regards, tom lane

[ANNOUNCE] == PostgreSQL Weekly News - July 06 2008 ==

== PostgreSQL Weekly News - July 06 2008 ==

The July CommitFest has begun. Start reviewing :)
http://wiki.postgresql.org/wiki/CommitFest:2008-07

== PostgreSQL Product News ==

MicroOLAP Database Designer 1.2.4 for PostgreSQL released.
http://microolap.com/products/database/postgresql-designer/

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2008-07/threads.php

== PostgreSQL Local ==

pgDay Portland is July 20, just before OSCON.
http://pugs.postgresql.org/node/400

PGCon Brazil 2008 will be on September 26-27 at Unicamp in Campinas.
http://pgcon.postgresql.org.br/index.en.html

PGDay.IT 2008 will be October 17 and 18 in Prato.
http://www.pgday.org/it/

== PostgreSQL in the News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits, Archives and occasional new articles:
http://www.varlena.com/GeneralBits/

PostgreSQL Weekly News is brought to you this week by David Fetter.

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.

== Applied Patches ==

Heikki Linnakangas committed:

- Turn PGBE_ACTIVITY_SIZE into a GUC variable, track_activity_query_size.
As the buffer could now be a lot larger than before, and copying it
could thus be a lot more expensive than before, use strcpy instead
of memcpy to copy the query string, as was already suggested in
comments. Also, only copy the PgBackendStatus struct and string if
the slot is in use. Patch by Thomas Lee, with some changes by me.

- Extend VacAttrStats to allow typanalyze functions to store statistic
values of different types than the underlying column. The capability
isn't yet used for anything, but will be required by upcoming patch
to analyze tsvector columns. Jan Urbanski

- In pgsql/src/bin/pg_dump/pg_dump.c, move volatility, language, etc.
modifiers before function body in the pg_dump output for CREATE
FUNCTION. This makes it easier to read especially if the function
body is long. Original idea and patch by Greg Sabino Mullane,
though this is a stripped down version of that.

Teodor Sigaev committed:

- ltree support for multibyte encodings. Patch was made by Weiping
(laser) He, with some editorization by me.

- In pgsql/src/backend/access/gin/ginscan.c, fix initialization of
GinScanEntryData.partialMatch

Bruce Momjian committed:

- Add psql TODO item: "Add option to wrap column values at whitespace
boundaries, rather than chopping them at a fixed width. Currently,
'wrapped' format chops values into fixed widths. Perhaps the word
wrapping could use the same algorithm documented in the W3C
specification."

- Add psql TODO item: "Add 'auto' expanded mode that outputs in
expanded format if "wrapped" mode can't wrap the output to the
screen width."

- Fix recovery.conf boolean variables to take the same range of string
values as postgresql.conf.

- Issue psql connection warnings on connection start and via \c, per
observation by David Fetter.

- Add to TODO: "Fix TRUNCATE ... RESTART IDENTITY so its affect on
sequences is rolled back on transaction abort."

- Add URL for TODO: "Add database and transaction-level triggers."

- In pgsql/doc/src/sgml/config.sgml, documentation patch by Kevin L.
McBride explaining GUC lock variables, which are available if
LOCK_DEBUG is defined.

- In pgsql/src/include/c.h, update source code comment about when to
use gettext_noop().

Tom Lane committed:

- Teach autovacuum how to determine whether a temp table belongs to a
crashed backend. If so, send a LOG message to the postmaster log,
and if the table is beyond the vacuum-for-wraparound horizon,
forcibly drop it. Per recent discussions. Perhaps we ought to
back-patch this, but it probably needs to age a bit in HEAD first.

- In pgsql/src/timezone/pgtz.c, fix identify_system_timezone() so that
it tests the behavior of the system timezone setting in the current
year and for 100 years back, rather than always examining years
1904-2004. The original coding would have problems distinguishing
zones whose behavior diverged only after 2004; which is a situation
we will surely face sometime, if it's not out there already. In
passing, also prevent selection of the dummy "Factory" timezone,
even if that's exactly what the system is using. Reporting time as
GMT seems better than that.

- In pgsql/src/backend/utils/misc/guc.c, remove GUC extra_desc strings
that are redundant with the enum value lists.

- In pgsql/src/backend/utils/adt/xml.c, fix transaction-lifespan
memory leak in xpath(). Report by Matt Magoffin, fix by Kris Jurka.

- Fix psql's \d and allied commands to work with all server versions
back to 7.4. Guillaume Lelarge, with some additional fixes by me.

- Add a function pg_get_keywords() to let clients find out the set of
keywords known to the SQL parser. Dave Page

- In pgsql/src/backend/utils/misc/guc.c, prevent integer overflows
during units conversion when displaying a GUC variable that has
units. Per report from Stefan Kaltenbrunner. Backport to 8.2. I
also backported my patch of 2007-06-21 that prevented comparable
overflows on the input side, since that now seems to have enough
field track record to be back-patched safely. That patch included
addition of hints listing the available unit names, which I did not
bother to strip out of it --- this will make a little more work for
the translators, but they can copy the translation from 8.3, and
anyway an untranslated hint is better than no hint.

Magnus Hagander committed:

- In pgsql/src/backend/utils/misc/guc.c, split apart message_level_options
into one set for server-side settings and one for client-side,
restoring the previous behaviour with different sort order for the
'log' level. Also, remove redundant list of available options, since
the enum code will output it automatically.

- In pgsql/src/backend/utils/misc/guc.c, "debug" level was supposed to
be hidden, since it's just an alias for debug2.

- In pgsql/src/backend/port/win32_shmem.c, fix a couple of bugs in
win32 shmem name generation: 1. Don't cut off the prefix. With this
fix, it's again readable. 2. Properly store it in the Global
namespace as intended.

Joe Conway committed:

- When an ERROR happens on a dblink remote connection, take pains to
pass the ERROR message components locally, including using the
passed SQLSTATE. Also wrap the passed info in an appropriate
CONTEXT message. Addresses complaint by Henry Combrinck. Joe
Conway, with much good advice from Tom Lane.

Peter Eisentraut committed:

- In pgsql/src/test/regress/expected/prepare.out, clean up weird
whitespace. Separate patch to simplifiy the next change.

- Don't print the name of the database in psql \z.

- Don't refer to the database name "regression" inside the regression
test scripts, to allow running the test successfully with another
database name.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Simon Riggs sent in a patch which introduces a distinction between
hint bit setting and block dirtying, when such a distinction can
safely be made.

Simon Riggs sent in a patch which adds planner statistic hooks.

Tom Raney sent in another revision of his patch to allow EXPLAIN to
output XML.

Peter Eisentraut sent in a patch to let people set the name of the
regression test database on the command line.

Teodor Sigaev sent in revisions to the multicolumn and fast-insert
patches to GIN.

Dean Rasheed sent in a patch to add a debug_explain_plan GUC variable
which, when set to on, dumps the output of EXPLAIN ANALYZE to the
appropriate logging level.

Zdenek Kotala sent in three more revisions of his page macros cleanup
patch.

Garick Hamlin sent in a patch to support ident authentication when
using unix domain sockets on Solaris.

Simon Riggs sent in a bug fix for pg_standby per note from Ferenc
Felhoffer.

Simon Riggs sent in two revisions of a patch to document the
toggliness of certain psql commands.

Simon Riggs sent in a patch to pgbench which restricts vacuuming to
pgbench tables and changes a DELETE/VACUUM to a TRUNCATE.

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

pgsql-announce-unsubscribe@postgresql.org

[GENERAL] Quick way to alter a column type?

Is there any quick hacks to do this quickly? There's around 20-30million
rows of data.

I want to change a column type from varchar(4) to varchar(5) or should I
just use text instead.

--
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] postgresql's MVCC implementation

Tom Lane-2 wrote:
>
> It's not defined by the SQL standard, nor any other standard that I know
> of. So yes, different implementations might mean subtly different
> things by it.
>

OK, I see. Where can I find out the precise meaning of MVCC as in
PostgreSQL. I've read

http://www.postgresql.org/docs/8.3/interactive/transaction-iso.html
and wondering if there is any more detailed description.

Thanks!

-----
--
Kent Tong
Wicket tutorials freely available at http://www.agileskills2.org/EWDW
Axis2 tutorials freely available at http://www.agileskills2.org/DWSAA
--
View this message in context: http://www.nabble.com/postgresql%27s-MVCC-implementation-tp18302020p18309553.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
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] postgresql's MVCC implementation

Kent Tong <kent@cpttm.org.mo> writes:
> Is MVCC not well defined?

It's not defined by the SQL standard, nor any other standard that I know
of. So yes, different implementations might mean subtly different
things by it.

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: [GENERAL] postgresql's MVCC implementation

Tom Lane-2 wrote:
>
> If you want that to fail, use a SELECT FOR UPDATE at steps 3/4.
>
> My interpretation of MVCC is that the above example isn't even
> meaningful, because it assumes that "writing into Y" is an overwrite,
> which it is not in Postgres --- that is, if T2 reads Y again, it'll
> get the same value as before.
>

Hi Tom,

Thanks for your reply. I think what I'd like to know is the exact meaning
of MVCC as implemented in PostgreSQL. It seems that a transaction
(with isolation set to serializable) will always read the values as if they
were when the transaction started.

If it is the case, why? Is MVCC not well defined? Could say Oracle or MS
SQL implement it differently?

-----
--
Kent Tong
Wicket tutorials freely available at http://www.agileskills2.org/EWDW
Axis2 tutorials freely available at http://www.agileskills2.org/DWSAA
--
View this message in context: http://www.nabble.com/postgresql%27s-MVCC-implementation-tp18302020p18309342.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
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-es-ayuda] DATESTYLE

El día 5 de julio de 2008 14:27, Noel Martínez Juárez
<noelius79@hotmail.com> escribió:
> Fabio, te refieres al tipo de dato? me podrías explicar, gracias...

>> Porke no manejas el formato con to_char o to_date?

>> 2008/7/4, Noel Martínez Juárez <noelius79@hotmail.com>:
>> > Buenas noches, he intentado cambiar el formato de la fecha en
>> > postgresql, a
>> > día-mes-año, pero el PG-ADMIN no me muestra dicho cambio, la variable
>> > que
>> > modifico es DATESTYLE, gracias.NOEL

Amigo, se refiere, al formateado de las columnas de tipo date a la
hora de mostrarlas, date una vuelta por la ayuda en:

http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html
http://www.postgresql.org/docs/8.3/interactive/functions-formatting.html
http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html

Y si quieres materiales en español busca en el archivo de la lista en:
http://archives.postgresql.org/pgsql-es-ayuda/

Ahi si ya te debo la busqueda, porque ya hablamos de este tema
muuuuchas veces. ;)

Espero que te sirva, un abrazo,
--
§~^Calabaza^~§ from Villa Elisa, Paraguay
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?

http://archives.postgresql.org/pgsql-es-ayuda

Re: [sydpug] crash in psql unixodbc driver

On Fri, Jul 4, 2008 at 8:50 PM, Amos Shapira <amos.shapira@gmail.com> wrote:

>
> We are having troubles with unixodbc postgresql driver on CentOS 5
> x86_64 on a Xen DomU.
>
> Here is a sample program which demonstrates the problem:
>

If I get a bit of free time later today, I'll try and reproduce your
problem here. In the meantime, I recommend you post on one of the main
postgresql lists with your problem, if you haven't already. Be sure to
include an actual description of the issue, rather than just some
source for a program which exhibits it. Also include exact versions
for every piece of software involved (postgres, odbc driver, etc).

There is a specific postgresql-ODBC list, but there's very little
traffic on it. You might be better off posting on -general...

Thanks,

Charles Duffy

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

Re: [GENERAL] creating "a perfect sequence" column

On Sun, Jul 6, 2008 at 7:33 PM, Berend Tober <btober@ct.metrocast.net> wrote:
> Jack Brown wrote:
>>
>> Dear list,
>>
>> I need some tips and/or pointers to relevant documentation implementing
>> (what I chose to call) "a perfect sequence" i.e. a sequence that has no
>> missing numbers in the sequence. I'd like it to auto increment on insert,
>> and auto decrement everything bigger than its value on delete. There are
>> many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure
>> which combination would result in the most elegant implementation.
>>
>> Oh, and if you know the right term for what I just described, I'd be more
>> than pleased to hear it! :-)
>>
>
> This question comes up a lot. A term used in prior discussions is "gapless
> sequence".
>
> What would be really more interesting for discussion on this community forum
> is a detailed description or your actual use case and requirements.

I will say that if you need a gapless serial numbering system it's
still better to NOT try and do it with a pre-checked out number. For
instance, you might have a system like a court document system that
might have this requirement, that you hace CR-1 through CR-99999999 or
whatever.

In that case it's better to let the user start work, then hit CREATE
DOCUMENT when they're ready. Then your business logic can put the
data into the database, and if it goes in, then check out a number
from the sequence. I.e. there are no deletes, only failed inserts. A
system that requires you to show a number before the document has been
"created" in the system but wants no gaps is flawed. Don't give them
a number until they HAVE a document. reusing numbers already shown to
a user is a recipe for a disaster. they write down the number, and
two weeks later reference it, but it's not there.

That's one use case. It's important here to look for the way that is
less likely to lead to "oh crap!" moments.

Adding gapless sequences increases the complexity. Better to let the
complexity only live in a display layer of sorts than to rely on it
for FK-PK type stuff.

If there's any FK->PK relations involving these keys and they aren't
fully cascaded, then allowing them to be renumbered is courting
disaster. If you use a separate table for "user visible sequence
number" and store the plain sequence, gaps and all in the db, then
your actual core data is safer. You can recreate the user visible
sequence number table without affecting the actual relationship of the
data in the real data table.

I hope I'm not rambling too much.

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

[HACKERS] pg_ctl -w with postgresql.conf in non-default path

Hello guys,

This is my first post to this list..

I'm using PostgreSQL8.3.3 and I moved postgresql.conf to the
outside of DATA direcotory, and invoked postgres via pg_ctl
as following.

pg_ctl -w -D /data -o '--config-file=/home/hirano/postgresql.conf' start

This seems to work well, but when I changed the port parameter in
that postgresql.conf, pg_ctl waits for timeout by "-w" option.
In this case, postgres correctly listens the port I wrote in
config, but pg_ctl checkes the port in the data/postgresql.conf
file.

I think this is because the path to postgresql.conf is hard-coded
in the pg_ctl.c

| snprintf(conf_file, MAXPGPATH, "%s/postgresql.conf", pg_data);

But actually there're no descriptions of --config-file option in the
manual of postgres command, although I'm not sure how I could
find it...

Is it bad way to use --config-file option or pg_ctl bug?

Regards.

--
HIRANO Yoshitaka

--
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] [PATCHES] WIP: executor_hook for pg_stat_statements

Simon Riggs <simon@2ndquadrant.com> wrote:

> > The attached patch (executor_hook.patch) modifies HEAD as follows.
> >
> > - Add "tag" field (uint32) into PlannedStmt.
> > - Add executor_hook to replace ExecutePlan().
> > - Move ExecutePlan() to a global function.
>
> The executor_hook.patch is fairly trivial and I see no errors.
>
> The logic of including such a patch is clear. If we have a planner hook
> then we should also have an executor hook.

One issue is "tag" field. The type is now uint32. It's enough in my plugin,
but if some people need to add more complex structures in PlannedStmt,
Node type would be better rather than uint32. Which is better?

> Will you be completing the plugin for use in contrib?

Yes, I'll fix memory management in my plugin and re-post it
by the next commit-fest.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

--
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] Problema con caractere en un select

2008/7/4 Marcos Saldivar <baron.rojo.cuerdas.de.acero@gmail.com>:
> El día 4 de julio de 2008 17:21, Fernando Siguenza <fsigu@hotmail.com> escribió:
>>
>> Amigos yo nuevamente espero me puedan dar una mano ota ves muchas gracias a
>> todos los que me han ayudado hasta el momento
>> ahora tengo otro inconveniente, quiero realizar un select a la base de
>> datos.
>> tengo un campo de tipo cod varchar(3) y otro nom varchar(20) lo que quiero
>> hacer es esto
>> select * from tabla where cod like '%' or nom like '%' para que me retorne
>> todos los valores.
>
> quieres saber si cod o nom contiene el string "%" ???

O sea, te falta lo que va a variar en tu where:

select * from tabla
where cod like '%tupalabraabuscar%' or nom like '%tupalabraabuscar%'

Algo así verdad?

Ahora, sobre el error que te da, puede que sea otra cosa también, si
lo anterior no funciona, puedes probar lo siguiente:

select * from tabla
where cod::char like '%tupalabraabuscar%' or nom::char like
'%tupalabraabuscar%'

Date una vuelta por:
http://www.postgresql.org/docs/8.3/interactive/errcodes-appendix.html

Ahi explica algo sobre el error que tienes;

Un Abrazo,
--
§~^Calabaza^~§ from Villa Elisa, Paraguay
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [GENERAL] creating "a perfect sequence" column

Jack Brown wrote:
> Dear list,
>
> I need some tips and/or pointers to relevant documentation implementing (what I chose to call) "a perfect sequence" i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure which combination would result in the most elegant implementation.
>
> Oh, and if you know the right term for what I just described, I'd be more than pleased to hear it! :-)
>

This question comes up a lot. A term used in prior discussions is
"gapless sequence".

What would be really more interesting for discussion on this
community forum is a detailed description or your actual use case
and requirements.

--
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] Sorting writes during checkpoint

(Go back to -hackers)

Simon Riggs <simon@2ndquadrant.com> wrote:

> No action on this seen since last commitfest, but I think we should do
> something with it, rather than just ignore it.

I will have a plan to test it on RAID-5 disks, where sequential writing
are much better than random writing. I'll send the result as an evidence.

Also, I have a relevant idea to sorting writes. Smoothed checkpoint in 8.3
spreads write(), but calls fsync() at once. With sorted writes, we can
call fsync() segment-by-segment for each writes of dirty pages contained
in the segment. It could improve worst response time during checkpoints.

> Note that if we do this for checkpoint we should also do this for
> FlushRelationBuffers(), used during heap_sync(), for exactly the same
> reasons.

Ah, I overlooked FlushRelationBuffers(). It is worth sorting.

> Would suggest calling it bulk_io_hook() or similar.

I think we need to reconsider the "bufmgr - smgr - md" layers, not only
an I/O elevator hook. If we will have spreading fsync(), bufmgr should
know where the file segments are switched. It seems to break area
between bufmgr and md in the current architecture unhappily.

In addition, the current smgr layer is completely useless because
it cannot be extended dynamically and cannot handle multiple md-layer
modules. I would rather merge current smgr and part of bufmgr into
a new smgr and add smgr_hook() than bulk_io_hook().

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

--
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] creating "a perfect sequence" column

On Sun, Jul 6, 2008 at 6:15 PM, Jack Brown <zidibik@yahoo.com> wrote:
> Dear list,
>
> I need some tips and/or pointers to relevant documentation implementing (what I chose to call) "a perfect sequence" i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure which combination would result in the most elegant implementation.

This would actually be a perfectly awful sequence. :) Seriously,
it's costly to lock the whole table, set the sequence to the last
available value and lock it in terms of concurrency.

>
> Oh, and if you know the right term for what I just described, I'd be more than pleased to hear it! :-)

I believe it's called a "How to destroy concurrency" or something like that.

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

[GENERAL] creating "a perfect sequence" column

Dear list,

I need some tips and/or pointers to relevant documentation implementing (what I chose to call) "a perfect sequence" i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure which combination would result in the most elegant implementation.

Oh, and if you know the right term for what I just described, I'd be more than pleased to hear it! :-)

All the best,
Jack

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

[HACKERS] Priority of timezone names vs abbreviations in AT TIME ZONE

I've been thinking about the complaint here:
http://archives.postgresql.org/pgsql-general/2008-07/msg00201.php
and I think that the real issue boils down to the fact that timestamp
input checks a name against the timezone abbrevs list first, and the
zic database second; whereas the various forms of AT TIME ZONE
do the lookup in the other order. I am not sure what the original
rationale for that behavior was, if indeed it was thought through
at all --- but what I think now is we should change AT TIME ZONE to
check for abbrevs first. Consistency suggests that both lookups
should produce the same result, and given that timestamp input is
far more heavily used than AT TIME ZONE, changing the behavior of
the latter seems safest. I'm inclined to think that the timestamp
input behavior is more correct anyway: to me "midnight EST" means
midnight standard time, regardless of whether daylight savings
time is in force or not.

Should we consider this a bug and back-patch it, or just fix it
for 8.4 and later?

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: [SQL] Egroupware infolog query slow (includes query plan)

Mark Stosberg <mark@summersault.com> writes:
> I'm not skilled enough at reading the "Explain Analzyze" output to
> understand what the primary problem is.

The problem is the repeated execution of the subquery in the SELECT
list; that's taking over 683 of the 686 seconds:

> SubPlan
> -> Aggregate (cost=2162.60..2162.61 rows=1
> width=0) (actual time=21.073..21.073 rows=1 loops=32424)
^^^^^^ ^^^^^

The current formulation of the query guarantees that you can't do better
than a nestloop join with "sub" on the inside, and that nestloop isn't
even indexed. See if you can convert it to a regular join instead of a
sub-select (probably with GROUP BY instead of DISTINCT).

Also, those LIKE conditions are just horrid: slow *and* unreadable.
Consider redesigning your data representation. Perhaps converting
info_responsible to an int array would be reasonable.

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: [PORTS] contrib - fuzzystrmatch installation issue

Tom Lane wrote:
>> psql:fuzzystrmatch.sql:32: ERROR: could not find function "dmetaphone_alt"
>> in file "/usr/lib/pgsql/fuzzystrmatch.so"
>
> I'm suspicious that /usr/lib/pgsql/fuzzystrmatch.so is actually the 7.4
> version of that module (which is what shipped in RHEL4 to begin with,
> and which would've lacked exactly those three functions). However a
> hole in this theory is that an 8.3 server should've refused to load
> a 7.4 extension module at all. Anyway, double check versions,
> installation locations, etc. A self-configured Postgres installation
> would not default to installing into /usr/lib/pgsql, so it certainly
> seems possible that that file isn't your newly built version.

He had also written me directly off list -- version mismatch it was. See
below. I should have thought to copy the list on my reply -- sorry about
that.

Joe

----------------------
Kenaniah Cerny wrote:
> Joe,
>
> You hit the nail right on the head. Apparently, I was trying to use
> Postgres 7.4.x with the 8.1.11 contrib version. Upgrading Postgres to
> 8.1 solved the issue. Can't believe I did that.
>
> Thanks for pointing it out. It would be a great addition to the
> documentation if the contrib functions were labeled with the versions
> that they first appear in the stable distro. It would most definitely
> help out people like me :-/
>
> And sorry for the double-email.
>
> Thanks,
> Kenaniah
>
> On Sat, Jul 5, 2008 at 4:10 PM, Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.com>> wrote:
>
> Kenaniah Cerny wrote:
>
> The compiler didn't return any errors during the build process,
> leaving me clueless as to why it's not working correctly.
>
> If you have any insight, please feel free to shoot me back an
> email or to call me at (951) 385-9506, as it is slightly urgent.
>
>
> I see that you simultaneously wrote to the list, but please do that
> first next time.
>
> You haven't provided enough information to be sure, but it sounds to
> me like you have at least two different versions of Postgres
> installed -- probably both 8.3.3 and 7.4.x. Try doing:
>
> locate fuzzystrmatch.so
>
> In 7.4.x the functions being complained about did not exist. You
> could also try doing:
>
>
> psql -d template1 -U postgres
> template1=# select version();
>
> and see what version of postgres you wind up attached to.
>
> Joe
>
>

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

Re: [PORTS] contrib - fuzzystrmatch installation issue

"Kenaniah Cerny" <kenaniah@gmail.com> writes:
> I am running Postgres 8.3.3 on a CentOS box and I had a few issues when
> trying to install the fuzzystrmatch library. The library seemed to compile
> and make install correctly, but when I ran the sql scripts to add the
> functions to a database, I came up with this issue:

> # psql -d template1 -U postgres -f fuzzystrmatch.sql
> SET
> CREATE FUNCTION
> CREATE FUNCTION
> CREATE FUNCTION
> CREATE FUNCTION
> psql:fuzzystrmatch.sql:24: ERROR: could not find function "difference" in
> file "/usr/lib/pgsql/fuzzystrmatch.so"
> psql:fuzzystrmatch.sql:28: ERROR: could not find function "dmetaphone" in
> file "/usr/lib/pgsql/fuzzystrmatch.so"
> psql:fuzzystrmatch.sql:32: ERROR: could not find function "dmetaphone_alt"
> in file "/usr/lib/pgsql/fuzzystrmatch.so"

I'm suspicious that /usr/lib/pgsql/fuzzystrmatch.so is actually the 7.4
version of that module (which is what shipped in RHEL4 to begin with,
and which would've lacked exactly those three functions). However a
hole in this theory is that an 8.3 server should've refused to load
a 7.4 extension module at all. Anyway, double check versions,
installation locations, etc. A self-configured Postgres installation
would not default to installing into /usr/lib/pgsql, so it certainly
seems possible that that file isn't your newly built version.

Another thing that seems pretty odd is that the above complains about
difference(), which *is* present in the .so according to nm.

BTW, why are you compiling fuzzystrmatch for yourself at all, instead
of just installing the postgresql-contrib RPM?

regards, tom lane

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

Re: [SQL] Egroupware infolog query slow (includes query plan)

I should have mentioned in the last post that PostgreSQL 8.2.9 is in
use. I could upgrade to 8.3.x if that is expected to help performance in
this case.

Mark

On Sun, 2008-07-06 at 16:23 -0400, Mark Stosberg wrote:
> Hello,
>
> I could use some help figuring out how to speed up a query. Below is the
> SQL and query plan for a common SELECT done by the open source
> eGroupware project.
>
> Now that there are about 16,000 rows in egw_infolog and 32,000 in
> egw_infolog_extra, the below query takes about 6 minutes to finish!
>
> I'm not skilled enough at reading the "Explain Analzyze" output to
> understand what the primary problem is.
>
> Thanks!
>
> Mark
>
> ###########
>
> SELECT DISTINCT main.* ,(
> SELECT count(*) FROM egw_infolog sub WHERE
> sub.info_id_parent=main.info_id AND (info_owner=6 OR
> ((','||info_responsible||',' LIKE '%,-2,%' OR
> ','||info_responsible||',' LIKE '%,-1,%' OR
> ','||info_responsible||',' LIKE '%,6,%') AND
> info_access='public') OR info_owner IN (6) OR
> (info_access='public'
> AND info_owner IN(6)))
> ) AS info_anz_subs FROM egw_infolog main
> LEFT JOIN egw_infolog_extra ON main.info_id=egw_infolog_extra.info_id
> WHERE (
> (info_owner=6 OR ((','||info_responsible||',' LIKE '%,-2,%' OR
> ','||info_responsible||',' LIKE '%,-1,%' OR
> ','||info_responsible||',' LIKE '%,6,%') AND
> info_access='public') OR info_owner IN (6) OR
> (info_access='public'
> AND info_owner IN(6))) AND info_status <> 'deleted' )
> ORDER BY
> info_datemodified DESC LIMIT 15 OFFSET 0
>
> Query plan:
> ####
> Limit (cost=68624989.18..68624991.31 rows=15 width=1011) (actual
> time=686260.735..686260.878 rows=15 loops=1)
> -> Unique (cost=68624989.18..68627288.59 rows=16212 width=1011)
> (actual time=686260.733..686260.857 rows=15 loops=1)
> -> Sort (cost=68624989.18..68625068.47 rows=31716 width=1011)
> (actual time=686260.730..686260.766 rows=29 loops=1)
> Sort Key: main.info_datemodified, main.info_id,
> main.info_type, main.info_from, main.info_addr, main.info_subject,
> main.info_des, main.info_owner, main.info_responsible, main.info_access,
> main.info_cat, main.info_startdate, main.info_enddate,
> main.info_id_parent, main.info_planned_time, main.info_used_time,
> main.info_status, main.info_confirm, main.info_modifier,
> main.info_link_id, main.info_priority, main.pl_id, main.info_price,
> main.info_percent, main.info_datecompleted, main.info_location,
> main.info_custom_from, (subplan)
> -> Merge Left Join (cost=0.00..68594428.95 rows=31716
> width=1011) (actual time=21.358..684226.134 rows=32424 loops=1)
> Merge Cond: (main.info_id =
> egw_infolog_extra.info_id)
> -> Index Scan using egw_infolog_pkey on egw_infolog
> main (cost=0.00..3025.84 rows=16212 width=1011) (actual
> time=0.060..135.766 rows=16212 loops=1)
> Filter: (((info_owner = 6) OR (((((','::text
> || (info_responsible)::text) || ','::text) ~~ '%,-2,%'::text) OR
> (((','::text || (info_responsible)::text) || ','::text) ~~ '%,-1,
> %'::text) OR (((','::text || (info_responsible)::text) || ','::text) ~~
> '%,6,%'::text)) AND ((info_access)::text = 'public'::text)) OR
> (info_owner = 6) OR (((info_access)::text = 'public'::text) AND
> (info_owner = 6))) AND ((info_status)::text <> 'deleted'::text))
> -> Index Scan using egw_infolog_extra_pkey on
> egw_infolog_extra (cost=0.00..1546.30 rows=32424 width=4) (actual
> time=0.025..317.272 rows=32424 loops=1)
> SubPlan
> -> Aggregate (cost=2162.60..2162.61 rows=1
> width=0) (actual time=21.073..21.073 rows=1 loops=32424)
> -> Seq Scan on egw_infolog sub
> (cost=0.00..2122.07 rows=16212 width=0) (actual time=21.065..21.065
> rows=0 loops=32424)
> Filter: ((info_id_parent = $0) AND
> ((info_owner = 6) OR (((((','::text || (info_responsible)::text) ||
> ','::text) ~~ '%,-2,%'::text) OR (((','::text ||
> (info_responsible)::text) || ','::text) ~~ '%,-1,%'::text) OR
> (((','::text || (info_responsible)::text) || ','::text) ~~ '%,6,
> %'::text)) AND ((info_access)::text = 'public'::text)) OR (info_owner =
> 6) OR (((info_access)::text = 'public'::text) AND (info_owner = 6))))
> Total runtime: 686278.730 ms
>
>
>
>
>

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

[SQL] Egroupware infolog query slow (includes query plan)

Hello,

I could use some help figuring out how to speed up a query. Below is the
SQL and query plan for a common SELECT done by the open source
eGroupware project.

Now that there are about 16,000 rows in egw_infolog and 32,000 in
egw_infolog_extra, the below query takes about 6 minutes to finish!

I'm not skilled enough at reading the "Explain Analzyze" output to
understand what the primary problem is.

Thanks!

Mark

###########

SELECT DISTINCT main.* ,(
SELECT count(*) FROM egw_infolog sub WHERE
sub.info_id_parent=main.info_id AND (info_owner=6 OR
((','||info_responsible||',' LIKE '%,-2,%' OR
','||info_responsible||',' LIKE '%,-1,%' OR
','||info_responsible||',' LIKE '%,6,%') AND
info_access='public') OR info_owner IN (6) OR
(info_access='public'
AND info_owner IN(6)))
) AS info_anz_subs FROM egw_infolog main
LEFT JOIN egw_infolog_extra ON main.info_id=egw_infolog_extra.info_id
WHERE (
(info_owner=6 OR ((','||info_responsible||',' LIKE '%,-2,%' OR
','||info_responsible||',' LIKE '%,-1,%' OR
','||info_responsible||',' LIKE '%,6,%') AND
info_access='public') OR info_owner IN (6) OR
(info_access='public'
AND info_owner IN(6))) AND info_status <> 'deleted' )
ORDER BY
info_datemodified DESC LIMIT 15 OFFSET 0

Query plan:
####
Limit (cost=68624989.18..68624991.31 rows=15 width=1011) (actual
time=686260.735..686260.878 rows=15 loops=1)
-> Unique (cost=68624989.18..68627288.59 rows=16212 width=1011)
(actual time=686260.733..686260.857 rows=15 loops=1)
-> Sort (cost=68624989.18..68625068.47 rows=31716 width=1011)
(actual time=686260.730..686260.766 rows=29 loops=1)
Sort Key: main.info_datemodified, main.info_id,
main.info_type, main.info_from, main.info_addr, main.info_subject,
main.info_des, main.info_owner, main.info_responsible, main.info_access,
main.info_cat, main.info_startdate, main.info_enddate,
main.info_id_parent, main.info_planned_time, main.info_used_time,
main.info_status, main.info_confirm, main.info_modifier,
main.info_link_id, main.info_priority, main.pl_id, main.info_price,
main.info_percent, main.info_datecompleted, main.info_location,
main.info_custom_from, (subplan)
-> Merge Left Join (cost=0.00..68594428.95 rows=31716
width=1011) (actual time=21.358..684226.134 rows=32424 loops=1)
Merge Cond: (main.info_id =
egw_infolog_extra.info_id)
-> Index Scan using egw_infolog_pkey on egw_infolog
main (cost=0.00..3025.84 rows=16212 width=1011) (actual
time=0.060..135.766 rows=16212 loops=1)
Filter: (((info_owner = 6) OR (((((','::text
|| (info_responsible)::text) || ','::text) ~~ '%,-2,%'::text) OR
(((','::text || (info_responsible)::text) || ','::text) ~~ '%,-1,
%'::text) OR (((','::text || (info_responsible)::text) || ','::text) ~~
'%,6,%'::text)) AND ((info_access)::text = 'public'::text)) OR
(info_owner = 6) OR (((info_access)::text = 'public'::text) AND
(info_owner = 6))) AND ((info_status)::text <> 'deleted'::text))
-> Index Scan using egw_infolog_extra_pkey on
egw_infolog_extra (cost=0.00..1546.30 rows=32424 width=4) (actual
time=0.025..317.272 rows=32424 loops=1)
SubPlan
-> Aggregate (cost=2162.60..2162.61 rows=1
width=0) (actual time=21.073..21.073 rows=1 loops=32424)
-> Seq Scan on egw_infolog sub
(cost=0.00..2122.07 rows=16212 width=0) (actual time=21.065..21.065
rows=0 loops=32424)
Filter: ((info_id_parent = $0) AND
((info_owner = 6) OR (((((','::text || (info_responsible)::text) ||
','::text) ~~ '%,-2,%'::text) OR (((','::text ||
(info_responsible)::text) || ','::text) ~~ '%,-1,%'::text) OR
(((','::text || (info_responsible)::text) || ','::text) ~~ '%,6,
%'::text)) AND ((info_access)::text = 'public'::text)) OR (info_owner =
6) OR (((info_access)::text = 'public'::text) AND (info_owner = 6))))
Total runtime: 686278.730 ms

--
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] log_rotation_age integer overflow display quirk

Bernd Helmle <mailings@oopsware.de> writes:
> --On Freitag, Juli 04, 2008 11:31:07 +0200 Stefan Kaltenbrunner
> <stefan@kaltenbrunner.cc> wrote:
>> I just noticed that setting log_rotation_age to a value larger than 24
>> days results in rather weird output (I have not actually tested yet if
>> that affects the functionality too or just the output):

> This seems to be a bug in _ShowOption(), where the corresponding value is
> converted into milliseconds to get the biggest possible time unit to
> display. This overflows the result variable (which is declared as int),
> causing this strange output.

Yup --- fixed by using int64 arithmetic for the units conversion.

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

[COMMITTERS] pgsql: Prevent integer overflows during units conversion when displaying

Log Message:
-----------
Prevent integer overflows during units conversion when displaying a GUC
variable that has units. Per report from Stefan Kaltenbrunner.

Backport to 8.2. I also backported my patch of 2007-06-21 that prevented
comparable overflows on the input side, since that now seems to have enough
field track record to be back-patched safely. That patch included addition
of hints listing the available unit names, which I did not bother to strip
out of it --- this will make a little more work for the translators, but
they can copy the translation from 8.3, and anyway an untranslated hint
is better than no hint.

Tags:
----
REL8_2_STABLE

Modified Files:
--------------
pgsql/src/backend/utils/misc:
guc.c (r1.360.2.2 -> r1.360.2.3)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc.c?r1=1.360.2.2&r2=1.360.2.3)

--
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: Prevent integer overflows during units conversion when displaying

Log Message:
-----------
Prevent integer overflows during units conversion when displaying a GUC
variable that has units. Per report from Stefan Kaltenbrunner.

Tags:
----
REL8_3_STABLE

Modified Files:
--------------
pgsql/src/backend/utils/misc:
guc.c (r1.432.2.1 -> r1.432.2.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc.c?r1=1.432.2.1&r2=1.432.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: Prevent integer overflows during units conversion when displaying

Log Message:
-----------
Prevent integer overflows during units conversion when displaying a GUC
variable that has units. Per report from Stefan Kaltenbrunner.

Modified Files:
--------------
pgsql/src/backend/utils/misc:
guc.c (r1.461 -> r1.462)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc.c?r1=1.461&r2=1.462)

--
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] CommitFest rules

On Sun, Jul 6, 2008 at 11:44 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> Just a personal request, but I would like a permanent URL that points to
> the in-progress commit page and is only changed when the commit fest of
> _over_.
>

Well, most of the time there isn't any commitfest "in-progress" at
all. If you want a permanent redirect, how do you expect it to behave
when we are between commitfests?

Speaking of the issue of navigating between commitfests, I agree that
this could use some love.

I mentioned to Alvaro over IM that I was thinking about adding a
navigation bar at the bottom of each commitfest. This would show,
e.g., for the current July commitfest:

<< Previous commitfest | Current commitfest | Next commitfest >>
2008-05 2008-07 2008-09
[closed] [current] [open]

I'm still pondering a way to add this that would require minimal
ongoing maintenance, but I wanted to gauge interest before investing
too much more headspace into it.

Is this something that you guys would find valuable?

Cheers,
BJ

--
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] postgresql's MVCC implementation

Kent Tong <kent@cpttm.org.mo> writes:
> 1: T1 sets isolation to serializable & begins a transaction
> 2: T2 sets isolation to serializable & begins a transaction
> 3: T1 reads X into v1
> 4: T2 reads Y into v2
> 5: T1 writes v1 into Y
> 6: T2 writes v2 into X
> 7: T1 commits
> 8: T2 commits

> Obviously, this sequence is also not a serializable execution. However, it
> is allowed by
> PostgreSQL. Moreover, according to the MVCC reference above, step 5 should
> really
> fail because the read timestamp of Y is that of T2, which is greater than
> that of T1.

If you want that to fail, use a SELECT FOR UPDATE at steps 3/4.

My interpretation of MVCC is that the above example isn't even
meaningful, because it assumes that "writing into Y" is an overwrite,
which it is not in Postgres --- that is, if T2 reads Y again, it'll
get the same value as before.

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: [pgsql-www] Proposal to remove some mailing lists

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

- --On Thursday, July 03, 2008 13:02:16 -0400 Robert Treat
<xzilla@users.sourceforge.net> wrote:

> This presumes one only uses php for web development... really it can be used
> for scripting and gui applications as well. Heck, I even heard of some
> company trying to use it for database procedural work.

How about pgsql-interfaces?

- --
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.9 (FreeBSD)

iEYEARECAAYFAkhwz2AACgkQ4QvfyHIvDvOw0gCg45fPEXHLyH8dqmR9tf/VeOao
OdQAnjvIa7nQzyDHwA+34cUtpjJD3Ob2
=r7bz
-----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

Re: [GENERAL] Installation problem -- another installation is in progress

Thanks for your prompt response. Actually, I solved the problem after posting. When I got to the "Ready to Install" dialog, in the Task Manager, there were 4 instances of msiexec.exe running. I experimented with deleting 3 of them. At first, I killed the whole install, but finally I deleting the correct three, and the install ran to completion.

Susan

--- On Sun, 7/6/08, Dave Page <dpage@pgadmin.org> wrote:

> From: Dave Page <dpage@pgadmin.org>
> Subject: Re: [GENERAL] Installation problem -- another installation is in progress
> To: susancrayne@yahoo.com
> Cc: pgsql-general@postgresql.org
> Date: Sunday, July 6, 2008, 3:59 AM
> On Sat, Jul 5, 2008 at 11:16 PM, Susan Crayne
> <susancrayne@yahoo.com> wrote:
> > I am attempting to install the postrgresql-8.3.3-1
> download on Windows Vista. When I get to the "Ready
> to install" dialog and click OK, I get the message
> "Another installation is in progress", and I need
> to click on cancel and end the installation -- otherwise I
> am in a loop. This happens even after I reboot. Any help
> would be greatly appreciated.
> >
>
> It sounds like your installer database is, umm, confused. I
> assume
> you're running setup.bat? If so, try just running the
> vcredist
> executable on it's own. If that completes OK, then try
> postgresql-8.3.msi.
>
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com

--
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] CommitFest rules

Dave Page wrote:
> On Sun, Jul 6, 2008 at 6:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Robert Treat <xzilla@users.sourceforge.net> writes:
> >> Hmm, looks like some of the things I was thinking about have been added
> >> recenelt... cool. One question I have still remains though, on the main
> >> developer page (http://wiki.postgresql.org/wiki/Development_information) it
> >> has a link to the "current commitfest", which points to september's
> >> commitfest page. ISTM the current commitfest is July's, since that's the one
> >> we're currently working on.
> >
> > The meaning of "current commitfest" as used on that page is "the place
> > you should submit a new patch today". I agree there's a terminological
> > problem here, and we need to somehow distinguish that meaning from "the
> > commitfest we are currently trying to close out". But you are not
> > helping matters by trying to eliminate the distinction.
>
> Agreed - but Robert does have a point - I know both Greg & I have
> resorted to searching to find the in-progress fest page. I'll see if I
> can improve the index page a little.

Just a personal request, but I would like a permanent URL that points to
the in-progress commit page and is only changed when the commit fest of
_over_.

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

Monday, July 7, 2008

Sunday, July 6, 2008

Blog Archive